namespace SkillManagementDAL.Query.SkillMatrixConfig
{
    public static class SkillMatrixConfigQB
    {
        // ========================================================
        // GET_LIST (FIXED)
        // ========================================================
        public const string GET_LIST = @"
SELECT
    C.CONFIGID                AS ConfigId,
    C.MATRIXCODE              AS MatrixCode,
    C.MATRIXTITLE             AS MatrixTitle,
    C.DOMAINID                AS DomainId,
    C.TARGETDEPT              AS TargetDept,
    C.TARGETROLEIDS           AS TargetRoleIdsJson,
    C.PRODUCTFAMILYID         AS ProductFamilyId,
    IT.ITEMCODE               AS ProductFamilyCode,
    IT.ITEMNAME               AS ProductFamilyName,
    C.MODELIDS                AS ModelIdsJson,
    C.INCLUDESKILLIDS         AS IncludeSkillIdsJson,
    C.SHOWSUMMARYROW          AS ShowSummaryRow,
    C.SHOWMINLEVEL            AS ShowMinLevel,
    C.UPDATEFREQUENCY         AS UpdateFrequency,
    C.ISACTIVE                AS IsActive,
    C.VERSION                 AS Version,
    C.STATUS                  AS Status,
    C.SORTORDER               AS SortOrder,
    C.CREATEDBYID             AS CreatedById,
    C.CREATEDON               AS CreatedOn,
    C.MODIFIEDBYID            AS ModifiedById,
    C.MODIFIEDON              AS ModifiedOn,
    C.SOURCETYPE              AS SourceType,
    C.TENANTID                AS TenantId,
    D.DOMAINNAME              AS DomainName,

