// ============================================================
// GoodBooks ERP — Skill Management Module
// Query  : SkillLevelQB
// Table  : MSKILLLEVEL (S4) — child of MSKILL (S3)
// Updated: Aligned to new DDL schema v1.0
//
// CRITICAL — MSKILLLEVEL IS A CHILD TABLE:
//   NO TENANTID, VERSION, STATUS, SORTORDER, SOURCETYPE,
//   CREATEDBYID, MODIFIEDBYID on this table.
//   All 8 DDL columns: SLEVELID, SKILLID, SLNO, LEVELLABEL,
//   LEVELDESCRIPTOR, ISMINREQUIRED, CANTRAINOTHERS, REMARKS.
//
// FIXES APPLIED (vs old script):
//   [1]  UPDATE     : Added comment — SLNO is part of
//                     UK_MSKILLLEVEL_SKILLID_SLNO; updating it
//                     requires caller to ensure no duplicate SLNO
//                     exists for the same SKILLID first.
//   [2]  GET_BY_SKILL: Added ORDER BY SLNO ASC (explicit ASC)
//   [3]  Added GET_BY_ID       — post-insert/update single row fetch
//   [4]  Added CHECK_IN_USE    — blocks level delete when employees
//                                hold the level or roles require it
//
// NOTES:
//   * Tenant scoping on MSKILLLEVEL queries is done via the
//     parent MSKILL join when needed (see GET_BY_SKILL_TENANTED).
//   * Index hint: IX_MSKILLLEVEL_SKILLID  (SKILLID, SLNO)
// ============================================================

namespace SkillManagementDAL.Query.SkillLevel
{
    public class SkillLevelQB
    {
        // ── GET_BY_SKILL ─────────────────────────────────────────
        // Returns all proficiency levels for a skill ordered by
        // level number ascending (1 = lowest, 4 = highest).
        // No TENANTID filter — child table has no TENANTID column.
        // To scope by tenant, use GET_BY_SKILL_TENANTED below.
        // Index hint : IX_MSKILLLEVEL_SKILLID  (SKILLID, SLNO)
        // ────────────────────────────────────────────────────────
        public const string GET_BY_SKILL = @"
            SELECT
                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
            WHERE   SKILLID = @SkillId
            ORDER BY SLNO   ASC";


        // ── GET_BY_SKILL_TENANTED ─────────────────────────────────
        // Same as GET_BY_SKILL but scoped through the parent MSKILL
        // to ensure the skill belongs to the requesting tenant.
        // Use this in public-facing API endpoints where tenant
        // isolation must be enforced at the query level.
        // ────────────────────────────────────────────────────────
        public const string GET_BY_SKILL_TENANTED = @"
            SELECT
                SL.SLEVELID         AS SLevelId,
                SL.SKILLID          AS SkillId,
                SL.SLNO             AS SlNo,
                SL.LEVELLABEL       AS LevelLabel,
                SL.LEVELCOUNT       AS LevelCount,
                SL.LEVELDESCRIPTOR  AS LevelDescriptor,
                SL.ISMINREQUIRED    AS IsMinRequired,
                SL.CANTRAINOTHERS   AS CanTrainOthers,
                SL.REMARKS          AS Remarks
            FROM        MSKILLLEVEL SL
            INNER JOIN  MSKILL      S   ON  S.SKILLID   = SL.SKILLID
                                        AND S.TENANTID  = @TenantId
                                        AND S.STATUS    = 1
            WHERE   SL.SKILLID  = @SkillId
            ORDER BY SL.SLNO    ASC";


        // ── GET_BY_ID ────────────────────────────────────────────
        // Single level fetch by PK — used after insert/update
        // to return saved state to the API caller.
        // FIX [3]: New query — was missing entirely
        // ────────────────────────────────────────────────────────
        public const string GET_BY_ID = @"
            SELECT
                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
            WHERE   SLEVELID    = @SLevelId
            AND     SKILLID     = @SkillId";


        // ── INSERT ───────────────────────────────────────────────
        // Inserts exactly the 8 DDL columns — no standard fields.
        // UK_MSKILLLEVEL_SKILLID_SLNO enforces uniqueness — caller
        // must ensure SLNO is not already taken for this SKILLID.
        // SLNO must be <= parent MSKILL.LEVELCOUNT — enforced
        // at application layer (no DB constraint for this).
        // ────────────────────────────────────────────────────────
        public const string INSERT = @"
            INSERT INTO MSKILLLEVEL
            (
                SLEVELID,
                SKILLID,
                SLNO,
                LEVELLABEL,
                LEVELCOUNT,
                LEVELDESCRIPTOR,
                ISMINREQUIRED,
                CANTRAINOTHERS,
                REMARKS
            )
            VALUES
            (
                @SLevelId,
                @SkillId,
                @SlNo,
                @LevelLabel,
                @LevelCount,
                @LevelDescriptor,
                @IsMinRequired,
                @CanTrainOthers,
                @Remarks
            )";


