// ============================================================
// GoodBooks ERP — Skill Management Module
// Query  : SkillProcessStageMapQB
// Table  : MROUTINGSKILL (M2) — child/mapping table
// Updated: Aligned to new DDL schema v1.0
//
// CRITICAL — MROUTINGSKILL IS A CHILD/MAPPING TABLE:
//   NO TENANTID, VERSION, STATUS, SORTORDER, SOURCETYPE,
//   CREATEDBYID, MODIFIEDBYID on this table.
//   All 10 DDL columns: MAPID, SKILLID, SLNO, ROUTINGID,
//   ROUTINGOPCODE, ROUTINGOPID, WORKCENTERID, ISPREREQUISITE,
//   MINLEVELREQUIRED, REMARKS.
//
// FIXES APPLIED (vs old script):
//   [1]  GET_BY_SKILL: Added S.STATUS = 1 guard on MSKILL join
//   [2]  GET_BY_SKILL: Added MROUTING / MROUTINGOP / MWORKCENTER
//                      joins for RoutingName, RoutingOpName,
//                      WorkCenterName  (were in DTO, no joins existed)
//   [3]  GET_BY_SKILL: Added SkillCode, SkillScope from MSKILL
//   [4]  GET_BY_SKILL: Added MSKILLLEVEL join for LevelLabel,
//                      LevelDescriptor at SLNO = MINLEVELREQUIRED
//   [5]  GET_BY_SKILL: Added ORDER BY RS.SLNO ASC (explicit ASC)
//   [6]  DELETE_BY_ID: Added AND SKILLID = @SkillId safety guard
//   [7]  Added GET_BY_ROUTING — all skills for a routing/process
//   [8]  Added GET_BY_ID     — post-insert/update single-row fetch
//   [9]  Added UPDATE        — edit existing mapping row
//   [10] Added CHECK_IN_USE  — verify skill is not active before
//                              deleting its routing mappings
//
// NOTES:
//   * MROUTING, MROUTINGOP, MWORKCENTER are external tables.
//     Adjust table and column names to match your actual schema:
//       MROUTING.ROUTINGNAME
//       MROUTINGOP.OPNAME
//       MWORKCENTER.WCENTERNAME
//   * Tenant scoping uses parent MSKILL join — no TENANTID on
//     MROUTINGSKILL itself.
//   * Index hints:
//       IX_MROUTINGSKILL_SKILLID   (SKILLID)
//       IX_MROUTINGSKILL_ROUTINGID (ROUTINGID, SKILLID)
// ============================================================

namespace SkillManagementDAL.Query.SkillProcessStageMap
{
    public class SkillProcessStageMapQB
    {
        // ── GET_BY_SKILL ─────────────────────────────────────────
        // Returns all routing stage mappings for a given skill.
        // FIX [1]: S.STATUS = 1 guard added to MSKILL join
        // FIX [2]: MROUTING / MROUTINGOP / MWORKCENTER joins added
        // FIX [3]: SkillCode, SkillScope added from MSKILL
        // FIX [4]: MSKILLLEVEL join added for LevelLabel/Descriptor
        // FIX [5]: ORDER BY RS.SLNO ASC (explicit direction)
        // Index hint : IX_MROUTINGSKILL_SKILLID  (SKILLID)
        // ────────────────────────────────────────────────────────
        public const string GET_BY_SKILL = @"SELECT
                -- MROUTINGSKILL (M2) — all 10 mapped columns
                RS.MAPID                AS MapId,
                RS.SKILLID              AS SkillId,
                RS.SLNO                 AS SlNo,
                RS.ROUTINGID            AS RoutingId,
                RS.ROUTINGOPCODE        AS RoutingOpCode,
                RS.ROUTINGOPID          AS RoutingOpId,
                RS.WORKCENTERID         AS WorkCenterId,
                RS.ISPREREQUISITE       AS IsPrerequisite,
                RS.MINLEVELREQUIRED     AS MinLevelRequired,
                RS.REMARKS              AS Remarks,

                -- MSKILL (S3) — skill display fields
                S.SKILLNAME             AS SkillName,
                S.SKILLCODE             AS SkillCode,
                S.SKILLSCOPE            AS SkillScope,

