// ============================================================
// GoodBooks ERP — Skill Management Module
// Query   : SkillMatrixQB
// Updated : 2026-05-13  — Full fix pass v2
//
// FIXES IN THIS VERSION:
//
//   [A]  GET_SKILLS_FOR_MATRIX
//        Was querying MSKILLPRODUCT (scope rows) — WRONG TABLE.
//        Now correctly queries MSKILL master with SKILLNAME,
//        SKILLSCOPE, LEVELCOUNT, ISSPECIALCHAR, MATRIXSEQNO.
//        Joined to MSKILLGROUP for GroupName.
//
//   [B]  GET_PRODUCT_SCOPE_FOR_SKILLS
//        Added TenantId guard via INNER JOIN to MSKILL.
//
//   [C]  GET_PROFILE_CELLS  ← CRITICAL CELL-KEY FIX
//        Problem:
//          MEMPLOYEESKILLPROFILE stores real PRODUCTFAMILYID/MODELID even
//          when the skill is SkillScope=0 (General). The BLL builds
//          the cell-lookup key as "{EmpId}_{SkillId}_-1_-1" for
//          General scope, but SQL was returning real IDs → lookup
//          never matched → all cells appeared empty (null).
//        Fix:
//          JOIN MSKILL to get SKILLSCOPE. Use CASE to normalise:
//            SkillScope = 0 → ProductFamilyId = -1, ModelId = -1
//            SkillScope = 1 → keep real ProductFamilyId, ModelId = -1
//            SkillScope = 2 → keep real ProductFamilyId and ModelId
//          This makes DB keys align with BLL keys exactly.
//
//   [D]  GET_MIN_LEVELS
//        Added missing TENANTID filter — was cross-tenant unsafe
//        and returned 0 rows on a fresh tenant.
//
//   [E]  GET_EMPLOYEES_FOR_MATRIX
//        1. Added LEFT JOIN to MDEPARTMENT → returns DepartmentName
//           (ISNULL fallback to raw ID if dept not in master).
//        2. MJOBROLE JOIN no longer filters R.STATUS = 1 — that
//           silently set RoleName = null for roles with other statuses.
//           Added R.TENANTID = E.TENANTID guard instead.
//
//   [F]  GET_MATRIX_CONFIG / GET_MATRIX_CONFIG_LIST
//        DOMAINID guard changed from != -1 to > 0 to handle
//        GoodBooks negative IDs correctly.
//        GET_MATRIX_CONFIG_LIST now has AND M.STATUS = 1.
// ============================================================

namespace SkillManagementDAL.Query.SkillMatrix
{
    public class SkillMatrixQB
    {
        // ── GET_MATRIX_CONFIG ────────────────────────────────────
        // Loads a single config row by CONFIGID + TENANTID.
        // Index hint: PK_MSKILLMATRIX_CONFIGID
        // ────────────────────────────────────────────────────────
        public const string GET_MATRIX_CONFIG = @"
SELECT
    M.CONFIGID          AS ConfigId,
    M.MATRIXCODE        AS MatrixCode,
    M.MATRIXTITLE       AS MatrixTitle,
    M.DOMAINID          AS DomainId,
    M.TARGETDEPT        AS TargetDept,
    M.TARGETROLEIDS     AS TargetRoleIds,
    M.PRODUCTFAMILYID   AS ProductFamilyId,
    M.MODELIDS          AS ModelIds,
    M.INCLUDESKILLIDS   AS IncludeSkillIds,
    M.SHOWSUMMARYROW    AS ShowSummaryRow,
    M.SHOWMINLEVEL      AS ShowMinLevel,
    M.UPDATEFREQUENCY   AS UpdateFrequency,
    M.ISACTIVE          AS IsActive,
    M.VERSION           AS Version,
    M.STATUS            AS Status,
    M.SORTORDER         AS SortOrder,
    M.CREATEDBYID       AS CreatedById,
    M.CREATEDON         AS CreatedOn,
    M.MODIFIEDBYID      AS ModifiedById,
    M.MODIFIEDON        AS ModifiedOn,
    M.SOURCETYPE        AS SourceType,
    M.TENANTID          AS TenantId,
    D.DOMAINNAME        AS DomainName
FROM MSKILLMATRIX M
LEFT JOIN MSKILLDOMAIN D
    ON  D.DOMAINID = M.DOMAINID
    AND M.DOMAINID > 0
    AND D.STATUS   = 1
WHERE M.CONFIGID = @ConfigId
AND   M.TENANTID = @TenantId;";


