namespace TMSDAL.Query.SkillGapLink
{
    public static class SkillGapLinkQB
    {
        // Dedup check for producers (e.g. WiSkillGapSubscriber) that re-detect the same
        // employee/skill gap repeatedly — GAPSTATUS IN (0,1,2) is Open/NeedRaised/
        // TrainingScheduled, i.e. anything not yet Closed(3). Recommended index:
        // IX_TSKILLGAPLINK_EMPLOYEE_SKILL on (TENANTID, EMPLOYEEID, SKILLID, GAPSTATUS).
        public const string FIND_ACTIVE_LINK = @"
SELECT TOP 1 SKILLGAPLINKID
FROM DBO.TSKILLGAPLINK
WHERE EMPLOYEEID = @EmployeeId
  AND SKILLID     = @SkillId
  AND GAPSTATUS IN (0, 1, 2)
  AND TENANTID = @TenantId";

        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_SKILLGAPLINK = @"
SELECT  sgl.SKILLGAPLINKID              AS SkillGapLinkId,
        sgl.SKILLID                     AS SkillId,
        sk.SKILLCODE                    AS SkillCode,
        sk.SKILLNAME                    AS SkillName,
        sgl.EMPLOYEEID                  AS EmployeeId,
        emp.EMPLOYEECODE                AS EmployeeCode,
        emp.EMPLOYEENAME                AS EmployeeName,
        sgl.CURRENTLEVELNO              AS CurrentLevelNo,
        sgl.REQUIREDLEVELNO             AS RequiredLevelNo,
        sgl.GAPSEVERITY                 AS GapSeverity,
        sgl.DETECTEDON                  AS DetectedOn,
        sgl.AUTORAISEDNEEDID            AS AutoRaisedNeedId,
        tn.NEEDCODE                     AS NeedCode,
        tn.NEEDTITLE                    AS NeedTitle,
        sgl.RECOMMENDEDPROGRAMMEID      AS RecommendedProgrammeId,
        tp.PROGRAMMECODE                AS ProgrammeCode,
        tp.PROGRAMMETITLE               AS ProgrammeTitle,
        sgl.GAPSTATUS                   AS GapStatus,
        sgl.CLOSEDON                    AS ClosedOn,
        sgl.REMARKS                     AS Remarks,
        sgl.VERSION                     AS Version,
        sgl.STATUS                      AS Status,
        sgl.SORTORDER                   AS SortOrder,
        sgl.SOURCETYPE                  AS SourceType,
        sgl.CREATEDBYID                 AS CreatedById,
        cu.USERNAME                     AS CreatedByName,
        sgl.CREATEDON                   AS CreatedOn,
        sgl.MODIFIEDBYID                AS ModifiedById,
        mu.USERNAME                     AS ModifiedByName,
        sgl.MODIFIEDON                  AS ModifiedOn,
        sgl.TENANTID                    AS TenantId
FROM    DBO.TSKILLGAPLINK sgl
LEFT JOIN DBO.MSKILL sk        ON sk.SKILLID = sgl.SKILLID
LEFT JOIN DBO.MEMPLOYEE emp    ON emp.EMPLOYEEID = sgl.EMPLOYEEID
LEFT JOIN DBO.TTRAININGNEED tn ON tn.TRAININGNEEDID = sgl.AUTORAISEDNEEDID
LEFT JOIN DBO.MTRAININGPROGRAMME tp ON tp.PROGRAMMEID = sgl.RECOMMENDEDPROGRAMMEID
LEFT JOIN DBO.MUSER cu         ON cu.USERID = sgl.CREATEDBYID
LEFT JOIN DBO.MUSER mu         ON mu.USERID = sgl.MODIFIEDBYID
WHERE   sgl.SKILLGAPLINKID = @SkillGapLinkId
AND     sgl.TENANTID = @TenantId";


