// ============================================================
// GoodBooks ERP — Skill Management Module
// Query  : SkillQB
// Table  : MSKILL (S3) — primary master
//          MSKILLLEVEL (S4) — child  [NO standard fields]
// Updated: Aligned to new DDL schema v1.0
//
// FIXES APPLIED (vs old script):
//   [1] S.VERSIONNO          -> S.VERSION       (correct DDL column name)
//   [2] G.SKILLGROUPID       -> G.GROUPID        (correct DDL PK on MSKILLGROUP)
//   [3] SKILLLEVELID         -> SLEVELID         (correct DDL PK on MSKILLLEVEL)
//   [4] LEVELNO              -> SLNO             (correct DDL column on MSKILLLEVEL)
//   [5] MSKILLLEVEL.TENANTID -> REMOVED          (child table has NO standard fields)
//   [6] INSERT_LEVEL columns -> stripped to only the 8 DDL columns on MSKILLLEVEL
//   [7] DELETE_LEVELS        -> TENANTID filter removed (column does not exist)
//   [8] MEMPLOYEESKILLPROFILEPROFILE-> MEMPLOYEESKILLPROFILE   (correct table name)
//   [9] SOFT_DELETE STATUS=5 -> STATUS=2         (2=Deleted per DDL; 5=Archived)
//  [10] GET_LIST             -> added all missing standard field SELECTs
//  [11] GET_BY_ID            -> added CREATEDON, MODIFIEDON
// ============================================================

namespace SkillManagementDAL.Query.Skill
{
    public class SkillQB
    {
        // ── GET_LIST ─────────────────────────────────────────────
        // Returns all active skills for a tenant with optional
        // domain and group filters.
        // Index hint : IX_MSKILL_DOMAINID_STATUS  (DOMAINID, STATUS, MATRIXSEQNO)
        //              IX_MSKILL_GROUPID_STATUS   (GROUPID,  STATUS, MATRIXSEQNO)
        //              IX_MSKILL_TENANTID_STATUS  (TENANTID, STATUS)
        // ────────────────────────────────────────────────────────
        // ── GET_LIST ─────────────────────────────────────────────
        // Returns all active skills for a tenant with joined
        // domain/group display fields.
        //
        // Includes:
        //   • Full MSKILL mapped columns
        //   • GroupName
        //   • DomainName
        //   • Notes
        //
        // Ordering:
        //   MATRIXSEQNO -> SKILLCODE
        //
        // Index Hint:
        //   IX_MSKILL_GROUPID
        //   IX_MSKILL_DOMAINID
        //   IX_MSKILL_TENANTID
        // ────────────────────────────────────────────────────────
        public const string GET_LIST = @"
    SELECT
        -- =====================================================
        -- MSKILL (S3) — Primary Skill Master
        -- =====================================================

        S.SKILLID           AS SkillId,
        S.GROUPID           AS GroupId,
        S.DOMAINID          AS DomainId,

        S.SKILLCODE         AS SkillCode,
        S.SKILLNAME         AS SkillName,
        S.SKILLNAMEFULL     AS SkillNameFull,

        S.SKILLSCOPE        AS SkillScope,
        S.LEVELCOUNT        AS LevelCount,

        S.ISSPECIALCHAR     AS IsSpecialChar,

        S.MATRIXSEQNO       AS MatrixSeqNo,

        S.ISACTIVE          AS IsActive,

        S.REMARKS           AS Remarks,
        S.NOTES             AS Notes,

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

        S.CREATEDBYID       AS CreatedById,
        S.CREATEDON         AS CreatedOn,

        S.MODIFIEDBYID      AS ModifiedById,
        S.MODIFIEDON        AS ModifiedOn,

        S.SOURCETYPE        AS SourceType,
        S.TENANTID          AS TenantId,

        -- =====================================================
        -- MSKILLGROUP (S2)
        -- =====================================================

        G.GROUPNAME         AS GroupName,

        -- =====================================================
        -- MSKILLDOMAIN (S1)
        -- =====================================================

