namespace TMSDAL.Query.Assessment
{
    /// <summary>
    /// Required indexes:
    ///   IX_MSESSIONASSESSMENT_SESSID_TENANTID on (PROGRAMMESESSIONID, TENANTID)
    ///   IX_MASSESSMENTQUESTION_ASSESSMENTID on (ASSESSMENTID)
    ///   IX_MASSESSMENTPAPER_ASSESSMENTID on (ASSESSMENTID)
    ///   IX_MPAPERSECTION_PAPERID on (PAPERID)
    ///   IX_TASSESSMENTATTEMPT_ENROLLMENTID on (ENROLLMENTID)
    ///   IX_TATTEMPTRESPONSE_ATTEMPTID on (ATTEMPTID)
    /// </summary>
    public static class AssessmentQB
    {
        public const string GET_ASSESSMENT = @"
SELECT
    sa.ASSESSMENTID              AS AssessmentId,

    sa.PROGRAMMEID               AS ProgrammeId,
    tp.PROGRAMMECODE             AS ProgrammeCode,
    tp.PROGRAMMETITLE            AS ProgrammeTitle,

    sa.PROGRAMMESESSIONID        AS ProgrammeSessionId,
    ps.SESSIONCODE               AS SessionCode,
    ps.SESSIONTITLE              AS SessionTitle,

    sa.SESSIONTOPICID            AS SessionTopicId,
    st.TOPICCODE                 AS TopicCode,
    st.TOPICTITLE                AS TopicTitle,

    sa.ASSESSMENTTITLE           AS AssessmentTitle,
    sa.ASSESSMENTTYPE            AS AssessmentType,
    sa.ASSESSMENTMODE            AS AssessmentMode,
    sa.ISPREASSESSMENT           AS IsPreAssessment,
    sa.ISPOSTASSESSMENT          AS IsPostAssessment,
    sa.PASSTHRESHOLDPCT          AS PassThresholdPct,
    sa.MAXATTEMPTS               AS MaxAttempts,
    sa.DURATIONMINS              AS DurationMins,
    sa.TOTALMARKS                AS TotalMarks,
    sa.ISLMSDELIVERED            AS IsLmsDelivered,
    sa.LMSASSESSMENTID           AS LmsAssessmentId,
    sa.RANDOMISEQUESTIONS        AS RandomiseQuestions,
    sa.SHOWANSWERSONCOMPLETE     AS ShowAnswersOnComplete,
    sa.ISACTIVE                  AS IsActive,
    sa.TENANTID                  AS TenantId,
    sa.CREATEDBYID               AS CreatedById,
    sa.CREATEDON                 AS CreatedOn,
    sa.MODIFIEDBYID              AS ModifiedById,
    sa.MODIFIEDON                AS ModifiedOn
FROM DBO.MSESSIONASSESSMENT sa
LEFT JOIN DBO.MTRAININGPROGRAMME tp
    ON  tp.PROGRAMMEID = sa.PROGRAMMEID
    AND tp.TENANTID    = sa.TENANTID
LEFT JOIN DBO.MPROGRAMMESESSION ps
    ON  ps.PROGRAMMESESSIONID = sa.PROGRAMMESESSIONID
LEFT JOIN DBO.MSESSIONTOPIC st
    ON  st.SESSIONTOPICID = sa.SESSIONTOPICID
WHERE sa.ASSESSMENTID = @AssessmentId
AND   sa.TENANTID     = @TenantId";

        public const string GET_SIMPLE_QUESTIONS = @"
SELECT  
    ASSESSMENTQUESTIONID AS AssessmentQuestionId,
    ASSESSMENTID         AS AssessmentId,
    SLNO                 AS SlNo,
    QUESTIONTEXT         AS QuestionText,
    QUESTIONTYPE         AS QuestionType,
    OPTIONSJSON          AS OptionsJson,
    CORRECTANSWER        AS CorrectAnswer,
    MARKS                AS Marks,
    EXPLANATION          AS Explanation,
    REQUIRESMANUALEVALUATION AS RequiresManualEvaluation
FROM DBO.MASSESSMENTQUESTION
WHERE ASSESSMENTID = @AssessmentId
ORDER BY SLNO";

