namespace TMSDAL.Query.Quiz
{
    /// <summary>
    /// Required indexes:
    ///   IX_MQUIZSECTION_QUIZID on (QUIZID)
    /// </summary>
    public static class QuizSectionQB
    {
        public const string GET_QUIZSECTION = @"
SELECT  qs.QUIZSECTIONID   AS QuizSectionId,
        qs.QUIZID           AS QuizId,
        qs.SLNO              AS SlNo,
        qs.SECTIONTITLE      AS SectionTitle,
        qs.QUESTIONCOUNT     AS QuestionCount,
        qs.QUESTIONTYPEFILTER AS QuestionTypeFilter,
        qs.DIFFICULTYFILTER  AS DifficultyFilter,
        qs.INDUSTRYTAGFILTER AS IndustryTagFilter,
        qs.SKILLTAGFILTER    AS SkillTagFilter,
        qs.TOPICTAGFILTER    AS TopicTagFilter,
        qs.COURSEIDFILTER    AS CourseIdFilter,
        qs.SUBJECTIDFILTER   AS SubjectIdFilter,
        qs.TOPICIDFILTER     AS TopicIdFilter,
        qs.MARKSPERQUESTION  AS MarksPerQuestion
FROM    DBO.MQUIZSECTION qs
WHERE   qs.QUIZSECTIONID = @QuizSectionId";


        public const string GET_QUIZSECTION_LIST = @"
SELECT  qs.QUIZSECTIONID   AS QuizSectionId,
        qs.QUIZID           AS QuizId,
        qs.SLNO              AS SlNo,
        qs.SECTIONTITLE      AS SectionTitle,
        qs.QUESTIONCOUNT     AS QuestionCount,
        qs.QUESTIONTYPEFILTER AS QuestionTypeFilter,
        qs.DIFFICULTYFILTER  AS DifficultyFilter,
        qs.INDUSTRYTAGFILTER AS IndustryTagFilter,
        qs.SKILLTAGFILTER    AS SkillTagFilter,
        qs.TOPICTAGFILTER    AS TopicTagFilter,
        qs.COURSEIDFILTER    AS CourseIdFilter,
        qs.SUBJECTIDFILTER   AS SubjectIdFilter,
        qs.TOPICIDFILTER     AS TopicIdFilter,
        qs.MARKSPERQUESTION  AS MarksPerQuestion
FROM    DBO.MQUIZSECTION qs
WHERE   qs.QUIZID = @QuizId
ORDER BY qs.SLNO";


        public const string SAVE_QUIZSECTION = @"
INSERT INTO DBO.MQUIZSECTION
(
    QUIZSECTIONID, QUIZID, SLNO, SECTIONTITLE, QUESTIONCOUNT,
    QUESTIONTYPEFILTER, DIFFICULTYFILTER, INDUSTRYTAGFILTER, SKILLTAGFILTER, TOPICTAGFILTER,
    COURSEIDFILTER, SUBJECTIDFILTER, TOPICIDFILTER, MARKSPERQUESTION
)
VALUES
(
    @QuizSectionId, @QuizId, @SlNo, @SectionTitle, @QuestionCount,
    @QuestionTypeFilter, @DifficultyFilter, @IndustryTagFilter, @SkillTagFilter, @TopicTagFilter,
    @CourseIdFilter, @SubjectIdFilter, @TopicIdFilter, @MarksPerQuestion
)";


        public const string UPDATE_QUIZSECTION = @"
UPDATE  DBO.MQUIZSECTION
SET     SLNO                = @SlNo,
        SECTIONTITLE        = @SectionTitle,
        QUESTIONCOUNT       = @QuestionCount,
        QUESTIONTYPEFILTER  = @QuestionTypeFilter,
        DIFFICULTYFILTER    = @DifficultyFilter,
        INDUSTRYTAGFILTER   = @IndustryTagFilter,
        SKILLTAGFILTER      = @SkillTagFilter,
        TOPICTAGFILTER      = @TopicTagFilter,
        COURSEIDFILTER      = @CourseIdFilter,
        SUBJECTIDFILTER     = @SubjectIdFilter,
        TOPICIDFILTER       = @TopicIdFilter,
        MARKSPERQUESTION    = @MarksPerQuestion
WHERE   QUIZSECTIONID = @QuizSectionId";


        public const string DELETE_QUIZSECTION = @"
DELETE FROM DBO.MQUIZSECTION
WHERE   QUIZSECTIONID = @QuizSectionId";


        public const string DELETE_QUIZSECTIONS_BY_QUIZ = @"
DELETE FROM DBO.MQUIZSECTION
WHERE   QUIZID = @QuizId";


        // =====================================================
        // GET QUESTIONS FOR A SECTION — random selection, mirrors TMSDAL.Query.QuestionBank.
        // QuestionBankQB.GET_QUESTIONS_FOR_SECTION exactly, adding the same TopicId dual-read filter.
        // =====================================================
        public const string GET_QUESTIONS_FOR_QUIZSECTION = @"
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    (@QuestionTypeFilter = -1 OR qb.QUESTIONTYPE    = @QuestionTypeFilter)
AND    (@DifficultyFilter   = -1 OR qb.QBANKDIFFICULTY = @DifficultyFilter)
AND    (@IndustryTagFilter IS NULL OR qb.INDUSTRYTAG   = @IndustryTagFilter)
AND    (@TopicIdFilter      = -1 OR qb.TOPICID         = @TopicIdFilter)
ORDER BY NEWID()";
    }
}