        D.DOMAINNAME        AS DomainName

    FROM        MSKILL          S

    -- =========================================================
    -- Skill Group Join
    -- FIX:
    --     G.GROUPID used instead of old G.SKILLGROUPID
    -- =========================================================
    INNER JOIN  MSKILLGROUP     G
            ON  G.GROUPID       = S.GROUPID
            AND G.STATUS        = 1

    -- =========================================================
    -- Skill Domain Join
    -- =========================================================
    INNER JOIN  MSKILLDOMAIN    D
            ON  D.DOMAINID      = S.DOMAINID
            AND D.STATUS        = 1

    -- =========================================================
    -- Tenant Isolation
    -- =========================================================
    WHERE   S.TENANTID          = @TenantId

    -- =========================================================
    -- Ordering
    -- =========================================================
    ORDER BY
            S.MATRIXSEQNO   ASC,
            S.SKILLCODE     ASC";


        // ── GET_BY_ID ────────────────────────────────────────────
        // Returns a single Skill Master record by primary key.
        //
        // Used For:
        //   • Edit screen load
        //   • View details
        //   • Post-save reload
        //
        // Includes:
        //   • Full MSKILL mapped fields
        //   • GroupName
        //   • DomainName
        //   • Notes
        //
        // Tenant secured.
        //
        // FIXES:
        //   • GROUPID join corrected
        //   • Added NOTES
        //   • Added full audit fields
        // ────────────────────────────────────────────────────────
        public const string GET_BY_ID = @"
    SELECT

        -- =====================================================
        -- MSKILL (S3) — Primary Skill Master
        -- =====================================================

        S.SKILLID           AS SkillId,

        S.GROUPID           AS GroupId,
        S.DOMAINID          AS DomainId,

        S.SKILLCODE         AS SkillCode,
        S.SKILLNAME         AS SkillName,
        S.SKILLNAMEFULL     AS SkillNameFull,

        S.SKILLSCOPE        AS SkillScope,

        S.LEVELCOUNT        AS LevelCount,

        S.ISSPECIALCHAR     AS IsSpecialChar,

        S.MATRIXSEQNO       AS MatrixSeqNo,

        S.ISACTIVE          AS IsActive,

        S.REMARKS           AS Remarks,
        S.NOTES             AS Notes,

        S.VERSION           AS Version,

        S.STATUS            AS Status,

        S.SORTORDER         AS SortOrder,

        S.CREATEDBYID       AS CreatedById,
        S.CREATEDON         AS CreatedOn,

        S.MODIFIEDBYID      AS ModifiedById,
        S.MODIFIEDON        AS ModifiedOn,

        S.SOURCETYPE        AS SourceType,

        S.TENANTID          AS TenantId,

        -- =====================================================
        -- MSKILLGROUP (S2)
        -- =====================================================

        G.GROUPNAME         AS GroupName,

        -- =====================================================
        -- MSKILLDOMAIN (S1)
        -- =====================================================

        D.DOMAINNAME        AS DomainName

    FROM        MSKILL          S

    -- =========================================================
    -- Skill Group Join
    -- =========================================================
    INNER JOIN  MSKILLGROUP     G
            ON  G.GROUPID       = S.GROUPID

    -- =========================================================
    -- Skill Domain Join
    -- =========================================================
    INNER JOIN  MSKILLDOMAIN    D
            ON  D.DOMAINID      = S.DOMAINID

    -- =========================================================
    -- PK + Tenant Filter
    -- =========================================================
    WHERE   S.SKILLID           = @SkillId
    AND     S.TENANTID          = @TenantId";


