using Dapper;
using GB5Shared.DTO.Framework.AutoNumber;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.GB5Exception;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using Microsoft.AspNetCore.SignalR.Protocol;
using Microsoft.Extensions.Logging;
using PayRollDAL.CustomeCode.PayConfiguration;
using PayRollDAL.CustomeCode.PayPeriod;
using PayRollDAL.CustomeCode.PayProcess;
using PayRollDAL.DTO.PayConfiguration;
using PayRollDAL.DTO.PayProcess;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace PayRollBLL.PayProcess
{
    public class PayProcessBLL : IPayProcessBLL
    {
        private IPayProcessDAL payProcessDAL;
        private readonly ILogger<PayProcessBLL> _logger;
        private readonly IPayPeriodDAL _PayPeriodDAL;
        private readonly IPayConfigurationDAL _PayConfigurationDAL;
        private readonly AutoNumber _AutoNumber;
        private readonly IQueryExecutor _QueryExecutor;

        public PayProcessBLL(
            IPayProcessDAL IPayProcessDAL,
            ILogger<PayProcessBLL> ILogger,
            IPayPeriodDAL PayPeriodDAL,
            IPayConfigurationDAL PayConfigurationDAL,
            AutoNumber AutoNumber,
            IQueryExecutor QueryExecutor)
        {
            payProcessDAL = IPayProcessDAL;
            _logger = ILogger;
            _PayPeriodDAL = PayPeriodDAL;
            _PayConfigurationDAL = PayConfigurationDAL;
            _AutoNumber = AutoNumber;
            _QueryExecutor = QueryExecutor;
        }
        public async Task<List<PaySlipReportDTO>> GetPaySlipReport(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool Count = false, int Type = 0)
        {
            try
            {
                return await payProcessDAL.GetPaySlipReport(FirstNumber, MaxResult, CriteriaDTO, LoginDTO, Count, Type);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── Picklists ─────────────────────────────────────────────────────

        public async Task<string> GetAdditionDeductionPicklist(int FirstNumber, int MaxResult, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, bool IsCount = false)
        {
            return await payProcessDAL
                .GetAdditionDeductionPicklist(FirstNumber, MaxResult, CriteriaDTO, LoginDTO, IsCount)
                .ConfigureAwait(false);
        }

        public async Task<string> GetPayProcessAddonPicklist(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            return await payProcessDAL
                .GetPayProcessAddonPicklist(CriteriaDTO, LoginDTO)
                .ConfigureAwait(false);
        }

        public async Task<string> LeaveStatusReportAsync(int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO)
        {
            if (criteriaDTO == null || criteriaDTO.SectionCriteriaList?.Count == 0)
                throw new ArgumentException("CriteriaDTO cannot be null or empty");

            // GB4 parity: criteria are honored from both the request CriteriaDTO and the
            // login's security-scoping UserCriteriaDTO (PayProcessBLL.cs ApplyLeaveStatusNewReportCriteria,
            // applied twice in GB4 — once per source). Merge both before reading fields.
            var mergedCriteriaDTO = MergeCriteria(criteriaDTO, loginDTO?.UserCriteriaDTO);

            // 1️⃣ Extract criteria safely
            string periodFromStr = GetCriteriaValue(mergedCriteriaDTO, "periodfrom");
            string periodToStr = GetCriteriaValue(mergedCriteriaDTO, "periodto");

            // Support multiple OUIds like GB4
            string ouIdStr = GetCriteriaValue(mergedCriteriaDTO, "ouid") ?? GetCriteriaValue(mergedCriteriaDTO, "workouid") ?? "";

            int type = int.TryParse(GetCriteriaValue(mergedCriteriaDTO, "type"), out int t) ? t : 0;
            int toIncludeDebitClass = int.TryParse(GetCriteriaValue(mergedCriteriaDTO, "toincludedebitclass"), out int d) ? d : 0;

            int? employeeId = int.TryParse(GetCriteriaValue(mergedCriteriaDTO, "employeeid"), out int empId) ? empId : null;
            int? departmentId = int.TryParse(GetCriteriaValue(mergedCriteriaDTO, "departmentid"), out int deptId) ? deptId : null;
            int? leaveTypeId = int.TryParse(GetCriteriaValue(mergedCriteriaDTO, "leavetypeid"), out int ltid) ? ltid : null;
            int? employeeTypeId = int.TryParse(GetCriteriaValue(mergedCriteriaDTO, "employeetypeid"), out int etid) ? etid : null;
            int? staffTypeId = int.TryParse(GetCriteriaValue(mergedCriteriaDTO, "stafftypeid"), out int stid) ? stid : null;
            int? payGroupId = int.TryParse(GetCriteriaValue(mergedCriteriaDTO, "paygroupid"), out int pgid) ? pgid : null;

            // 2️⃣ Parse dates safely
            var pf = ParseDate(periodFromStr);
            var pt = ParseDate(periodToStr);

            if (!pf.HasValue || !pt.HasValue)
            {
                throw new ArgumentException($"Invalid periodFrom/periodTo. From={periodFromStr}, To={periodToStr}");
            }

            DateTime periodFrom = pf.Value;
            DateTime periodTo = pt.Value;

            // 3️⃣ Fetch period if Type == -2 (Screen case)
            // GB4 parity: Type == -2 discards the incoming PeriodFrom/PeriodTo and substitutes
            // the current calendar year (server GETDATE()), and also leaves the report's
            // TLEAVEDETAIL cutoff date unbounded ('31-Dec-9999') instead of PeriodTo.
            bool isScreenPeriod = type == -2;
            if (isScreenPeriod)
            {
                var periodList = await payProcessDAL.GetPeriodAsync(loginDTO.WorkPeriodId, loginDTO);
                if (periodList.Count == 0)
                    throw new MethodNotAllowedException("No record found for login period.");

                periodFrom = periodList[0].PeriodFromDate;
                periodTo = periodList[0].PeriodToDate;
            }

            // 4️⃣ Truncate DateTime to seconds
            periodFrom = TruncateToSeconds(periodFrom);
            periodTo = TruncateToSeconds(periodTo);

            // 5️⃣ Execute pre-processing (temp tables created here — same connection)
            await payProcessDAL.ExecuteLeaveStatusPreProcessingAsync(periodFrom, periodTo, ouIdStr, loginDTO);

            // 6️⃣ Execute main leave status report
            var leaveStatusList = await payProcessDAL.GetLeaveStatusReportAsync(
                periodFrom,
                periodTo,
                ouIdStr,
                toIncludeDebitClass,
                loginDTO,
                employeeId,
                isScreenPeriod,
                departmentId,
                leaveTypeId,
                employeeTypeId,
                staffTypeId,
                payGroupId
            );

            // 7️⃣ Adjust balances safely (GB4 parity: LeaveBalance is only reduced when the
            // result would stay >= 0; otherwise the raw SQL value is kept as-is, not floored to 0)
            foreach (var leave in leaveStatusList)
            {
                leave.Opening -= leave.OpeningExpiredLeave;

                if (leave.ExpiredLeave < 0)
                {
                    leave.ExpiredLeave = 0;
                }
                else if (leave.LeaveBalance - leave.OpeningExpiredLeave - leave.ExpiredLeave >= 0)
                {
                    leave.LeaveBalance -= (leave.OpeningExpiredLeave + leave.ExpiredLeave);
                }
            }

            // 9️⃣ Serialize JSON
            return JsonSerializer.Serialize(leaveStatusList, new JsonSerializerOptions
            {
                WriteIndented = true,
                PropertyNamingPolicy = JsonNamingPolicy.CamelCase
            });
        }

        // Helper: merge request criteria with the login's security-scoping UserCriteriaDTO
        // (GB4 applies both sources — request CriteriaDTO and LoginDTO.UserCriteriaDTO — to the report)
        private CriteriaDTO MergeCriteria(CriteriaDTO criteriaDTO, CriteriaDTO userCriteriaDTO)
        {
            if (userCriteriaDTO?.SectionCriteriaList == null || userCriteriaDTO.SectionCriteriaList.Count == 0)
                return criteriaDTO;

            var merged = new CriteriaDTO
            {
                SectionCriteriaList = new List<SectionCriteriaDTO>()
            };

            if (criteriaDTO?.SectionCriteriaList != null)
                merged.SectionCriteriaList.AddRange(criteriaDTO.SectionCriteriaList);

            merged.SectionCriteriaList.AddRange(userCriteriaDTO.SectionCriteriaList);

            return merged;
        }

        // Helper: Extract criteria
        private string GetCriteriaValue(CriteriaDTO criteriaDTO, string fieldName)
        {
            return criteriaDTO?.SectionCriteriaList?
                .SelectMany(s => s.AttributesCriteriaList)
                .FirstOrDefault(a => a.FieldName.Equals(fieldName, StringComparison.OrdinalIgnoreCase))
                ?.FieldValue?.ToString();
        }

        // Helper: Truncate DateTime to seconds
        private DateTime TruncateToSeconds(DateTime dt)
        {
            return new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second, 0, dt.Kind);
        }
        private DateTime? ParseDate(string value)
        {
            if (string.IsNullOrWhiteSpace(value))
                return null;

            // ✅ Try normal DateTime
            if (DateTime.TryParse(value, out var dt))
                return dt;

            // ✅ Try Epoch (milliseconds)
            if (long.TryParse(value, out var epoch))
            {
                try
                {
                    // Detect seconds vs milliseconds
                    if (epoch > 100000000000) // milliseconds
                        return DateTimeOffset.FromUnixTimeMilliseconds(epoch).DateTime;
                    else
                        return DateTimeOffset.FromUnixTimeSeconds(epoch).DateTime;
                }
                catch
                {
                    return null;
                }
            }

            return null;
        }

        // ════════════════════════════════════════════════════════════════════
        // PayProcessForSepration (GB4 route/BLL-method spelling; GB5 method named
        // with correct spelling for readability). GB4 source: PayProcessBLL.cs
        // PayProcessForSepration, lines 20327-21108.
        //
        // IsUndoRequired semantics (verified against live GB4 code):
        //   0 = pure undo — runs the 3 undo queries, commits, and returns
        //       "Undo Done.." immediately WITHOUT falling through to insert logic.
        //   1 = normal process — duplicate-guard is ACTIVE (throws if a TPAYPROCESS
        //       row already exists for OU/PayPeriod(-4)/PayConfig/Employee).
        //   2 = undo-then-reprocess — runs the same 3 undo queries first (rows are
        //       gone by the time the duplicate-check count runs), then falls through
        //       to the insert logic. The duplicate-guard condition in GB4 is
        //       `Count > 0 && IsUndoRequired == 1`, so it is structurally bypassed
        //       for IsUndoRequired == 2 regardless of undo — GB4 never re-arms the
        //       guard for the reprocess branch.
        //
        // SEPARATION_PAY_PERIOD_ID = -4 (PayProcessQB.SEPARATION_PAY_PERIOD_ID) is
        // written as the literal PayPeriodId for both TPAYPROCESS and TPAYPROCESSADDON
        // rows; PayProcessPostDTO.PayPeriodId (the real period) is used only for
        // MPayPeriod/TPAYREVISION date-context joins, mirroring GB4 exactly.
        //
        // Scope decision — Payparsing / TotalEarning_Decution_NetSalaryUpdation / C2C_UPDATION:
        // All three GB4 helpers are confirmed GENERIC, SHARED pay-process infrastructure
        // (driven purely by MFormula/MPayConfigurationDetail metadata + PayPeriodId/
        // PayConfigId/OUId — nothing separation-specific), called by other GB4 pay-process
        // variants too. Ported here as private helpers scoped to what separation-processing
        // needs; NOT extracted to a shared class yet since no second GB5 pay-process
        // migration exists in this codebase to share them with. A future migration of
        // another PayProcess variant (regular monthly run, arrear run, etc.) that also
        // needs these three should hoist them into a shared helper rather than
        // re-duplicating — flagging explicitly, not a vague TODO.
        //   - Payparsing: ported in full — per-phase MFormula evaluation loop building
        //     "F_<code> = ISNULL(<expr>,<default>)" UPDATE fragments against TPAYPROCESSADDON,
        //     plus the "exec PROCAFTERPAYPROCESSPOSTINGDURINGFORMULA" stored-proc call per
        //     phase. GB4's AutoId/MinPhase/StartNumber optional params (used by OTHER pay-process
        //     callers for narrower re-runs) are omitted here since separation always calls with
        //     the 3-arg form (AutoId=0, MinPhase=-1, StartNumber=-1) — full phase range every time.
        //   - TotalEarning_Decution_NetSalaryUpdation: ported in full (earning/deduction/addition
        //     sum-expression building from PayConfigurationDetailArray, then 2 UPDATE statements).
        //   - C2C_UPDATION: ported in full, including GB4's hardcoded PayElementIds allowlist
        //     ("-1499999992,-1499999989,...") used verbatim.
        // ════════════════════════════════════════════════════════════════════

        private const int SEPARATION_MODULE_ID = -1499999788; // MFormula.ModuleId for PAYPROCESS phase formulas (GB4 parity)

        public async Task<string> PayProcessForSeparation(PayProcessPostDTO PayProcessPostDTO, LoginDTO LoginDTO)
        {
            if (PayProcessPostDTO == null)
                throw new ArgumentNullException(nameof(PayProcessPostDTO));

            if (PayProcessPostDTO.EmployeeId == -1)
                throw new MethodNotAllowedException("Employee must be selected..");

            // NumberofDays==0 check intentionally NOT enforced — GB4 disabled this check
            // ("Not Required we can process for 0 days as per discussion with VV Sir on
            // 10th Oct 2022") and left it live that way; match GB4's actual behavior.

            if (PayProcessPostDTO.ReleivingId == -1)
                throw new MethodNotAllowedException("Please select releiving details to proceed..");

            var payPeriod = await _PayPeriodDAL.GetPayPeriod(PayProcessPostDTO.PayPeriodId, LoginDTO).ConfigureAwait(false);
            if (payPeriod == null)
                throw new DataNotFoundException("Pay period not found.");

            DateTime fromDate = payPeriod.PayPeriodFromDate;
            DateTime toDate = payPeriod.PayPeriodToDate;
            decimal numberOfDays = PayProcessPostDTO.NumberofDays; // GB4: taken from DTO, not recomputed from date range
            int totalDays = Convert.ToInt32(payPeriod.PayPeriodTotalDays);
            const int weeklyOff = 0; // GB4: hardcoded 0

            int ouId = LoginDTO.WorkOUId;
            int payConfigurationId = PayProcessPostDTO.PayConfigId;
            int employeeId = PayProcessPostDTO.EmployeeId;

            var trans = await _QueryExecutor.BeginTransactionAsync(LoginDTO).ConfigureAwait(false);
            try
            {
                // 0 = pure undo, 2 = undo-then-reprocess
                if (PayProcessPostDTO.IsUndoRequired == 0 || PayProcessPostDTO.IsUndoRequired == 2)
                {
                    await payProcessDAL.UndoSeparationPayProcess(ouId, payConfigurationId, employeeId, LoginDTO, trans).ConfigureAwait(false);

                    if (PayProcessPostDTO.IsUndoRequired == 0)
                    {
                        await _QueryExecutor.CommitAsync(trans).ConfigureAwait(false);
                        return "Undo Done..";
                    }
                }

                int duplicateCount = await payProcessDAL.GetSeparationDuplicateCount(ouId, payConfigurationId, employeeId, LoginDTO, trans).ConfigureAwait(false);
                if (duplicateCount > 0 && PayProcessPostDTO.IsUndoRequired == 1)
                {
                    throw new FoundException("A pay-process record already exists for this employee/period — undo the existing one first.");
                }

                // GB4: GenericLazy.GetNumber(2, "MONTHLYATTENDANCE", LoginDTO) — deliberately reuses
                // the MONTHLYATTENDANCE autonumber sequence (not a dedicated PAYPROCESS sequence).
                AutoNumberDTO autoNumberDTO = await _AutoNumber.GetNumberAsync(2, "MONTHLYATTENDANCE", LoginDTO).ConfigureAwait(false);
                int startNumber = autoNumberDTO.StartNumber;

                await payProcessDAL.InsertSeparationPayProcessHeader(
                    startNumber, PayProcessPostDTO.PayPeriodId, payConfigurationId, ouId,
                    numberOfDays, totalDays, fromDate, toDate, weeklyOff, employeeId,
                    LoginDTO, trans).ConfigureAwait(false);

                await payProcessDAL.UpdateSeparationDailyAttendanceSum(ouId, payConfigurationId, employeeId, LoginDTO, trans).ConfigureAwait(false);

                // ── Addon fields (Leave / Fixed / Variable) ──────────────────────
                const int monthDiff = 1; // GB4: hardcoded 1 for separation

                var creditLeaveCodes = await payProcessDAL.GetCreditLeaveCodes(LoginDTO, trans).ConfigureAwait(false);
                var leaveColumns = new List<string>();
                var leaveValues = new List<string>();
                foreach (var code in creditLeaveCodes)
                {
                    if (string.IsNullOrWhiteSpace(code) || !IsSafeIdentifier(code)) continue; // metadata guard
                    leaveColumns.Add($"L_{code}");
                    leaveColumns.Add($"L_{code}OP");
                    leaveValues.Add("0"); // GB4: leave addon values are always literal 0 during separation
                    leaveValues.Add("0");
                }

                var fixedFields = await payProcessDAL.GetFixedAdditionDeductionFields(LoginDTO, trans).ConfigureAwait(false);
                var fixedColumns = new List<string>();
                var fixedValues = new List<string>();
                foreach (var f in fixedFields)
                {
                    if (string.IsNullOrWhiteSpace(f.Code) || !IsSafeIdentifier(f.Code))
                        throw new InvalidOperationException($"Unsafe addon field code encountered: '{f.Code}'.");
                    fixedColumns.Add(f.Code);
                    fixedValues.Add(BuildFixedAddonValueExpr(f.ApplicableType, f.Nature, f.FieldDataType, f.Code, monthDiff));
                }

                var variableFields = await payProcessDAL.GetVariableAdditionDeductionFields(LoginDTO, trans).ConfigureAwait(false);
                var variableColumns = new List<string>();
                var variableValues = new List<string>();
                foreach (var f in variableFields)
                {
                    if (string.IsNullOrWhiteSpace(f.Code) || !IsSafeIdentifier(f.Code))
                        throw new InvalidOperationException($"Unsafe addon field code encountered: '{f.Code}'.");
                    variableColumns.Add(f.Code);
                    variableValues.Add(BuildVariableAddonValueExpr(f.ApplicableType, f.FieldDataType, f.Code, monthDiff));
                }

                var allColumns = new List<string>();
                allColumns.AddRange(leaveColumns);
                allColumns.AddRange(fixedColumns);
                allColumns.AddRange(variableColumns);

                var allValues = new List<string>();
                allValues.AddRange(leaveValues);
                allValues.AddRange(fixedValues);
                allValues.AddRange(variableValues);

                string dynamicColumns = allColumns.Count > 0 ? "," + string.Join(",\r\n", allColumns) : string.Empty;
                string dynamicValues = allValues.Count > 0 ? "," + string.Join(",\r\n", allValues) : string.Empty;

                await payProcessDAL.InsertSeparationPayProcessAddon(
                    startNumber, payConfigurationId, ouId, fromDate, toDate, employeeId,
                    dynamicColumns, dynamicValues, LoginDTO, trans).ConfigureAwait(false);

                // GB4 mutates the in-memory DTO to -4 before calling the 3 follow-on methods —
                // proving -4 is the permanent stored PayPeriodId for all separation rows.
                PayProcessPostDTO.PayPeriodId = PayRollDAL.Query.PayProcess.PayProcessQB.SEPARATION_PAY_PERIOD_ID;

                await PayparsingAsync(PayProcessPostDTO, LoginDTO, trans).ConfigureAwait(false);
                await TotalEarningDeductionNetSalaryUpdationAsync(PayProcessPostDTO, LoginDTO, trans).ConfigureAwait(false);
                await C2CUpdationAsync(PayProcessPostDTO, LoginDTO, trans).ConfigureAwait(false);

                await _QueryExecutor.CommitAsync(trans).ConfigureAwait(false);
                return "Payroll Processing Done..";
            }
            catch (Exception)
            {
                await _QueryExecutor.RollbackAsync(trans).ConfigureAwait(false);
                throw;
            }
        }

        // GB4 Fixed-addon value-expression branching (ApplicableType x Nature x FieldDataType).
        private static string BuildFixedAddonValueExpr(int applicableType, int nature, int fieldDataType, string code, int monthDiff)
        {
            string alias = applicableType switch
            {
                0 => nature == 0 ? "overallpayrevisionaddon" : "overallpayrevisionaddonfo",
                1 => nature == 0 ? "oulevelpayrevisionaddon" : "oulevelpayrevisionaddonfo",
                2 => nature == 0 ? "payconfigurationpayrevisionaddon" : "payconfigurationpayrevisionaddonfo",
                3 => nature == 0 ? "employeegrouppayrevisionaddon" : "employeegrouppayrevisionaddonfo",
                4 => nature == 0 ? "dagrouppayrevisionaddon" : "dagrouppayrevisionaddonfo",
                5 => nature == 0 ? "employeepayrevisionaddon" : "employeepayrevisionaddonfo",
                _ => throw new InvalidOperationException($"Unsupported ApplicableType '{applicableType}' for fixed addon field '{code}'.")
            };

            return fieldDataType == 2
                ? $"{monthDiff}*ISNULL({alias}.{code},0)"
                : $"ISNULL({alias}.{code},'')";
        }

        // GB4 Variable/Periodic-addon value-expression branching (ApplicableType x FieldDataType only).
        private static string BuildVariableAddonValueExpr(int applicableType, int fieldDataType, string code, int monthDiff)
        {
            string alias = applicableType switch
            {
                0 => "OverallPeriodicaddon",
                1 => "oulevelperiodicaddon",
                2 => "payconfigurationPeriodicaddon",
                3 => "employeegroupperiodicaddon",
                4 => "dagroupperiodicaddon",
                5 => "EmployeePeriodicaddonon",
                _ => throw new InvalidOperationException($"Unsupported ApplicableType '{applicableType}' for variable addon field '{code}'.")
            };

            return fieldDataType == 2
                ? $"{monthDiff}*ISNULL({alias}.{code},0)"
                : $"ISNULL({alias}.{code},'')";
        }

        // GB4: Payparsing (shared, generic phase-formula engine — see scope-decision note above).
        // Ported in full for the 3-arg call shape separation always uses (AutoId=0, MinPhase=-1,
        // StartNumber=-1 — i.e. full phase range, TPAYPROCESS-scoped by PayPeriodId/PayConfigId/EmployeeId).
        private async Task PayparsingAsync(PayProcessPostDTO PayProcessPostDTO, LoginDTO LoginDTO, DbTransaction trans)
        {
            var fieldNames = new List<string>();

            var creditLeaveCodes = await payProcessDAL.GetCreditLeaveCodes(LoginDTO, trans).ConfigureAwait(false);
            foreach (var code in creditLeaveCodes)
            {
                fieldNames.Add($"L_{code}");
                fieldNames.Add($"L_{code}OP");
            }

            var fixedFields = await payProcessDAL.GetFixedAdditionDeductionFields(LoginDTO, trans).ConfigureAwait(false);
            foreach (var f in fixedFields) fieldNames.Add(f.Code);

            var variableFields = await payProcessDAL.GetVariableAdditionDeductionFields(LoginDTO, trans).ConfigureAwait(false);
            foreach (var f in variableFields) fieldNames.Add(f.Code);

            // GB4 also folds in Advance (A_<code>) and WageGroup leave-type-5 (L_<code>AMT / L_<code>)
            // field names here. GB5 does not yet have GB5-side DAL lookups for the Advance/Gcm
            // (MGCM GcmType=-1399999983) or WageGroup-leave-type=5 metadata sources in PayRollDAL —
            // both are out of scope for this migration (no existing GB5 DAL surface to call) and are
            // deliberately omitted. Any MFormula field whose code maps to one of those two sources
            // will simply not match a "F_<code>" comparison below and will be skipped, matching the
            // conservative direction of GB4's own "if FormulaCode==FieldName" gate (a formula with no
            // matching field name is silently never applied) — documented gap, not a silent bug.

            var formulas = await payProcessDAL.GetFormulaFields(LoginDTO, trans).ConfigureAwait(false);
            int maxPhase = await payProcessDAL.GetMaxFormulaPhase(LoginDTO, trans).ConfigureAwait(false);

            for (int phase = 0; phase <= maxPhase; phase++)
            {
                var strUpdate = new StringBuilder();
                var phaseFormulas = formulas.Where(f => f.Phase == phase).ToList();

                foreach (var formula in phaseFormulas)
                {
                    foreach (var fieldName in fieldNames)
                    {
                        if (!string.Equals($"f_{formula.Code}".ToLowerInvariant(), fieldName.ToLowerInvariant(), StringComparison.Ordinal))
                            continue;

                        if (!IsSafeIdentifier(formula.Code))
                            throw new InvalidOperationException($"Unsafe formula code encountered: '{formula.Code}'.");

                        if (formula.FormulaType == 0) // Formula
                        {
                            string expr = (formula.Expression ?? string.Empty).Replace("{", "(").Replace("}", ")");
                            strUpdate.Append($"F_{formula.Code}\r\n = ISNULL({expr},0),\r\n");
                        }
                        else if (formula.FormulaType == 1) // Function
                        {
                            strUpdate.Append(formula.DataType == 1
                                ? $"F_{formula.Code}\r\n = ISNULL({formula.FunctionName},''),\r\n"
                                : $"F_{formula.Code}\r\n = ISNULL({formula.FunctionName},0),\r\n");
                        }
                    }
                }

                if (strUpdate.Length > 0)
                {
                    string update = strUpdate.ToString();
                    update = update.Remove(update.Length - 3); // trim trailing ",\r\n"
                    await payProcessDAL.UpdatePayProcessAddonByFormula(
                        PayProcessPostDTO.PayPeriodId, PayProcessPostDTO.PayConfigId, PayProcessPostDTO.EmployeeId, update,
                        LoginDTO, trans).ConfigureAwait(false);
                }

                // GB4: exec PROCAFTERPAYPROCESSPOSTINGDURINGFORMULA @OUID,@EMPLOYEEID,@PAYCONFIGURATIONID,@PAYPERIODID,@FORMULALEVEL
                await _QueryExecutor.ExecuteAsync(
                    LoginDTO,
                    "exec PROCAFTERPAYPROCESSPOSTINGDURINGFORMULA @OUID,@EMPLOYEEID,@PAYCONFIGURATIONID,@PAYPERIODID,@FORMULALEVEL",
                    new
                    {
                        OUID = LoginDTO.WorkOUId,
                        EMPLOYEEID = PayProcessPostDTO.EmployeeId,
                        PAYCONFIGURATIONID = PayProcessPostDTO.PayConfigId,
                        PAYPERIODID = PayProcessPostDTO.PayPeriodId,
                        FORMULALEVEL = phase
                    },
                    trans).ConfigureAwait(false);
            }
        }

        // GB4: TotalEarning_Decution_NetSalaryUpdation — ported in full.
        private async Task TotalEarningDeductionNetSalaryUpdationAsync(PayProcessPostDTO PayProcessPostDTO, LoginDTO LoginDTO, DbTransaction trans)
        {
            PayConfigurationDTO payConfiguration = await _PayConfigurationDAL.GetPayConfiguration(PayProcessPostDTO.PayConfigId, LoginDTO).ConfigureAwait(false);

            var totalEarning = new StringBuilder();
            var totalDeduction = new StringBuilder();
            var totalAddition = new StringBuilder();

            foreach (var detail in payConfiguration?.PayConfigurationDetailArray ?? new List<PayConfigurationDetailDTO>())
            {
                string? fieldExpr = detail.PayConfigurationDetailFieldType switch
                {
                    0 => $"ISNULL({detail.PayConfigurationDetailFieldCode},0)+", // AdditionDeduction
                    1 => $"ISNULL(F_{detail.PayConfigurationDetailFieldCode},0)+", // Formula
                    3 => $"ISNULL(A_{detail.PayConfigurationDetailFieldCode},0)+", // Advance
                    _ => null // Standard(2) / Info(4) contribute nothing
                };

                if (fieldExpr == null) continue;

                if (detail.PayConfigurationDetailOperationType == 0) // Add
                {
                    totalEarning.Append(fieldExpr);
                    if (detail.PayConfigurationDetailFieldType == 0) totalAddition.Append(fieldExpr); // GB4: CR #29303, 30 Nov 2020
                }
                else if (detail.PayConfigurationDetailOperationType == 1) // Sub
                {
                    totalDeduction.Append(fieldExpr);
                }
            }

            totalEarning.Append('0');
            totalDeduction.Append('0');
            totalAddition.Append('0');

            await payProcessDAL.UpdateTotalEarningDeduction(
                PayProcessPostDTO.PayConfigId, PayProcessPostDTO.PayPeriodId, LoginDTO.WorkOUId,
                totalEarning.ToString(), totalDeduction.ToString(), totalAddition.ToString(),
                LoginDTO, trans).ConfigureAwait(false);

            await payProcessDAL.UpdateNetSalary(
                PayProcessPostDTO.PayConfigId, PayProcessPostDTO.PayPeriodId, LoginDTO.WorkOUId,
                LoginDTO, trans).ConfigureAwait(false);
        }

        // GB4: C2C_UPDATION — ported in full, including the hardcoded PayElementIds allowlist.
        private async Task C2CUpdationAsync(PayProcessPostDTO PayProcessPostDTO, LoginDTO LoginDTO, DbTransaction trans)
        {
            PayConfigurationDTO payConfiguration = await _PayConfigurationDAL.GetPayConfiguration(PayProcessPostDTO.PayConfigId, LoginDTO).ConfigureAwait(false);

            const string payElementIds = "-1499999992,-1499999989,-1499999987,-1499999986,-1499999984 ,-1499999983,-1499999982 ,-1499999981";
            var fieldName = new StringBuilder();

            foreach (var detail in payConfiguration?.PayConfigurationDetailArray ?? new List<PayConfigurationDetailDTO>())
            {
                if (detail.PayelementId == -1) continue; // GB4: -1 PayelementId excluded (Vikash, 04 Aug 2023)
                if (!payElementIds.Contains(detail.PayelementId.ToString())) continue;

                string? fieldExpr = detail.PayConfigurationDetailFieldType switch
                {
                    0 => $"ISNULL({detail.PayConfigurationDetailFieldCode},0)+",
                    1 => $"ISNULL(F_{detail.PayConfigurationDetailFieldCode},0)+",
                    3 => $"ISNULL(A_{detail.PayConfigurationDetailFieldCode},0)+",
                    _ => null
                };
                if (fieldExpr != null) fieldName.Append(fieldExpr);
            }

            fieldName.Append('0');

            await payProcessDAL.UpdateC2C(
                PayProcessPostDTO.PayConfigId, PayProcessPostDTO.PayPeriodId, LoginDTO.WorkOUId,
                fieldName.ToString(), LoginDTO, trans).ConfigureAwait(false);
        }

        // ════════════════════════════════════════════════════════════════════
        // AllOuPayProcessing (GB4 route: PayProcess.svc/All/Ou/Payprocess/).
        //
        // GB4's AllOuPayProcessing is a thin loop: fetch every OU the caller has access to
        // (PayProcessQueryBuilder.GET_ALL_OU_COMPNAY_WISE_USER_ACCESS_RIGHTS) and call the
        // single-OU pay-process engine — GB4 PayProcessBLL.PayProcessing — once per OU. That
        // engine is ~20,000 lines (PayProcessBLL.cs lines 77-20042 in GB4): attendance rollups,
        // formula/addon computation, leave/advance handling, account posting. It has NOT been
        // migrated to GB5 — PayRollBLL.PayProcess currently only has GetPaySlipReport,
        // GetAdditionDeductionPicklist, GetPayProcessAddonPicklist, LeaveStatusReportAsync, and
        // PayProcessForSeparation.
        //
        // Safe-side decision: do NOT port the OU-discovery loop on its own. A loop with nothing
        // to call per OU is dead code that looks half-working, and it is one accidental edit away
        // from someone wiring it to the wrong thing and posting incomplete payroll data per OU.
        // For a financial process, failing loudly and immediately (never touching the database)
        // is safer than any partial implementation. This throws until PayProcessing itself is
        // migrated — do that first, then replace this method with the real OU loop calling it.
        // ════════════════════════════════════════════════════════════════════
        public Task<string> AllOuPayProcessing(PayProcessPostDTO PayProcessPostDTO, LoginDTO LoginDTO)
        {
            throw new NotSupportedException(
                "AllOuPayProcessing is not available in GB5 yet: the underlying single-OU pay-process " +
                "engine (GB4 PayProcessBLL.PayProcessing, ~20,000 lines) has not been migrated. " +
                "Migrate PayProcessing first, then implement the per-OU loop here.");
        }

        // Identifier-safety allowlist for DB-driven column names/codes spliced into dynamic SQL —
        // same approach established by this session's LoadPeriodic/DeleteEmployeeVersion migrations
        // (PeriodicBLL.IsSafeIdentifier). Kept as a private copy here since the helper is not yet
        // promoted to a shared location; a future migration touching a third dynamic-SQL DAL should
        // consider hoisting this to a common helper class instead of a third duplicate.
        private static bool IsSafeIdentifier(string value) =>
            !string.IsNullOrEmpty(value) && value.All(c => char.IsLetterOrDigit(c) || c == '_') && !char.IsDigit(value[0]);
    }
}