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

namespace TMSDAL.Query.PaperSection
{
    /// <summary>
    /// Required indexes:
    ///   IX_MPAPERSECTION_ASSESSMENTPAPERID on (ASSESSMENTPAPERID)
    /// </summary>
    public static class PaperSectionQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_PAPERSECTION = @"
SELECT  ps.PAPERSECTIONID       AS PaperSectionId,
        ps.ASSESSMENTPAPERID    AS AssessmentPaperId,
        ap.PAPERTITLE           AS PaperTitle,
        ps.SLNO                 AS SlNo,
        ps.SECTIONTITLE         AS SectionTitle,
        ps.QUESTIONCOUNT        AS QuestionCount,
        ps.QUESTIONTYPEFILTER   AS QuestionTypeFilter,
        ps.DIFFICULTYFILTER     AS DifficultyFilter,
        ps.INDUSTRYTAGFILTER    AS IndustryTagFilter,
        ps.SKILLTAGFILTER       AS SkillTagFilter,
        ps.TOPICTAGFILTER       AS TopicTagFilter,
        ps.COURSEIDFILTER       AS CourseIdFilter,
        ps.SUBJECTIDFILTER      AS SubjectIdFilter,
        ps.TOPICIDFILTER        AS TopicIdFilter,
        ps.MARKSPERQUESTION     AS MarksPerQuestion
FROM    DBO.MPAPERSECTION ps
INNER JOIN DBO.MASSESSMENTPAPER ap
        ON ap.ASSESSMENTPAPERID = ps.ASSESSMENTPAPERID
WHERE   ps.PAPERSECTIONID = @PaperSectionId";


        // =====================================================
        // GET LIST BY PAPER
        // =====================================================
        public const string GET_PAPERSECTION_LIST = @"
SELECT  ps.PAPERSECTIONID       AS PaperSectionId,
        ps.ASSESSMENTPAPERID    AS AssessmentPaperId,
        ap.PAPERTITLE           AS PaperTitle,
        ps.SLNO                 AS SlNo,
        ps.SECTIONTITLE         AS SectionTitle,
        ps.QUESTIONCOUNT        AS QuestionCount,
        ps.QUESTIONTYPEFILTER   AS QuestionTypeFilter,
        ps.DIFFICULTYFILTER     AS DifficultyFilter,
        ps.INDUSTRYTAGFILTER    AS IndustryTagFilter,
        ps.SKILLTAGFILTER       AS SkillTagFilter,
        ps.TOPICTAGFILTER       AS TopicTagFilter,
        ps.COURSEIDFILTER       AS CourseIdFilter,
        ps.SUBJECTIDFILTER      AS SubjectIdFilter,
        ps.TOPICIDFILTER        AS TopicIdFilter,
        ps.MARKSPERQUESTION     AS MarksPerQuestion
FROM    DBO.MPAPERSECTION ps
INNER JOIN DBO.MASSESSMENTPAPER ap
        ON ap.ASSESSMENTPAPERID = ps.ASSESSMENTPAPERID
WHERE   ps.ASSESSMENTPAPERID = @AssessmentPaperId
ORDER BY ps.SLNO";


        // =====================================================
        // GET SELECTLIST — Criteria Based
        // =====================================================
        public const string GET_SELECTLIST_PAPERSECTION = @"
SELECT  ps.PAPERSECTIONID       AS PaperSectionId,
        ps.ASSESSMENTPAPERID    AS AssessmentPaperId,
        ap.PAPERTITLE           AS PaperTitle,
        ps.SLNO                 AS SlNo,
        ps.SECTIONTITLE         AS SectionTitle
FROM    DBO.MPAPERSECTION ps
INNER JOIN DBO.MASSESSMENTPAPER ap
        ON ap.ASSESSMENTPAPERID = ps.ASSESSMENTPAPERID
WHERE   1 = 1
{DYNAMIC_WHERE}
ORDER BY ps.SLNO";


        // =====================================================
        // SAVE
        // =====================================================
        public const string SAVE_PAPERSECTION = @"
INSERT INTO DBO.MPAPERSECTION
(
    PAPERSECTIONID,
    ASSESSMENTPAPERID,
    SLNO,
    SECTIONTITLE,
    QUESTIONCOUNT,
    QUESTIONTYPEFILTER,
    DIFFICULTYFILTER,
    INDUSTRYTAGFILTER,
    SKILLTAGFILTER,
    TOPICTAGFILTER,
    COURSEIDFILTER,
    SUBJECTIDFILTER,
    TOPICIDFILTER,
    MARKSPERQUESTION
)
VALUES
(
    @PaperSectionId,
    @AssessmentPaperId,
    @SlNo,
    @SectionTitle,
    @QuestionCount,
    @QuestionTypeFilter,
    @DifficultyFilter,
    @IndustryTagFilter,
    @SkillTagFilter,
    @TopicTagFilter,
    @CourseIdFilter,
    @SubjectIdFilter,
    @TopicIdFilter,
    @MarksPerQuestion
)";


        // =====================================================
        // UPDATE
        // =====================================================
        public const string UPDATE_PAPERSECTION = @"
UPDATE  DBO.MPAPERSECTION
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   PAPERSECTIONID = @PaperSectionId";


        // =====================================================
        // DELETE SINGLE
        // =====================================================
        public const string DELETE_PAPERSECTION = @"
DELETE FROM DBO.MPAPERSECTION
WHERE   PAPERSECTIONID = @PaperSectionId";


        // =====================================================
        // DELETE ALL SECTIONS BY PAPER
        // =====================================================
        public const string DELETE_PAPERSECTIONS_BY_PAPER = @"
DELETE FROM DBO.MPAPERSECTION
WHERE   ASSESSMENTPAPERID = @AssessmentPaperId";
    }
}