        public const string GET_ASSESSMENT_PAPER = @"
SELECT  
    ASSESSMENTPAPERID AS AssessmentPaperId,
    ASSESSMENTID      AS AssessmentId,
    PAPERTITLE        AS PaperTitle,
    SELECTIONMODE     AS SelectionMode,
    TOTALQUESTIONS    AS TotalQuestions,
    DURATIONMINS      AS DurationMins,
    RANDOMISEORDER    AS RandomiseOrder,
    SHOWONEATTIME     AS ShowOneAtTime,
    ALLOWREVIEW       AS AllowReview,
    ISLMSDELIVERED    AS IsLmsDelivered,
    LMSASSESSMENTID   AS LmsAssessmentId
FROM DBO.MASSESSMENTPAPER
WHERE ASSESSMENTID = @AssessmentId";

        public const string GET_PAPER_SECTIONS = @"
SELECT  
    PAPERSECTIONID       AS PaperSectionId,
    ASSESSMENTPAPERID    AS AssessmentPaperId,
    SLNO                 AS SlNo,
    SECTIONTITLE         AS SectionTitle,
    QUESTIONCOUNT        AS QuestionCount,
    QUESTIONTYPEFILTER   AS QuestionTypeFilter,
    DIFFICULTYFILTER     AS DifficultyFilter,
    INDUSTRYTAGFILTER    AS IndustryTagFilter,
    SKILLTAGFILTER       AS SkillTagFilter,
    TOPICTAGFILTER       AS TopicTagFilter,
    MARKSPERQUESTION     AS MarksPerQuestion
FROM DBO.MPAPERSECTION
WHERE ASSESSMENTPAPERID = @AssessmentPaperId
ORDER BY SLNO";

        public const string GET_SECTION_QUESTIONS = @"
            SELECT  QBANKID,
                    SECTIONID,
                    QUESTIONTEXT,
                    QUESTIONTYPE,
                    DIFFICULTY,
                    OPTIONA,
                    OPTIONB,
                    OPTIONC,
                    OPTIOND,
                    CORRECTANSWER,
                    TENANTID
            FROM    DBO.MQUESTIONBANK
            WHERE   SECTIONID = @SectionId
            AND     TENANTID  = @TenantId";

        public const string GET_ATTEMPT_COUNT = @"
            SELECT  COUNT(1)
            FROM    DBO.TASSESSMENTATTEMPT
            WHERE   ASSESSMENTID = @AssessmentId
            AND     ENROLLMENTID = @EnrollmentId
            AND     TENANTID     = @TenantId";

