using GB5Shared.DTO.Framework.Login;
using SkillManagementDAL.CustomCode.SkillMatrix;
using SkillManagementDAL.CustomCode.SkillMatrixConfig;
using SkillManagementDAL.DTO.SkillMatrix;

namespace SkillManagementBLL.SkillMatrix
{
    public class SkillMatrixBLL : ISkillMatrixBLL
    {
        private readonly ISkillMatrixConfigDAL _configDal;
        private readonly ISkillMatrixDAL _matrixDal;

        public SkillMatrixBLL(ISkillMatrixConfigDAL configDal, ISkillMatrixDAL matrixDal)
        {
            _configDal = configDal;
            _matrixDal = matrixDal;
        }

        // ============================================================
        // GoodBooks ERP — Skill Management Module
        // Layer   : Business Logic Layer (BLL)
        // Updated : 2026-06-22 — Final fix pass v4
        //
        // FIXES IN THIS VERSION:
        //
        //   [1]  TargetDept normalisation in DAL (GetEmployeesForMatrix).
        //        BLL passes config.TargetDept as-is; DAL handles '' → null.
        //
        //   [2]  Role-scoped min levels: BLL selects the correct DAL method.
        //        GetMinLevelsByRoles called when valid non-zero RoleIds exist
        //        and ALL-marker is absent. GetMinLevels used otherwise.
        //
        //   [3]  BuildRows — cell key for General scope (SkillScope=0) now
        //        explicitly uses "_-1_-1" suffix. SQL normalises
        //        ProductFamilyId/ModelId to -1 for Scope=0 so keys match.
        //
        //   [4]  MeetsMinLevel guard: only true when MinLevelRequired > 0.
        //        Avoids falsely marking everyone "qualified" when no minimum
        //        is configured for a skill.
        //
        //   [5]  BuildScopeLabel: sentinel values (-1500000000, 0, -1) never
        //        exposed in label. Shows "Roles: All" for ALL-marker.
        //
        //   [6]  SkillMatrixViewDTO maps MatrixCode from config.
        //
        //   [7]  ROLE FILTER REMOVED FROM PHASE 6 — DB INVESTIGATION CONFIRMED:
        //        TARGETROLEIDS stores MJOBROLESKILL.ROLEID values.
        //        These are used ONLY for min-level calculation (Phase 7).
        //        MEMPLOYEE.DESIGNATIONID is a completely separate ID space
        //        and CANNOT be compared against TARGETROLEIDS in memory.
        //        Confirmed by DB: employee DESIGNATIONID values
        //        (-1199998659, 1100000117, -1199998460) do not exist
        //        anywhere in MJOBROLESKILL.ROLEID.
        //        Phase 6 in-memory role filter deleted entirely.
        //        GetEmployeesForMatrix reverted to original 3-arg signature.
        //        Employee scope = TARGETDEPT alone.
        //
        //   [8]  BuildColumns Scope=1 now explicitly sets ModelId = -1
        //        to guarantee cell key suffix is "_-1" not "_0".
        //
        //   [9]  Min-level aggregation changed from Min() to Max().
        //        Max() = strictest requirement across all configured roles.
        //        Min() was incorrectly letting the lowest-requirement role
        //        pass everyone as qualified.
        //
        //  [10]  Phase 8 early-return now calls BuildSummary() with an
        //        empty rows list instead of returning Summary = [].
        //        Ensures UI gets consistent column-keyed summary entries
        //        even when no employees are found.
        // ============================================================