        // ── UPDATE ───────────────────────────────────────────────
        // FIX [1]: SLNO is part of UK_MSKILLLEVEL_SKILLID_SLNO.
        // Updating SLNO is allowed but caller must ensure no other
        // level row already has the target SLNO for this SKILLID,
        // otherwise the unique constraint will throw.
        // Safest pattern: DELETE_BY_SKILL + bulk INSERT when
        // reordering all levels. Use UPDATE only for single-field
        // edits (label, descriptor, flags) where SLNO stays same.
        // ────────────────────────────────────────────────────────
        public const string UPDATE = @"
            UPDATE  MSKILLLEVEL
            SET     SLNO            = @SlNo,
                    LEVELLABEL      = @LevelLabel,
                    LEVELCOUNT      = @LevelCount,
                    LEVELDESCRIPTOR = @LevelDescriptor,
                    ISMINREQUIRED   = @IsMinRequired,
                    CANTRAINOTHERS  = @CanTrainOthers,
                    REMARKS         = @Remarks
            WHERE   SLEVELID    = @SLevelId
            AND     SKILLID     = @SkillId";


        // ── DELETE_BY_SKILL ──────────────────────────────────────
        // Bulk-removes all level rows for a skill.
        // Used before re-inserting a revised complete level set.
        // Caller must check CHECK_IN_USE before calling this —
        // deleting levels that employees currently hold or roles
        // require will leave those references dangling.
        // ────────────────────────────────────────────────────────
        public const string DELETE_BY_SKILL = @"
            DELETE FROM MSKILLLEVEL
            WHERE   SKILLID = @SkillId";


        // ── DELETE_BY_ID ─────────────────────────────────────────
        // Removes a single level row by primary key.
        // Caller must check CHECK_IN_USE before calling this.
        // ────────────────────────────────────────────────────────
        public const string DELETE_BY_ID = @"
            DELETE FROM MSKILLLEVEL
            WHERE   SLEVELID    = @SLevelId
            AND     SKILLID     = @SkillId";


        // ── CHECK_IN_USE ─────────────────────────────────────────
        // Checks all three tables that hold a level number
        // reference before allowing a level delete:
        //
        //   1. MEMPLOYEESKILLPROFILE  — employees currently at this level
        //      (CURRENTLEVNO = SLNO for this skill)
        //   2. MJOBROLESKILL   — job roles that require this level
        //      as their minimum (MINLEVNO = SLNO for this skill)
        //   3. MROUTINGSKILL   — routing operations that require
        //      this level as minimum (MINLEVELREQUIRED = SLNO)
        //
        // FIX [4]: New query — was missing entirely.
        // Returns total count. If > 0 block the delete.
        // ────────────────────────────────────────────────────────
        public const string CHECK_IN_USE = @"
            SELECT
                (
                    -- 1. Employees currently holding this level for this skill
                    SELECT COUNT(1)
                    FROM   MEMPLOYEESKILLPROFILE
                    WHERE  SKILLID      = @SkillId
                    AND    CURRENTLEVNO = @SlNo
                    AND    STATUS       = 1
                )
                +
                (
                    -- 2. Job role requirements that set this as minimum level
                    SELECT COUNT(1)
                    FROM   MJOBROLESKILL
                    WHERE  SKILLID  = @SkillId
                    AND    MINLEVNO = @SlNo
                )
                +
                (
                    -- 3. Routing operations that require this minimum level
                    SELECT COUNT(1)
                    FROM   MROUTINGSKILL
                    WHERE  SKILLID          = @SkillId
                    AND    MINLEVELREQUIRED = @SlNo
                )
            AS TotalInUseCount";

        public const string GET_SELECT_LIST = @"
            SELECT
                SL.SLEVELID         AS SLevelId,
                SL.SKILLID          AS SkillId,
                SL.SLNO             AS SlNo,
                SL.LEVELLABEL       AS LevelLabel,
                SL.LEVELCOUNT       AS LevelCount,
                SL.LEVELDESCRIPTOR  AS LevelDescriptor,
                SL.REMARKS          AS Remarks,
                SL.ISMINREQUIRED    AS IsMinRequired,
                SL.CANTRAINOTHERS   AS CanTrainOthers
            FROM MSKILLLEVEL SL
            INNER JOIN MSKILL S ON S.SKILLID  = SL.SKILLID
                                AND S.TENANTID = @TenantId
            WHERE 1 = 1
            {DYNAMIC_WHERE}
            ORDER BY LevelLabel ASC";

    }
}