using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Runtime.CompilerServices;
using System.Text;
using System.Text.Json;
using System.Threading;
using System.Threading.Tasks;
using ClosedXML.Excel;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Report;
using GB5Shared.Export;
using GB5Shared.GB5Exception;
using GB5Shared.Query.ExcelQuery;
using GB5Shared.QueryExecutor;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;

namespace GB5Shared.ExcelExport
{
    public class ExcelExport : IExcelExport
    {
        private readonly IQueryExecutor _queryExecutor;
        private readonly ILogger<ExcelExport> _logger;
        private readonly IHttpClientFactory _httpClientFactory;
        private readonly int _maxRowsPerSheet;

        private const int MinColumnWidth = 8;
        private const int MaxColumnWidth = 60;

        public ExcelExport(IQueryExecutor queryExecutor, ILogger<ExcelExport> logger,
            IHttpClientFactory httpClientFactory, IConfiguration configuration)
        {
            _queryExecutor = queryExecutor;
            _logger = logger;
            _httpClientFactory = httpClientFactory;
            _maxRowsPerSheet = int.TryParse(configuration["EXCELEXPORT:MaxRowsPerSheet"], out var configured) && configured > 0
                ? configured
                : 1_000_000;
        }

        public async IAsyncEnumerable<Dictionary<string, object?>> ReportURICalling(
          ReportCallingDTO reportCallingDTO,
          LoginDTO? loginDTO,
          [EnumeratorCancellation] CancellationToken cancellationToken)
        {
            using var httpClient = _httpClientFactory.CreateClient("ReportClient");

            // Add login headers
            LoginHeader(httpClient, loginDTO);

            // Prepare request body
            var bodyJson = System.Text.Json.JsonSerializer.Serialize(reportCallingDTO.CriteriaDTO);
            using var content = new StringContent(bodyJson, Encoding.UTF8, "application/json");

            // Call API
            using var response = await httpClient.PostAsync(
                reportCallingDTO.ReportUri,
                content,
                cancellationToken);

            response.EnsureSuccessStatusCode();
            var responseString = await response.Content.ReadAsStringAsync(cancellationToken);
            using var doc = JsonDocument.Parse(responseString);

            if (doc.RootElement.ValueKind == JsonValueKind.Array)
            {
                foreach (var element in doc.RootElement.EnumerateArray())
                    yield return ToDictionary(element);
            }
            else if (doc.RootElement.ValueKind == JsonValueKind.Object)
            {
                // Unwrap ResponseStandardDTO: if root has a "Body" array, yield each Body item
                if (doc.RootElement.TryGetProperty("Body", out var bodyEl) &&
                    bodyEl.ValueKind == JsonValueKind.Array)
                {
                    foreach (var element in bodyEl.EnumerateArray())
                        yield return ToDictionary(element);
                }
                else
                {
                    yield return ToDictionary(doc.RootElement);
                }
            }
        }

        /// <summary>
        /// Convert JsonElement -> Dictionary<string, object?>
        /// </summary>
        private static Dictionary<string, object?> ToDictionary(JsonElement element)
        {
            var dict = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);

            foreach (var prop in element.EnumerateObject())
            {
                dict[prop.Name] = prop.Value.ValueKind switch
                {
                    JsonValueKind.String => prop.Value.GetString(),
                    JsonValueKind.Number => prop.Value.TryGetInt64(out var l) ? l : prop.Value.GetDecimal(),
                    JsonValueKind.True => true,
                    JsonValueKind.False => false,
                    JsonValueKind.Null => null,
                    JsonValueKind.Object => ToDictionary(prop.Value), // nested object
                    JsonValueKind.Array => prop.Value.EnumerateArray()
                                                        .Select<JsonElement, object?>(v => v.ValueKind switch
                                                        {
                                                            JsonValueKind.Object => ToDictionary(v),
                                                            JsonValueKind.String => v.GetString(),
                                                            JsonValueKind.Number => v.TryGetInt64(out var ln) ? ln : v.GetDecimal(),
                                                            JsonValueKind.True   => true,
                                                            JsonValueKind.False  => false,
                                                            JsonValueKind.Null   => null,
                                                            _                    => v.ToString()
                                                        })
                                                        .ToList(),
                    _ => prop.Value.ToString()
                };
            }