    ISNULL(R.RoleNames, '')   AS RoleNames,
    ISNULL(M.ModelNames, '')  AS ModelNames,
    ISNULL(S.SkillNames, '')  AS SkillNames

FROM MSKILLMATRIX C

LEFT JOIN MSKILLDOMAIN D
    ON D.DOMAINID = C.DOMAINID
   AND D.STATUS = 1

LEFT JOIN MITEM IT
    ON IT.ITEMID = C.PRODUCTFAMILYID

/* =========================
   ROLE NAMES
   ========================= */
OUTER APPLY
(
    SELECT STUFF((
        SELECT ', ' + RO.ROLENAME
        FROM (
            SELECT T.N.value('.', 'INT') AS RoleId
            FROM (
                SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.TARGETROLEIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                WHERE C.TARGETROLEIDS IS NOT NULL AND C.TARGETROLEIDS NOT IN ('[]','null','')
            ) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MJOBROLESKILL RO ON RO.ROLEID = Ids.RoleId
        FOR XML PATH('')
    ), 1, 2, '') AS RoleNames
) R

/* =========================
   MODEL NAMES
   ========================= */
OUTER APPLY
(
    SELECT STUFF((
        SELECT ', ' + MO.MODELNAME
        FROM (
            SELECT T.N.value('.', 'INT') AS ModelId
            FROM (
                SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.MODELIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                WHERE C.MODELIDS IS NOT NULL AND C.MODELIDS NOT IN ('[]','null','')
            ) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MMODEL MO ON MO.MODELID = Ids.ModelId
        FOR XML PATH('')
    ), 1, 2, '') AS ModelNames
) M

/* =========================
   SKILL NAMES
   ========================= */
OUTER APPLY
(
    SELECT STUFF((
        SELECT ', ' + SK.SKILLNAME
        FROM (
            SELECT T.N.value('.', 'INT') AS SkillId
            FROM (
                SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.INCLUDESKILLIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                WHERE C.INCLUDESKILLIDS IS NOT NULL AND C.INCLUDESKILLIDS NOT IN ('[]','null','')
            ) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MSKILL SK ON SK.SKILLID = Ids.SkillId
        FOR XML PATH('')
    ), 1, 2, '') AS SkillNames
) S
WHERE C.TENANTID = @TenantId
  AND C.STATUS = 1

ORDER BY
    C.SORTORDER,
    C.MATRIXTITLE;
";

        // ========================================================
        // GET_BY_ID (FIXED)
        // ========================================================
        public const string GET_BY_ID = @"
SELECT
    C.CONFIGID                AS ConfigId,
    C.MATRIXCODE             AS MatrixCode,
    C.MATRIXTITLE            AS MatrixTitle,
    C.DOMAINID               AS DomainId,

    D.DOMAINCODE             AS DomainCode,
    D.DOMAINNAME             AS DomainName,

    C.TARGETDEPT             AS TargetDept,
    DEP.DEPARTMENTCODE       AS DepartmentCode,
    DEP.DEPARTMENTNAME       AS DepartmentName,

    -- JSON FIELDS
    C.TARGETROLEIDS          AS TargetRoleIdsJson,
    C.PRODUCTFAMILYID        AS ProductFamilyId,
    IT.ITEMCODE              AS ProductFamilyCode,
    IT.ITEMNAME              AS ProductFamilyName,

    C.MODELIDS               AS ModelIdsJson,
    C.INCLUDESKILLIDS        AS IncludeSkillIdsJson,

    C.SHOWSUMMARYROW         AS ShowSummaryRow,
    C.SHOWMINLEVEL           AS ShowMinLevel,
    C.UPDATEFREQUENCY        AS UpdateFrequency,
    C.ISACTIVE               AS IsActive,
    C.VERSION                AS Version,
    C.STATUS                 AS Status,
    C.SORTORDER              AS SortOrder,
    C.CREATEDBYID            AS CreatedById,
    C.CREATEDON              AS CreatedOn,
    C.MODIFIEDBYID           AS ModifiedById,
    C.MODIFIEDON             AS ModifiedOn,
    C.SOURCETYPE             AS SourceType,
    C.TENANTID               AS TenantId,

    ISNULL(R.RoleNames, '')  AS RoleNames,
    ISNULL(M.ModelNames, '') AS ModelNames,
    ISNULL(S.SkillNames, '') AS SkillNames

FROM MSKILLMATRIX C

LEFT JOIN MSKILLDOMAIN D
    ON D.DOMAINID = C.DOMAINID

LEFT JOIN MDEPARTMENT DEP
    ON DEP.DEPARTMENTID = C.TARGETDEPT

LEFT JOIN MITEM IT
    ON IT.ITEMID = C.PRODUCTFAMILYID

/* =========================
   ROLE NAMES
   ========================= */
OUTER APPLY
(
    SELECT STUFF((
        SELECT ', ' + RO.ROLENAME
        FROM (
            SELECT T.N.value('.', 'INT') AS RoleId
            FROM (
                SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.TARGETROLEIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                WHERE C.TARGETROLEIDS IS NOT NULL AND C.TARGETROLEIDS NOT IN ('[]','null','')
            ) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MJOBROLESKILL RO ON RO.ROLEID = Ids.RoleId
        FOR XML PATH('')
    ), 1, 2, '') AS RoleNames
) R

/* =========================
   MODEL NAMES
   ========================= */
OUTER APPLY
(
    SELECT STUFF((
        SELECT ', ' + MO.MODELNAME
        FROM (
            SELECT T.N.value('.', 'INT') AS ModelId
            FROM (
                SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.MODELIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                WHERE C.MODELIDS IS NOT NULL AND C.MODELIDS NOT IN ('[]','null','')
            ) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MMODEL MO ON MO.MODELID = Ids.ModelId
        FOR XML PATH('')
    ), 1, 2, '') AS ModelNames
) M

/* =========================
   SKILL NAMES
   ========================= */
OUTER APPLY
(
    SELECT STUFF((
        SELECT ', ' + SK.SKILLNAME
        FROM (
            SELECT T.N.value('.', 'INT') AS SkillId
            FROM (
                SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.INCLUDESKILLIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                WHERE C.INCLUDESKILLIDS IS NOT NULL AND C.INCLUDESKILLIDS NOT IN ('[]','null','')
            ) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MSKILL SK ON SK.SKILLID = Ids.SkillId
        FOR XML PATH('')
    ), 1, 2, '') AS SkillNames
) S
WHERE C.CONFIGID = @ConfigId
  AND C.TENANTID = @TenantId;
";

        // ========================================================
        // GET_SELECT_LIST
        // ========================================================
        public const string GET_SELECT_LIST = @"
SELECT
    CONFIGID        AS Id,
    MATRIXTITLE     AS Name,
    MATRIXCODE      AS Code,
    DOMAINID        AS DomainId,
    SORTORDER       AS SortOrder
FROM MSKILLMATRIX
WHERE TENANTID = @TenantId
  AND STATUS = 1
  AND ISACTIVE = 0
{DYNAMIC_WHERE}
ORDER BY
    SortOrder ASC,
    Name ASC";

        // ========================================================
        // INSERT
        // ========================================================
        public const string INSERT = @"
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
        // ========================================================
        public const string UPDATE = @"
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
        // ========================================================
        public const string SOFT_DELETE = @"
UPDATE MSKILLMATRIX
SET
    STATUS          = 2,
    VERSION         = VERSION + 1,
    MODIFIEDBYID    = @ModifiedById,
    MODIFIEDON      = GETDATE()
WHERE CONFIGID = @ConfigId
  AND TENANTID = @TenantId";

        // ========================================================
        // GET_SKILLMATRIXCONFIG_LIST_PAGED
        // ========================================================
        public const string GET_SKILLMATRIXCONFIG_LIST_PAGED = @"
SELECT
    C.CONFIGID                AS ConfigId,
    C.MATRIXCODE              AS MatrixCode,
    C.MATRIXTITLE             AS MatrixTitle,
    C.DOMAINID                AS DomainId,
    C.TARGETDEPT              AS TargetDept,
    C.TARGETROLEIDS           AS TargetRoleIdsJson,
    C.PRODUCTFAMILYID         AS ProductFamilyId,
    IT.ITEMCODE               AS ProductFamilyCode,
    IT.ITEMNAME               AS ProductFamilyName,
    C.MODELIDS                AS ModelIdsJson,
    C.INCLUDESKILLIDS         AS IncludeSkillIdsJson,
    C.SHOWSUMMARYROW          AS ShowSummaryRow,
    C.SHOWMINLEVEL            AS ShowMinLevel,
    C.UPDATEFREQUENCY         AS UpdateFrequency,
    C.ISACTIVE                AS IsActive,
    C.VERSION                 AS Version,
    C.STATUS                  AS Status,
    C.SORTORDER               AS SortOrder,
    C.CREATEDBYID             AS CreatedById,
    C.CREATEDON               AS CreatedOn,
    C.MODIFIEDBYID            AS ModifiedById,
    C.MODIFIEDON              AS ModifiedOn,
    C.SOURCETYPE              AS SourceType,
    C.TENANTID                AS TenantId,
    D.DOMAINNAME              AS DomainName,
    ISNULL(R.RoleNames, '')   AS RoleNames,
    ISNULL(M.ModelNames, '')  AS ModelNames,
    ISNULL(S.SkillNames, '')  AS SkillNames
FROM MSKILLMATRIX C
LEFT JOIN MSKILLDOMAIN D ON D.DOMAINID = C.DOMAINID AND D.STATUS = 1
LEFT JOIN MITEM IT ON IT.ITEMID = C.PRODUCTFAMILYID
OUTER APPLY (
    SELECT STUFF((
        SELECT ', ' + RO.ROLENAME
        FROM (
            SELECT T.N.value('.', 'INT') AS RoleId
            FROM (SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.TARGETROLEIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                  WHERE C.TARGETROLEIDS IS NOT NULL AND C.TARGETROLEIDS NOT IN ('[]','null','')) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MJOBROLESKILL RO ON RO.ROLEID = Ids.RoleId
        FOR XML PATH('')
    ), 1, 2, '') AS RoleNames
) R
OUTER APPLY (
    SELECT STUFF((
        SELECT ', ' + MO.MODELNAME
        FROM (
            SELECT T.N.value('.', 'INT') AS ModelId
            FROM (SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.MODELIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                  WHERE C.MODELIDS IS NOT NULL AND C.MODELIDS NOT IN ('[]','null','')) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MMODEL MO ON MO.MODELID = Ids.ModelId
        FOR XML PATH('')
    ), 1, 2, '') AS ModelNames
) M
OUTER APPLY (
    SELECT STUFF((
        SELECT ', ' + SK.SKILLNAME
        FROM (
            SELECT T.N.value('.', 'INT') AS SkillId
            FROM (SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.INCLUDESKILLIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                  WHERE C.INCLUDESKILLIDS IS NOT NULL AND C.INCLUDESKILLIDS NOT IN ('[]','null','')) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MSKILL SK ON SK.SKILLID = Ids.SkillId
        FOR XML PATH('')
    ), 1, 2, '') AS SkillNames
) S
WHERE C.TENANTID = @TenantId
  AND C.STATUS = 1
ORDER BY C.SORTORDER, C.MATRIXTITLE
OFFSET     (@FirstNumber - 1) ROWS
FETCH NEXT  @MaxResult        ROWS ONLY
OPTION (RECOMPILE);";

        // ========================================================
        // GET_SKILLMATRIXCONFIG_LIST_ALL
        // ========================================================
        public const string GET_SKILLMATRIXCONFIG_LIST_ALL = @"
SELECT
    C.CONFIGID                AS ConfigId,
    C.MATRIXCODE              AS MatrixCode,
    C.MATRIXTITLE             AS MatrixTitle,
    C.DOMAINID                AS DomainId,
    C.TARGETDEPT              AS TargetDept,
    C.TARGETROLEIDS           AS TargetRoleIdsJson,
    C.PRODUCTFAMILYID         AS ProductFamilyId,
    IT.ITEMCODE               AS ProductFamilyCode,
    IT.ITEMNAME               AS ProductFamilyName,
    C.MODELIDS                AS ModelIdsJson,
    C.INCLUDESKILLIDS         AS IncludeSkillIdsJson,
    C.SHOWSUMMARYROW          AS ShowSummaryRow,
    C.SHOWMINLEVEL            AS ShowMinLevel,
    C.UPDATEFREQUENCY         AS UpdateFrequency,
    C.ISACTIVE                AS IsActive,
    C.VERSION                 AS Version,
    C.STATUS                  AS Status,
    C.SORTORDER               AS SortOrder,
    C.CREATEDBYID             AS CreatedById,
    C.CREATEDON               AS CreatedOn,
    C.MODIFIEDBYID            AS ModifiedById,
    C.MODIFIEDON              AS ModifiedOn,
    C.SOURCETYPE              AS SourceType,
    C.TENANTID                AS TenantId,
    D.DOMAINNAME              AS DomainName,
    ISNULL(R.RoleNames, '')   AS RoleNames,
    ISNULL(M.ModelNames, '')  AS ModelNames,
    ISNULL(S.SkillNames, '')  AS SkillNames
FROM MSKILLMATRIX C
LEFT JOIN MSKILLDOMAIN D ON D.DOMAINID = C.DOMAINID AND D.STATUS = 1
LEFT JOIN MITEM IT ON IT.ITEMID = C.PRODUCTFAMILYID
OUTER APPLY (
    SELECT STUFF((
        SELECT ', ' + RO.ROLENAME
        FROM (
            SELECT T.N.value('.', 'INT') AS RoleId
            FROM (SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.TARGETROLEIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                  WHERE C.TARGETROLEIDS IS NOT NULL AND C.TARGETROLEIDS NOT IN ('[]','null','')) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MJOBROLESKILL RO ON RO.ROLEID = Ids.RoleId
        FOR XML PATH('')
    ), 1, 2, '') AS RoleNames
) R
OUTER APPLY (
    SELECT STUFF((
        SELECT ', ' + MO.MODELNAME
        FROM (
            SELECT T.N.value('.', 'INT') AS ModelId
            FROM (SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.MODELIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                  WHERE C.MODELIDS IS NOT NULL AND C.MODELIDS NOT IN ('[]','null','')) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MMODEL MO ON MO.MODELID = Ids.ModelId
        FOR XML PATH('')
    ), 1, 2, '') AS ModelNames
) M
OUTER APPLY (
    SELECT STUFF((
        SELECT ', ' + SK.SKILLNAME
        FROM (
            SELECT T.N.value('.', 'INT') AS SkillId
            FROM (SELECT CAST('<r><v>' + REPLACE(REPLACE(REPLACE(C.INCLUDESKILLIDS,'[',''),']',''),',','</v><v>') + '</v></r>' AS XML) AS X
                  WHERE C.INCLUDESKILLIDS IS NOT NULL AND C.INCLUDESKILLIDS NOT IN ('[]','null','')) XmlData
            CROSS APPLY XmlData.X.nodes('/r/v') AS T(N)
        ) Ids
        INNER JOIN MSKILL SK ON SK.SKILLID = Ids.SkillId
        FOR XML PATH('')
    ), 1, 2, '') AS SkillNames
) S
WHERE C.TENANTID = @TenantId
  AND C.STATUS = 1
ORDER BY C.SORTORDER, C.MATRIXTITLE;";

        // ========================================================
        // CHECK_IN_USE
        // ========================================================
        public const string CHECK_IN_USE = @"
SELECT COUNT(1)
FROM MSKILLMATRIX
WHERE CONFIGID = @ConfigId
  AND TENANTID = @TenantId
  AND STATUS = 1
  AND ISACTIVE = 0";
    }
}