// ============================================================
// GoodBooks ERP — Skill Management Module
// Query  : EmployeeSkillProfileQB
// Table  : MEMPLOYEESKILLPROFILE (E1) — primary
// Updated: Aligned to new DDL schema v1.0
//
// NOTES:
//   * MEMPLOYEE join uses LEFT JOIN — adjust column name
//     (EMPLOYEENAME / FULLNAME / EMPNAME) to match your
//     actual MEMPLOYEE table definition.
//   * MPRODUCTFAMILY and MMODEL joins are LEFT JOIN guarded
//     by PRODUCTFAMILYID != -1 / MODELID != -1 — adjust
//     table and column names to match your actual tables.
//   * MUSER join for AssessorName uses USERNAME — adjust
//     to your actual MUSER display column if different.
//   * Index hints documented inline for DBA reference.
// ============================================================

namespace SkillManagementDAL.Query.EmployeeSkillProfile
{
    public class EmployeeSkillProfileQB
    {
        // ── GET_BY_EMPLOYEE ──────────────────────────────────────
        // Returns full profile list for one employee with all
        // display-name joins resolved.
        // Index hint : IX_MEMPLOYEESKILLPROFILE_EMPID_TENANTID
        //              (EMPLOYEEID, TENANTID, PROFILESTATUS)
        // ────────────────────────────────────────────────────────
        public const string GET_BY_EMPLOYEE = @"
SELECT
    -- Display-only serial number (matches ORDER BY below)
    ROW_NUMBER() OVER (ORDER BY S.MATRIXSEQNO ASC, S.SKILLNAME ASC, P.CURRENTLEVNO ASC) AS SLNo,

    -- EMPLOYEESKILL (P)
    P.PROFILEID             AS ProfileId,
    P.PROFILENAME           AS ProfileName,
    P.EMPLOYEEID            AS EmployeeId,
    P.SKILLID               AS SkillId,
    P.CURRENTLEVNO          AS CurrentLevNo,
    P.REMARKS               AS Remarks,
    P.PRODUCTFAMILYID       AS ProductFamilyId,
    IT.ITEMCODE             AS ProductFamilyCode,
    IT.ITEMNAME             AS ProductFamilyName,
    P.MODELID               AS ModelId,
    MO.MODELCODE            AS ModelCode,
    MO.MODELNAME            AS ModelName,
    P.PROFILESTATUS         AS ProfileStatus,
    P.ASSESSEDDT            AS AssessedDt,
    P.TRAININGPLANDATE      AS TrainingPlanDate,
    P.ACTUALTRAINDT         AS ActualTrainDt,
    P.NEXTREVIEWDT          AS NextReviewDt,
    P.ASSESSEDBYID          AS AssessedById,

    -- Standard fields
    P.VERSION               AS Version,
    P.STATUS                AS Status,
    P.SORTORDER             AS SortOrder,
    P.CREATEDBYID           AS CreatedById,
    P.CREATEDON             AS CreatedOn,
    P.MODIFIEDBYID          AS ModifiedById,
    P.MODIFIEDON            AS ModifiedOn,
    P.SOURCETYPE            AS SourceType,

    -- MSKILL (S)
    S.SKILLNAME             AS SkillName,
    S.SKILLNAMEFULL         AS SkillNameFull,
    S.SKILLCODE             AS SkillCode,
    S.SKILLSCOPE            AS SkillScope,
    S.ISSPECIALCHAR         AS IsSpecialChar,
    S.LEVELCOUNT            AS LevelCount,
    S.MATRIXSEQNO           AS MatrixSeqNo,
    S.GROUPID               AS GroupId,
    S.TENANTID              AS TenantId,

    -- MSKILLGROUP (G)
    G.GROUPNAME             AS GroupName,

    -- MSKILLLEVEL (SL)
    SL.LEVELLABEL           AS LevelLabel,
    SL.LEVELDESCRIPTOR      AS LevelDescriptor,

    -- MUSER (U) — assessor display name
    U.USERNAME              AS AssessorName,

