using Dapper;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.CommonConfig;  // ✅ ADD THIS
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.PickListGenerator;
using GB5Shared.QueryExecutor;
using GB5Shared.Resource.Response;
using GB5Shared.ResponseStandard;
using GB5Shared.Validation;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using PayRollDAL.CustomeCode.Address;
using PayRollDAL.CustomeCode.Extension;
using PayRollDAL.DTO.Address;
using PayRollDAL.DTO.Employee;
using PayRollDAL.DTO.EmployeeExperience;
using PayRollDAL.DTO.EmployeeQualification;
using PayRollDAL.DTO.EmployeeReference;
using PayRollDAL.DTO.EmployeeSalary;
using PayRollDAL.DTO.EmployeeSkill;
using PayRollDAL.DTO.EmployeeWorking;
using PayRollDAL.Query.Employee;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Globalization;
using System.Text;
using Unisoft.PayrollDAL.Employee;

namespace PayRollDAL.CustomeCode.Employee
{
    public class EmployeeDAL : IEmployeeDAL
    {
        private readonly IQueryExecutor _QueryExecutor;
        private readonly IValidation _Validation;
        private readonly ILogger<EmployeeDAL> logger;
        private readonly IApplicationConnection applicationConnection;
        private readonly IDbConnection _db;
        private readonly DatabaseTypeConfig _dbTypeConfig;
        private readonly IAddressDAL _addressDAL;

        public EmployeeDAL(
            IQueryExecutor QueryExecutor,
            IValidation Validation,
            ILogger<EmployeeDAL> Logger,
            IApplicationConnection IApplicationConnection,
            IDbConnection db,
            DatabaseTypeConfig dbTypeConfig,
            IAddressDAL addressDAL)
        {
            _QueryExecutor = QueryExecutor;
            _Validation = Validation;
            logger = Logger;
            applicationConnection = IApplicationConnection;
            _db = db;
            _dbTypeConfig = dbTypeConfig;
            _addressDAL = addressDAL;
        }

        // ============================================================
        // EmployeeDAL.GetEmployee  — fully corrected
        //
        // Fixes applied:
        //   1. PopulateFromDynamic: prefix: "Perm"  (was aliasMapper)
        //   2. SQL column alias emp.CONTACTID → EmployeeContactId
        //      (handled in GET_EMPLOYEE.sql; DAL code itself unchanged
        //       except for the PopulateFromDynamic line below)
        //   3. Guard against null result before accessing EmployeeAddon
        // ============================================================

        public async Task<string> GetEmployee(int employeeId, string languageId, LoginDTO loginDTO)
        {
            try
            {
                string sql = EmployeeQueryProvider.GetEmployeeQuery(_dbTypeConfig.Value);

                var parameters = new { employeeid = employeeId, languageid = languageId };

                var result = await _QueryExecutor.QueryMultiMapAsync<EmployeeDTO, EmployeeAddon, AddressDTO, dynamic, ContactDTO, EmployeeDTO>(
                    loginDTO,
                    sql,
                    (emp, _, presentAddr, permAddrRaw, contact) =>
                    {
                        // ── Present Address ──────────────────────────────────────
                        if (presentAddr != null && presentAddr.AddressId > 0)
                            emp.PresentAddressDTO = presentAddr;

                        // ── Permanent Address ────────────────────────────────────
                        if (permAddrRaw != null)
                        {
                            var permAddr = new AddressDTO();
                            permAddr.PopulateFromDynamic((object)permAddrRaw, prefix: "Perm");
                            if (permAddr.AddressId > 0)
                                emp.PermanentAddressDTO = permAddr;
                        }

                        // ── Contact ──────────────────────────────────────────────
                        if (contact != null && contact.ContactId > 0)
                            emp.ContactDTO = contact;

                        return emp;
                    },
                    parameters,
                    splitOn: "AddonEmployeeId,AddressId,PermAddressId,ContactId"
                );

                // ── Guard: result may be empty ───────────────────────────────────
                var finalEmployee = result?.FirstOrDefault();
                if (finalEmployee == null)
                    throw new Exception($"Employee ID {employeeId} not found.");

                if (finalEmployee.EmployeeAddon == null)
                    finalEmployee.EmployeeAddon = new List<EmployeeAddon>();

                // ── Location / OU Address Resolution ─────────────────────────────
                finalEmployee.LocationAddressDTO = await GetLocationOrOuAddressAsync(
                    finalEmployee.LocationId,
                    finalEmployee.WorkOUId,
                    loginDTO
                );

                return JsonConvert.SerializeObject(finalEmployee, new JsonSerializerSettings
                {
                    NullValueHandling = NullValueHandling.Include,
                    DateFormatHandling = DateFormatHandling.MicrosoftDateFormat,
                    Formatting = Formatting.None
                });
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"EmployeeDAL.GetEmployee(EmployeeId={employeeId})");
                throw new Exception(error, ex);
            }
        }

        #region Helper: Location / OU Address Fallback
        private async Task<AddressDTO> GetLocationOrOuAddressAsync(int locationId, int workOUId, LoginDTO loginDTO)
        {
            const int entityTypeLocationAddress = -2147482766;
            const int entityTypeOUAddress = -1899999977;

            // 1. Fetch Primary Location Address if LocationId != -1
            if (locationId != -1)
            {
                var locationAddresses = await _addressDAL.GetAddressDetailAsync(entityTypeLocationAddress, locationId, loginDTO);
                if (locationAddresses != null && locationAddresses.Count > 0)
                {
                    return locationAddresses[0];
                }
            }

            // 2. Fallback: Fetch Work OU Address if primary location address was not found
            var ouAddresses = await _addressDAL.GetAddressDetailAsync(entityTypeOUAddress, workOUId, loginDTO);
            return ouAddresses?.FirstOrDefault();
        }
        #endregion