        public async Task<SkillMatrixViewDTO> BuildMatrix(
            int configId,
            LoginDTO login,
            CancellationToken ct)
        {
            // ============================================================
            // 1. Load Matrix Configuration
            // ============================================================
            var config = await _configDal.GetById(configId, login, ct)
                         ?? throw new InvalidOperationException(
                             $"Skill Matrix configuration '{configId}' not found.");

            if (config.IncludeSkillIds == null || config.IncludeSkillIds.Count == 0)
                throw new InvalidOperationException(
                    "No skills configured for this matrix.");

            var includeSkillIds = config.IncludeSkillIds.Distinct().ToList();

            // ============================================================
            // 2. Determine role strategy for min-level query ONLY
            //
            //    TARGETROLEIDS purpose in this system:
            //      → Drives GetMinLevelsByRoles (Phase 7) only.
            //      → Controls which role's skill requirements set
            //        MinLevelRequired on each column.
            //      → Does NOT filter employees.
            //        MEMPLOYEE.DESIGNATIONID and MJOBROLESKILL.ROLEID
            //        are completely separate ID spaces (confirmed by DB).
            //
            //    ALL-marker (-1500000000) → use general min-levels query.
            //    Valid role IDs present   → use role-scoped min-levels query.
            //    Empty list               → use general min-levels query.
            //
            //    Employee scope = TARGETDEPT only (Phase 3-Q3).
            // ============================================================
            const int AllRolesMarker = -1500000000;

            bool hasAllMarker = config.TargetRoleIds?.Contains(AllRolesMarker) ?? false;

            // All non-zero, non-AllMarker IDs — MJOBROLESKILL.ROLEID values.
            // Negative GoodBooks IDs are valid and kept deliberately.
            var validTargetRoleIds = (config.TargetRoleIds ?? new List<int>())
                .Where(r => r != 0 && r != AllRolesMarker)
                .Distinct()
                .ToList();

            bool useRoleScopedMinLevels = !hasAllMarker && validTargetRoleIds.Count > 0;

            // ============================================================
            // 3. Execute independent queries in parallel
            // ============================================================
            var skillsTask = _matrixDal.GetSkillsForMatrix(
                includeSkillIds, login, ct);

            var scopeTask = _matrixDal.GetProductScopeForSkills(
                includeSkillIds, login, ct);

            // FIX [1]: DAL normalises empty/whitespace TargetDept → null
            // so SQL (@TargetDept IS NULL) branch fires for "all employees".
            // FIX [7]: No roleIds passed — employee scope = dept only.
            var employeesTask = _matrixDal.GetEmployeesForMatrix(
                config.TargetDept,
                login,
                ct);

            // FIX [7]: validTargetRoleIds used for min-levels only.
            var minLevelsTask = useRoleScopedMinLevels
                ? _matrixDal.GetMinLevelsByRoles(includeSkillIds, validTargetRoleIds, login, ct)
                : _matrixDal.GetMinLevels(includeSkillIds, login, ct);

            await Task.WhenAll(skillsTask, scopeTask, employeesTask, minLevelsTask);

            // ============================================================
            // 4. Reorder skills per IncludeSkillIds sequence
            //    Preserves config-defined column order regardless of
            //    DB return order.
            // ============================================================
            var skillRows = await skillsTask;
            var skillLookup = skillRows.ToDictionary(x => x.SkillId);

            var orderedSkills = includeSkillIds
                .Where(skillLookup.ContainsKey)
                .Select(id => skillLookup[id])
                .ToList();

            if (orderedSkills.Count == 0)
                throw new InvalidOperationException(
                    $"None of the {includeSkillIds.Count} configured skill IDs " +
                    "were found in the database.");

            // ============================================================
            // 5. Build dynamic columns
            //    FIX [8]: Scope=1 columns explicitly set ModelId = -1
            //    so cell key suffix is always "_-1" not "_0".
            // ============================================================
            var productScopes = (await scopeTask).ToList();
            var columns = BuildColumns(orderedSkills, productScopes);

            // ============================================================
            // 6. Employees — no role filter applied
            //    FIX [7]: Phase 6 role filter block removed entirely.
            //    MEMPLOYEE.DESIGNATIONID ≠ MJOBROLESKILL.ROLEID space.
            //    All dept employees returned by DAL pass through as-is.
            // ============================================================
            var employees = (await employeesTask).ToList();

            // ============================================================
            // 7. Assign MinLevelRequired to each column
            //    FIX [9]: Max() = strictest requirement across all roles.
            //    Min() was incorrectly passing everyone via lowest role.
            // ============================================================
            var minLevels = (await minLevelsTask)
                .GroupBy(x => x.SkillId)
                .ToDictionary(g => g.Key, g => g.Max(x => x.MinLevNo));

            foreach (var col in columns)
            {
                col.MinLevelRequired = minLevels.TryGetValue(col.SkillId, out var ml)
                    ? ml
                    : (byte)0;
            }

            // ============================================================
            // 8. If no employees → return empty matrix safely
            //    FIX [10]: BuildSummary() called with empty list so UI
            //    gets column-keyed summary entries with TotalEmployees=0.
            // ============================================================
            if (employees.Count == 0)
            {
                return new SkillMatrixViewDTO
                {
                    ConfigId = configId,
                    MatrixCode = config.MatrixCode,
                    MatrixTitle = config.MatrixTitle,
                    UpdateFrequency = config.UpdateFrequency,
                    ShowSummaryRow = config.ShowSummaryRow,
                    ShowMinLevel = config.ShowMinLevel,
                    Scope = BuildScopeLabel(config),
                    Columns = columns,
                    Rows = new List<SkillMatrixRowDTO>(),
                    Summary = BuildSummary(columns, new List<SkillMatrixRowDTO>())
                };
            }

            // ============================================================
            // 9. Load Profile Cells
            //    Key: "{EmployeeId}_{SkillId}_{ProductFamilyId}_{ModelId}"
            //    SQL CASE in GET_PROFILE_CELLS normalises PF/Model per
            //    SkillScope so keys match BLL switch in BuildRows exactly.
            // ============================================================
            var employeeIds = employees.Select(x => x.EmployeeId).Distinct().ToList();

            var cells = (await _matrixDal.GetProfileCells(
                includeSkillIds, employeeIds, login, ct)).ToList();

            // Duplicate cell rows can occur when MEMPLOYEESKILL has multiple
            // profile records for the same employee+skill+scope (e.g. two assessments).
            // SQL CASE normalisation collapses them to the same key, causing ToDictionary
            // to throw. Group and keep the row with the highest ProfileStatus, then
            // highest ProfileId (most recently inserted) as a tiebreaker.
            var cellLookup = cells
                .GroupBy(x => $"{x.EmployeeId}_{x.SkillId}_{x.ProductFamilyId}_{x.ModelId}")
                .ToDictionary(
                    g => g.Key,
                    g => g.OrderByDescending(x => x.ProfileStatus)
                           .ThenByDescending(x => x.ProfileId)
                           .First());

            // ============================================================
            // 10. Build Rows
            // ============================================================
            var rows = BuildRows(employees, columns, cellLookup);

            // ============================================================
            // 11. Build Summary
            // ============================================================
            var summary = BuildSummary(columns, rows);

            // ============================================================
            // 12. Return final DTO
            // ============================================================
            return new SkillMatrixViewDTO
            {
                ConfigId = config.ConfigId,
                MatrixCode = config.MatrixCode,
                MatrixTitle = config.MatrixTitle,
                UpdateFrequency = config.UpdateFrequency,
                ShowSummaryRow = config.ShowSummaryRow,
                ShowMinLevel = config.ShowMinLevel,
                Scope = BuildScopeLabel(config),
                Columns = columns,
                Rows = rows,
                Summary = summary
            };
        }