                -- MSKILLLEVEL (S4) — required level label and descriptor
                -- Resolved at SLNO = MINLEVELREQUIRED
                SL.SLEVELID             AS SkillLevelId,
                SL.LEVELLABEL           AS LevelLabel,
                SL.LEVELDESCRIPTOR      AS LevelDescriptor,

                -- MROUTING — routing name (LEFT — ROUTINGID = -1 when not set)
                -- NOTE: adjust ROUTINGNAME to your actual MROUTING column
                R.ROUTINGNAME           AS RoutingName,

                -- MROUTINGOP — routing operation name (LEFT — ROUTINGOPID = -1 when not set)
                -- NOTE: adjust OPNAME to your actual MROUTINGOP column
                --RO.OPNAME               AS RoutingOpName,

                -- MWORKCENTER — work centre name (LEFT — WORKCENTERID = -1 when not set)
                -- NOTE: adjust WCENTERNAME to your actual MWORKCENTER column
                WC.WORKCENTERNAME          AS WorkCenterName

            FROM        MROUTINGSKILL   RS

            -- Skill master (INNER — every mapping must have valid active skill)
            INNER JOIN  MSKILL          S   ON  S.SKILLID       = RS.SKILLID
                                            AND S.STATUS        = 1

            -- Required level label (LEFT — resolved at SLNO = MINLEVELREQUIRED)
            LEFT  JOIN  MSKILLLEVEL     SL  ON  SL.SKILLID      = RS.SKILLID
                                            AND SL.SLNO         = RS.MINLEVELREQUIRED

            -- Routing name (LEFT — ROUTINGID = -1 means not linked to a routing)
            LEFT  JOIN  MROUTING        R   ON  R.ROUTINGID     = RS.ROUTINGID
                                            AND RS.ROUTINGID   != -1

            ---- Routing operation name (LEFT — ROUTINGOPID = -1 means not linked)
            --LEFT  JOIN  MROUTINGOP      RO  ON  RO.ROUTINGOPID  = RS.ROUTINGOPID
            --                                AND RS.ROUTINGOPID != -1

            -- Work centre name (LEFT — WORKCENTERID = -1 means not linked)
            LEFT  JOIN  MWORKCENTER     WC  ON  WC.WORKCENTERID = RS.WORKCENTERID
                                            AND RS.WORKCENTERID != -1

            WHERE   RS.SKILLID  = @SkillId

            ORDER BY RS.SLNO    ASC";


        // ── GET_BY_ROUTING ────────────────────────────────────────
        // Returns all skill requirements for a given routing/process.
        // This is the primary use case for this table — used by
        // Coverage and Qualification reports to check which skills
        // are needed for each production routing.
        // FIX [7]: New query — was missing entirely
        // Index hint : IX_MROUTINGSKILL_ROUTINGID  (ROUTINGID, SKILLID)
        // ────────────────────────────────────────────────────────
        public const string GET_BY_ROUTING = @"
            SELECT
                RS.MAPID                AS MapId,
                RS.SKILLID              AS SkillId,
                RS.SLNO                 AS SlNo,
                RS.ROUTINGID            AS RoutingId,
                RS.ROUTINGOPCODE        AS RoutingOpCode,
                RS.ROUTINGOPID          AS RoutingOpId,
                RS.WORKCENTERID         AS WorkCenterId,
                RS.ISPREREQUISITE       AS IsPrerequisite,
                RS.MINLEVELREQUIRED     AS MinLevelRequired,
                RS.REMARKS              AS Remarks,
                S.SKILLNAME             AS SkillName,
                S.SKILLCODE             AS SkillCode,
                S.SKILLSCOPE            AS SkillScope,
                SL.SLEVELID             AS SkillLevelId,
                SL.LEVELLABEL           AS LevelLabel,
                SL.LEVELDESCRIPTOR      AS LevelDescriptor,
                R.ROUTINGNAME           AS RoutingName,
                RO.OPNAME               AS RoutingOpName,
                WC.WORKCENTERNAME       AS WorkCenterName

