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

namespace TMSDAL.Query.QuestionBank
{
    /// <summary>
    /// Required indexes:
    ///   IX_MQUESTIONBANK_TENANTID on (TENANTID)
    ///   IX_MQUESTIONBANK_ISACTIVE_TENANTID on (ISACTIVE, TENANTID)
    ///   IX_MQUESTIONBANK_QUESTIONTYPE on (QUESTIONTYPE)
    ///   IX_MQUESTIONBANK_QBANKDIFFICULTY on (QBANKDIFFICULTY)
    /// Note: TENANTID = -1 means shared global questions visible to all tenants.
    /// </summary>
    public static class QuestionBankQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_QUESTIONBANK = @"
SELECT  qb.QUESTIONBANKID       AS QuestionBankId,
        qb.ISSHARED             AS IsShared,
        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.COURSEID             AS CourseId,
        qb.SUBJECTID            AS SubjectId,
        qb.TOPICID              AS TopicId,
        qb.USAGECOUNT           AS UsageCount,
        qb.ISACTIVE             AS IsActive,
        qb.VERSION              AS Version,
        qb.STATUS               AS Status,
        qb.SORTORDER            AS SortOrder,
        qb.SOURCETYPE           AS SourceType,
        qb.TENANTID             AS TenantId,
        qb.CREATEDBYID          AS CreatedById,
        qb.CREATEDON            AS CreatedOn,
        qb.MODIFIEDBYID         AS ModifiedById,
        qb.MODIFIEDON           AS ModifiedOn
FROM    DBO.MQUESTIONBANK qb
WHERE   qb.QUESTIONBANKID = @QuestionBankId
AND    (qb.TENANTID = @TenantId OR qb.TENANTID = -1)";


        // =====================================================
        // GET LIST
        // =====================================================
        public const string GET_QUESTIONBANK_LIST = @"
SELECT  qb.QUESTIONBANKID       AS QuestionBankId,
        qb.ISSHARED             AS IsShared,
        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.COURSEID             AS CourseId,
        qb.SUBJECTID            AS SubjectId,
        qb.TOPICID              AS TopicId,
        qb.USAGECOUNT           AS UsageCount,
        qb.ISACTIVE             AS IsActive,
        qb.VERSION              AS Version,
        qb.STATUS               AS Status,
        qb.SORTORDER            AS SortOrder,
        qb.SOURCETYPE           AS SourceType,
        qb.TENANTID             AS TenantId,
        qb.CREATEDBYID          AS CreatedById,
        qb.CREATEDON            AS CreatedOn,
        qb.MODIFIEDBYID         AS ModifiedById,
        qb.MODIFIEDON           AS ModifiedOn
FROM    DBO.MQUESTIONBANK qb
WHERE  (qb.TENANTID = @TenantId OR qb.TENANTID = -1)
AND     qb.ISACTIVE = 1
ORDER BY qb.SORTORDER, qb.QUESTIONTITLE";


        // =====================================================
        // GET SELECTLIST — Criteria Based
        // =====================================================
        public const string GET_SELECTLIST_QUESTIONBANK = @"
SELECT  qb.QUESTIONBANKID       AS QuestionBankId,
        qb.QUESTIONTITLE        AS QuestionTitle,
        qb.QUESTIONTYPE         AS QuestionType,
        qb.QBANKDIFFICULTY      AS QBankDifficulty,
        qb.MARKS                AS Marks,
        qb.ISSHARED             AS IsShared
FROM    DBO.MQUESTIONBANK qb
WHERE  (qb.TENANTID = @TenantId OR qb.TENANTID = -1)
AND     qb.ISACTIVE = 1
{DYNAMIC_WHERE}
ORDER BY qb.SORTORDER, qb.QUESTIONTITLE";


        // =====================================================
        // GET QUESTIONS FOR PAPER SECTION
        // Filters by QuestionType, Difficulty, Tags matching section criteria
        // =====================================================
        public const string GET_QUESTIONS_FOR_SECTION = @"
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()";


        // =====================================================
        // SAVE
        // =====================================================
        public const string SAVE_QUESTIONBANK = @"
INSERT INTO DBO.MQUESTIONBANK
(
    QUESTIONBANKID,
    ISSHARED,
    QUESTIONTITLE,
    QUESTIONTEXT,
    QUESTIONTYPE,
    OPTIONSJSON,
    CORRECTANSWER,
    MARKS,
    QBANKDIFFICULTY,
    INDUSTRYTAG,
    SKILLTAGS,
    TOPICTAGS,
    EXPLANATIONTEXT,
    COURSEID,
    SUBJECTID,
    TOPICID,
    USAGECOUNT,
    ISACTIVE,
    VERSION,
    STATUS,
    SORTORDER,
    SOURCETYPE,
    TENANTID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @QuestionBankId,
    @IsShared,
    @QuestionTitle,
    @QuestionText,
    @QuestionType,
    @OptionsJson,
    @CorrectAnswer,
    @Marks,
    @QBankDifficulty,
    @IndustryTag,
    @SkillTags,
    @TopicTags,
    @ExplanationText,
    @CourseId,
    @SubjectId,
    @TopicId,
    @UsageCount,
    @IsActive,
    @Version,
    @Status,
    @SortOrder,
    @SourceType,
    @TenantId,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn
)";


        // =====================================================
        // UPDATE
        // =====================================================
        public const string UPDATE_QUESTIONBANK = @"
UPDATE  DBO.MQUESTIONBANK
SET     ISSHARED         = @IsShared,
        QUESTIONTITLE    = @QuestionTitle,
        QUESTIONTEXT     = @QuestionText,
        QUESTIONTYPE     = @QuestionType,
        OPTIONSJSON      = @OptionsJson,
        CORRECTANSWER    = @CorrectAnswer,
        MARKS            = @Marks,
        QBANKDIFFICULTY  = @QBankDifficulty,
        INDUSTRYTAG      = @IndustryTag,
        SKILLTAGS        = @SkillTags,
        TOPICTAGS        = @TopicTags,
        EXPLANATIONTEXT  = @ExplanationText,
        COURSEID         = @CourseId,
        SUBJECTID        = @SubjectId,
        TOPICID          = @TopicId,
        ISACTIVE         = @IsActive,
        VERSION          = @Version,
        STATUS           = @Status,
        SORTORDER        = @SortOrder,
        MODIFIEDBYID     = @ModifiedById,
        MODIFIEDON       = @ModifiedOn
WHERE   QUESTIONBANKID = @QuestionBankId
AND    (TENANTID = @TenantId OR TENANTID = -1)";


        // =====================================================
        // DELETE
        // =====================================================
        public const string DELETE_QUESTIONBANK = @"
DELETE FROM DBO.MQUESTIONBANK
WHERE   QUESTIONBANKID = @QuestionBankId
AND     TENANTID       = @TenantId";


        // =====================================================
        // INCREMENT USAGE COUNT
        // Called when a question is used in an assessment attempt
        // =====================================================
        public const string INCREMENT_USAGE_COUNT = @"
UPDATE  DBO.MQUESTIONBANK
SET     USAGECOUNT = USAGECOUNT + 1
WHERE   QUESTIONBANKID = @QuestionBankId";
    }
}