        // ── GET_MATRIX_CONFIG_LIST ───────────────────────────────
        // All active (STATUS=1) configs for a tenant.
        // Index hint: IX_MSKILLMATRIX_TENANTID_STATUS
        // ────────────────────────────────────────────────────────
        public const string GET_MATRIX_CONFIG_LIST = @"
SELECT
    M.CONFIGID          AS ConfigId,
    M.MATRIXCODE        AS MatrixCode,
    M.MATRIXTITLE       AS MatrixTitle,
    M.DOMAINID          AS DomainId,
    M.TARGETDEPT        AS TargetDept,
    M.PRODUCTFAMILYID   AS ProductFamilyId,
    M.SHOWSUMMARYROW    AS ShowSummaryRow,
    M.SHOWMINLEVEL      AS ShowMinLevel,
    M.UPDATEFREQUENCY   AS UpdateFrequency,
    M.ISACTIVE          AS IsActive,
    M.VERSION           AS Version,
    M.STATUS            AS Status,
    M.SORTORDER         AS SortOrder,
    M.TENANTID          AS TenantId,
    D.DOMAINNAME        AS DomainName
FROM MSKILLMATRIX M
LEFT JOIN MSKILLDOMAIN D
    ON  D.DOMAINID = M.DOMAINID
    AND M.DOMAINID > 0
    AND D.STATUS   = 1
WHERE M.TENANTID = @TenantId
AND   M.STATUS   = 1
ORDER BY
    M.SORTORDER   ASC,
    M.MATRIXTITLE ASC;";


        // ── GET_SKILLS_FOR_MATRIX ────────────────────────────────
        // FIX [A]: Queries MSKILL master (not MSKILLPRODUCT).
        //          Joined MSKILLGROUP for GroupName.
        // Maps to : SkillMatrixColumnDTO
        // ────────────────────────────────────────────────────────
        public const string GET_SKILLS_FOR_MATRIX = @"
SELECT
    S.SKILLID                           AS SkillId,
    S.SKILLCODE                         AS SkillCode,
    S.SKILLNAME                         AS SkillName,
    S.SKILLSCOPE                        AS SkillScope,
    S.LEVELCOUNT                        AS LevelCount,
    ISNULL(S.ISSPECIALCHAR, 0)          AS IsSpecialChar,
    ISNULL(S.MATRIXSEQNO,   0)          AS MatrixSeqNo,
    ISNULL(G.GROUPID,      -1)          AS GroupId,
    G.GROUPNAME                         AS GroupName
FROM MSKILL S
LEFT JOIN MSKILLGROUP G
    ON  G.GROUPID  = S.GROUPID
    AND G.TENANTID = S.TENANTID
WHERE
    S.TENANTID = @TenantId
    AND S.SKILLID IN
    (
        SELECT DISTINCT TRY_CAST(n.value('.[1]','varchar(20)') AS INT)
        FROM (SELECT CAST('<r><i>' + REPLACE(@SkillIds,',','</i><i>') + '</i></r>' AS XML)) x(doc)
        CROSS APPLY doc.nodes('/r/i') n(n)
        WHERE TRY_CAST(n.value('.[1]','varchar(20)') AS INT) IS NOT NULL
    )
ORDER BY
    CASE WHEN S.MATRIXSEQNO > 0 THEN S.MATRIXSEQNO ELSE 9999 END ASC,
    S.SKILLNAME ASC;";