        // ── GET_LEVELS ───────────────────────────────────────────
        // Returns all proficiency levels associated
        // with a specific skill.
        //
        // Parent Table:
        //      MSKILL (S3)
        //
        // Child Table:
        //      MSKILLLEVEL (S4)
        //
        // IMPORTANT:
        //      MSKILLLEVEL is a PURE CHILD TABLE.
        //
        //      It DOES NOT contain:
        //          • TENANTID
        //          • STATUS
        //          • VERSION
        //          • SORTORDER
        //          • CREATEDBYID
        //          • CREATEDON
        //          • MODIFIEDBYID
        //          • MODIFIEDON
        //          • SOURCETYPE
        //
        // FIXES APPLIED:
        //      [1] SKILLLEVELID -> SLEVELID
        //      [2] LEVELNO      -> SLNO
        //      [3] Added LEVELCOUNT
        //      [4] Removed invalid TENANTID filtering
        //
        // Ordering:
        //      SLNO ASC
        //
        // Index Hint:
        //      IX_MSKILLLEVEL_SKILLID (SKILLID, SLNO)
        // ────────────────────────────────────────────────────────
        public const string GET_LEVELS = @"
    SELECT

        -- =====================================================
        -- MSKILLLEVEL (S4)
        -- =====================================================

        SLEVELID               AS SLevelId,

        SKILLID                AS SkillId,

        SLNO                   AS SlNo,

        LEVELLABEL             AS LevelLabel,

        LEVELCOUNT             AS LevelCount,

        LEVELDESCRIPTOR        AS LevelDescriptor,

        ISMINREQUIRED          AS IsMinRequired,

        CANTRAINOTHERS         AS CanTrainOthers,

        REMARKS                AS Remarks

    FROM    MSKILLLEVEL

    -- =========================================================
    -- Skill Filter
    -- =========================================================
    WHERE   SKILLID            = @SkillId

    -- =========================================================
    -- Ordering
    -- =========================================================
    ORDER BY
            SLNO ASC";


        // ── GET_SELECT_LIST ──────────────────────────────────────
        // Lightweight skill dropdown / lookup query.
        //
        // Purpose:
        //      Used for:
        //          • Dropdowns
        //          • Auto-complete
        //          • Lookup popups
        //          • Matrix skill selection
        //
        // Returns:
        //      Minimal fields required for UI selection.
        //
        // Filters:
        //      • Tenant isolated
        //      • Active records only
        //
        // Ordering:
        //      MATRIXSEQNO
        //      SKILLNAME
        //
        // Index Hint:
        //      IX_MSKILL_TENANTID
        //
        // Notes:
        //      • LEVELCOUNT = total number of levels
        //        configured for the skill.
        //
        //      • ISSPECIALCHAR controls matrix
        //        special indicator rendering.
        // ────────────────────────────────────────────────────────
        public const string GET_SELECT_LIST = @"
            SELECT
                SKILLID         AS Id,
                SKILLNAME       AS Name,
                SKILLCODE       AS Code,
                SKILLSCOPE      AS SkillScope,
                LEVELCOUNT      AS LevelCount,
                ISSPECIALCHAR   AS IsSpecialChar,
                MATRIXSEQNO     AS MatrixSeqNo
            FROM    MSKILL
            WHERE   TENANTID    = @TenantId
                AND STATUS      = 1
                AND ISACTIVE    = 0
            {DYNAMIC_WHERE}
            ORDER BY MatrixSeqNo    ASC,
                     Name           ASC";


