using System.Collections;
using System.Globalization;
using System.Management;
using System.Runtime.CompilerServices;
using static GB5Shared.GB5Constant.Constant;
using System.Text.Json;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using DocumentFormat.OpenXml.Spreadsheet;
using GB5Shared.DTO.Framework.Criteria;
using GB5Shared.DTO.Framework.Enum;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Report;
using GB5Shared.ExcelExport;
using GB5Shared.Export.CSVExport;
using GB5Shared.Export.HybridReport;
using GB5Shared.Export.Pivot;
using GB5Shared.GB5CommonFunction;
using GB5Shared.GB5Exception;
using GB5Shared.Query.Report;
using GB5Shared.QueryExecutor;
using GB5Shared.Telemetry;
using HandlebarsDotNet;
using Microsoft.AspNetCore.Components;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using PdfSharpCore.Pdf;
using PdfSharpCore.Pdf.IO;
using PuppeteerSharp;
using PuppeteerSharp.Media;

namespace GB5Shared.Export
{
    public class ReportExport : IReportExport
    {
        private readonly IExcelExport IExcelExport;
        private readonly IGB5CommonFunction GB5CommonFunction;
        private readonly IConfiguration _configuration;
        private readonly ICSVExport _CSVExport;
        private readonly IDataPivotEngine _dataPivotEngine;
        private readonly IPivotExcelExport _pivotExcelExport;
        private readonly IPivotPdfExport _pivotPdfExport;
        private readonly IQueryExecutor queryExecutor;
        private readonly IMemoryCache _fieldCache;
        private readonly GB5Shared.Export.DrillDown.DrillDownLinkResolver _drillDownLinkResolver;
        private readonly GB5Shared.Export.NativePdf.INativePdfExport _nativePdfExport;

        public ReportExport(
            ICSVExport ICSVExport,
            IExcelExport ExcelExport,
            IGB5CommonFunction IGB5CommonFunction,
            IConfiguration configuration,
            IDataPivotEngine dataPivotEngine,
            IPivotExcelExport pivotExcelExport,
            IPivotPdfExport pivotPdfExport,
            IQueryExecutor IQueryExecutor,
            IMemoryCache fieldCache,
            GB5Shared.Export.NativePdf.INativePdfExport nativePdfExport)
        {
            IExcelExport = ExcelExport;
            GB5CommonFunction = IGB5CommonFunction;
            _CSVExport = ICSVExport;
            _configuration = configuration;
            _dataPivotEngine = dataPivotEngine;
            _pivotExcelExport = pivotExcelExport;
            _pivotPdfExport = pivotPdfExport;
            queryExecutor = IQueryExecutor;
            _fieldCache = fieldCache;
            _drillDownLinkResolver = new GB5Shared.Export.DrillDown.DrillDownLinkResolver(IQueryExecutor, configuration);
            _nativePdfExport = nativePdfExport;
        }

        // PDFEXPORT config paths are optional per-environment overrides (e.g. a client-specific
        // logo/footer) — when unset or pointing at a file that isn't actually there, fall back to
        // the template bundled into the build via ReportTemplateResolver instead of failing.
        private static string ResolveConfiguredOrBundled(string? configuredPath, string bundledPath)
        {
            return !string.IsNullOrWhiteSpace(configuredPath) && File.Exists(configuredPath)
                ? configuredPath
                : bundledPath;
        }

        // Returns pre-fetched data when supplied; otherwise falls back to the URI-calling stream.
        // Every export format (Excel/CSV/PDF/JSON/ReportData/HTML/PdfNative + pivot + the legacy
        // ReportURICalling path) funnels through this one method, which makes it the single place
        // computed fields (MREPORTVSFIELDS.COMPUTEEXPRESSION) need to be layered in for exports.
        private IAsyncEnumerable<Dictionary<string, object?>> GetDataSource(
            ReportCallingDTO req, LoginDTO login, CancellationToken ct,
            IAsyncEnumerable<Dictionary<string, object?>>? preloaded)
        {
            var raw = preloaded ?? IExcelExport.ReportURICalling(req, login, ct);
            return DecorateWithComputedFields(raw, req.ReportViewId, login, ct);
        }

        // Awaits the computed-field catalog lookup once, on first enumeration, then decorates via
        // ComputedFieldEvaluator.Decorate -- which is itself a no-op passthrough when the catalog
        // returns zero rows for this ReportViewId (every report/view that hasn't configured a
        // computed field, i.e. all of them today).
        private async IAsyncEnumerable<Dictionary<string, object?>> DecorateWithComputedFields(
            IAsyncEnumerable<Dictionary<string, object?>> raw,
            int reportViewId,
            LoginDTO login,
            [System.Runtime.CompilerServices.EnumeratorCancellation] CancellationToken ct)
        {
            var defs = await ComputedFieldCatalog
                .GetForViewAsync(queryExecutor, _fieldCache, reportViewId, login, ct)
                .ConfigureAwait(false);

            await foreach (var row in ComputedFieldEvaluator.Decorate(raw, defs, logger: null, ct)
                .WithCancellation(ct).ConfigureAwait(false))
            {
                yield return row;
            }
        }

        private async Task<(PivotResultDTO pivot, List<PivotFieldConfigDTO> fieldConfig)> BuildPivotAsync(
            ReportCallingDTO req, LoginDTO login, CancellationToken ct,
            IAsyncEnumerable<Dictionary<string, object?>>? preloaded = null)
        {
            var fieldConfig = (await GetPivotFieldConfig(req.ReportViewId, login, ct)
                .ConfigureAwait(false)).ToList();
            var rawRows = GetDataSource(req, login, ct, preloaded);
            var pivot = await _dataPivotEngine
                .TransformAsync(rawRows, fieldConfig, ct: ct)
                .ConfigureAwait(false);
            return (pivot, fieldConfig);
        }

        public Task<object> ExcelExport(
                ReportCallingDTO ReportCallingDTO,
                LoginDTO LoginDTO,
                CancellationToken cancellationToken)
            => ExcelExportCore(ReportCallingDTO, LoginDTO, null, cancellationToken);

        public Task<object> ExcelExport(
                ReportCallingDTO ReportCallingDTO,
                LoginDTO LoginDTO,
                IAsyncEnumerable<Dictionary<string, object?>> data,
                CancellationToken cancellationToken)
            => ExcelExportCore(ReportCallingDTO, LoginDTO, data, cancellationToken);

        private async Task<object> ExcelExportCore(
                ReportCallingDTO ReportCallingDTO,
                LoginDTO LoginDTO,
                IAsyncEnumerable<Dictionary<string, object?>>? preloaded,
                CancellationToken cancellationToken)
        {
            var meta = await GetReportViewMeta(ReportCallingDTO.ReportViewId, LoginDTO, cancellationToken)
                .ConfigureAwait(false);

            if (meta.ReportViewType == (int)FrameworkEnumDTO.ReportViewType.Pivot)
            {
                var reportStandardDTO = await FillReportStandardDTO(
                    LoginDTO,
                    ReportCallingDTO.CriteriaDTO,
                    ReportCallingDTO.ReportFormatId,
                    ReportCallingDTO.ReportId,
                    ReportCallingDTO.ReportViewId).ConfigureAwait(false);

                reportStandardDTO.ReportTitle = await ReportTitle(ReportCallingDTO.ReportTitle, ReportCallingDTO.CriteriaDTO!, LoginDTO).ConfigureAwait(false);
                reportStandardDTO.ReportTitleOptional = await ReportTitleOptional(ReportCallingDTO.CriteriaDTO!, LoginDTO).ConfigureAwait(false);
                reportStandardDTO.ReportHeader.ReportTitle = reportStandardDTO.ReportTitle;
                reportStandardDTO.ReportHeader.ReportTitleOptional = reportStandardDTO.ReportTitleOptional;

                var headers = (await IExcelExport
                    .GetReportViewHeader(ReportCallingDTO.ReportViewId, ReportCallingDTO, LoginDTO))
                    .ToList();

                foreach (var h in headers)
                    h.Expression = await ReplaceExcelHeaderPlaceholders(h.Expression, reportStandardDTO, LoginDTO).ConfigureAwait(false);

                var (pivot, _) = await BuildPivotAsync(ReportCallingDTO, LoginDTO, cancellationToken, preloaded).ConfigureAwait(false);
                return await _pivotExcelExport.ExportAsync(pivot, reportStandardDTO, headers, LoginDTO, cancellationToken).ConfigureAwait(false);
            }

            var flatHeaders = (await IExcelExport
                .GetReportViewHeader(ReportCallingDTO.ReportViewId, ReportCallingDTO, LoginDTO))
                .ToList();
            var flatReportStandardDTO = await FillReportStandardDTO(
                LoginDTO,
                ReportCallingDTO.CriteriaDTO,
                ReportCallingDTO.ReportFormatId,
                ReportCallingDTO.ReportId,
                ReportCallingDTO.ReportViewId);

            flatReportStandardDTO.ReportTitle = await ReportTitle(ReportCallingDTO.ReportTitle, ReportCallingDTO.CriteriaDTO!, LoginDTO);
            flatReportStandardDTO.ReportTitleOptional = await ReportTitleOptional(ReportCallingDTO.CriteriaDTO!, LoginDTO);
            flatReportStandardDTO.ReportHeader.ReportTitle = flatReportStandardDTO.ReportTitle;
            flatReportStandardDTO.ReportHeader.ReportTitleOptional = flatReportStandardDTO.ReportTitleOptional;

            foreach (var h in flatHeaders)
                h.Expression = await ReplaceExcelHeaderPlaceholders(h.Expression, flatReportStandardDTO, LoginDTO);

            // Always fetch the FULL field catalog (FieldIsDisplay=1), regardless of what the caller
            // sent — a hidden (ISDISPLAY-suppressed) field can still be flagged as a group/subtotal/
            // grandtotal/merge key, and ExcelExport.Export needs to see it via reportViewFields to
            // build correct grouping even though it's excluded from the visible column list below.
            var AllReportViewFieldsDTOs = await GetReportViewField(ReportCallingDTO.ReportViewId, 1, LoginDTO);
            var ReportViewFieldsDTOs = AllReportViewFieldsDTOs
                .Where(f => f.ReportViewFieldsIsDisplay == 0)
                .OrderBy(f => f.ReportViewFieldsDisplaySlNo)
                .ToList();
            var ColumnTitles = ReportViewFieldsDTOs.Select(f => f.ReportViewFieldsFieldTitle).ToList();
            var ColumnFieldNames = ReportViewFieldsDTOs.Select(f => f.ReportVsFieldsFieldName).ToList();

            await using var ms = new MemoryStream();

            await IExcelExport.Export(
                      GetDataSource(ReportCallingDTO, LoginDTO, cancellationToken, preloaded)
                    , flatHeaders.ToAsyncEnumerable(), ColumnTitles.ToAsyncEnumerable(), ColumnFieldNames.ToAsyncEnumerable(), ms, LoginDTO, "Report", cancellationToken, AllReportViewFieldsDTOs.ToList());

            return ms.ToArray();
        }
        // -----------------------------
        // NATIVE PDF EXPORT (PdfSharpCore, no browser) — an additional, explicit option
        // (ReportFormat.PdfNative) alongside PdfExport above, not a replacement for it. Thin wrapper,
        // same shape as ExcelExportCore — pivot views are not supported in this pass (matches the
        // Phase 11 spike's own scope; pivot's cross-tab shape doesn't have a natural row-group tree the
        // way flat reports do).
        // -----------------------------
        public Task<object> PdfNativeExport(ReportCallingDTO reportCallingDTO, LoginDTO loginDTO, CancellationToken cancellationToken)
            => PdfNativeExportCore(reportCallingDTO, loginDTO, null, cancellationToken);

        public Task<object> PdfNativeExport(ReportCallingDTO reportCallingDTO, LoginDTO loginDTO, IAsyncEnumerable<Dictionary<string, object?>> data, CancellationToken cancellationToken)
            => PdfNativeExportCore(reportCallingDTO, loginDTO, data, cancellationToken);

        private async Task<object> PdfNativeExportCore(
                ReportCallingDTO reportCallingDTO,
                LoginDTO loginDTO,
                IAsyncEnumerable<Dictionary<string, object?>>? preloaded,
                CancellationToken cancellationToken)
        {
            var meta = await GetReportViewMeta(reportCallingDTO.ReportViewId, loginDTO, cancellationToken).ConfigureAwait(false);

            if (meta.ReportViewType == (int)FrameworkEnumDTO.ReportViewType.Pivot)
                throw new NotSupportedException("Native PDF export does not support pivot views — use PDF/Excel/CSV for pivot reports.");

            var headers = (await IExcelExport
                .GetReportViewHeader(reportCallingDTO.ReportViewId, reportCallingDTO, loginDTO))
                .ToList();
            var reportStandardDTO = await FillReportStandardDTO(
                loginDTO, reportCallingDTO.CriteriaDTO, reportCallingDTO.ReportFormatId,
                reportCallingDTO.ReportId, reportCallingDTO.ReportViewId);

            reportStandardDTO.ReportTitle = await ReportTitle(reportCallingDTO.ReportTitle, reportCallingDTO.CriteriaDTO!, loginDTO);
            reportStandardDTO.ReportTitleOptional = await ReportTitleOptional(reportCallingDTO.CriteriaDTO!, loginDTO);
            reportStandardDTO.ReportHeader.ReportTitle = reportStandardDTO.ReportTitle;
            reportStandardDTO.ReportHeader.ReportTitleOptional = reportStandardDTO.ReportTitleOptional;

            foreach (var h in headers)
                h.Expression = await ReplaceExcelHeaderPlaceholders(h.Expression, reportStandardDTO, loginDTO);

            // Same static-bundled-logo mechanism the existing Puppeteer path already uses (see
            // HtmlExportCore/PdfExportCore) — reused as-is rather than base64-encoded into a data URI,
            // since XImage.FromStream wants raw bytes directly. Wiring up real per-company/per-OU
            // DB-driven logos is a separate, later phase (see Phase 12's plan notes) — this matches
            // what the current pipeline actually shows customers today, not a permanent design choice.
            byte[]? logoBytes = null;
            var pdfConfig = _configuration.GetSection("PDFEXPORT");
            var logoPath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("LogoPath"), ReportTemplateResolver.LogoPath);
            if (!string.IsNullOrWhiteSpace(logoPath) && File.Exists(logoPath))
                logoBytes = await File.ReadAllBytesAsync(logoPath, cancellationToken);

            // Always fetch the FULL field catalog — see the identical comment in ExcelExportCore.
            var allReportViewFieldsDTOs = await GetReportViewField(reportCallingDTO.ReportViewId, 1, loginDTO);
            var reportViewFieldsDTOs = allReportViewFieldsDTOs
                .Where(f => f.ReportViewFieldsIsDisplay == 0)
                .OrderBy(f => f.ReportViewFieldsDisplaySlNo)
                .ToList();
            var columnTitles = reportViewFieldsDTOs.Select(f => f.ReportViewFieldsFieldTitle).ToList();
            var columnFieldNames = reportViewFieldsDTOs.Select(f => f.ReportVsFieldsFieldName).ToList();

            await using var ms = new MemoryStream();

            await _nativePdfExport.Export(
                GetDataSource(reportCallingDTO, loginDTO, cancellationToken, preloaded),
                headers.ToAsyncEnumerable(), columnTitles.ToAsyncEnumerable(), columnFieldNames.ToAsyncEnumerable(),
                ms, loginDTO, logoBytes, cancellationToken, allReportViewFieldsDTOs.ToList(),
                meta.ReportViewPageFormat, meta.ReportViewPageOrientation);