        // ── GET_PRODUCT_SCOPE_FOR_SKILLS ─────────────────────────
        // FIX [B]: Tenant-guarded via INNER JOIN to MSKILL.
        //          Scope rows only loaded for Scope 1/2 skills.
        // Maps to : SkillProductScopeDTO
        // ────────────────────────────────────────────────────────
        public const string GET_PRODUCT_SCOPE_FOR_SKILLS = @"
SELECT
    SP.SKILLID          AS SkillId,
    SP.SLNO             AS SlNo,
    SP.PRODUCTFAMILYID  AS ProductFamilyId,
    SP.MODELID          AS ModelId,
    PF.ITEMNAME         AS ProductFamilyName,
    MM.MODELNAME        AS ModelName
FROM MSKILLPRODUCT SP
INNER JOIN MSKILL S
    ON  S.SKILLID  = SP.SKILLID
    AND S.TENANTID = @TenantId
LEFT JOIN MITEM PF
    ON  PF.ITEMID = SP.PRODUCTFAMILYID
LEFT JOIN MMODEL MM
    ON  MM.MODELID = SP.MODELID
WHERE SP.SKILLID IN
(
    SELECT TRY_CAST(n.value('.[1]','varchar(20)') AS INT)
    FROM (SELECT CAST('<r><i>' + REPLACE(@SkillIds,',','</i><i>') + '</i></r>' AS XML)) x(doc)
    CROSS APPLY doc.nodes('/r/i') n(n)
    WHERE TRY_CAST(n.value('.[1]','varchar(20)') AS INT) IS NOT NULL
)
ORDER BY
    SP.SKILLID ASC,
    SP.SLNO    ASC;";


        // ── GET_EMPLOYEES_FOR_MATRIX ─────────────────────────────
        // FIX [E]:
        //   1. Joined MDEPARTMENT → DepartmentName returned instead
        //      of raw numeric DEPARTMENTID.  ISNULL fallback keeps
        //      raw ID visible if the dept row is missing.
        //   2. MJOBROLESKILL join changed to grouped subquery
        //      (MAX(ROLENAME) GROUP BY ROLEID) to prevent duplicate
        //      employee rows — direct join on ROLEID alone produced
        //      N rows per employee (N = skills in that role).
        //   3. DAL normalises empty TARGETDEPT to null before calling
        //      this query so the IS NULL branch fires for "all depts".
        //
        // Maps to : SkillMatrixEmployeeDTO
        // Index hint: IX_MEMPLOYEE_TENANTID_STATUS
        // ────────────────────────────────────────────────────────
       

        // ── GET_PROFILE_CELLS ────────────────────────────────────
        // FIX [C]: CRITICAL CELL-KEY MISMATCH FIX
        //
        //   Root cause:
        //     MEMPLOYEESKILLPROFILE rows for SkillScope=0 (General) skills
        //     were saved with real PRODUCTFAMILYID/MODELID values
        //     (e.g. -1399999745 / -1399999765).  The BLL builds the
        //     cell-lookup key as "{EmpId}_{SkillId}_-1_-1" for
        //     General scope.  The dictionary lookup never matched,
        //     so all cells appeared empty (ProfileId=null,
        //     CurrentLevNo=0) even when profile data existed.
        //
        //   Fix:
        //     JOIN MSKILL S to get S.SKILLSCOPE.
        //     CASE expressions normalise ProductFamilyId/ModelId:
        //       SkillScope = 0  →  PFId = -1,  MId = -1   (General)
        //       SkillScope = 1  →  PFId = real, MId = -1  (PF-scoped)
        //       SkillScope = 2  →  PFId = real, MId = real (Model-scoped)
        //
        //   Cell lookup key format (same in BLL):
        //     Scope 0 → "{EmpId}_{SkillId}_-1_-1"
        //     Scope 1 → "{EmpId}_{SkillId}_{ProductFamilyId}_-1"
        //     Scope 2 → "{EmpId}_{SkillId}_{ProductFamilyId}_{ModelId}"
        //
        // Maps to : SkillMatrixProfileCellDTO
        // ────────────────────────────────────────────────────────
        public const string GET_PROFILE_CELLS = @"
WITH EmployeeFilter AS
(
    SELECT DISTINCT TRY_CAST(n.value('.[1]','varchar(20)') AS INT) AS EmployeeId
    FROM (SELECT CAST('<r><i>' + REPLACE(@EmployeeIds,',','</i><i>') + '</i></r>' AS XML)) x(doc)
    CROSS APPLY doc.nodes('/r/i') n(n)
    WHERE TRY_CAST(n.value('.[1]','varchar(20)') AS INT) IS NOT NULL
),
SkillFilter AS
(
    SELECT DISTINCT TRY_CAST(n.value('.[1]','varchar(20)') AS INT) AS SkillId
    FROM (SELECT CAST('<r><i>' + REPLACE(@SkillIds,',','</i><i>') + '</i></r>' AS XML)) x(doc)
    CROSS APPLY doc.nodes('/r/i') n(n)
    WHERE TRY_CAST(n.value('.[1]','varchar(20)') AS INT) IS NOT NULL
)
SELECT
    P.EMPLOYEEID                        AS EmployeeId,
    P.SKILLID                           AS SkillId,
    P.PROFILEID                         AS ProfileId,
    P.CURRENTLEVNO                      AS CurrentLevNo,
    P.PROFILESTATUS                     AS ProfileStatus,

