namespace TMSDAL.Query.Quiz
{
    /// <summary>
    /// Required indexes:
    ///   IX_TQUIZATTEMPT_QUIZID_EMPLOYEEID on (QUIZID, EMPLOYEEID)
    ///   IX_TQUIZATTEMPT_TENANTID on (TENANTID)
    ///   IX_TQUIZATTEMPTRESPONSE_QUIZATTEMPTID on (QUIZATTEMPTID)
    /// </summary>
    public static class QuizAttemptQB
    {
        public const string GET_QUIZATTEMPT = @"
SELECT  qa.QUIZATTEMPTID    AS QuizAttemptId,
        qa.QUIZID            AS QuizId,
        qa.EMPLOYEEID        AS EmployeeId,
        qa.CONTEXTENTITYTYPE AS ContextEntityType,
        qa.CONTEXTENTITYID   AS ContextEntityId,
        qa.ATTEMPTNO         AS AttemptNo,
        qa.DELIVERYCHANNEL   AS DeliveryChannel,
        qa.STARTEDON         AS StartedOn,
        qa.COMPLETEDON       AS CompletedOn,
        qa.TOTALMARKS        AS TotalMarks,
        qa.MARKSOBTAINED     AS MarksObtained,
        qa.SCOREPCT          AS ScorePct,
        qa.ATTEMPTISPASSED   AS AttemptIsPassed,
        qa.EVALUATEDBYID     AS EvaluatedById,
        qa.REMARKS           AS Remarks,
        qa.VERSION           AS Version,
        qa.STATUS            AS Status,
        qa.SORTORDER         AS SortOrder,
        qa.SOURCETYPE        AS SourceType,
        qa.TENANTID          AS TenantId,
        qa.CREATEDBYID       AS CreatedById,
        qa.CREATEDON         AS CreatedOn,
        qa.MODIFIEDBYID      AS ModifiedById,
        qa.MODIFIEDON        AS ModifiedOn
FROM    DBO.TQUIZATTEMPT qa
WHERE   qa.QUIZATTEMPTID = @QuizAttemptId
AND    (qa.TENANTID = @TenantId OR qa.TENANTID = -1)";


        public const string GET_QUIZATTEMPT_COUNT = @"
SELECT  COUNT(1)
FROM    DBO.TQUIZATTEMPT qa
WHERE   qa.QUIZID     = @QuizId
AND     qa.EMPLOYEEID = @EmployeeId";


        // =====================================================
        // LIST — grid consumption (mirrors AssessmentAttemptQB.GET_ASSESSMENTATTEMPT_FULL_LIST's
        // shape, minus the Enrollment/Instance/Programme joins a standalone quiz has none of).
        // PendingResponseCount counts responses awaiting manual evaluation (ISCORRECT = -1) —
        // only ever non-zero for ShortAnswer/Matching/Ordering question types.
        // =====================================================
        public const string GET_QUIZATTEMPT_FULL_LIST = @"
SELECT  qa.QUIZATTEMPTID    AS QuizAttemptId,
        qa.QUIZID            AS QuizId,
        q.QUIZTITLE           AS QuizTitle,
        qa.EMPLOYEEID        AS EmployeeId,
        emp.EMPLOYEENAME      AS EmployeeName,
        qa.ATTEMPTNO         AS AttemptNo,
        qa.DELIVERYCHANNEL   AS DeliveryChannel,
        qa.STARTEDON         AS StartedOn,
        qa.COMPLETEDON       AS CompletedOn,
        qa.TOTALMARKS        AS TotalMarks,
        qa.MARKSOBTAINED     AS MarksObtained,
        qa.SCOREPCT          AS ScorePct,
        qa.ATTEMPTISPASSED   AS AttemptIsPassed,
        qa.EVALUATEDBYID     AS EvaluatedById,
        evl.EMPLOYEENAME      AS EvaluatedByName,
        qa.REMARKS           AS Remarks,
        (SELECT COUNT(1) FROM DBO.TQUIZATTEMPTRESPONSE r
          WHERE r.QUIZATTEMPTID = qa.QUIZATTEMPTID AND r.ISCORRECT = -1) AS PendingResponseCount
FROM    DBO.TQUIZATTEMPT qa
INNER JOIN DBO.MQUIZ q
        ON q.QUIZID = qa.QUIZID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = qa.EMPLOYEEID
LEFT JOIN DBO.MEMPLOYEE evl
        ON evl.EMPLOYEEID = qa.EVALUATEDBYID
WHERE  (qa.TENANTID = @TenantId OR qa.TENANTID = -1)
ORDER BY qa.STARTEDON DESC";