        // ── INSERT ───────────────────────────────────────────────
        // Inserts a new Skill Master record into MSKILL.
        //
        // Notes:
        //      • VERSION initialized as 0
        //      • DOMAINID is denormalized from GROUPID
        //      • Caller must pass matching DOMAINID
        //      • NOTES field added
        //      • Audit fields maintained at parent level
        //
        // Business Rules:
        //      • LEVELCOUNT = total number of levels
        //      • SKILLSCOPE:
        //            0 = General
        //            1 = ProductFamily
        //            2 = Model
        //
        //      • ISACTIVE:
        //            0 = Inactive
        //            1 = Active
        //
        //      • STATUS:
        //            0 = Pending
        //            1 = Active
        //            2 = Deleted
        //            3 = Amended
        //            4 = Inactive
        //            5 = Archived
        //
        // FIXES APPLIED:
        //      [1] VERSION used instead of VERSIONNO
        //      [2] NOTES column added
        //      [3] Full schema alignment with DTO
        // ────────────────────────────────────────────────────────
        public const string INSERT = @"
    INSERT INTO MSKILL
    (
        -- =====================================================
        -- Primary / Relationship Fields
        -- =====================================================

        SKILLID,
        GROUPID,
        DOMAINID,

        -- =====================================================
        -- Business Fields
        -- =====================================================

        SKILLCODE,
        SKILLNAME,
        SKILLNAMEFULL,

        SKILLSCOPE,

        LEVELCOUNT,

        ISSPECIALCHAR,

        MATRIXSEQNO,

        ISACTIVE,

        REMARKS,
        NOTES,

        -- =====================================================
        -- Standard / Audit Fields
        -- =====================================================

        VERSION,

        STATUS,

        SORTORDER,

        CREATEDBYID,
        CREATEDON,

        MODIFIEDBYID,
        MODIFIEDON,

        SOURCETYPE,

        TENANTID
    )
    VALUES
    (
        -- =====================================================
        -- Primary / Relationship Fields
        -- =====================================================

        @SkillId,
        @GroupId,
        @DomainId,

        -- =====================================================
        -- Business Fields
        -- =====================================================

        @SkillCode,
        @SkillName,
        @SkillNameFull,

        @SkillScope,

        @LevelCount,

        @IsSpecialChar,

        @MatrixSeqNo,

        @IsActive,

        @Remarks,
        @Notes,

        -- =====================================================
        -- Standard / Audit Fields
        -- =====================================================

        0,

        @Status,

        @SortOrder,

        @CreatedById,
        @CreatedOn,

        @ModifiedById,
        @ModifiedOn,

        @SourceType,

        @TenantId
    )";

        // ── UPDATE ───────────────────────────────────────────────
        // Updates an existing Skill Master record.
        //
        // Notes:
        //      • VERSION incremented automatically
        //      • DOMAINID updated together with GROUPID
        //      • Caller must pass matching DOMAINID
        //      • NOTES field added
        //
        // Business Rules:
        //      • LEVELCOUNT = total number of levels
        //      • SKILLSCOPE:
        //            0 = General
        //            1 = ProductFamily
        //            2 = Model
        //
        //      • ISACTIVE:
        //            0 = Inactive
        //            1 = Active
        //
        //      • STATUS:
        //            0 = Pending
        //            1 = Active
        //            2 = Deleted
        //            3 = Amended
        //            4 = Inactive
        //            5 = Archived
        //
        // FIXES APPLIED:
        //      [1] VERSION = VERSION + 1
        //      [2] NOTES column added
        //      [3] Full DTO alignment
        //      [4] Proper tenant isolation
        // ────────────────────────────────────────────────────────
        public const string UPDATE = @"

    UPDATE  MSKILL

    SET

        -- =====================================================
        -- Relationship Fields
        -- =====================================================

        GROUPID            = @GroupId,

        DOMAINID           = @DomainId,

        -- =====================================================
        -- Business Fields
        -- =====================================================

        SKILLCODE          = @SkillCode,

        SKILLNAME          = @SkillName,

        SKILLNAMEFULL      = @SkillNameFull,

        SKILLSCOPE         = @SkillScope,

        LEVELCOUNT         = @LevelCount,

        ISSPECIALCHAR      = @IsSpecialChar,

        MATRIXSEQNO        = @MatrixSeqNo,

        ISACTIVE           = @IsActive,

        REMARKS            = @Remarks,

        NOTES              = @Notes,

        -- =====================================================
        -- Standard / Audit Fields
        -- =====================================================

        STATUS             = @Status,

        SORTORDER          = @SortOrder,

        VERSION            = VERSION + 1,

        MODIFIEDBYID       = @ModifiedById,

        MODIFIEDON         = @ModifiedOn

    WHERE

        SKILLID            = @SkillId