    CAST(P.ASSESSEDDT       AS DATE)    AS AssessedDt,
    CAST(P.TRAININGPLANDATE AS DATE)    AS TrainingPlanDate,
    CAST(P.ACTUALTRAINDT    AS DATE)    AS ActualTrainDt,
    CAST(P.NEXTREVIEWDT     AS DATE)    AS NextReviewDt,

    -- FIX [C]: normalise scope-key fields so BLL lookup keys match.
    -- For General (Scope=0): force -1/-1
    -- For ProductFamily (Scope=1): keep real PFId, force ModelId=-1
    -- For Model (Scope=2): keep real PFId and ModelId
    CASE
        WHEN S.SKILLSCOPE = 0 THEN -1
        ELSE ISNULL(P.PRODUCTFAMILYID, -1)
    END                                 AS ProductFamilyId,

    CASE
        WHEN S.SKILLSCOPE = 0 THEN -1
        WHEN S.SKILLSCOPE = 1 THEN -1
        ELSE ISNULL(P.MODELID, -1)
    END                                 AS ModelId,

    SL.LEVELLABEL                       AS LevelLabel

FROM MEMPLOYEESKILLPROFILE P

-- FIX [C]: need SKILLSCOPE to normalise PF/Model IDs
INNER JOIN MSKILL S
    ON  S.SKILLID  = P.SKILLID
    AND S.TENANTID = @TenantId

LEFT JOIN MSKILLLEVEL SL
    ON  SL.SKILLID = P.SKILLID
    AND SL.SLNO    = P.CURRENTLEVNO

INNER JOIN EmployeeFilter EF
    ON EF.EmployeeId = P.EMPLOYEEID

INNER JOIN SkillFilter SF
    ON SF.SkillId = P.SKILLID

WHERE
    P.TENANTID = @TenantId;";


        // ── GET_MIN_LEVELS ───────────────────────────────────────
        // FIX [D]: Added TENANTID filter.
        //          Previous version had no tenant guard — returned
        //          0 rows on a fresh tenant → MinLevelRequired = 0
        //          for every column.
        // Maps to : SkillMinLevelDTO
        // ────────────────────────────────────────────────────────
        public const string GET_MIN_LEVELS = @"
SELECT
    JR.SKILLID       AS SkillId,
    MAX(JR.MINLEVNO) AS MinLevNo
FROM MJOBROLESKILL JR
WHERE
    JR.TENANTID = @TenantId
    AND JR.SKILLID IN
    (
        SELECT TRY_CAST(n.value('.[1]','varchar(20)') AS INT)
        FROM (SELECT CAST('<r><i>' + REPLACE(@SkillIds,',','</i><i>') + '</i></r>' AS XML)) x(doc)
        CROSS APPLY doc.nodes('/r/i') n(n)
        WHERE TRY_CAST(n.value('.[1]','varchar(20)') AS INT) IS NOT NULL
    )
GROUP BY JR.SKILLID;";


