// ============================================================
// GoodBooks ERP — Skill Management Module
// Layer   : Data Access Layer (DAL)
// Class   : SkillMatrixDAL
// Updated : 2026-06-22 — Dynamic fix pass v3
//
// FIXES IN THIS VERSION:
//   [7]  GetEmployeesForMatrix — new overload accepts roleIds list.
//        When roleIds is non-empty: SQL filters employees using a
//        subquery against MJOBROLESKILL so the correct role join
//        is done in SQL, not in BLL in-memory comparison.
//        When roleIds is empty: all dept employees returned (no role
//        filter) — same behaviour as before for ALL-marker case.
//
//        BLL no longer does e.RoleId in-memory comparison.
//        The DAL owns the join between MEMPLOYEE.DESIGNATIONID
//        and whichever role table is authoritative.
// ============================================================

using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using SkillManagementDAL.DTO.SkillMatrix;
using SkillManagementDAL.Query.SkillMatrix;

namespace SkillManagementDAL.CustomCode.SkillMatrix
{
    public class SkillMatrixDAL : ISkillMatrixDAL
    {
        private readonly IQueryExecutor _qe;

        public SkillMatrixDAL(IQueryExecutor qe) => _qe = qe;

        // ─────────────────────────────────────────────────────────
        // GET SKILLS FOR MATRIX
        // ─────────────────────────────────────────────────────────
        public async Task<IEnumerable<SkillMatrixColumnDTO>> GetSkillsForMatrix(
            IEnumerable<int> skillIds,
            LoginDTO login,
            CancellationToken ct)
        {
            var ids = NormalizeIds(skillIds);
            if (ids.Count == 0)
                throw new InvalidOperationException(
                    "No valid SkillIds provided for Skill Matrix.");

            var csv = ToCsv(ids);
            Log("GET_SKILLS_FOR_MATRIX", login,
                ("SkillIds", csv), ("Count", ids.Count.ToString()));

            return await _qe.QueryAsync<SkillMatrixColumnDTO>(
                login,
                SkillMatrixQB.GET_SKILLS_FOR_MATRIX,
                new { TenantId = login.ClientId, SkillIds = csv },
                cancellationToken: ct);
        }

        // ─────────────────────────────────────────────────────────
        // GET PRODUCT SCOPE FOR SKILLS
        // ─────────────────────────────────────────────────────────
        public async Task<IEnumerable<SkillProductScopeDTO>> GetProductScopeForSkills(
            IEnumerable<int> skillIds,
            LoginDTO login,
            CancellationToken ct)
        {
            var ids = NormalizeIds(skillIds);
            if (ids.Count == 0)
                return Enumerable.Empty<SkillProductScopeDTO>();

            var csv = ToCsv(ids);
            Log("GET_PRODUCT_SCOPE_FOR_SKILLS", login, ("SkillIds", csv));

            return await _qe.QueryAsync<SkillProductScopeDTO>(
                login,
                SkillMatrixQB.GET_PRODUCT_SCOPE_FOR_SKILLS,
                new { TenantId = login.ClientId, SkillIds = csv },
                cancellationToken: ct);
        }

        // ─────────────────────────────────────────────────────────
        // GET EMPLOYEES FOR MATRIX
        //
        // FIX [1]: empty/whitespace TargetDept → null so SQL
        //          (@TargetDept IS NULL) branch fires correctly.
        //
        // FIX [7]: roleIds parameter added.
        //   Empty list  → no role filter (ALL employees for dept).
        //   Non-empty   → SQL filters via MJOBROLESKILL subquery.
        //                 The join MEMPLOYEE.DESIGNATIONID →
        //                 MJOBROLESKILL.ROLEID is done in SQL so
        //                 the correct ID space is used — BLL never
        //                 does in-memory RoleId comparison anymore.
        // ─────────────────────────────────────────────────────────
        // NEW — 3 arguments, role filter removed entirely
        public async Task<IEnumerable<SkillMatrixEmployeeDTO>> GetEmployeesForMatrix(
            string? targetDept,
            LoginDTO login,
            CancellationToken ct)
        {
            // Normalise empty/whitespace → null so SQL (@TargetDept IS NULL)
            // branch fires correctly returning all dept employees.
            var deptParam = string.IsNullOrWhiteSpace(targetDept)
                ? null
                : targetDept.Trim();

            Log("GET_EMPLOYEES_FOR_MATRIX", login,
                ("TargetDept", deptParam ?? "ALL"));

            return await _qe.QueryAsync<SkillMatrixEmployeeDTO>(
                login,
                SkillMatrixQB.GET_EMPLOYEES_FOR_MATRIX,
                new
                {
                    TenantId = login.ClientId,
                    TargetDept = deptParam
                },
                cancellationToken: ct);
        }

