// ============================================================
// GoodBooks ERP — Skill Management Module
// Query  : RoleSkillRequirementQB
// Table  : MJOBROLESKILL (M3) — child/mapping table
// Updated: Aligned to new DDL schema v1.0
//
// NOTES:
//   * MJOBROLESKILL has NO standard fields — no STATUS,
//     VERSION, TENANTID, CREATEDBYID etc. per DDL.
//     All filters are on the joined MSKILL.STATUS only.
//   * MJOBROLE join uses LEFT JOIN — adjust table/column
//     name (ROLENAME) to match your actual MJOBROLE table.
//   * UK_MJOBROLESKILL_ROLEID_SKILLID enforces one row per
//     role-skill pair — INSERT must respect this unique key.
//   * Index hints documented inline for DBA reference.
// ============================================================

namespace SkillManagementDAL.Query.RoleSkillRequirement
{
    public class RoleSkillRequirementQB
    {
        // ── GET_BY_ROLE ──────────────────────────────────────────
        // Returns all skill requirements for a given job role
        // with full display-name joins resolved.
        // Index hint : IX_MJOBROLESKILL_ROLEID  (ROLEID)
        // ────────────────────────────────────────────────────────
        // ── GET_BY_ROLE ──────────────────────────────────────────
        public const string GET_BY_ROLE = @"
SELECT
    JR.REQID            AS ReqId,
    JR.ROLEID           AS RoleId,
    JR.ROLECODE         AS RoleCode,
    JR.ROLENAME         AS RoleName,
    JR.SKILLID          AS SkillId,
    JR.SLNO             AS SlNo,
    JR.MINLEVNO         AS MinLevNo,
    JR.ISMANDATORY      AS IsMandatory,
    JR.CATEGORY         AS Category,
    JR.REMARKS          AS Remarks,

    S.SKILLNAME         AS SkillName,
    S.SKILLNAMEFULL     AS SkillNameFull,
    S.SKILLCODE         AS SkillCode,
    S.SKILLSCOPE        AS SkillScope,
    S.ISSPECIALCHAR     AS IsSpecialChar,
    S.LEVELCOUNT        AS LevelCount,
    S.MATRIXSEQNO       AS MatrixSeqNo,
    S.GROUPID           AS GroupId,

    ISNULL(G.GROUPNAME, 'No Group') AS GroupName,

    SL.LEVELLABEL       AS LevelLabel,
    SL.LEVELDESCRIPTOR  AS LevelDescriptor

FROM MJOBROLESKILL JR

INNER JOIN MSKILL S 
    ON S.SKILLID = JR.SKILLID

LEFT JOIN MSKILLGROUP G 
    ON G.GROUPID = S.GROUPID

LEFT JOIN MSKILLLEVEL SL 
    ON SL.SKILLID = JR.SKILLID
   AND SL.SLNO    = JR.MINLEVNO

WHERE JR.ROLEID = @RoleId

ORDER BY 
    JR.SLNO ASC,
    S.MATRIXSEQNO ASC,
    S.SKILLNAME ASC;";


        // ── GET_BY_ID ────────────────────────────────────────────
        public const string GET_BY_ID = @"
SELECT
    JR.REQID            AS ReqId,
    JR.ROLEID           AS RoleId,
    JR.ROLECODE         AS RoleCode,
    JR.ROLENAME         AS RoleName,
    JR.SKILLID          AS SkillId,
    JR.SLNO             AS SlNo,
    JR.MINLEVNO         AS MinLevNo,
    JR.ISMANDATORY      AS IsMandatory,
    JR.CATEGORY         AS Category,
    JR.REMARKS          AS Remarks,
    S.SKILLNAME         AS SkillName,
    S.SKILLNAMEFULL     AS SkillNameFull,
    S.SKILLCODE         AS SkillCode,
    S.SKILLSCOPE        AS SkillScope,
    S.ISSPECIALCHAR     AS IsSpecialChar,
    S.LEVELCOUNT        AS LevelCount,
    S.MATRIXSEQNO       AS MatrixSeqNo,
    S.GROUPID           AS GroupId,

    G.GROUPNAME         AS GroupName,

