// ============================================================
// GoodBooks ERP — Skill Management Module
// DTO    : SkillMatrix DTOs  (multiple classes — one file)
// Tables : MSKILLMATRIX (E3), MSKILL (S3), MSKILLPRODUCT (M1),
//          MEMPLOYEESKILL (E1), MSKILLLEVEL (S4),
//          MJOBROLESKILL (M3), MROUTINGSKILL (M2),
//          MEMPLOYEE (external), MJOBROLE (external)
// Updated: Aligned to new DDL schema v1.0
//
// ASSEMBLY NOTE:
//   SkillMatrixViewDTO is assembled in BLL memory — it is NOT
//   returned by a single SQL query. The BLL calls multiple QB
//   queries and stitches the result together.
// ============================================================

namespace SkillManagementDAL.DTO.SkillMatrix
{
    // ── MSKILLMATRIX config DTO ──────────────────────────────────
    /// <summary>
    /// Full config record for one matrix definition.
    /// Maps directly to MSKILLMATRIX (E3).
    /// Used by the matrix setup/admin screen.
    /// Was missing entirely from old DTO file.
    /// </summary>
    public class SkillMatrixConfigDTO
    {
        // Primary key
        public int ConfigId { get; set; }

        // Business columns  (MSKILLMATRIX)
        public string MatrixCode { get; set; } = string.Empty;  // MSKILLMATRIX.MATRIXCODE  UK per TENANTID
        public string MatrixTitle { get; set; } = string.Empty;  // MSKILLMATRIX.MATRIXTITLE
        public int DomainId { get; set; } = -1;            // MSKILLMATRIX.DOMAINID  -1=all domains
        public string? TargetDept { get; set; }                  // MSKILLMATRIX.TARGETDEPT  NULL=all
        public string? TargetRoleIds { get; set; }                  // MSKILLMATRIX.TARGETROLEIDS  JSON int array e.g. [10,11]
        public int ProductFamilyId { get; set; } = -1;            // MSKILLMATRIX.PRODUCTFAMILYID  -1=not scoped
        public string? ModelIds { get; set; }                  // MSKILLMATRIX.MODELIDS  JSON int array
        public string IncludeSkillIds { get; set; } = string.Empty;  // MSKILLMATRIX.INCLUDESKILLIDS  JSON ordered int array — mandatory
        public byte ShowSummaryRow { get; set; } = 1;             // MSKILLMATRIX.SHOWSUMMARYROW  0=No,1=Yes
        public byte ShowMinLevel { get; set; } = 1;             // MSKILLMATRIX.SHOWMINLEVEL    0=No,1=Yes
        public string? UpdateFrequency { get; set; }                  // MSKILLMATRIX.UPDATEFREQUENCY  e.g. 'Monthly','Quarterly'
        public byte IsActive { get; set; } = 1;             // MSKILLMATRIX.ISACTIVE  0=No,1=Yes

        // Standard fields  (MSKILLMATRIX)
        public short Version { get; set; }
        public byte Status { get; set; } = 1;             // 0=Pending,1=Active,2=Deleted,3=Amended,4=Inactive,5=Archived
        public short SortOrder { get; set; } = 9999;
        public int CreatedById { get; set; }
        public DateTime? CreatedOn { get; set; }
        public int ModifiedById { get; set; }
        public DateTime? ModifiedOn { get; set; }
        public byte SourceType { get; set; } = 5;
        public int TenantId { get; set; }

        // Joined display fields
        public string? DomainName { get; set; }                  // MSKILLDOMAIN.DOMAINNAME  (when DomainId != -1)
    }


    // ── View DTO — assembled in BLL ──────────────────────────────
    /// <summary>
    /// Complete matrix view assembled in BLL memory from multiple
    /// queries. Not returned by a single SQL query.
    /// </summary>
    public class SkillMatrixViewDTO
    {
        // Config context — from MSKILLMATRIX (E3)
        public int ConfigId { get; set; }
        public string MatrixCode { get; set; } = string.Empty;  // Added — needed to identify the matrix
        public string MatrixTitle { get; set; } = string.Empty;
        public int? UpdateFrequency { get; set; }
        public byte ShowSummaryRow { get; set; }                  // 0=No, 1=Yes
        public byte ShowMinLevel { get; set; }                  // 0=No, 1=Yes

        // Scope descriptor — computed from MSKILLMATRIX fields
        // Values: "General" | "ProductFamily" | "Model"
        // Maps to MSKILL.SKILLSCOPE: 0=General, 1=ProductFamily, 2=Model
        public string Scope { get; set; } = string.Empty;