            FROM        MROUTINGSKILL   RS
            INNER JOIN  MSKILL          S   ON  S.SKILLID       = RS.SKILLID
                                            AND S.STATUS        = 1
                                            AND S.TENANTID      = @TenantId
            LEFT  JOIN  MSKILLLEVEL     SL  ON  SL.SKILLID      = RS.SKILLID
                                            AND SL.SLNO         = RS.MINLEVELREQUIRED
            LEFT  JOIN  MROUTING        R   ON  R.ROUTINGID     = RS.ROUTINGID
                                            AND RS.ROUTINGID   != -1
            LEFT  JOIN  MROUTINGOP      RO  ON  RO.ROUTINGOPID  = RS.ROUTINGOPID
                                            AND RS.ROUTINGOPID != -1
            LEFT  JOIN  MWORKCENTER     WC  ON  WC.WORKCENTERID = RS.WORKCENTERID
                                            AND RS.WORKCENTERID != -1

            WHERE   RS.ROUTINGID    = @RoutingId

            ORDER BY RS.ROUTINGOPCODE   ASC,
                     RS.SLNO            ASC";


        // ── GET_BY_ID ────────────────────────────────────────────
        // Single row fetch by PK — used after insert/update to
        // return saved state to the API caller.
        // FIX [8]: New query — was missing entirely
        // ────────────────────────────────────────────────────────
        public const string GET_BY_ID = @"
            SELECT
                RS.MAPID                AS MapId,
                RS.SKILLID              AS SkillId,
                RS.SLNO                 AS SlNo,
                RS.ROUTINGID            AS RoutingId,
                RS.ROUTINGOPCODE        AS RoutingOpCode,
                RS.ROUTINGOPID          AS RoutingOpId,
                RS.WORKCENTERID         AS WorkCenterId,
                RS.ISPREREQUISITE       AS IsPrerequisite,
                RS.MINLEVELREQUIRED     AS MinLevelRequired,
                RS.REMARKS              AS Remarks,
                S.SKILLNAME             AS SkillName,
                S.SKILLCODE             AS SkillCode,
                S.SKILLSCOPE            AS SkillScope,
                SL.SLEVELID             AS SkillLevelId,
                SL.LEVELLABEL           AS LevelLabel,
                SL.LEVELDESCRIPTOR      AS LevelDescriptor,
                R.ROUTINGNAME           AS RoutingName,
                RO.OPNAME               AS RoutingOpName,
                WC.WORKCENTERNAME       AS WorkCenterName

            FROM        MROUTINGSKILL   RS
            INNER JOIN  MSKILL          S   ON  S.SKILLID       = RS.SKILLID
            LEFT  JOIN  MSKILLLEVEL     SL  ON  SL.SKILLID      = RS.SKILLID
                                            AND SL.SLNO         = RS.MINLEVELREQUIRED
            LEFT  JOIN  MROUTING        R   ON  R.ROUTINGID     = RS.ROUTINGID
                                            AND RS.ROUTINGID   != -1
            LEFT  JOIN  MROUTINGOP      RO  ON  RO.ROUTINGOPID  = RS.ROUTINGOPID
                                            AND RS.ROUTINGOPID != -1
            LEFT  JOIN  MWORKCENTER     WC  ON  WC.WORKCENTERID = RS.WORKCENTERID
                                            AND RS.WORKCENTERID != -1

            WHERE   RS.MAPID    = @MapId
            AND     RS.SKILLID  = @SkillId";


        // ── INSERT ───────────────────────────────────────────────
        // Inserts exactly the 10 DDL columns — no standard fields.
        // SLNO is a sequence number — caller manages ordering.
        // ────────────────────────────────────────────────────────
        public const string INSERT = @"
            INSERT INTO MROUTINGSKILL
            (
                MAPID,          SKILLID,        SLNO,
                ROUTINGID,      ROUTINGOPCODE,  ROUTINGOPID,
                WORKCENTERID,   ISPREREQUISITE, MINLEVELREQUIRED,
                REMARKS
            )
            VALUES
            (
                @MapId,         @SkillId,       @SlNo,
                @RoutingId,     @RoutingOpCode, @RoutingOpId,
                @WorkCenterId,  @IsPrerequisite, @MinLevelRequired,
                @Remarks
            )";


        // ── UPDATE ───────────────────────────────────────────────
        // Updates editable columns only.
        // SKILLID is the FK anchor — not updatable.
        // Use DELETE + INSERT to change the skill association.
        // FIX [9]: New query — was missing entirely
        // ────────────────────────────────────────────────────────
        public const string UPDATE = @"
            UPDATE  MROUTINGSKILL
            SET     SLNO                = @SlNo,
                    ROUTINGID           = @RoutingId,
                    ROUTINGOPCODE       = @RoutingOpCode,
                    ROUTINGOPID         = @RoutingOpId,
                    WORKCENTERID        = @WorkCenterId,
                    ISPREREQUISITE      = @IsPrerequisite,
                    MINLEVELREQUIRED    = @MinLevelRequired,
                    REMARKS             = @Remarks
            WHERE   MAPID       = @MapId
            AND     SKILLID     = @SkillId";