    SL.LEVELLABEL       AS LevelLabel,
    SL.LEVELDESCRIPTOR  AS LevelDescriptor

FROM        MJOBROLESKILL   JR
INNER JOIN  MSKILL          S   ON  S.SKILLID   = JR.SKILLID
                                AND S.STATUS    = 1
LEFT  JOIN  MSKILLGROUP     G   ON  G.GROUPID   = S.GROUPID
                                AND G.STATUS    = 1
LEFT  JOIN  MSKILLLEVEL     SL  ON  SL.SKILLID  = JR.SKILLID
                                AND SL.SLNO     = JR.MINLEVNO
WHERE JR.REQID = @ReqId";


        // ── GET_BY_SKILL ─────────────────────────────────────────
        public const string GET_BY_SKILL = @"
SELECT
    JR.REQID            AS ReqId,
    JR.ROLEID           AS RoleId,
    JR.ROLECODE         AS RoleCode,
    JR.ROLENAME         AS RoleName,
    JR.SKILLID          AS SkillId,
    JR.SLNO             AS SlNo,
    JR.MINLEVNO         AS MinLevNo,
    JR.ISMANDATORY      AS IsMandatory,
    JR.CATEGORY         AS Category,
    JR.REMARKS          AS Remarks,
    S.SKILLNAME         AS SkillName,
    S.SKILLNAMEFULL     AS SkillNameFull,
    S.SKILLCODE         AS SkillCode,
    S.SKILLSCOPE        AS SkillScope,
    S.ISSPECIALCHAR     AS IsSpecialChar,
    S.LEVELCOUNT        AS LevelCount,
    S.MATRIXSEQNO       AS MatrixSeqNo,
    S.GROUPID           AS GroupId,

    G.GROUPNAME         AS GroupName,

    SL.LEVELLABEL       AS LevelLabel,
    SL.LEVELDESCRIPTOR  AS LevelDescriptor

FROM        MJOBROLESKILL   JR
INNER JOIN  MSKILL          S   ON  S.SKILLID   = JR.SKILLID
                                AND S.STATUS    = 1
LEFT  JOIN  MSKILLGROUP     G   ON  G.GROUPID   = S.GROUPID
                                AND G.STATUS    = 1
LEFT  JOIN  MSKILLLEVEL     SL  ON  SL.SKILLID  = JR.SKILLID
                                AND SL.SLNO     = JR.MINLEVNO

WHERE JR.SKILLID = @SkillId

ORDER BY 
    JR.SLNO    ASC";


        // ── INSERT ───────────────────────────────────────────────
        public const string INSERT = @"
INSERT INTO MJOBROLESKILL
(
    REQID,
    ROLEID,
    ROLECODE,
    ROLENAME,
    SKILLID,
    SLNO,
    MINLEVNO,
    ISMANDATORY,
    CATEGORY,
    REMARKS
)
VALUES
(
    @ReqId,
    @RoleId,
    @RoleCode,
    @RoleName,
    @SkillId,
    @SlNo,
    @MinLevNo,
    @IsMandatory,
    @Category,
    @Remarks
)";


        // ── UPDATE ───────────────────────────────────────────────
        public const string UPDATE = @"
UPDATE  MJOBROLESKILL
SET     SLNO        = @SlNo,
        MINLEVNO    = @MinLevNo,
        ISMANDATORY = @IsMandatory,
        CATEGORY    = @Category,
        REMARKS     = @Remarks,
        ROLECODE    = @RoleCode,
        ROLENAME    = @RoleName
WHERE   REQID       = @ReqId";


        // ── GET_SELECT_LIST ──────────────────────────────────────
        public const string GET_SELECT_LIST = @"
SELECT
    JR.REQID            AS ReqId,
    JR.ROLEID           AS RoleId,
    JR.ROLECODE         AS RoleCode,
    JR.ROLENAME         AS RoleName,
    JR.SKILLID          AS SkillId,
    JR.SLNO             AS SlNo,
    JR.MINLEVNO         AS MinLevNo,
    JR.ISMANDATORY      AS IsMandatory,
    JR.CATEGORY         AS Category,
    JR.REMARKS          AS Remarks
FROM MJOBROLESKILL JR
WHERE
    1=1
    {DYNAMIC_WHERE}
ORDER BY RoleName ASC";


        // ── DELETE_BY_ROLE ───────────────────────────────────────
        public const string DELETE_BY_ROLE = @"
DELETE FROM MJOBROLESKILL
WHERE ROLEID = @RoleId";