        // =====================================================
        // GET LIST (with {DYNAMIC_WHERE})
        // =====================================================
        public const string GET_SKILLGAPLINK_LIST = @"
SELECT  sgl.SKILLGAPLINKID              AS SkillGapLinkId,
        sk.SKILLCODE                    AS SkillCode,
        sk.SKILLNAME                    AS SkillName,
        emp.EMPLOYEECODE                AS EmployeeCode,
        emp.EMPLOYEENAME                AS EmployeeName,
        sgl.CURRENTLEVELNO              AS CurrentLevelNo,
        sgl.REQUIREDLEVELNO             AS RequiredLevelNo,
        sgl.GAPSEVERITY                 AS GapSeverity,
        sgl.GAPSTATUS                   AS GapStatus,
        sgl.DETECTEDON                  AS DetectedOn,
        tn.NEEDCODE                     AS NeedCode,
        tp.PROGRAMMECODE                AS ProgrammeCode,
        sgl.STATUS                      AS Status,
        sgl.CREATEDON                   AS CreatedOn
FROM    DBO.TSKILLGAPLINK sgl
LEFT JOIN DBO.MSKILL sk        ON sk.SKILLID = sgl.SKILLID
LEFT JOIN DBO.MEMPLOYEE emp    ON emp.EMPLOYEEID = sgl.EMPLOYEEID
LEFT JOIN DBO.TTRAININGNEED tn ON tn.TRAININGNEEDID = sgl.AUTORAISEDNEEDID
LEFT JOIN DBO.MTRAININGPROGRAMME tp ON tp.PROGRAMMEID = sgl.RECOMMENDEDPROGRAMMEID
WHERE   sgl.TENANTID = @TenantId
AND     {DYNAMIC_WHERE}
ORDER BY sgl.SKILLGAPLINKID DESC";


        // =====================================================
        // GET SELECT LIST
        // =====================================================
        public const string GET_SELECT_LIST_SKILLGAPLINK = @"
SELECT  sgl.SKILLGAPLINKID              AS SkillGapLinkId,
        sk.SKILLNAME                    AS SkillName,
        emp.EMPLOYEENAME                AS EmployeeName,
        sgl.GAPSEVERITY                 AS GapSeverity,
        sgl.GAPSTATUS                   AS GapStatus
FROM    DBO.TSKILLGAPLINK sgl
LEFT JOIN DBO.MSKILL sk        ON sk.SKILLID = sgl.SKILLID
LEFT JOIN DBO.MEMPLOYEE emp    ON emp.EMPLOYEEID = sgl.EMPLOYEEID
WHERE   sgl.TENANTID = @TenantId
AND     sgl.STATUS = 1
AND     {DYNAMIC_WHERE}
ORDER BY sk.SKILLNAME";


        // =====================================================
        // SAVE (INSERT)
        // =====================================================
        public const string SAVE_SKILLGAPLINK = @"
INSERT INTO DBO.TSKILLGAPLINK
(
    SKILLGAPLINKID, SKILLID, EMPLOYEEID,
    CURRENTLEVELNO, REQUIREDLEVELNO, GAPSEVERITY, DETECTEDON,
    AUTORAISEDNEEDID, RECOMMENDEDPROGRAMMEID,
    GAPSTATUS, CLOSEDON, REMARKS,
    VERSION, STATUS, SORTORDER, SOURCETYPE,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID
)
VALUES
(
    @SkillGapLinkId, @SkillId, @EmployeeId,
    @CurrentLevelNo, @RequiredLevelNo, @GapSeverity, @DetectedOn,
    @AutoRaisedNeedId, @RecommendedProgrammeId,
    @GapStatus, @ClosedOn, @Remarks,
    @Version, @Status, @SortOrder, @SourceType,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @TenantId
)";


        // =====================================================
        // UPDATE
        // =====================================================
        public const string UPDATE_SKILLGAPLINK = @"
UPDATE  DBO.TSKILLGAPLINK
SET     SKILLID                 = @SkillId,
        EMPLOYEEID              = @EmployeeId,
        CURRENTLEVELNO          = @CurrentLevelNo,
        REQUIREDLEVELNO         = @RequiredLevelNo,
        GAPSEVERITY             = @GapSeverity,
        DETECTEDON              = @DetectedOn,
        AUTORAISEDNEEDID        = @AutoRaisedNeedId,
        RECOMMENDEDPROGRAMMEID  = @RecommendedProgrammeId,
        GAPSTATUS               = @GapStatus,
        CLOSEDON                = @ClosedOn,
        REMARKS                 = @Remarks,
        VERSION                 = @Version,
        STATUS                  = @Status,
        SORTORDER               = @SortOrder,
        MODIFIEDBYID            = @ModifiedById,
        MODIFIEDON              = @ModifiedOn
WHERE   SKILLGAPLINKID = @SkillGapLinkId
AND     TENANTID = @TenantId";


        // =====================================================
        // DELETE
        // =====================================================
        public const string DELETE_SKILLGAPLINK = @"
DELETE FROM DBO.TSKILLGAPLINK
WHERE   SKILLGAPLINKID = @SkillGapLinkId
AND     TENANTID = @TenantId";
    }
}