namespace TMSDAL.Query.AssessmentAttempt
{
    /// <summary>
    /// Required indexes:
    ///   IX_TASSESSMENTATTEMPT_ASSESSMENTID_TENANTID on (ASSESSMENTID, TENANTID)
    ///   IX_TASSESSMENTATTEMPT_ENROLLMENTID on (ENROLLMENTID)
    ///   IX_TASSESSMENTATTEMPT_TENANTID on (TENANTID)
    /// </summary>
    public static class AssessmentAttemptQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_ASSESSMENTATTEMPT = @"
SELECT  aa.ASSESSMENTATTEMPTID      AS AssessmentAttemptId,
        aa.ASSESSMENTID             AS AssessmentId,
        sa.ASSESSMENTTITLE          AS AssessmentTitle,
        aa.ENROLLMENTID             AS EnrollmentId,
        emp.EMPLOYEENAME            AS EmployeeName,
        aa.ATTEMPTNO                AS AttemptNo,
        aa.STARTEDON                AS StartedOn,
        aa.COMPLETEDON              AS CompletedOn,
        aa.TOTALMARKS               AS TotalMarks,
        aa.MARKSOBTAINED            AS MarksObtained,
        aa.SCOREPCT                 AS ScorePct,
        aa.ATTEMPTISPASSED          AS AttemptIsPassed,
        aa.EVALUATEDBYID            AS EvaluatedById,
        evl.EMPLOYEENAME            AS EvaluatedByName,
        aa.ISLMSSYNCED              AS IsLmsSynced,
        aa.REMARKS                  AS Remarks,
        aa.VERSION                  AS Version,
        aa.STATUS                   AS Status,
        aa.SORTORDER                AS SortOrder,
        aa.SOURCETYPE               AS SourceType,
        aa.TENANTID                 AS TenantId,
        aa.CREATEDBYID              AS CreatedById,
        aa.CREATEDON                AS CreatedOn,
        aa.MODIFIEDBYID             AS ModifiedById,
        aa.MODIFIEDON               AS ModifiedOn
FROM    DBO.TASSESSMENTATTEMPT aa
INNER JOIN DBO.MSESSIONASSESSMENT sa
        ON sa.ASSESSMENTID = aa.ASSESSMENTID
INNER JOIN DBO.TTRAININGENROLLMENT te
        ON te.ENROLLMENTID = aa.ENROLLMENTID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = te.EMPLOYEEID
LEFT JOIN DBO.MEMPLOYEE evl
        ON evl.EMPLOYEEID = aa.EVALUATEDBYID
WHERE   aa.ASSESSMENTATTEMPTID = @AssessmentAttemptId
AND     aa.TENANTID            = @TenantId";


        // =====================================================
        // GET LIST BY ENROLLMENT
        // =====================================================
        public const string GET_ASSESSMENTATTEMPT_LIST = @"
SELECT  aa.ASSESSMENTATTEMPTID      AS AssessmentAttemptId,
        aa.ASSESSMENTID             AS AssessmentId,
        sa.ASSESSMENTTITLE          AS AssessmentTitle,
        aa.ENROLLMENTID             AS EnrollmentId,
        emp.EMPLOYEENAME            AS EmployeeName,
        aa.ATTEMPTNO                AS AttemptNo,
        aa.STARTEDON                AS StartedOn,
        aa.COMPLETEDON              AS CompletedOn,
        aa.TOTALMARKS               AS TotalMarks,
        aa.MARKSOBTAINED            AS MarksObtained,
        aa.SCOREPCT                 AS ScorePct,
        aa.ATTEMPTISPASSED          AS AttemptIsPassed,
        aa.EVALUATEDBYID            AS EvaluatedById,
        evl.EMPLOYEENAME            AS EvaluatedByName,
        aa.ISLMSSYNCED              AS IsLmsSynced,
        aa.REMARKS                  AS Remarks,
        aa.VERSION                  AS Version,
        aa.STATUS                   AS Status,
        aa.TENANTID                 AS TenantId,
        aa.CREATEDBYID              AS CreatedById,
        aa.CREATEDON                AS CreatedOn,
        aa.MODIFIEDBYID             AS ModifiedById,
        aa.MODIFIEDON               AS ModifiedOn
FROM    DBO.TASSESSMENTATTEMPT aa
INNER JOIN DBO.MSESSIONASSESSMENT sa
        ON sa.ASSESSMENTID = aa.ASSESSMENTID
INNER JOIN DBO.TTRAININGENROLLMENT te
        ON te.ENROLLMENTID = aa.ENROLLMENTID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = te.EMPLOYEEID
LEFT JOIN DBO.MEMPLOYEE evl
        ON evl.EMPLOYEEID = aa.EVALUATEDBYID
WHERE   aa.ENROLLMENTID = @EnrollmentId
AND     aa.TENANTID     = @TenantId
ORDER BY aa.ATTEMPTNO DESC";