    -- MEMPLOYEE (E)
    E.EMPLOYEENAME          AS EmployeeName,
    E.THUMBNAIL             AS Thumbnail

FROM        MEMPLOYEESKILLPROFILE  P

LEFT  JOIN  MITEM           IT  ON  IT.ITEMID       = P.PRODUCTFAMILYID
                                AND P.PRODUCTFAMILYID != -1
LEFT  JOIN  MMODEL          MO  ON  MO.MODELID      = P.MODELID
                                AND P.MODELID       != -1
INNER JOIN  MSKILL          S   ON  S.SKILLID       = P.SKILLID
                                AND S.STATUS        = 1
LEFT  JOIN  MSKILLGROUP     G   ON  G.GROUPID       = S.GROUPID
                                AND G.STATUS        = 1
LEFT  JOIN  MSKILLLEVEL     SL  ON  SL.SKILLID      = P.SKILLID
                                AND SL.SLNO         = P.CURRENTLEVNO
                                AND P.CURRENTLEVNO  > 0
LEFT  JOIN  MUSER           U   ON  U.USERID        = P.ASSESSEDBYID
                                AND P.ASSESSEDBYID  > 0
LEFT  JOIN  MEMPLOYEE       E   ON  E.EMPLOYEEID    = P.EMPLOYEEID

WHERE       P.EMPLOYEEID    = @EmployeeId
AND         P.TENANTID      = @TenantId
AND         (@ProfileId     IS NULL OR P.PROFILEID  = @ProfileId)
AND         (@SkillId       IS NULL OR P.SKILLID    = @SkillId)

ORDER BY    S.MATRIXSEQNO   ASC,
            S.SKILLNAME     ASC,
            P.CURRENTLEVNO  ASC";
        public const string INSERT = @"INSERT INTO MEMPLOYEESKILLPROFILE
(
    PROFILEID,
    PROFILENAME,
    EMPLOYEEID,
    SKILLID,
    PRODUCTFAMILYID,
    MODELID,
    CURRENTLEVNO,
    PROFILESTATUS,
    ASSESSEDDT,
    TRAININGPLANDATE,
    ACTUALTRAINDT,
    NEXTREVIEWDT,
    ASSESSEDBYID,
    REMARKS,
    VERSION,
    STATUS,
    SORTORDER,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    TENANTID
)
VALUES
(
    @ProfileId,
    @ProfileName,
    @EmployeeId,
    @SkillId,
    @ProductFamilyId,
    @ModelId,
    @CurrentLevNo,
    @ProfileStatus,
    @AssessedDt,
    @TrainingPlanDate,
    @ActualTrainDt,
    @NextReviewDt,
    @AssessedById,
    @Remarks,
    @Version,
    @Status,
    @SortOrder,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @SourceType,
    @TenantId
);";

        // ── UPSERT (MERGE) ───────────────────────────────────────
        // Write the live "current level" profile row.
        // Must always be called inside the same DB transaction
        // as INSERT into TASSESSMENT (E2).
        // Index hint : IX_MEMPLOYEESKILLPROFILE_EMPID_TENANTID
        //              (unique key: EMPLOYEEID, SKILLID,
        //               PRODUCTFAMILYID, MODELID, TENANTID)
        // ────────────────────────────────────────────────────────
        public const string UPSERT = @"
MERGE MEMPLOYEESKILLPROFILE AS T
USING (SELECT
        @EmployeeId       AS EmployeeId,
        @SkillId          AS SkillId,
        @ProductFamilyId  AS ProductFamilyId,
        @ModelId          AS ModelId,
        @TenantId         AS TenantId) AS S
ON  T.EMPLOYEEID      = S.EmployeeId
AND T.SKILLID         = S.SkillId
AND T.PRODUCTFAMILYID = S.ProductFamilyId
AND T.MODELID         = S.ModelId
AND T.TENANTID        = S.TenantId

WHEN MATCHED THEN
    UPDATE SET
        PROFILENAME       = @ProfileName,
        CURRENTLEVNO      = @CurrentLevNo,
        PROFILESTATUS     = @ProfileStatus,
        ASSESSEDDT        = @AssessedDt,
        TRAININGPLANDATE  = @TrainingPlanDate,
        ACTUALTRAINDT     = @ActualTrainDt,
        NEXTREVIEWDT      = @NextReviewDt,
        ASSESSEDBYID      = @AssessedById,
        REMARKS           = @Remarks,
        STATUS            = @Status,
        SORTORDER         = @SortOrder,
        MODIFIEDBYID      = @ModifiedById,
        MODIFIEDON        = @ModifiedOn,
        SOURCETYPE        = @SourceType,
        VERSION           = ISNULL(VERSION, 0) + 1

WHEN NOT MATCHED THEN
    INSERT (
        PROFILEID, PROFILENAME, EMPLOYEEID, SKILLID,
        PRODUCTFAMILYID, MODELID, CURRENTLEVNO, PROFILESTATUS,
        ASSESSEDDT, TRAININGPLANDATE, ACTUALTRAINDT, NEXTREVIEWDT,
        ASSESSEDBYID, REMARKS, VERSION, STATUS, SORTORDER,
        CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
        SOURCETYPE, TENANTID
    )
    VALUES (
        @ProfileId, @ProfileName, @EmployeeId, @SkillId,
        @ProductFamilyId, @ModelId, @CurrentLevNo, @ProfileStatus,
        @AssessedDt, @TrainingPlanDate, @ActualTrainDt, @NextReviewDt,
        @AssessedById, @Remarks, @Version, @Status, @SortOrder,
        @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn,
        @SourceType, @TenantId
    );";