    AND TENANTID           = @TenantId";


        // ── SOFT_DELETE ──────────────────────────────────────────
        // FIX [9]: STATUS = 2  (Deleted per DDL)
        //          STATUS = 5  is Archived — wrong for a delete action
        // FIX [1]: VERSION = VERSION + 1  (not VERSIONNO)
        // ────────────────────────────────────────────────────────
        public const string SOFT_DELETE = @"
            UPDATE  MSKILL
            SET     STATUS          = 2,
                    VERSION         = VERSION + 1,
                    MODIFIEDBYID    = @ModifiedById,
                    MODIFIEDON      = GETDATE()
            WHERE   SKILLID     = @SkillId
            AND     TENANTID    = @TenantId";


        // ── DELETE_LEVELS ────────────────────────────────────────
        // Removes all level rows for a skill before re-inserting.
        // MSKILLLEVEL is a child table — NO TENANTID column.
        // FIX [5]: TENANTID filter REMOVED (column does not exist)
        // ────────────────────────────────────────────────────────
        public const string DELETE_LEVELS = @"
    DELETE FROM MSKILLLEVEL
    WHERE   SKILLID = @SkillId";


        // ── INSERT_LEVEL ─────────────────────────────────────────
        // MSKILLLEVEL is a CHILD table — it has ONLY these 8
        // business columns per DDL.  NO standard fields at all:
        // no TENANTID, no STATUS, no VERSION, no SORTORDER,
        // no SOURCETYPE, no CREATEDBYID, no MODIFIEDBYID.
        // FIX [3]: SKILLLEVELID  -> SLEVELID
        // FIX [4]: LEVELNO       -> SLNO
        // FIX [6]: All standard-field columns REMOVED from INSERT
        // ────────────────────────────────────────────────────────
        // ── INSERT_LEVEL ─────────────────────────────────────────
        // Inserts a Skill Level row into MSKILLLEVEL.
        //
        // Parent Table:
        //      MSKILL
        //
        // Child Table:
        //      MSKILLLEVEL
        //
        // IMPORTANT:
        //      MSKILLLEVEL is a PURE CHILD TABLE.
        //
        //      It contains ONLY:
        //          • SLEVELID
        //          • SKILLID
        //          • SLNO
        //          • LEVELLABEL
        //          • LEVELCOUNT
        //          • LEVELDESCRIPTOR
        //          • ISMINREQUIRED
        //          • CANTRAINOTHERS
        //          • REMARKS
        //
        //      It DOES NOT contain:
        //          • TENANTID
        //          • STATUS
        //          • VERSION
        //          • SORTORDER
        //          • CREATEDBYID
        //          • CREATEDON
        //          • MODIFIEDBYID
        //          • MODIFIEDON
        //          • SOURCETYPE
        //
        // Business Rules:
        //      • SLNO must be unique per SKILLID
        //      • LEVELCOUNT stores the measurable level value
        //      • ISMINREQUIRED:
        //            0 = No
        //            1 = Yes
        //
        //      • CANTRAINOTHERS:
        //            0 = No
        //            1 = Yes
        //
        // FIXES APPLIED:
        //      [1] Added LEVELCOUNT column
        //      [2] Full DDL alignment
        //      [3] NULL-safe REMARKS handling
        // ────────────────────────────────────────────────────────
        public const string INSERT_LEVEL = @"

    INSERT INTO MSKILLLEVEL
    (
        -- =====================================================
        -- Primary / Relationship Fields
        -- =====================================================

        SLEVELID,

        SKILLID,

        -- =====================================================
        -- Business Fields
        -- =====================================================

        SLNO,

        LEVELLABEL,

        LEVELCOUNT,

        LEVELDESCRIPTOR,

        ISMINREQUIRED,

        CANTRAINOTHERS,

        REMARKS
    )

