using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TMSDAL.Query.AttemptResponse
{
    /// <summary>
    /// Required indexes:
    ///   IX_TATTEMPTRESPONSE_ASSESSMENTATTEMPTID on (ASSESSMENTATTEMPTID)
    ///   IX_TATTEMPTRESPONSE_QUESTIONBANKID on (QUESTIONBANKID)
    /// </summary>
    public static class AttemptResponseQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_ATTEMPTRESPONSE = @"
SELECT  ar.ATTEMPTRESPONSEID                            AS AttemptResponseId,
        ar.ASSESSMENTATTEMPTID                          AS AssessmentAttemptId,
        ar.ASSESSMENTQUESTIONID                         AS AssessmentQuestionId,
        ar.QUESTIONBANKID                               AS QuestionBankId,
        qb.QUESTIONTITLE                                AS QuestionTitle,
        COALESCE(aq.QUESTIONTEXT,  qb.QUESTIONTEXT)     AS QuestionText,
        ar.RESPONSEGIVEN                                AS ResponseGiven,
        ar.ISCORRECT                                    AS IsCorrect,
        ar.MARKSAWARDED                                 AS MarksAwarded,
        ar.EVALUATORNOTES                               AS EvaluatorNotes
FROM    DBO.TATTEMPTRESPONSE ar
LEFT JOIN DBO.MASSESSMENTQUESTION aq
        ON aq.ASSESSMENTQUESTIONID = ar.ASSESSMENTQUESTIONID
LEFT JOIN DBO.MQUESTIONBANK qb
        ON qb.QUESTIONBANKID = ar.QUESTIONBANKID
WHERE   ar.ATTEMPTRESPONSEID = @AttemptResponseId";


        // =====================================================
        // GET LIST BY ATTEMPT
        // =====================================================
        public const string GET_ATTEMPTRESPONSE_LIST = @"
SELECT  ar.ATTEMPTRESPONSEID                            AS AttemptResponseId,
        ar.ASSESSMENTATTEMPTID                          AS AssessmentAttemptId,
        ar.ASSESSMENTQUESTIONID                         AS AssessmentQuestionId,
        ar.QUESTIONBANKID                               AS QuestionBankId,
        qb.QUESTIONTITLE                                AS QuestionTitle,
        COALESCE(aq.QUESTIONTEXT,  qb.QUESTIONTEXT)     AS QuestionText,
        ar.RESPONSEGIVEN                                AS ResponseGiven,
        ar.ISCORRECT                                    AS IsCorrect,
        ar.MARKSAWARDED                                 AS MarksAwarded,
        ar.EVALUATORNOTES                               AS EvaluatorNotes
FROM    DBO.TATTEMPTRESPONSE ar
LEFT JOIN DBO.MASSESSMENTQUESTION aq
        ON aq.ASSESSMENTQUESTIONID = ar.ASSESSMENTQUESTIONID
LEFT JOIN DBO.MQUESTIONBANK qb
        ON qb.QUESTIONBANKID = ar.QUESTIONBANKID
WHERE   ar.ASSESSMENTATTEMPTID = @AssessmentAttemptId
ORDER BY ar.ATTEMPTRESPONSEID";


        // =====================================================
        // GET LIST BY ATTEMPT IDs — bulk fetch for list view
        // =====================================================
        public const string GET_ATTEMPTRESPONSE_LIST_BY_ATTEMPT_IDS = @"
SELECT  ar.ATTEMPTRESPONSEID                            AS AttemptResponseId,
        ar.ASSESSMENTATTEMPTID                          AS AssessmentAttemptId,
        ar.ASSESSMENTQUESTIONID                         AS AssessmentQuestionId,
        ar.QUESTIONBANKID                               AS QuestionBankId,
        qb.QUESTIONTITLE                                AS QuestionTitle,
        COALESCE(aq.QUESTIONTEXT,  qb.QUESTIONTEXT)     AS QuestionText,
        ar.RESPONSEGIVEN                                AS ResponseGiven,
        ar.ISCORRECT                                    AS IsCorrect,
        ar.MARKSAWARDED                                 AS MarksAwarded,
        ar.EVALUATORNOTES                               AS EvaluatorNotes
FROM    DBO.TATTEMPTRESPONSE ar
LEFT JOIN DBO.MASSESSMENTQUESTION aq
        ON aq.ASSESSMENTQUESTIONID = ar.ASSESSMENTQUESTIONID
LEFT JOIN DBO.MQUESTIONBANK qb
        ON qb.QUESTIONBANKID = ar.QUESTIONBANKID
WHERE   ar.ASSESSMENTATTEMPTID IN @AssessmentAttemptIds";


        // =====================================================
        // SAVE
        // =====================================================
        public const string SAVE_ATTEMPTRESPONSE = @"
INSERT INTO DBO.TATTEMPTRESPONSE
(
    ATTEMPTRESPONSEID,
    ASSESSMENTATTEMPTID,
    ASSESSMENTQUESTIONID,
    QUESTIONBANKID,
    RESPONSEGIVEN,
    ISCORRECT,
    MARKSAWARDED,
    EVALUATORNOTES
)
VALUES
(
    @AttemptResponseId,
    @AssessmentAttemptId,
    @AssessmentQuestionId,
    @QuestionBankId,
    @ResponseGiven,
    @IsCorrect,
    @MarksAwarded,
    @EvaluatorNotes
)";


        // =====================================================
        // UPDATE — by AttemptResponseId (manual evaluator)
        // =====================================================
        public const string UPDATE_ATTEMPTRESPONSE = @"
UPDATE  DBO.TATTEMPTRESPONSE
SET     ISCORRECT      = @IsCorrect,
        MARKSAWARDED   = @MarksAwarded,
        EVALUATORNOTES = @EvaluatorNotes
WHERE   ATTEMPTRESPONSEID = @AttemptResponseId";


        // =====================================================
        // UPDATE — by AttemptId + QuestionId (auto-evaluation on submit)
        // =====================================================
        public const string UPDATE_ATTEMPTRESPONSE_BY_QUESTION = @"
UPDATE  DBO.TATTEMPTRESPONSE
SET     ISCORRECT      = @IsCorrect,
        MARKSAWARDED   = @MarksAwarded,
        RESPONSEGIVEN  = @ResponseGiven,
        EVALUATORNOTES = @EvaluatorNotes
WHERE   ASSESSMENTATTEMPTID  = @AssessmentAttemptId
AND     ASSESSMENTQUESTIONID = @AssessmentQuestionId";


        // =====================================================
        // UPSERT — INSERT if not exists, UPDATE if exists (submit flow)
        // =====================================================
        public const string UPSERT_ATTEMPTRESPONSE_BY_QUESTION = @"
IF EXISTS (
    SELECT 1 FROM DBO.TATTEMPTRESPONSE
    WHERE  ASSESSMENTATTEMPTID  = @AssessmentAttemptId
    AND    ASSESSMENTQUESTIONID = @AssessmentQuestionId
)
    UPDATE DBO.TATTEMPTRESPONSE
    SET    RESPONSEGIVEN  = @ResponseGiven,
           ISCORRECT      = @IsCorrect,
           MARKSAWARDED   = @MarksAwarded,
           EVALUATORNOTES = @EvaluatorNotes
    WHERE  ASSESSMENTATTEMPTID  = @AssessmentAttemptId
    AND    ASSESSMENTQUESTIONID = @AssessmentQuestionId
ELSE
    INSERT INTO DBO.TATTEMPTRESPONSE
    (
        ATTEMPTRESPONSEID, ASSESSMENTATTEMPTID, ASSESSMENTQUESTIONID,
        QUESTIONBANKID, RESPONSEGIVEN, ISCORRECT, MARKSAWARDED, EVALUATORNOTES
    )
    VALUES
    (
        @AttemptResponseId, @AssessmentAttemptId, @AssessmentQuestionId,
        @QuestionBankId, @ResponseGiven, @IsCorrect, @MarksAwarded, @EvaluatorNotes
    )";


        // =====================================================
        // DELETE BY ATTEMPT — cascade when attempt is deleted
        // =====================================================
        public const string DELETE_ATTEMPTRESPONSES_BY_ATTEMPT = @"
DELETE FROM DBO.TATTEMPTRESPONSE
WHERE   ASSESSMENTATTEMPTID = @AssessmentAttemptId";

        // =====================================================
        // DELETE BY ASSESSMENT — before re-saving questions on update
        // Must run before DELETE_ATTEMPTS_BY_ASSESSMENT and DeleteQuestions
        // =====================================================
        public const string DELETE_ATTEMPTRESPONSES_BY_ASSESSMENT = @"
DELETE ar FROM DBO.TATTEMPTRESPONSE ar
INNER JOIN DBO.TASSESSMENTATTEMPT at2
    ON at2.ASSESSMENTATTEMPTID = ar.ASSESSMENTATTEMPTID
WHERE at2.ASSESSMENTID = @AssessmentId";

        // =====================================================
        // DELETE ATTEMPTS BY ASSESSMENT — before deleting questions
        // =====================================================
        public const string DELETE_ATTEMPTS_BY_ASSESSMENT = @"
DELETE FROM DBO.TASSESSMENTATTEMPT
WHERE ASSESSMENTID = @AssessmentId";
    }
}