        // Optional scope context — from MSKILLMATRIX
        public string? TargetDept { get; set; }                  // MSKILLMATRIX.TARGETDEPT
        public int ProductFamilyId { get; set; } = -1;            // MSKILLMATRIX.PRODUCTFAMILYID

        public List<SkillMatrixColumnDTO> Columns { get; set; } = new();
        public List<SkillMatrixRowDTO> Rows { get; set; } = new();
        public List<SkillMatrixSummaryDTO> Summary { get; set; } = new();
    }


    // ── Column header DTO ────────────────────────────────────────
    /// <summary>
    /// One column in the matrix header. Sourced from MSKILL (S3)
    /// and MSKILLPRODUCT (M1) for product/model scoped skills.
    ///
    /// ColumnKey pattern (from DDL COLUMN KEY PATTERN):
    ///   SKILLSCOPE=0 General        → "{SkillId}_0"
    ///   SKILLSCOPE=1 ProductFamily  → "{SkillId}_{ProductFamilyId}"
    ///   SKILLSCOPE=2 Model          → "{SkillId}_{ModelId}"
    ///
    /// MinLevelRequired source:
    ///   Loaded from MJOBROLESKILL.MINLEVNO (job role minimum)
    ///   OR MROUTINGSKILL.MINLEVELREQUIRED (routing op minimum)
    ///   depending on matrix scope. See SkillMatrixQB comments.
    /// </summary>
    public class SkillMatrixColumnDTO
    {
        public int SkillId { get; set; }
        public string SkillName { get; set; } = string.Empty;  // MSKILL.SKILLNAME
        public string? SkillCode { get; set; }                  // MSKILL.SKILLCODE

        public byte SkillScope { get; set; }                    // 0=General,1=ProductFamily,2=Model
        public byte IsSpecialChar { get; set; }                 // 0/1 star indicator

        public short MatrixSeqNo { get; set; }                  // column ordering
        public byte LevelCount { get; set; }                    // 1-4 levels

        public byte MinLevelRequired { get; set; }              // from job/routing logic (not SQL yet)

        public string? ScopeLabel { get; set; }                 // e.g. "General", "Model X200"

        public int ProductFamilyId { get; set; } = -1;          // -1 = not applicable
        public int ModelId { get; set; } = -1;                  // -1 = not applicable

        public string ColumnKey { get; set; } = string.Empty;   // "{SkillId}_0" / "{SkillId}_{PFId}" / "{SkillId}_{ModelId}"

        public int GroupId { get; set; } = -1;                  // FIXED: safe default for NULL groups
        public string? GroupName { get; set; }                 // MSKILLGROUP.GROUPNAME
    }


    // ── Employee row DTO ─────────────────────────────────────────
    /// <summary>One employee row — cells keyed by ColumnKey.</summary>
    public class SkillMatrixRowDTO
    {
        public int EmployeeId { get; set; }
        public string EmployeeName { get; set; } = string.Empty;
        public string? Department { get; set; }
        public string? RoleName { get; set; }
        public string? Thumbnail { get; set; }
        public Dictionary<string, SkillMatrixCellDTO> Cells { get; set; } = new();
    }


    // ── Cell DTO ─────────────────────────────────────────────────
    /// <summary>
    /// One cell — one employee x one skill(xscope) intersection.
    /// Sourced from MEMPLOYEESKILL (E1) and MSKILLLEVEL (S4).
    /// NULL ProfileId means the employee has no profile row for
    /// this skill yet (CurrentLevNo defaults to 0 = NotAssessed).
    /// </summary>
    public class SkillMatrixCellDTO
    {
        public int? ProfileId { get; set; }               // MEMPLOYEESKILL.PROFILEID  NULL = not assessed yet
        public byte CurrentLevNo { get; set; }               // MEMPLOYEESKILL.CURRENTLEVNO  0=NotAssessed,1-4
        public byte ProfileStatus { get; set; }               // MEMPLOYEESKILL.PROFILESTATUS  0=NotAssessed,1=Current,2=TrainingPlan,3=Expired
        public DateOnly? AssessedDt { get; set; }               // MEMPLOYEESKILL.ASSESSEDDT
        public DateOnly? TrainingPlanDate { get; set; }               // MEMPLOYEESKILL.TRAININGPLANDATE
        public DateOnly? ActualTrainDt { get; set; }               // MEMPLOYEESKILL.ACTUALTRAINDT
        public DateOnly? NextReviewDt { get; set; }               // MEMPLOYEESKILL.NEXTREVIEWDT  (added — for expired review highlighting)
        public string? LevelLabel { get; set; }               // MSKILLLEVEL.LEVELLABEL at SLNO = CurrentLevNo
        public byte MinLevelRequired { get; set; }               // From column — MJOBROLESKILL.MINLEVNO
        public bool MeetsMinLevel { get; set; }               // Computed in BLL: CurrentLevNo >= MinLevelRequired
    }