        // ── BULK_UPSERT (MERGE on natural key) ───────────────────
        // Used by BulkSave — matches the UNIQUE KEY
        // UK_MEMPLOYEESKILLPROFILE_EMP_SKILL_SCOPE
        // (EMPLOYEEID, SKILLID, PRODUCTFAMILYID, MODELID, TENANTID)
        // If the row exists: UPDATE the live level + audit; keep
        //                    the original PROFILEID.
        // If missing       : INSERT with the supplied @ProfileId.
        // ────────────────────────────────────────────────────────
        public const string BULK_UPSERT = @"
MERGE MEMPLOYEESKILLPROFILE AS T
USING (SELECT
        @EmployeeId       AS EmployeeId,
        @SkillId          AS SkillId,
        @ProductFamilyId  AS ProductFamilyId,
        @ModelId          AS ModelId,
        @TenantId         AS TenantId) AS S
ON  T.EMPLOYEEID      = S.EmployeeId
AND T.SKILLID         = S.SkillId
AND T.PRODUCTFAMILYID = S.ProductFamilyId
AND T.MODELID         = S.ModelId
AND T.TENANTID        = S.TenantId

WHEN MATCHED THEN
    UPDATE SET
        PROFILENAME       = @ProfileName,
        CURRENTLEVNO      = @CurrentLevNo,
        PROFILESTATUS     = @ProfileStatus,
        ASSESSEDDT        = @AssessedDt,
        TRAININGPLANDATE  = @TrainingPlanDate,
        ACTUALTRAINDT     = @ActualTrainDt,
        NEXTREVIEWDT      = @NextReviewDt,
        ASSESSEDBYID      = @AssessedById,
        REMARKS           = @Remarks,
        STATUS            = @Status,
        SORTORDER         = @SortOrder,
        MODIFIEDBYID      = @ModifiedById,
        MODIFIEDON        = @ModifiedOn,
        SOURCETYPE        = @SourceType,
        VERSION           = ISNULL(VERSION, 0) + 1

WHEN NOT MATCHED THEN
    INSERT (
        PROFILEID, PROFILENAME, EMPLOYEEID, SKILLID,
        PRODUCTFAMILYID, MODELID, CURRENTLEVNO, PROFILESTATUS,
        ASSESSEDDT, TRAININGPLANDATE, ACTUALTRAINDT, NEXTREVIEWDT,
        ASSESSEDBYID, REMARKS, VERSION, STATUS, SORTORDER,
        CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
        SOURCETYPE, TENANTID
    )
    VALUES (
        @ProfileId, @ProfileName, @EmployeeId, @SkillId,
        @ProductFamilyId, @ModelId, @CurrentLevNo, @ProfileStatus,
        @AssessedDt, @TrainingPlanDate, @ActualTrainDt, @NextReviewDt,
        @AssessedById, @Remarks, @Version, @Status, @SortOrder,
        @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn,
        @SourceType, @TenantId
    );";


        // ── GET_CURRENT_LEVEL ────────────────────────────────────
        // Used to snapshot CURRENTLEVNO into TASSESSMENT.PREVIOUSLEVEL
        // before writing a new assessment row.
        // Returns 0 (default) when no active profile row exists yet.
        // ────────────────────────────────────────────────────────
        public const string GET_CURRENT_LEVEL = @"SELECT ISNULL(CURRENTLEVNO, 0) AS CurrentLevNo
    FROM MEMPLOYEESKILLPROFILE
    WHERE EMPLOYEEID = @EmployeeId
    AND SKILLID = @SkillId";


        // ── GET_BY_PROFILE_ID ────────────────────────────────────
        // Single row fetch by primary key — used after upsert to
        // return the saved state to the API caller.
        // ────────────────────────────────────────────────────────
        public const string GET_BY_PROFILE_ID = @"SELECT
    P.PROFILEID AS ProfileId,
    P.PROFILENAME   AS ProfileName,
    P.EMPLOYEEID      AS EmployeeId,
    P.SKILLID         AS SkillId,
    P.CURRENTLEVNO         AS CurrentLevNo,
    P.REMARKS     AS Remarks,

    S.SKILLNAME       AS SkillName,
    S.SKILLCODE       AS SkillCode,
    G.GROUPNAME       AS GroupName,
    G.GROUPCODE       AS GroupCode

FROM MEMPLOYEESKILLPROFILE P

INNER JOIN MSKILL S
    ON S.SKILLID = P.SKILLID

LEFT JOIN MSKILLGROUP G
    ON G.GROUPID = S.GROUPID
    where p.EMPLOYEEID = @EmployeeId";