    VALUES
    (
        -- =====================================================
        -- Primary / Relationship Fields
        -- =====================================================

        @SLevelId,

        @SkillId,

        -- =====================================================
        -- Business Fields
        -- =====================================================

        @SlNo,

        @LevelLabel,

        @LevelCount,

        @LevelDescriptor,

        @IsMinRequired,

        @CanTrainOthers,

        ISNULL(@Remarks, '')
    )";

        // ── CHECK_IN_USE ─────────────────────────────────────────
        // Checks if any active employee profile references
        // this skill — used before allowing a delete.
        // FIX [8]: MEMPLOYEESKILLPROFILEPROFILE -> MEMPLOYEESKILLPROFILE  (correct table name)
        // Index hint : IX_MEMPLOYEESKILLPROFILE_SKILLID_STATUS
        //              (SKILLID, TENANTID, PROFILESTATUS)
        // ────────────────────────────────────────────────────────
        public const string CHECK_IN_USE = @"
            SELECT  COUNT(1)
            FROM    MEMPLOYEESKILLPROFILE
            WHERE   SKILLID     = @SkillId
            AND     TENANTID    = @TenantId
            AND     STATUS      = 1";

        public const string DELETE_SKILL = @"

-- STEP 1: Delete deepest dependencies first

DELETE FROM TASSESSMENT
WHERE SKILLID = @SkillId;

DELETE FROM MSKILLPRODUCT
WHERE SKILLID = @SkillId;

DELETE FROM MSKILLLEVEL
WHERE SKILLID = @SkillId;

DELETE FROM MSELECTIONROUNDDETAIL
WHERE SKILLID = @SkillId;

DELETE FROM MROUTINGSKILL
WHERE SKILLID = @SkillId;

DELETE FROM MPROGRAMMEPREREQUISITE
WHERE PREREQSKILLID = @SkillId;

DELETE FROM MJOBROLESKILL
WHERE SKILLID = @SkillId;

DELETE FROM MJOBPROFILEDETAIL
WHERE SKILLID = @SkillId;

DELETE FROM MEMPLOYEESKILLPROFILE
WHERE SKILLID = @SkillId;

-- STEP 2: Finally delete from master

DELETE FROM MSKILL
WHERE SKILLID = @SkillId;";

        // ──────────────────────────────────────────────────────────
        // GET_SKILL_LIST_ALL
        // ──────────────────────────────────────────────────────────
        // Returns all active skills.
        //
        // Includes:
        //      • Full Skill master fields
        //      • GroupName
        //      • DomainName
        //      • Notes
        //
        // Filters:
        //      • STATUS = 1
        //
        // Ordering:
        //      • MATRIXSEQNO
        //      • SKILLCODE
        //
        // Notes:
        //      • Tenant-independent admin listing
        //      • Used for reports/export/master screens
        // ──────────────────────────────────────────────────────────
        public const string GET_SKILL_LIST_ALL = @"

    SELECT

        -- =====================================================
        -- MSKILL (S3)
        -- =====================================================

        S.SKILLID               AS SkillId,

        S.GROUPID               AS GroupId,

        S.DOMAINID              AS DomainId,

        S.SKILLCODE             AS SkillCode,

        S.SKILLNAME             AS SkillName,

        S.SKILLNAMEFULL         AS SkillNameFull,

        S.SKILLSCOPE            AS SkillScope,

        S.LEVELCOUNT            AS LevelCount,

        S.ISSPECIALCHAR         AS IsSpecialChar,

        S.MATRIXSEQNO           AS MatrixSeqNo,

        S.ISACTIVE              AS IsActive,

        S.REMARKS               AS Remarks,

        S.NOTES                 AS Notes,

        S.VERSION               AS Version,

        S.STATUS                AS Status,

        S.SORTORDER             AS SortOrder,

        S.CREATEDBYID           AS CreatedById,

        S.CREATEDON             AS CreatedOn,

        S.MODIFIEDBYID          AS ModifiedById,

        S.MODIFIEDON            AS ModifiedOn,

        S.SOURCETYPE            AS SourceType,

        S.TENANTID              AS TenantId,