        // =====================================================
        // GET SELECTLIST — Criteria Based
        // =====================================================
        public const string GET_SELECTLIST_ASSESSMENTATTEMPT = @"
SELECT  aa.ASSESSMENTATTEMPTID      AS AssessmentAttemptId,
        aa.ASSESSMENTID             AS AssessmentId,
        sa.ASSESSMENTTITLE          AS AssessmentTitle,
        aa.ENROLLMENTID             AS EnrollmentId,
        emp.EMPLOYEENAME            AS EmployeeName,
        aa.ATTEMPTNO                AS AttemptNo,
        aa.SCOREPCT                 AS ScorePct,
        aa.ATTEMPTISPASSED          AS AttemptIsPassed,
        aa.STARTEDON                AS StartedOn,
        aa.COMPLETEDON              AS CompletedOn
FROM    DBO.TASSESSMENTATTEMPT aa
INNER JOIN DBO.MSESSIONASSESSMENT sa
        ON sa.ASSESSMENTID = aa.ASSESSMENTID
INNER JOIN DBO.TTRAININGENROLLMENT te
        ON te.ENROLLMENTID = aa.ENROLLMENTID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = te.EMPLOYEEID
WHERE   aa.TENANTID = @TenantId
{DYNAMIC_WHERE}
ORDER BY aa.STARTEDON DESC";


        // =====================================================
        // GET FULL LIST — all attempts with enrollment details
        // =====================================================
        public const string GET_ASSESSMENTATTEMPT_FULL_LIST = @"
SELECT  aa.ASSESSMENTATTEMPTID      AS AssessmentAttemptId,
        aa.ASSESSMENTID             AS AssessmentId,
        sa.ASSESSMENTTITLE          AS AssessmentTitle,
        sa.ASSESSMENTTYPE           AS AssessmentType,

        aa.ENROLLMENTID             AS EnrollmentId,
        te.ENROLLMENTCODE           AS EnrollmentCode,
        te.ENROLLMENTNAME           AS EnrollmentName,

        ti.INSTANCEID               AS InstanceId,
        ti.INSTANCECODE             AS InstanceCode,
        ti.INSTANCETITLE            AS InstanceTitle,

        tp.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,

        emp.EMPLOYEEID              AS EmployeeId,
        emp.EMPLOYEECODE            AS EmployeeCode,
        emp.EMPLOYEENAME            AS EmployeeName,

        aa.ATTEMPTNO                AS AttemptNo,
        aa.STARTEDON                AS StartedOn,
        aa.COMPLETEDON              AS CompletedOn,
        aa.TOTALMARKS               AS TotalMarks,
        aa.MARKSOBTAINED            AS MarksObtained,
        aa.SCOREPCT                 AS ScorePct,
        aa.ATTEMPTISPASSED          AS AttemptIsPassed,
        aa.EVALUATEDBYID            AS EvaluatedById,
        evl.EMPLOYEENAME            AS EvaluatedByName,
        aa.ISLMSSYNCED              AS IsLmsSynced,
        aa.REMARKS                  AS Remarks,
        aa.VERSION                  AS Version,
        aa.STATUS                   AS Status,
        aa.SORTORDER                AS SortOrder,
        aa.SOURCETYPE               AS SourceType,
        aa.TENANTID                 AS TenantId,
        aa.CREATEDBYID              AS CreatedById,
        aa.CREATEDON                AS CreatedOn,
        aa.MODIFIEDBYID             AS ModifiedById,
        aa.MODIFIEDON               AS ModifiedOn,

