using System.Collections;
using System.Globalization;
using System.Management;
using System.Runtime.CompilerServices;
using System.Text.Json;
using DocumentFormat.OpenXml.Drawing.Diagrams;
using DocumentFormat.OpenXml.Spreadsheet;
using FrameworkDAL.CustomCode.CommonReport;
using FrameworkDAL.DTO.Enum;
using FrameworkDAL.DTO.ReportViewer;
using GB5Shared.DTO.Framework.Criteria;
using ReportHeader = GB5Shared.DTO.Report.ReportHeader;
using ReportViewDTO = GB5Shared.DTO.Report.ReportViewDTO;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Report;
using GB5Shared.ExcelExport;
using GB5Shared.Export.CSVExport;
using GB5Shared.Export.HybridReport;
using SharedGlobalBrowser = GB5Shared.Export.HybridReport.GlobalBrowser;
using GB5Shared.Export.Pivot;
using GB5Shared.GB5CommonFunction;
using GB5Shared.GB5Exception;
using HandlebarsDotNet;
using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using PdfSharpCore.Pdf;
using PdfSharpCore.Pdf.IO;
using PuppeteerSharp;
using PuppeteerSharp.BrowserData;
using PuppeteerSharp.Media;

namespace FrameworkBLL.CommonReport
{
    public class CommonReportBLL : ICommonReportBLL
    {
        private readonly ICommonReportDAL CommonReportDAL;
        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 GB5Shared.Export.NativePdf.INativePdfExport _nativePdfExport;
        private readonly IMemoryCache _fieldCache;

        public CommonReportBLL(
            ICommonReportDAL ICommonReportDAL,
            ICSVExport ICSVExport,
            IExcelExport ExcelExport,
            IGB5CommonFunction IGB5CommonFunction,
            IConfiguration configuration,
            IDataPivotEngine dataPivotEngine,
            IPivotExcelExport pivotExcelExport,
            IPivotPdfExport pivotPdfExport,
            GB5Shared.Export.NativePdf.INativePdfExport nativePdfExport,
            IMemoryCache fieldCache)
        {
            CommonReportDAL = ICommonReportDAL;
            IExcelExport = ExcelExport;
            GB5CommonFunction = IGB5CommonFunction;
            _CSVExport = ICSVExport;
            _configuration = configuration;
            _dataPivotEngine = dataPivotEngine;
            _pivotExcelExport = pivotExcelExport;
            _pivotPdfExport = pivotPdfExport;
            _nativePdfExport = nativePdfExport;
            _fieldCache = fieldCache;
        }

        private async Task<List<ReportViewFieldsDTO>> GetCachedReportViewFieldAsync(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 result = await CommonReportDAL.GetReportViewField(reportViewId, fieldIsDisplay, loginDTO);
            _fieldCache.Set(cacheKey, result, TimeSpan.FromMinutes(5));
            return result;
        }

        public async Task<IEnumerable<PivotFieldConfigDTO>> GetPivotFieldConfig(
            int reportViewId, LoginDTO loginDTO, CancellationToken cancellationToken)
        {
            return await CommonReportDAL
                .GetPivotFieldConfig(reportViewId, loginDTO, cancellationToken)
                .ConfigureAwait(false);
        }

        // 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) && System.IO.File.Exists(configuredPath)
                ? configuredPath
                : bundledPath;
        }

        private async Task<(PivotResultDTO pivot, List<PivotFieldConfigDTO> fieldConfig)> BuildPivotAsync(
            ReportCallingDTO req, LoginDTO login, CancellationToken ct)
        {
            var fieldConfig = (await CommonReportDAL
                .GetPivotFieldConfig(req.ReportViewId, login, ct)
                .ConfigureAwait(false)).ToList();
            var rawRows = IExcelExport.ReportURICalling(req, login, ct);
            var pivot = await _dataPivotEngine
                .TransformAsync(rawRows, fieldConfig, ct: ct)
                .ConfigureAwait(false);
            return (pivot, fieldConfig);
        }