        -- =====================================================
        -- MSKILLGROUP
        -- =====================================================

        G.GROUPNAME             AS GroupName,

        -- =====================================================
        -- MSKILLDOMAIN
        -- =====================================================

        D.DOMAINNAME            AS DomainName

    FROM        MSKILL              S

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

    INNER JOIN  MSKILLDOMAIN        D
            ON  D.DOMAINID          = S.DOMAINID
            AND D.STATUS            = 1

    WHERE       S.STATUS            = 1

    ORDER BY
                S.MATRIXSEQNO   ASC,
                S.SKILLCODE     ASC;";



        // ──────────────────────────────────────────────────────────
        // GET_SKILL_LIST_PAGED
        // ──────────────────────────────────────────────────────────
        // Returns paged skill list.
        //
        // Includes:
        //      • Full Skill master fields
        //      • GroupName
        //      • DomainName
        //      • Notes
        //
        // Filters:
        //      • STATUS = 1
        //
        // Paging:
        //      OFFSET/FETCH pagination
        //
        // Ordering:
        //      • MATRIXSEQNO
        //      • SKILLCODE
        //
        // Notes:
        //      • OPTION(RECOMPILE) added for paging optimization
        // ──────────────────────────────────────────────────────────
        public const string GET_SKILL_LIST_PAGED = @"

    SELECT

        -- =====================================================
        -- MSKILL (S3)
        -- =====================================================

        S.SKILLID               AS SkillId,

        S.GROUPID               AS GroupId,

        S.DOMAINID              AS DomainId,

        S.SKILLCODE             AS SkillCode,

        S.SKILLNAME             AS SkillName,

        S.SKILLNAMEFULL         AS SkillNameFull,

        S.SKILLSCOPE            AS SkillScope,

        S.LEVELCOUNT            AS LevelCount,

        S.ISSPECIALCHAR         AS IsSpecialChar,

        S.MATRIXSEQNO           AS MatrixSeqNo,

        S.ISACTIVE              AS IsActive,

        S.REMARKS               AS Remarks,

        S.NOTES                 AS Notes,

        S.VERSION               AS Version,

        S.STATUS                AS Status,

        S.SORTORDER             AS SortOrder,

        S.CREATEDBYID           AS CreatedById,

        S.CREATEDON             AS CreatedOn,

        S.MODIFIEDBYID          AS ModifiedById,

        S.MODIFIEDON            AS ModifiedOn,

        S.SOURCETYPE            AS SourceType,

        S.TENANTID              AS TenantId,

        -- =====================================================
        -- MSKILLGROUP
        -- =====================================================

        G.GROUPNAME             AS GroupName,

        -- =====================================================
        -- MSKILLDOMAIN
        -- =====================================================

        D.DOMAINNAME            AS DomainName

    FROM        MSKILL              S

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

    INNER JOIN  MSKILLDOMAIN        D
            ON  D.DOMAINID          = S.DOMAINID
            AND D.STATUS            = 1

    WHERE       S.STATUS            = 1

    ORDER BY
                S.MATRIXSEQNO   ASC,
                S.SKILLCODE     ASC

    OFFSET      (@FirstNumber - 1) ROWS

    FETCH NEXT  @MaxResult ROWS ONLY

    OPTION (RECOMPILE);";



        // ──────────────────────────────────────────────────────────
        // GET_SKILL_COUNT
        // ──────────────────────────────────────────────────────────
        // Returns total active skill count.
        //
        // Used For:
        //      • Grid pagination
        //      • Dashboard metrics
        //      • Report totals
        //
        // Filters:
        //      • STATUS = 1
        // ──────────────────────────────────────────────────────────
        public const string GET_SKILL_COUNT = @"

    SELECT COUNT(1)

    FROM        MSKILL              S

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

    INNER JOIN  MSKILLDOMAIN        D
            ON  D.DOMAINID          = S.DOMAINID
            AND D.STATUS            = 1

    WHERE       S.STATUS            = 1;";
    }
}