namespace TMSDAL.Query.EvaluationTemplate
{
    /// <summary>
    /// Required indexes:
    ///   IX_MEVALUATIONTEMPLATE_EVALUATIONPLANID on (EVALUATIONPLANID)
    /// </summary>
    public static class EvaluationTemplateQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_EVALUATIONTEMPLATE = @"
SELECT  et.EVALUATIONTEMPLATEID AS EvaluationTemplateId,
        et.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PLANTITLE            AS PlanTitle,
        et.TEMPLATETITLE        AS TemplateTitle,
        et.TEMPLATEVERSIONNO    AS TemplateVersionNo,
        et.QUESTIONSJSON        AS QuestionsJson,
        et.INTROTEXT            AS IntroText,
        et.ISACTIVE             AS IsActive
FROM    DBO.MEVALUATIONTEMPLATE et
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = et.EVALUATIONPLANID
WHERE   et.EVALUATIONTEMPLATEID = @EvaluationTemplateId";


        // =====================================================
        // GET LIST BY PLAN
        // =====================================================
        public const string GET_EVALUATIONTEMPLATE_LIST = @"
SELECT  et.EVALUATIONTEMPLATEID AS EvaluationTemplateId,
        et.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PLANTITLE            AS PlanTitle,
        et.TEMPLATETITLE        AS TemplateTitle,
        et.TEMPLATEVERSIONNO    AS TemplateVersionNo,
        et.QUESTIONSJSON        AS QuestionsJson,
        et.INTROTEXT            AS IntroText,
        et.ISACTIVE             AS IsActive
FROM    DBO.MEVALUATIONTEMPLATE et
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = et.EVALUATIONPLANID
WHERE   et.EVALUATIONPLANID = @EvaluationPlanId
ORDER BY et.TEMPLATETITLE";


        // =====================================================
        // GET LIST BY PLAN IDs (for bulk fetch)
        // =====================================================
        public const string GET_EVALUATIONTEMPLATE_LIST_BY_PLAN_IDS = @"
SELECT  et.EVALUATIONTEMPLATEID AS EvaluationTemplateId,
        et.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PLANTITLE            AS PlanTitle,
        et.TEMPLATETITLE        AS TemplateTitle,
        et.TEMPLATEVERSIONNO    AS TemplateVersionNo,
        et.QUESTIONSJSON        AS QuestionsJson,
        et.INTROTEXT            AS IntroText,
        et.ISACTIVE             AS IsActive
FROM    DBO.MEVALUATIONTEMPLATE et
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = et.EVALUATIONPLANID
WHERE   et.EVALUATIONPLANID IN @EvaluationPlanIds
ORDER BY et.TEMPLATETITLE";


        // =====================================================
        // GET SELECTLIST — Criteria Based
        // =====================================================
        public const string GET_SELECTLIST_EVALUATIONTEMPLATE = @"
SELECT  et.EVALUATIONTEMPLATEID AS EvaluationTemplateId,
        et.EVALUATIONPLANID     AS EvaluationPlanId,
        ep.PLANTITLE            AS PlanTitle,
        et.TEMPLATETITLE        AS TemplateTitle,
        et.TEMPLATEVERSIONNO    AS TemplateVersionNo,
        et.ISACTIVE             AS IsActive
FROM    DBO.MEVALUATIONTEMPLATE et
INNER JOIN DBO.MEVALUATIONPLAN ep
        ON ep.EVALUATIONPLANID = et.EVALUATIONPLANID
WHERE   ep.TENANTID = @TenantId
AND     et.ISACTIVE = 1
{DYNAMIC_WHERE}
ORDER BY et.TEMPLATETITLE";


        // =====================================================
        // SAVE
        // =====================================================
        public const string SAVE_EVALUATIONTEMPLATE = @"
INSERT INTO DBO.MEVALUATIONTEMPLATE
(
    EVALUATIONTEMPLATEID,
    EVALUATIONPLANID,
    TEMPLATETITLE,
    TEMPLATEVERSIONNO,
    QUESTIONSJSON,
    INTROTEXT,
    ISACTIVE
)
VALUES
(
    @EvaluationTemplateId,
    @EvaluationPlanId,
    @TemplateTitle,
    @TemplateVersionNo,
    @QuestionsJson,
    @IntroText,
    @IsActive
)";


        // =====================================================
        // UPDATE
        // =====================================================
        public const string UPDATE_EVALUATIONTEMPLATE = @"
UPDATE  DBO.MEVALUATIONTEMPLATE
SET     TEMPLATETITLE     = @TemplateTitle,
        TEMPLATEVERSIONNO = @TemplateVersionNo,
        QUESTIONSJSON     = @QuestionsJson,
        INTROTEXT         = @IntroText,
        ISACTIVE          = @IsActive
WHERE   EVALUATIONTEMPLATEID = @EvaluationTemplateId";


        // =====================================================
        // DELETE SINGLE
        // =====================================================
        public const string DELETE_EVALUATIONTEMPLATE = @"
DELETE FROM DBO.MEVALUATIONTEMPLATE
WHERE   EVALUATIONTEMPLATEID = @EvaluationTemplateId";


        // =====================================================
        // DELETE ALL BY PLAN
        // =====================================================
        public const string DELETE_EVALUATIONTEMPLATES_BY_PLAN = @"
DELETE FROM DBO.MEVALUATIONTEMPLATE
WHERE   EVALUATIONPLANID = @EvaluationPlanId";
    }
}