        public const string GET_SELECT_LIST = @"SELECT
                RS.MAPID                AS MapId,
                RS.SKILLID              AS SkillId,
                RS.SLNO                 AS SlNo,
                RS.ROUTINGID            AS RoutingId,
                RS.ROUTINGOPCODE        AS RoutingOpCode,
                RS.ROUTINGOPID          AS RoutingOpId,
                RS.WORKCENTERID         AS WorkCenterId,
                RS.ISPREREQUISITE       AS IsPrerequisite,
                RS.MINLEVELREQUIRED     AS MinLevelRequired,
                RS.REMARKS              AS Remarks,
                S.SKILLCODE             AS SkillCode,
                S.SKILLNAME             AS SkillName,
                SL.LEVELLABEL           AS LevelLabel
            FROM        MROUTINGSKILL   RS
            INNER JOIN  MSKILL          S   ON  S.SKILLID   = RS.SKILLID
                                            AND S.TENANTID  = @TenantId
                                            AND S.STATUS    = 1
            LEFT JOIN   MSKILLLEVEL     SL  ON  SL.SKILLID  = RS.SKILLID
                                            AND SL.SLNO     = RS.MINLEVELREQUIRED
            WHERE 1 = 1
                {DYNAMIC_WHERE}
            ORDER BY SlNo ASC";


        // ── DELETE_BY_SKILL ──────────────────────────────────────
        // Bulk-removes all routing stage mappings for a skill.
        // Caller must run CHECK_IN_USE before calling this.
        // ────────────────────────────────────────────────────────
        public const string DELETE_BY_SKILL = @"
            DELETE FROM MROUTINGSKILL
            WHERE   SKILLID = @SkillId";


        // ── DELETE_BY_ID ─────────────────────────────────────────
        // Removes a single mapping row by primary key.
        // FIX [6]: Added AND SKILLID = @SkillId safety guard —
        //          prevents accidental cross-skill deletes if a
        //          wrong MapId is passed.
        // ────────────────────────────────────────────────────────
        public const string DELETE_BY_ID = @"
            DELETE FROM MROUTINGSKILL
            WHERE   MAPID   = @MapId";


        // ── CHECK_IN_USE ─────────────────────────────────────────
        // Checks whether any active employee has a skill profile
        // for this skill — used before deleting all routing
        // mappings for a skill to warn that active profiles exist.
        // Also checks TASSESSMENT for assessment history.
        // FIX [10]: New query — was missing entirely
        //
        // Returns total count. If > 0 warn before deleting.
        // (Does not block — routing maps can exist independently
        //  of employee profiles, but deletion should be flagged.)
        // ────────────────────────────────────────────────────────
        public const string CHECK_IN_USE = @"
            SELECT
                (
                    -- Active employee skill profiles for this skill
                    SELECT COUNT(1)
                    FROM   MEMPLOYEESKILLPROFILE
                    WHERE  SKILLID  = @SkillId
                    AND    STATUS   = 1
                )
                +
                (
                    -- Assessment history records for this skill
                    SELECT COUNT(1)
                    FROM   TASSESSMENT
                    WHERE  SKILLID  = @SkillId
                    AND    STATUS   = 1
                )
            AS TotalInUseCount";

        public const string GET_ROUTINGSKILL_LIST_ALL = @"
SELECT
    RS.MAPID                AS MapId,
    RS.SKILLID              AS SkillId,
    RS.SLNO                 AS SlNo,
    RS.ROUTINGID            AS RoutingId,
    RS.ROUTINGOPCODE        AS RoutingOpCode,
    RS.ROUTINGOPID          AS RoutingOpId,
    RS.WORKCENTERID         AS WorkCenterId,
    RS.ISPREREQUISITE       AS IsPrerequisite,
    RS.MINLEVELREQUIRED     AS MinLevelRequired,
    RS.REMARKS              AS Remarks,


    S.SKILLNAME             AS SkillName,
    S.SKILLCODE             AS SkillCode,
    S.SKILLSCOPE            AS SkillScope,