        // ================================================================
        // BuildColumns
        //
        // Scope 0 → single "General" column
        //           ColumnKey       = "{SkillId}_0"
        //           ProductFamilyId = -1
        //           ModelId         = -1
        //
        // Scope 1 → one column per ProductFamily
        //           ColumnKey       = "{SkillId}_{ProductFamilyId}"
        //           ModelId         = -1  ← FIX [8]: explicit, not default
        //
        // Scope 2 → one column per Model
        //           ColumnKey       = "{SkillId}_{ModelId}"
        //           ProductFamilyId = real value
        //           ModelId         = real value
        // ================================================================
        private static List<SkillMatrixColumnDTO> BuildColumns(
            IEnumerable<SkillMatrixColumnDTO> skills,
            IEnumerable<SkillProductScopeDTO> productScopes)
        {
            var columns = new List<SkillMatrixColumnDTO>();
            var scopeList = productScopes.ToList();

            foreach (var skill in skills)
            {
                if (skill.SkillScope == 0)
                {
                    columns.Add(new SkillMatrixColumnDTO
                    {
                        SkillId = skill.SkillId,
                        SkillCode = skill.SkillCode,
                        SkillName = skill.SkillName,
                        SkillScope = skill.SkillScope,
                        IsSpecialChar = skill.IsSpecialChar,
                        MatrixSeqNo = skill.MatrixSeqNo,
                        LevelCount = skill.LevelCount,
                        GroupId = skill.GroupId,
                        GroupName = skill.GroupName,
                        ScopeLabel = "General",
                        ProductFamilyId = -1,
                        ModelId = -1,
                        ColumnKey = $"{skill.SkillId}_0"
                    });
                }
                else
                {
                    var scopes = scopeList.Where(p => p.SkillId == skill.SkillId);

                    foreach (var scope in scopes)
                    {
                        // Scope 1 ColumnKey uses ProductFamilyId.
                        // Scope 2 ColumnKey uses ModelId.
                        var columnKey = skill.SkillScope == 2
                            ? $"{skill.SkillId}_{scope.ModelId}"
                            : $"{skill.SkillId}_{scope.ProductFamilyId}";

                        columns.Add(new SkillMatrixColumnDTO
                        {
                            SkillId = skill.SkillId,
                            SkillCode = skill.SkillCode,
                            SkillName = skill.SkillName,
                            SkillScope = skill.SkillScope,
                            IsSpecialChar = skill.IsSpecialChar,
                            MatrixSeqNo = skill.MatrixSeqNo,
                            LevelCount = skill.LevelCount,
                            GroupId = skill.GroupId,
                            GroupName = skill.GroupName,
                            ScopeLabel = scope.ModelName ?? scope.ProductFamilyName ?? "Unknown",
                            ProductFamilyId = scope.ProductFamilyId,
                            // FIX [8]: Scope=1 must be -1 explicitly.
                            // If left as DTO default (0), BuildRows builds
                            // cell key suffix "_0" which never matches
                            // SQL output "_-1" → silent lookup miss.
                            ModelId = skill.SkillScope == 1 ? -1 : scope.ModelId,
                            ColumnKey = columnKey
                        });
                    }
                }
            }

            return columns;
        }


