using static GB5Shared.DTO.GOP.GBGatewayDTO;

namespace TMSDAL.Query.TrainingProgramme
{
    /// <summary>
    /// Required indexes:
    ///   IX_MTRAININGPROGRAMME_ISACTIVE_TENANTID on (ISACTIVE, TENANTID)
    ///   IX_MTRAININGPROGRAMME_DOMAIN_TENANTID on (TRAININGDOMAINID, TENANTID)
    ///   IX_MPROGRAMMESESSION_PROGRAMMEID on (PROGRAMMEID)
    ///   IX_MSESSIONTOPIC_PROGRAMMESESSIONID on (PROGRAMMESESSIONID)
    /// </summary>
    public static class TrainingProgrammeQB
    {


        // =====================================================
        // GET PROGRAMME
        // =====================================================
        public const string GET_PROGRAMME = @"
SELECT  tp.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,

        tp.SHORTDESCRIPTION         AS ShortDescription,
        tp.FULLDESCRIPTION          AS FullDescription,

        tp.TRAININGDOMAINID         AS TrainingDomainId,
        td.DOMAINCODE               AS DomainCode,
        td.DOMAINNAME               AS DomainName,

        tp.PROGRAMMETYPE            AS ProgrammeType,
        tp.DELIVERYMODE             AS DeliveryMode,
        tp.COMPLEXITYLEVEL          AS ComplexityLevel,
        tp.DIFFICULTYLEVEL          AS DifficultyLevel,
        tp.PROVIDERTYPE             AS ProviderType,
        tp.PROGRAMMELEVEL           AS ProgrammeLevel,

        tp.DURATIONHOURS            AS DurationHours,
        tp.PASSMARK                 AS PassMark,
        tp.ISREFRESHER              AS IsRefresher,
        tp.HASASSESSMENT            AS HasAssessment,
        tp.HASPREASSESSMENT         AS HasPreAssessment,
        tp.CERTONCOMPLETE           AS CertOnComplete,

        tp.CERTVALIDITYDAYS         AS CertValidityDays,
        tp.RENEWALWINDOWDAYS        AS RenewalWindowDays,

        tp.MINCAPACITY              AS MinCapacity,
        tp.MAXCAPACITY              AS MaxCapacity,

        tp.COSTPERPARTICIPANT       AS CostPerParticipant,

        tp.LEARNINGOBJECTIVES       AS LearningObjectives,
        tp.TARGETAUDIENCE           AS TargetAudience,
        tp.PREREQUISITES            AS Prerequisites,

        tp.PROGRAMMEVERSIONNO       AS ProgrammeVersionNo,

        tp.ISACTIVE                 AS IsActive,

        tp.VERSION                  AS Version,
        tp.STATUS                   AS Status,
        tp.SORTORDER                AS SortOrder,

        tp.SOURCETYPE               AS SourceType,

        tp.TENANTID                 AS TenantId,
        tp.CREATEDBYID              AS CreatedById,
        tp.CREATEDON                AS CreatedOn,
        tp.MODIFIEDBYID             AS ModifiedById,
        tp.MODIFIEDON               AS ModifiedOn

FROM    DBO.MTRAININGPROGRAMME tp
LEFT JOIN DBO.MTRAININGDOMAIN td
       ON td.TRAININGDOMAINID = tp.TRAININGDOMAINID
      AND td.TENANTID = tp.TENANTID

WHERE   tp.PROGRAMMEID = @ProgrammeId
AND     tp.TENANTID    = @TenantId";


        // =====================================================
        // GET SESSIONS
        // =====================================================
        public const string GET_PROGRAMME_SESSIONS = @"
SELECT  ps.PROGRAMMESESSIONID     AS ProgrammeSessionId,
        ps.PROGRAMMEID            AS ProgrammeId,
        ps.SLNO                   AS SlNo,

        ps.SESSIONCODE            AS SessionCode,
        ps.SESSIONTITLE           AS SessionTitle,

        ps.SESSIONTYPE            AS SessionType,
        ps.SESSIONDELIVMODE       AS SessionDelivMode,

        ps.DURATIONHOURS          AS DurationHours,

        ps.ISOPTIONAL             AS IsOptional,
        ps.ISMANDATORYFORCERT     AS IsMandatoryForCert,
        ps.ISASSESSMENTREQUIRED   AS IsAssessmentRequired,

        ps.MINATTENDANCEPCT       AS MinAttendancePct,

        ps.DELIVERYNOTE           AS DeliveryNote,
        ps.DOJOSTATIONREF         AS DojoStationRef,

        ps.ISACTIVE               AS IsActive,

        ps.TENANTID               AS TenantId

FROM    DBO.MPROGRAMMESESSION ps
WHERE   ps.PROGRAMMEID = @ProgrammeId
AND     ps.TENANTID    = @TenantId
ORDER BY ps.SLNO";


        public const string GET_PROGRAMME_SESSIONS_LIST = @"
SELECT  ps.PROGRAMMESESSIONID     AS ProgrammeSessionId,
        ps.PROGRAMMEID            AS ProgrammeId,
        ps.SLNO                   AS SlNo,

        ps.SESSIONCODE            AS SessionCode,
        ps.SESSIONTITLE           AS SessionTitle,

        ps.SESSIONTYPE            AS SessionType,
        ps.SESSIONDELIVMODE       AS SessionDelivMode,

        ps.DURATIONHOURS          AS DurationHours,

        ps.ISOPTIONAL             AS IsOptional,
        ps.ISMANDATORYFORCERT     AS IsMandatoryForCert,
        ps.ISASSESSMENTREQUIRED   AS IsAssessmentRequired,

        ps.MINATTENDANCEPCT       AS MinAttendancePct,

