using ClosedXML.Excel;
using GB5Shared.Addon;   // for IAddonService
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.EntityHandler;
using GB5Shared.GenerateAutoNumber;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using PayRollDAL.CustomeCode.Periodic;
using PayRollDAL.DTO.Periodic;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant;

namespace PayRollBLL.Periodic
{
    public class PeriodicBLL : IPeriodicBLL
    {
        private readonly IPeriodicDAL _PeriodicDAL;
        private readonly AutoNumber _AutoNumber;
        private readonly IQueryExecutor _QueryExecutor;
        private readonly BaseEntityAppService<PeriodicDTO> _baseEntityAppService;
        private readonly ILogger<PeriodicBLL> _Logger;
        private readonly IAddonService _addonService;   // ← ADDED

        private const string AddonTable = "TPERIODICADDON";   // ← ADDED
        private const string AddonFkCol = "PERIODICID";       // ← ADDED

        public PeriodicBLL(
            IPeriodicDAL IPeriodicDAL,
            AutoNumber AutoNumber,
            IQueryExecutor queryExecutor,
            BaseEntityAppService<PeriodicDTO> baseEntityAppService,
            ILogger<PeriodicBLL> logger,
            IAddonService addonService)   // ← ADDED
        {
            _PeriodicDAL = IPeriodicDAL;
            _AutoNumber = AutoNumber;
            _QueryExecutor = queryExecutor;
            _baseEntityAppService = baseEntityAppService;
            _Logger = logger;
            _addonService = addonService;   // ← ADDED
        }        // ── SavePeriodic ─────────────────────────────────────────────────────────────
        //
        // Mirrors GB4 PeriodicBLL.SavePeriodic(PeriodicDTO, LoginDTO). Preserves: MainSubTypeId
        // auto-lookup (every save), MMDetailId auto-lookup (only when the caller didn't supply
        // one), PeriodicApplicable == 5 ("only for Employee") forcing PayConfigurationId = -1,
        // AutoNumber generation for new records, and FunctionReportingTo/AdminReportingTo mail+
        // mobile auto-population from the employee record for the outbound event payload.
        //
        // NOT ported (out of scope — see PeriodicQB.cs header note):
        //   - Periodic.PeriodicAddon (dynamic addition/deduction child rows / TEMPID bag)
        //   - SavePeriodicMultiple / DeleteBulkPeriodic / PeriodicReport / PeriodicLoad / GetPeriodic
        //
        // NOT ported (genuine GB5 capability gap — called out, not silently invented):
        //   - GB4's PayProcessBLL.IsUpdatePossible(...) check ("Redmine 6147", PeriodicApplicable
        //     == 5 only). No GB5 equivalent exists anywhere in PayRollBLL, matching the identical
        //     gap already documented in OverTimeBLL.SaveOverTime.
        public async Task<int> SavePeriodic(PeriodicDTO periodicDTO, LoginDTO loginDTO, DbTransaction? sameTransaction = null, CancellationToken ct = default)
        {
            if (periodicDTO == null) throw new ArgumentNullException(nameof(periodicDTO));

            bool isNew = periodicDTO.PeriodicId == 0;

            bool isExternalTx = sameTransaction != null;
            var trans = sameTransaction ?? await _QueryExecutor.BeginTransactionAsync(loginDTO);

            try
            {
                // ── MainSubTypeId — always looked up (GB4 parity, unconditional) ────────
                periodicDTO.MainSubTypeId = await _PeriodicDAL.GetMainSubTypeId(
                    periodicDTO.EmployeeId, periodicDTO.OUId, periodicDTO.PayPeriodId, loginDTO, trans);

                // ── MMDetailId — only looked up when the caller didn't already supply one ─
                if (periodicDTO.MMDetailId == -1)
                {
                    periodicDTO.MMDetailId = await _PeriodicDAL.GetMMDetailId(
                        periodicDTO.PayPeriodId, periodicDTO.EmployeeId,
                        periodicDTO.PartyCode, periodicDTO.PartyBranchCode, periodicDTO.WorkTypeCode,
                        loginDTO, trans);
                }

                // ── GB4 PayProcessBLL.IsUpdatePossible(...) check (Redmine 6147, Applicable == 5
                // only) — NOTE: no GB5 equivalent of PayProcessBLL.IsUpdatePossible exists anywhere
                // in PayRollBLL as of this migration. Same gap already documented in
                // OverTimeBLL.SaveOverTime — called out here rather than silently invented.

                // ── PeriodicApplicable == 5 ("only for Employee") forces PayConfigurationId=-1 ─
                if (periodicDTO.PeriodicApplicable == 5)
                {
                    periodicDTO.PayConfigurationId = -1;
                }

                // ── AutoNumber — new records only ───────────────────────────────────────
                if (isNew)
                {
                    var autoNumber = await _AutoNumber.GetNumberAsync(1, AUTONUMBERCONSTANT.PERIODIC, loginDTO);
                    periodicDTO.PeriodicId = autoNumber.StartNumber;
                }

                // ── FunctionReportingTo / AdminReportingTo mail+mobile (event payload only —
                // TPERIODIC has no backing columns for these, matching GB4's Periodic.hbm.xml,
                // which maps neither FunctionalReportingTo nor AdminReportingTo) ────────────
                var employeeInfo = await _PeriodicDAL.GetEmployeeReportingInfo(periodicDTO.EmployeeId, loginDTO, trans);
                if (employeeInfo == null)
                    throw new InvalidOperationException($"Employee (Id={periodicDTO.EmployeeId}) not found.");

                periodicDTO.FunctionReportingToMailId = employeeInfo.ReportInttoEmployeeMailId;
                periodicDTO.FunctionReportingToMobileNumber = employeeInfo.ReportInttoEmployeeMobile;
                periodicDTO.AdminReportingToMailId = employeeInfo.AdminReportingToMailId;
                periodicDTO.AdminReportingToMobileNumber = employeeInfo.AdminReportingToMobile;

                // ── Audit fields (GB4: GenericDal.StandardFieldAssiging) ────────────────
                var now = DateTime.UtcNow;
                if (isNew)
                {
                    periodicDTO.PeriodicCreatedById = loginDTO.UserId;
                    periodicDTO.PeriodicCreatedOn = now;
                }
                periodicDTO.PeriodicModifiedById = loginDTO.UserId;
                periodicDTO.PeriodicModifiedOn = now;

                await _baseEntityAppService.ExecuteSaveAsync(
                    EntityConstant.OBJECTPERIODIC,
                    EventTypeConstant.SAVEPERIODICEVENTTYPEID,
                    periodicDTO,
                    loginDTO,
                    async tx =>
                    {
                        return isNew
                            ? await _PeriodicDAL.SavePeriodic(periodicDTO, loginDTO, tx)
                            : await _PeriodicDAL.UpdatePeriodic(periodicDTO, loginDTO, tx);
                    },
                    null,
                    -1, // no BizTransactionClass concept for Periodic — GB4's PeriodicDTO has no BIZTransactionTypeId field at all
                    -1,
                    trans,
                    isNewEntity: isNew);

                if (!isExternalTx)
                    await _QueryExecutor.CommitAsync(trans);

                _Logger.LogInformation(
                    "Periodic {Action} — Id: {PeriodicId}, Employee: {EmployeeCode} {EmployeeName}",
                    isNew ? "Created" : "Updated", periodicDTO.PeriodicId,
                    employeeInfo.EmployeeCode, employeeInfo.EmployeeName);

                return periodicDTO.PeriodicId;
            }
            catch (Exception)
            {
                if (!isExternalTx)
                    await _QueryExecutor.RollbackAsync(trans);
                throw;
            }
        }
        public async Task<string> GetPeriodicList(
        CriteriaDTO CriteriaDTO, LoginDTO LoginDTO, CancellationToken ct = default)
        {
            try
            {
                var list = (await _PeriodicDAL.GetPeriodicList(CriteriaDTO, LoginDTO, ct))?.ToList()
                           ?? new List<PeriodicDTO>();

                if (list.Count > 0)
                {
                    var ids = list.Select(x => x.PeriodicId);
                    var addons = await _addonService.GetAddonJsonBatchAsync(AddonTable, AddonFkCol, ids, LoginDTO, ct);
                    var addonMap = addons.ToDictionary(a => a.Id, a => a.FeAddon);

                    foreach (var item in list)
                    {
                        addonMap.TryGetValue(item.PeriodicId, out var feAddon);
                        item.PeriodicAddon = new List<PeriodicAddonDTO>
                {
                    new PeriodicAddonDTO { Id = item.PeriodicId, FeAddon = feAddon! }
                };
                    }
                }

                return JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // ── GetPeriodic (Periodic.svc/GetPeriodic) ──────────────────────────────────────
        //
        // Mirrors GB4 PeriodicDAL.GetPeriodicDetails (called via PeriodicBLL.GetPeriodic /
        // Periodic.svc.cs). GB4 called the DAL method TWICE — once for data, once with Count=true
        // for Total — this GB5 port instead issues the paged query and the COUNT(*) query in a
        // single DAL call and returns (Items, Total), matching this session's established tuple
        // convention (see EmployeeBLL.GetEmployeeVsShiftPattern).
        //
        // Preserved GB4 business rules:
        //   1. Mandatory PayConfiguration selection — throws if "payconfiguration.id"/
        //      "payconfigurationid" is absent from CriteriaDTO (GB4: MethodNotAllowedException).
        //   2. IsPartialProcessRequired==0 → dispatches entirely to the "New" (multi-posting-
        //      position, TPOSTINGPOSITION-driven) query shape; otherwise the "standard"
        //      (TPERIODIC-table-based) shape is used.
        //   3. The 4-flag (employeecodesort/employeenamesort/*ascdesc) sort-order if-chain — 5
        //      possible outcomes — applies ONLY to the standard path (GB4's "New" path has no
        //      sort-flag handling at all; it always orders by EMP.EMPLOYEECODE).
        //   4. "Keep only first PeriodicAddon" flatten quirk — standard path only (GB4 parity).
        //
        // GB5 improvement over GB4 on the "New" path: GB4 re-fetches the addon collection with a
        // separate NHibernate Get<Periodic>() call PER ROW (N+1). GB5 batches every non-zero
        // PeriodicId's addon lookup into one call via IAddonService.GetAddonJsonBatchAsync.
        public async Task<(List<PeriodicDTO> Items, int Total)> GetPeriodic(
            int payPeriodId, int firstNumber, int maxResult, CriteriaDTO criteriaDTO, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var flat = GB5Shared.DTO.Framework.Criteria.CriteriaParser.Parse(criteriaDTO);

            int employeeCodeSort = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetInt(flat, "employeecodesort", 1);
            int employeeNameSort = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetInt(flat, "employeenamesort", 1);
            int employeeCodeSortAscDesc = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetInt(flat, "employeecodesortascdesc", 1);
            int employeeNameSortAscDesc = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetInt(flat, "employeenamesortascdesc", 1);

            int payConfigId = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetInt(flat, "payconfiguration.id", -1);
            if (payConfigId == -1)
                payConfigId = GB5Shared.DTO.Framework.Criteria.CriteriaParser.GetInt(flat, "payconfigurationid", -1);

            if (payConfigId == -1)
                throw new InvalidOperationException("PayConfiguration must be selected..");

            var isPartialProcessRequired = await _PeriodicDAL.GetPayConfigurationIsPartialProcessRequired(payConfigId, loginDTO, ct);

            if (isPartialProcessRequired == 0)
            {
                var (newItems, newTotal) = await _PeriodicDAL.GetPeriodicNew(
                    payPeriodId, firstNumber, maxResult, criteriaDTO, loginDTO, ct);

                // GB5 batched addon fetch — replaces GB4's per-row N+1 re-fetch.
                var nonZeroIds = newItems.Where(x => x.PeriodicId != 0).Select(x => x.PeriodicId).ToList();
                if (nonZeroIds.Count > 0)
                {
                    var addons = await _addonService.GetAddonJsonBatchAsync(AddonTable, AddonFkCol, nonZeroIds, loginDTO, ct);
                    var addonMap = addons.ToDictionary(a => a.Id, a => a.FeAddon);
                    foreach (var item in newItems)
                    {
                        if (item.PeriodicId == 0) continue;
                        addonMap.TryGetValue(item.PeriodicId, out var feAddon);
                        item.PeriodicAddon = new List<PeriodicAddonDTO>
                        {
                            new PeriodicAddonDTO { Id = item.PeriodicId, FeAddon = feAddon! }
                        };
                    }
                }

                return (newItems, newTotal);
            }

            // ── Standard path — 4-flag sort-order if-chain (GB4 parity, 5 outcomes) ─────
            string orderBy;
            if (employeeCodeSort == 1 && employeeNameSort == 1)
                orderBy = "ORDER BY emp.EMPLOYEESORTORDER";
            else if (employeeCodeSort == 0 && employeeNameSort == 1 && employeeCodeSortAscDesc == 0)
                orderBy = "ORDER BY emp.EMPLOYEECODE ASC";
            else if (employeeCodeSort == 0 && employeeNameSort == 1 && employeeCodeSortAscDesc == 1)
                orderBy = "ORDER BY emp.EMPLOYEECODE DESC";
            else if (employeeCodeSort == 1 && employeeNameSort == 0 && employeeNameSortAscDesc == 0)
                orderBy = "ORDER BY emp.EMPLOYEENAME ASC";
            else if (employeeCodeSort == 1 && employeeNameSort == 0 && employeeNameSortAscDesc == 1)
                orderBy = "ORDER BY emp.EMPLOYEENAME DESC";
            else
                orderBy = "ORDER BY periodic.PERIODICID"; // GB4 has no explicit fallback branch; a stable default is required for OFFSET/FETCH.

            var (items, total) = await _PeriodicDAL.GetPeriodicStandard(
                payPeriodId, firstNumber, maxResult, orderBy, criteriaDTO, loginDTO, ct);

            // GB4 quirk: keep only the FIRST PeriodicAddon child when more than one exists.
            var nonZeroStdIds = items.Where(x => x.PeriodicId != 0).Select(x => x.PeriodicId).ToList();
            if (nonZeroStdIds.Count > 0)
            {
                var addons = (await _addonService.GetAddonJsonBatchAsync(AddonTable, AddonFkCol, nonZeroStdIds, loginDTO, ct)).ToList();
                var addonMap = addons.ToDictionary(a => a.Id, a => a.FeAddon);
                foreach (var item in items)
                {
                    if (item.PeriodicId == 0) continue;
                    addonMap.TryGetValue(item.PeriodicId, out var feAddon);
                    // "Flatten to first addon" — GB5 batch already returns at most one row per
                    // PeriodicId, so this assignment IS the flatten (no further truncation needed).
                    item.PeriodicAddon = new List<PeriodicAddonDTO>
                    {
                        new PeriodicAddonDTO { Id = item.PeriodicId, FeAddon = feAddon! }
                    };
                }
            }

            return (items, total);
        }

        // ── LoadPeriodic (bulk generation) ──────────────────────────────────────────────
        //
        // Mirrors GB4 Periodic.svc/LoadPeriodic (GET) → PeriodicBLL.PeriodicLoad. Despite the GET
        // verb this is a bulk write: it seeds one TPERIODIC row per eligible active employee for
        // the pay period, plus one TPERIODICADDON row per employee carrying the addon-field
        // default values (fields where MADDITIONDEDUCTION.Nature=1 and ApplicableType=5).
        //
        // AutoNumber reservation intentionally matches GB4's over-allocation: the block is sized
        // from the TOTAL employee count (Count(*) FROM MEMPLOYEE), not the actual eligible-row
        // count (which is only known after the eligibility query runs). AutoNumber blocks are
        // cheap to over-reserve and GB4's own behavior is preserved here rather than "fixed" —
        // the alternative (running the eligibility query first, then reserving exactly enough
        // ids) would require executing the eligibility subquery twice or restructuring the whole
        // insert as two round trips, for no material benefit.
        //
        // MTEMP is eliminated — see PeriodicQB.BuildLoadPeriodic's header comment for the reason
        // and the replacement (@Eligible table variable, single call scope). The GB4 "previous
        // period" LEFT JOIN inside INSERT_PERIODICADDON was verified to be dead code (joined but
        // never projected) and was not ported.
        public async Task<string> LoadPeriodic(int payPeriodId, int payConfigurationId, LoginDTO loginDTO, CancellationToken ct = default)
        {
            var trans = await _QueryExecutor.BeginTransactionAsync(loginDTO);
            try
            {
                var toDate = await _PeriodicDAL.GetPayPeriodToDate(payPeriodId, loginDTO, trans);
                if (toDate == null)
                    throw new InvalidOperationException($"PayPeriod (Id={payPeriodId}) not found.");

                // GB4: Generic.TotalCountSql("Select Count(*) from Memployee") — reserves one
                // PERIODIC id per employee in the system, not just the eligible subset.
                var employeeCount = await _EmployeeCountAsync(loginDTO, trans);

                var autoNumber = await _AutoNumber.GetNumberAsync(employeeCount, AUTONUMBERCONSTANT.PERIODIC, loginDTO);

                var addonFields = (await _PeriodicDAL.GetPeriodicAddonFieldDefaults(loginDTO, trans))?.ToList()
                    ?? new List<PeriodicAddonFieldDefaultDTO>();

                if (addonFields.Count == 0)
                    throw new InvalidOperationException(
                        "There are no fields found in AdditionDeduction for variable nature and Employee level.");

                // Validate every field code is a safe SQL identifier before splicing it into the
                // dynamic column/VALUES lists — column/table names cannot be parameterized, so
                // this is the injection guard (matches the precedent in DynamicAddonService /
                // EmployeeAddOn: reject anything that isn't a plain identifier).
                foreach (var f in addonFields)
                {
                    if (string.IsNullOrWhiteSpace(f.FieldCode) || !IsSafeIdentifier(f.FieldCode))
                        throw new InvalidOperationException($"Unsafe addon field code encountered: '{f.FieldCode}'.");
                }

                var fieldNameList = string.Join(",\r\n", addonFields.Select(f => f.FieldCode));
                var fieldValueList = string.Join(",\r\n", addonFields.Select(f =>
                    $"{FormatDefaultValueLiteral(f.DefaultValue)} AS {f.FieldCode}"));

                await _PeriodicDAL.LoadPeriodic(
                    payPeriodId, payConfigurationId, autoNumber.StartNumber, toDate.Value,
                    fieldNameList, fieldValueList, loginDTO, trans);

                await _QueryExecutor.CommitAsync(trans);

                _Logger.LogInformation(
                    "LoadPeriodic completed — PayPeriodId: {PayPeriodId}, PayConfigurationId: {PayConfigurationId}",
                    payPeriodId, payConfigurationId);

                return "Process Completed..";
            }
            catch (Exception)
            {
                await _QueryExecutor.RollbackAsync(trans);
                throw;
            }
        }

        // ── PeriodicServiceBasedExport (Periodic.svc/Periodic/Service/Based/Export) ─────────
        //
        // GB4: DumpPerodicServiceBased.PeriodicServiceBasedExport. GB4's owning class is a
        // separate class (not PeriodicBLL) that:
        //   1. Loads header rows via LOAD_PERIODIC_FOR_MULTIPLE_NEW into a temp table, then
        //      re-selects them ordered by MGCM.SortOrder/PeriodicId — collapsed into one query
        //      here (PeriodicQB.BuildPeriodicServiceBasedExport — see its header note).
        //   2. Throws "There is not entry found for export.." when zero header rows are
        //      returned (GB4: MethodNotAllowedException) — preserved verbatim below.
        //   3. Loads non-mandatory-flagged... actually MANDATORY-flagged AddOnFields (Entity.Id
        //      == PERIODICADDON_ENTITYID, ISMANDATORY encoding 0=YES) ordered by SortOrder, and
        //      builds a dynamic addon column list from them — same IsSafeIdentifier guard
        //      already established in this class's LoadPeriodic (dynamic SQL column/identifier
        //      injection guard).
        //   4. Merges header + addon values (matched by PeriodicId) and serializes to Excel.
        //      GB4 wrote an actual .xlsx to LogPath/PeriodicServiceBasedExport/{guid}-{ts}/ on
        //      disk, read the bytes back, and returned them Base64-encoded as a JSON string body
        //      — an artifact of needing to hand a "file" back through a WCF string return value.
        //      GB5 modernization: build the workbook directly into the caller-supplied Stream via
        //      ClosedXML (no disk round-trip) and stream it back through the endpoint's
        //      HttpContext.Response (SendStreamAsync — see PeriodicServiceBasedExport.cs), which
        //      this codebase's BaseEndpoint already supports for binary downloads (established
        //      precedent: TMSSL.EndPoints.Certificate.DownloadCertificate). This is an
        //      intentional behavior modernization (binary file response instead of base64-in-
        //      JSON), not a silent change — called out here and in the migration report.
        //
        // -1399999776 = GB4's hardcoded AddOnField "Entity.Id" filter value for TPERIODICADDON's
        // owning entity (PERIODICADDON). No GB5Shared.EntityConstant equivalent exists yet
        // (same gap noted for ADDONFIELD's own entity id in analysis/AddonField_Analysis.md) —
        // preserved as GB4 hardcoded it rather than inventing a new shared constant out of scope
        // for this migration.
        private const int PeriodicAddonEntityId = -1399999776;

        public async Task PeriodicServiceBasedExport(
            int payPeriodId, CriteriaDTO criteriaDTO, LoginDTO loginDTO, Stream output, CancellationToken ct = default)
        {
            var headerRows = await _PeriodicDAL.GetPeriodicServiceBasedExport(payPeriodId, criteriaDTO, loginDTO, ct);
            if (headerRows.Count == 0)
                throw new InvalidOperationException("There is not entry found for export..");

            // ── AddOnFields — mandatory-flagged, ordered by SortOrder (GB4 parity) ──────────
            var addonFieldNames = await _PeriodicDAL.GetPeriodicAddonExportFieldNames(PeriodicAddonEntityId, loginDTO, ct);

            foreach (var fieldName in addonFieldNames)
            {
                if (string.IsNullOrWhiteSpace(fieldName) || !IsSafeIdentifier(fieldName))
                    throw new InvalidOperationException($"Unsafe addon field name encountered: '{fieldName}'.");
            }

            // PeriodicId -> { FieldName -> Value }, populated only when addon fields exist.
            var addonValuesByPeriodicId = new Dictionary<int, IDictionary<string, object>>();
            if (addonFieldNames.Count > 0)
            {
                var addonColumns = ",\r\n       " + string.Join(",\r\n       ", addonFieldNames.Select(f => $"b.{f}"));
                var addonRows = await _PeriodicDAL.GetPeriodicServiceBasedExportAddonValues(payPeriodId, addonColumns, loginDTO, ct);

                foreach (var addonRow in addonRows)
                {
                    var rowDict = (IDictionary<string, object>)addonRow;
                    int rowPeriodicId = Convert.ToInt32(rowDict["PeriodicId"]);
                    addonValuesByPeriodicId[rowPeriodicId] = rowDict;
                }
            }

            // ── Build workbook directly into the caller-supplied stream (no disk round-trip) ──
            using var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("PeriodicServiceBased");

            var fixedHeaders = new[]
            {
                "PayperiodCode", "CustomerCode", "CustomerName", "SiteCode", "SiteName",
                "OrderNumber", "OrderDate", "WorktypeCode", "WorktypeName",
                "Employeecode", "EmployeeName", "Rate"
            };

            int col = 1;
            foreach (var h in fixedHeaders)
                ws.Cell(1, col++).Value = h;
            foreach (var f in addonFieldNames)
                ws.Cell(1, col++).Value = f;

            int row = 2;
            foreach (var dto in headerRows)
            {
                col = 1;
                ws.Cell(row, col++).Value = dto.PayPeriodCode;
                ws.Cell(row, col++).Value = dto.PartyCode;
                ws.Cell(row, col++).Value = dto.PartyName;
                ws.Cell(row, col++).Value = dto.PartyBranchCode;
                ws.Cell(row, col++).Value = dto.PartyBranchName;
                ws.Cell(row, col++).Value = dto.OrderNumber;
                ws.Cell(row, col++).Value = dto.OrderDate;
                ws.Cell(row, col++).Value = dto.WorkExcelTypeCode;
                ws.Cell(row, col++).Value = dto.WorkExcelTypeName;
                ws.Cell(row, col++).Value = dto.EmployeeCode;
                ws.Cell(row, col++).Value = dto.EmployeeName;
                ws.Cell(row, col++).Value = dto.WageRate;

                // GB4: rows whose PeriodicId==0, or with no matching addon row, get "0" literals
                // for every addon column rather than being left blank.
                bool hasAddon = dto.PeriodicId != 0
                    && addonValuesByPeriodicId.TryGetValue(dto.PeriodicId, out var addonValues);

                foreach (var f in addonFieldNames)
                {
                    if (hasAddon && addonValuesByPeriodicId[dto.PeriodicId].TryGetValue(f, out var val) && val != null)
                        ws.Cell(row, col++).Value = Convert.ToString(val);
                    else
                        ws.Cell(row, col++).Value = "0";
                }

                row++;
            }

            wb.SaveAs(output);

            _Logger.LogInformation(
                "PeriodicServiceBasedExport completed — PayPeriodId: {PayPeriodId}, Rows: {RowCount}",
                payPeriodId, headerRows.Count);
        }

        private static bool IsSafeIdentifier(string value) =>
            value.Length > 0 && value.All(c => char.IsLetterOrDigit(c) || c == '_') && !char.IsDigit(value[0]);

        // GB4 built FieldValue as "<DefaultValue> as <Code>" and passed DefaultValue through
        // unescaped (it is admin-configured metadata, not end-user input) — DefaultValue is
        // typically a numeric/expression literal (e.g. "0"). Preserve that shape; guard against
        // empty/whitespace by defaulting to 0.
        private static string FormatDefaultValueLiteral(string? defaultValue) =>
            string.IsNullOrWhiteSpace(defaultValue) ? "0" : defaultValue;

        private async Task<int> _EmployeeCountAsync(LoginDTO loginDTO, DbTransaction trans)
        {
            // GB4: Generic.TotalCountSql("Select Count(*) from Memployee", LoginDTO, true) —
            // total employee count (not filtered by OU/status), matching GB4's over-allocation.
            return await _QueryExecutor.ExecuteScalarAsync<int>(
                loginDTO, "SELECT COUNT(*) FROM MEMPLOYEE WHERE TENANTID = @TenantId",
                new { TenantId = loginDTO.ClientId }, trans);
        }
    }
}