        // ─────────────────────────────────────────────────────────
        // GET PROFILE CELLS
        // ─────────────────────────────────────────────────────────
        public async Task<IEnumerable<SkillMatrixProfileCellDTO>> GetProfileCells(
            IEnumerable<int> skillIds,
            IEnumerable<int> employeeIds,
            LoginDTO login,
            CancellationToken ct)
        {
            var skillList = NormalizeIds(skillIds);
            var empList = NormalizeIds(employeeIds);

            if (skillList.Count == 0 || empList.Count == 0)
                return Enumerable.Empty<SkillMatrixProfileCellDTO>();

            var skillCsv = ToCsv(skillList);
            var empCsv = ToCsv(empList);

            Log("GET_PROFILE_CELLS", login,
                ("SkillIds", skillCsv),
                ("EmployeeIds", empCsv));

            return await _qe.QueryAsync<SkillMatrixProfileCellDTO>(
                login,
                SkillMatrixQB.GET_PROFILE_CELLS,
                new
                {
                    TenantId = login.ClientId,
                    SkillIds = skillCsv,
                    EmployeeIds = empCsv
                },
                cancellationToken: ct);
        }

        // ─────────────────────────────────────────────────────────
        // GET MIN LEVELS  (all-roles / no role scope)
        // ─────────────────────────────────────────────────────────
        public async Task<IEnumerable<SkillMinLevelDTO>> GetMinLevels(
            IEnumerable<int> skillIds,
            LoginDTO login,
            CancellationToken ct)
        {
            var ids = NormalizeIds(skillIds);
            if (ids.Count == 0)
                throw new InvalidOperationException(
                    "No SkillIds provided for GetMinLevels.");

            var csv = ToCsv(ids);
            Log("GET_MIN_LEVELS", login, ("SkillIds", csv));

            return await _qe.QueryAsync<SkillMinLevelDTO>(
                login,
                SkillMatrixQB.GET_MIN_LEVELS,
                new { TenantId = login.ClientId, SkillIds = csv },
                cancellationToken: ct);
        }

        // ─────────────────────────────────────────────────────────
        // GET MIN LEVELS BY ROLES  (role-scoped)
        // ─────────────────────────────────────────────────────────
        public async Task<IEnumerable<SkillMinLevelDTO>> GetMinLevelsByRoles(
            IEnumerable<int> skillIds,
            IEnumerable<int> roleIds,
            LoginDTO login,
            CancellationToken ct)
        {
            var skillList = NormalizeIds(skillIds);
            var roleList = NormalizeIds(roleIds);

            if (skillList.Count == 0)
                throw new InvalidOperationException(
                    "No SkillIds provided for GetMinLevelsByRoles.");
            if (roleList.Count == 0)
                throw new InvalidOperationException(
                    "No RoleIds provided for GetMinLevelsByRoles.");

            var skillCsv = ToCsv(skillList);
            var roleCsv = ToCsv(roleList);

            Log("GET_MIN_LEVELS_BY_ROLES", login,
                ("SkillIds", skillCsv),
                ("RoleIds", roleCsv));

            return await _qe.QueryAsync<SkillMinLevelDTO>(
                login,
                SkillMatrixQB.GET_MIN_LEVELS_BY_ROLES,
                new
                {
                    TenantId = login.ClientId,
                    SkillIds = skillCsv,
                    RoleIds = roleCsv
                },
                cancellationToken: ct);
        }

        // ─────────────────────────────────────────────────────────
        // PRIVATE HELPERS
        // ─────────────────────────────────────────────────────────

        /// Removes 0 and duplicates. Preserves negative GoodBooks IDs.
        private static List<int> NormalizeIds(IEnumerable<int>? ids)
            => (ids ?? Enumerable.Empty<int>())
                .Where(x => x != 0)
                .Distinct()
                .ToList();

        /// Comma-separated string for SQL STRING_SPLIT.
        private static string ToCsv(IEnumerable<int> ids)
            => string.Join(",", ids);

        private static void Log(string method, LoginDTO login, params (string Key, string Value)[] fields)
        {
            // Console.WriteLine removed — wire ILogger<SkillMatrixDAL> for structured observability
        }
    }
}