        public const string GET_SLNO_BY_SLEVELID = @"
            SELECT SLNO FROM MSKILLLEVEL WHERE SLEVELID = @SlevelId AND SKILLID = @SkillId";

        public const string GET_EMPLOYEE_NAME = @"
            SELECT EMPLOYEENAME FROM MEMPLOYEE WHERE EMPLOYEEID = @EmployeeId";

        public const string GET_SLNO_BY_LEVEL_LABEL = @"
            SELECT TOP 1 SLNO FROM MSKILLLEVEL
            WHERE SKILLID    = @SkillId
            AND   LEVELLABEL = @LevelLabel";

        public const string GET_SELECT_LIST = @"
SELECT
    P.PROFILEID         AS ProfileId,
    P.PROFILENAME       AS ProfileName,
    P.EMPLOYEEID        AS EmployeeId,
    P.SKILLID           AS SkillId,
    P.PRODUCTFAMILYID   AS ProductFamilyId,
    P.MODELID           AS ModelId,
    P.CURRENTLEVNO      AS CurrentLevNo,
    P.PROFILESTATUS     AS ProfileStatus,
    P.ASSESSEDDT        AS AssessedDt,
    P.TRAININGPLANDATE  AS TrainingPlanDate,
    P.ACTUALTRAINDT     AS ActualTrainDt,
    P.NEXTREVIEWDT      AS NextReviewDt,
    P.ASSESSEDBYID      AS AssessedById,
    P.REMARKS           AS Remarks,
    P.STATUS            AS Status,
    P.TENANTID          AS TenantId
FROM MEMPLOYEESKILLPROFILE P
WHERE
    P.TENANTID = @TenantId
    {DYNAMIC_WHERE}
ORDER BY ProfileId ASC";

        public const string GET_LIST = @"SELECT
    -- EMPLOYEESKILL (P)
    P.PROFILEID             AS ProfileId,
    P.PROFILENAME           AS ProfileName,
    P.EMPLOYEEID            AS EmployeeId,
    P.SKILLID               AS SkillId,
    P.CURRENTLEVNO          AS CurrentLevNo,
    P.REMARKS               AS Remarks,
    P.PRODUCTFAMILYID       AS ProductFamilyId,
    IT.ITEMCODE             AS ProductFamilyCode,
    IT.ITEMNAME             AS ProductFamilyName,
    P.MODELID               AS ModelId,
    MO.MODELCODE            AS ModelCode,
    MO.MODELNAME            AS ModelName,
    P.PROFILESTATUS         AS ProfileStatus,
    P.ASSESSEDDT            AS AssessesDate,
    P.TRAININGPLANDATE      AS TrainingPlanDate,
    P.ACTUALTRAINDT         AS ActualTrainingDate,
    P.NEXTREVIEWDT          AS NextReviewDate,
    P.ASSESSEDBYID          AS AssessedById,

    -- MSKILL (S)
    S.SKILLNAME             AS SkillName,
    S.SKILLNAMEFULL         AS SkillNameFull,
    S.SKILLCODE             AS SkillCode,
    S.SKILLSCOPE            AS SkillScope,
    S.ISSPECIALCHAR         AS IsSpecialChar,
    S.LEVELCOUNT            AS LevelCount,
    S.MATRIXSEQNO           AS MatrixSeqNo,
    S.GROUPID               AS GroupId,
    S.TENANTID              AS TenantId,

    -- MSKILLGROUP (G)
    G.GROUPNAME             AS GroupName,

    -- MSKILLLEVEL (SL)
    SL.LEVELLABEL           AS LevelLabel,
    SL.LEVELDESCRIPTOR      AS LevelDescriptor,

    -- MEMPLOYEE (E)
    E.EMPLOYEENAME          AS EmployeeName,
    E.THUMBNAIL             AS Thumbnail

FROM        MEMPLOYEESKILLPROFILE  P

LEFT  JOIN  MITEM           IT  ON  IT.ITEMID       = P.PRODUCTFAMILYID
                                AND P.PRODUCTFAMILYID != -1
LEFT  JOIN  MMODEL          MO  ON  MO.MODELID      = P.MODELID
                                AND P.MODELID       != -1
INNER JOIN  MSKILL          S   ON  S.SKILLID       = P.SKILLID
                                AND S.STATUS        = 1
LEFT  JOIN  MSKILLGROUP     G   ON  G.GROUPID       = S.GROUPID
                                AND G.STATUS        = 1
LEFT  JOIN  MSKILLLEVEL     SL  ON  SL.SKILLID      = P.SKILLID
                                AND SL.SLNO         = P.CURRENTLEVNO
                                AND P.CURRENTLEVNO  > 0
LEFT  JOIN  MEMPLOYEE       E   ON  E.EMPLOYEEID    = P.EMPLOYEEID
";

    }
}