    // ── Summary row DTO ──────────────────────────────────────────
    /// <summary>Summary row — qualified count per column.</summary>
    public class SkillMatrixSummaryDTO
    {
        public string ColumnKey { get; set; } = string.Empty;   // Matches SkillMatrixColumnDTO.ColumnKey
        public int QualifiedCount { get; set; }                    // Employees meeting MinLevelRequired for this column
        public int TotalEmployees { get; set; }                    // Total employees in the matrix
    }


    // ── Internal: product scope ──────────────────────────────────
    /// <summary>
    /// Internal DTO — product/model scope rows from MSKILLPRODUCT (M1).
    /// Used when building columns for SKILLSCOPE=1 or SKILLSCOPE=2 skills.
    /// ProductFamilyName and ModelName resolved via BLL or QB join.
    /// </summary>
    public class SkillProductScopeDTO
    {
        public int SkillId { get; set; }               // MSKILLPRODUCT.SKILLID
        public short SlNo { get; set; }               // MSKILLPRODUCT.SLNO  (added — sequence within skill)
        public int ProductFamilyId { get; set; } = -1;         // MSKILLPRODUCT.PRODUCTFAMILYID  -1=not applicable
        public int ModelId { get; set; } = -1;         // MSKILLPRODUCT.MODELID  -1=not applicable
        public string? ProductFamilyName { get; set; }               // MPRODUCTFAMILY.FAMILYNAME  (from join in QB)
        public string? ModelName { get; set; }               // MMODEL.MODELNAME  (from join in QB)
    }


    // ── Internal: employee row loader ────────────────────────────
    /// <summary>
    /// Internal DTO — employee rows loaded for a matrix.
    /// Sourced from MEMPLOYEE (external) with optional MJOBROLE join.
    /// </summary>
    public class SkillMatrixEmployeeDTO
    {
        public int EmployeeId { get; set; }               // MEMPLOYEE.EMPLOYEEID
        public string EmployeeName { get; set; } = string.Empty; // MEMPLOYEE.EMPLOYEENAME
        public string? Department { get; set; }               // MEMPLOYEE.DEPARTMENT
        public int RoleId { get; set; } = -1;         // MEMPLOYEE.ROLEID  (added — needed for role-based min level lookup)
        public string? RoleName { get; set; }               // MJOBROLE.ROLENAME
        public string? Thumbnail { get; set; }            // MEMPLOYEE.THUMBNAIL
    }


    // ── Internal: profile cell loader ────────────────────────────
    /// <summary>
    /// Internal DTO — profile cell data loaded from MEMPLOYEESKILL (E1).
    /// One row per (EmployeeId, SkillId, ProductFamilyId, ModelId).
    /// Matched to matrix columns by ColumnKey in BLL.
    /// </summary>
    public class SkillMatrixProfileCellDTO
    {
        public int EmployeeId { get; set; }
        public int SkillId { get; set; }
        public int ProductFamilyId { get; set; } = -1;
        public int ModelId { get; set; } = -1;

        // Must be nullable because missing profile rows are allowed
        public int? ProfileId { get; set; }

        public byte CurrentLevNo { get; set; }
        public byte ProfileStatus { get; set; }

        // IMPORTANT:
        // Dapper cannot automatically convert SQL DATETIME to DateOnly.
        // Use DateTime? here, or add a custom TypeHandler.
        public DateTime? AssessedDt { get; set; }
        public DateTime? TrainingPlanDate { get; set; }
        public DateTime? ActualTrainDt { get; set; }
        public DateTime? NextReviewDt { get; set; }

        public string? LevelLabel { get; set; }
    }


    // ── Internal: min level per skill ────────────────────────────
    /// <summary>
    /// Internal DTO — minimum required level per skill.
    /// Sourced from MJOBROLESKILL.MINLEVNO via GET_MIN_LEVELS query.
    ///
    /// IMPORTANT: GET_MIN_LEVELS uses MIN(MINLEVNO) aggregated across
    /// ALL roles for each skill. This returns the lowest bar across
    /// all job roles. If the matrix is role-scoped via
    /// MSKILLMATRIX.TARGETROLEIDS, the QB query must be called with
    /// @RoleIds filter to get the role-specific minimum.
    /// See SkillMatrixQB.GET_MIN_LEVELS_BY_ROLES for scoped variant.
    /// </summary>
    public class SkillMinLevelDTO
    {
        public int SkillId { get; set; }                           // MJOBROLESKILL.SKILLID
        public byte MinLevNo { get; set; }                           // MIN(MJOBROLESKILL.MINLEVNO) across target roles
    }
}