        public const string GET_QUIZATTEMPT_LIST_BY_EMPLOYEE = @"
SELECT  qa.QUIZATTEMPTID    AS QuizAttemptId,
        qa.QUIZID            AS QuizId,
        q.QUIZTITLE           AS QuizTitle,
        qa.EMPLOYEEID        AS EmployeeId,
        emp.EMPLOYEENAME      AS EmployeeName,
        qa.ATTEMPTNO         AS AttemptNo,
        qa.DELIVERYCHANNEL   AS DeliveryChannel,
        qa.STARTEDON         AS StartedOn,
        qa.COMPLETEDON       AS CompletedOn,
        qa.TOTALMARKS        AS TotalMarks,
        qa.MARKSOBTAINED     AS MarksObtained,
        qa.SCOREPCT          AS ScorePct,
        qa.ATTEMPTISPASSED   AS AttemptIsPassed,
        qa.EVALUATEDBYID     AS EvaluatedById,
        evl.EMPLOYEENAME      AS EvaluatedByName,
        qa.REMARKS           AS Remarks,
        (SELECT COUNT(1) FROM DBO.TQUIZATTEMPTRESPONSE r
          WHERE r.QUIZATTEMPTID = qa.QUIZATTEMPTID AND r.ISCORRECT = -1) AS PendingResponseCount
FROM    DBO.TQUIZATTEMPT qa
INNER JOIN DBO.MQUIZ q
        ON q.QUIZID = qa.QUIZID
INNER JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = qa.EMPLOYEEID
LEFT JOIN DBO.MEMPLOYEE evl
        ON evl.EMPLOYEEID = qa.EVALUATEDBYID
WHERE   qa.EMPLOYEEID = @EmployeeId
AND    (qa.TENANTID = @TenantId OR qa.TENANTID = -1)
ORDER BY qa.STARTEDON DESC";


