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

namespace TMSDAL.Query.AssesmentQuestion

{
    /// <summary>
    /// Query builder for MASSESSMENTQUESTION.
    /// Simple mode — questions attached directly to MSESSIONASSESSMENT.
    /// No audit columns (CREATEDBYID / CREATEDON / MODIFIEDBYID / MODIFIEDON)
    /// on this table — confirmed from DDL.
    /// </summary>
    public static class AssessmentQuestionQB
    {
        // ── GET single ────────────────────────────────────────────────────────
        public const string GET_ASSESSMENT_QUESTION = @"
            SELECT
                Q.ASSESSMENTQUESTIONID  AS AssessmentQuestionId,
                Q.ASSESSMENTID          AS AssessmentId,
                A.ASSESSMENTTITLE       AS AssessmentTitle,
                Q.SLNO                  AS SlNo,
                Q.QUESTIONTEXT          AS QuestionText,
                Q.QUESTIONTYPE          AS QuestionType,
                Q.OPTIONSJSON           AS OptionsJson,
                Q.CORRECTANSWER         AS CorrectAnswer,
                Q.MARKS                 AS Marks,
                Q.EXPLANATION           AS Explanation,
                Q.REQUIRESMANUALEVALUATION AS RequiresManualEvaluation
            FROM MASSESSMENTQUESTION Q
            INNER JOIN MSESSIONASSESSMENT A
                ON A.ASSESSMENTID = Q.ASSESSMENTID
            WHERE Q.ASSESSMENTQUESTIONID = @AssessmentQuestionId
              AND A.STATUS <> 2;";

        // ── GET all questions for one assessment (ordered by SLNO) ────────────
        public const string GET_ASSESSMENT_QUESTION_LIST = @"
            SELECT
                Q.ASSESSMENTQUESTIONID  AS AssessmentQuestionId,
                Q.ASSESSMENTID          AS AssessmentId,
                A.ASSESSMENTTITLE       AS AssessmentTitle,
                Q.SLNO                  AS SlNo,
                Q.QUESTIONTEXT          AS QuestionText,
                Q.QUESTIONTYPE          AS QuestionType,
                Q.OPTIONSJSON           AS OptionsJson,
                Q.CORRECTANSWER         AS CorrectAnswer,
                Q.MARKS                 AS Marks,
                Q.EXPLANATION           AS Explanation,
                Q.REQUIRESMANUALEVALUATION AS RequiresManualEvaluation
            FROM MASSESSMENTQUESTION Q
            INNER JOIN MSESSIONASSESSMENT A
                ON A.ASSESSMENTID = Q.ASSESSMENTID
            WHERE A.STATUS <> 2
            ORDER BY Q.ASSESSMENTID, Q.SLNO;";

        // ── GET select list (id + text for dropdowns, filtered by AssessmentId) ─
        public const string GET_SELECTLIST_ASSESSMENT_QUESTION = @"
            SELECT
                Q.ASSESSMENTQUESTIONID  AS Id,
                Q.QUESTIONTEXT          AS Text,
                Q.QUESTIONTYPE          AS QuestionType,
                Q.MARKS                 AS Marks,
                Q.SLNO                  AS SlNo
            FROM MASSESSMENTQUESTION Q
            INNER JOIN MSESSIONASSESSMENT A
                ON A.ASSESSMENTID = Q.ASSESSMENTID
            WHERE A.STATUS <> 2
            {DYNAMIC_WHERE}
            ORDER BY Q.SLNO;";

        // ── INSERT ────────────────────────────────────────────────────────────
        // No audit columns on this table — DDL confirmed.
        // SLNO defaults to 1 in DB but is always set explicitly by BLL.
        public const string SAVE_ASSESSMENT_QUESTION = @"
            INSERT INTO MASSESSMENTQUESTION
                (ASSESSMENTQUESTIONID,
                 ASSESSMENTID,
                 SLNO,
                 QUESTIONTEXT,
                 QUESTIONTYPE,
                 OPTIONSJSON,
                 CORRECTANSWER,
                 MARKS,
                 EXPLANATION,
                 REQUIRESMANUALEVALUATION)
            VALUES
                (@AssessmentQuestionId,
                 @AssessmentId,
                 @SlNo,
                 @QuestionText,
                 @QuestionType,
                 @OptionsJson,
                 @CorrectAnswer,
                 @Marks,
                 @Explanation,
                 @RequiresManualEvaluation);";

        // ── UPDATE ────────────────────────────────────────────────────────────
        // AssessmentId (parent FK) and AssessmentQuestionId (PK) are immutable.
        // All business columns are updatable.
        public const string UPDATE_ASSESSMENT_QUESTION = @"
            UPDATE MASSESSMENTQUESTION SET
                SLNO            = @SlNo,
                QUESTIONTEXT    = @QuestionText,
                QUESTIONTYPE    = @QuestionType,
                OPTIONSJSON     = @OptionsJson,
                CORRECTANSWER   = @CorrectAnswer,
                MARKS           = @Marks,
                EXPLANATION     = @Explanation,
                REQUIRESMANUALEVALUATION = @RequiresManualEvaluation
            WHERE ASSESSMENTQUESTIONID = @AssessmentQuestionId;";

        // ── DELETE (hard delete — no STATUS column on this table) ─────────────
        // MASSESSMENTQUESTION has no STATUS column (confirmed from DDL).
        // Hard delete is correct here.
        // Parent assessment soft-delete (STATUS=2) cascades logically via BLL.
        public const string DELETE_ASSESSMENT_QUESTION = @"
            DELETE FROM MASSESSMENTQUESTION
            WHERE ASSESSMENTQUESTIONID = @AssessmentQuestionId;";
    }
}