using System.Globalization;
using ClosedXML.Excel;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Report;
using GB5Shared.Export;
using GB5Shared.ExcelExport;

namespace GB5Shared.Export.Pivot
{
    public sealed class PivotExcelExport : IPivotExcelExport
    {
        private const int MinColumnWidth = 8;
        private const int MaxColumnWidth = 60;

        public Task<byte[]> ExportAsync(
            PivotResultDTO                 pivotResult,
            ReportStandardDTO              reportStandard,
            List<ReportViewExcelHeaderDTO> headers,
            LoginDTO                       login,
            CancellationToken              ct = default)
        {
            var theme = ReportTheme.FromLogin(login);

            using var workbook = new XLWorkbook();
            var ws = workbook.Worksheets.Add(
                string.IsNullOrWhiteSpace(reportStandard.ExcelSheetName) ? "Pivot" : reportStandard.ExcelSheetName);

            int currentRow = 1;

            // 1. Report header rows — same designer-driven rows the flat exporter renders, same styling.
            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++;
            }

            // 1b. Generated-on sub-row
            var allColumnHeaders = pivotResult.RowHeaders.Concat(pivotResult.PivotColumns).ToList();
            if (allColumnHeaders.Count > 0)
            {
                var genRange = ws.Range(currentRow, 1, currentRow, allColumnHeaders.Count);
                genRange.Merge();
                genRange.FirstCell().Value = $"Generated on {DateTime.UtcNow:dd-MMM-yyyy HH:mm} UTC";
                ExcelStyleHelpers.ApplyGeneratedOnRowStyle(genRange.Style, theme);
            }
            currentRow++;

            // 2. Column header row: row dimension titles + pivot column titles
            for (int col = 0; col < allColumnHeaders.Count; col++)
            {
                var hCell = ws.Cell(currentRow, col + 1);
                hCell.Value = allColumnHeaders[col];
                ExcelStyleHelpers.ApplyProfessionalHeaderStyle(hCell.Style, theme);
            }
            ExcelStyleHelpers.ApplyFreezeHeader(ws, currentRow);
            currentRow++;

            // 3. Data rows
            foreach (var row in pivotResult.Rows)
            {
                for (int col = 0; col < allColumnHeaders.Count; col++)
                {
                    var header = allColumnHeaders[col];
                    row.TryGetValue(header, out var value);
                    var cell = ws.Cell(currentRow, col + 1);
                    SetCellValue(cell, value);
                }
                currentRow++;
            }

            // 4. Grand total row
            if (pivotResult.GrandTotal != null)
            {
                var gtRow = ws.Row(currentRow);
                for (int col = 0; col < allColumnHeaders.Count; col++)
                {
                    var header = allColumnHeaders[col];
                    var cell = gtRow.Cell(col + 1);

                    if (col == 0)
                    {
                        cell.Value = "Grand Total";
                    }
                    else
                    {
                        pivotResult.GrandTotal.TryGetValue(header, out var value);
                        SetCellValue(cell, value);
                    }

                    ExcelStyleHelpers.ApplyGrandTotalStyle(cell.Style);
                }
                currentRow++;
            }

            // 5. Auto-fit columns, clamped to a sane range
            for (int col = 1; col <= allColumnHeaders.Count; col++)
            {
                try
                {
                    ws.Column(col).AdjustToContents();
                    ws.Column(col).Width = Math.Clamp(ws.Column(col).Width, MinColumnWidth, MaxColumnWidth);
                }
                catch { }
            }

            using var ms = new MemoryStream();
            workbook.SaveAs(ms);
            return Task.FromResult(ms.ToArray());
        }

        private static void SetCellValue(IXLCell cell, object? value)
        {
            if (value == null)
            {
                cell.Value = string.Empty;
            }
            else if (value is DateTime dt)
            {
                cell.Value = dt;
                cell.Style.NumberFormat.Format = "yyyy-MM-dd";
            }
            else if (value is decimal or double or float or int or long)
            {
                cell.Value = Convert.ToDouble(value, CultureInfo.InvariantCulture);
            }
            else if (double.TryParse(value.ToString(), NumberStyles.Any, CultureInfo.InvariantCulture, out var num))
            {
                cell.Value = num;
            }
            else
            {
                cell.Value = value.ToString();
            }
        }
    }
}