    SL.SLEVELID             AS SkillLevelId,
    SL.LEVELLABEL           AS LevelLabel,
    SL.LEVELDESCRIPTOR      AS LevelDescriptor,

    R.ROUTINGNAME           AS RoutingName,
    WC.WORKCENTERNAME       AS WorkCenterName

FROM        MROUTINGSKILL   RS
INNER JOIN  MSKILL          S   ON  S.SKILLID = RS.SKILLID
                                AND S.STATUS  = 1
LEFT JOIN   MSKILLLEVEL     SL  ON  SL.SKILLID = RS.SKILLID
                                AND SL.SLNO    = RS.MINLEVELREQUIRED
LEFT JOIN   MROUTING        R   ON  R.ROUTINGID = RS.ROUTINGID
                                AND RS.ROUTINGID != -1
LEFT JOIN   MWORKCENTER     WC  ON  WC.WORKCENTERID = RS.WORKCENTERID
                                AND RS.WORKCENTERID != -1
ORDER BY    RS.MAPID;";


        public const string GET_ROUTINGSKILL_LIST_PAGED = @"
SELECT
    RS.MAPID                AS MapId,
    RS.SKILLID              AS SkillId,
    RS.SLNO                 AS SlNo,
    RS.ROUTINGID            AS RoutingId,
    RS.ROUTINGOPCODE        AS RoutingOpCode,
    RS.ROUTINGOPID          AS RoutingOpId,
    RS.WORKCENTERID         AS WorkCenterId,
    RS.ISPREREQUISITE       AS IsPrerequisite,
    RS.MINLEVELREQUIRED     AS MinLevelRequired,
    RS.REMARKS              AS Remarks,

    S.SKILLNAME             AS SkillName,
    S.SKILLCODE             AS SkillCode,
    S.SKILLSCOPE            AS SkillScope,

    SL.SLEVELID             AS SkillLevelId,
    SL.LEVELLABEL           AS LevelLabel,
    SL.LEVELDESCRIPTOR      AS LevelDescriptor,

    R.ROUTINGNAME           AS RoutingName,
    WC.WORKCENTERNAME       AS WorkCenterName

FROM        MROUTINGSKILL   RS
INNER JOIN  MSKILL          S   ON  S.SKILLID = RS.SKILLID
                                AND S.STATUS  = 1
LEFT JOIN   MSKILLLEVEL     SL  ON  SL.SKILLID = RS.SKILLID
                                AND SL.SLNO    = RS.MINLEVELREQUIRED
LEFT JOIN   MROUTING        R   ON  R.ROUTINGID = RS.ROUTINGID
                                AND RS.ROUTINGID != -1
LEFT JOIN   MWORKCENTER     WC  ON  WC.WORKCENTERID = RS.WORKCENTERID
                                AND RS.WORKCENTERID != -1
ORDER BY    RS.MAPID
OFFSET      (@FirstNumber - 1) ROWS
FETCH NEXT  @MaxResult ROWS ONLY
OPTION (RECOMPILE);";


        // ── GET_SLNO_BY_SLEVELID ─────────────────────────────────
        // Resolves the level sequence number (SLNO) from a
        // MSKILLLEVEL primary key (SLEVELID).
        // Used in Save to map SkillLevelId → MinLevelRequired.
        // ────────────────────────────────────────────────────────
        public const string GET_SLNO_BY_SLEVELID = @"
            SELECT SLNO FROM MSKILLLEVEL WHERE SLEVELID = @SkillLevelId";

        public const string GET_ROUTINGSKILL_COUNT = @"
SELECT COUNT(1)
FROM        MROUTINGSKILL   RS
INNER JOIN  MSKILL          S   ON  S.SKILLID = RS.SKILLID
                                AND S.STATUS  = 1
LEFT JOIN   MSKILLLEVEL     SL  ON  SL.SKILLID = RS.SKILLID
                                AND SL.SLNO    = RS.MINLEVELREQUIRED
LEFT JOIN   MROUTING        R   ON  R.ROUTINGID = RS.ROUTINGID
                                AND RS.ROUTINGID != -1
LEFT JOIN   MWORKCENTER     WC  ON  WC.WORKCENTERID = RS.WORKCENTERID
                                AND RS.WORKCENTERID != -1
";
    }
}