        // ── GET_MIN_LEVELS_BY_ROLES ──────────────────────────────
        // Called by BLL when TargetRoleIds has valid positive IDs
        // and the ALL-marker (-1500000000) is absent.
        // Maps to : SkillMinLevelDTO
        // ────────────────────────────────────────────────────────
        public const string GET_MIN_LEVELS_BY_ROLES = @"
SELECT
    JR.SKILLID        AS SkillId,
    MAX(JR.MINLEVNO)  AS MinLevNo
FROM MJOBROLESKILL JR
WHERE
     JR.SKILLID IN
    (
        SELECT TRY_CAST(n.value('.[1]','varchar(20)') AS INT)
        FROM (SELECT CAST('<r><i>' + REPLACE(@SkillIds,',','</i><i>') + '</i></r>' AS XML)) x(doc)
        CROSS APPLY doc.nodes('/r/i') n(n)
        WHERE TRY_CAST(n.value('.[1]','varchar(20)') AS INT) IS NOT NULL
    )
    AND JR.ROLEID IN
    (
        SELECT TRY_CAST(n.value('.[1]','varchar(20)') AS INT)
        FROM (SELECT CAST('<r><i>' + REPLACE(@RoleIds,',','</i><i>') + '</i></r>' AS XML)) x(doc)
        CROSS APPLY doc.nodes('/r/i') n(n)
        WHERE TRY_CAST(n.value('.[1]','varchar(20)') AS INT) IS NOT NULL
    )
GROUP BY JR.SKILLID;";


        // ── GET_ROUTING_MIN_LEVELS ───────────────────────────────
        // Pass @RoutingId = -1 to aggregate across all routings.
        // Maps to : SkillMinLevelDTO
        // ────────────────────────────────────────────────────────
        public const string GET_ROUTING_MIN_LEVELS = @"
SELECT
    RS.SKILLID               AS SkillId,
    MIN(RS.MINLEVELREQUIRED) AS MinLevNo
FROM MROUTINGSKILL RS
WHERE
    RS.SKILLID IN
    (
        SELECT TRY_CAST(n.value('.[1]','varchar(20)') AS INT)
        FROM (SELECT CAST('<r><i>' + REPLACE(@SkillIds,',','</i><i>') + '</i></r>' AS XML)) x(doc)
        CROSS APPLY doc.nodes('/r/i') n(n)
        WHERE TRY_CAST(n.value('.[1]','varchar(20)') AS INT) IS NOT NULL
    )
    AND (@RoutingId = -1 OR RS.ROUTINGID = @RoutingId)
GROUP BY RS.SKILLID;";


        // ── INSERT_CONFIG ────────────────────────────────────────
        public const string INSERT_CONFIG = @"
INSERT INTO MSKILLMATRIX
(
    CONFIGID,         MATRIXCODE,      MATRIXTITLE,
    DOMAINID,         TARGETDEPT,      TARGETROLEIDS,
    PRODUCTFAMILYID,  MODELIDS,        INCLUDESKILLIDS,
    SHOWSUMMARYROW,   SHOWMINLEVEL,    UPDATEFREQUENCY,
    ISACTIVE,         VERSION,         STATUS,
    SORTORDER,        CREATEDBYID,     CREATEDON,
    MODIFIEDBYID,     MODIFIEDON,      SOURCETYPE,
    TENANTID
)
VALUES
(
    @ConfigId,        @MatrixCode,     @MatrixTitle,
    @DomainId,        @TargetDept,     @TargetRoleIds,
    @ProductFamilyId, @ModelIds,       @IncludeSkillIds,
    @ShowSummaryRow,  @ShowMinLevel,   @UpdateFrequency,
    @IsActive,        0,               @Status,
    @SortOrder,       @CreatedById,    GETDATE(),
    @ModifiedById,    GETDATE(),       @SourceType,
    @TenantId
);";


        // ── UPDATE_CONFIG ────────────────────────────────────────
        public const string UPDATE_CONFIG = @"
UPDATE MSKILLMATRIX
SET
    MATRIXCODE      = @MatrixCode,
    MATRIXTITLE     = @MatrixTitle,
    DOMAINID        = @DomainId,
    TARGETDEPT      = @TargetDept,
    TARGETROLEIDS   = @TargetRoleIds,
    PRODUCTFAMILYID = @ProductFamilyId,
    MODELIDS        = @ModelIds,
    INCLUDESKILLIDS = @IncludeSkillIds,
    SHOWSUMMARYROW  = @ShowSummaryRow,
    SHOWMINLEVEL    = @ShowMinLevel,
    UPDATEFREQUENCY = @UpdateFrequency,
    ISACTIVE        = @IsActive,
    STATUS          = @Status,
    SORTORDER       = @SortOrder,
    VERSION         = VERSION + 1,
    MODIFIEDBYID    = @ModifiedById,
    MODIFIEDON      = GETDATE()
WHERE CONFIGID = @ConfigId
AND   TENANTID = @TenantId;";