        ps.DELIVERYNOTE           AS DeliveryNote,
        ps.DOJOSTATIONREF         AS DojoStationRef,

        ps.ISACTIVE               AS IsActive,

        ps.TENANTID               AS TenantId

FROM    DBO.MPROGRAMMESESSION ps
WHERE   ps.PROGRAMMEID IN @ProgrammeIds
AND     ps.TENANTID    = @TenantId
ORDER BY ps.SLNO";


        // =====================================================
        // GET TOPICS
        // =====================================================

        public const string GET_SESSION_TOPICS = @"
SELECT  st.SESSIONTOPICID           AS SessionTopicId,
        st.PROGRAMMESESSIONID       AS ProgrammeSessionId,
        ps.SESSIONCODE              AS SessionCode,
        ps.SESSIONTITLE             AS SessionTitle,
        st.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        st.SLNO                     AS SlNo,
        st.TOPICCODE                AS TopicCode,
        st.TOPICTITLE               AS TopicTitle,
        st.TOPICTYPE                AS TopicType,
        st.DURATIONHOURS            AS DurationHours,
        st.ISOPTIONAL               AS IsOptional,
        st.CONTENTSUMMARY           AS ContentSummary,
        st.LMSCONTENTID             AS LmsContentId,
        st.LMSCOURSEID              AS LmsCourseId,
        st.ISACTIVE                 AS IsActive
FROM    DBO.MSESSIONTOPIC st
LEFT JOIN DBO.MPROGRAMMESESSION ps
       ON ps.PROGRAMMESESSIONID = st.PROGRAMMESESSIONID
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = st.PROGRAMMEID
WHERE   st.PROGRAMMESESSIONID = @ProgrammeSessionId
ORDER BY st.SLNO";


        public const string GET_SESSION_TOPICS_LIST = @"
SELECT  st.SESSIONTOPICID           AS SessionTopicId,
        st.PROGRAMMESESSIONID       AS ProgrammeSessionId,
        ps.SESSIONCODE              AS SessionCode,
        ps.SESSIONTITLE             AS SessionTitle,
        st.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        st.SLNO                     AS SlNo,
        st.TOPICCODE                AS TopicCode,
        st.TOPICTITLE               AS TopicTitle,
        st.TOPICTYPE                AS TopicType,
        st.DURATIONHOURS            AS DurationHours,
        st.ISOPTIONAL               AS IsOptional,
        st.CONTENTSUMMARY           AS ContentSummary,
        st.LMSCONTENTID             AS LmsContentId,
        st.LMSCOURSEID              AS LmsCourseId,
        st.ISACTIVE                 AS IsActive 
FROM    DBO.MSESSIONTOPIC st
LEFT JOIN DBO.MPROGRAMMESESSION ps
       ON ps.PROGRAMMESESSIONID = st.PROGRAMMESESSIONID
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = st.PROGRAMMEID
WHERE   st.PROGRAMMESESSIONID IN @ProgrammeSessionIds
ORDER BY st.SLNO";


        // =====================================================
        // CATALOGUE LIST
        // =====================================================
        public const string GET_CATALOGUE_PAGED = @"
SELECT  tp.PROGRAMMEID          AS ProgrammeId,
        tp.PROGRAMMECODE        AS ProgrammeCode,
        tp.PROGRAMMETITLE       AS ProgrammeTitle,
        tp.PROGRAMMETYPE        AS ProgrammeType,
        tp.DELIVERYMODE         AS DeliveryMode,
        tp.COMPLEXITYLEVEL      AS ComplexityLevel,
        tp.PROGRAMMEVERSIONNO   AS ProgrammeVersionNo,
        td.DOMAINCODE           AS DomainCode,
        td.DOMAINNAME           AS DomainName,
        tp.DURATIONHOURS        AS DurationHours,
        tp.ISACTIVE             AS IsActive,
        tp.CREATEDON            AS CreatedOn

FROM    DBO.MTRAININGPROGRAMME tp
LEFT JOIN DBO.MTRAININGDOMAIN td
       ON td.TRAININGDOMAINID = tp.TRAININGDOMAINID
      AND td.TENANTID = tp.TENANTID

WHERE   tp.TENANTID = @TenantId
AND     (@IsActive IS NULL OR tp.ISACTIVE = @IsActive)
AND     (@ProgrammeType IS NULL OR tp.PROGRAMMETYPE = @ProgrammeType)
AND     (@DomainId IS NULL OR tp.TRAININGDOMAINID = @DomainId)

ORDER BY tp.PROGRAMMETITLE";


        public const string GET_CATALOGUE_PAGED_COUNT = @"
SELECT COUNT(1)
FROM DBO.MTRAININGPROGRAMME tp
WHERE tp.TENANTID = @TenantId
AND (@IsActive IS NULL OR tp.ISACTIVE = @IsActive)
AND (@ProgrammeType IS NULL OR tp.PROGRAMMETYPE = @ProgrammeType)
AND (@DomainId IS NULL OR tp.TRAININGDOMAINID = @DomainId)";