        #region Save Employee
        public async Task<int> SaveEmployee(EmployeeDTO EmployeeDTO, LoginDTO LoginDTO)
                {
                    try
                    {
                        EmployeeDTO.EmployeeCreatedById = LoginDTO.UserId;
                        EmployeeDTO.EmployeeCreatedOn = DateTime.UtcNow;
                        EmployeeDTO.EmployeeModifiedById = LoginDTO.UserId;
                        EmployeeDTO.EmployeeModifiedOn = DateTime.UtcNow;

                        const string sql = EmployeeQB.SAVE_EMPLOYEE;
                        return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, EmployeeDTO);
                    }
                    catch (Exception ex)
                    {
                        string error = await _Validation.HandleException(ex, $"EmployeeDAL.SaveEmployee(EmployeeId={EmployeeDTO.EmployeeId})");
                        throw new Exception(error, ex);
                    }
                }

                #endregion

        #region Count TRM Employee
        public async Task<int> CountTRMEmployee(EmployeeDTO EmployeeDTO, string LanguageId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = EmployeeQB.COUNT_OF_TRMEMPLOYEE;
                var parameters = new
                {
                    employeeid = EmployeeDTO.EmployeeId,
                    languageid = LanguageId
                };
                return await _QueryExecutor.TotalCountSql(sql, LoginDTO, parameters, true);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while Counting Employee", ex);
            }
        }
        #endregion

        #region Update Employee
        public async Task<int> UpdateEmployee(EmployeeDTO EmployeeDTO, LoginDTO LoginDTO)
        {
            try
            {
                EmployeeDTO.EmployeeModifiedById = LoginDTO.UserId;
                EmployeeDTO.EmployeeModifiedOn = DateTime.UtcNow;

                const string sql = EmployeeQB.UPDATE_EMPLOYEE;
                return await _QueryExecutor.ExecuteAsync(LoginDTO, sql, EmployeeDTO);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"EmployeeDAL.UpdateEmployee(EmployeeId={EmployeeDTO.EmployeeId})");
                throw new Exception(error, ex);
            }
        }

        #endregion

        #region Future Version
        public async Task<EmployeeVersionDTO> GetFutureEmployeeVersionAsync(int employeeId, DateTime versionFrom, LoginDTO loginDTO)
        {
            try
            {
                string sql = EmployeeQB.GET_FUTURE_EMPLOYEE_VERSION;
                var parameters = new
                {
                    employeeid = employeeId,
                    versionfrom = versionFrom
                };

                var result = await _QueryExecutor.QueryAsync<EmployeeVersionDTO>(loginDTO, sql, parameters);
                return result?.FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw new Exception($"Error fetching future employee version for EmployeeId: {employeeId}", ex);
            }
        }
        #endregion

        #region Save or Update Employee ESI
        public async Task SaveOrUpdateEmployeeESIAsync(EmployeeDTO employee, LoginDTO loginDTO)
        {
            try
            {
                employee.EmployeeCreatedById = loginDTO.UserId;
                employee.EmployeeCreatedOn = DateTime.UtcNow;
                employee.EmployeeModifiedById = loginDTO.UserId;
                employee.EmployeeModifiedOn = DateTime.UtcNow;
                employee.EmployeeReasonForESILeaving ??= "NONE";
                employee.EmployeeESINumber ??= string.Empty;
                employee.EmployeeESIDispensary ??= string.Empty;

                if (employee.EmployeeIsESIApplicable == 1)
                {
                    await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.SAVE_OR_UPDATE_EMPLOYEE_ESI, employee);
                }
                else
                {
                    employee.EmployeeESIEndDate = DateTime.UtcNow;
                    await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.CLOSE_EMPLOYEE_ESI, employee);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region Save or Update Employee PF
        public async Task SaveOrUpdateEmployeePFAsync(EmployeeDTO employee, LoginDTO loginDTO)
        {
            try
            {
                employee.EmployeeCreatedById = loginDTO.UserId;
                employee.EmployeeCreatedOn = DateTime.UtcNow;
                employee.EmployeeModifiedById = loginDTO.UserId;
                employee.EmployeeModifiedOn = DateTime.UtcNow;
                employee.EmployeeReasonForPFLeaving ??= "NONE";
                employee.EmployeePFNumber ??= string.Empty;

                if (employee.EmployeeIsPFApplicable == 1)
                {
                    await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.SAVE_OR_UPDATE_EMPLOYEE_PF, employee);
                }
                else
                {
                    employee.EmployeePFEndDate = DateTime.UtcNow;
                    await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.CLOSE_EMPLOYEE_PF, employee);
                }
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion

        #region Close ESI and PF
        public async Task CloseEmployeeESIAsync(EmployeeDTO employee, LoginDTO loginDTO)
        {
            employee.EmployeeESIEndDate = DateTime.UtcNow;
            employee.EmployeeReasonForESILeaving ??= "NONE";
            employee.EmployeeModifiedById = loginDTO.UserId;
            employee.EmployeeModifiedOn = DateTime.UtcNow;

            await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.CLOSE_EMPLOYEE_ESI, employee);
        }

        public async Task CloseEmployeePFAsync(EmployeeDTO employee, LoginDTO loginDTO)
        {
            employee.EmployeePFEndDate = DateTime.UtcNow;
            employee.EmployeeReasonForPFLeaving ??= "NONE";
            employee.EmployeeModifiedById = loginDTO.UserId;
            employee.EmployeeModifiedOn = DateTime.UtcNow;

            await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.CLOSE_EMPLOYEE_PF, employee);
        }
        #endregion

        #region Resignation
        public async Task<int> UpdateAttendanceAfterResignationAsync(int employeeId, DateTime resignationDate, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new
                {
                    EmployeeId = employeeId,
                    ResignationDate = resignationDate
                };

                return await _QueryExecutor.ExecuteAsync(LoginDTO, EmployeeQB.UPDATE_ATTENDANCE_AFTER_RESIGNATION, parameters);
            }
            catch (Exception ex)
            {
                throw new Exception("Error while updating attendance after resignation.", ex);
            }
        }

        public async Task<bool> UpdateUserStatusAfterResignationAsync(int employeeId, DateTime resignationDate, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new
                {
                    EmployeeId = employeeId,
                    ResignationDate = resignationDate
                };

                int rows = await _QueryExecutor.ExecuteAsync(LoginDTO, EmployeeQB.UPDATE_USER_STATUS_AFTER_RESIGNATION, parameters);

                // Stop all scheduled jobs whose RunAs context was this user — prevents orphaned
                // jobs from firing with a deactivated user's identity after resignation.
                await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    EmployeeQB.DEACTIVATE_SCHEDULER_JOBS_FOR_USER,
                    new { EmployeeId = employeeId, ModifiedById = LoginDTO.UserId });

                return rows > 0;
            }
            catch (Exception ex)
            {
                throw new Exception("Error while updating user status after resignation.", ex);
            }
        }
        #endregion
        public async Task UpdateEmployeeShiftPatternAsync(EmployeeShiftPatternDTO dto, LoginDTO loginDTO)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.UPDATE_MEMPLOYEE_FOR_SHIFTPATTERN, dto);
        }

        public async Task UpdateEmployeeShiftPatternAsync(EmployeeShiftPatternDTO dto, LoginDTO loginDTO, System.Data.Common.DbTransaction transaction, CancellationToken ct)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.UPDATE_MEMPLOYEE_FOR_SHIFTPATTERN, dto, transaction, cancellationToken: ct).ConfigureAwait(false);
        }

        #region Addon
        public async Task DeleteEmployeeAddon(int employeeId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.DELETE_EMPLOYEE_ADDON,
                new { EmployeeId = employeeId }, cancellationToken: ct);
        }

        public async Task UpsertEmployeeAddon(EmployeeAddon addon, LoginDTO loginDTO, CancellationToken ct = default)
        {
            await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.UPSERT_EMPLOYEE_ADDON, addon);
        }
        #endregion

        #region Delete Employee
        public async Task<Result<string>> DeleteEmployee(int employeeId, LoginDTO LoginDTO)
        {
            try
            {
                string sql = EmployeeQB.DELETE_EMPLOYEE;
                var param = new { employeeid = employeeId };

                int rows = await _QueryExecutor.ExecuteAsync(LoginDTO, sql, param);

                if (rows > 0)
                    return Result<string>.Success("Employee deleted.");
                else
                    return Result<string>.Failure("No Employee record found.");
            }
            catch (Exception ex)
            {
                throw new Exception("Error while deleting Employee.", ex);
            }
        }
        #endregion

        #region Transfer Employee Advance
        public async Task<TransferAdvanceResultDTO> PostTransferEmployeeAdvance(
     List<PostTransferEmployeeAdvanceDTO> dtos,
     LoginDTO loginDTO)
        {
            var resultDto = new TransferAdvanceResultDTO();

            foreach (var dto in dtos)
            {
                try
                {
                    DateTime parsedDate = DateTime.Parse(dto.TransferDate!, CultureInfo.InvariantCulture);

                    var parameters = new
                    {
                        oldemployeeid = dto.OldEmployeeId,
                        newemployeeid = dto.NewEmployeeId,
                        transferdate = parsedDate
                    };

                    var procedures = new List<(string ProcedureName, object Param)>
            {
                ("TRANSFER_EMPLOYEE_ADVANCES", parameters)
            };

                    var resultList = await _QueryExecutor.ProcedureExecuteAsync(loginDTO, procedures);

                    string status = resultList.FirstOrDefault() ?? "NO RESPONSE";

                    // Include which employee this status belongs to
                    resultDto.Status.Add(
                        $"{dto.OldEmployeeName} {status}"
                    );
                }
                catch (Exception ex)
                {
                    resultDto.Status.Add(
                        $"{dto.OldEmployeeName} Error: {ex.Message}"
                    );
                }
            }

            return resultDto;
        }





        public async Task<string> GetTransferEmployeeAdvance(LoginDTO LoginDTO)
        {
            try
            {
                string sql = EmployeeQB.GETTRANSFEREMPLOYEEADVANCE;
                List<EmployeeAdvanceDTO> EmployeeAdvanceDTO = (await _QueryExecutor.QueryAsync<EmployeeAdvanceDTO>(LoginDTO, sql, null!)).ToList();
                string Result = JsonConvert.SerializeObject(EmployeeAdvanceDTO);
                return Result;
            }
            catch (Exception)
            {
                throw;
            }
        }
        #endregion


        public async Task<string> SaveEmployeeSecretKey(int EmployeeId, string SecretKey, LoginDTO LoginDTO)
        {
            try
            {

                string SQL = EmployeeQB.SAVE_EMPLOYEE_SECRET_KEY;
                string Encrypt = applicationConnection.Encrypt(SecretKey);
                var parameters = new
                {
                    employeeid = EmployeeId,
                    secretkey = Encrypt
                };
                await _QueryExecutor.ExecuteAsync(LoginDTO, SQL, parameters);
                return "SecretKey Saved successfully";
            }
            catch (Exception ex)
            {
                throw new Exception($"Error while updating secret key", ex);
            }
        }

        // GB4 parity (Employee.svc.cs GetDifferenceFromJoiningDate): splits the calendar gap between
        // an employee's DateOfJoining and a given separation date into years/months/days via the
        // GetAgeSplit TVF. SQL-Server-only in GB4 (LoginDTO.DatabaseType == 0) — other DB types
        // returned zeros there, preserved here rather than guessing new cross-DB behavior.
        public async Task<(int SeparationYears, int SeparationMonths, int SeparationDays)> GetDifferenceFromJoiningDate(
            int employeeId, DateTime separationDate, LoginDTO loginDTO)
        {
            try
            {
                var dateOfJoining = await _QueryExecutor.QuerySingleAsync<DateTime?>(
                    loginDTO, EmployeeQB.GET_EMPLOYEE_DATE_OF_JOINING, new { employeeid = employeeId });

                if (dateOfJoining == null)
                    throw new Exception($"Employee ID {employeeId} not found.");

                if (loginDTO.DatabaseType != 0)
                    return (0, 0, 0);

                var result = await _QueryExecutor.QuerySingleAsync<AgeSplitResult>(
                    loginDTO,
                    EmployeeQB.GET_AGE_SPLIT_FOR_EMPLOYEE,
                    new { employeedateofjoining = dateOfJoining.Value, separationdate = separationDate });

                return result == null
                    ? (0, 0, 0)
                    : (result.SeparationYears, result.SeparationMonths, result.SeparationDays);
            }
            catch (Exception ex)
            {
                string error = await _Validation.HandleException(ex, $"EmployeeDAL.GetDifferenceFromJoiningDate(EmployeeId={employeeId})");
                throw new Exception(error, ex);
            }
        }

        private sealed class AgeSplitResult
        {
            public int SeparationYears { get; set; }
            public int SeparationMonths { get; set; }
            public int SeparationDays { get; set; }
        }

        // Declarative FieldName -> real-column map for the Employee picklist. Any criteria whose
        // FieldName matches an entry here is applied generically — operator, JoinType (and/or) and
        // value all come straight from the posted AttributesCriteriaDTO via the shared
        // PickListExecutor.ApplyCriteria engine (GB5Shared.PickListGenerator — same mechanism
        // GCMDAL/OrganizationUnitDAL already use). Adding a new filterable column is a one-line
        // addition here, not a new switch-case — nothing else in this method needs to change.
        private static readonly string[,] EmployeeCriteriaColumnMap =
        {
            { "EmployeeId",              "emp.EMPLOYEEID" },
            { "Status",                  "emp.STATUS" },
            { "EmployerStatus",          "emp.EMPLOYEESTATUS" },
            { "EmployeeStatus",          "emp.EMPLOYEESTATUS" },
            { "CodeDefineId",            "emp.CODEDEFINEID" },
            { "CodeDefine",              "emp.CODEDEFINEID" },
            { "DepartmentId",            "emp.DEPARTMENTID" },
            { "ReportingToEmployeeId",   "emp.REPORTINGTOEMPLOYEEID" },
            { "ReportingIntoEmployeeId", "emp.REPORTINGTOEMPLOYEEID" },
            { "PayGroupId",              "emp.PAYGROUPID" },
            { "IsMarketing",             "emp.ISMARKETING" },
            { "IsSales",                 "emp.ISSALES" },
            { "BasicOn",                 "emp.BASICON" },
        };

        // Fields that control *how* the query is shaped rather than mapping onto a single real
        // column — a multi-column search (Code/Name/Aadhaar), the OU-scope switch, row-security
        // toggles, the query-template switch, and sort order. GB4 kept these outside its own
        // generic ApplyCriteria for the same reason; they stay as explicit, typed reads here.
        private sealed class EmployeePicklistControlFlags
        {
            public string? Code;
            public string? Name;
            public string? DepartmentName;
            public string? AadhaarNumber;
            // EMPLOYEENEWSTATUS is a computed display label (CASE emp.STATUS ... END in the SELECT),
            // not a real column — matched via the same CASE expression in the WHERE clause.
            public string? EmployeeNewStatus;
            public byte? OuFilterType;
            public byte? AadhaarBased;
            public byte? UserBasedFilter;
            public byte? FilterForEmpMaster;
            public int? OrderByTag;
            // Call-assignment eligibility: restricts to employees linked (via TCALL.ALLOTEDTOEMPLOYEEID)
            // to a prior call of this nature/type. Confirmed with the caller — no such Employee-side
            // column/table exists; this is the intended new TCALL-based filter, not a bug fix.
            public int? CallNatureId;
            public int? CallTypeId;
        }

        private static void ParseControlFlags(CriteriaDTO? criteriaDTO, EmployeePicklistControlFlags target)
        {
            if (criteriaDTO?.SectionCriteriaList == null) return;

            foreach (var sec in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in sec.AttributesCriteriaList)
                {
                    if (attr.FieldValue is null) continue;

                    switch (attr.FieldName?.ToLowerInvariant())
                    {
                        case "code":
                            target.Code = GetValue<string>(attr.FieldValue);
                            break;
                        case "name":
                            target.Name = GetValue<string>(attr.FieldValue);
                            break;
                        case "departmentname":
                            target.DepartmentName = GetValue<string>(attr.FieldValue);
                            break;
                        case "aadhaarnumber":
                            target.AadhaarNumber = GetValue<string>(attr.FieldValue);
                            break;
                        case "employeenewstatus":
                            target.EmployeeNewStatus = GetValue<string>(attr.FieldValue);
                            break;
                        // GB4 named this "oufiltertype"; some GB5 callers post "oufilter" — accept both.
                        case "oufiltertype":
                        case "oufilter":
                            target.OuFilterType = GetValue<byte>(attr.FieldValue);
                            break;
                        case "callnatureid":
                            target.CallNatureId = GetValue<int>(attr.FieldValue);
                            break;
                        case "calltypeid":
                            target.CallTypeId = GetValue<int>(attr.FieldValue);
                            break;
                        case "aadhaarbased":
                            target.AadhaarBased = GetValue<byte>(attr.FieldValue);
                            break;
                        case "userbasedfilter":
                            target.UserBasedFilter = GetValue<byte>(attr.FieldValue);
                            break;
                        case "filterforempmaster":
                            target.FilterForEmpMaster = GetValue<byte>(attr.FieldValue);
                            break;
                        case "orderbytag":
                            target.OrderByTag = GetValue<int>(attr.FieldValue);
                            break;
                    }
                }
            }
        }

        // Builds the shared base-query + WHERE-clause additions for both the list and the count
        // query, so the two always agree on which rows match. Column-mapped criteria are applied
        // dynamically via PickListExecutor.ApplyCriteria (skipped automatically when a field is
        // absent from the posted CriteriaDTO — that engine already treats "not posted" as "no
        // filter"); only the handful of control flags above are read explicitly.
        private static async Task<(string BaseSql, string OrderBySql, Dictionary<string, object> Param)> BuildEmployeePicklistQueryAsync(
            CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            IPickListExecutor pickListExecutor = new PickListExecutor();

            string sql = "";
            sql = await pickListExecutor.ApplyCriteria(sql, EmployeeCriteriaColumnMap, criteriaDTO, loginDTO);
            // Row-level security: the logged-in user's own restricted criteria are ANDed on top of
            // whatever the caller asked for — a caller cannot widen past this.
            sql = await pickListExecutor.ApplyCriteria(sql, EmployeeCriteriaColumnMap, loginDTO.UserCriteriaDTO, loginDTO);

            var flags = new EmployeePicklistControlFlags();
            ParseControlFlags(criteriaDTO, flags);
            ParseControlFlags(loginDTO.UserCriteriaDTO, flags);

            var param = new Dictionary<string, object> { ["tenantId"] = loginDTO.ClientId };
            var filterSql = new StringBuilder(sql);

            // MDEPARTMENT is only joined (as `dep`) in the richer default template
            // (GET_EMPLOYEEPICKLIST_SELECT2) — the lean FilterForEmpMaster=0 template has no such
            // alias, so a DepartmentName search can only be added when that richer template is in play.
            bool hasCode = !string.IsNullOrWhiteSpace(flags.Code);
            bool hasName = !string.IsNullOrWhiteSpace(flags.Name);
            bool wantsAadhaar = flags.AadhaarBased == 0 && !string.IsNullOrWhiteSpace(flags.AadhaarNumber);
            bool canSearchDepartment = flags.FilterForEmpMaster != 0 && !string.IsNullOrWhiteSpace(flags.DepartmentName);
            bool hasEmployeeNewStatus = !string.IsNullOrWhiteSpace(flags.EmployeeNewStatus);

            if (hasCode || hasName || wantsAadhaar || canSearchDepartment || hasEmployeeNewStatus)
            {
                // Only include a column's condition when that specific field actually has a value —
                // LIKE '%' + '' + '%' matches every row, so blindly OR-ing in a blank field would
                // silently defeat whichever other field the caller actually meant to filter by.
                var quickSearchTerms = new List<string>();

                if (hasCode)
                {
                    quickSearchTerms.Add("emp.EmployeeCode LIKE '%' + @code + '%'");
                    param["code"] = flags.Code!;
                }
                if (hasName)
                {
                    quickSearchTerms.Add("emp.EmployeeName LIKE '%' + @name + '%'");
                    param["name"] = flags.Name!;
                }
                if (wantsAadhaar)
                {
                    quickSearchTerms.Add("emp.ADHARNUMBER LIKE '%' + @aadhaarNumber + '%'");
                    param["aadhaarNumber"] = flags.AadhaarNumber!;
                }
                if (canSearchDepartment)
                {
                    quickSearchTerms.Add("dep.DEPARTMENTNAME LIKE '%' + @departmentName + '%'");
                    param["departmentName"] = flags.DepartmentName!;
                }
                if (hasEmployeeNewStatus)
                {
                    // EMPLOYEENEWSTATUS has no real column — match the same CASE expression the
                    // SELECT list uses to produce the display label (Pending/Active/Deleted/...).
                    quickSearchTerms.Add(@"
                        CASE emp.Status
                            WHEN 0 THEN 'Pending' WHEN 1 THEN 'Active' WHEN 2 THEN 'Deleted'
                            WHEN 3 THEN 'Amended' WHEN 4 THEN 'InActive' WHEN 5 THEN 'Archived'
                            ELSE ''
                        END LIKE '%' + @employeeNewStatus + '%'");
                    param["employeeNewStatus"] = flags.EmployeeNewStatus!;
                }

                filterSql.Append(" AND (" + string.Join(" OR ", quickSearchTerms) + ")");
            }

            // Call-assignment eligibility: restrict to employees linked to a prior TCALL row for this
            // CallNatureId/CallTypeId (matched via ALLOTEDTOEMPLOYEEID — "who this call is assigned
            // to" — any status/date, per explicit product decision; no such relationship existed
            // anywhere in GB4, this is new intended behavior, not a restored gap).
            if (flags.CallNatureId.HasValue || flags.CallTypeId.HasValue)
            {
                filterSql.Append(@"
                    AND EXISTS (
                        SELECT 1 FROM TCALL tc
                        WHERE tc.ALLOTEDTOEMPLOYEEID = emp.EMPLOYEEID");
                if (flags.CallNatureId.HasValue)
                {
                    filterSql.Append(" AND tc.CALLNATUREID = @callNatureId");
                    param["callNatureId"] = flags.CallNatureId.Value;
                }
                if (flags.CallTypeId.HasValue)
                {
                    filterSql.Append(" AND tc.CALLTYPEID = @callTypeId");
                    param["callTypeId"] = flags.CallTypeId.Value;
                }
                filterSql.Append(")");
            }

            // OuFilterType: 0=WorkOU, 1=CurrentOU, 2=Both, 3=NONE (no OU restriction at all) —
            // matches GB4's original definition. (A prior version of this code briefly redefined
            // 3 to mean "Both", but that was reverted — 3 means no restriction.)
            if (flags.OuFilterType is 0 or 1 or 2)
            {
                filterSql.Append(flags.OuFilterType switch
                {
                    0 => " AND emp.WORKOUID = @workOuId",
                    1 => " AND emp.CURRENTOUID = @workOuId",
                    _ => " AND emp.WORKOUID = @workOuId AND emp.CURRENTOUID = @workOuId"
                });
                param["workOuId"] = loginDTO.WorkOUId;
            }

            // Row-level OU restriction — mirrors GB4's INNER JOIN MUSERACCESSRIGHTS uar (userbasedfilter=0)
            if (flags.UserBasedFilter == 0)
            {
                filterSql.Append(" AND EXISTS (SELECT 1 FROM MUSERACCESSRIGHTS uar WHERE uar.OUID = emp.WORKOUID AND uar.USERID = @userId)");
                param["userId"] = loginDTO.UserId;
            }

            string orderBy = flags.OrderByTag switch
            {
                0 => " ORDER BY emp.EmployeeCode",
                1 => " ORDER BY emp.EmployeeName",
                2 => " ORDER BY emp.SortOrder",
                _ => " ORDER BY emp.EmployeeId"
            };

            // FilterForEmpMaster=0 => GB4's lean, MUSERACCESSRIGHTS-restricted picklist template;
            // anything else (including absent) => the richer default picklist with lookup names.
            string baseSql;
            if (flags.FilterForEmpMaster == 0)
            {
                // The stored template ends in ';' and declares its own @UserId parameter — strip
                // the terminator so our filters/order-by can append, and always bind @UserId since
                // the template's WHERE treats a null value as "no restriction".
                baseSql = EmployeeQB.GET_EMPLOYEEPICKLIST_FOR_SELECT2.TrimEnd().TrimEnd(';') + filterSql;
                param["UserId"] = flags.UserBasedFilter == 0 ? loginDTO.UserId : (object)DBNull.Value;
            }
            else
            {
                baseSql = EmployeeQB.GET_EMPLOYEEPICKLIST_SELECT2 + filterSql;
            }

            return (baseSql, orderBy, param);
        }

        // Single entry point for both GB4 Employee.svc.cs picklist services (GetSelectListEmployee
        // and GetEmployeePicklistSelect2) — one SL/BLL/DAL call returns page data + Total together.
        // Any criteria field absent from the posted CriteriaDTO is skipped, so the picklist shows
        // everything unless the caller explicitly narrows it.
        public async Task<(string Json, int Total)> GetSelectListEmployee(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            var (baseSql, orderBySql, param) = await BuildEmployeePicklistQueryAsync(criteriaDTO, loginDTO);

            // GB4 defaulted FirstNumber=0/MaxResult=0 to a 50-row page rather than "return everything".
            if (firstNumber == 0 && maxResult == 0)
            {
                maxResult = 50;
            }

            string listSql = baseSql + orderBySql;
            var listParam = new Dictionary<string, object>(param);
            if (firstNumber >= 0 && maxResult > 0)
            {
                // FirstNumber is 1-indexed from the caller's perspective (FirstNumber=1 means
                // "start at the 1st record"), but SQL Server's OFFSET is 0-indexed (OFFSET 0 = no
                // skip, start at record 1). Passing FirstNumber straight into OFFSET skipped one
                // extra record every time — FirstNumber=1 was skipping the actual first row.
                int offset = firstNumber <= 1 ? 0 : firstNumber - 1;
                listSql += " OFFSET @firstNumber ROWS FETCH NEXT @maxResult ROWS ONLY";
                listParam["firstNumber"] = offset;
                listParam["maxResult"] = maxResult;
            }

            var listTask = _QueryExecutor.QueryAsync<EmployeePicklistDTO>(loginDTO, listSql, listParam);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, $"SELECT COUNT(1) FROM ({baseSql}) t", param);
            await Task.WhenAll(listTask, countTask);

            return (JsonConvert.SerializeObject(listTask.Result), countTask.Result);
        }
        private static int GetInt(object value)
        {
            if (value is System.Text.Json.JsonElement json)
            {
                return json.ValueKind switch
                {
                    System.Text.Json.JsonValueKind.Number => json.GetInt32(),
                    System.Text.Json.JsonValueKind.String => int.Parse(json.GetString() ?? "0"),
                    _ => 0
                };
            }

            return Convert.ToInt32(value);
        }

        private static string GetString(object value)
        {
            if (value is System.Text.Json.JsonElement json)
                return json.GetString() ?? "";

            return value?.ToString() ?? "";
        }
        private static T GetValue<T>(object value)
        {
            if (value is System.Text.Json.JsonElement json)
            {
                if (typeof(T) == typeof(int))
                    return (T)(object)(json.ValueKind == System.Text.Json.JsonValueKind.Number
                        ? json.GetInt32()
                        : int.Parse(json.GetString() ?? "0"));

                if (typeof(T) == typeof(byte))
                    return (T)(object)(byte)(json.ValueKind == System.Text.Json.JsonValueKind.Number
                        ? json.GetByte()
                        : byte.Parse(json.GetString() ?? "0"));

                if (typeof(T) == typeof(string))
                    return (T)(object)(json.GetString() ?? "");
            }

            return (T)Convert.ChangeType(value, typeof(T));
        }

        public async Task<int> GetEmployeeIdByCode(string employeeCode, LoginDTO loginDTO)
        {
            var result = await _QueryExecutor.QueryAsync<int>(
                loginDTO,
                EmployeeQB.GET_EMPLOYEEID_BY_CODE,
                new { EmployeeCode = employeeCode, TenantId = loginDTO.ClientId });

            return result.DefaultIfEmpty(-1).First();
        }

        // HRMS dashboard / hierarchy criteria are posted the same generic attribute-bag way as
        // everywhere else in this DAL (see ParseControlFlags above) — GB4's CriteriaDTO carries
        // named attributes, not strongly-typed properties.
        private sealed class HrmsDashboardCriteria
        {
            public int? OuId;
            // Multi-OU support for GetEmployeeHierarchy's own re-rooting filter (see GET_EMPLOYEE_HIERARCHY) --
            // OuId above stays single-value for the other HRMS dashboard widget queries, which never asked
            // for a list. Populated from the same "OUId" attribute; parsed once, used by whichever query needs it.
            public List<int> OuIds = new();
            public int? EmployeeId;
            public DateTime? PeriodFromDate;
            public DateTime? PeriodToDate;
            public int NumberConversion;
            public int? DepartmentId;
        }

        private static HrmsDashboardCriteria ParseHrmsDashboardCriteria(CriteriaDTO? criteriaDTO)
        {
            var result = new HrmsDashboardCriteria();
            if (criteriaDTO?.SectionCriteriaList == null) return result;

            foreach (var sec in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in sec.AttributesCriteriaList)
                {
                    if (attr.FieldValue is null) continue;

                    switch (attr.FieldName?.ToLowerInvariant())
                    {
                        case "ouid":
                            // FieldValue can be a single id ("-1500000000") or a comma-separated list
                            // ("-1500000000,-1499999980") for a multi-OU selection -- GetValue<int> crashed
                            // on the latter with a FormatException. Real OU ids are large NEGATIVE numbers
                            // in this schema, so "!= 0" (not "> 0") is the correct "was this set" check.
                            result.OuIds = GetString(attr.FieldValue)
                                .Split(',', StringSplitOptions.RemoveEmptyEntries)
                                .Select(s => int.TryParse(s.Trim(), out var v) ? v : 0)
                                .Where(v => v != 0)
                                .ToList();
                            result.OuId = result.OuIds.Count == 1 ? result.OuIds[0] : null;
                            break;
                        case "employeeid":
                        case "reportingtoemployeeid":
                            var employeeId = GetValue<int>(attr.FieldValue);
                            result.EmployeeId = employeeId > 0 ? employeeId : null;
                            break;
                        case "periodfromdate":
                            result.PeriodFromDate = DateTime.TryParse(GetValue<string>(attr.FieldValue), out var pf) ? pf : null;
                            break;
                        case "periodtodate":
                            result.PeriodToDate = DateTime.TryParse(GetValue<string>(attr.FieldValue), out var pt) ? pt : null;
                            break;
                        case "numberconversion":
                            result.NumberConversion = GetValue<int>(attr.FieldValue);
                            break;
                        case "departmentid":
                            var deptId = GetValue<int>(attr.FieldValue);
                            result.DepartmentId = deptId > 0 ? deptId : null;
                            break;
                    }
                }
            }
            return result;
        }

        public async Task<List<EmployeeHierarchyDTO>> GetEmployeeHierarchy(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var criteria = ParseHrmsDashboardCriteria(criteriaDTO);
            // 0 is never a real WORKOUID (see this codebase's own large-negative-id convention) --
            // used here purely so Dapper's "IN @OuIds" list expansion always has a non-empty list to
            // bind, without needing a separate "was any OU given" flag parameter.
            var ouIds = criteria.OuIds.Count > 0 ? criteria.OuIds : new List<int> { 0 };
            var result = await _QueryExecutor.QueryAsync<EmployeeHierarchyDTO>(
                loginDTO,
                EmployeeQB.GET_EMPLOYEE_HIERARCHY,
                new { OuIds = ouIds, TenantId = loginDTO.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
            return result.ToList();
        }

        public async Task<EmployeesDashboardDTO> GetHRMSDashboard(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var criteria = ParseHrmsDashboardCriteria(criteriaDTO);
            int? ouId = criteria.OuId;
            DateTime? periodFrom = criteria.PeriodFromDate;
            DateTime? periodTo = criteria.PeriodToDate;
            int numberConversion = criteria.NumberConversion;
            int? departmentId = criteria.DepartmentId;

            var commonParam = new { OuId = ouId, TenantId = loginDTO.ClientId };
            var periodParam = new { OuId = ouId, TenantId = loginDTO.ClientId, PeriodFromDate = periodFrom, PeriodToDate = periodTo };

            var breakUpTask = _QueryExecutor.QueryAsync<BreakUpEmployeesDTO>(loginDTO, EmployeeQB.GET_HRMS_BREAKUP_SEX_AGE, commonParam, cancellationToken: ct);
            var deptTask = _QueryExecutor.QueryAsync<DepartmentWiseBreakUpDTO>(loginDTO, EmployeeQB.GET_HRMS_DEPARTMENT_WISE_BREAKUP, commonParam, cancellationToken: ct);
            var designationTask = _QueryExecutor.QueryAsync<DesignationWiseBreakUpDTO>(loginDTO, EmployeeQB.GET_HRMS_DESIGNATION_WISE_BREAKUP,
                new { OuId = ouId, TenantId = loginDTO.ClientId, DepartmentId = departmentId }, cancellationToken: ct);
            var yearTask = _QueryExecutor.QueryAsync<BreakUpEmployeesYearDTO>(loginDTO, EmployeeQB.GET_HRMS_HEADCOUNT_BY_YEAR, commonParam, cancellationToken: ct);
            var headCountTask = _QueryExecutor.QueryAsync<BreakUpEmployeesHeadCountDTO>(loginDTO, EmployeeQB.GET_HRMS_HEADCOUNT, commonParam, cancellationToken: ct);
            var attendanceTask = _QueryExecutor.QueryAsync<BreakUpEmployeesAttendanceSummaryDTO>(loginDTO, EmployeeQB.GET_HRMS_ATTENDANCE_SUMMARY, periodParam, cancellationToken: ct);
            var absentismTask = _QueryExecutor.QueryAsync<BreakUpEmployeesAbsentismDTO>(loginDTO, EmployeeQB.GET_HRMS_ABSENTISM_SUMMARY, periodParam, cancellationToken: ct);
            var payCostTask = _QueryExecutor.QueryAsync<EmployeePayCostDTO>(loginDTO, EmployeeQB.GET_HRMS_PAY_COST_BY_MONTH,
                new { OuId = ouId, TenantId = loginDTO.ClientId, PeriodFromDate = periodFrom, PeriodToDate = periodTo, NumberConversion = numberConversion }, cancellationToken: ct);
            var yearlySalaryTask = _QueryExecutor.QueryAsync<EmployeeYearlySalaryCostDTO>(loginDTO, EmployeeQB.GET_HRMS_YEARLY_SALARY_COST,
                new { OuId = ouId, TenantId = loginDTO.ClientId, PeriodFromDate = periodFrom, PeriodToDate = periodTo, NumberConversion = numberConversion }, cancellationToken: ct);
            var hierarchyTask = GetEmployeeHierarchy(criteriaDTO, loginDTO, ct);

            // Attendance shift widgets need a real date range — default to the current month when the caller didn't supply one.
            var shiftFrom = periodFrom ?? new DateTime(DateTime.UtcNow.Year, DateTime.UtcNow.Month, 1);
            var shiftTo = periodTo ?? DateTime.UtcNow.Date;
            var shiftParam = new { OuId = ouId, TenantId = loginDTO.ClientId, PeriodFromDate = shiftFrom, PeriodToDate = shiftTo };
            var shiftSummaryTask = _QueryExecutor.QueryAsync<AttendanceShiftSummaryDTO>(loginDTO, EmployeeQB.GET_HRMS_ATTENDANCE_SHIFT_SUMMARY, shiftParam, cancellationToken: ct);
            var shiftChangeTask = _QueryExecutor.QueryAsync<DailyAttedanceShiftChangedEmployeeDetailDTO>(loginDTO, EmployeeQB.GET_HRMS_DAILY_SHIFT_CHANGE, shiftParam, cancellationToken: ct);

            await Task.WhenAll(breakUpTask, deptTask, designationTask, yearTask, headCountTask, attendanceTask,
                absentismTask, payCostTask, yearlySalaryTask, hierarchyTask, shiftSummaryTask, shiftChangeTask).ConfigureAwait(false);

            var dashboard = new EmployeesDashboardDTO();
            if (breakUpTask.Result.Any()) dashboard.BreakUpEmployeesDTOs = breakUpTask.Result.ToList();
            if (deptTask.Result.Any()) dashboard.DepartmentWiseBreakUpDTOs = deptTask.Result.ToList();
            if (designationTask.Result.Any()) dashboard.DesignationWiseBreakUpDTOs = designationTask.Result.ToList();
            if (yearTask.Result.Any()) dashboard.BreakUpEmployeesYearDTOs = yearTask.Result.ToList();
            if (headCountTask.Result.Any()) dashboard.BreakUpEmployeesHeadCountDTOs = headCountTask.Result.ToList();
            if (attendanceTask.Result.Any()) dashboard.BreakUpEmployeesAttendanceSummaryDTOs = attendanceTask.Result.ToList();
            if (absentismTask.Result.Any()) dashboard.BreakUpEmployeesAbsentismDTOs = absentismTask.Result.ToList();
            if (payCostTask.Result.Any()) dashboard.EmployeePayCostDTOs = payCostTask.Result.ToList();
            if (yearlySalaryTask.Result.Any()) dashboard.EmployeeYearlySalaryCostDTOs = yearlySalaryTask.Result.ToList();
            if (hierarchyTask.Result.Any()) dashboard.EmployeeHierarchyDTOs = hierarchyTask.Result.ToList();
            if (shiftSummaryTask.Result.Any()) dashboard.AttendanceShiftSummaryDTOs = shiftSummaryTask.Result.ToList();
            if (shiftChangeTask.Result.Any()) dashboard.DailyAttedanceShiftChangedEmployeeDetailDTOs = shiftChangeTask.Result.ToList();
            // WorkflowPendingAndEntityDetailDTOs intentionally left empty — deferred, no GB5 Activiti equivalent yet.
            return dashboard;
        }

        public async Task<(List<EmployeeProfileReportDTO> Items, int Total)> GetEmployeeProfileReport(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var criteria = ParseHrmsDashboardCriteria(criteriaDTO);

            string baseSql = EmployeeQB.GET_EMPLOYEE_PROFILE_REPORT;
            var baseParam = new Dictionary<string, object?>
            {
                ["TenantId"] = loginDTO.ClientId,
                ["EmployeeId"] = criteria.EmployeeId,
                ["EmployeeStatus"] = (int?)null // no dedicated status flag parsed yet — reserved for future criteria wiring
            };

            string listSql = baseSql + " ORDER BY a.EMPLOYEEID OFFSET @Offset ROWS FETCH NEXT @MaxResult ROWS ONLY";
            var listParam = new Dictionary<string, object?>(baseParam);
            int offset = firstNumber <= 1 ? 0 : firstNumber - 1;
            listParam["Offset"] = offset;
            listParam["MaxResult"] = maxResult > 0 ? maxResult : 50;

            var masterTask = _QueryExecutor.QueryAsync<EmployeeProfileReportDTO>(loginDTO, listSql, listParam, cancellationToken: ct);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, $"SELECT COUNT(1) FROM ({baseSql}) t", baseParam, cancellationToken: ct);
            await Task.WhenAll(masterTask, countTask).ConfigureAwait(false);

            var items = masterTask.Result.ToList();
            if (items.Count == 0) return (items, countTask.Result);

            var employeeIds = items.Select(i => i.EmployeeId).ToArray();
            var subParam = new { EmployeeIds = employeeIds };

            var salaryTask = _QueryExecutor.QueryAsync<EmployeeSalaryDTO>(loginDTO, EmployeeQB.GET_EMPLOYEE_PROFILE_SALARY, subParam, cancellationToken: ct);
            var skillTask = _QueryExecutor.QueryAsync<EmployeeSkillDTO>(loginDTO, EmployeeQB.GET_EMPLOYEE_PROFILE_SKILL, subParam, cancellationToken: ct);
            var workingTask = _QueryExecutor.QueryAsync<EmployeeWorkingDTO>(loginDTO, EmployeeQB.GET_EMPLOYEE_PROFILE_WORKING, subParam, cancellationToken: ct);
            var experienceTask = _QueryExecutor.QueryAsync<EmployeeExperienceDTO>(loginDTO, EmployeeQB.GET_EMPLOYEE_PROFILE_EXPERIENCE, subParam, cancellationToken: ct);
            var qualificationTask = _QueryExecutor.QueryAsync<EmployeeQualificationDTO>(loginDTO, EmployeeQB.GET_EMPLOYEE_PROFILE_QUALIFICATION, subParam, cancellationToken: ct);
            var referenceTask = _QueryExecutor.QueryAsync<EmployeeReferenceDTO>(loginDTO, EmployeeQB.GET_EMPLOYEE_PROFILE_REFERENCE, subParam, cancellationToken: ct);
            await Task.WhenAll(salaryTask, skillTask, workingTask, experienceTask, qualificationTask, referenceTask).ConfigureAwait(false);

            var salaryByEmployee = salaryTask.Result.ToLookup(x => x.EmployeeId);
            var skillByEmployee = skillTask.Result.ToLookup(x => x.EmployeeId);
            var workingByEmployee = workingTask.Result.ToLookup(x => x.EmployeeId);
            var experienceByEmployee = experienceTask.Result.ToLookup(x => x.EmployeeId);
            var qualificationByEmployee = qualificationTask.Result.ToLookup(x => x.EmployeeId);
            var referenceByEmployee = referenceTask.Result.ToLookup(x => x.EmployeeId);

            foreach (var item in items)
            {
                item.EmployeeSalary = salaryByEmployee[item.EmployeeId].ToList();
                item.EmployeeSkill = skillByEmployee[item.EmployeeId].ToList();
                item.EmployeeWorking = workingByEmployee[item.EmployeeId].ToList();
                item.EmployeeExperiences = experienceByEmployee[item.EmployeeId].ToList();
                item.EmployeeQualification = qualificationByEmployee[item.EmployeeId].ToList();
                item.EmployeeReference = referenceByEmployee[item.EmployeeId].ToList();
            }

            return (items, countTask.Result);
        }

        // ── EmployeePostingSelectionList ─────────────────────────────────────────────
        //
        // Migrated from GB4 EmployeeDAL.EmployeePostingSelectionList. Criteria fields
        // (case-insensitive, mirrors GB4): ouid, employeeid (required), frominventory,
        // fromdate/todate (epoch millis), searchquery, isdrilldown (default 1), payperiodid.
        //
        // When payperiodid is supplied and isdrilldown != 0, FromDate/ToDate are overridden
        // from the pay period's own date range (GB4 parity).
        //
        // frominventory == -1 selects the full posting-history query (with daily-attendance/
        // timesheet enrichment); any other value selects the simplified FromInventory variant.
        public async Task<(List<EmployeePostingSelectListDTO> Items, int Total)> EmployeePostingSelectionList(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            int ouId = -1, employeeId = -1, fromInventory = -1, payPeriodId = -1;
            DateTime periodFromDate = new DateTime(1899, 1, 1);
            DateTime periodToDate = new DateTime(9999, 12, 31);
            string searchQuery = "";
            int isDrillDown = 1; // 1 = opened from screen; 0 = opened from report (GB4 parity)
            int? partyId = null, postingPositionId = null, mmdId = null;
            string? partyCode = null, partyName = null;

            if (criteriaDTO?.SectionCriteriaList != null)
            {
                foreach (var section in criteriaDTO.SectionCriteriaList)
                {
                    foreach (var attr in section.AttributesCriteriaList ?? [])
                    {
                        var value = attr.FieldValue;
                        switch (attr.FieldName?.ToLower())
                        {
                            case "ouid": ouId = Convert.ToInt32(value); break;
                            case "employeeid": employeeId = Convert.ToInt32(value); break;
                            case "frominventory": fromInventory = Convert.ToInt32(value); break;
                            case "fromdate": periodFromDate = FromEpoch(Convert.ToInt64(value)); break;
                            case "todate": periodToDate = FromEpoch(Convert.ToInt64(value)); break;
                            case "searchquery": searchQuery = Convert.ToString(value) ?? ""; break;
                            case "isdrilldown": isDrillDown = Convert.ToInt32(value); break;
                            case "payperiodid": payPeriodId = Convert.ToInt32(value); break;
                            case "partyid": partyId = Convert.ToInt32(value); break;
                            case "partycode": partyCode = Convert.ToString(value); break;
                            case "partyname": partyName = Convert.ToString(value); break;
                            case "postingpositionid": postingPositionId = Convert.ToInt32(value); break;
                            case "mmdid": mmdId = Convert.ToInt32(value); break;
                        }
                    }
                }
            }

            if (employeeId == -1)
                throw new InvalidOperationException("Employee must be selected..");

            if (payPeriodId != -1 && isDrillDown != 0)
            {
                // Narrow direct lookup (not a call to IPayPeriodDAL) — PayPeriodDAL already
                // depends on IEmployeeDAL, so injecting it here would create a DI cycle.
                var payPeriodRange = await _QueryExecutor.QuerySingleAsync<PayPeriodDateRangeDTO?>(
                    loginDTO, EmployeeQB.GET_PAYPERIOD_DATE_RANGE, new { PayPeriodId = payPeriodId }, cancellationToken: ct)
                    .ConfigureAwait(false);
                if (payPeriodRange != null)
                {
                    periodFromDate = payPeriodRange.FromDate;
                    periodToDate = payPeriodRange.ToDate;
                }
            }

            var parameters = new Dictionary<string, object?>
            {
                ["OUId"] = ouId,
                ["EmployeeId"] = employeeId,
                ["PeriodFromDate"] = periodFromDate,
                ["PeriodToDate"] = periodToDate,
                ["SearchQuery"] = searchQuery,
                ["TenantId"] = loginDTO.ClientId,
                ["PartyId"] = partyId,
                ["PartyCode"] = partyCode,
                ["PartyName"] = partyName,
                ["PostingPositionId"] = postingPositionId,
                ["MMDId"] = mmdId,
                ["IsDrillDown"] = isDrillDown,
            };

            string baseSql = fromInventory == -1
                ? EmployeeQB.GET_EMPLOYEE_POSTING_SELECTION
                : EmployeeQB.GET_EMPLOYEE_POSTING_SELECTION_FORMM;

            int offset = firstNumber <= 1 ? 0 : firstNumber - 1;
            int pageSize = maxResult > 0 ? maxResult : 50;

            string listSql = baseSql + " OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            var listParam = new Dictionary<string, object?>(parameters)
            {
                ["Offset"] = offset,
                ["PageSize"] = pageSize,
            };

            var itemsTask = _QueryExecutor.QueryAsync<EmployeePostingSelectListDTO>(loginDTO, listSql, listParam, cancellationToken: ct);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, $"SELECT COUNT(1) FROM ({baseSql}) t", parameters, cancellationToken: ct);
            await Task.WhenAll(itemsTask, countTask).ConfigureAwait(false);

            return (itemsTask.Result.ToList(), countTask.Result);
        }

        private static DateTime FromEpoch(long epoch) =>
            epoch > 9_999_999_999L
                ? DateTimeOffset.FromUnixTimeMilliseconds(epoch).UtcDateTime
                : DateTimeOffset.FromUnixTimeSeconds(epoch).UtcDateTime;

        // ── Criteria parsing helper shared by the 7 newly-migrated operations below ──────────────
        private static Dictionary<string, object> FlattenCriteria(CriteriaDTO? criteriaDTO)
        {
            var map = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
            if (criteriaDTO?.SectionCriteriaList == null) return map;
            foreach (var sec in criteriaDTO.SectionCriteriaList)
            {
                foreach (var attr in sec.AttributesCriteriaList ?? [])
                {
                    if (attr.FieldValue is null || string.IsNullOrWhiteSpace(attr.FieldName)) continue;
                    map[attr.FieldName!] = attr.FieldValue;
                }
            }
            return map;
        }

        private static int? GetNullableInt(Dictionary<string, object> map, string key)
        {
            if (!map.TryGetValue(key, out var v) || v is null) return null;
            return GetInt(v);
        }

        private static string? GetNullableString(Dictionary<string, object> map, string key)
        {
            if (!map.TryGetValue(key, out var v) || v is null) return null;
            var s = GetString(v);
            return string.IsNullOrWhiteSpace(s) ? null : s;
        }

        private static DateTime? GetNullableDate(Dictionary<string, object> map, string key)
        {
            if (!map.TryGetValue(key, out var v) || v is null) return null;
            var s = GetString(v);
            return DateTime.TryParse(s, out var d) ? d : null;
        }

        // ── GetEmployeeVsShiftPattern ──────────────────────────────────────────────────────────────
        public async Task<(List<EmployeeVsShiftPatternDTO> Items, int Total)> GetEmployeeVsShiftPattern(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var map = FlattenCriteria(criteriaDTO);
            var param = new Dictionary<string, object?>
            {
                ["TenantId"] = loginDTO.ClientId,
                ["EmployeeId"] = GetNullableInt(map, "EMPLOYEEID"),
                ["DepartmentId"] = GetNullableInt(map, "DEPARTMENTID"),
                ["PayConfigurationId"] = GetNullableInt(map, "PAYCONFIGURATIONID"),
                ["EmployeeTypeId"] = GetNullableInt(map, "EMPLOYEETYPEID"),
                ["UnitId"] = GetNullableInt(map, "UNITID"),
                ["StaffTypeId"] = GetNullableInt(map, "STAFFTYPEID"),
                ["ShiftPatternId"] = GetNullableInt(map, "SHIFTPATTERNID"),
                ["EmployeeStatus"] = GetNullableInt(map, "EMPLOYEESTATUS"),
                ["WorkOUId"] = GetNullableInt(map, "WORKOUID"),
                ["CurrentOUId"] = GetNullableInt(map, "CurrentOUId"),
                ["PunchType"] = GetNullableInt(map, "PunchType"),
            };

            int offset = firstNumber <= 1 ? 0 : firstNumber - 1;
            int pageSize = maxResult > 0 ? maxResult : 50;
            string listSql = EmployeeQB.GET_EMPLOYEE_VS_SHIFT_PATTERN +
                " ORDER BY A.EMPLOYEECODE OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            var listParam = new Dictionary<string, object?>(param) { ["Offset"] = offset, ["PageSize"] = pageSize };

            var itemsTask = _QueryExecutor.QueryAsync<EmployeeVsShiftPatternDTO>(loginDTO, listSql, listParam, cancellationToken: ct);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, EmployeeQB.GET_EMPLOYEE_VS_SHIFT_PATTERN_COUNT, param, cancellationToken: ct);
            await Task.WhenAll(itemsTask, countTask).ConfigureAwait(false);

            return (itemsTask.Result.ToList(), countTask.Result);
        }

        // ── GetEmployeeVsShiftPatternNew ───────────────────────────────────────────────────────────
        public async Task<(List<EmployeeVsShiftPatternNewDTO> Items, int Total)> GetEmployeeVsShiftPatternNew(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var map = FlattenCriteria(criteriaDTO);
            var userMap = FlattenCriteria(loginDTO.UserCriteriaDTO);

            int? GetEither(string field) => GetNullableInt(map, field) ?? GetNullableInt(userMap, field);

            var param = new Dictionary<string, object?>
            {
                ["TenantId"] = loginDTO.ClientId,
                ["EmployeeId"] = GetEither("EMPLOYEEID"),
                ["DepartmentId"] = GetEither("DEPARTMENTID"),
                ["ShiftPatternId"] = GetEither("SHIFTPATTERNID"),
                ["WorkOUId"] = GetEither("WORKOUID"),
                // GB4 parity: EmployeeBLL.GetEmployeeVsShiftPatternLoad's CriteriaWithQueryField map.
                ["WorkTypeId"] = GetEither("WORKTYPEID"),
                ["StaffTypeId"] = GetEither("STAFFTYPEID"),
                ["EmployeeTypeId"] = GetEither("EMPLOYEETYPEID"),
                ["OurBranchId"] = GetEither("LocationId"),
                ["FunctionalReportingToId"] = GetEither("FUNCTIONALREPORTINGTOID"),
                ["SubUnitId"] = GetEither("SUBUNITID"),
                ["PayConfigurationId"] = GetEither("PAYCONFIGURATIONID"),
                ["AdminReportingToId"] = GetEither("ADMINREPORTINGTOID"),
            };

            int offset = firstNumber <= 1 ? 0 : firstNumber - 1;
            int pageSize = maxResult > 0 ? maxResult : 50;
            string listSql = EmployeeQB.GET_EMPLOYEE_VS_SHIFT_PATTERN_NEW_LOAD +
                " ORDER BY A.EMPLOYEEID OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            var listParam = new Dictionary<string, object?>(param) { ["Offset"] = offset, ["PageSize"] = pageSize };

            var itemsTask = _QueryExecutor.QueryAsync<EmployeeVsShiftPatternNewDTO>(loginDTO, listSql, listParam, cancellationToken: ct);
            var countTask = _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, EmployeeQB.GET_EMPLOYEE_VS_SHIFT_PATTERN_NEW_LOAD_COUNT, param, cancellationToken: ct);
            await Task.WhenAll(itemsTask, countTask).ConfigureAwait(false);

            return (itemsTask.Result.ToList(), countTask.Result);
        }

        // ── GetEmployeeVersionPicklist ─────────────────────────────────────────────────────────────
        public async Task<List<EmployeeVersionPickListDTO>> GetEmployeeVersionPicklist(
            int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            // Mandatory business rule preserved from GB4 EmployeeBLL.GetEmployeeVerisonPicklist.
            if (criteriaDTO == null)
                throw new ArgumentException("Please select employee..");

            var map = FlattenCriteria(criteriaDTO);
            int? employeeId = GetNullableInt(map, "employeeid") ?? GetNullableInt(map, "EMPLOYEEID");
            if (employeeId == null)
                throw new ArgumentException("Please select employee..");

            var param = new
            {
                EmployeeId = employeeId.Value,
                TenantId = loginDTO.ClientId,
                VersionFrom = GetNullableDate(map, "VersionFrom") ?? GetNullableDate(map, "versionfrom"),
            };

            var result = await _QueryExecutor.QueryAsync<EmployeeVersionPickListDTO>(
                loginDTO, EmployeeQB.GET_EMPLOYEEVERSION_PICKLIST, param, cancellationToken: ct).ConfigureAwait(false);
            return result.ToList();
        }

        // ── LegecyBasedEmployeeInformation ─────────────────────────────────────────────────────────
        public async Task<List<EmployeeBasedLegecyInformationDTO>> LegecyBasedEmployeeInformation(
            int employeeId, LoginDTO loginDTO, CancellationToken ct)
        {
            var result = await _QueryExecutor.QueryAsync<EmployeeBasedLegecyInformationDTO>(
                loginDTO,
                EmployeeQB.LEGECY_BASED_EMPLOYEE_INFORMATION,
                new { EmployeeId = employeeId, TenantId = loginDTO.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
            return result.ToList();
        }

        // ── GetEmployeeVersionInPayProcess ─────────────────────────────────────────────────────────
        public async Task<List<EmployeeVersionInPayProcessDTO>> GetEmployeeVersionInPayProcess(
            CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var map = FlattenCriteria(criteriaDTO);
            int employeeId = GetNullableInt(map, "employeeid") ?? -1;
            DateTime? versionFrom = GetNullableDate(map, "versionfrom");

            var result = await _QueryExecutor.QueryAsync<EmployeeVersionInPayProcessDTO>(
                loginDTO,
                EmployeeQB.EMPLOYEE_VERSION_CHECKING_WITH_PAYPROCESS,
                new { EmployeeId = employeeId, VersionFrom = versionFrom, TenantId = loginDTO.ClientId },
                cancellationToken: ct).ConfigureAwait(false);
            return result.ToList();
        }

        // ── GetEmployeeVersion — full parity ───────────────────────────────────────────────────────
        public async Task<EmployeeVersionDTO?> GetEmployeeVersion(int employeeVersionId, LoginDTO loginDTO, CancellationToken ct)
        {
            var dto = await _QueryExecutor.QuerySingleAsync<EmployeeVersionDTO>(
                loginDTO,
                EmployeeQB.GET_EMPLOYEE_VERSION,
                new { EmployeeVersionId = employeeVersionId, TenantId = loginDTO.ClientId },
                cancellationToken: ct).ConfigureAwait(false);

            if (dto == null) return null;

            // GB4 parity (a): age-split calc, SQL-Server-only, only when DateOfResignation is a real date.
            if (loginDTO.DatabaseType == 0 && dto.EmployeeDateOfResignation.Year != 9999)
            {
                var (years, months, days) = await GetDifferenceFromJoiningDate(dto.EmployeeId, dto.EmployeeDateOfResignation, loginDTO).ConfigureAwait(false);
                dto.SeparationYears = years;
                dto.SeparationMonths = months;
                dto.SeparationDays = days;
            }

            // GB4 parity (b): location-name resolution from comma-separated EmployeeLocationIds.
            if (!string.IsNullOrWhiteSpace(dto.EmployeeLocationIds) && dto.EmployeeLocationIds != "-1")
            {
                var ids = dto.EmployeeLocationIds.Split(',', StringSplitOptions.RemoveEmptyEntries)
                    .Select(s => int.TryParse(s.Trim(), out var v) ? v : (int?)null)
                    .Where(v => v.HasValue).Select(v => v!.Value).ToList();
                if (ids.Count > 0)
                {
                    var names = await _QueryExecutor.QueryAsync<string>(
                        loginDTO,
                        "SELECT LOCATIONNAME FROM MLOCATION WHERE LOCATIONID IN @Ids",
                        new { Ids = ids },
                        cancellationToken: ct).ConfigureAwait(false);
                    dto.EmployeeLocationNames = string.Join(",", names);
                }
            }

            return dto;
        }

        // ── GetDuplicateEmployee ────────────────────────────────────────────────────────────────────
        public async Task<List<DuplicateEmployeeDTO>> GetDuplicateEmployee(CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct)
        {
            var map = FlattenCriteria(criteriaDTO);
            var param = new
            {
                TenantId = loginDTO.ClientId,
                EmployeeName = GetNullableString(map, "employeename"),
                FatherName = GetNullableString(map, "fathername"),
                DateOfBirth = GetNullableDate(map, "dateofbirth"),
                AdharNumber = GetNullableString(map, "adharnumber"),
                PFNumber = GetNullableString(map, "pfnumber"),
                ESINumber = GetNullableString(map, "esinumber"),
                PFUANNumber = GetNullableString(map, "pfuannumber"),
            };

            var result = await _QueryExecutor.QueryAsync<DuplicateEmployeeDTO>(
                loginDTO, EmployeeQB.GET_DUPLICATE_EMPLOYEE, param, cancellationToken: ct).ConfigureAwait(false);
            return result.ToList();
        }

        // ── DeleteEmployeeVersion support methods (guards + rollback), called inside BLL transaction ─
        public async Task<int> GetPayProcessDoneAfterEmployeeVersion(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
            => await _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, EmployeeQB.GET_PAYPROCESS_DONE_AFTER_EMPLOYEE_VERSION,
                new { EmployeeVersionId = employeeVersionId }, tx, cancellationToken: ct).ConfigureAwait(false);

        public async Task<int> GetLatestVersionAfterGivenVersion(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
            => await _QueryExecutor.ExecuteScalarAsync<int>(loginDTO, EmployeeQB.GET_LATEST_VERSION_AFTER_GIVEN_VERSION,
                new { EmployeeVersionId = employeeVersionId }, tx, cancellationToken: ct).ConfigureAwait(false);

        public async Task<int> GetEmployeeIdFromVersion(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            var result = await _QueryExecutor.QuerySingleAsync<int?>(loginDTO, EmployeeQB.GET_EMPLOYEE_ID_FROM_VERSION,
                new { EmployeeVersionId = employeeVersionId }, tx, cancellationToken: ct).ConfigureAwait(false);
            return result ?? -1;
        }

        public async Task DeleteEmployeeAddonVersionByVersion(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
            => await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.DELETE_EMPLOYEEADDONVERSION_BY_VERSION,
                new { EmployeeVersionId = employeeVersionId }, tx, cancellationToken: ct).ConfigureAwait(false);

        public async Task DeleteEmployeeVersionById(int employeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
            => await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.DELETE_EMPLOYEEVERSION_BY_ID,
                new { EmployeeVersionId = employeeVersionId }, tx, cancellationToken: ct).ConfigureAwait(false);

        public async Task<int> GetEmployeeVersionLatestId(int employeeId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
        {
            var result = await _QueryExecutor.QuerySingleAsync<int?>(loginDTO, EmployeeQB.GET_EMPLOYEE_VERSION_LATEST_ID,
                new { EmployeeId = employeeId }, tx, cancellationToken: ct).ConfigureAwait(false);
            return result ?? -1;
        }

        public async Task RollbackEmployeeToLatestVersion(int latestEmployeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
            => await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.UPDATE_LATEST_VERSION_IN_MEMPLOYEE_AFTER_DELETE,
                new { LatestEmployeeVersionId = latestEmployeeVersionId }, tx, cancellationToken: ct).ConfigureAwait(false);

        public async Task RollbackEmployeeAddonToLatestVersion(int latestEmployeeVersionId, LoginDTO loginDTO, DbTransaction tx, CancellationToken ct)
            => await _QueryExecutor.ExecuteAsync(loginDTO, EmployeeQB.UPDATE_LATEST_VERSION_IN_MEMPLOYEE_ADDON_AFTER_DELETE,
                new { LatestEmployeeVersionId = latestEmployeeVersionId }, tx, cancellationToken: ct).ConfigureAwait(false);
    }

    /// <summary>Narrow projection used only by EmployeeDAL.EmployeePostingSelectionList.</summary>
    public sealed class PayPeriodDateRangeDTO
    {
        public DateTime FromDate { get; set; }
        public DateTime ToDate { get; set; }
    }
}