            return dict;
        }

        private static void LoginHeader(HttpClient httpClient, LoginDTO? loginDTO)
        {
            var loginJson = System.Text.Json.JsonSerializer.Serialize(loginDTO);

            if (httpClient.DefaultRequestHeaders.Contains("Login"))
                    httpClient.DefaultRequestHeaders.Remove("Login");
            httpClient.DefaultRequestHeaders.Add("Login", loginJson);
        }

        public async Task<IEnumerable<ReportViewExcelHeaderDTO>> GetReportViewHeader(int reportViewId, ReportCallingDTO reportCalling, LoginDTO loginDTO)
        {
            try
            {
                var headers = await _queryExecutor.QueryAsync<ReportViewExcelHeaderDTO>(
                    loginDTO,
                    ExcelQueryQB.GET_REPORTVIEW_HEADER,
                    new { reportviewid = reportViewId });

                if (!headers.Any())
                {
                    _logger.LogWarning("No headers found for ReportView ID: {ReportViewId}", reportViewId);
                    return Enumerable.Empty<ReportViewExcelHeaderDTO>();
                }

                var firstHeader = headers.First();
                if (firstHeader.IsHeaderRequired == 0 && firstHeader.ReportHeaderId == -1)
                {
                    throw new MethodNotAllowedException("Report view header is applicable but no header selected.");
                }

                return headers;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Database error retrieving headers for ReportView ID: {ReportViewId}", reportViewId);
                throw;
            }
        }

        public string ContentType => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        public string FileExtension => ".xlsx";

