namespace TMSDAL.Query.Quiz
{
    /// <summary>
    /// Required indexes:
    ///   IX_MQUIZ_TENANTID on (TENANTID)
    ///   IX_MQUIZ_TOPICID on (TOPICID)
    /// Note: TENANTID = -1 means shared global quiz visible to all tenants.
    /// </summary>
    public static class QuizQB
    {
        public const string GET_QUIZ = @"
SELECT  q.QUIZID                AS QuizId,
        q.QUIZTITLE              AS QuizTitle,
        q.QUIZPURPOSE            AS QuizPurpose,
        q.COURSEID               AS CourseId,
        q.SUBJECTID              AS SubjectId,
        q.TOPICID                AS TopicId,
        q.ISSHARED               AS IsShared,
        q.QUIZMODE               AS QuizMode,
        q.SELECTIONMODE          AS SelectionMode,
        q.TOTALQUESTIONS         AS TotalQuestions,
        q.PASSTHRESHOLDPCT       AS PassThresholdPct,
        q.MAXATTEMPTS            AS MaxAttempts,
        q.TIMELIMITMIN           AS TimeLimitMin,
        q.RANDOMISEQUESTIONS     AS RandomiseQuestions,
        q.SHOWANSWERSONCOMPLETE  AS ShowAnswersOnComplete,
        q.ISANONYMOUS            AS IsAnonymous,
        q.SOURCEMODULEID         AS SourceModuleId,
        q.ISACTIVE               AS IsActive,
        q.VERSION                AS Version,
        q.STATUS                 AS Status,
        q.SORTORDER              AS SortOrder,
        q.SOURCETYPE             AS SourceType,
        q.TENANTID               AS TenantId,
        q.CREATEDBYID            AS CreatedById,
        q.CREATEDON              AS CreatedOn,
        q.MODIFIEDBYID           AS ModifiedById,
        q.MODIFIEDON             AS ModifiedOn
FROM    DBO.MQUIZ q
WHERE   q.QUIZID = @QuizId
AND    (q.TENANTID = @TenantId OR q.TENANTID = -1)";


        public const string GET_SELECTLIST_QUIZ = @"
SELECT  q.QUIZID                AS Id,
        q.QUIZTITLE              AS Name
FROM    DBO.MQUIZ q
WHERE  (q.TENANTID = @TenantId OR q.TENANTID = -1)
AND     q.ISACTIVE = 1
AND    (@TopicId = -1 OR q.TOPICID = @TopicId)
ORDER BY q.SORTORDER, q.QUIZTITLE";


        public const string SAVE_QUIZ = @"
INSERT INTO DBO.MQUIZ
(
    QUIZID, QUIZTITLE, QUIZPURPOSE, COURSEID, SUBJECTID, TOPICID, ISSHARED,
    QUIZMODE, SELECTIONMODE, TOTALQUESTIONS, PASSTHRESHOLDPCT, MAXATTEMPTS, TIMELIMITMIN,
    RANDOMISEQUESTIONS, SHOWANSWERSONCOMPLETE, ISANONYMOUS, SOURCEMODULEID,
    ISACTIVE, VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @QuizId, @QuizTitle, @QuizPurpose, @CourseId, @SubjectId, @TopicId, @IsShared,
    @QuizMode, @SelectionMode, @TotalQuestions, @PassThresholdPct, @MaxAttempts, @TimeLimitMin,
    @RandomiseQuestions, @ShowAnswersOnComplete, @IsAnonymous, @SourceModuleId,
    @IsActive, @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        public const string UPDATE_QUIZ = @"
UPDATE  DBO.MQUIZ
SET     QUIZTITLE              = @QuizTitle,
        QUIZPURPOSE            = @QuizPurpose,
        COURSEID               = @CourseId,
        SUBJECTID              = @SubjectId,
        TOPICID                = @TopicId,
        ISSHARED               = @IsShared,
        QUIZMODE               = @QuizMode,
        SELECTIONMODE          = @SelectionMode,
        TOTALQUESTIONS         = @TotalQuestions,
        PASSTHRESHOLDPCT       = @PassThresholdPct,
        MAXATTEMPTS            = @MaxAttempts,
        TIMELIMITMIN           = @TimeLimitMin,
        RANDOMISEQUESTIONS     = @RandomiseQuestions,
        SHOWANSWERSONCOMPLETE  = @ShowAnswersOnComplete,
        ISANONYMOUS            = @IsAnonymous,
        SOURCEMODULEID         = @SourceModuleId,
        ISACTIVE               = @IsActive,
        VERSION                = @Version,
        STATUS                 = @Status,
        SORTORDER              = @SortOrder,
        MODIFIEDBYID           = @ModifiedById,
        MODIFIEDON             = @ModifiedOn
WHERE   QUIZID = @QuizId
AND    (TENANTID = @TenantId OR TENANTID = -1)";


        public const string DELETE_QUIZ = @"
DELETE FROM DBO.MQUIZ
WHERE   QUIZID   = @QuizId
AND     TENANTID = @TenantId";
    }
}