        // ── DELETE_BY_ID ─────────────────────────────────────────
        public const string DELETE_BY_ID = @"
DELETE FROM MJOBROLESKILL
WHERE REQID = @ReqId";

        public const string GET_JOBROLE_SKILL_LIST_ALL = @"
SELECT
    JR.REQID            AS ReqId,
    JR.ROLEID           AS RoleId,
    JR.ROLECODE         AS RoleCode,
    JR.ROLENAME         AS RoleName,
    JR.SKILLID          AS SkillId,
    JR.SLNO             AS SlNo,
    JR.MINLEVNO         AS MinLevNo,
    JR.ISMANDATORY      AS IsMandatory,
    JR.CATEGORY         AS Category,
    JR.REMARKS          AS Remarks,

    S.SKILLNAME         AS SkillName,
    S.SKILLNAMEFULL     AS SkillNameFull,
    S.SKILLCODE         AS SkillCode,
    S.SKILLSCOPE        AS SkillScope,
    S.ISSPECIALCHAR     AS IsSpecialChar,
    S.LEVELCOUNT        AS LevelCount,
    S.MATRIXSEQNO       AS MatrixSeqNo,
    S.GROUPID           AS GroupId,

    ISNULL(G.GROUPNAME, 'No Group') AS GroupName,

    SL.LEVELLABEL       AS LevelLabel,
    SL.LEVELDESCRIPTOR  AS LevelDescriptor

FROM MJOBROLESKILL JR

INNER JOIN MSKILL S 
    ON S.SKILLID = JR.SKILLID

LEFT JOIN MSKILLGROUP G 
    ON G.GROUPID = S.GROUPID

LEFT JOIN MSKILLLEVEL SL 
    ON SL.SKILLID = JR.SKILLID
   AND SL.SLNO    = JR.MINLEVNO

ORDER BY JR.REQID;";

        public const string GET_JOBROLE_SKILL_LIST_PAGED = @"
SELECT
    JR.REQID            AS ReqId,
    JR.ROLEID           AS RoleId,
    JR.ROLECODE         AS RoleCode,
    JR.ROLENAME         AS RoleName,
    JR.SKILLID          AS SkillId,
    JR.SLNO             AS SlNo,
    JR.MINLEVNO         AS MinLevNo,
    JR.ISMANDATORY      AS IsMandatory,
    JR.CATEGORY         AS Category,
    JR.REMARKS          AS Remarks,

    S.SKILLNAME         AS SkillName,
    S.SKILLNAMEFULL     AS SkillNameFull,
    S.SKILLCODE         AS SkillCode,
    S.SKILLSCOPE        AS SkillScope,
    S.ISSPECIALCHAR     AS IsSpecialChar,
    S.LEVELCOUNT        AS LevelCount,
    S.MATRIXSEQNO       AS MatrixSeqNo,
    S.GROUPID           AS GroupId,

    ISNULL(G.GROUPNAME, 'No Group') AS GroupName,

    SL.LEVELLABEL       AS LevelLabel,
    SL.LEVELDESCRIPTOR  AS LevelDescriptor

FROM MJOBROLESKILL JR

INNER JOIN MSKILL S
    ON S.SKILLID = JR.SKILLID
    AND S.STATUS = 1

LEFT JOIN MSKILLGROUP G
    ON G.GROUPID = S.GROUPID
    AND G.STATUS = 1

LEFT JOIN MSKILLLEVEL SL
    ON SL.SKILLID = JR.SKILLID
   AND SL.SLNO    = JR.MINLEVNO

ORDER BY JR.REQID
OFFSET (@FirstNumber - 1) ROWS
FETCH NEXT @MaxResult ROWS ONLY
OPTION (RECOMPILE);";

        public const string GET_JOBROLE_SKILL_COUNT = @"
SELECT COUNT(1)
FROM MJOBROLESKILL JR

INNER JOIN MSKILL S
    ON S.SKILLID = JR.SKILLID
    AND S.STATUS = 1

LEFT JOIN MSKILLGROUP G
    ON G.GROUPID = S.GROUPID
    AND G.STATUS = 1

LEFT JOIN MSKILLLEVEL SL
    ON SL.SKILLID = JR.SKILLID
   AND SL.SLNO    = JR.MINLEVNO;";

    }
}