        // ================================================================
        // BuildRows
        //
        // Cell key format matches SQL CASE normalisation in
        // GET_PROFILE_CELLS exactly:
        //
        //   Scope 0 (General)       → "{EmpId}_{SkillId}_-1_-1"
        //   Scope 1 (ProductFamily) → "{EmpId}_{SkillId}_{PFId}_-1"
        //   Scope 2 (Model)         → "{EmpId}_{SkillId}_{PFId}_{ModelId}"
        //
        // col.ModelId is guaranteed -1 for Scope=1 via FIX [8] above.
        // ================================================================
        private static List<SkillMatrixRowDTO> BuildRows(
            IEnumerable<SkillMatrixEmployeeDTO> employees,
            IEnumerable<SkillMatrixColumnDTO> columns,
            Dictionary<string, SkillMatrixProfileCellDTO> cellLookup)
        {
            var rows = new List<SkillMatrixRowDTO>();
            var colList = columns.ToList();

            foreach (var emp in employees)
            {
                var row = new SkillMatrixRowDTO
                {
                    EmployeeId = emp.EmployeeId,
                    EmployeeName = emp.EmployeeName,
                    Department = emp.Department,
                    RoleName = emp.RoleName,
                    Thumbnail = emp.Thumbnail
                };

                foreach (var col in colList)
                {
                    // Build lookup key — must match SQL CASE-normalised values.
                    var cellKey = col.SkillScope switch
                    {
                        0 => $"{emp.EmployeeId}_{col.SkillId}_-1_-1",
                        1 => $"{emp.EmployeeId}_{col.SkillId}_{col.ProductFamilyId}_-1",
                        2 => $"{emp.EmployeeId}_{col.SkillId}_{col.ProductFamilyId}_{col.ModelId}",
                        _ => $"{emp.EmployeeId}_{col.SkillId}_-1_-1"   // safe fallback
                    };

                    if (cellLookup.TryGetValue(cellKey, out var pc))
                    {
                        // Guard: clamp CurrentLevNo if it exceeds LevelCount.
                        // DAL should never return an out-of-range value but
                        // defensive clamp avoids phantom level rendering.
                        var safeLevNo = (pc.CurrentLevNo > col.LevelCount && col.LevelCount > 0)
                            ? (byte)0
                            : pc.CurrentLevNo;

                        // LevelLabel fallback: if MSKILLLEVEL JOIN missed the
                        // level row, show numeric level instead of blank cell.
                        var levelLabel = !string.IsNullOrWhiteSpace(pc.LevelLabel)
                            ? pc.LevelLabel
                            : safeLevNo > 0 ? safeLevNo.ToString() : null;

                        row.Cells[col.ColumnKey] = new SkillMatrixCellDTO
                        {
                            ProfileId = pc.ProfileId,
                            CurrentLevNo = safeLevNo,
                            ProfileStatus = pc.ProfileStatus,

                            AssessedDt = pc.AssessedDt.HasValue
                                ? DateOnly.FromDateTime(pc.AssessedDt.Value) : null,

                            TrainingPlanDate = pc.TrainingPlanDate.HasValue
                                ? DateOnly.FromDateTime(pc.TrainingPlanDate.Value) : null,

                            ActualTrainDt = pc.ActualTrainDt.HasValue
                                ? DateOnly.FromDateTime(pc.ActualTrainDt.Value) : null,

                            NextReviewDt = pc.NextReviewDt.HasValue
                                ? DateOnly.FromDateTime(pc.NextReviewDt.Value) : null,

                            LevelLabel = levelLabel,
                            MinLevelRequired = col.MinLevelRequired,

                            // FIX [4]: MeetsMinLevel only true when a minimum
                            // IS configured AND employee meets or exceeds it.
                            // Guards against falsely marking all as qualified
                            // when MinLevelRequired = 0 (not configured).
                            MeetsMinLevel = col.MinLevelRequired > 0
                                            && safeLevNo >= col.MinLevelRequired
                        };
                    }
                    else
                    {
                        // No profile record exists for this employee × skill/scope.
                        // Emit a complete empty cell so the grid stays consistent —
                        // every row has the same number of cells as there are columns.
                        row.Cells[col.ColumnKey] = new SkillMatrixCellDTO
                        {
                            ProfileId = null,
                            CurrentLevNo = 0,
                            ProfileStatus = 0,   // 0 = NotAssessed
                            LevelLabel = null,
                            MinLevelRequired = col.MinLevelRequired,
                            MeetsMinLevel = false
                        };
                    }
                }

                rows.Add(row);
            }

            return rows;
        }