        -- Responses left at ISCORRECT=-1 (Pending) after submit — RequiresManualEvaluation
        -- questions the default evaluator never auto-grades. Surfaced so a trainer can find
        -- attempts actually needing manual grading instead of opening each one to check.
        (SELECT COUNT(1) FROM DBO.TATTEMPTRESPONSE ar
         WHERE ar.ASSESSMENTATTEMPTID = aa.ASSESSMENTATTEMPTID
         AND   ar.ISCORRECT = -1)    AS PendingResponseCount
FROM    DBO.TASSESSMENTATTEMPT aa
INNER JOIN DBO.MSESSIONASSESSMENT sa
        ON sa.ASSESSMENTID = aa.ASSESSMENTID
INNER JOIN DBO.TTRAININGENROLLMENT te
        ON te.ENROLLMENTID = aa.ENROLLMENTID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = te.EMPLOYEEID
INNER JOIN DBO.TTRAININGINSTANCE ti
        ON ti.INSTANCEID = te.INSTANCEID
INNER JOIN DBO.MTRAININGPROGRAMME tp
        ON tp.PROGRAMMEID = ti.PROGRAMMEID
LEFT JOIN DBO.MEMPLOYEE evl
        ON evl.EMPLOYEEID = aa.EVALUATEDBYID
WHERE   aa.TENANTID = @TenantId
ORDER BY aa.STARTEDON DESC";


        // =====================================================
        // GET ATTEMPT COUNT — for AttemptNo calculation
        // =====================================================
        public const string GET_ATTEMPT_COUNT = @"
SELECT  COUNT(1)
FROM    DBO.TASSESSMENTATTEMPT
WHERE   ASSESSMENTID = @AssessmentId
AND     ENROLLMENTID = @EnrollmentId
AND     TENANTID     = @TenantId";


        // =====================================================
        // SAVE
        // =====================================================
        public const string SAVE_ASSESSMENTATTEMPT = @"
INSERT INTO DBO.TASSESSMENTATTEMPT
(
    ASSESSMENTATTEMPTID, ASSESSMENTID, ENROLLMENTID,
    ATTEMPTNO, STARTEDON, COMPLETEDON,
    TOTALMARKS, MARKSOBTAINED, SCOREPCT,
    ATTEMPTISPASSED, EVALUATEDBYID, ISLMSSYNCED, REMARKS, DELIVERYCHANNEL,
    VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @AssessmentAttemptId, @AssessmentId, @EnrollmentId,
    @AttemptNo, @StartedOn, @CompletedOn,
    @TotalMarks, @MarksObtained, @ScorePct,
    @AttemptIsPassed, @EvaluatedById, @IsLmsSynced, @Remarks, @DeliveryChannel,
    @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        // =====================================================
        // UPDATE SCORE — Submit / Evaluate
        // =====================================================
        public const string UPDATE_ASSESSMENTATTEMPT_SCORE = @"
UPDATE  DBO.TASSESSMENTATTEMPT
SET     COMPLETEDON     = @CompletedOn,
        TOTALMARKS      = @TotalMarks,
        MARKSOBTAINED   = @MarksObtained,
        SCOREPCT        = @ScorePct,
        ATTEMPTISPASSED = @AttemptIsPassed,
        EVALUATEDBYID   = @EvaluatedById,
        REMARKS         = @Remarks,
        MODIFIEDBYID    = @ModifiedById,
        MODIFIEDON      = @ModifiedOn
WHERE   ASSESSMENTATTEMPTID = @AssessmentAttemptId
AND     TENANTID            = @TenantId";


        // =====================================================
        // DELETE
        // =====================================================
        public const string DELETE_ASSESSMENTATTEMPT = @"
DELETE FROM DBO.TASSESSMENTATTEMPT
WHERE   ASSESSMENTATTEMPTID = @AssessmentAttemptId
AND     TENANTID            = @TenantId";
    }
}