        // =====================================================
        // SAVE PROGRAMME
        // =====================================================
        public const string SAVE_PROGRAMME = @"
INSERT INTO DBO.MTRAININGPROGRAMME
(
    PROGRAMMEID, PROGRAMMECODE, PROGRAMMETITLE,
    SHORTDESCRIPTION, FULLDESCRIPTION,

    TRAININGDOMAINID,

    PROGRAMMETYPE, DELIVERYMODE, COMPLEXITYLEVEL,
    DIFFICULTYLEVEL, PROVIDERTYPE, PROGRAMMELEVEL,

    DURATIONHOURS,

    ISREFRESHER, HASASSESSMENT, HASPREASSESSMENT, CERTONCOMPLETE,

    CERTVALIDITYDAYS, RENEWALWINDOWDAYS,

    MINCAPACITY, MAXCAPACITY,PASSMARK,

    COSTPERPARTICIPANT,

    LEARNINGOBJECTIVES, TARGETAUDIENCE, PREREQUISITES,

    PROGRAMMEVERSIONNO,

    ISACTIVE,

    VERSION, STATUS, SORTORDER, SOURCETYPE,

    TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @ProgrammeId, @ProgrammeCode, @ProgrammeTitle,
    @ShortDescription, @FullDescription,

    @TrainingDomainId,

    @ProgrammeType, @DeliveryMode, @ComplexityLevel,
    @DifficultyLevel, @ProviderType, @ProgrammeLevel,

    @DurationHours,

    @IsRefresher, @HasAssessment, @HasPreAssessment, @CertOnComplete,

    @CertValidityDays, @RenewalWindowDays,

    @MinCapacity, @MaxCapacity,@PassMark,

    @CostPerParticipant,

    @LearningObjectives, @TargetAudience, @Prerequisites,

    @ProgrammeVersionNo,

    @IsActive,

    @Version, @Status, @SortOrder, @SourceType,

    @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";


        // =====================================================
        // SAVE SESSION
        // =====================================================
        public const string SAVE_PROGRAMME_SESSION = @"
INSERT INTO DBO.MPROGRAMMESESSION
(
    PROGRAMMESESSIONID, PROGRAMMEID, SLNO,
    SESSIONCODE, SESSIONTITLE,
    SESSIONTYPE, SESSIONDELIVMODE,
    DURATIONHOURS,
    ISOPTIONAL, ISMANDATORYFORCERT, ISASSESSMENTREQUIRED,
    MINATTENDANCEPCT,
    DELIVERYNOTE, DOJOSTATIONREF,
    ISACTIVE,
    TENANTID
)
VALUES
(
    @ProgrammeSessionId, @ProgrammeId, @SlNo,
    @SessionCode, @SessionTitle,
    @SessionType, @SessionDelivMode,
    @DurationHours,
    @IsOptional, @IsMandatoryForCert, @IsAssessmentRequired,
    @MinAttendancePct,
    @DeliveryNote, @DojoStationRef,
    @IsActive,
    @TenantId
)";


        // =====================================================
        // SAVE TOPIC
        // =====================================================
        public const string SAVE_SESSION_TOPIC = @"
INSERT INTO DBO.MSESSIONTOPIC
(
    SESSIONTOPICID, PROGRAMMESESSIONID, PROGRAMMEID,
    SLNO,
    TOPICCODE, TOPICTITLE,
    TOPICTYPE,
    DURATIONHOURS,
    ISOPTIONAL,
    CONTENTSUMMARY,
    LMSCONTENTID, LMSCOURSEID,
    ISACTIVE
)
VALUES
(
    @SessionTopicId, @ProgrammeSessionId, @ProgrammeId,
    @SlNo,
    @TopicCode, @TopicTitle,
    @TopicType,
    @DurationHours,
    @IsOptional,
    @ContentSummary,
    @LmsContentId, @LmsCourseId,
    @IsActive
)";


        public const string DELETE_PROGRAMME = @"

-- =====================================================
-- STEP 1 : DELETE ASSESSMENT QUESTIONS
-- =====================================================

DELETE AQ
FROM DBO.MASSESSMENTQUESTION AQ
INNER JOIN DBO.MSESSIONASSESSMENT SA
    ON SA.ASSESSMENTID = AQ.ASSESSMENTID
INNER JOIN DBO.MSESSIONTOPIC ST
    ON ST.SESSIONTOPICID = SA.SESSIONTOPICID
WHERE ST.PROGRAMMEID = @ProgrammeId;

-- =====================================================
-- STEP 2 : DELETE TRAINING CERTIFICATES
-- =====================================================

DELETE TCERT
FROM DBO.TTRAININGCERTIFICATE TCERT
INNER JOIN DBO.TTRAININGCOMPLETION TCOMP
    ON TCOMP.TRAININGCOMPLETIONID = TCERT.TRAININGCOMPLETIONID
INNER JOIN DBO.TTRAININGINSTANCE TINS
    ON TINS.INSTANCEID = TCOMP.INSTANCEID
WHERE TINS.PROGRAMMEID = @ProgrammeId;

DELETE FROM DBO.TTRAININGCERTIFICATE
WHERE PROGRAMMEID = @ProgrammeId;

-- =====================================================
-- STEP 3 : DELETE TRAINING COMPLETION
-- =====================================================

DELETE TCOMP
FROM DBO.TTRAININGCOMPLETION TCOMP
INNER JOIN DBO.TTRAININGINSTANCE TINS
    ON TINS.INSTANCEID = TCOMP.INSTANCEID
WHERE TINS.PROGRAMMEID = @ProgrammeId;

DELETE FROM DBO.TTRAININGCOMPLETION
WHERE PROGRAMMEID = @ProgrammeId;

-- =====================================================
-- STEP 4 : DELETE SESSION ASSESSMENT
-- =====================================================

DELETE SA
FROM DBO.MSESSIONASSESSMENT SA
INNER JOIN DBO.MSESSIONTOPIC ST
    ON ST.SESSIONTOPICID = SA.SESSIONTOPICID
WHERE ST.PROGRAMMEID = @ProgrammeId;

DELETE FROM DBO.MSESSIONASSESSMENT
WHERE PROGRAMMEID = @ProgrammeId;

-- =====================================================
-- STEP 5 : DELETE SESSION TOPIC
-- =====================================================

DELETE FROM DBO.MSESSIONTOPIC
WHERE PROGRAMMEID = @ProgrammeId;

-- =====================================================
-- STEP 6 : DELETE TRAINING ATTENDANCE
-- =====================================================

DELETE TA
FROM DBO.TTRAININGATTENDANCE TA
INNER JOIN DBO.TINSTANCESESSION TIS
    ON TIS.INSTANCESESSIONID = TA.INSTANCESESSIONID
INNER JOIN DBO.TTRAININGINSTANCE TI
    ON TI.INSTANCEID = TIS.INSTANCEID
WHERE TI.PROGRAMMEID = @ProgrammeId;

-- =====================================================
-- STEP 7 : DELETE INSTANCE SESSION
-- =====================================================

DELETE TIS
FROM DBO.TINSTANCESESSION TIS
INNER JOIN DBO.TTRAININGINSTANCE TI
    ON TI.INSTANCEID = TIS.INSTANCEID
WHERE TI.PROGRAMMEID = @ProgrammeId;

-- =====================================================
-- STEP 8 : DELETE OTHER CHILD TABLES
-- =====================================================

DELETE FROM DBO.MPROGRAMMESKILLMAP
WHERE PROGRAMMEID = @ProgrammeId;

DELETE FROM DBO.MPROGRAMMESESSION
WHERE PROGRAMMEID = @ProgrammeId;

DELETE FROM DBO.MPROGRAMMEPREREQUISITE
WHERE PROGRAMMEID = @ProgrammeId
   OR PREREQPROGRAMMEID = @ProgrammeId;

DELETE FROM DBO.MPROGCOMPETENCYMAP
WHERE PROGRAMMEID = @ProgrammeId;

DELETE FROM DBO.MEVALUATIONPLAN
WHERE PROGRAMMEID = @ProgrammeId;

DELETE FROM DBO.TLMSBRIDGE
WHERE TMSPROGRAMMEID = @ProgrammeId;

DELETE FROM DBO.TSKILLGAPLINK
WHERE RECOMMENDEDPROGRAMMEID = @ProgrammeId;

-- =====================================================
-- STEP 9 : DELETE TRAINING INSTANCE
-- =====================================================

DELETE FROM DBO.TTRAININGINSTANCE
WHERE PROGRAMMEID = @ProgrammeId;

-- =====================================================
-- STEP 10 : DELETE PROGRAMME MASTER
-- =====================================================

DELETE FROM DBO.MTRAININGPROGRAMME
WHERE PROGRAMMEID = @ProgrammeId
AND TENANTID = @TenantId;";





        public const string DELETE_PROGRAMME_SESSIONS = @"
DELETE FROM DBO.MPROGRAMMESESSION
WHERE PROGRAMMEID = @ProgrammeId
AND TENANTID = @TenantId";

        public const string DELETE_SESSION_TOPICS = @"
DELETE FROM DBO.MSESSIONTOPIC
WHERE PROGRAMMEID = @ProgrammeId";

        // ── Children of MSESSIONASSESSMENT ───────────────────────────────────────
        public const string DELETE_PAPER_SECTIONS_BY_PROGRAMME = @"
DELETE ps2 FROM DBO.MPAPERSECTION ps2
INNER JOIN DBO.MASSESSMENTPAPER ap ON ap.ASSESSMENTPAPERID = ps2.ASSESSMENTPAPERID
INNER JOIN DBO.MSESSIONASSESSMENT sa ON sa.ASSESSMENTID = ap.ASSESSMENTID
WHERE sa.PROGRAMMEID = @ProgrammeId";

        public const string DELETE_ASSESSMENT_PAPERS_BY_PROGRAMME = @"
DELETE ap FROM DBO.MASSESSMENTPAPER ap
INNER JOIN DBO.MSESSIONASSESSMENT sa ON sa.ASSESSMENTID = ap.ASSESSMENTID
WHERE sa.PROGRAMMEID = @ProgrammeId";

        public const string DELETE_ASSESSMENT_QUESTIONS_BY_PROGRAMME = @"
DELETE aq FROM DBO.MASSESSMENTQUESTION aq
INNER JOIN DBO.MSESSIONASSESSMENT sa ON sa.ASSESSMENTID = aq.ASSESSMENTID
WHERE sa.PROGRAMMEID = @ProgrammeId";

        public const string DELETE_ATTEMPT_RESPONSES_BY_PROGRAMME = @"
DELETE ar FROM DBO.TATTEMPTRESPONSE ar
INNER JOIN DBO.TASSESSMENTATTEMPT at2 ON at2.ASSESSMENTATTEMPTID = ar.ASSESSMENTATTEMPTID
INNER JOIN DBO.MSESSIONASSESSMENT sa ON sa.ASSESSMENTID = at2.ASSESSMENTID
WHERE sa.PROGRAMMEID = @ProgrammeId";

        public const string DELETE_ASSESSMENT_ATTEMPTS_BY_PROGRAMME = @"
DELETE at2 FROM DBO.TASSESSMENTATTEMPT at2
INNER JOIN DBO.MSESSIONASSESSMENT sa ON sa.ASSESSMENTID = at2.ASSESSMENTID
WHERE sa.PROGRAMMEID = @ProgrammeId";

        public const string DELETE_SESSION_ASSESSMENTS_BY_PROGRAMME = @"
DELETE FROM DBO.MSESSIONASSESSMENT
WHERE PROGRAMMEID = @ProgrammeId";

        // ── Children of TINSTANCESESSION (via MPROGRAMMESESSION) ─────────────────
        public const string DELETE_ATTENDANCE_BY_PROGRAMME = @"
DELETE att FROM DBO.TTRAININGATTENDANCE att
INNER JOIN DBO.TINSTANCESESSION ins ON ins.INSTANCESESSIONID = att.INSTANCESESSIONID
INNER JOIN DBO.MPROGRAMMESESSION ps ON ps.PROGRAMMESESSIONID = ins.PROGRAMMESESSIONID
WHERE ps.PROGRAMMEID = @ProgrammeId";

        public const string DELETE_INSTANCE_SESSIONS_BY_PROGRAMME = @"
DELETE ins FROM DBO.TINSTANCESESSION ins
INNER JOIN DBO.MPROGRAMMESESSION ps ON ps.PROGRAMMESESSIONID = ins.PROGRAMMESESSIONID
WHERE ps.PROGRAMMEID = @ProgrammeId";

        public const string DELETE_LMS_BRIDGE_BY_PROGRAMME = @"
DELETE lb FROM DBO.TLMSBRIDGE lb
INNER JOIN DBO.MPROGRAMMESESSION ps ON ps.PROGRAMMESESSIONID = lb.TMSPROGRAMMESESSIONID
WHERE ps.PROGRAMMEID = @ProgrammeId";


        // =====================================================
        // GET PREREQUISITES
        // =====================================================
        public const string GET_PROGRAMME_PREREQUISITES = @"
SELECT  pp.PROGRAMMEPREREQID        AS ProgrammePrereqId,
        pp.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        pp.PROGRAMMEPREREQCODE      AS ProgrammePrereqCode,
        pp.PROGRAMMEPREREQNAME      AS ProgrammePrereqName,
        pp.PREREQPROGRAMMEID        AS PrereqProgrammeId,
        rp.PROGRAMMECODE            AS PrereqProgrammeCode,
        rp.PROGRAMMETITLE           AS PrereqProgrammeName,
        pp.PREREQSKILLID            AS PrereqSkillId,
        sk.SKILLCODE                AS PrereqSkillCode,
        sk.SKILLNAME                AS PrereqSkillName,
        pp.PREREQMINLEVELNO         AS PrereqMinLevelNo,
        pp.ISHARD                   AS IsHard,
        pp.REMARKS                  AS Remarks
FROM    DBO.MPROGRAMMEPREREQUISITE pp
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = pp.PROGRAMMEID
LEFT JOIN DBO.MTRAININGPROGRAMME rp
       ON rp.PROGRAMMEID = pp.PREREQPROGRAMMEID
LEFT JOIN DBO.MSKILL sk
       ON sk.SKILLID = pp.PREREQSKILLID
WHERE   pp.PROGRAMMEID = @ProgrammeId";

        public const string GET_PROGRAMME_PREREQUISITES_LIST = @"
SELECT  pp.PROGRAMMEPREREQID        AS ProgrammePrereqId,
        pp.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        pp.PROGRAMMEPREREQCODE      AS ProgrammePrereqCode,
        pp.PROGRAMMEPREREQNAME      AS ProgrammePrereqName,
        pp.PREREQPROGRAMMEID        AS PrereqProgrammeId,
        rp.PROGRAMMECODE            AS PrereqProgrammeCode,
        rp.PROGRAMMETITLE           AS PrereqProgrammeName,
        pp.PREREQSKILLID            AS PrereqSkillId,
        sk.SKILLCODE                AS PrereqSkillCode,
        sk.SKILLNAME                AS PrereqSkillName,
        pp.PREREQMINLEVELNO         AS PrereqMinLevelNo,
        pp.ISHARD                   AS IsHard,
        pp.REMARKS                  AS Remarks
FROM    DBO.MPROGRAMMEPREREQUISITE pp
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = pp.PROGRAMMEID
LEFT JOIN DBO.MTRAININGPROGRAMME rp
       ON rp.PROGRAMMEID = pp.PREREQPROGRAMMEID
LEFT JOIN DBO.MSKILL sk
       ON sk.SKILLID = pp.PREREQSKILLID
WHERE   pp.PROGRAMMEID IN @ProgrammeIds";


        // =====================================================
        // GET SKILL MAPS
        // =====================================================
        public const string GET_PROGRAMME_SKILLMAPS = @"
SELECT  sm.PROGRAMMESKILLMAPID      AS ProgrammeSkillMapId,
        sm.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        sm.PROGRAMMESKILLMAPCODE    AS ProgrammeSkillMapCode,
        sm.PROGRAMMESKILLMAPNAME    AS ProgrammeSkillMapName,
        sm.PROGRAMMESESSIONID       AS ProgrammeSessionId,
        ps.SESSIONCODE              AS SessionCode,
        ps.SESSIONTITLE             AS SessionTitle,
        sm.SKILLID                  AS SkillId,
        sk.SKILLCODE                AS SkillCode,
        sk.SKILLNAME                AS SkillName,
        sm.PRODUCTFAMILYID          AS ProductFamilyId,
        pf.ITEMCODE                 AS ProductFamilyCode,
        pf.ITEMNAME                 AS ProductFamilyName,
        sm.MODELID                  AS ModelId,
        md.MODELCODE                AS ModelCode,
        md.MODELNAME                AS ModelName,
        sm.QUALIFIESFORLEVELNO      AS QualifiesForLevelNo,
        sm.ISAUTOPROPOSE            AS IsAutoPropose,
        sm.REQUIRESPASSEDASSESS     AS RequiresPassedAssess,
        sm.ASSESSMENTID             AS AssessmentId,
        sm.REMARKS                  AS Remarks
FROM    DBO.MPROGRAMMESKILLMAP sm
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = sm.PROGRAMMEID
LEFT JOIN DBO.MPROGRAMMESESSION ps
       ON ps.PROGRAMMESESSIONID = sm.PROGRAMMESESSIONID
LEFT JOIN DBO.MSKILL sk
       ON sk.SKILLID = sm.SKILLID
LEFT JOIN DBO.MITEM pf
       ON pf.ITEMID = sm.PRODUCTFAMILYID
LEFT JOIN DBO.MMODEL md
       ON md.MODELID = sm.MODELID
WHERE   sm.PROGRAMMEID = @ProgrammeId";

        public const string GET_PROGRAMME_SKILLMAPS_LIST = @"
SELECT  sm.PROGRAMMESKILLMAPID      AS ProgrammeSkillMapId,
        sm.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        sm.PROGRAMMESKILLMAPCODE    AS ProgrammeSkillMapCode,
        sm.PROGRAMMESKILLMAPNAME    AS ProgrammeSkillMapName,
        sm.PROGRAMMESESSIONID       AS ProgrammeSessionId,
        ps.SESSIONCODE              AS SessionCode,
        ps.SESSIONTITLE             AS SessionTitle,
        sm.SKILLID                  AS SkillId,
        sk.SKILLCODE                AS SkillCode,
        sk.SKILLNAME                AS SkillName,
        sm.PRODUCTFAMILYID          AS ProductFamilyId,
        pf.ITEMCODE                 AS ProductFamilyCode,
        pf.ITEMNAME                 AS ProductFamilyName,
        sm.MODELID                  AS ModelId,
        md.MODELCODE                AS ModelCode,
        md.MODELNAME                AS ModelName,
        sm.QUALIFIESFORLEVELNO      AS QualifiesForLevelNo,
        sm.ISAUTOPROPOSE            AS IsAutoPropose,
        sm.REQUIRESPASSEDASSESS     AS RequiresPassedAssess,
        sm.ASSESSMENTID             AS AssessmentId,
        sm.REMARKS                  AS Remarks
FROM    DBO.MPROGRAMMESKILLMAP sm
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = sm.PROGRAMMEID
LEFT JOIN DBO.MPROGRAMMESESSION ps
       ON ps.PROGRAMMESESSIONID = sm.PROGRAMMESESSIONID
LEFT JOIN DBO.MSKILL sk
       ON sk.SKILLID = sm.SKILLID
LEFT JOIN DBO.MITEM pf
       ON pf.ITEMID = sm.PRODUCTFAMILYID
LEFT JOIN DBO.MMODEL md
       ON md.MODELID = sm.MODELID
WHERE sm.PROGRAMMEID IN @ProgrammeIds";

        // Resolves the skill-grant mapping for the skill-level-update trigger: rows scoped to
        // this specific AssessmentId OR to the whole programme (-1 sentinel). Preferring the
        // more specific match when both exist for the same SkillId is done in C# (TxSkillUpgradeBLL),
        // not here, to keep this query a simple, cacheable read.
        public const string GET_PROGRAMME_SKILLMAPS_FOR_TRIGGER = @"
SELECT  sm.PROGRAMMESKILLMAPID      AS ProgrammeSkillMapId,
        sm.PROGRAMMEID              AS ProgrammeId,
        sm.SKILLID                  AS SkillId,
        sk.SKILLCODE                AS SkillCode,
        sk.SKILLNAME                AS SkillName,
        sm.PRODUCTFAMILYID          AS ProductFamilyId,
        sm.MODELID                  AS ModelId,
        sm.QUALIFIESFORLEVELNO      AS QualifiesForLevelNo,
        sm.ISAUTOPROPOSE            AS IsAutoPropose,
        sm.REQUIRESPASSEDASSESS     AS RequiresPassedAssess,
        sm.ASSESSMENTID             AS AssessmentId
FROM    DBO.MPROGRAMMESKILLMAP sm
LEFT JOIN DBO.MSKILL sk
       ON sk.SKILLID = sm.SKILLID
INNER JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = sm.PROGRAMMEID AND tp.TENANTID = @TenantId
WHERE   sm.PROGRAMMEID = @ProgrammeId
AND     (sm.ASSESSMENTID = @AssessmentId OR sm.ASSESSMENTID = -1)";

        // =====================================================
        // GET COMPETENCY MAPS
        // =====================================================
        public const string GET_PROGRAMME_COMPETENCYMAPS = @"
SELECT  cm.PROGCOMPETENCYMAPID      AS ProgCompetencyMapId,
        cm.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        cm.PROGRAMMESESSIONID       AS ProgrammeSessionId,
        ps.SESSIONCODE              AS SessionCode,
        ps.SESSIONTITLE             AS SessionTitle,
        cm.COMPETENCYID             AS CompetencyId,
        co.COMPETENCYCODE           AS CompetencyCode,
        co.COMPETENCYNAME           AS CompetencyName,
        cm.CONTRIBUTESTOLEVELNO     AS ContributesToLevelNo,
        cm.CREDITWEIGHT             AS CreditWeight,
        cm.ISDIRECTEVIDENCE         AS IsDirectEvidence,
        cm.REMARKS                  AS Remarks
FROM    DBO.MPROGCOMPETENCYMAP cm
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = cm.PROGRAMMEID
LEFT JOIN DBO.MPROGRAMMESESSION ps
       ON ps.PROGRAMMESESSIONID = cm.PROGRAMMESESSIONID
LEFT JOIN DBO.MCOMPETENCY co
       ON co.COMPETENCYID = cm.COMPETENCYID
WHERE   cm.PROGRAMMEID = @ProgrammeId";

        public const string GET_PROGRAMME_COMPETENCYMAPS_LIST = @"
SELECT  cm.PROGCOMPETENCYMAPID      AS ProgCompetencyMapId,
        cm.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,
        cm.PROGRAMMESESSIONID       AS ProgrammeSessionId,
        ps.SESSIONCODE              AS SessionCode,
        ps.SESSIONTITLE             AS SessionTitle,
        cm.COMPETENCYID             AS CompetencyId,
        co.COMPETENCYCODE           AS CompetencyCode,
        co.COMPETENCYNAME           AS CompetencyName,
        cm.CONTRIBUTESTOLEVELNO     AS ContributesToLevelNo,
        cm.CREDITWEIGHT             AS CreditWeight,
        cm.ISDIRECTEVIDENCE         AS IsDirectEvidence,
        cm.REMARKS                  AS Remarks
FROM    DBO.MPROGCOMPETENCYMAP cm
LEFT JOIN DBO.MTRAININGPROGRAMME tp
       ON tp.PROGRAMMEID = cm.PROGRAMMEID
LEFT JOIN DBO.MPROGRAMMESESSION ps
       ON ps.PROGRAMMESESSIONID = cm.PROGRAMMESESSIONID
LEFT JOIN DBO.MCOMPETENCY co
       ON co.COMPETENCYID = cm.COMPETENCYID
WHERE   cm.PROGRAMMEID IN @ProgrammeIds";

        // =====================================================
        // SAVE PREREQUISITE
        // =====================================================
        public const string SAVE_PROGRAMME_PREREQUISITE = @"
INSERT INTO DBO.MPROGRAMMEPREREQUISITE
(
    PROGRAMMEPREREQID, PROGRAMMEID,
    PROGRAMMEPREREQCODE, PROGRAMMEPREREQNAME,
    PREREQPROGRAMMEID, PREREQSKILLID,
    PREREQMINLEVELNO, ISHARD,
    REMARKS
)
VALUES
(
    @ProgrammePrereqId, @ProgrammeId,
    @ProgrammePrereqCode, @ProgrammePrereqName,
    @PrereqProgrammeId, @PrereqSkillId,
    @PrereqMinLevelNo, @IsHard,
    @Remarks
)";


        // =====================================================
        // SAVE SKILL MAP
        // =====================================================
        public const string SAVE_PROGRAMME_SKILLMAP = @"
INSERT INTO DBO.MPROGRAMMESKILLMAP
(
    PROGRAMMESKILLMAPID, PROGRAMMEID,
    PROGRAMMESKILLMAPCODE, PROGRAMMESKILLMAPNAME,
    PROGRAMMESESSIONID, SKILLID,
    PRODUCTFAMILYID, MODELID,
    QUALIFIESFORLEVELNO, ISAUTOPROPOSE,
    REQUIRESPASSEDASSESS, ASSESSMENTID, REMARKS
)
VALUES
(
    @ProgrammeSkillMapId, @ProgrammeId,
    @ProgrammeSkillMapCode, @ProgrammeSkillMapName,
    @ProgrammeSessionId, @SkillId,
    @ProductFamilyId, @ModelId,
    @QualifiesForLevelNo, @IsAutoPropose,
    @RequiresPassedAssess, @AssessmentId, @Remarks
)";


        // =====================================================
        // SAVE COMPETENCY MAP
        // =====================================================
        public const string SAVE_PROGRAMME_COMPETENCYMAP = @"
INSERT INTO DBO.MPROGCOMPETENCYMAP
(
    PROGCOMPETENCYMAPID, PROGRAMMEID,
    PROGRAMMESESSIONID, COMPETENCYID,
    CONTRIBUTESTOLEVELNO, CREDITWEIGHT,
    ISDIRECTEVIDENCE, REMARKS
)
VALUES
(
    @ProgCompetencyMapId, @ProgrammeId,
    @ProgrammeSessionId, @CompetencyId,
    @ContributesToLevelNo, @CreditWeight,
    @IsDirectEvidence, @Remarks
)";


        // =====================================================
        // DELETE PREREQUISITES
        // =====================================================
        public const string DELETE_PROGRAMME_PREREQUISITES = @"
DELETE FROM DBO.MPROGRAMMEPREREQUISITE
WHERE PROGRAMMEID = @ProgrammeId";


        // =====================================================
        // DELETE SKILL MAPS
        // =====================================================
        public const string DELETE_PROGRAMME_SKILLMAPS = @"
DELETE FROM DBO.MPROGRAMMESKILLMAP
WHERE PROGRAMMEID = @ProgrammeId";


        // =====================================================
        // DELETE COMPETENCY MAPS
        // =====================================================
        public const string DELETE_PROGRAMME_COMPETENCYMAPS = @"
DELETE FROM DBO.MPROGCOMPETENCYMAP
WHERE PROGRAMMEID = @ProgrammeId";




        public const string UPDATE_PROGRAMME = @"
UPDATE DBO.MTRAININGPROGRAMME
SET
    -- =====================================================
    -- BASIC DETAILS
    -- =====================================================
    PROGRAMMECODE         = @ProgrammeCode,
    PROGRAMMETITLE        = @ProgrammeTitle,

    SHORTDESCRIPTION      = @ShortDescription,
    FULLDESCRIPTION       = @FullDescription,

    TRAININGDOMAINID      = @TrainingDomainId,

    -- =====================================================
    -- PROGRAMME CONFIGURATION
    -- =====================================================
    PROGRAMMETYPE         = @ProgrammeType,
    DELIVERYMODE          = @DeliveryMode,
    COMPLEXITYLEVEL       = @ComplexityLevel,
    DIFFICULTYLEVEL       = @DifficultyLevel,
    PROVIDERTYPE          = @ProviderType,
    PROGRAMMELEVEL        = @ProgrammeLevel,

    -- =====================================================
    -- DURATION
    -- =====================================================
    DURATIONHOURS         = @DurationHours,

    -- =====================================================
    -- FLAGS
    -- =====================================================
    ISREFRESHER           = @IsRefresher,
    HASASSESSMENT         = @HasAssessment,
    HASPREASSESSMENT      = @HasPreAssessment,
    CERTONCOMPLETE        = @CertOnComplete,

    -- =====================================================
    -- CERTIFICATION
    -- =====================================================
    CERTVALIDITYDAYS      = @CertValidityDays,
    RENEWALWINDOWDAYS     = @RenewalWindowDays,

    -- =====================================================
    -- CAPACITY
    -- =====================================================
    MINCAPACITY           = @MinCapacity,
    MAXCAPACITY           = @MaxCapacity,
    PASSMARK              = @PassMark,

    -- =====================================================
    -- COSTING
    -- =====================================================
    COSTPERPARTICIPANT    = @CostPerParticipant,

    -- =====================================================
    -- LEARNING DETAILS
    -- =====================================================
    LEARNINGOBJECTIVES    = @LearningObjectives,
    TARGETAUDIENCE        = @TargetAudience,
    PREREQUISITES         = @Prerequisites,

    -- =====================================================
    -- VERSIONING
    -- =====================================================
    PROGRAMMEVERSIONNO    = @ProgrammeVersionNo,

    -- =====================================================
    -- STATUS
    -- =====================================================
    ISACTIVE              = @IsActive,

    VERSION               = @Version,
    STATUS                = @Status,
    SORTORDER             = @SortOrder,

    -- =====================================================
    -- AUDIT
    -- =====================================================
    MODIFIEDBYID          = @ModifiedById,
    MODIFIEDON            = @ModifiedOn

WHERE PROGRAMMEID = @ProgrammeId
AND   TENANTID    = @TenantId";



        public const string GET_SELECTLIST_PROGRAMME = @"
            SELECT  tp.PROGRAMMEID          AS ProgrammeId,
                    tp.PROGRAMMECODE        AS ProgrammeCode,
                    tp.PROGRAMMETITLE       AS ProgrammeTitle,
                    tp.PROGRAMMETYPE        AS ProgrammeType,
                    tp.COMPLEXITYLEVEL      AS ComplexityLevel,
                    tp.PROGRAMMEVERSIONNO   AS ProgrammeVersionNo,
                    tp.ISACTIVE             AS IsActive,
                    td.DOMAINCODE           AS DomainCode,
                    td.DOMAINNAME           AS DomainName
            FROM    DBO.MTRAININGPROGRAMME tp
            LEFT JOIN DBO.MTRAININGDOMAIN td
                ON td.TRAININGDOMAINID = tp.TRAININGDOMAINID
               AND td.TENANTID         = tp.TENANTID
            WHERE   tp.TENANTID = @TenantId
            {DYNAMIC_WHERE}
            ORDER BY tp.PROGRAMMETITLE";


        public const string GET_TRAINING_PROGRAMME_LIST = @"SELECT  tp.PROGRAMMEID              AS ProgrammeId,
        tp.PROGRAMMECODE            AS ProgrammeCode,
        tp.PROGRAMMETITLE           AS ProgrammeTitle,

        tp.SHORTDESCRIPTION         AS ShortDescription,
        tp.FULLDESCRIPTION          AS FullDescription,

        tp.TRAININGDOMAINID         AS TrainingDomainId,
        td.DOMAINCODE               AS DomainCode,
        td.DOMAINNAME               AS DomainName,

        tp.PROGRAMMETYPE            AS ProgrammeType,
        tp.DELIVERYMODE             AS DeliveryMode,
        tp.COMPLEXITYLEVEL          AS ComplexityLevel,
        tp.DIFFICULTYLEVEL          AS DifficultyLevel,
        tp.PROVIDERTYPE             AS ProviderType,
        tp.PROGRAMMELEVEL           AS ProgrammeLevel,

        tp.DURATIONHOURS            AS DurationHours,
        tp.PASSMARK                 AS PassMark,
        tp.ISREFRESHER              AS IsRefresher,
        tp.HASASSESSMENT            AS HasAssessment,
        tp.HASPREASSESSMENT         AS HasPreAssessment,
        tp.CERTONCOMPLETE           AS CertOnComplete,

        tp.CERTVALIDITYDAYS         AS CertValidityDays,
        tp.RENEWALWINDOWDAYS        AS RenewalWindowDays,

        tp.MINCAPACITY              AS MinCapacity,
        tp.MAXCAPACITY              AS MaxCapacity,

        tp.COSTPERPARTICIPANT       AS CostPerParticipant,

        tp.LEARNINGOBJECTIVES       AS LearningObjectives,
        tp.TARGETAUDIENCE           AS TargetAudience,
        tp.PREREQUISITES            AS Prerequisites,

        tp.PROGRAMMEVERSIONNO       AS ProgrammeVersionNo,

        tp.ISACTIVE                 AS IsActive,

        tp.VERSION                  AS Version,
        tp.STATUS                   AS Status,
        tp.SORTORDER                AS SortOrder,

        tp.SOURCETYPE               AS SourceType,

        tp.TENANTID                 AS TenantId,
        tp.CREATEDBYID              AS CreatedById,
        tp.CREATEDON                AS CreatedOn,
        tp.MODIFIEDBYID             AS ModifiedById,
        tp.MODIFIEDON               AS ModifiedOn

FROM    DBO.MTRAININGPROGRAMME tp
LEFT JOIN DBO.MTRAININGDOMAIN td
       ON td.TRAININGDOMAINID = tp.TRAININGDOMAINID
      AND td.TENANTID = tp.TENANTID

WHERE
    tp.TENANTID    = @TenantId";
    }
}