        public async Task<object> ExcelExport(
                ReportCallingDTO ReportCallingDTO,
                LoginDTO LoginDTO,
                CancellationToken cancellationToken)
        {
            var meta = await CommonReportDAL
                .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).ConfigureAwait(false);
                return await _pivotExcelExport.ExportAsync(pivot, reportStandardDTO, headers, LoginDTO, cancellationToken).ConfigureAwait(false);
            }

            // ↓ Existing flat-report Excel path — untouched
            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);

            var ReportViewFieldsDTOs = await GetCachedReportViewFieldAsync(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 IExcelExport.Export(
                      IExcelExport.ReportURICalling(ReportCallingDTO, LoginDTO, cancellationToken)
                    , flatHeaders.ToAsyncEnumerable(), ColumnTitles.ToAsyncEnumerable(), ColumnFieldNames.ToAsyncEnumerable(), ms, LoginDTO, "Report", cancellationToken, ReportViewFieldsDTOs);

            return ms.ToArray();
        }

        // -----------------------------
        // NATIVE PDF EXPORT (PdfSharpCore, no browser) — an additional, explicit option
        // (FILEFORMAT.PDFNATIVE) alongside PdfExport below (Puppeteer), not a replacement for it.
        // Mirrors the flat-report ExcelExport path above (no chunking needed — PdfSharpCore streams
        // pages directly, unlike the Puppeteer pipeline's chunk-then-merge design) and the sibling
        // ReportExport.PdfNativeExportCore implementation in the BaseReportEndpoint pipeline.
        // -----------------------------
        public async Task<object> PdfNativeExport(
                ReportCallingDTO ReportCallingDTO,
                LoginDTO LoginDTO,
                CancellationToken cancellationToken)
        {
            var meta = await CommonReportDAL
                .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 (PdfExport below) already
            // uses — reused as-is (raw bytes, not base64 data-URI, since XImage.FromStream wants bytes
            // directly). DB-driven per-company/per-OU logos are a separate, later phase.
            byte[]? logoBytes = null;
            var pdfConfig = _configuration.GetSection("PDFEXPORT");
            var logoPath = ResolveConfiguredOrBundled(pdfConfig.GetValue<string>("LogoPath"), ReportTemplateResolver.LogoPath);
            if (!string.IsNullOrWhiteSpace(logoPath) && System.IO.File.Exists(logoPath))
                logoBytes = await System.IO.File.ReadAllBytesAsync(logoPath, cancellationToken);

            var ReportViewFieldsDTOs = await GetCachedReportViewFieldAsync(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 _nativePdfExport.Export(
                IExcelExport.ReportURICalling(ReportCallingDTO, LoginDTO, cancellationToken),
                headers.ToAsyncEnumerable(), ColumnTitles.ToAsyncEnumerable(), ColumnFieldNames.ToAsyncEnumerable(),
                ms, LoginDTO, logoBytes, cancellationToken, ReportViewFieldsDTOs.ToList());

            return ms.ToArray();
        }

        public async Task<object> CSVExport(
               ReportCallingDTO ReportCallingDTO,
               LoginDTO LoginDTO,
               CancellationToken cancellationToken)
        {
            var meta = await CommonReportDAL
                .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.");

            // ↓ Existing flat-report CSV path — untouched
            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 GetCachedReportViewFieldAsync(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(
                      IExcelExport.ReportURICalling(ReportCallingDTO, LoginDTO, cancellationToken)
                    , headers.ToAsyncEnumerable(), ColumnTitles.ToAsyncEnumerable(), ColumnFieldNames.ToAsyncEnumerable(), ms, "Report", cancellationToken);

            return ms.ToArray();
        }

        public async Task<string> JsonReport(
                ReportCallingDTO ReportCallingDTO,
                LoginDTO LoginDTO,
                CancellationToken cancellationToken)
        {
            var meta = await CommonReportDAL
                .GetReportViewMeta(ReportCallingDTO.ReportViewId, LoginDTO, cancellationToken)
                .ConfigureAwait(false);

            if (meta.ReportViewType == (int)FrameworkEnumDTO.ReportViewType.Pivot)
            {
                if (ReportCallingDTO.IsPivotView == 0)
                {
                    // FE-driven: return all flat rows + field config
                    // FE pivot component handles dimension assignment, drag-drop, charts
                    var fieldConfig = (await CommonReportDAL
                        .GetPivotFieldConfig(ReportCallingDTO.ReportViewId, LoginDTO, cancellationToken)
                        .ConfigureAwait(false)).ToList();

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

                    return Newtonsoft.Json.JsonConvert.SerializeObject(new { fields = fieldConfig, rows = allRows });
                }
                else
                {
                    // BE DataPivot: transform → pivot-shaped JSON
                    // FE receives dynamic column keys and displays as grid; user can sort/filter/chart
                    var (pivot, _) = await BuildPivotAsync(ReportCallingDTO, LoginDTO, cancellationToken)
                        .ConfigureAwait(false);
                    return Newtonsoft.Json.JsonConvert.SerializeObject(new
                    {
                        rowHeaders   = pivot.RowHeaders,
                        pivotColumns = pivot.PivotColumns,
                        rows         = pivot.Rows,
                        grandTotal   = pivot.GrandTotal
                    });
                }
            }

            // ↓ Existing flat-report JSON path
            // IAsyncEnumerable cannot be serialized directly — drain into list first
            var flatRows = new List<Dictionary<string, object?>>();
            await foreach (var row in IExcelExport.ReportURICalling(ReportCallingDTO, LoginDTO, cancellationToken)
                .ConfigureAwait(false))
                flatRows.Add(row);

            return Newtonsoft.Json.JsonConvert.SerializeObject(flatRows);
        }

        // -----------------------------
        // NEW: PDF EXPORT (full)
        // -----------------------------
        public async Task<object> PdfExport(ReportCallingDTO reportCallingDTO, LoginDTO loginDTO, CancellationToken cancellationToken)
        {
            var meta = await CommonReportDAL
                .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;

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

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

            // ↓ Existing flat-report PDF path — untouched
            try
            {
                // ===== 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.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;
                }

                // ===== 3. Dynamic columns and metadata =====
                var reportViewFields = await GetCachedReportViewFieldAsync(reportCallingDTO.ReportViewId, reportCallingDTO.FieldIsDisplay, loginDTO);

                var mainFields = reportViewFields.Where(f => f.ReportViewFieldsRowLevel == 0).OrderBy(f => f.ReportViewFieldsDisplaySlNo).ToList();
                var subFields = reportViewFields.Where(f => f.ReportViewFieldsRowLevel > 0).OrderBy(f => f.ReportViewFieldsDisplaySlNo).ToList();

                var subToParentMap = new Dictionary<string, string>();
                foreach (var sub in subFields)
                {
                    var parent = mainFields.Where(m => m.ReportViewFieldsDisplaySlNo < sub.ReportViewFieldsDisplaySlNo)
                                           .OrderByDescending(m => m.ReportViewFieldsDisplaySlNo)
                                           .FirstOrDefault();
                    if (parent != null) subToParentMap[sub.ReportViewFieldsFieldTitle] = parent.ReportViewFieldsFieldTitle;
                }

                var pdfColumnHeaders = reportViewFields.Select(f => f.ReportViewFieldsFieldTitle).ToList();
                var fieldMap = reportViewFields.ToDictionary(f => f.ReportViewFieldsFieldTitle, f => f.ReportVsFieldsFieldName);

                var rowLevelFieldsByLevel = reportViewFields
                    .Where(f => f.ReportViewFieldsRowLevel > 0)
                    .GroupBy(f => f.ReportViewFieldsRowLevel)
                    .OrderBy(g => g.Key)
                    .ToDictionary(g => g.Key, g => g.Select(x => x.ReportViewFieldsFieldTitle).ToList());

                int maxRowLevel = rowLevelFieldsByLevel.Any() ? rowLevelFieldsByLevel.Keys.Max() : 0;

                var fieldRowLevelMap = reportViewFields.ToDictionary(f => f.ReportViewFieldsFieldTitle, f => f.ReportViewFieldsRowLevel);

                // ===== 4. Stream rows from source and build chunk-friendly rows =====
                // Instead of loading ALL rows into memory, we accumulate rows per chunk file.
                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 = pdfColumnHeaders.Where(h => !fieldRowLevelMap.ContainsKey(h) || fieldRowLevelMap[h] == 0).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.

                // Chunks render concurrently (bounded by GlobalBrowser.RenderGate) instead of one
                // at a time — dispatched below, 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>>();

                // 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 IExcelExport.ReportURICalling(reportCallingDTO, loginDTO, cancellationToken))
                {
                    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);
                       // object? rawValue = record.ContainsKey(fieldName) ? record[fieldName] : null;
                        switch (fieldMeta?.ReportVsFieldsFieldType)
                        {
                            case 3: // date
                                newRow[header] = FormatDateValue(rawValue, fieldMeta.ReportViewFieldsCalFormat);
                                break;
                            default:
                                newRow[header] = rawValue ?? "";
                                break;
                        }
                    }

                    // handle subrows (row-level fields)
                    var subRows = new List<Dictionary<string, object>>();
                    foreach (var lvl in rowLevelFieldsByLevel.OrderBy(kvp => kvp.Key))
                    {
                        int level = lvl.Key;
                        foreach (var title in lvl.Value)
                        {
                            var val = newRow.ContainsKey(title) ? (newRow[title]?.ToString() ?? "") : "";
                            if (!string.IsNullOrWhiteSpace(val))
                            {
                                subRows.Add(new Dictionary<string, object>
                                {
                                    ["RowLevel"] = level,
                                    ["FieldTitle"] = title,
                                    ["Value"] = val,
                                    ["ParentField"] = subToParentMap.ContainsKey(title) ? subToParentMap[title] : null!
                                });
                                newRow.Remove(title);
                            }
                        }
                    }
                    newRow["__subRows"] = subRows;

                    chunkRows.Add(newRow);

                    // When chunk full, dispatch its render (don't block reading the next chunk)
                    if (chunkRows.Count >= rowsPerChunk)
                    {
                        cancellationToken.ThrowIfCancellationRequested();
                        pendingChunkTasks.Add(RenderChunkToPdfFileAsync(
                            chunkRows,
                            chunkIndex++,
                            templatePath,
                            headerPath,
                            footerPath,
                            visibleHeaders,
                            fieldMap,
                            reportStandardDTO,
                            reportCallingDTO,
                            loginDTO,
                            outputDir,
                            reportViewFields,
                            cancellationToken));
                        chunkRows = new List<Dictionary<string, object>>(capacity: rowsPerChunk);
                    }
                }

                // final leftover chunk
                if (chunkRows.Count > 0)
                {
                    cancellationToken.ThrowIfCancellationRequested();
                    pendingChunkTasks.Add(RenderChunkToPdfFileAsync(
                        chunkRows,
                        chunkIndex++,
                        templatePath,
                        headerPath,
                        footerPath,
                        visibleHeaders,
                        fieldMap,
                        reportStandardDTO,
                        reportCallingDTO,
                        loginDTO,
                        outputDir,
                        reportViewFields,
                        cancellationToken));
                }

                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).
                tempPdfPaths.AddRange(await Task.WhenAll(pendingChunkTasks).ConfigureAwait(false));

                // ===== Merge parts into final PDF (streaming merge frees part files) =====
                byte[] 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);

                // 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);
            }
        }
        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;
        }

        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)
        {
            var report = reportStandardDTO as ReportStandardDTO;

            // 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();

            var chunkContext = new
            {
                Header = report?.ReportHeader,
                Footer = report?.ReportFooter,
                Report = reportStandardDTO,
                Criteria = reportCallingDTO.CriteriaDTO,
                ColumnHeaders = visibleHeaders,
                ColumnWidths = new Dictionary<string, double>(),
                Scale = 1.0,
                FieldMap = fieldMapCtx,
                Rows = rowsForChunk,
                Groups = (object?)null,
                HasGrouping = false,
                HasRowLevel = false,
                RowLevelCount = 0,
                RowLevelHeaders = (object?)null,
                FieldRowLevelMap = (object?)null,
                GeneratedOn = DateTime.UtcNow,
                User = loginDTO.UserName,
                EmpInfoFields = empInfoFieldList,
                AttFields = attFieldList
            };

            // 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>";

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

            // Produce PDF bytes using shared browser
            byte[] pdfBytes = await RenderHtmlToPdfBytesUsingSharedBrowserAsync(html, metadata, 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;
        }

        // ---------------- Render HTML -> PDF using shared browser (returns bytes) ----------------
        private static async Task<byte[]> RenderHtmlToPdfBytesUsingSharedBrowserAsync(string html, IDictionary<string, object>? metadata, CancellationToken cancellationToken)
        {
            await SharedGlobalBrowser.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>";

                var pdfOptions = new PdfOptions
                {
                    Format = PaperFormat.A4,
                    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
                    {
                        var browser = await SharedGlobalBrowser.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);

                        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)
                    {
                        await SharedGlobalBrowser.ResetAsync().ConfigureAwait(false);
                        await Task.Delay(TimeSpan.FromSeconds(attempt * 2), cancellationToken).ConfigureAwait(false);
                    }
                }
            }
            finally
            {
                SharedGlobalBrowser.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);
                    }
                });

                hb.RegisterHelper("renderSubRows", (writer, contextObj, parameters) =>
                {
                    if (parameters.Length < 2) return;

                    var rowsObj = parameters[0];
                    if (rowsObj == null) return;

                    if (!(rowsObj is IEnumerable<object> rowsEnum)) return;
                    var rowsList = rowsEnum.OfType<IDictionary<string, object>>().ToList();
                    if (!rowsList.Any()) return;

                    var columnHeaders = parameters[1] as IEnumerable<object>;
                    if (columnHeaders == null) return;

                    // ✅ Group by RowLevel
                    var groupedByRowLevel = rowsList
                        .GroupBy(sub =>
                        {
                            if (sub.TryGetValue("RowLevel", out var rl) && int.TryParse(rl?.ToString(), out var rowLevel))
                                return rowLevel;
                            return 1;
                        })
                        .OrderBy(g => g.Key);

                    foreach (var group in groupedByRowLevel)
                    {
                        int rowLevel = group.Key;

                        // Group by parent field for alignment
                        var groupedByParentField = group
                            .GroupBy(sub =>
                            {
                                if (sub.TryGetValue("ParentField", out var pf))
                                    return pf?.ToString() ?? "";
                                return "";
                            })
                            .ToDictionary(
                                g => g.Key,
                                g => g.Select(sub => new
                                {
                                    Value = sub.TryGetValue("Value", out var val) ? val?.ToString() ?? "" : ""
                                }).ToList()
                            );

                        writer.WriteSafeString("<tr class='row-sub'>");

                        foreach (var col in columnHeaders)
                        {
                            string colName = col?.ToString() ?? "";

                            if (groupedByParentField.TryGetValue(colName, out var subFields))
                            {
                                int padding = 12 * rowLevel;
                                string combined = string.Join("<br>",
                                    subFields.Select(sf => System.Net.WebUtility.HtmlEncode(sf.Value)));

                                writer.WriteSafeString($@"
                    <td style='padding-left:{padding}px; white-space:nowrap; vertical-align:top;'>
                        {combined}
                    </td>");
                            }
                            else
                            {
                                writer.WriteSafeString("<td></td>");
                            }
                        }

                        writer.WriteSafeString("</tr>");
                    }
                });



                // ===== 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);
            }
        }

        // Browser singleton now lives in GB5Shared.Export.HybridReport.GlobalBrowser
        /// <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() ?? "");
        }

        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() ?? ""
            };
        }

        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 CommonReportDAL.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 CommonReportDAL.GetReport(ReportId, LoginDTO);
                    ReportCode = ReportDTO.ReportCode;
                }
                if (ReportViewId != -1)
                {

                    List<ReportViewDTO> ReportViewDTOList = await CommonReportDAL.GetReportView(ReportViewId, LoginDTO);

                    if (!ReportViewDTOList.Any())
                        throw new NotFoundException("Report View not found for given ReportViewId");

                    ReportViewDTO ReportViewDTO = ReportViewDTOList.First();
                    ReportStandardDTO.ReportViewDynamicFooter = string.Join(";", ReportViewDTOList.Select(x => x.ReportViewDynamicFooter));

                }
                #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;
                }
                // Partner logo is fallback — only when customer has no OU/company logo configured
                if (string.IsNullOrEmpty(ReportStandardDTO.CompanyLogo1)
                    && !string.IsNullOrEmpty(ReportStandardDTO.PartnerLogo1))
                {
                    ReportStandardDTO.CompanyLogo1 = ReportStandardDTO.PartnerLogo1;
                }
                #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
            {
                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 = 0;
                                                if (CriteriaDTO.SectionCriteriaList[i].AttributesCriteriaList[z].FieldValue is JsonElement element)
                                                {
                                                    if (element.ValueKind == JsonValueKind.Number)
                                                    {
                                                        unixTime = element.GetInt64();
                                                    }
                                                    else if (element.ValueKind == JsonValueKind.String)
                                                    {
                                                        unixTime = long.Parse(element.GetString()!);
                                                    }
                                                }
                                                else
                                                {
                                                    unixTime = Convert.ToInt64(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 = 0;
                                                if (fieldValue is JsonElement element)
                                                {
                                                    if (element.ValueKind == JsonValueKind.Number)
                                                    {
                                                        unixTime = element.GetInt64();
                                                    }
                                                    else if (element.ValueKind == JsonValueKind.String)
                                                    {
                                                        unixTime = long.Parse(element.GetString()!);
                                                    }
                                                }
                                                else
                                                {
                                                    unixTime = Convert.ToInt64(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 CommonReportDAL.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;
            }
        }
    }
}