        public const string SAVE_QUIZATTEMPT = @"
INSERT INTO DBO.TQUIZATTEMPT
(
    QUIZATTEMPTID, QUIZID, EMPLOYEEID, CONTEXTENTITYTYPE, CONTEXTENTITYID, ATTEMPTNO, DELIVERYCHANNEL,
    STARTEDON, COMPLETEDON, TOTALMARKS, MARKSOBTAINED, SCOREPCT, ATTEMPTISPASSED, EVALUATEDBYID, REMARKS,
    VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @QuizAttemptId, @QuizId, @EmployeeId, @ContextEntityType, @ContextEntityId, @AttemptNo, @DeliveryChannel,
    @StartedOn, @CompletedOn, @TotalMarks, @MarksObtained, @ScorePct, @AttemptIsPassed, @EvaluatedById, @Remarks,
    @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        public const string UPDATE_QUIZATTEMPT_SCORE = @"
UPDATE  DBO.TQUIZATTEMPT
SET     COMPLETEDON     = @CompletedOn,
        TOTALMARKS      = @TotalMarks,
        MARKSOBTAINED   = @MarksObtained,
        SCOREPCT        = @ScorePct,
        ATTEMPTISPASSED = @AttemptIsPassed,
        EVALUATEDBYID   = @EvaluatedById,
        REMARKS         = @Remarks,
        MODIFIEDBYID    = @ModifiedById,
        MODIFIEDON      = @ModifiedOn
WHERE   QUIZATTEMPTID = @QuizAttemptId
AND    (TENANTID = @TenantId OR TENANTID = -1)";


        public const string DELETE_QUIZATTEMPT = @"
DELETE FROM DBO.TQUIZATTEMPT
WHERE   QUIZATTEMPTID = @QuizAttemptId
AND     TENANTID       = @TenantId";


        // =====================================================
        // Latest passing attempt — consumed by GB5Framework's IValidationCriteriaEvaluator
        // (VALIDATIONTYPE=Quiz) to check "has this employee passed this quiz within N days."
        // =====================================================
        public const string GET_LATEST_PASSING_ATTEMPT = @"
SELECT TOP (1)
        qa.QUIZATTEMPTID  AS QuizAttemptId,
        qa.COMPLETEDON    AS CompletedOn,
        qa.SCOREPCT       AS ScorePct
FROM    DBO.TQUIZATTEMPT qa
WHERE   qa.QUIZID          = @QuizId
AND     qa.EMPLOYEEID      = @EmployeeId
AND     qa.ATTEMPTISPASSED = 1
AND     qa.COMPLETEDON IS NOT NULL
ORDER BY qa.COMPLETEDON DESC";


        // =====================================================
        // Instant-mode question selection — random pick from MQUESTIONBANK, optionally scoped to a
        // topic. Reuses the same TENANTID/ISACTIVE/TopicId filter shape as
        // TMSDAL.Query.Quiz.QuizSectionQB.GET_QUESTIONS_FOR_QUIZSECTION.
        // =====================================================
        public const string GET_RANDOM_QUESTIONS_BY_TOPIC = @"
SELECT  TOP (@QuestionCount)
        qb.QUESTIONBANKID       AS QuestionBankId,
        qb.QUESTIONTITLE        AS QuestionTitle,
        qb.QUESTIONTEXT         AS QuestionText,
        qb.QUESTIONTYPE         AS QuestionType,
        qb.OPTIONSJSON          AS OptionsJson,
        qb.CORRECTANSWER        AS CorrectAnswer,
        qb.MARKS                AS Marks,
        qb.QBANKDIFFICULTY      AS QBankDifficulty,
        qb.INDUSTRYTAG          AS IndustryTag,
        qb.SKILLTAGS            AS SkillTags,
        qb.TOPICTAGS            AS TopicTags,
        qb.EXPLANATIONTEXT      AS ExplanationText,
        qb.TOPICID              AS TopicId,
        qb.ISACTIVE             AS IsActive,
        qb.TENANTID             AS TenantId
FROM    DBO.MQUESTIONBANK qb
WHERE  (qb.TENANTID = @TenantId OR qb.TENANTID = -1)
AND     qb.ISACTIVE = 1
AND    (@TopicId = -1 OR qb.TOPICID = @TopicId)
ORDER BY NEWID()";


        // =====================================================
        // RESPONSES
        // =====================================================
        // Used both server-side for grading (SubmitQuizAttempt, needs CorrectAnswer) and, via
        // QuizAttemptBLL.GetQuizAttempt, to render the actual questions to the learner while
        // taking the quiz — CorrectAnswer is stripped from that client-facing path in the BLL,
        // never omitted here (this one query serves both, per CLAUDE.md's "avoid duplicating
        // near-identical queries" guidance — the security boundary belongs in the BLL, not SQL).
        public const string GET_RESPONSES_BY_ATTEMPT = @"
SELECT  r.QUIZATTEMPTRESPONSEID AS QuizAttemptResponseId,
        r.QUIZATTEMPTID         AS QuizAttemptId,
        r.QUESTIONBANKID        AS QuestionBankId,
        r.SLNO                  AS SlNo,
        r.GIVENANSWER           AS GivenAnswer,
        r.ISCORRECT             AS IsCorrect,
        r.MARKSAWARDED          AS MarksAwarded,
        qb.QUESTIONTITLE        AS QuestionTitle,
        qb.QUESTIONTEXT         AS QuestionText,
        qb.QUESTIONTYPE         AS QuestionType,
        qb.OPTIONSJSON          AS OptionsJson,
        qb.CORRECTANSWER        AS CorrectAnswer,
        qb.MARKS                AS Marks,
        qb.EXPLANATIONTEXT      AS ExplanationText
FROM    DBO.TQUIZATTEMPTRESPONSE r
INNER JOIN DBO.MQUESTIONBANK qb ON qb.QUESTIONBANKID = r.QUESTIONBANKID
WHERE   r.QUIZATTEMPTID = @QuizAttemptId
ORDER BY r.SLNO";


        public const string SAVE_QUIZATTEMPTRESPONSE = @"
INSERT INTO DBO.TQUIZATTEMPTRESPONSE
(
    QUIZATTEMPTRESPONSEID, QUIZATTEMPTID, QUESTIONBANKID, SLNO, GIVENANSWER, ISCORRECT, MARKSAWARDED
)
VALUES
(
    @QuizAttemptResponseId, @QuizAttemptId, @QuestionBankId, @SlNo, @GivenAnswer, @IsCorrect, @MarksAwarded
)";


        public const string UPDATE_QUIZATTEMPTRESPONSE = @"
UPDATE  DBO.TQUIZATTEMPTRESPONSE
SET     GIVENANSWER  = @GivenAnswer,
        ISCORRECT    = @IsCorrect,
        MARKSAWARDED = @MarksAwarded
WHERE   QUIZATTEMPTRESPONSEID = @QuizAttemptResponseId";
    }
}