        public async Task Export(
              IAsyncEnumerable<Dictionary<string, object?>> records,
              IAsyncEnumerable<ReportViewExcelHeaderDTO> mainHeaders,
              IAsyncEnumerable<string> columnTitles,
              IAsyncEnumerable<string> columnFieldNames,
              Stream outputStream,
              LoginDTO login,
              string sheetName = "Report",
              CancellationToken cancellationToken = default,
              List<ReportViewFieldsDTO>? reportViewFields = null)
        {
            var theme = ReportTheme.FromLogin(login);
            reportViewFields ??= new List<ReportViewFieldsDTO>();

            // 1. Materialize headers/titles/field names
            var headers = new List<ReportViewExcelHeaderDTO>();
            await foreach (var h in mainHeaders.WithCancellation(cancellationToken))
                headers.Add(h);

            var titles = new List<string>();
            await foreach (var t in columnTitles.WithCancellation(cancellationToken))
                titles.Add(t);

            var fieldNames = new List<string>();
            await foreach (var f in columnFieldNames.WithCancellation(cancellationToken))
                fieldNames.Add(f);

            // Array/sub-row groups (see ReportSubRowResolver) — same MREPORTVIEWFIELDS convention as
            // the PDF/HTML export path (ReportExport.cs). A field only participates if it's actually
            // configured in this report view's field catalog with RowLevel>0 and a dotted FieldName
            // ("VoucherInstrumentArray.InstrumentNumber") — never automatic for every array property
            // a row happens to carry. These titles/field names must never appear as ordinary flat
            // columns (their value is a whole list, not a scalar), so they're dropped from
            // titles/fieldNames here before anything downstream (colIndexByTitle, fieldMetaByTitle,
            // the main write loop) ever sees them.
            var subRowRootGroups = ReportSubRowResolver.BuildGroupTree(reportViewFields);
            var subRowFieldTitles = new HashSet<string>(
                reportViewFields.Where(f => f.ReportViewFieldsRowLevel > 0
                                             && !string.IsNullOrWhiteSpace(f.ReportVsFieldsFieldName)
                                             && f.ReportVsFieldsFieldName.Contains('.'))
                                .Select(f => f.ReportViewFieldsFieldTitle),
                StringComparer.OrdinalIgnoreCase);
            if (subRowFieldTitles.Count > 0)
            {
                var keptIndices = Enumerable.Range(0, Math.Min(titles.Count, fieldNames.Count))
                    .Where(i => !subRowFieldTitles.Contains(titles[i]))
                    .ToList();
                titles = keptIndices.Select(i => titles[i]).ToList();
                fieldNames = keptIndices.Select(i => fieldNames[i]).ToList();
            }

            // A group/subtotal/grandtotal/merge key field can be intentionally hidden from the visible
            // column list (ISDISPLAY) — e.g. grouping by a date/voucher-number the group header banner
            // already shows — but BuildGroupNodes/ColumnAggregator below still look its value up BY
            // TITLE on each row. Without this, a hidden group field silently produces no grouping at
            // all in the export (title never present in rowByTitle → every row falls into one empty-key
            // bucket) even though the same field works correctly in the live Grid, which reads column
            // config through a different path unaffected by ISDISPLAY. reportViewFields must be the
            // FULL field list here (not display-filtered) for this to find anything — see the caller.
            var structuralOnlyFields = reportViewFields
                .Where(f => (f.ReportViewFieldsIsGroupcolumn == 0 || f.ReportViewFieldsIsSubTotal == 0
                             || f.ReportViewFieldsIsGrandTotal == 0 || f.ReportViewFieldsIsMergeColumn == 0)
                            && !string.IsNullOrWhiteSpace(f.ReportViewFieldsFieldTitle)
                            && !titles.Contains(f.ReportViewFieldsFieldTitle, StringComparer.OrdinalIgnoreCase))
                .GroupBy(f => f.ReportViewFieldsFieldTitle, StringComparer.OrdinalIgnoreCase)
                .Select(g => g.First())
                .ToList();

            // 2. Buffer records and reshape into title-keyed rows — needed up front to build the
            // multi-level group tree (ClosedXML fully buffers the workbook in memory regardless, so
            // this isn't a new memory-cost regression over what SaveAs already required).
            var rowsByTitle = new List<Dictionary<string, object>>();
            await foreach (var record in records.WithCancellation(cancellationToken))
            {
                var rowByTitle = new Dictionary<string, object>(StringComparer.OrdinalIgnoreCase);
                for (int col = 0; col < titles.Count && col < fieldNames.Count; col++)
                {
                    record.TryGetValue(fieldNames[col], out var v);
                    rowByTitle[titles[col]] = v!;
                }
                foreach (var f in structuralOnlyFields)
                {
                    record.TryGetValue(f.ReportVsFieldsFieldName, out var v);
                    rowByTitle[f.ReportViewFieldsFieldTitle] = v!;
                }

                if (subRowRootGroups.Count > 0)
                {
                    // Raw values passed straight through (not pre-formatted to text) — WriteSubRowGroups
                    // applies the same typed WriteValueCell formatting real columns get, using each
                    // field's own ReportViewFieldsDTO looked up from fieldMetaByTitle at write time.
                    var groups = ReportSubRowResolver.ResolveForRow(subRowRootGroups, record, (raw, _) => raw);
                    if (groups.Count > 0) rowByTitle["__subRowGroups"] = groups;
                }

                rowsByTitle.Add(rowByTitle);
            }

            var colIndexByTitle = titles
                .Select((t, i) => (t, i))
                .GroupBy(x => x.t, StringComparer.OrdinalIgnoreCase)
                .ToDictionary(g => g.Key, g => g.First().i + 1, StringComparer.OrdinalIgnoreCase);

            var fieldMetaByTitle = reportViewFields
                .Where(f => !string.IsNullOrEmpty(f.ReportViewFieldsFieldTitle))
                .GroupBy(f => f.ReportViewFieldsFieldTitle, StringComparer.OrdinalIgnoreCase)
                .ToDictionary(g => g.Key, g => g.First(), StringComparer.OrdinalIgnoreCase);

            // "0 = yes" convention, matching every other consumer of these flags in this codebase.
            var groupColumns = reportViewFields
                .Where(f => f.ReportViewFieldsIsGroupcolumn == 0)
                .OrderBy(f => f.ReportViewFieldsDisplaySlNo)
                .ToList();
            var subTotalFields = reportViewFields.Where(f => f.ReportViewFieldsIsSubTotal == 0).ToList();
            var grandTotalFields = reportViewFields.Where(f => f.ReportViewFieldsIsGrandTotal == 0).ToList();
            // Merge-flagged fields, excluding whatever is already a group column at any level (those are
            // merged via their own LevelTitle span instead, see WriteGroupNodeRecursive).
            var mergeFields = reportViewFields
                .Where(f => f.ReportViewFieldsIsMergeColumn == 0
                            && !groupColumns.Any(g => string.Equals(g.ReportViewFieldsFieldTitle, f.ReportViewFieldsFieldTitle, StringComparison.OrdinalIgnoreCase)))
                .ToList();
            bool hasGrouping = groupColumns.Count > 0;

            using var workbook = new XLWorkbook();
            string baseSheetName = SanitizeSheetName(string.IsNullOrWhiteSpace(sheetName) ? "Report" : sheetName);

            IXLWorksheet NewSheet(int index) =>
                workbook.Worksheets.Add(index == 0 ? baseSheetName : SanitizeSheetName($"{baseSheetName} ({index + 1})"));

            int EmitSheetPrelude(IXLWorksheet ws)
            {
                int currentRow = 1;
                foreach (var h in headers)
                {
                    var cell = ws.Cell(currentRow, h.FromColumnNumber);
                    cell.Value = h.Expression ?? string.Empty;

                    if (h.ToColumnNumber > h.FromColumnNumber)
                    {
                        var range = ws.Range(currentRow, h.FromColumnNumber, currentRow, h.ToColumnNumber);
                        range.Merge();
                        ExcelStyleHelpers.ApplyReportHeaderRowStyle(range.Style, h, theme);
                    }
                    else
                    {
                        ExcelStyleHelpers.ApplyReportHeaderRowStyle(cell.Style, h, theme);
                    }
                    currentRow++;
                }

                if (titles.Count > 0)
                {
                    var genRange = ws.Range(currentRow, 1, currentRow, titles.Count);
                    genRange.Merge();
                    genRange.FirstCell().Value = $"Generated on {DateTime.UtcNow:dd-MMM-yyyy HH:mm} UTC";
                    ExcelStyleHelpers.ApplyGeneratedOnRowStyle(genRange.Style, theme);
                }
                currentRow++;

                for (int col = 0; col < titles.Count; col++)
                {
                    var cell = ws.Cell(currentRow, col + 1);
                    cell.Value = titles[col];
                    ExcelStyleHelpers.ApplyProfessionalHeaderStyle(cell.Style, theme);
                }
                ExcelStyleHelpers.ApplyFreezeHeader(ws, currentRow);
                currentRow++;
                return currentRow;
            }

            void AdjustColumnWidths(IXLWorksheet ws)
            {
                for (int col = 1; col <= titles.Count; col++)
                {
                    try
                    {
                        ws.Column(col).AdjustToContents();
                        ws.Column(col).Width = Math.Clamp(ws.Column(col).Width, MinColumnWidth, MaxColumnWidth);
                    }
                    catch { /* AdjustToContents can throw on pathological content; keep the default width */ }
                }
            }

            static bool TryToDateTime(object? value, out DateTime dt)
            {
                dt = default;
                if (value is DateTime d) { dt = d; return true; }
                var s = value?.ToString();
                if (string.IsNullOrWhiteSpace(s)) return false;
                // Mirrors ReportExport.FormatDateValue's parse order (ISO/offset first, then plain), but
                // returns a typed value for a native Excel cell instead of a pre-formatted display string.
                if (DateTimeOffset.TryParse(s, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal, out var dto))
                { dt = dto.Date; return true; }
                return DateTime.TryParse(s, CultureInfo.InvariantCulture, DateTimeStyles.None, out dt);
            }

            static bool TryToDecimal(object? value, out decimal d) =>
                decimal.TryParse(value?.ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out d);

            void WriteValueCell(IXLCell cell, object? value, ReportViewFieldsDTO? meta)
            {
                byte fieldType = meta?.ReportVsFieldsFieldType ?? byte.MaxValue;

                if (value == null || (value is string sv && sv.Length == 0))
                {
                    cell.Value = string.Empty;
                }
                else if (fieldType == 3 && TryToDateTime(value, out var dt))
                {
                    cell.Value = dt;
                    cell.Style.DateFormat.Format = ExcelStyleHelpers.ToExcelNumberFormat(meta?.ReportViewFieldsCalFormat, fieldType);
                }
                else if ((fieldType == 0 || fieldType == 6) && TryToDecimal(value, out var num))
                {
                    cell.Value = (double)num;
                    cell.Style.NumberFormat.Format = ExcelStyleHelpers.ToExcelNumberFormat(meta?.ReportViewFieldsCalFormat, fieldType);
                }
                else
                {
                    cell.Value = value.ToString();
                }

                cell.Style.Alignment.Horizontal = ExcelStyleHelpers.ToExcelAlignment(meta?.ReportViewFieldsAlignment ?? 0, fieldType);
            }

            void WriteDataRow(IXLWorksheet ws, int row, Dictionary<string, object> rowByTitle, int zebraIndex)
            {
                for (int col = 0; col < titles.Count; col++)
                {
                    var title = titles[col];
                    rowByTitle.TryGetValue(title, out var value);
                    fieldMetaByTitle.TryGetValue(title, out var meta);
                    var cell = ws.Cell(row, col + 1);
                    WriteValueCell(cell, value, meta);
                    ExcelStyleHelpers.ApplyBandedRowStyle(cell.Style, zebraIndex);
                }
            }

            // Writes one or more stacked array-group mini-tables directly beneath a data row —
            // mirrors ReportExport.cs's RenderSubRowGroupsHtml (see ReportSubRowResolver), just as
            // real worksheet rows/cells instead of HTML. Each group gets its own bold header row
            // (its own field titles only, indented one column further than its parent), then one
            // row per array item using the same typed WriteValueCell formatting real columns get.
            // Recurses into NestedGroups, indenting one column further each level, for embedded
            // arrays. Returns the next free row after everything this call wrote.
            int WriteSubRowGroups(IXLWorksheet ws, int row, IEnumerable<ReportSubRowResolver.ResolvedGroupResult> groups, int indentCols)
            {
                foreach (var group in groups)
                {
                    if (group.Rows.Count == 0) continue;

                    for (int c = 0; c < group.ColumnTitles.Count; c++)
                    {
                        var headerCell = ws.Cell(row, indentCols + c + 1);
                        headerCell.Value = group.ColumnTitles[c];
                        headerCell.Style.Font.Bold = true;
                        headerCell.Style.Font.FontSize = 9;
                    }
                    row++;

                    foreach (var subRow in group.Rows)
                    {
                        for (int c = 0; c < group.ColumnTitles.Count; c++)
                        {
                            var title = group.ColumnTitles[c];
                            subRow.Values.TryGetValue(title, out var value);
                            fieldMetaByTitle.TryGetValue(title, out var meta);
                            var cell = ws.Cell(row, indentCols + c + 1);
                            WriteValueCell(cell, value, meta);
                            cell.Style.Font.FontSize = 9;
                        }
                        row++;

                        if (subRow.NestedGroups.Count > 0)
                            row = WriteSubRowGroups(ws, row, subRow.NestedGroups, indentCols + 1);
                    }
                }
                return row;
            }

            void WriteTotalsRowNative(IXLWorksheet ws, int row, string label, Dictionary<string, object?> totals, bool isGrandTotal)
            {
                for (int col = 0; col < titles.Count; col++)
                {
                    var cell = ws.Cell(row, col + 1);
                    if (col == 0)
                    {
                        cell.Value = label;
                    }
                    else
                    {
                        var title = titles[col];
                        fieldMetaByTitle.TryGetValue(title, out var meta);
                        totals.TryGetValue(title, out var value);
                        WriteValueCell(cell, value, meta);
                    }
                    if (isGrandTotal) ExcelStyleHelpers.ApplyGrandTotalStyle(cell.Style);
                    else ExcelStyleHelpers.ApplySubtotalStyle(cell.Style);
                }
            }

            Dictionary<string, object?> ComputeAggregateRow(IEnumerable<Dictionary<string, object>> rows, List<ReportViewFieldsDTO> fields)
            {
                var result = new Dictionary<string, object?>(StringComparer.OrdinalIgnoreCase);
                var materialized = rows as IList<Dictionary<string, object>> ?? rows.ToList();
                foreach (var f in fields)
                {
                    var title = f.ReportViewFieldsFieldTitle ?? "";
                    var agg = new ColumnAggregator(f.ReportViewFieldsAggregationType, f.ReportVsFieldsFieldType);
                    foreach (var row in materialized)
                    {
                        row.TryGetValue(title, out var v);
                        agg.Accumulate(v);
                    }
                    result[title] = agg.Result;
                }
                return result;
            }

            // physicalRows[i] is the actual worksheet row rowsInRange[i] landed on — NOT assumed to be
            // startRow+i, since a row with sub-row-groups pushes every later row down by however many
            // extra lines its groups took, breaking that contiguous assumption.
            void ApplyMergeRunsFlat(IXLWorksheet ws, IReadOnlyList<int> physicalRows, IReadOnlyList<Dictionary<string, object>> rowsInRange)
            {
                if (rowsInRange.Count < 2) return;
                foreach (var f in mergeFields)
                {
                    var title = f.ReportViewFieldsFieldTitle ?? "";
                    if (!colIndexByTitle.TryGetValue(title, out var col)) continue;

                    int runStart = 0;
                    string? runValue = rowsInRange[0].GetValueOrDefault(title)?.ToString();
                    for (int i = 1; i <= rowsInRange.Count; i++)
                    {
                        string? v = i < rowsInRange.Count ? rowsInRange[i].GetValueOrDefault(title)?.ToString() : null;
                        if (i == rowsInRange.Count || v != runValue)
                        {
                            if (i - runStart > 1)
                            {
                                var range = ws.Range(physicalRows[runStart], col, physicalRows[i - 1], col);
                                range.Merge();
                                range.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                            }
                            runStart = i;
                            runValue = v;
                        }
                    }
                }
            }

            // Recursively writes a GroupTreeBuilder node: an explicit "LevelTitle: LevelValue" banner
            // row first — unconditionally, regardless of row count or whether the group column is a
            // visible column — then leaf rows or child nodes, group-invariant merge-flagged fields
            // (leaf level only), native outline grouping over the span, and finally this level's Sub
            // Total row (left un-merged/un-grouped, so it stays visible when the detail rows above it
            // are collapsed — matching Excel's own built-in Outline/Subtotal feature).
            //
            // The banner row is the Excel equivalent of ReportExport.cs's RenderNode/.group-header row
            // (PDF/HTML) — added because relying solely on merging the group-key column (the original
            // design here) silently shows nothing when that column is ISDISPLAY-hidden (a common,
            // deliberate config — the banner is meant to make the repeating column redundant, see Phase
            // 9a) or when every group happens to be exactly one row (no span to merge). Both are real,
            // reported cases: PDF/HTML render an unconditional banner regardless, Excel rendered nothing.
            int WriteGroupNodeRecursive(IXLWorksheet ws, int currentRow, object nodeObj, ref int zebraIndex, int level)
            {
                if (nodeObj is not Dictionary<string, object> node) return currentRow;

                string levelTitle = node.TryGetValue("LevelTitle", out var lt) ? lt?.ToString() ?? "" : "";
                string levelValueText = node.TryGetValue("LevelValue", out var lv) ? lv?.ToString() ?? "" : "";
                bool isLeaf = node.TryGetValue("IsLeaf", out var ilObj) && ilObj is bool ilb && ilb;

                int headerRow = currentRow;
                if (titles.Count > 0)
                {
                    var headerRange = ws.Range(headerRow, 1, headerRow, titles.Count);
                    headerRange.Merge();
                    headerRange.FirstCell().Value = $"{levelTitle}: {levelValueText}";
                    ExcelStyleHelpers.ApplyGroupHeaderStyle(headerRange.Style, level);
                }
                else
                {
                    ws.Cell(headerRow, 1).Value = $"{levelTitle}: {levelValueText}";
                    ExcelStyleHelpers.ApplyGroupHeaderStyle(ws.Cell(headerRow, 1).Style, level);
                }
                currentRow++;

                int dataStartRow = currentRow;
                List<Dictionary<string, object>>? leafRows = null;

                if (isLeaf)
                {
                    if (node.TryGetValue("Rows", out var rowsObj) && rowsObj is IEnumerable<object> rowsEnum)
                    {
                        leafRows = rowsEnum.OfType<Dictionary<string, object>>().ToList();
                        foreach (var r in leafRows)
                        {
                            WriteDataRow(ws, currentRow, r, zebraIndex);
                            currentRow++;
                            zebraIndex++;

                            if (r.TryGetValue("__subRowGroups", out var groupsObj)
                                && groupsObj is IEnumerable<ReportSubRowResolver.ResolvedGroupResult> groups)
                                currentRow = WriteSubRowGroups(ws, currentRow, groups, indentCols: 1);
                        }
                    }
                }
                else if (node.TryGetValue("Children", out var childrenObj) && childrenObj is IEnumerable<object> childrenEnum)
                {
                    foreach (var child in childrenEnum)
                        currentRow = WriteGroupNodeRecursive(ws, currentRow, child, ref zebraIndex, level + 1);
                }

                int dataEndRow = currentRow - 1;
                bool spansMultipleRows = dataEndRow > dataStartRow;

                // No separate merge of the group-key column's own cells here (unlike an earlier version
                // of this method) — the banner row above is now the single, unconditional group
                // indicator, matching PDF/HTML exactly: a visible group-key column simply repeats its
                // value per row like any other column, same as PDF's table body does.

                // Group-invariant merge-flagged fields — leaf level only (an intermediate level has no
                // single concrete row set of its own to check constancy against).
                if (isLeaf && spansMultipleRows && leafRows != null)
                {
                    foreach (var f in mergeFields)
                    {
                        var title = f.ReportViewFieldsFieldTitle ?? "";
                        if (string.Equals(title, levelTitle, StringComparison.OrdinalIgnoreCase)) continue;
                        if (!colIndexByTitle.TryGetValue(title, out var mcol)) continue;

                        var distinctCount = leafRows.Select(r => r.GetValueOrDefault(title)?.ToString() ?? "").Distinct().Count();
                        if (distinctCount == 1)
                        {
                            var mrange = ws.Range(dataStartRow, mcol, dataEndRow, mcol);
                            mrange.Merge();
                            mrange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        }
                        // else: genuinely varies within this group instance — leave the real per-row values, no merge.
                    }
                }

                if (spansMultipleRows)
                    ws.Rows(dataStartRow, dataEndRow).Group();

                if (node.TryGetValue("SubTotal", out var subTotalObj) && subTotalObj is Dictionary<string, object?> subTotal)
                {
                    WriteTotalsRowNative(ws, currentRow, "Sub Total", subTotal, isGrandTotal: false);
                    currentRow++;
                }

                return currentRow;
            }

            // ── Render ──────────────────────────────────────────────────────────────────────────────
            if (hasGrouping)
            {
                // Multi-sheet splitting is intentionally NOT applied to grouped exports in this pass —
                // safely splitting a live multi-level outline/merge tree across worksheets mid-render is
                // substantially more involved than the flat case below, and grouped reports at a scale
                // that would actually cross MaxRowsPerSheet are a rare edge case. A grouped report that
                // large still renders (subject to ClosedXML/Excel's own hard 1,048,576-row sheet limit)
                // — flagged here deliberately rather than silently claiming full coverage.
                var ws = NewSheet(0);
                int row = EmitSheetPrelude(ws);
                int zebra = 0;

                var groups = GroupTreeBuilder.BuildGroupNodes(
                    rowsByTitle,
                    groupColumns,
                    0,
                    groupRows => subTotalFields.Count > 0 ? ComputeAggregateRow(groupRows, subTotalFields) : null);

                foreach (var node in groups)
                    row = WriteGroupNodeRecursive(ws, row, node, ref zebra, level: 0);

                if (grandTotalFields.Count > 0)
                {
                    WriteTotalsRowNative(ws, row, "Grand Total", ComputeAggregateRow(rowsByTitle, grandTotalFields), isGrandTotal: true);
                    row++;
                }

                AdjustColumnWidths(ws);
            }
            else
            {
                var chunks = rowsByTitle.Count == 0
                    ? new List<List<Dictionary<string, object>>> { new() }
                    : rowsByTitle
                        .Select((r, i) => (r, i))
                        .GroupBy(x => x.i / _maxRowsPerSheet)
                        .Select(g => g.Select(x => x.r).ToList())
                        .ToList();

                for (int sheetIdx = 0; sheetIdx < chunks.Count; sheetIdx++)
                {
                    var ws = NewSheet(sheetIdx);
                    int row = EmitSheetPrelude(ws);
                    int zebra = 0;
                    var physicalRows = new List<int>(chunks[sheetIdx].Count);

                    foreach (var r in chunks[sheetIdx])
                    {
                        physicalRows.Add(row);
                        WriteDataRow(ws, row, r, zebra);
                        row++;
                        zebra++;

                        if (r.TryGetValue("__subRowGroups", out var groupsObj)
                            && groupsObj is IEnumerable<ReportSubRowResolver.ResolvedGroupResult> groups)
                            row = WriteSubRowGroups(ws, row, groups, indentCols: 1);
                    }

                    ApplyMergeRunsFlat(ws, physicalRows, chunks[sheetIdx]);

                    if (sheetIdx == chunks.Count - 1 && grandTotalFields.Count > 0)
                    {
                        WriteTotalsRowNative(ws, row, "Grand Total", ComputeAggregateRow(rowsByTitle, grandTotalFields), isGrandTotal: true);
                        row++;
                    }

                    AdjustColumnWidths(ws);
                }
            }

            workbook.SaveAs(outputStream);
        }

        private static string SanitizeSheetName(string name)
        {
            var cleaned = new string(name.Where(c => !"\\/?*[]:".Contains(c)).ToArray());
            if (cleaned.Length == 0) cleaned = "Report";
            return cleaned.Length > 31 ? cleaned[..31] : cleaned;
        }
    }
}