        // ================================================================
        // BuildSummary
        //
        // Returns one entry per column with:
        //   TotalEmployees = total row count (all employees in matrix)
        //   QualifiedCount = employees whose cell.MeetsMinLevel = true
        //
        // Called with an empty rows list at Phase 8 early-return so the
        // UI always receives column-keyed summary entries (FIX [10]).
        // ================================================================
        private static List<SkillMatrixSummaryDTO> BuildSummary(
            IEnumerable<SkillMatrixColumnDTO> columns,
            IEnumerable<SkillMatrixRowDTO> rows)
        {
            var rowList = rows.ToList();
            int totalEmployees = rowList.Count;

            return columns.Select(col => new SkillMatrixSummaryDTO
            {
                ColumnKey = col.ColumnKey,
                TotalEmployees = totalEmployees,
                QualifiedCount = rowList.Count(r =>
                    r.Cells.TryGetValue(col.ColumnKey, out var cell) && cell.MeetsMinLevel)
            }).ToList();
        }


        // ================================================================
        // BuildScopeLabel
        //
        // Builds a human-readable scope string for the matrix header.
        //
        // Rules:
        //   Dept  — shown only when a real TARGETDEPT is stored.
        //           Uses config.DepartmentName (resolved in DAL GetById)
        //           if available, otherwise falls back to raw TARGETDEPT.
        //   Roles — shown as resolved names via config.RoleNames
        //           (DAL GetById joins OPENJSON(TARGETROLEIDS) to role
        //           master for names). Falls back to raw IDs if blank.
        //           ALL-marker (-1500000000) → "Roles: All".
        //           Sentinel values (0, -1, AllMarker) never exposed.
        //   Empty — falls back to "All Employees".
        //
        // Examples:
        //   "Dept: Quality Control | Roles: Admin, Operator"
        //   "Dept: Assembly | Roles: All"
        //   "Roles: Operator"
        //   "All Employees"
        // ================================================================
        private static string BuildScopeLabel(
            SkillManagementDAL.DTO.SkillMatrixConfig.SkillMatrixConfigDTO config)
        {
            const int AllRolesMarker = -1500000000;
            var parts = new List<string>();

            // --- Department ---
            if (!string.IsNullOrWhiteSpace(config.TargetDept))
            {
                var deptLabel = !string.IsNullOrWhiteSpace(config.DepartmentName)
                    ? config.DepartmentName
                    : config.TargetDept.Trim();
                parts.Add($"Dept: {deptLabel}");
            }

            // --- Roles ---
            if (config.TargetRoleIds != null && config.TargetRoleIds.Count > 0)
            {
                bool hasAll = config.TargetRoleIds.Contains(AllRolesMarker);
                var validRoles = config.TargetRoleIds
                    .Where(r => r != 0 && r != AllRolesMarker)
                    .ToList();

                if (hasAll || validRoles.Count == 0)
                {
                    parts.Add("Roles: All");
                }
                else
                {
                    // Prefer resolved names from DAL OPENJSON join.
                    // Falls back to raw IDs only if names not resolved.
                    var roleLabel = !string.IsNullOrWhiteSpace(config.RoleNames)
                        ? config.RoleNames
                        : string.Join(", ", validRoles);
                    parts.Add($"Roles: {roleLabel}");
                }
            }

            return parts.Count > 0
                ? string.Join(" | ", parts)
                : "All Employees";
        }
    }
}