            return ms.ToArray();
        }

        public Task<object> CSVExport(
               ReportCallingDTO ReportCallingDTO,
               LoginDTO LoginDTO,
               CancellationToken cancellationToken)
            => CSVExportCore(ReportCallingDTO, LoginDTO, null, cancellationToken);

        public Task<object> CSVExport(
               ReportCallingDTO ReportCallingDTO,
               LoginDTO LoginDTO,
               IAsyncEnumerable<Dictionary<string, object?>> data,
               CancellationToken cancellationToken)
            => CSVExportCore(ReportCallingDTO, LoginDTO, data, cancellationToken);

        private async Task<object> CSVExportCore(
               ReportCallingDTO ReportCallingDTO,
               LoginDTO LoginDTO,
               IAsyncEnumerable<Dictionary<string, object?>>? preloaded,
               CancellationToken cancellationToken)
        {
            var meta = await GetReportViewMeta(ReportCallingDTO.ReportViewId, LoginDTO, cancellationToken)
                .ConfigureAwait(false);

            if (meta.ReportViewType == (int)FrameworkEnumDTO.ReportViewType.Pivot)
                throw new NotSupportedException("CSV export is not supported for pivot views. Use Excel or PDF instead.");

            var headers = (await IExcelExport
                .GetReportViewHeader(ReportCallingDTO.ReportViewId, ReportCallingDTO, LoginDTO))
                .ToList();
            var reportStandardDTO = await FillReportStandardDTO(
                LoginDTO,
                ReportCallingDTO.CriteriaDTO,
                ReportCallingDTO.ReportFormatId,
                ReportCallingDTO.ReportId,
                ReportCallingDTO.ReportViewId);

            reportStandardDTO.ReportTitle = await ReportTitle(ReportCallingDTO.ReportTitle, ReportCallingDTO.CriteriaDTO!, LoginDTO);
            reportStandardDTO.ReportTitleOptional = await ReportTitleOptional(ReportCallingDTO.CriteriaDTO!, LoginDTO);
            reportStandardDTO.ReportHeader.ReportTitle = reportStandardDTO.ReportTitle;
            reportStandardDTO.ReportHeader.ReportTitleOptional = reportStandardDTO.ReportTitleOptional;

            foreach (var h in headers)
                h.Expression = await ReplaceExcelHeaderPlaceholders(h.Expression, reportStandardDTO, LoginDTO);

            var ReportViewFieldsDTOs = await GetReportViewField(ReportCallingDTO.ReportViewId, ReportCallingDTO.FieldIsDisplay, LoginDTO);
            var ColumnTitles = ReportViewFieldsDTOs.Select(f => f.ReportViewFieldsFieldTitle).ToList();
            var ColumnFieldNames = ReportViewFieldsDTOs.Select(f => f.ReportVsFieldsFieldName).ToList();

            await using var ms = new MemoryStream();

            await _CSVExport.Export(
                      GetDataSource(ReportCallingDTO, LoginDTO, cancellationToken, preloaded)
                    , headers.ToAsyncEnumerable(), ColumnTitles.ToAsyncEnumerable(), ColumnFieldNames.ToAsyncEnumerable(), ms, "Report", cancellationToken, ReportViewFieldsDTOs.ToList());

            return ms.ToArray();
        }

        public Task<string> JsonReport(
                ReportCallingDTO ReportCallingDTO,
                LoginDTO LoginDTO,
                CancellationToken cancellationToken)
            => JsonReportCore(ReportCallingDTO, LoginDTO, null, cancellationToken);

        public Task<string> JsonReport(
                ReportCallingDTO ReportCallingDTO,
                LoginDTO LoginDTO,
                IAsyncEnumerable<Dictionary<string, object?>> data,
                CancellationToken cancellationToken)
            => JsonReportCore(ReportCallingDTO, LoginDTO, data, cancellationToken);

        private async Task<string> JsonReportCore(
                ReportCallingDTO ReportCallingDTO,
                LoginDTO LoginDTO,
                IAsyncEnumerable<Dictionary<string, object?>>? preloaded,
                CancellationToken cancellationToken)
        {
            var meta = await GetReportViewMeta(ReportCallingDTO.ReportViewId, LoginDTO, cancellationToken)
                .ConfigureAwait(false);

            if (meta.ReportViewType == (int)FrameworkEnumDTO.ReportViewType.Pivot)
            {
                if (ReportCallingDTO.IsPivotView == 0)
                {
                    var fieldConfig = (await GetPivotFieldConfig(ReportCallingDTO.ReportViewId, LoginDTO, cancellationToken)
                        .ConfigureAwait(false)).ToList();

                    var allRows = new List<Dictionary<string, object?>>();
                    await foreach (var row in GetDataSource(ReportCallingDTO, LoginDTO, cancellationToken, preloaded)
                        .ConfigureAwait(false))
                        allRows.Add(row);

                    return Newtonsoft.Json.JsonConvert.SerializeObject(new { fields = fieldConfig, rows = allRows });
                }
                else
                {
                    var (pivot, _) = await BuildPivotAsync(ReportCallingDTO, LoginDTO, cancellationToken, preloaded)
                        .ConfigureAwait(false);
                    return Newtonsoft.Json.JsonConvert.SerializeObject(new
                    {
                        rowHeaders = pivot.RowHeaders,
                        pivotColumns = pivot.PivotColumns,
                        rows = pivot.Rows,
                        grandTotal = pivot.GrandTotal
                    });
                }
            }

            try
            {
                var Result = GetDataSource(ReportCallingDTO, LoginDTO, cancellationToken, preloaded);
                var list = new List<Dictionary<string, object?>>();
                await foreach (var row in Result.WithCancellation(cancellationToken).ConfigureAwait(false))
                    list.Add(row);
                return Newtonsoft.Json.JsonConvert.SerializeObject(list);
            }
            catch (Exception)
            {
                throw;
            }
        }

        // -----------------------------
        // NEW: PDF EXPORT (full)
        // -----------------------------
        public Task<object> PdfExport(ReportCallingDTO reportCallingDTO, LoginDTO loginDTO, CancellationToken cancellationToken)
            => PdfExportCore(reportCallingDTO, loginDTO, null, cancellationToken);

        public Task<object> PdfExport(ReportCallingDTO reportCallingDTO, LoginDTO loginDTO, IAsyncEnumerable<Dictionary<string, object?>> data, CancellationToken cancellationToken)
            => PdfExportCore(reportCallingDTO, loginDTO, data, cancellationToken);

        // -----------------------------
        // HTML EXPORT — a read-only, browser-viewable alternative to the interactive Grid, sharing
        // the exact same rendering pipeline (template, grouping, Sub Total/Grand Total) as PDF export
        // instead of re-deriving grouping/totals from raw rows client-side. Unlike PdfExportCore, this
        // is NOT chunked — the whole report is held in memory and rendered as one HTML page. That's
        // appropriate for an interactive/on-screen view (reports intended for casual browsing, not
        // 200k-row bulk exports); large exports should keep using PDF/Excel/CSV.
        // -----------------------------
        public Task<object> HtmlExport(ReportCallingDTO reportCallingDTO, LoginDTO loginDTO, CancellationToken cancellationToken)
            => HtmlExportCore(reportCallingDTO, loginDTO, null, cancellationToken);

        public Task<object> HtmlExport(ReportCallingDTO reportCallingDTO, LoginDTO loginDTO, IAsyncEnumerable<Dictionary<string, object?>> data, CancellationToken cancellationToken)
            => HtmlExportCore(reportCallingDTO, loginDTO, data, cancellationToken);

        private async Task<object> HtmlExportCore(ReportCallingDTO reportCallingDTO, LoginDTO loginDTO, IAsyncEnumerable<Dictionary<string, object?>>? preloaded, CancellationToken cancellationToken)
        {
            var meta = await GetReportViewMeta(reportCallingDTO.ReportViewId, loginDTO, cancellationToken).ConfigureAwait(false);

            if (meta.ReportViewType == (int)FrameworkEnumDTO.ReportViewType.Pivot)
                throw new NotSupportedException("HTML export does not support pivot views yet — use PDF/Excel/CSV for pivot reports.");

            // Mirrors PdfExportCore's flat-path setup (Header & Standard Info, columns/metadata,
            // Grand Total aggregator setup) — see that method's comments for what each step does.
            var reportStandardDTO = await FillReportStandardDTO(
                loginDTO, reportCallingDTO.CriteriaDTO, reportCallingDTO.ReportFormatId,
                reportCallingDTO.ReportId, reportCallingDTO.ReportViewId);

            reportStandardDTO.ReportViewPageFormat = meta.ReportViewPageFormat;
            reportStandardDTO.ReportViewPageOrientation = meta.ReportViewPageOrientation;
            reportStandardDTO.ReportTitle = await ReportTitle(reportCallingDTO.ReportTitle, reportCallingDTO.CriteriaDTO!, loginDTO);
            reportStandardDTO.ReportTitleOptional = await ReportTitleOptional(reportCallingDTO.CriteriaDTO!, loginDTO);
            reportStandardDTO.ReportHeader.ReportTitle = reportStandardDTO.ReportTitle;

            var pdfConfig = _configuration.GetSection("PDFEXPORT");
            var logoPath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("LogoPath"), ReportTemplateResolver.LogoPath);
            if (!string.IsNullOrWhiteSpace(logoPath) && System.IO.File.Exists(logoPath))
            {
                var bytes = await System.IO.File.ReadAllBytesAsync(logoPath, cancellationToken);
                reportStandardDTO.OULogo1 = $"data:image/png;base64,{Convert.ToBase64String(bytes)}";
                reportStandardDTO.ReportHeader.IsPrintLogo = true;
            }

            // Always fetch the FULL field catalog (FieldIsDisplay=1) — a hidden (ISDISPLAY-suppressed)
            // field can still be a group/subtotal/grandtotal key that BuildGroupNodes/aggregation below
            // need to find BY TITLE on each row; pdfColumnHeaders (drives newRow population) must see
            // it even though visibleHeaders (drives what's actually rendered as a column) must not.
            var reportViewFields = await GetReportViewField(reportCallingDTO.ReportViewId, 1, loginDTO);

            // Array/sub-row groups (see ReportSubRowResolver) — a RowLevel>0 field with a dotted
            // FieldName ("VoucherInstrumentArray.InstrumentNumber") belongs to a repeating group
            // expanded from the row's own array property, not a scalar main-row cell. Built once,
            // resolved per row below.
            var subRowRootGroups = ReportSubRowResolver.BuildGroupTree(reportViewFields);
            var subRowFieldTitles = new HashSet<string>(
                reportViewFields.Where(f => f.ReportViewFieldsRowLevel > 0
                                             && !string.IsNullOrWhiteSpace(f.ReportVsFieldsFieldName)
                                             && f.ReportVsFieldsFieldName.Contains('.'))
                                .Select(f => f.ReportViewFieldsFieldTitle),
                StringComparer.OrdinalIgnoreCase);

            var pdfColumnHeaders = reportViewFields.Select(f => f.ReportViewFieldsFieldTitle)
                .Where(h => !subRowFieldTitles.Contains(h)).ToList();
            var fieldMap = reportViewFields.ToDictionary(f => f.ReportViewFieldsFieldTitle, f => f.ReportVsFieldsFieldName);
            var visibleHeaders = reportViewFields
                .Where(f => f.ReportViewFieldsIsDisplay == 0 && !subRowFieldTitles.Contains(f.ReportViewFieldsFieldTitle))
                .Select(f => f.ReportViewFieldsFieldTitle)
                .ToList();