        // ── SOFT_DELETE_CONFIG ───────────────────────────────────
        public const string SOFT_DELETE_CONFIG = @"
UPDATE MSKILLMATRIX
SET
    STATUS       = 2,
    VERSION      = VERSION + 1,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = GETDATE()
WHERE CONFIGID = @ConfigId
AND   TENANTID = @TenantId;";


public const string GET_EMPLOYEES_FOR_MATRIX = @"
SELECT
    E.EMPLOYEEID                                    AS EmployeeId,
    E.EMPLOYEENAME                                  AS EmployeeName,
    E.DEPARTMENTID                                  AS DepartmentId,
    NULLIF(ISNULL(D.DEPARTMENTNAME, NULLIF(CAST(E.DEPARTMENTID AS VARCHAR(20)), '-1')), '')  AS Department,
    E.DESIGNATIONID                                 AS RoleId,
    ISNULL(
        (
            SELECT TOP 1 JR.ROLENAME
            FROM MJOBROLESKILL JR
            WHERE JR.ROLEID = E.DESIGNATIONID
        ), ''
    )                                               AS RoleName,
    E.THUMBNAIL                                     AS Thumbnail
FROM MEMPLOYEE E
LEFT JOIN MDEPARTMENT D
    ON  D.DEPARTMENTID = E.DEPARTMENTID
    AND D.TENANTID     = E.TENANTID
WHERE
    E.TENANTID = @TenantId
    AND E.STATUS = 1
    AND (@TargetDept IS NULL OR E.DEPARTMENTID = @TargetDept)
ORDER BY
    E.EMPLOYEENAME ASC;";
 

public const string GET_EMPLOYEES_FOR_MATRIX_BY_ROLES = @"
SELECT
    E.EMPLOYEEID                                    AS EmployeeId,
    E.EMPLOYEENAME                                  AS EmployeeName,
    E.DEPARTMENTID                                  AS DepartmentId,
    NULLIF(ISNULL(D.DEPARTMENTNAME, NULLIF(CAST(E.DEPARTMENTID AS VARCHAR(20)), '-1')), '')  AS Department,
    E.DESIGNATIONID                                 AS RoleId,
    ISNULL(
        (
            SELECT TOP 1 JR.ROLENAME
            FROM MJOBROLESKILL JR
            WHERE JR.ROLEID = E.DESIGNATIONID
        ), ''
    )                                               AS RoleName,
    E.THUMBNAIL                                     AS Thumbnail
FROM MEMPLOYEE E
LEFT JOIN MDEPARTMENT D
    ON  D.DEPARTMENTID = E.DEPARTMENTID
    AND D.TENANTID     = E.TENANTID
WHERE
    E.TENANTID = @TenantId
    AND E.STATUS = 1
    AND (@TargetDept IS NULL OR E.DEPARTMENTID = @TargetDept)
    -- FIX [7]: Role filter done in SQL using MJOBROLESKILL.ROLEID.
    -- Replace MJOBROLESKILL with the correct table once confirmed.
    AND E.DESIGNATIONID IN
    (
        SELECT DISTINCT JR.ROLEID
        FROM   MJOBROLESKILL JR
        WHERE  JR.ROLEID IN
        (
            SELECT TRY_CAST(n.value('.[1]','varchar(20)') AS INT)
            FROM (SELECT CAST('<r><i>' + REPLACE(@RoleIds,',','</i><i>') + '</i></r>' AS XML)) x(doc)
            CROSS APPLY doc.nodes('/r/i') n(n)
            WHERE TRY_CAST(n.value('.[1]','varchar(20)') AS INT) IS NOT NULL
        )
    )
ORDER BY
    E.EMPLOYEENAME ASC;";

    }
}