        public const string SAVE_ASSESSMENT = @"
INSERT INTO DBO.MSESSIONASSESSMENT
(
    ASSESSMENTID, PROGRAMMEID, PROGRAMMESESSIONID, SESSIONTOPICID,
    ASSESSMENTTITLE, ASSESSMENTTYPE, ASSESSMENTMODE,
    ISPREASSESSMENT, ISPOSTASSESSMENT,
    PASSTHRESHOLDPCT, MAXATTEMPTS, DURATIONMINS,
    TOTALMARKS, ISLMSDELIVERED, LMSASSESSMENTID,
    RANDOMISEQUESTIONS, SHOWANSWERSONCOMPLETE,
    ISACTIVE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @AssessmentId, @ProgrammeId, @ProgrammeSessionId, @SessionTopicId,
    @AssessmentTitle, @AssessmentType, @AssessmentMode,
    @IsPreAssessment, @IsPostAssessment,
    @PassThresholdPct, @MaxAttempts, @DurationMins,
    @TotalMarks, @IsLmsDelivered, @LmsAssessmentId,
    @RandomiseQuestions, @ShowAnswersOnComplete,
    @IsActive, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";

        public const string UPDATE_ASSESSMENT = @"
UPDATE DBO.MSESSIONASSESSMENT
SET
    ASSESSMENTTITLE       = @AssessmentTitle,
    PROGRAMMEID           = @ProgrammeId,
    PROGRAMMESESSIONID    = @ProgrammeSessionId,
    SESSIONTOPICID        = @SessionTopicId,
    ASSESSMENTTYPE        = @AssessmentType,
    ASSESSMENTMODE        = @AssessmentMode,
    ISPREASSESSMENT       = @IsPreAssessment,
    ISPOSTASSESSMENT      = @IsPostAssessment,
    PASSTHRESHOLDPCT      = @PassThresholdPct,
    MAXATTEMPTS           = @MaxAttempts,
    DURATIONMINS          = @DurationMins,
    TOTALMARKS            = @TotalMarks,
    ISLMSDELIVERED        = @IsLmsDelivered,
    LMSASSESSMENTID       = @LmsAssessmentId,
    RANDOMISEQUESTIONS    = @RandomiseQuestions,
    SHOWANSWERSONCOMPLETE = @ShowAnswersOnComplete,
    ISACTIVE              = @IsActive,
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = @ModifiedOn
WHERE ASSESSMENTID = @AssessmentId
AND   TENANTID     = @TenantId";

        public const string DELETE_ASSESSMENT = @"
            DELETE FROM DBO.MSESSIONASSESSMENT
            WHERE   ASSESSMENTID = @AssessmentId
            AND     TENANTID     = @TenantId";

        public const string SAVE_QUESTION = @"
INSERT INTO DBO.MASSESSMENTQUESTION
(
    ASSESSMENTQUESTIONID, ASSESSMENTID, SLNO,
    QUESTIONTEXT, QUESTIONTYPE,
    OPTIONSJSON, CORRECTANSWER, MARKS, EXPLANATION, REQUIRESMANUALEVALUATION
)
VALUES
(
    @AssessmentQuestionId, @AssessmentId, @SlNo,
    @QuestionText, @QuestionType,
    @OptionsJson, @CorrectAnswer, @Marks, @Explanation, @RequiresManualEvaluation
)";

        public const string DELETE_QUESTIONS = @"
            DELETE FROM DBO.MASSESSMENTQUESTION
            WHERE   ASSESSMENTID = @AssessmentId";

        public const string SAVE_ATTEMPT = @"
            INSERT INTO DBO.TASSESSMENTATTEMPT
                (ATTEMPTID, ASSESSMENTID, ENROLLMENTID, EMPLOYEEID,
                 ATTEMPTNO, STARTEDON, SUBMITTEDON, SCOREPCT, ATTEMPTISPASSED, TENANTID)
            VALUES
                (@AttemptId, @AssessmentId, @EnrollmentId, @EmployeeId,
                 @AttemptNo, @StartedOn, @SubmittedOn, @ScorePct, @AttemptIsPassed, @TenantId)";

        public const string BULK_INSERT_RESPONSES = @"
            INSERT INTO DBO.TATTEMPTRESPONSE
                (RESPONSEID, ATTEMPTID, QUESTIONID, SELECTEDANSWER, ISCORRECT, TENANTID)
            VALUES
                (@ResponseId, @AttemptId, @QuestionId, @SelectedAnswer, @IsCorrect, @TenantId)";

        public const string GET_SELECT_LIST = @"
SELECT
    sa.ASSESSMENTID              AS AssessmentId,
    sa.PROGRAMMEID               AS ProgrammeId,
    tp.PROGRAMMECODE             AS ProgrammeCode,
    tp.PROGRAMMETITLE            AS ProgrammeTitle,
    sa.PROGRAMMESESSIONID        AS ProgrammeSessionId,
    ps.SESSIONCODE               AS SessionCode,
    ps.SESSIONTITLE              AS SessionTitle,
    sa.SESSIONTOPICID            AS SessionTopicId,
    st.TOPICCODE                 AS TopicCode,
    st.TOPICTITLE                AS TopicTitle,
    sa.ASSESSMENTTITLE           AS AssessmentTitle,
    sa.ASSESSMENTTYPE            AS AssessmentType,
    sa.ASSESSMENTMODE            AS AssessmentMode,
    sa.ISPREASSESSMENT           AS IsPreAssessment,
    sa.ISPOSTASSESSMENT          AS IsPostAssessment,
    sa.PASSTHRESHOLDPCT          AS PassThresholdPct,
    sa.MAXATTEMPTS               AS MaxAttempts,
    sa.DURATIONMINS              AS DurationMins,
    sa.TOTALMARKS                AS TotalMarks,
    sa.ISLMSDELIVERED            AS IsLmsDelivered,
    sa.LMSASSESSMENTID           AS LmsAssessmentId,
    sa.RANDOMISEQUESTIONS        AS RandomiseQuestions,
    sa.SHOWANSWERSONCOMPLETE     AS ShowAnswersOnComplete,
    sa.ISACTIVE                  AS IsActive,
    sa.TENANTID                  AS TenantId,
    sa.CREATEDBYID               AS CreatedById,
    sa.CREATEDON                 AS CreatedOn,
    sa.MODIFIEDBYID              AS ModifiedById,
    sa.MODIFIEDON                AS ModifiedOn,
    (SELECT TOP 1 te.ENROLLMENTID
     FROM DBO.TTRAININGINSTANCE ti2
     JOIN DBO.TTRAININGENROLLMENT te
         ON  te.INSTANCEID = ti2.INSTANCEID
         AND te.TENANTID   = ti2.TENANTID
     WHERE ti2.PROGRAMMEID = sa.PROGRAMMEID
     AND   ti2.TENANTID    = sa.TENANTID) AS EnrollmentId
FROM DBO.MSESSIONASSESSMENT sa
LEFT JOIN DBO.MTRAININGPROGRAMME tp
    ON  tp.PROGRAMMEID = sa.PROGRAMMEID
    AND tp.TENANTID    = sa.TENANTID
LEFT JOIN DBO.MPROGRAMMESESSION ps
    ON  ps.PROGRAMMESESSIONID = sa.PROGRAMMESESSIONID
LEFT JOIN DBO.MSESSIONTOPIC st
    ON  st.SESSIONTOPICID = sa.SESSIONTOPICID
WHERE sa.TENANTID = @TenantId
{DYNAMIC_WHERE}
";
    

        public const string GET_ASSESSMENT_LIST = @"
SELECT
    sa.ASSESSMENTID              AS AssessmentId,
    sa.PROGRAMMEID               AS ProgrammeId,
    tp.PROGRAMMECODE             AS ProgrammeCode,
    tp.PROGRAMMETITLE            AS ProgrammeTitle,
    sa.PROGRAMMESESSIONID        AS ProgrammeSessionId,
    ps.SESSIONCODE               AS SessionCode,
    ps.SESSIONTITLE              AS SessionTitle,
    sa.SESSIONTOPICID            AS SessionTopicId,
    st.TOPICCODE                 AS TopicCode,
    st.TOPICTITLE                AS TopicTitle,
    sa.ASSESSMENTTITLE           AS AssessmentTitle,
    sa.ASSESSMENTTYPE            AS AssessmentType,
    sa.ASSESSMENTMODE            AS AssessmentMode,
    sa.ISPREASSESSMENT           AS IsPreAssessment,
    sa.ISPOSTASSESSMENT          AS IsPostAssessment,
    sa.PASSTHRESHOLDPCT          AS PassThresholdPct,
    sa.MAXATTEMPTS               AS MaxAttempts,
    sa.DURATIONMINS              AS DurationMins,
    sa.TOTALMARKS                AS TotalMarks,
    sa.ISLMSDELIVERED            AS IsLmsDelivered,
    sa.LMSASSESSMENTID           AS LmsAssessmentId,
    sa.RANDOMISEQUESTIONS        AS RandomiseQuestions,
    sa.SHOWANSWERSONCOMPLETE     AS ShowAnswersOnComplete,
    sa.ISACTIVE                  AS IsActive,
    sa.TENANTID                  AS TenantId,
    sa.CREATEDBYID               AS CreatedById,
    sa.CREATEDON                 AS CreatedOn,
    sa.MODIFIEDBYID              AS ModifiedById,
    sa.MODIFIEDON                AS ModifiedOn
FROM DBO.MSESSIONASSESSMENT sa
LEFT JOIN DBO.MTRAININGPROGRAMME tp
    ON  tp.PROGRAMMEID = sa.PROGRAMMEID
    AND tp.TENANTID    = sa.TENANTID
LEFT JOIN DBO.MPROGRAMMESESSION ps
    ON  ps.PROGRAMMESESSIONID = sa.PROGRAMMESESSIONID
LEFT JOIN DBO.MSESSIONTOPIC st
    ON  st.SESSIONTOPICID = sa.SESSIONTOPICID
WHERE sa.TENANTID = @TenantId
ORDER BY sa.ASSESSMENTID DESC";
    }
}