            string templatePath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("DefaultView"), ReportTemplateResolver.DefaultView);
            string headerPath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("headerHTML"), ReportTemplateResolver.HeaderHtml);
            string footerPath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("footerHTML"), ReportTemplateResolver.FooterHtml);

            var grandTotalAggregators = ResolveTotalFields(reportViewFields, f => f.ReportViewFieldsIsGrandTotal)
                .ToDictionary(
                    f => f.ReportViewFieldsFieldTitle,
                    f => new ColumnAggregator(f.ReportViewFieldsAggregationType, f.ReportVsFieldsFieldType),
                    StringComparer.OrdinalIgnoreCase);
            var subTotalFieldTitles = new HashSet<string>(
                ResolveTotalFields(reportViewFields, f => f.ReportViewFieldsIsSubTotal).Select(f => f.ReportViewFieldsFieldTitle),
                StringComparer.OrdinalIgnoreCase);

            // No chunking — every row goes into one list, rendered as a single page.
            var allRows = new List<Dictionary<string, object>>();
            await foreach (var record in GetDataSource(reportCallingDTO, loginDTO, cancellationToken, preloaded))
            {
                cancellationToken.ThrowIfCancellationRequested();

                var newRow = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                newRow["__rowId"] = Guid.NewGuid().ToString();
                newRow["__rawRecord"] = record;

                foreach (var header in pdfColumnHeaders)
                {
                    var fieldName = fieldMap[header];
                    var fieldMeta = reportViewFields.FirstOrDefault(f => f.ReportViewFieldsFieldTitle == header);
                    object? rawValue = GetDynamicFieldValue(record, fieldName);
                    newRow[header] = FormatFieldByType(rawValue, fieldMeta) ?? "";

                    if (fieldMeta?.ReportViewFieldsSuppressIfEmpty == 0 && IsZeroOrEmpty(newRow[header]?.ToString()))
                        newRow[header] = "";

                    if (grandTotalAggregators.TryGetValue(header, out var grandTotalAgg))
                        grandTotalAgg.Accumulate(rawValue);

                    if (subTotalFieldTitles.Contains(header))
                        newRow[$"__raw_{header}"] = rawValue!;
                }

                newRow["__subRowGroups"] = ReportSubRowResolver.ResolveForRow(subRowRootGroups, record, FormatFieldByType);

                allRows.Add(newRow);
            }

            Dictionary<string, object?>? grandTotalDict = grandTotalAggregators.Count > 0
                ? PadWithNullsForHeaders(
                    grandTotalAggregators.ToDictionary(kvp => kvp.Key, kvp => kvp.Value.Result, StringComparer.OrdinalIgnoreCase),
                    visibleHeaders)
                : null;

            // grandTotal is passed alongside the full row set (not as a separate empty-rows call like
            // PdfExportCore's trailing totals-only chunk) — the template renders the data table AND,
            // when GrandTotal is present outside the dedicated IsFinalTotalsChunk branch, appends the
            // Grand Total table right after it on the same page.
            var (html, headerHtml, footerHtml) = await BuildChunkHtmlAsync(
                allRows, templatePath, headerPath, footerPath, visibleHeaders, fieldMap,
                reportStandardDTO, reportCallingDTO, loginDTO, reportViewFields, cancellationToken, grandTotalDict);

            // headerHtml/footerHtml (company logo/name/branch, "Generated on" timestamp) are normally
            // handed to Puppeteer as headerTemplate/footerTemplate and rendered as a repeating page
            // header/footer (see RenderChunkToPdfFileAsync) — there is no equivalent mechanism for a
            // plain browser-rendered HTML page, so without this splice the standalone HTML view was
            // silently missing the company header/footer entirely, even though the PDF export (which
            // shares this exact same html/headerHtml/footerHtml) shows them correctly. Puppeteer-only
            // bits inside footerHtml (the pageNumber/totalPages spans) render blank here, which is
            // expected — a single continuous HTML page has no per-page pagination to report.
            int bodyTagStart = html.IndexOf("<body", StringComparison.OrdinalIgnoreCase);
            int bodyOpenEnd = bodyTagStart >= 0 ? html.IndexOf('>', bodyTagStart) : -1;
            int bodyCloseStart = html.LastIndexOf("</body>", StringComparison.OrdinalIgnoreCase);
            if (bodyOpenEnd > 0 && bodyCloseStart > bodyOpenEnd)
            {
                html = html[..(bodyOpenEnd + 1)]
                    + $"<div class=\"standalone-report-header\">{headerHtml}</div>"
                    + html[(bodyOpenEnd + 1)..bodyCloseStart]
                    + $"<div class=\"standalone-report-footer\">{footerHtml}</div>"
                    + html[bodyCloseStart..];
            }

            return System.Text.Encoding.UTF8.GetBytes(html);
        }

        private async Task<object> PdfExportCore(ReportCallingDTO reportCallingDTO, LoginDTO loginDTO, IAsyncEnumerable<Dictionary<string, object?>>? preloaded, CancellationToken cancellationToken)
        {
            var meta = await
                 GetReportViewMeta(reportCallingDTO.ReportViewId, loginDTO, cancellationToken)
                .ConfigureAwait(false);

            if (meta.ReportViewType == (int)FrameworkEnumDTO.ReportViewType.Pivot)
            {
                var reportStandardDTO = await FillReportStandardDTO(
                    loginDTO,
                    reportCallingDTO.CriteriaDTO,
                    reportCallingDTO.ReportFormatId,
                    reportCallingDTO.ReportId,
                    reportCallingDTO.ReportViewId).ConfigureAwait(false);

                reportStandardDTO.ReportViewPageFormat = meta.ReportViewPageFormat;
                reportStandardDTO.ReportViewPageOrientation = meta.ReportViewPageOrientation;

                reportStandardDTO.ReportTitle = await ReportTitle(reportCallingDTO.ReportTitle, reportCallingDTO.CriteriaDTO!, loginDTO).ConfigureAwait(false);
                reportStandardDTO.ReportTitleOptional = await ReportTitleOptional(reportCallingDTO.CriteriaDTO!, loginDTO).ConfigureAwait(false);
                reportStandardDTO.ReportHeader.ReportTitle = reportStandardDTO.ReportTitle;

                var pdfCfg = _configuration.GetSection("PDFEXPORT");
                string templatePath = ResolveConfiguredOrBundled(pdfCfg.GetValue<string>("PivotView"), ReportTemplateResolver.PivotView);

                var (pivot, _) = await BuildPivotAsync(reportCallingDTO, loginDTO, cancellationToken, preloaded).ConfigureAwait(false);
                return await _pivotPdfExport.ExportAsync(pivot, reportStandardDTO, templatePath, cancellationToken).ConfigureAwait(false);
            }

            // ↓ Existing flat-report PDF path — untouched
            // Each numbered phase below gets its own child span so a still-in-flight PDF export
            // shows real progress in Zipkin (header built, columns resolved, then one span per
            // rendered chunk) instead of one giant span that only appears once the whole export
            // finishes. Declared here (not `using var` inside the try) so the outer finally can
            // always close whichever phase span was still open if an exception cuts a phase short
            // — Activity.Dispose() is safe to call twice, so this doesn't conflict with the normal
            // per-phase Dispose() calls on the success path below.
            System.Diagnostics.Activity? headerSection = null;
            System.Diagnostics.Activity? metadataSection = null;
            System.Diagnostics.Activity? streamAndChunkSection = null;
            try
            {
                headerSection = GB5Trace.BeginSection("pdf:header-and-logo");
                // ===== 1. Header & Standard Info =====
                var headers = (await IExcelExport
                    .GetReportViewHeader(reportCallingDTO.ReportViewId, reportCallingDTO, loginDTO))
                    .ToList();

                var reportStandardDTO = await FillReportStandardDTO(
                    loginDTO,
                    reportCallingDTO.CriteriaDTO,
                    reportCallingDTO.ReportFormatId,
                    reportCallingDTO.ReportId,
                    reportCallingDTO.ReportViewId);

                reportStandardDTO.ReportViewPageFormat = meta.ReportViewPageFormat;
                reportStandardDTO.ReportViewPageOrientation = meta.ReportViewPageOrientation;

                reportStandardDTO.ReportTitle = await ReportTitle(reportCallingDTO.ReportTitle, reportCallingDTO.CriteriaDTO!, loginDTO);
                reportStandardDTO.ReportTitleOptional = await ReportTitleOptional(reportCallingDTO.CriteriaDTO!, loginDTO);
                reportStandardDTO.ReportHeader.ReportTitle = reportStandardDTO.ReportTitle;

                // ===== 2. Load Logo (optional) =====
                var pdfConfig = _configuration.GetSection("PDFEXPORT");
                var logoPath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("LogoPath"), ReportTemplateResolver.LogoPath);
                if (!string.IsNullOrWhiteSpace(logoPath) && System.IO.File.Exists(logoPath))
                {
                    var bytes = await System.IO.File.ReadAllBytesAsync(logoPath, cancellationToken);
                    reportStandardDTO.OULogo1 = $"data:image/png;base64,{Convert.ToBase64String(bytes)}";
                    reportStandardDTO.ReportHeader.IsPrintLogo = true;
                }

                headerSection?.Dispose();

                // ===== 3. Dynamic columns and metadata =====
                metadataSection = GB5Trace.BeginSection("pdf:load-metadata");
                // Always fetch the FULL field catalog — see the identical comment in HtmlExportCore.
                var reportViewFields = await  GetReportViewField(reportCallingDTO.ReportViewId, 1, loginDTO);

                // Array/sub-row groups (see ReportSubRowResolver) — same convention as the flat
                // (non-chunked) export path above.
                var subRowRootGroups = ReportSubRowResolver.BuildGroupTree(reportViewFields);
                var subRowFieldTitles = new HashSet<string>(
                    reportViewFields.Where(f => f.ReportViewFieldsRowLevel > 0
                                                 && !string.IsNullOrWhiteSpace(f.ReportVsFieldsFieldName)
                                                 && f.ReportVsFieldsFieldName.Contains('.'))
                                    .Select(f => f.ReportViewFieldsFieldTitle),
                    StringComparer.OrdinalIgnoreCase);

                var pdfColumnHeaders = reportViewFields.Select(f => f.ReportViewFieldsFieldTitle)
                    .Where(h => !subRowFieldTitles.Contains(h)).ToList();
                var fieldMap = reportViewFields.ToDictionary(f => f.ReportViewFieldsFieldTitle, f => f.ReportVsFieldsFieldName);

                // Grand total spans every chunk, so it's accumulated here rather than per-chunk.
                var grandTotalAggregators = ResolveTotalFields(reportViewFields, f => f.ReportViewFieldsIsGrandTotal)
                    .ToDictionary(
                        f => f.ReportViewFieldsFieldTitle,
                        f => new ColumnAggregator(f.ReportViewFieldsAggregationType, f.ReportVsFieldsFieldType),
                        StringComparer.OrdinalIgnoreCase);

                // Subtotal is computed per-chunk (grouping is already chunk-local) — preserve each
                // subtotal-flagged column's raw (pre-format) value on the row for aggregation there.
                var subTotalFieldTitles = new HashSet<string>(
                    ResolveTotalFields(reportViewFields, f => f.ReportViewFieldsIsSubTotal).Select(f => f.ReportViewFieldsFieldTitle),
                    StringComparer.OrdinalIgnoreCase);

                metadataSection?.Dispose();

                // ===== 4. Stream rows from source and build chunk-friendly rows =====
                // Instead of loading ALL rows into memory, we accumulate rows per chunk file.
                streamAndChunkSection = GB5Trace.BeginSection("pdf:stream-and-dispatch-chunks");
                var tempPdfPaths = new List<string>();
                string outputDir = pdfConfig.GetValue<string>("OutputPath") ?? Path.Combine(Path.GetTempPath(), "pdf-output");
                Directory.CreateDirectory(outputDir);

                string templatePath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("DefaultView"), ReportTemplateResolver.DefaultView);
                string headerPath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("headerHTML"), ReportTemplateResolver.HeaderHtml);
                string footerPath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("footerHTML"), ReportTemplateResolver.FooterHtml);

                // chunk settings - tune as needed. For 200k rows, 6k-10k is safe depending on row complexity.
                int rowsPerChunk = pdfConfig.GetValue<int?>("RowsPerChunk") ?? 8000;

                // We'll collect rows into a small list and when reaching rowsPerChunk render to PDF chunk file.
                var chunkRows = new List<Dictionary<string, object>>(capacity: rowsPerChunk);
                int overallRowCount = 0;
                int chunkIndex = 0;

                // Precompute constant things used in each chunk
                var visibleHeaders = reportViewFields
                    .Where(f => f.ReportViewFieldsIsDisplay == 0 && !subRowFieldTitles.Contains(f.ReportViewFieldsFieldTitle))
                    .Select(f => f.ReportViewFieldsFieldTitle)
                    .ToList();

                // We'll gather text length and widths incrementally across a small sample (optional).
                // For simplicity we skip heavy width recomputation per chunk here; you can reuse your existing width calculation if required.

                // ── Chunk-boundary group-awareness. Protecting only the OUTERMOST group column's
                // boundary is sufficient even for multi-level grouping (see BuildGroupNodes in
                // RenderChunkToPdfFileAsync): as long as a chunk never splits one outer-level group's
                // rows across two chunks, every nested sub-level grouped from WITHIN that chunk's copy
                // of those rows is automatically complete too — there's nothing to split, since all of
                // an outer group's rows (and therefore all of its descendants' rows, at every level)
                // are guaranteed to live in exactly one chunk. This does assume rows arrive pre-sorted
                // by ALL group-column levels in order (not just the first) — true single-level grouping
                // already required sorting by the first column; multi-level extends that to the full
                // group-column order, which is the same requirement a correct GROUP BY-adjacent stream
                // needs regardless of chunking.
                var groupColumnsForChunking = reportViewFields
                    .Where(f => f.ReportViewFieldsIsGroupcolumn == 0)
                    .OrderBy(f => f.ReportViewFieldsDisplaySlNo)
                    .ToList();
                bool hasGroupingForChunking = groupColumnsForChunking.Count > 0;
                string? primaryGroupTitleForChunking = hasGroupingForChunking
                    ? groupColumnsForChunking.First().ReportViewFieldsFieldTitle
                    : null;
                object? lastGroupKeyInChunk = null;
                int hardCapRows = rowsPerChunk * 3; // safety net if one group vastly exceeds a chunk

                // Chunks render concurrently (bounded by GlobalBrowser.RenderGate) instead of one
                // at a time — dispatched here, awaited together (in dispatch order) right before
                // the merge step, so reading the next chunk's rows overlaps with rendering this one.
                var pendingChunkTasks = new List<Task<string>>();

                Task<string> FlushChunkAsync(List<Dictionary<string, object>> rows)
                {
                    cancellationToken.ThrowIfCancellationRequested();
                    return RenderChunkToPdfFileAsync(
                        rows,
                        chunkIndex++,
                        templatePath,
                        headerPath,
                        footerPath,
                        visibleHeaders,
                        fieldMap,
                        reportStandardDTO,
                        reportCallingDTO,
                        loginDTO,
                        outputDir,
                        reportViewFields,
                        cancellationToken);
                }

                // Pre-compute field-meta lookup once — avoids O(N*M) FirstOrDefault scan inside the
                // per-row × per-column loop; same dictionary used for every row in the export.
                var fieldMetaByTitle = reportViewFields
                    .GroupBy(f => f.ReportViewFieldsFieldTitle, StringComparer.OrdinalIgnoreCase)
                    .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);

                // Create async enumerator from streaming source
                await foreach (var record in GetDataSource(reportCallingDTO, loginDTO, cancellationToken, preloaded))
                {
                    cancellationToken.ThrowIfCancellationRequested();
                    overallRowCount++;

                    var newRow = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                    newRow["__rowId"] = Guid.NewGuid().ToString();
                    newRow["__rawRecord"] = record; // raw API record for direct path navigation in templates

                    foreach (var header in pdfColumnHeaders)
                    {
                        var fieldName = fieldMap[header];
                        fieldMetaByTitle.TryGetValue(header, out var fieldMeta);
                        object? rawValue = GetDynamicFieldValue(record, fieldName);
                        newRow[header] = FormatFieldByType(rawValue, fieldMeta) ?? "";

                        // SuppressIfEmpty: 0 = Yes — blank the cell when value is zero or empty
                        if (fieldMeta?.ReportViewFieldsSuppressIfEmpty == 0 &&
                            IsZeroOrEmpty(newRow[header]?.ToString()))
                            newRow[header] = "";

                        if (grandTotalAggregators.TryGetValue(header, out var grandTotalAgg))
                            grandTotalAgg.Accumulate(rawValue);

                        if (subTotalFieldTitles.Contains(header))
                            newRow[$"__raw_{header}"] = rawValue!;
                    }

                    newRow["__subRowGroups"] = ReportSubRowResolver.ResolveForRow(subRowRootGroups, record, FormatFieldByType);

                    // Decide (before adding this row) whether the chunk accumulated so far should be
                    // cut here — only when we're not mid-group (or the hard cap forces it), so a
                    // group's rows are never split across two rendered chunk files.
                    object? currentGroupKey = hasGroupingForChunking
                        ? (newRow.ContainsKey(primaryGroupTitleForChunking!) ? newRow[primaryGroupTitleForChunking!]?.ToString() ?? "" : "")
                        : null;
                    bool startingNewGroup = hasGroupingForChunking
                        && lastGroupKeyInChunk != null
                        && !Equals(currentGroupKey, lastGroupKeyInChunk);
                    bool overSoftLimit = chunkRows.Count >= rowsPerChunk;
                    bool overHardLimit = chunkRows.Count >= hardCapRows;

                    if (chunkRows.Count > 0 && (overHardLimit || (overSoftLimit && (!hasGroupingForChunking || startingNewGroup))))
                    {
                        pendingChunkTasks.Add(FlushChunkAsync(chunkRows));
                        chunkRows = new List<Dictionary<string, object>>(capacity: rowsPerChunk);
                    }

                    chunkRows.Add(newRow);
                    lastGroupKeyInChunk = currentGroupKey;
                }

                // final leftover chunk
                if (chunkRows.Count > 0)
                {
                    pendingChunkTasks.Add(FlushChunkAsync(chunkRows));
                }

                streamAndChunkSection?.SetTag("gb5.rows.count", overallRowCount);
                streamAndChunkSection?.SetTag("gb5.chunks.dispatched", pendingChunkTasks.Count);
                streamAndChunkSection?.Dispose();

                // ===== Grand total — one trailing page, accumulated across every chunk =====
                if (grandTotalAggregators.Count > 0)
                {
                    var grandTotalDict = PadWithNullsForHeaders(
                        grandTotalAggregators.ToDictionary(kvp => kvp.Key, kvp => kvp.Value.Result, StringComparer.OrdinalIgnoreCase),
                        visibleHeaders);

                    pendingChunkTasks.Add(RenderChunkToPdfFileAsync(
                        new List<Dictionary<string, object>>(),
                        chunkIndex++,
                        templatePath,
                        headerPath,
                        footerPath,
                        visibleHeaders,
                        fieldMap,
                        reportStandardDTO,
                        reportCallingDTO,
                        loginDTO,
                        outputDir,
                        reportViewFields,
                        cancellationToken,
                        grandTotalDict));
                }

                if (pendingChunkTasks.Count == 0)
                {
                    // no data produced: return empty PDF or a clear message file
                    throw new Exception("No rows returned from data source.");
                }

                // All chunks were dispatched above and render concurrently; wait for them together
                // (Task.WhenAll preserves input order, which matches chunkIndex/page order).
                var awaitChunksSection = GB5Trace.BeginSection("pdf:await-pending-chunks");
                try
                {
                    tempPdfPaths.AddRange(await Task.WhenAll(pendingChunkTasks).ConfigureAwait(false));
                }
                finally
                {
                    awaitChunksSection?.Dispose();
                }

                // ===== Merge parts into final PDF (streaming merge frees part files) =====
                byte[] finalPdf;
                var mergeSection = GB5Trace.BeginSection("pdf:merge-and-write");
                try
                {
                    finalPdf = MergePdfFilesAndCleanup(tempPdfPaths);

                    // Save final PDF to OutputPath
                    string finalFileName = Path.Combine(outputDir, $"reportpdf-{DateTime.UtcNow:yyyyMMddHHmmss}.pdf");
                    await System.IO.File.WriteAllBytesAsync(finalFileName, finalPdf, cancellationToken);
                }
                finally
                {
                    mergeSection?.Dispose();
                }

                // Return final bytes (or you may return file path if preferred)
                return finalPdf;
            }
            catch (OperationCanceledException)
            {
                throw; // propagate cancellation
            }
            catch (Exception ex)
            {
                // Keep stacktrace
                throw new Exception($"PDF Export failed: {ex.Message}", ex);
            }
            finally
            {
                // Normal path already disposes each of these as its phase finishes; this only
                // catches the case where an exception cut a phase short before its own Dispose().
                headerSection?.Dispose();
                metadataSection?.Dispose();
                streamAndChunkSection?.Dispose();
            }
        }
        // Legacy fallback: when NOT ONE column in a view has the real ISSUBTOTAL/ISGRANDTOTAL flag
        // set at all (the current state of every ledger-style report — nothing has been migrated to
        // the ColumnAggregator-driven config via gbreportviewsetting yet), auto-detect Debit/Credit
        // named numeric columns instead of showing no totals at all. This mirrors the same
        // "no config -> legacy behavior" fallback already used on the FE
        // (reportviewer.component.ts's hasGroupingConfig) — once ANY field in a view has the real
        // flag set, explicit config always wins outright; no partial mixing of heuristic + real flags.
        private static List<ReportViewFieldsDTO> ResolveTotalFields(
            List<ReportViewFieldsDTO> reportViewFields, Func<ReportViewFieldsDTO, int> flagSelector)
        {
            var flagged = reportViewFields.Where(f => flagSelector(f) == 0).ToList();
            if (flagged.Count > 0) return flagged;

            return reportViewFields
                .Where(f => (f.ReportVsFieldsFieldType == 0 || f.ReportVsFieldsFieldType == 6)
                         && ((f.ReportViewFieldsFieldTitle ?? "").Contains("debit", StringComparison.OrdinalIgnoreCase)
                          || (f.ReportViewFieldsFieldTitle ?? "").Contains("credit", StringComparison.OrdinalIgnoreCase)))
                .ToList();
        }

        // The template looks up {{lookup GrandTotal/SubTotal this}} for EVERY column header, not just
        // the flagged ones — a dictionary that only contains entries for flagged columns leaves every
        // other header an absent key, and HandlebarsDotNet's built-in "lookup" helper does not resolve
        // a missing dictionary key to null/blank the way a plain C# indexer would; it falls back to
        // returning the lookup KEY itself, which rendered literal header text ("Type", "Ref.No", ...)
        // into the Grand Total row's non-flagged cells instead of leaving them blank. Explicitly filling
        // every other header with a real null entry sidesteps that fallback entirely.
        private static Dictionary<string, object?> PadWithNullsForHeaders(
            Dictionary<string, object?> values, IEnumerable<string> allHeaders)
        {
            foreach (var header in allHeaders)
                if (!values.ContainsKey(header)) values[header] = null;
            return values;
        }

        private static object? GetDynamicFieldValue(object data, string fieldPath)
        {
            if (data == null || string.IsNullOrWhiteSpace(fieldPath))
                return null;

            object current = data;

            var parts = fieldPath.Split('.');

            foreach (var part in parts)
            {
                if (current == null)
                    return null;

                string property = part;
                int? index = null;

                // Handle array syntax Field[0]
                if (part.Contains("["))
                {
                    property = part.Substring(0, part.IndexOf("["));
                    var idx = part.Substring(part.IndexOf("[") + 1).Replace("]", "");

                    if (int.TryParse(idx, out int parsed))
                        index = parsed;
                }

                // 🔹 Handle Dictionary<string, object?> (nullable — from ToDictionary)
                if (current is IDictionary<string, object?> dictNullable)
                {
                    dictNullable.TryGetValue(property, out var nVal);
                    current = nVal!;
                }

                // 🔹 Handle Dictionary<string, object>
                else if (current is IDictionary<string, object> dict)
                {
                    dict.TryGetValue(property, out current!);
                }

                // 🔹 Handle JObject
                else if (current is Newtonsoft.Json.Linq.JObject jObj)
                {
                    current = jObj[property]!;
                }

                // 🔹 Handle normal C# object
                else
                {
                    var prop = current.GetType().GetProperty(property);

                    if (prop == null)
                        return null;

                    current = prop.GetValue(current)!;
                }

                if (current == null)
                    return null;

                // 🔹 Handle List index
                if (index.HasValue)
                {
                    if (current is IEnumerable list)
                    {
                        current = list.Cast<object>().ElementAtOrDefault(index.Value);
                    }
                }

                // 🔹 Auto parse JSON string
                if (current is string str && str.TrimStart().StartsWith("{"))
                {
                    try
                    {
                        current = JsonConvert.DeserializeObject<Dictionary<string, object>>(str);
                    }
                    catch
                    {
                        return str;
                    }
                }
            }

            return current;
        }

        // Builds the rendered report HTML for a set of rows — shared by the PDF path
        // (RenderChunkToPdfFileAsync, which feeds Html to Puppeteer and HeaderHtml/FooterHtml to its
        // print-header/footer metadata) and the plain HTML export path (HtmlExportCore, which only
        // needs Html). Everything here is template/context concerns only — no PDF-specific rendering
        // (Puppeteer, paper size, temp files) belongs in this method; that stays in the PDF caller.
        private async Task<(string Html, string HeaderHtml, string FooterHtml)> BuildChunkHtmlAsync(
           List<Dictionary<string, object>> rowsForChunk,
           string templatePath,
           string headerPath,
           string footerPath,
           List<string> visibleHeaders,
           Dictionary<string, string> fieldMap,
           object reportStandardDTO,
           ReportCallingDTO reportCallingDTO,
           LoginDTO loginDTO,
           List<ReportViewFieldsDTO> reportViewFields,
           CancellationToken ct,
           Dictionary<string, object?>? grandTotal = null)
        {
            var report = reportStandardDTO as ReportStandardDTO;

            // Drill-down (MDRILLDOWN/MDRILLDOWNDETAIL) — one lookup per export request (not per
            // row), then resolved against each row's actual data. See DrillDownLinkResolver for
            // why SourceField is matched against row data rather than a specific visible column.
            var drillDownTargets = await _drillDownLinkResolver.GetTargetsForMenuAsync(
                reportCallingDTO.MenuId, loginDTO, ct);
            if (drillDownTargets.Count > 0)
            {
                foreach (var row in rowsForChunk)
                {
                    row["DrillDownLinks"] = _drillDownLinkResolver.ResolveLinksForRow(
                        row, drillDownTargets, reportCallingDTO.CriteriaDTO?.SectionCriteriaList);
                }
            }

            // FieldMap exposed to templates as Dictionary<string,object?> so Handlebars helpers
            // can resolve values by API path (fieldMap[header] == "apiPath").
            // Keys = display column headers, Values = API field paths.
            var fieldMapCtx = fieldMap.ToDictionary(
                kvp => kvp.Key,
                kvp => (object?)kvp.Value,
                StringComparer.OrdinalIgnoreCase);

            // EmpInfo: only string(1) and date(3) type fields — excludes ints, decimals, and arrays.
            // Fields with no resolved value in the row are suppressed at render time by BuildFieldTable.
            var empInfoFieldList = reportViewFields
                .Where(f => f.ReportVsFieldsFieldType == 1 || f.ReportVsFieldsFieldType == 3)
                .OrderBy(f => f.ReportViewFieldsDisplaySlNo)
                .Select(f => (object)new Dictionary<string, object>
                {
                    ["Title"] = f.ReportViewFieldsFieldTitle ?? "",
                    ["Path"] = f.ReportVsFieldsFieldName ?? "",
                    ["Type"] = (int)f.ReportVsFieldsFieldType
                }).ToList();

            // AttFields: payslip-specific numeric fields (int/decimal that are hidden in list view)
            var attFieldList = reportViewFields
                .Where(f => (f.ReportVsFieldsFieldType == 0 || f.ReportVsFieldsFieldType == 6)
                            && f.ReportViewFieldsIsDisplay == 1)
                .OrderBy(f => f.ReportViewFieldsDisplaySlNo)
                .Select(f => (object)new Dictionary<string, object>
                {
                    ["Title"] = f.ReportViewFieldsFieldTitle ?? "",
                    ["Path"] = f.ReportVsFieldsFieldName ?? "",
                    ["Type"] = (int)f.ReportVsFieldsFieldType
                }).ToList();

            // Detect group columns: IsGroupcolumn == 0 means YES this field is a group key
            var groupColumns = reportViewFields
                .Where(f => f.ReportViewFieldsIsGroupcolumn == 0)
                .OrderBy(f => f.ReportViewFieldsDisplaySlNo)
                .ToList();

            bool hasGrouping = groupColumns.Count > 0;
            object? groups = null;

            // NonGroupHeaders: visible columns whose title is NOT a group key.
            // Used by the template as {{../NonGroupHeaders}} and {{../../NonGroupHeaders}}.
            var groupTitleSet = new HashSet<string>(
                groupColumns.Select(gc => gc.ReportViewFieldsFieldTitle ?? ""),
                StringComparer.OrdinalIgnoreCase);
            var nonGroupHeaders = visibleHeaders.Where(h => !groupTitleSet.Contains(h)).ToList();

            // Subtotal is computed per group, per chunk (grouping itself is already chunk-local).
            var subTotalFields = ResolveTotalFields(reportViewFields, f => f.ReportViewFieldsIsSubTotal);

            Dictionary<string, object?> ComputeSubTotal(IEnumerable<Dictionary<string, object>> groupRows)
            {
                var subTotal = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
                foreach (var f in subTotalFields)
                {
                    var agg = new ColumnAggregator(f.ReportViewFieldsAggregationType, f.ReportVsFieldsFieldType);
                    foreach (var row in groupRows)
                    {
                        row.TryGetValue($"__raw_{f.ReportViewFieldsFieldTitle}", out var v);
                        agg.Accumulate(v);
                    }
                    subTotal[f.ReportViewFieldsFieldTitle] = agg.Result;
                }
                return PadWithNullsForHeaders(subTotal, nonGroupHeaders);
            }

            // Recursive multi-level group tree — see GroupTreeBuilder.BuildGroupNodes for the shape.
            // A single group column (today's only live-configured case) produces a tree exactly one
            // level deep — same shape the old flat single-level code produced, just via the general path.
            if (hasGrouping)
            {
                groups = GroupTreeBuilder.BuildGroupNodes(
                    rowsForChunk,
                    groupColumns,
                    0,
                    groupRows => subTotalFields.Count > 0 ? ComputeSubTotal(groupRows) : null);
            }

            var chunkContext = new
            {
                Header = report?.ReportHeader,
                Footer = report?.ReportFooter,
                Report = reportStandardDTO,
                Criteria = reportCallingDTO.CriteriaDTO,
                ColumnHeaders = visibleHeaders,
                NonGroupHeaders = nonGroupHeaders,
                ColumnWidths = new Dictionary<string, double>(),
                Scale = 1.0,
                FieldMap = fieldMapCtx,
                Rows = rowsForChunk,
                Groups = groups,
                HasGrouping = hasGrouping,
                HasRowLevel = false,
                RowLevelCount = 0,
                RowLevelHeaders = (object?)null,
                FieldRowLevelMap = (object?)null,
                GeneratedOn = DateTime.UtcNow,
                User = loginDTO.UserName,
                EmpInfoFields = empInfoFieldList,
                AttFields = attFieldList,
                GrandTotal = grandTotal,
                // MDRILLDOWN/MDRILLDOWNDETAIL — true only when this menu has at least one target,
                // so reports with no drill-down configured (the vast majority) render with no extra
                // "Actions" column at all. Per-row resolution happens via each row's own
                // DrillDownLinks (set in BuildChunkHtmlAsync above), not here.
                HasDrillDown = drillDownTargets.Count > 0,
                // True only for the PDF path's dedicated trailing chunk (real rows == 0, just the
                // computed totals) — NOT simply "grandTotal is present", since HtmlExportCore passes a
                // populated grandTotal ALONGSIDE a full row set in one call. Using "grandTotal != null"
                // alone here made HtmlExportCore always take the grand-total-only exclusive branch,
                // silently dropping every data row from the HTML output.
                IsFinalTotalsChunk = grandTotal != null && rowsForChunk.Count == 0
            };

            // Load partial sources so {{> header}} and {{> footer}} resolve inside the main template
            string headerSource = System.IO.File.Exists(headerPath) ? await System.IO.File.ReadAllTextAsync(headerPath) : "<div></div>";
            string footerSource = System.IO.File.Exists(footerPath) ? await System.IO.File.ReadAllTextAsync(footerPath) : "<div></div>";
            var partials = new Dictionary<string, string>
            {
                { "header", headerSource },
                { "footer", footerSource }
            };

            // render html + header/footer
            string html = await RenderTemplateFromFileAsync(templatePath, chunkContext, partials);
            string headerHtml = System.IO.File.Exists(headerPath) ? await RenderTemplateFromFileAsync(headerPath, chunkContext) : "<div></div>";
            string footerHtml = System.IO.File.Exists(footerPath) ? await RenderTemplateFromFileAsync(footerPath, chunkContext) : "<div></div>";

            return (html, headerHtml, footerHtml);
        }

        // PDF-specific: builds the chunk HTML, then converts it to a PDF file via the shared browser.
        // Split out of BuildChunkHtmlAsync so the plain HTML export path doesn't pay for (or need)
        // Puppeteer, paper-size, or temp-file handling at all.
        private async Task<string> RenderChunkToPdfFileAsync(
           List<Dictionary<string, object>> rowsForChunk,
           int chunkIndex,
           string templatePath,
           string headerPath,
           string footerPath,
           List<string> visibleHeaders,
           Dictionary<string, string> fieldMap,
           object reportStandardDTO,
           ReportCallingDTO reportCallingDTO,
           LoginDTO loginDTO,
           string outputDir,
           List<ReportViewFieldsDTO> reportViewFields,
           CancellationToken cancellationToken,
           Dictionary<string, object?>? grandTotal = null)
        {
            // Chunks render concurrently (bounded by GlobalBrowser.RenderGate), each dispatched
            // as its own child span under whatever span was current when the chunk was queued
            // (pdf:stream-and-dispatch-chunks). They end independently as each chunk's PDF
            // finishes, so watching Zipkin during a large export shows chunk spans completing
            // one after another in real time instead of nothing until the whole export is done.
            using var chunkSection = GB5Trace.BeginSection($"pdf:render-chunk-{chunkIndex}");
            chunkSection?.SetTag("gb5.pdf.chunk_index", chunkIndex);
            chunkSection?.SetTag("gb5.pdf.chunk_rows", rowsForChunk.Count);

            var (html, headerHtml, footerHtml) = await BuildChunkHtmlAsync(
                rowsForChunk, templatePath, headerPath, footerPath, visibleHeaders, fieldMap,
                reportStandardDTO, reportCallingDTO, loginDTO, reportViewFields, cancellationToken, grandTotal);

            var report = reportStandardDTO as ReportStandardDTO;

            var metadata = new Dictionary<string, object>
            {
                { "headerTemplate", headerHtml },
                { "footerTemplate", footerHtml }
            };

            // Produce PDF bytes using shared browser
            byte[] pdfBytes = await RenderHtmlToPdfBytesUsingSharedBrowserAsync(html, metadata, report!, cancellationToken);

            // Write to temp file
            string tempPath = Path.Combine(outputDir, $"tmp_part_{DateTime.UtcNow:yyyyMMddHHmmss}_{chunkIndex}.pdf");
            await System.IO.File.WriteAllBytesAsync(tempPath, pdfBytes, cancellationToken);

            return tempPath;
        }

        private static PaperFormat GetPaperFormatFromEnum(byte reportViewPageFormat)
        {
            return reportViewPageFormat switch
            {
                // A Series
                0 => PaperFormat.A0,
                1 => PaperFormat.A1,
                2 => PaperFormat.A2,
                3 => PaperFormat.A3,
                4 => PaperFormat.A4,
                5 => PaperFormat.A5,
                6 => PaperFormat.A6,
                7 => new PaperFormat(2.9134m, 4.1339m),   // A7
                8 => new PaperFormat(2.0472m, 2.9134m),   // A8
                9 => new PaperFormat(1.4567m, 2.0472m),   // A9
                10 => new PaperFormat(1.0236m, 1.4567m),   // A10

                // B Series
                11 => new PaperFormat(39.3701m, 55.6693m), // B0
                12 => new PaperFormat(27.8346m, 39.3701m), // B1
                13 => new PaperFormat(19.6850m, 27.8346m), // B2
                14 => new PaperFormat(13.8898m, 19.6850m), // B3
                15 => new PaperFormat(9.8425m, 13.8898m),  // B4
                16 => new PaperFormat(6.9291m, 9.8425m),   // B5

                // Others
                17 => PaperFormat.Letter,
                18 => PaperFormat.Legal,
                19 => new PaperFormat(8.5m, 13m),          // Note/Folio

                // Default
                _ => PaperFormat.A4
            };
        }

        private static bool GetLandscapeFromEnum(byte reportViewPageOrientation)
        {
            return reportViewPageOrientation switch
            {
                0 => false, // Portrait
                1 => true,  // Landscape
                _ => false  // Default to Portrait
            };
        }

        // ---------------- Render HTML -> PDF using shared browser (returns bytes) ----------------
        private static async Task<byte[]> RenderHtmlToPdfBytesUsingSharedBrowserAsync(string html, IDictionary<string, object>? metadata, ReportStandardDTO reportStandardDTO, CancellationToken cancellationToken)
        {
            await GlobalBrowser.RenderGate.WaitAsync(cancellationToken).ConfigureAwait(false);
            try
            {
                // Ensure header/footer present
                string header = metadata != null && metadata.ContainsKey("headerTemplate") ? metadata["headerTemplate"]?.ToString() ?? "" : "";
                string footer = metadata != null && metadata.ContainsKey("footerTemplate") ? metadata["footerTemplate"]?.ToString() ?? "Page <span class='pageNumber'></span> of <span class='totalPages'></span>" : "Page <span class='pageNumber'></span> of <span class='totalPages'></span>";

                PaperFormat paperFormat = GetPaperFormatFromEnum(reportStandardDTO.ReportViewPageFormat);
                bool isLandscape = GetLandscapeFromEnum(reportStandardDTO.ReportViewPageOrientation);

                var pdfOptions = new PdfOptions
                {
                    Format = paperFormat,
                    Landscape = isLandscape,
                    PrintBackground = true,
                    DisplayHeaderFooter = true,
                    HeaderTemplate = header,
                    FooterTemplate = footer,
                    MarginOptions = new MarginOptions
                    {
                        Top = "70px",
                        Left = "10px",
                        Right = "10px",
                        Bottom = "40px"
                    }
                };

                // "Protocol error (Page.printToPDF): Printing failed" is a known transient
                // Chromium renderer-memory failure under load. On each retry we reset the shared
                // browser singleton so a fresh Chromium process is used — retrying on a corrupted
                // renderer always fails. PdfDataAsync has no built-in timeout, so we race it
                // against a 120 s deadline to prevent indefinite hangs.
                const int maxAttempts = 3;
                const int pdfTimeoutSeconds = 120;
                for (int attempt = 1; ; attempt++)
                {
                    try
                    {
                        // Fetch browser inside the loop so each retry gets a fresh instance
                        // after ResetAsync() cleared the previous one.
                        var browser = await GlobalBrowser.GetAsync().ConfigureAwait(false);
                        await using var page = await browser.NewPageAsync().ConfigureAwait(false);
                        await page.SetContentAsync(html, new PuppeteerSharp.NavigationOptions
                        {
                            WaitUntil = new[] { WaitUntilNavigation.Networkidle0 },
                            Timeout = 0
                        }).ConfigureAwait(false);

                        // PdfDataAsync has no CancellationToken — race it against a hard timeout.
                        var printTask   = page.PdfDataAsync(pdfOptions);
                        var timeoutTask = Task.Delay(TimeSpan.FromSeconds(pdfTimeoutSeconds), cancellationToken);
                        if (await Task.WhenAny(printTask, timeoutTask).ConfigureAwait(false) == timeoutTask)
                            throw new TimeoutException($"PdfDataAsync timed out after {pdfTimeoutSeconds} s.");

                        var data = await printTask.ConfigureAwait(false);
                        cancellationToken.ThrowIfCancellationRequested();
                        return data;
                    }
                    catch (Exception ex) when (attempt < maxAttempts
                        && !cancellationToken.IsCancellationRequested
                        && ex is not OperationCanceledException)
                    {
                        // Reset the shared browser before retrying — a protocol error or timeout
                        // often leaves Chromium's renderer in a degraded state; a fresh process
                        // is more likely to succeed than the same broken one.
                        await GlobalBrowser.ResetAsync().ConfigureAwait(false);
                        await Task.Delay(TimeSpan.FromSeconds(attempt * 2), cancellationToken).ConfigureAwait(false);
                    }
                }
            }
            finally
            {
                GlobalBrowser.RenderGate.Release();
            }
        }

        // ---------------- Merge PDF files and delete parts ----------------
        private static byte[] MergePdfFilesAndCleanup(List<string> pdfPaths)
        {
            using var output = new PdfDocument();

            foreach (var path in pdfPaths)
            {
                using var input = PdfReader.Open(path, PdfDocumentOpenMode.Import);
                for (int i = 0; i < input.PageCount; i++)
                    output.AddPage(input.Pages[i]);

                // delete part file - best effort
                try { System.IO.File.Delete(path); } catch { /* ignore delete errors */ }
            }

            using var ms = new MemoryStream();
            output.Save(ms);
            return ms.ToArray();
        }
        private static byte[] MergePdfParts(List<byte[]> pdfParts)
        {
            using var output = new PdfDocument();
            foreach (var pdfBytes in pdfParts)
            {
                using var msIn = new MemoryStream(pdfBytes ?? Array.Empty<byte>());
                using var input = PdfReader.Open(msIn, PdfDocumentOpenMode.Import);
                for (int i = 0; i < input.PageCount; i++)
                    output.AddPage(input.Pages[i]);
            }
            using var msOut = new MemoryStream();
            output.Save(msOut);
            return msOut.ToArray();
        }
        // ===== RenderTemplateFromFileAsync (full) =====
        private async Task<string> RenderTemplateFromFileAsync(string templatePath, object context, Dictionary<string, string>? partials = null)
        {
            try
            {
                if (!System.IO.File.Exists(templatePath))
                    throw new FileNotFoundException($"Template not found: {templatePath}");

                var source = await System.IO.File.ReadAllTextAsync(templatePath);

                var hb = Handlebars.Create();

                // Register any partials (e.g. "header", "footer") before compiling
                if (partials != null)
                {
                    foreach (var kvp in partials)
                        hb.RegisterTemplate(kvp.Key, kvp.Value);
                }

                // ===== Simple helpers =====
                hb.RegisterHelper("formatDate", (writer, _, parameters) =>
                {
                    if (parameters.Length >= 1 && DateTime.TryParse(parameters[0]?.ToString(), out var dt))
                    {
                        var fmt = parameters.Length > 1 ? parameters[1]?.ToString() : "dd-MMM-yyyy";
                        writer.WriteSafeString(dt.ToString(fmt));
                    }
                });

                hb.RegisterHelper("formatCurrency", (writer, _, parameters) =>
                {
                    if (parameters.Length >= 1 && decimal.TryParse(parameters[0]?.ToString(), out var v))
                        writer.WriteSafeString(v.ToString("N2"));
                });

                hb.RegisterHelper("formatAuto", (writer, _, parameters) =>
                {
                    var value = parameters.FirstOrDefault()?.ToString();
                    if (string.IsNullOrEmpty(value))
                    {
                        writer.WriteSafeString("");
                        return;
                    }

                    if (DateTime.TryParse(value, out var dt))
                        writer.WriteSafeString(dt.ToString("dd-MMM-yyyy"));
                    else if (decimal.TryParse(value, out var d))
                        writer.WriteSafeString(d.ToString("N2"));
                    else
                        writer.WriteSafeString(System.Net.WebUtility.HtmlEncode(value));
                });

                hb.RegisterHelper("add", (writer, _, parameters) =>
                {
                    var a = Convert.ToInt32(parameters[0]);
                    var b = Convert.ToInt32(parameters[1]);
                    writer.WriteSafeString((a + b).ToString());
                });

                hb.RegisterHelper("multiply", (writer, _, parameters) =>
                {
                    var a = Convert.ToInt32(parameters[0]);
                    var b = Convert.ToInt32(parameters[1]);
                    writer.WriteSafeString((a * b).ToString());
                });

                hb.RegisterHelper("eq", (writer, _, parameters) =>
                {
                    if (parameters.Length < 2) return;
                    bool result = Equals(parameters[0], parameters[1]);
                    if (result) writer.WriteSafeString("true");
                });

                hb.RegisterHelper("gt", (writer, _, parameters) =>
                {
                    if (parameters.Length < 2) return;
                    if (double.TryParse(parameters[0]?.ToString(), out double a) &&
                        double.TryParse(parameters[1]?.ToString(), out double b) &&
                        a > b)
                    {
                        writer.WriteSafeString("true");
                    }
                });

                // ===== JSON helper =====
                hb.RegisterHelper("json", (writer, _, parameters) =>
                {
                    var options = new JsonSerializerOptions { WriteIndented = false };
                    if (parameters.Length > 0)
                    {
                        string json = System.Text.Json.JsonSerializer.Serialize(parameters[0], options);
                        writer.WriteSafeString(json);
                    }
                });

                // Renders one or more stacked array-group mini-tables under a parent row — e.g. one
                // block for VoucherInstrumentArray, then another below it for
                // VoucherBillAllocationArray, each embedded group nested (and further indented)
                // inside its own parent item's row. See ReportSubRowResolver for how these groups are
                // resolved from MREPORTVIEWFIELDS + the row's own array properties.
                hb.RegisterHelper("renderSubRows", (writer, contextObj, parameters) =>
                {
                    if (parameters.Length < 1) return;
                    if (parameters[0] is not IEnumerable<ReportSubRowResolver.ResolvedGroupResult> groups) return;
                    var groupsList = groups.ToList();
                    if (groupsList.Count == 0) return;

                    int colSpan = 1;
                    if (parameters.Length > 1 && parameters[1] is IEnumerable<object> headers)
                        colSpan = headers.Count();
                    if (parameters.Length > 2 && parameters[2] is bool hasDrillDown && hasDrillDown)
                        colSpan += 1;

                    writer.WriteSafeString(RenderSubRowGroupsHtml(groupsList, Math.Max(colSpan, 1), 1));
                });

                // Shared by renderGroupNodes below — mirrors formatAuto's date/decimal/string
                // auto-detection so Sub Total/Grand Total values format identically whether they
                // reach the page via a Handlebars expression or this C# helper.
                static string FormatAutoText(object? value)
                {
                    var text = value?.ToString();
                    if (string.IsNullOrEmpty(text)) return "";
                    if (DateTime.TryParse(text, out var dt)) return dt.ToString("dd-MMM-yyyy");
                    if (decimal.TryParse(text, NumberStyles.Any, CultureInfo.InvariantCulture, out var d)) return d.ToString("N2");
                    return System.Net.WebUtility.HtmlEncode(text);
                }

                // Shared by renderSubRows above and renderGroupNodes below — renders every top-level
                // array group as its own mini-table (stacked one below another when there's more than
                // one, per the reviewer's request), recursing into NestedGroups for embedded arrays.
                static string RenderSubRowGroupsHtml(
                    IEnumerable<ReportSubRowResolver.ResolvedGroupResult> groups, int colSpan, int indentLevel)
                {
                    var sb = new System.Text.StringBuilder();
                    foreach (var group in groups)
                    {
                        if (group.Rows.Count == 0) continue;

                        int padding = 12 * indentLevel;
                        sb.Append($"<tr class='row-sub-group'><td colspan='{colSpan}' style='padding-left:{padding}px;'>");
                        sb.Append("<table class='sub-table' style='width:100%; border-collapse:collapse;'><thead><tr>");
                        foreach (var col in group.ColumnTitles)
                            sb.Append($"<th style='text-align:left; padding:2px 8px;'>{System.Net.WebUtility.HtmlEncode(col)}</th>");
                        sb.Append("</tr></thead><tbody>");

                        foreach (var subRow in group.Rows)
                        {
                            sb.Append("<tr>");
                            foreach (var col in group.ColumnTitles)
                            {
                                subRow.Values.TryGetValue(col, out var val);
                                sb.Append($"<td style='padding:2px 8px;'>{System.Net.WebUtility.HtmlEncode(val?.ToString() ?? "")}</td>");
                            }
                            sb.Append("</tr>");

                            if (subRow.NestedGroups.Count > 0)
                            {
                                sb.Append($"<tr><td colspan='{group.ColumnTitles.Count}'>");
                                sb.Append(RenderSubRowGroupsHtml(subRow.NestedGroups, group.ColumnTitles.Count, indentLevel + 1));
                                sb.Append("</td></tr>");
                            }
                        }

                        sb.Append("</tbody></table></td></tr>");
                    }
                    return sb.ToString();
                }

                // Recursively renders a multi-level group tree (see BuildGroupNodes in
                // RenderChunkToPdfFileAsync) — one indented group-header row per level, leaf-level
                // detail rows (with the same __subRowGroups/array-group nesting support as the flat/
                // single-level render paths), and a Sub Total row closing any level that has one.
                // Written as a C# helper rather than a recursive Handlebars partial for the same
                // reason renderSubRows above is — recursive context-path (../) tracking across
                // nested Handlebars block helpers is fragile; a plain recursive C# walk is not.
                hb.RegisterHelper("renderGroupNodes", (writer, _, parameters) =>
                {
                    if (parameters.Length < 2) return;
                    if (parameters[0] is not IEnumerable<object> nodesEnum) return;
                    if (parameters[1] is not IEnumerable<object> headersEnum) return;
                    var headers = headersEnum.Select(h => h?.ToString() ?? "").ToList();
                    if (headers.Count == 0) return;
                    // Matches the flat path's own {{#if HasDrillDown}} column — only present at all
                    // when this menu has at least one MDRILLDOWN target, so the vast majority of
                    // reports (no drill-down configured) render with no extra column whatsoever.
                    bool hasDrillDown = parameters.Length > 2 && parameters[2] is bool hd && hd;

                    static string RenderDrillDownCell(IDictionary<string, object> row)
                    {
                        if (!row.TryGetValue("DrillDownLinks", out var linksObj)
                            || linksObj is not IEnumerable<Dictionary<string, object?>> links)
                            return "<td></td>";
                        var anchors = links.Select(l =>
                            $"<a href=\"{System.Net.WebUtility.HtmlEncode(l.GetValueOrDefault("Href")?.ToString() ?? "")}\">" +
                            $"{System.Net.WebUtility.HtmlEncode(l.GetValueOrDefault("ToMenuDisplayName")?.ToString() ?? "")}</a>");
                        return $"<td>{string.Join(" | ", anchors)}</td>";
                    }

                    void RenderRow(IDictionary<string, object> row)
                    {
                        writer.WriteSafeString("<tr>");
                        foreach (var h in headers)
                        {
                            row.TryGetValue(h, out var v);
                            writer.WriteSafeString($"<td>{System.Net.WebUtility.HtmlEncode(v?.ToString() ?? "")}</td>");
                        }
                        if (hasDrillDown) writer.WriteSafeString(RenderDrillDownCell(row));
                        writer.WriteSafeString("</tr>");

                        // Array sub-row groups (see ReportSubRowResolver) — same shape renderSubRows
                        // above consumes, rendered via the same shared helper.
                        if (row.TryGetValue("__subRowGroups", out var groupsObj)
                            && groupsObj is IEnumerable<ReportSubRowResolver.ResolvedGroupResult> groups
                            && groups.Any())
                        {
                            int colSpan = headers.Count + (hasDrillDown ? 1 : 0);
                            writer.WriteSafeString(RenderSubRowGroupsHtml(groups, Math.Max(colSpan, 1), 1));
                        }
                    }

                    void RenderTotalRow(IDictionary<string, object?> subTotal, string label)
                    {
                        writer.WriteSafeString("<tr class='sub-total-row'>");
                        for (int i = 0; i < headers.Count; i++)
                        {
                            if (i == 0)
                            {
                                writer.WriteSafeString($"<td><strong>{System.Net.WebUtility.HtmlEncode(label)}</strong></td>");
                            }
                            else
                            {
                                subTotal.TryGetValue(headers[i], out var v);
                                writer.WriteSafeString($"<td>{FormatAutoText(v)}</td>");
                            }
                        }
                        if (hasDrillDown) writer.WriteSafeString("<td></td>");
                        writer.WriteSafeString("</tr>");
                    }

                    void RenderNode(IDictionary<string, object> node, int level)
                    {
                        string levelTitle = node.TryGetValue("LevelTitle", out var lt) ? lt?.ToString() ?? "" : "";
                        string levelValue = node.TryGetValue("LevelValue", out var lv) ? lv?.ToString() ?? "" : "";
                        // 10px matches the .group-header CSS class's own "padding: 9px 10px" left
                        // value at level 0 (the inline style only needs to override it for level > 0).
                        int padding = 10 + (level * 20);
                        writer.WriteSafeString(
                            $"<tr><td colspan=\"99\" class=\"group-header\" style=\"padding-left:{padding}px;\">" +
                            $"{System.Net.WebUtility.HtmlEncode(levelTitle)}: {System.Net.WebUtility.HtmlEncode(levelValue)}</td></tr>");

                        bool isLeaf = node.TryGetValue("IsLeaf", out var ilObj) && ilObj is bool ilb && ilb;
                        if (isLeaf)
                        {
                            if (node.TryGetValue("Rows", out var rowsObj) && rowsObj is IEnumerable<object> rowsEnum)
                            {
                                foreach (var rowObj in rowsEnum)
                                {
                                    if (rowObj is IDictionary<string, object> row) RenderRow(row);
                                }
                            }
                        }
                        else if (node.TryGetValue("Children", out var childrenObj) && childrenObj is IEnumerable<object> childrenEnum)
                        {
                            foreach (var childObj in childrenEnum)
                            {
                                if (childObj is IDictionary<string, object> childNode) RenderNode(childNode, level + 1);
                            }
                        }

                        if (node.TryGetValue("SubTotal", out var subTotalObj) && subTotalObj is IDictionary<string, object?> subTotal)
                        {
                            RenderTotalRow(subTotal, "Sub Total");
                        }
                    }

                    foreach (var nodeObj in nodesEnum)
                    {
                        if (nodeObj is IDictionary<string, object> node) RenderNode(node, 0);
                    }
                });

                // ===== formatNumber(value) — decimal to N2 =====
                hb.RegisterHelper("formatNumber", (writer, _, parameters) =>
                {
                    if (parameters.Length >= 1)
                        writer.WriteSafeString(FormatN2(parameters[0]));
                });

                // ===== labelFor(FieldMap, "ReportVsFieldsFieldName") — returns ReportViewFieldsFieldTitle =====
                // Reverse lookup: given the API field path stored in FieldMap values, returns the display title (key).
                // Falls back to the path itself if no matching entry exists.
                hb.RegisterHelper("labelFor", (writer, _, parameters) =>
                {
                    if (parameters.Length < 2) return;
                    if (parameters[0] is not IDictionary<string, object?> fm) return;
                    var apiPath = parameters[1]?.ToString();
                    if (string.IsNullOrEmpty(apiPath)) return;

                    foreach (var kvp in fm)
                    {
                        if (string.Equals(kvp.Value?.ToString(), apiPath, StringComparison.OrdinalIgnoreCase))
                        {
                            writer.WriteSafeString(System.Net.WebUtility.HtmlEncode(kvp.Key));
                            return;
                        }
                    }
                });

                // ===== rowValue(row, FieldMap, "apiPath") — scalar string from pre-processed row =====
                hb.RegisterHelper("rowValue", (writer, _, parameters) =>
                {
                    var val = ResolveByPathStatic(parameters);
                    if (val is IEnumerable && val is not string) return; // skip arrays
                    writer.WriteSafeString(System.Net.WebUtility.HtmlEncode(val?.ToString() ?? ""));
                });

                // ===== formatByPath(row, FieldMap, "apiPath") — numeric value formatted N2 =====
                hb.RegisterHelper("formatByPath", (writer, _, parameters) =>
                {
                    var val = ResolveByPathStatic(parameters);
                    writer.WriteSafeString(FormatN2(val));
                });

                // ===== firstByPath(row, FieldMap, "apiPath", "fieldName") — field from first array item =====
                hb.RegisterHelper("firstByPath", (writer, _, parameters) =>
                {
                    var list = ResolveByPathStatic(parameters) as IEnumerable;
                    var fieldName = parameters.Length >= 4 ? parameters[3]?.ToString() : null;
                    if (list == null || fieldName == null) return;
                    foreach (var item in list)
                    {
                        if (item is IDictionary<string, object?> d && d.TryGetValue(fieldName, out var v))
                            writer.WriteSafeString(System.Net.WebUtility.HtmlEncode(v?.ToString() ?? ""));
                        return; // first item only
                    }
                });

                // ===== eachByPath(row, FieldMap, "apiPath") — block: iterate array resolved by apiPath =====
                hb.RegisterHelper("eachByPath", (writer, options, context, parameters) =>
                {
                    var list = ResolveByPathStatic(parameters) as IEnumerable;
                    if (list == null) { options.Inverse(writer, context); return; }
                    bool any = false;
                    foreach (var item in list) { options.Template(writer, item); any = true; }
                    if (!any) options.Inverse(writer, context);
                });

                // ===== renderPayConfigTable(row, FieldMap, "apiPath") — paired earnings/deductions rows =====
                hb.RegisterHelper("renderPayConfigTable", (writer, _, parameters) =>
                {
                    var rawItems = ResolveByPathStatic(parameters) as IEnumerable;
                    if (rawItems == null) return;

                    var allItems = new List<IDictionary<string, object?>>();
                    foreach (var item in rawItems)
                        if (item is IDictionary<string, object?> d) allItems.Add(d);

                    bool ShouldPrint(IDictionary<string, object?> d) =>
                        !d.TryGetValue("PrintRequired", out var pr) || pr?.ToString() != "0";
                    bool IsDeduction(IDictionary<string, object?> d) =>
                        d.TryGetValue("PayConfigurationDetailOperationType", out var op) && op?.ToString() == "1";

                    var earnings = allItems.Where(d => ShouldPrint(d) && !IsDeduction(d)).ToList();
                    var deductions = allItems.Where(d => ShouldPrint(d) && IsDeduction(d)).ToList();
                    int rowCount = Math.Max(earnings.Count, deductions.Count);

                    var sb = new System.Text.StringBuilder();
                    for (int i = 0; i < rowCount; i++)
                    {
                        sb.Append("<tr>");
                        if (i < earnings.Count)
                        {
                            var e = earnings[i];
                            e.TryGetValue("DisplayName", out var dn);
                            e.TryGetValue("ActualFieldValue", out var av);
                            e.TryGetValue("FieldValue", out var fv);
                            sb.Append($"<td style='padding:3px 6px;border:1px solid #ccc;'>{System.Net.WebUtility.HtmlEncode(dn?.ToString() ?? "")}</td>");
                            sb.Append($"<td style='padding:3px 6px;border:1px solid #ccc;text-align:right;'>{FormatN2(av)}</td>");
                            sb.Append($"<td style='padding:3px 6px;border:1px solid #ccc;text-align:right;'>{FormatN2(fv)}</td>");
                        }
                        else
                        {
                            sb.Append("<td style='padding:3px 6px;border:1px solid #ccc;'>&nbsp;</td>");
                            sb.Append("<td style='padding:3px 6px;border:1px solid #ccc;'>&nbsp;</td>");
                            sb.Append("<td style='padding:3px 6px;border:1px solid #ccc;'>&nbsp;</td>");
                        }
                        if (i < deductions.Count)
                        {
                            var d = deductions[i];
                            d.TryGetValue("DisplayName", out var dn);
                            d.TryGetValue("FieldValue", out var fv);
                            sb.Append($"<td style='padding:3px 6px;border:1px solid #ccc;border-left:2px solid #555;'>{System.Net.WebUtility.HtmlEncode(dn?.ToString() ?? "")}</td>");
                            sb.Append($"<td style='padding:3px 6px;border:1px solid #ccc;text-align:right;'>{FormatN2(fv)}</td>");
                        }
                        else
                        {
                            sb.Append("<td style='padding:3px 6px;border:1px solid #ccc;border-left:2px solid #555;'>&nbsp;</td>");
                            sb.Append("<td style='padding:3px 6px;border:1px solid #ccc;'>&nbsp;</td>");
                        }
                        sb.Append("</tr>\n");
                    }
                    writer.WriteSafeString(sb.ToString());
                });

                // ===== renderEmpInfoTable(row, empFields, colsPerRow) — dynamic employee info grid =====
                // Renders a label-value grid for all emp-header fields driven by DB config.
                hb.RegisterHelper("renderEmpInfoTable", (writer, _, parameters) =>
                {
                    writer.WriteSafeString(BuildFieldTable(parameters, "emp-table", "emp-label", "emp-value", 3));
                });

                // ===== renderAttTable(row, attFields, colsPerRow) — dynamic attendance summary =====
                // Renders a compact label-value grid for payslip-specific numeric fields.
                hb.RegisterHelper("renderAttTable", (writer, _, parameters) =>
                {
                    writer.WriteSafeString(BuildFieldTable(parameters, "att-tbl", "att-lbl", null, 4));
                });

                var template = hb.Compile(source);
                return template(context);
            }
            catch (Exception ex)
            {
                throw new Exception("Template rendering failed: " + ex.Message, ex);
            }
        }

        private static class GlobalBrowser
        {
            public static Task<IBrowser> GetAsync() => Export.HybridReport.GlobalBrowser.GetAsync();
            public static SemaphoreSlim RenderGate => Export.HybridReport.GlobalBrowser.RenderGate;
            public static Task ResetAsync() => Export.HybridReport.GlobalBrowser.ResetAsync();
        }
        /// <summary>
        /// Looks up a value from a pre-processed row using the fieldMap.
        /// parameters[0] = row (IDictionary&lt;string,object?&gt;)
        /// parameters[1] = fieldMap context (IDictionary&lt;string,object?&gt; — header→apiPath)
        /// parameters[2] = apiPath (string)
        /// Finds the header whose apiPath matches, returns row[header].
        /// </summary>
        private static object? ResolveByPathStatic(Arguments parameters)
        {
            if (parameters.Length < 3) return null;
            if (parameters[1] is not IDictionary<string, object?> fm) return null;
            var apiPath = parameters[2]?.ToString();
            if (string.IsNullOrEmpty(apiPath)) return null;

            // 1. FieldMap lookup: find the title whose ReportVsFieldsFieldName matches apiPath,
            //    then return the pre-processed scalar value stored under that title in the row.
            foreach (var kvp in fm)
            {
                if (string.Equals(kvp.Value?.ToString(), apiPath, StringComparison.OrdinalIgnoreCase))
                {
                    var preProcessed = GetFromAnyRow(parameters[0], kvp.Key);
                    if (preProcessed != null) return preProcessed;
                }
            }

            // 2. Fallback: navigate the raw API record directly by apiPath.
            //    Handles arrays (EmployeePayconfigurationDetailReportDTOs),
            //    nested objects (PayprocessReport.PresentDays, Employee.DateOfJoining),
            //    and any path not present in the DB report-view config.
            var rawRecord = GetFromAnyRow(parameters[0], "__rawRecord");
            if (rawRecord != null)
                return GetDynamicFieldValue(rawRecord, apiPath);

            return null;
        }

        /// <summary>Reads a key from either Dictionary&lt;string,object&gt; or Dictionary&lt;string,object?&gt;.</summary>
        private static object? GetFromAnyRow(object? rowObj, string key)
        {
            if (rowObj is IDictionary<string, object?> nd)
            {
                nd.TryGetValue(key, out var v);
                return v;
            }
            if (rowObj is IDictionary<string, object> d)
            {
                d.TryGetValue(key, out var v);
                return v;
            }
            return null;
        }

        private static string FormatN2(object? value)
        {
            if (value == null) return "";
            return decimal.TryParse(value.ToString(),
                System.Globalization.NumberStyles.Any,
                System.Globalization.CultureInfo.InvariantCulture,
                out var d)
                ? d.ToString("N2")
                : (value.ToString() ?? "");
        }

        // Shared by both the main-row column loop and ReportSubRowResolver's per-array-item value
        // formatting, so a sub-row field (e.g. VoucherInstrumentArray.InstrumentDate) gets the exact
        // same date/decimal/CalFormat treatment a main-row field of the same ReportVsFieldsFieldType
        // would.
        private static object? FormatFieldByType(object? rawValue, ReportViewFieldsDTO? fieldMeta)
        {
            switch (fieldMeta?.ReportVsFieldsFieldType)
            {
                case 3: // date
                    return FormatDateValue(rawValue, fieldMeta.ReportViewFieldsCalFormat);
                case 0: // int
                case 6: // decimal
                    return !string.IsNullOrWhiteSpace(fieldMeta?.ReportViewFieldsCalFormat)
                        ? FormatWithCalFormat(rawValue, fieldMeta.ReportViewFieldsCalFormat)
                        : (rawValue ?? "");
                default:
                    return rawValue ?? "";
            }
        }

        // Applies ##,##,##0.000 (Indian) or ###,##0.000 (International) format from ReportViewFieldsCalFormat.
        // Decimal places are derived from the digits after '.' in the format string.
        private static string FormatWithCalFormat(object? value, string? calFormat)
        {
            if (value == null) return "";
            if (!decimal.TryParse(value.ToString(),
                    NumberStyles.Any, CultureInfo.InvariantCulture, out decimal d))
                return value.ToString() ?? "";

            if (string.IsNullOrWhiteSpace(calFormat))
                return d.ToString("N2", CultureInfo.InvariantCulture);

            // Count decimal places from the format string
            int decimals = 2;
            int dotIdx = calFormat.IndexOf('.');
            if (dotIdx >= 0)
                decimals = calFormat.Length - dotIdx - 1;

            // Indian format: ##,##,##0  → en-IN culture (groups: 3, then 2)
            if (calFormat.Contains("##,##,##0"))
                return d.ToString("N" + decimals, new CultureInfo("en-IN"));

            // International / standard: ###,##0 or any other comma-containing pattern
            return d.ToString("N" + decimals, CultureInfo.InvariantCulture);
        }

        // Returns true when a cell value should be suppressed (null, whitespace, or numeric zero).
        private static bool IsZeroOrEmpty(string? value)
        {
            if (string.IsNullOrWhiteSpace(value)) return true;
            return decimal.TryParse(value, NumberStyles.Any, CultureInfo.InvariantCulture, out decimal d) && d == 0m;
        }

        private static string FormatDateValue(object? rawValue, string? format)
        {
            if (rawValue == null)
                return "";

            // Default format if not provided
            string userFormat = string.IsNullOrWhiteSpace(format) ? "dd/MM/yyyy" : format;

            // Normalize format tokens: ensure correct month casing
            string normalizedFormat = userFormat
                .Replace("mmm", "MMM", StringComparison.OrdinalIgnoreCase)
                .Replace("mm", "MM", StringComparison.OrdinalIgnoreCase);

            // Try parsing ISO, local, or simple date strings
            if (DateTimeOffset.TryParse(rawValue.ToString(), CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal, out var dto))
            {
                var dateOnly = dto.Date;
                return dateOnly.ToString(normalizedFormat, CultureInfo.InvariantCulture);
            }

            if (DateTime.TryParse(rawValue.ToString(), CultureInfo.InvariantCulture, DateTimeStyles.None, out var dt))
            {
                var dateOnly = dt.Date;
                return dateOnly.ToString(normalizedFormat, CultureInfo.InvariantCulture);
            }

            // Parsing failed
            return "";
        }


        /// <summary>
        /// Shared table builder used by renderEmpInfoTable and renderAttTable helpers.
        /// parameters[0] = row dict (must contain __rawRecord)
        /// parameters[1] = IEnumerable of IDictionary with keys: Title, Path, Type
        /// parameters[2] = colsPerRow (optional int)
        /// </summary>
        private static string BuildFieldTable(
            Arguments parameters,
            string tableClass,
            string labelClass,
            string? valueClass,
            int defaultCols)
        {
            if (parameters.Length < 2) return "";
            var rawRecord = GetFromAnyRow(parameters[0], "__rawRecord");
            var fields = parameters[1] as IEnumerable;
            if (fields == null) return "";

            int colsPerRow = defaultCols;
            if (parameters.Length > 2 && int.TryParse(parameters[2]?.ToString(), out int c) && c > 0)
                colsPerRow = c;

            var fieldList = new List<(string Title, string Path, int Type)>();
            foreach (var f in fields)
            {
                if (f is IDictionary<string, object> fd)
                {
                    var title = fd.TryGetValue("Title", out var t) ? t?.ToString() ?? "" : "";
                    var path = fd.TryGetValue("Path", out var p) ? p?.ToString() ?? "" : "";
                    var type = fd.TryGetValue("Type", out var ty) && int.TryParse(ty?.ToString(), out var ti) ? ti : 1;
                    if (!string.IsNullOrEmpty(path))
                        fieldList.Add((title, path, type));
                }
            }

            if (!fieldList.Any()) return "";

            // Pre-resolve values; suppress fields whose value is null or empty string.
            // This eliminates system/calculation fields that don't map to any value in the
            // API response, keeping the table compact regardless of how many DB fields are configured.
            var resolved = new List<(string Title, string Formatted)>();
            foreach (var (title, path, type) in fieldList)
            {
                var rawVal = GetDynamicFieldValue(rawRecord, path);
                string fmted = FormatFieldValue(rawVal, type);
                if (!string.IsNullOrEmpty(fmted))
                    resolved.Add((title, fmted));
            }

            if (!resolved.Any()) return "";

            var sb = new System.Text.StringBuilder();
            sb.Append($"<table class=\"{tableClass}\"><tbody>");

            for (int i = 0; i < resolved.Count; i += colsPerRow)
            {
                sb.Append("<tr>");
                for (int j = 0; j < colsPerRow; j++)
                {
                    int idx = i + j;
                    if (idx < resolved.Count)
                    {
                        var (title, fmted) = resolved[idx];
                        string valCell = valueClass != null
                            ? $"<td class=\"{valueClass}\">{System.Net.WebUtility.HtmlEncode(fmted)}</td>"
                            : $"<td>{System.Net.WebUtility.HtmlEncode(fmted)}</td>";
                        sb.Append($"<td class=\"{labelClass}\">{System.Net.WebUtility.HtmlEncode(title)}</td>");
                        sb.Append(valCell);
                    }
                    else
                    {
                        sb.Append("<td></td><td></td>");
                    }
                }
                sb.Append("</tr>");
            }

            sb.Append("</tbody></table>");
            return sb.ToString();
        }

        private static string FormatFieldValue(object? val, int fieldType)
        {
            if (val == null) return "";
            return fieldType switch
            {
                3 => FormatDateValue(val, null),   // date
                6 => FormatN2(val),                // decimal
                _ => val?.ToString() ?? ""
            };
        }

        /// <summary>
        /// Extracts a Unix-epoch-seconds value from a date criteria field.
        /// Handles three input shapes:
        ///   • JsonElement Number  — already epoch seconds/ms, returned as-is
        ///   • JsonElement String  — numeric epoch string OR ISO date string (e.g. "2023-01-01")
        ///   • Any other object    — same string-level fallback
        /// For ISO date strings the value is converted to UTC epoch seconds so that
        /// FromEpoch(AddSeconds) downstream produces the correct DateTime.
        /// </summary>
        private static long ParseDateFieldToEpochSeconds(object? value)
        {
            if (value is null) return 0;

            if (value is JsonElement el)
            {
                if (el.ValueKind == JsonValueKind.Number)
                    return el.GetInt64();

                if (el.ValueKind == JsonValueKind.String)
                {
                    var s = el.GetString() ?? string.Empty;
                    if (long.TryParse(s, out long numericEpoch))
                        return numericEpoch;
                    if (DateTime.TryParse(s, null,
                            System.Globalization.DateTimeStyles.RoundtripKind |
                            System.Globalization.DateTimeStyles.AllowWhiteSpaces,
                            out DateTime parsedDt))
                        return (long)(parsedDt.ToUniversalTime() - DateTime.UnixEpoch).TotalSeconds;
                }
                return 0;
            }

            var raw = Convert.ToString(value) ?? string.Empty;
            if (long.TryParse(raw, out long longEpoch))
                return longEpoch;
            if (DateTime.TryParse(raw, null,
                    System.Globalization.DateTimeStyles.RoundtripKind |
                    System.Globalization.DateTimeStyles.AllowWhiteSpaces,
                    out DateTime dtVal))
                return (long)(dtVal.ToUniversalTime() - DateTime.UnixEpoch).TotalSeconds;

            return 0;
        }

        private async Task<ReportStandardDTO> FillReportStandardDTO(LoginDTO LoginDTO, CriteriaDTO CriteriaDTO, int ReportFormatId = -1, int ReportId = -1, int ReportViewId = -1)
        {
            try
            {
                ReportStandardDTO ReportStandardDTO = await ReportStandardField(LoginDTO, CriteriaDTO);
                string ReportFormatCode = "";
                string DataSourceFileName = "";
                int ReportFormatVersion = 0;
                DateTime ReportFormatModifiedOn = DateTime.UtcNow;
                string ReportFile = "";
                if (ReportFormatId != -1)
                {

                    ReportFormatDTO ReportFormatDTO = await  GetReportFormat(ReportFormatId, LoginDTO);

                    if (ReportFormatDTO.IsPrintLogo == 0)
                    {
                        ReportStandardDTO.IsPrintLogo = true;
                    }
                    else
                    {
                        ReportStandardDTO.IsPrintLogo = false;
                    }
                    ReportFormatCode = ReportFormatDTO.ReportFormatCode;
                    DataSourceFileName = ReportFormatDTO.DataSourceFileName;
                    ReportFile = ReportFormatDTO.ReportFile;
                    ReportFormatVersion = Convert.ToInt32(ReportFormatDTO.Version);
                    ReportFormatModifiedOn = ReportFormatDTO.ModifiedOn;
                }

                string ReportCode = "";

                if (ReportId != -1)
                {
                    ReportDTO ReportDTO = await  GetReport(ReportId, LoginDTO);
                    ReportCode = ReportDTO.ReportCode;
                }
                if (ReportViewId != -1)
                {
                    // GetReportView resolves the OPTIONAL legacy dynamic header/footer LAYOUT
                    // (MREPORTHEADER/MREPORTHEADERDETAIL) for this view — its query deliberately
                    // excludes REPORTHEADERID = -1 ("no custom layout configured"), which is the
                    // convention the vast majority of report views use. An empty result here means
                    // "this view has no custom dynamic header layout," NOT "this view doesn't exist"
                    // — existence is already verified by GetReportViewMeta (queries MREPORTVIEW
                    // directly) in every caller before FillReportStandardDTO is ever invoked. Treating
                    // "no custom layout" as fatal broke PDF/HTML export for every report using the
                    // default (-1) header, which is most of them.
                    List<ReportViewDTO> ReportViewDTOList = await  GetReportView(ReportViewId, LoginDTO);

                    if (ReportViewDTOList.Any())
                    {
                        ReportViewDTO ReportViewDTO = ReportViewDTOList.First();
                        ReportStandardDTO.ReportViewDynamicFooter = string.Join(";", ReportViewDTOList.Select(x => x.ReportViewDynamicFooter));
                        ReportStandardDTO.ReportViewPageFormat = ReportViewDTO.ReportViewPageFormat;
                        ReportStandardDTO.ReportViewPageOrientation = ReportViewDTO.ReportViewPageOrientation;
                    }
                }
                #region Logo Formation
                if (ReportStandardDTO.OULogo1 != null && ReportStandardDTO.OULogo1 != "" && ReportStandardDTO.OULogo1.ToUpper() != "NONE"!)
                {
                    ReportStandardDTO.CompanyLogo1 = ReportStandardDTO.OULogo1;
                }
                if (ReportStandardDTO.OULogo2 != null && ReportStandardDTO.OULogo2 != "" && ReportStandardDTO.OULogo2.ToUpper() != "NONE"!)
                {
                    ReportStandardDTO.CompanyLogo2 = ReportStandardDTO.OULogo2;
                }
                #endregion

                #region ReportHeader/Footer Assign 
                //Header 
                ReportHeader ReportHeader = new ReportHeader();

                ReportHeader.CompanyId = ReportStandardDTO.CompanyId;
                ReportHeader.CompanyCode = ReportStandardDTO.CompanyCode;
                ReportHeader.CompanyName = ReportStandardDTO.CompanyName;
                ReportHeader.BranchId = ReportStandardDTO.BranchId;
                ReportHeader.BranchCode = ReportStandardDTO.BranchCode;
                ReportHeader.BranchName = ReportStandardDTO.BranchName;
                ReportHeader.DivisionId = ReportStandardDTO.DivisionId;
                ReportHeader.DivisionCode = ReportStandardDTO.DivisionCode;
                ReportHeader.DivisionName = ReportStandardDTO.DivisionName;
                ReportHeader.OuId = ReportStandardDTO.OuId;
                ReportHeader.OuCode = ReportStandardDTO.OuCode;
                ReportHeader.OuName = ReportStandardDTO.OuName;
                ReportHeader.OUDisplayName = ReportStandardDTO.OUDisplayName;
                ReportHeader.AddressDetail = ReportStandardDTO.AddressDetail;
                ReportHeader.CompanyAddressDetail = ReportStandardDTO.CompanyAddressDetail;
                ReportHeader.ReportTitle = ReportStandardDTO.ReportTitle;
                ReportHeader.ReportTitleOptional = ReportStandardDTO.ReportTitleOptional;
                ReportHeader.ReportPath = ReportStandardDTO.ReportPath;
                ReportHeader.ReportFolder = ReportStandardDTO.ReportFolder;
                ReportHeader.CompanyLogo1 = ReportStandardDTO.CompanyLogo1;
                ReportHeader.CompanyLogo2 = ReportStandardDTO.CompanyLogo2;
                ReportHeader.IsPrintLogo = ReportStandardDTO.IsPrintLogo;

                //Footer

                ReportFooter ReportFooter = new ReportFooter();
                ReportFooter.IsoReferenceNumber = ReportStandardDTO.IsoReferenceNumber;
                ReportFooter.ReportFormatCode = ReportFormatCode;
                ReportFooter.DataSourceFileName = DataSourceFileName;
                ReportFooter.ReportFile = ReportFile;
                ReportFooter.ReportCode = ReportCode;

                ReportFooter.ReportFormatVersion = ReportFormatVersion;
                ReportFooter.ReportFormatModifiedOn = ReportFormatModifiedOn;

                //Final Assign
                ReportStandardDTO.ReportHeader = ReportHeader;
                ReportStandardDTO.ReportFooter = ReportFooter;

                #endregion

                #region Dynamic Jasper Region
                ReportStandardDTO.ReportViewId = ReportViewId;
                ReportStandardDTO.DatabaseName = LoginDTO.DatabaseName;
                #endregion

                #region User Format

                ReportStandardDTO.TimeFormat = LoginDTO.TimeFormat;
                ReportStandardDTO.QuantityFormat = LoginDTO.QuantityFormat;
                #endregion

                #region TimeZone
                ReportStandardDTO.ServiceOffSet = LoginDTO.ServiceOffSet * 60 * 1000;
                #endregion

                return ReportStandardDTO;

            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<string> ReportTitle(string ReportTitle, CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                ReportTitle ??= "";
                bool IsDate = false;
                if (CriteriaDTO != null)
                {
                    for (int i = 0; i < CriteriaDTO.SectionCriteriaList.Count; i++)
                    {
                        for (int z = 0; z < CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList.Count; z++)
                        {

                            if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].IsHeader == 0)//If HeaderRequired
                            {
                                if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].IsCompulsory == 0)//If Is Mandatory
                                {
                                    if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].FieldValue != null)
                                    {
                                        if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].FieldValue.ToString() != "")
                                        {

                                            string TempCriteriaFieldName = CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].CriteriaAttributeName;
                                            string TempCriteriaFieldValue = CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].CriteriaAttributeValue;
                                            string TempFieldName = CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].FieldName;

                                            if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].CriteriaAttributeType == 4)//Date
                                            {
                                                string format = LoginDTO.DateFormat;

                                                long unixTime = ParseDateFieldToEpochSeconds(
                                                    CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].FieldValue);

                                                DateTime ConvertedDate = await GB5CommonFunction.FromEpoch(unixTime);
                                                TimeSpan Timespan = new TimeSpan(0, LoginDTO.TimeZone, 0);
                                                ConvertedDate = ConvertedDate.Add(Timespan);
                                                string TempDate = ConvertedDate.ToString(format);

                                                if (IsDate == false)
                                                {
                                                    IsDate = true;
                                                    ReportTitle = ReportTitle + "  <<REPLACE>> " + " " + TempDate + " ";
                                                }
                                                else
                                                {
                                                    ReportTitle = ReportTitle.Replace("<<REPLACE>>", "From");
                                                    ReportTitle = ReportTitle + "  To " + " " + TempDate + " ";
                                                }
                                            }
                                            else
                                            {
                                                if (TempCriteriaFieldName != null)
                                                {
                                                    if (TempCriteriaFieldName.ToLower() == "payperiod"!)
                                                    {
                                                        ReportTitle = ReportTitle + " " + TempCriteriaFieldValue + " ";
                                                    }
                                                    else
                                                    {
                                                        ReportTitle = ReportTitle + " " + TempCriteriaFieldName + " :" + TempCriteriaFieldValue + " ";
                                                    }
                                                }
                                                else
                                                {
                                                    ReportTitle = ReportTitle + " " + TempCriteriaFieldName + " :" + TempCriteriaFieldValue + " ";
                                                }

                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                ReportTitle = ReportTitle.Replace("<<REPLACE>>", "as on");
                return ReportTitle;
            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<string> ReportTitleOptional(CriteriaDTO CriteriaDTO, LoginDTO LoginDTO)
        {
            try
            {
                string ReportOptionalTitle = "";
                string OperationType = "";
                bool IsDate = false;
                if (CriteriaDTO != null)
                {

                    for (int i = 0; i < CriteriaDTO.SectionCriteriaList.Count; i++)
                    {

                        for (int z = 0; z < CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList.Count; z++)
                        {

                            if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].IsHeader == 0)//If HeaderRequired
                            {
                                if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].IsCompulsory == 1)//Optional
                                {
                                    if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].FieldValue != null)
                                    {
                                        if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].FieldValue.ToString() != "")
                                        {
                                            if (CriteriaDTO.OperationType.None.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "Not Defined";
                                            }
                                            else if (CriteriaDTO.OperationType.Equal.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "=";
                                            }
                                            else if (CriteriaDTO.OperationType.Like.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "Like";
                                            }
                                            else if (CriteriaDTO.OperationType.Or.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "Or";
                                            }
                                            else if (CriteriaDTO.OperationType.And.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "And";
                                            }
                                            else if (CriteriaDTO.OperationType.In.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "In";
                                            }
                                            else if (CriteriaDTO.OperationType.NotIn.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "Not In";
                                            }
                                            else if (CriteriaDTO.OperationType.NotEqual.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "<>";
                                            }
                                            else if (CriteriaDTO.OperationType.GreaterThan.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = ">";
                                            }
                                            else if (CriteriaDTO.OperationType.LessThan.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "<";
                                            }
                                            else if (CriteriaDTO.OperationType.GreaterThanOrEqualTo.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = ">=";
                                            }
                                            else if (CriteriaDTO.OperationType.LessThanOrEqualTo.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "<=";
                                            }
                                            else if (CriteriaDTO.OperationType.Between.Equals(CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].OperationType))
                                            {
                                                OperationType = "Between";
                                            }

                                            string TempCriteriaFieldName = CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].CriteriaAttributeName;
                                            string TempCriteriaFieldValue = CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].CriteriaAttributeValue;
                                            string TempFieldName = CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].FieldName;

                                            if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].CriteriaAttributeType == 4)//Date 
                                            {
                                                string format = LoginDTO.DateFormat;

                                                var fieldValue = CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].FieldValue;

                                                long unixTime = ParseDateFieldToEpochSeconds(fieldValue);
                                                DateTime tempDateTime = await GB5CommonFunction.FromEpoch(unixTime);
                                                string TempDate = tempDateTime.ToString(format);
                                                if (IsDate == false)
                                                {
                                                    IsDate = true;
                                                    ReportOptionalTitle = ReportOptionalTitle + " " + TempCriteriaFieldName + " " + OperationType + " " + TempDate + " ";
                                                }
                                                else
                                                {
                                                    ReportOptionalTitle = ReportOptionalTitle + " " + TempCriteriaFieldName + " " + OperationType + " " + TempDate + " ";
                                                }
                                            }
                                            else
                                            {
                                                ReportOptionalTitle = ReportOptionalTitle + " " + TempCriteriaFieldName + " " + OperationType + " " + TempCriteriaFieldValue + " ";
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                return ReportOptionalTitle;
            }
            catch (Exception)
            {
                throw;
            }
        }

        private async Task<ReportStandardDTO> ReportStandardField(LoginDTO LoginDTO, CriteriaDTO CriteriaDTO)
        {
            try
            {
                ReportStandardDTO ReturnReportStandardDTO = new ReportStandardDTO();
                string OuId = LoginDTO.WorkOUId.ToString();
                string DatabaseName = LoginDTO.DatabaseName;
                if (CriteriaDTO != null)
                {
                    for (int i = 0; i < CriteriaDTO.SectionCriteriaList.Count; i++)
                    {
                        for (int j = 0; j < CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList.Count; j++)
                        {
                            if (CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[j].FieldName.ToLower() == "ouid"! ||
                                CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[j].FieldName.ToLower() == "ou"! ||
                                CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[j].FieldName.ToLower() == "workouid"! ||
                                CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[j].FieldName.ToLower() == "workou"! ||
                                CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[j].FieldName.ToLower() == "organizationunitid"! ||
                                CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[j].FieldName.ToLower() == "organizationunit"!
                                )
                            {
                                string TempOuId = Convert.ToString(CriteriaDTO.SectionCriteriaList[0].AttributesCriteriaList[j].FieldValue)!;
                                if (TempOuId.Split(',').Length == 1)
                                {
                                    OuId = TempOuId;
                                }
                            }
                        }
                    }
                }

                List<ReportStandardDTO> ReportStandardDTOs = await  GetStandardFields(OuId, LoginDTO);

                if (ReportStandardDTOs.Count == 0)
                {
                    throw new NotFoundException("Standard Fields for Report not found..Please Check Company,Branch,Division,OuId is correctly set..Contact Administrator..");
                }
                ReturnReportStandardDTO = ReportStandardDTOs[0];
                return ReturnReportStandardDTO;
            }
            catch (Exception)
            {
                throw;
            }
        }
        private async Task<string> ReplaceExcelHeaderPlaceholders(string expression, ReportStandardDTO reportStandard, LoginDTO loginDTO)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(expression))
                    return string.Empty;

                return expression
                    .Replace("{OUDisplayName}", reportStandard.OUDisplayName ?? "")
                    .Replace("{BranchName}", reportStandard.BranchName ?? "")
                    .Replace("{AddressLine1}", reportStandard.AddressDetail?.AddressLine1 ?? "")
                    .Replace("{AddressLine2}", reportStandard.AddressDetail?.AddressLine2 ?? "")
                    .Replace("{AddressLine3}", reportStandard.AddressDetail?.AddressLine3 ?? "")
                    .Replace("{ReportTitle}", reportStandard.ReportTitle ?? "")
                    .Replace("{ReportTitleOptional}", reportStandard.ReportTitleOptional ?? "")
                    .Replace("{UserName}", loginDTO.UserName ?? "")
                    .Replace("{GeneratedOn}", DateTime.UtcNow.ToString("dd-MMM-yyyy hh:mm tt"));
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<ReportFormatDTO> GetReportFormat(int ReportFormatId, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new Dictionary<string, object>
                {
                    { "@ReportFormatId", ReportFormatId }
                };

                string query = ReportQB.GETREPORTFORMAT;
                ReportFormatDTO reportFormat = await queryExecutor.QuerySingleAsync<ReportFormatDTO>(LoginDTO, query, parameters);

                return reportFormat;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<ReportDTO> GetReport(int ReportId, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new Dictionary<string, object>
                {
                    { "@ReportId", ReportId }
                };

                string query = ReportQB.GETREPORT;
                ReportDTO report = await queryExecutor.QuerySingleAsync<ReportDTO>(LoginDTO, query, parameters);

                return report;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<ReportViewDTO>> GetReportView(int ReportViewId, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new Dictionary<string, object>
                {
                    { "@ReportViewId", ReportViewId }
                };

                string query = ReportQB.GETREPORTVIEW;
                List<ReportViewDTO> reportViews = (await queryExecutor.QueryAsync<ReportViewDTO>(LoginDTO, query, parameters)).ToList();

                return reportViews;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<ReportStandardDTO>> GetStandardFields(string OuId, LoginDTO LoginDTO)
        {
            try
            {
                var parameters = new Dictionary<string, object>
                {
                    { "@ouid", OuId },
                    { "@clientid", LoginDTO.ClientId },
                    { "@baseuri", LoginDTO.BaseUri ?? "" }
                };

                string query = ReportQB.GET_STANDARD_FIELDS_FOR_REPORT;
                List<ReportStandardDTO> standardFields = (await queryExecutor.QueryAsync<ReportStandardDTO>(LoginDTO, query, parameters)).ToList();

                if (standardFields.Any())
                {
                    var std = standardFields[0];

                    // OBJECTTYPEID -1899999977 = MOrganizationUnit — OU/site address
                    var ouAddrParams = new Dictionary<string, object>
                    {
                        { "@ObjectTypeId", -1899999977 },
                        { "@ObjectId",     std.OuId    }
                    };
                    var ouAddr = (await queryExecutor.QueryAsync<AddressDTO>(LoginDTO, ReportQB.GET_ADDRESS_FOR_REPORT, ouAddrParams)).FirstOrDefault();
                    std.AddressDetail = ouAddr;

                    // OBJECTTYPEID -1899999839 = MBranch — branch/company address
                    var branchAddrParams = new Dictionary<string, object>
                    {
                        { "@ObjectTypeId", -1899999839 },
                        { "@ObjectId",     std.BranchId }
                    };
                    var branchAddr = (await queryExecutor.QueryAsync<AddressDTO>(LoginDTO, ReportQB.GET_ADDRESS_FOR_REPORT, branchAddrParams)).FirstOrDefault();
                    std.CompanyAddressDetail = branchAddr;
                }

                return standardFields;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<ReportViewFieldsDTO>> GetReportViewField(int ReportViewId, int FieldIsDisplay, LoginDTO LoginDTO)
        {
            var cacheKey = $"rvf:{ReportViewId}:{FieldIsDisplay}:{LoginDTO.DatabaseName}";
            if (_fieldCache.TryGetValue(cacheKey, out List<ReportViewFieldsDTO>? cached) && cached != null)
                return cached;

            var parameters = new Dictionary<string, object>
            {
                { "@ReportViewId", ReportViewId },
                { "@FieldIsDisplay", FieldIsDisplay }
            };

            string query = ReportQB.GET_REPORT_VIEW_FIELD;
            var result = (await queryExecutor.QueryAsync<ReportViewFieldsDTO>(LoginDTO, query, parameters)).ToList();
            _fieldCache.Set(cacheKey, result, TimeSpan.FromMinutes(5));
            return result;
        }

        public async Task<ReportViewMetaDTO> GetReportViewMeta(int reportViewId, LoginDTO loginDTO, CancellationToken ct)
        {
            var parameters = new Dictionary<string, object> { { "@ReportViewId", reportViewId } };
            return await queryExecutor
                .QuerySingleAsync<ReportViewMetaDTO>(loginDTO, ReportQB.GET_REPORT_VIEW_META, parameters, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task<IEnumerable<PivotFieldConfigDTO>> GetPivotFieldConfig(int reportViewId, LoginDTO loginDTO, CancellationToken ct)
        {
            var parameters = new Dictionary<string, object> { { "@ReportViewId", reportViewId } };
            return await queryExecutor
                .QueryAsync<PivotFieldConfigDTO>(loginDTO, ReportQB.GET_PIVOT_FIELD_CONFIG, parameters, cancellationToken: ct)
                .ConfigureAwait(false);
        }

        public async Task<object> ExportAsync(
            int formatCode,
            ReportCallingDTO reportCallingDTO,
            LoginDTO loginDTO,
            IAsyncEnumerable<Dictionary<string, object?>> rows,
            CancellationToken ct)
        {
            return formatCode switch
            {
                ReportFormat.Excel      => await ExcelExport(reportCallingDTO, loginDTO, rows, ct).ConfigureAwait(false),
                ReportFormat.Csv        => await CSVExport(reportCallingDTO, loginDTO, rows, ct).ConfigureAwait(false),
                ReportFormat.Pdf        => await PdfExport(reportCallingDTO, loginDTO, rows, ct).ConfigureAwait(false),
                ReportFormat.Json       => (object)await JsonReport(reportCallingDTO, loginDTO, rows, ct).ConfigureAwait(false),
                ReportFormat.ReportData => await BuildReportDataAsync(reportCallingDTO, loginDTO, rows, ct).ConfigureAwait(false),
                ReportFormat.Html       => await HtmlExport(reportCallingDTO, loginDTO, rows, ct).ConfigureAwait(false),
                ReportFormat.PdfNative  => await PdfNativeExport(reportCallingDTO, loginDTO, rows, ct).ConfigureAwait(false),
                _ => throw new InvalidOperationException($"Report format code {formatCode} has no export handler.")
            };
        }

        private async Task<object> BuildReportDataAsync(
            ReportCallingDTO reportCallingDTO,
            LoginDTO loginDTO,
            IAsyncEnumerable<Dictionary<string, object?>> rows,
            CancellationToken ct)
        {
            var metaTask   = GetReportViewMeta(reportCallingDTO.ReportViewId, loginDTO, ct);
            var fieldsTask = GetReportViewField(reportCallingDTO.ReportViewId, reportCallingDTO.FieldIsDisplay, loginDTO);

            var collected = new List<Dictionary<string, object?>>();
            await foreach (var row in rows.WithCancellation(ct).ConfigureAwait(false))
                collected.Add(row);

            await Task.WhenAll(metaTask, fieldsTask).ConfigureAwait(false);

            var reportStandardDTO = await FillReportStandardDTO(
                loginDTO,
                reportCallingDTO.CriteriaDTO,
                reportCallingDTO.ReportFormatId,
                reportCallingDTO.ReportId,
                reportCallingDTO.ReportViewId).ConfigureAwait(false);

            reportStandardDTO.ReportTitle = await ReportTitle(
                reportCallingDTO.ReportTitle, reportCallingDTO.CriteriaDTO!, loginDTO).ConfigureAwait(false);
            reportStandardDTO.ReportTitleOptional = await ReportTitleOptional(
                reportCallingDTO.CriteriaDTO!, loginDTO).ConfigureAwait(false);
            reportStandardDTO.ReportHeader.ReportTitle = reportStandardDTO.ReportTitle;
            reportStandardDTO.ReportHeader.ReportTitleOptional = reportStandardDTO.ReportTitleOptional;

            return new
            {
                ReportTitle = reportCallingDTO.ReportTitle,
                Meta        = metaTask.Result,
                Columns     = fieldsTask.Result,
                Rows        = collected,
                Report      = reportStandardDTO
            };
        }
    }
}