using System.Data;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Fast.Shared.Database.Models;
using Fast.Shared.Logic.Package;
using Fast.Shared.Logic.ValuationByPath;
using Fast.Web.Logic.ReportSources;

namespace Test.Private;

public class OpenXmlTest
{
    private static string _testOutputPath = "";

    [Before(Class)]
    public static async Task ClassInit()
    {
        _testOutputPath = Path.Combine(Path.GetTempPath(), "OpenXmlTests");
        Directory.CreateDirectory(_testOutputPath);
        await Task.CompletedTask;
    }

    private class SimpleModel
    {
        [Display(Name = "Name")]
        public string Name { get; set; } = "";

        [Display(Name = "Amount")]
        public decimal Amount { get; set; }

        [Display(Name = "Date")]
        public DateTime Date { get; set; }

        [Display(Name = "Active")]
        public bool Active { get; set; }
    }

    private class ModelWithNulls
    {
        [Display(Name = "ID")]
        public int Id { get; set; }

        [Display(Name = "Description")]
        public string? Description { get; set; }

        [Display(Name = "Price")]
        public decimal? Price { get; set; }
    }

    [Test]
    public async Task FillSheet_Weird_Template()
    {
        var inputfilePath = Path.Combine(GS.AssetsPath, "Weird-Val-By-Path-Template.xlsx");
        var outputFilePath = $"Weird-Val-By-Path-Template-Result-{Guid.NewGuid()}.xlsx";
        File.Copy(inputfilePath, outputFilePath, true);

        var vals = new List<PathValuationResult>();
        var val = new PathValuationResult();
        val.Day = new DateOnly(2025, 7, 30);
        val.ReceiptDeal = "GAS165530";
        val.ReceiptInternalEntity = "SUPERIOR NATURAL GAS CORPORATION";
        val.ReceiptCounterparty = "WALTER OIL & GAS CORPORATION";
        vals.Add(val);

        using var wb = SpreadsheetDocument.Open(outputFilePath, true);
        OpenXmlHelper.InitializeUniversalStylesheet(wb);

        OpenXmlHelper.GetOrCreateWorkbookPart(wb);
        var sheetName = "Valuation Data by Path";
        var templateFormulas = OpenXmlHelper.FillSheetForReport(wb, sheetName, vals);

        OpenXmlHelper.PostProcessingForReport(wb, sheetName, templateFormulas, vals.Count, true);
        OpenXmlHelper.SaveAllChanges(wb);

        var wsPartAssert = OpenXmlHelper.GetOrCreateWorksheetPart(wb, sheetName);
        var sheetDataAssert = wsPartAssert.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");

        string? getCellVal(string cellRef)
        {
            var rIdxStr = new string(cellRef.Where(char.IsDigit).ToArray());
            if (uint.TryParse(rIdxStr, out var rIdx))
            {
                var row = sheetDataAssert?.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == rIdx);
                var colPart = new string(cellRef.TakeWhile(char.IsLetter).ToArray());

                // Parse headers to find the index of the column
                // This is necessary because we optimized out the "r" attribute in data rows
                var headerRow = sheetDataAssert?.Elements<Row>().FirstOrDefault(r => r.RowIndex?.Value == 1U);
                var headerCellIndex = -1;

                if (headerRow != null)
                {
                    var headerCells = headerRow.Elements<Cell>().ToList();
                    for (int i = 0; i < headerCells.Count; i++)
                    {
                        var hRef = headerCells[i].CellReference?.Value;
                        string hCol;
                        if (hRef != null)
                            hCol = new string(hRef.TakeWhile(char.IsLetter).ToArray());
                        else
                            hCol = OpenXmlHelper.GetColumnName(i + 1);

                        if (string.Equals(hCol, colPart, StringComparison.OrdinalIgnoreCase))
                        {
                            headerCellIndex = i;
                            break;
                        }
                    }
                }

                // If explicit ref exists, use it (headers mostly), otherwise use index from header mapping
                var cell = row?.Elements<Cell>().FirstOrDefault(c => c.CellReference == cellRef);
                if (cell == null && headerCellIndex >= 0 && row != null)
                {
                    var cells = row.Elements<Cell>().ToList();
                    if (headerCellIndex < cells.Count)
                        cell = cells[headerCellIndex];
                }

                var cellVal = OpenXmlHelper.GetCellValue(cell, wb.WorkbookPart);

                // Handle date conversion...
                if (colPart == "A" && double.TryParse(cellVal, out var dVal))
                    return DateTime.FromOADate(dVal).ToString("M/d/yyyy");

                return cellVal;
            }
            return null;
        }

        var a1Val = getCellVal("A2");
        var b2Val = getCellVal("B2");

        await Assert.That(a1Val).IsEqualTo("7/30/2025");
        await Assert.That(b2Val).IsEqualTo("GAS165530");

        // Cleanup if possible, though strict cleanup isn't always required in temp folders
        try { File.Delete(outputFilePath); } catch { }
    }


    [Test]
    public async Task FillSheet_Generic_WritesHeadersAndData()
    {
        // Arrange
        var filePath = Path.Combine(_testOutputPath, "FillSheet_Generic_WritesHeadersAndData.xlsx");
        var data = new List<SimpleModel>
        {
            new() { Name = "Item 1", Amount = 100.50m, Date = new DateTime(2024, 1, 15), Active = true },
            new() { Name = "Item 2", Amount = 200.75m, Date = new DateTime(2024, 2, 20), Active = false },
            new() { Name = "Item 3", Amount = 0m, Date = new DateTime(2024, 3, 25), Active = true },
        };

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "TestSheet", data);
            doc.Save();
        }

        // Assert
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var rows = sheetData.Elements<Row>().ToList();

        await Assert.That(rows).Count().IsEqualTo(4); // Should have 1 header row + 3 data rows

        // Verify header row is row 1
        var headerRow = rows.First(r => r.RowIndex?.Value == 1U);
        var headerCells = headerRow.Elements<Cell>().ToList();
        await Assert.That(headerCells.Count).IsGreaterThanOrEqualTo(4); // Should have at least 4 header cells

        // Verify cells are in column order (A, B, C, D...)
        var cellRefs = headerCells.Select(c => c.CellReference?.Value ?? "").ToList();
        for (int i = 0; i < cellRefs.Count - 1; i++)
        {
            var idx1 = OpenXmlHelper.GetColumnIndex(cellRefs[i]);
            var idx2 = OpenXmlHelper.GetColumnIndex(cellRefs[i + 1]);
            await Assert.That(idx1).IsLessThan(idx2);
        }
    }

    [Test]
    public async Task FillSheet_Generic_WithEmptyData_CreatesEmptyRow()
    {
        // Arrange
        var filePath = Path.Combine(_testOutputPath, "FillSheet_Generic_WithEmptyData.xlsx");
        var data = new List<SimpleModel>();

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "TestSheet", data);
            doc.Save();
        }

        // Assert
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var rows = sheetData.Elements<Row>().ToList();

        await Assert.That(rows).Count().IsEqualTo(2); // Should have 1 header row + 1 empty row (to prevent table breakage)
    }

    [Test]
    public async Task FillSheet_Generic_WithNullValues_HandlesGracefully()
    {
        // Arrange
        var filePath = Path.Combine(_testOutputPath, "FillSheet_Generic_WithNullValues.xlsx");
        var data = new List<ModelWithNulls>
        {
            new() { Id = 1, Description = "Has Both", Price = 99.99m },
            new() { Id = 2, Description = null, Price = 50.00m },
            new() { Id = 3, Description = "No Price", Price = null },
            new() { Id = 4, Description = null, Price = null },
        };

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "TestSheet", data);
            doc.Save();
        }

        // Assert
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var rows = sheetData.Elements<Row>().ToList();

        await Assert.That(rows).Count().IsEqualTo(5); // Should have 1 header row + 4 data rows
    }

    [Test]
    public async Task FillSheet_Generic_WithColumnSelection_FiltersColumns()
    {
        // Arrange
        var filePath = Path.Combine(_testOutputPath, "FillSheet_Generic_WithColumnSelection.xlsx");
        var data = new List<SimpleModel>
        {
            new() { Name = "Item 1", Amount = 100.50m, Date = DateTime.Now, Active = true },
        };
        var columns = new[] { "Name", "Amount" };

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "TestSheet", data, columns);
            doc.Save();
        }

        // Assert
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var headerRow = sheetData.Elements<Row>().First();
        var headerCells = headerRow.Elements<Cell>().ToList();

        await Assert.That(headerCells).Count().IsEqualTo(2); // Should only have 2 header cells
    }

    [Test]
    public async Task FillSheet_DataTable_WritesHeadersAndData()
    {
        // Arrange
        var filePath = Path.Combine(_testOutputPath, "FillSheet_DataTable_WritesHeadersAndData.xlsx");
        var dt = CreateTestDataTable();

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "TestSheet", dt);
            doc.Save();
        }

        // Assert
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var rows = sheetData.Elements<Row>().ToList();

        await Assert.That(rows).Count().IsEqualTo(4); // Should have 1 header row + 3 data rows
    }

    [Test]
    public async Task FillSheet_WithExistingTemplate_PreservesHeaderOrder()
    {
        // Arrange - Create template with sparse header (A, C, E - gaps in B, D)
        var filePath = Path.Combine(_testOutputPath, "FillSheet_WithExistingTemplate.xlsx");
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            var wbPart = doc.AddWorkbookPart();
            wbPart.Workbook = new Workbook(new Sheets());
            var wsPart = wbPart.AddNewPart<WorksheetPart>();
            wsPart.Worksheet = new Worksheet(new SheetData());

            var sheets = wbPart.Workbook.GetFirstChild<Sheets>()!;
            sheets.Append(new Sheet { Id = doc.WorkbookPart!.GetIdOfPart(wsPart), SheetId = 1, Name = "TestSheet" });

            var sheetData = wsPart.Worksheet?.GetFirstChild<SheetData>()!;

            // Create sparse header row with gaps: A1="Amount", C1="Name" (skipping B)
            var headerRow = new Row { RowIndex = 1 };
            headerRow.Append(new Cell { CellReference = "A1", DataType = CellValues.String, CellValue = new CellValue("Amount") });
            headerRow.Append(new Cell { CellReference = "C1", DataType = CellValues.String, CellValue = new CellValue("Name") });
            sheetData.Append(headerRow);

            doc.Save();
        }

        // Act - Fill with data
        var data = new List<SimpleModel>
        {
            new() { Name = "Test", Amount = 123.45m, Date = DateTime.Now, Active = true },
        };

        using (var doc = SpreadsheetDocument.Open(filePath, true))
        {
            OpenXmlHelper.FillSheet(doc, "TestSheet", data);
            doc.Save();
        }

        // Assert
        using (var readDoc = SpreadsheetDocument.Open(filePath, false))
        {
            var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
            var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
                ?? throw new InvalidOperationException("SheetData not found");
            var headerRow = sheetData.Elements<Row>().First(r => r.RowIndex?.Value == 1U);
            var headerCells = headerRow.Elements<Cell>().ToList();

            // Verify cells are in column order
            for (int i = 0; i < headerCells.Count - 1; i++)
            {
                var ref1 = headerCells[i].CellReference?.Value ?? "";
                var ref2 = headerCells[i + 1].CellReference?.Value ?? "";
                var idx1 = OpenXmlHelper.GetColumnIndex(ref1);
                var idx2 = OpenXmlHelper.GetColumnIndex(ref2);
                await Assert.That(idx1).IsLessThan(idx2);
            }

            // Verify new columns were added after existing max column
            var newHeaders = headerCells.Skip(2).ToList(); // Skip original Amount that was there
            foreach (var cell in newHeaders)
            {
                var colIdx = OpenXmlHelper.GetColumnIndex(cell.CellReference?.Value ?? "");
                await Assert.That(colIdx).IsGreaterThan(3); // New headers should be added after column C (index 3)
            }
        }
    }

    [Test]
    public async Task FillSheet_WithOutOfOrderTemplateCells_NormalizesCellOrder()
    {
        // Arrange - Create template with out-of-order cells in header
        var filePath = Path.Combine(_testOutputPath, "FillSheet_WithOutOfOrderCells.xlsx");
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            var wbPart = doc.AddWorkbookPart();
            wbPart.Workbook = new Workbook(new Sheets());
            var wsPart = wbPart.AddNewPart<WorksheetPart>();
            wsPart.Worksheet = new Worksheet(new SheetData());

            var sheets = wbPart.Workbook.GetFirstChild<Sheets>()!;
            sheets.Append(new Sheet { Id = doc.WorkbookPart!.GetIdOfPart(wsPart), SheetId = 1, Name = "TestSheet" });

            var sheetData = wsPart.Worksheet?.GetFirstChild<SheetData>()!;

            // Create out-of-order header row: append C1 before A1
            var headerRow = new Row { RowIndex = 1 };
            headerRow.Append(new Cell { CellReference = "C1", DataType = CellValues.String, CellValue = new CellValue("Third") });
            headerRow.Append(new Cell { CellReference = "A1", DataType = CellValues.String, CellValue = new CellValue("First") });
            headerRow.Append(new Cell { CellReference = "B1", DataType = CellValues.String, CellValue = new CellValue("Second") });
            sheetData.Append(headerRow);

            doc.Save();
        }

        // Act - Fill with data (this should normalize cell order)
        var data = new List<SimpleModel>
        {
            new() { Name = "Test", Amount = 100m, Date = DateTime.Now, Active = true },
        };

        using (var doc = SpreadsheetDocument.Open(filePath, true))
        {
            OpenXmlHelper.FillSheet(doc, "TestSheet", data);
            doc.Save();
        }

        // Assert
        using (var readDoc = SpreadsheetDocument.Open(filePath, false))
        {
            var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
            var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
                ?? throw new InvalidOperationException("SheetData not found");
            var headerRow = sheetData.Elements<Row>().First(r => r.RowIndex?.Value == 1U);
            var headerCells = headerRow.Elements<Cell>().ToList();

            // Verify cells are now in correct order (A, B, C, ...)
            var lastColIdx = 0;
            foreach (var cell in headerCells)
            {
                var colIdx = OpenXmlHelper.GetColumnIndex(cell.CellReference?.Value ?? "");
                await Assert.That(colIdx).IsGreaterThan(lastColIdx);
                lastColIdx = colIdx;
            }
        }
    }

    [Test]
    [Arguments("A1", 1)]
    [Arguments("B5", 2)]
    [Arguments("Z1", 26)]
    [Arguments("AA1", 27)]
    [Arguments("AZ1", 52)]
    [Arguments("BA1", 53)]
    [Arguments("ZZ1", 702)]
    [Arguments("AAA1", 703)]
    public async Task GetColumnIndex_ConvertsCorrectly(string cellRef, int expectedIndex)
    {
        var result = OpenXmlHelper.GetColumnIndex(cellRef);
        await Assert.That(result).IsEqualTo(expectedIndex);
    }

    [Test]
    [Arguments(1, "A")]
    [Arguments(2, "B")]
    [Arguments(26, "Z")]
    [Arguments(27, "AA")]
    [Arguments(52, "AZ")]
    [Arguments(53, "BA")]
    [Arguments(702, "ZZ")]
    [Arguments(703, "AAA")]
    public async Task GetColumnName_ConvertsCorrectly(int columnIndex, string expectedName)
    {
        var result = OpenXmlHelper.GetColumnName(columnIndex);
        await Assert.That(result).IsEqualTo(expectedName);
    }

    [Test]
    public async Task GetColumnIndex_And_GetColumnName_AreInverses()
    {
        // Test that GetColumnName(GetColumnIndex(ref)) returns the column part
        for (int i = 1; i <= 1000; i++)
        {
            var name = OpenXmlHelper.GetColumnName(i);
            var cellRef = name + "1";
            var idx = OpenXmlHelper.GetColumnIndex(cellRef);
            await Assert.That(idx).IsEqualTo(i);
        }
    }

    [Repeat(2)]
    [NotInParallel]
    [Test]
    public async Task FillSheet_LargeDataset_HandlesEfficiently()
    {
        var testRowCount = 500000;

        // Arrange - set a reasonable row count for testing
        var filePath = Path.Combine(_testOutputPath, "FillSheet_LargeDataset.xlsx");
        var data = Enumerable.Range(1, testRowCount)
            .Select(i => new SimpleModel
            {
                Name = $"Item {i}",
                Amount = i * 1.5m,
                Date = new DateTime(2024, 1, 1).AddDays(i),
                Active = i % 2 == 0
            })
            .ToList();

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);

            var sw = System.Diagnostics.Stopwatch.StartNew();
            OpenXmlHelper.FillSheet(doc, "TestSheet", data);
            sw.Stop();
            GS.LogInfo($"FillSheet for {testRowCount:N0} rows took {sw.ElapsedMilliseconds}ms");

            doc.Save();
        }

        // Assert
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var rows = sheetData.Elements<Row>().ToList();

        await Assert.That(rows).Count().IsEqualTo(testRowCount + 1); // Should have 1 header row + testRowCount data rows

        // Verify first and last data rows have correct cell ordering
        var lastDataRow = rows.First(r => r.RowIndex?.Value == testRowCount + 1U);
        var cells = lastDataRow.Elements<Cell>().ToList();
        var lastIdx = 0;
        foreach (var cell in cells)
        {
            if (cell.CellReference?.Value != null)
            {
                var idx = OpenXmlHelper.GetColumnIndex(cell.CellReference.Value);
                await Assert.That(idx).IsGreaterThan(lastIdx);
                lastIdx = idx;
            }
        }
    }

    [Test]
    public async Task FillSheet_WithSpecialCharactersInData_HandlesCorrectly()
    {
        // Arrange - Test Unicode, quotes, newlines, XML special chars
        var filePath = Path.Combine(_testOutputPath, "FillSheet_SpecialChars.xlsx");
        var data = new List<SimpleModel>
        {
            new() { Name = "Item with \"quotes\"", Amount = 100m, Date = DateTime.Now, Active = true },
            new() { Name = "Item with <xml> chars", Amount = 200m, Date = DateTime.Now, Active = true },
            new() { Name = "Item with & ampersand", Amount = 300m, Date = DateTime.Now, Active = true },
            new() { Name = "日本語テスト", Amount = 400m, Date = DateTime.Now, Active = true },
            new() { Name = "Emoji 🎉🚀", Amount = 500m, Date = DateTime.Now, Active = true },
            new() { Name = "Line\nBreak", Amount = 600m, Date = DateTime.Now, Active = true },
        };

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "TestSheet", data);
            doc.Save();
        }

        // Assert - file should be readable and have correct row count
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var rows = sheetData.Elements<Row>().ToList();

        await Assert.That(rows).Count().IsEqualTo(7); // Should have 1 header row + 6 data rows with special chars
    }

    [Test]
    public async Task FillSheet_WithVeryLongStrings_HandlesCorrectly()
    {
        // Arrange - Test extremely long strings
        var filePath = Path.Combine(_testOutputPath, "FillSheet_LongStrings.xlsx");
        var longString = new string('A', 10000); // 10k character string
        var data = new List<SimpleModel>
        {
            new() { Name = longString, Amount = 100m, Date = DateTime.Now, Active = true },
        };

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "TestSheet", data);
            doc.Save();
        }

        // Assert - file should be created successfully
        await Assert.That(File.Exists(filePath)).IsTrue();
        var fileInfo = new FileInfo(filePath);
        await Assert.That(fileInfo.Length).IsGreaterThan(0);
    }

    [Test]
    public async Task FillSheet_MultipleSheets_PreservesAllData()
    {
        // Arrange - Multiple sheets in same document
        var filePath = Path.Combine(_testOutputPath, "FillSheet_MultipleSheets.xlsx");
        var data1 = new List<SimpleModel>
        {
            new() { Name = "Sheet1 Item", Amount = 100m, Date = DateTime.Now, Active = true },
        };
        var data2 = new List<SimpleModel>
        {
            new() { Name = "Sheet2 Item", Amount = 200m, Date = DateTime.Now, Active = false },
        };

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "Sheet1", data1);
            OpenXmlHelper.FillSheet(doc, "Sheet2", data2);
            doc.Save();
        }

        // Assert - verify both sheets exist with data
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var sheets = (readDoc.WorkbookPart?.Workbook?.Sheets
            ?? throw new InvalidOperationException("Sheets not found")).Elements<Sheet>().ToList();

        await Assert.That(sheets).Count().IsEqualTo(2);
        await Assert.That(sheets[0].Name?.Value).IsEqualTo("Sheet1");
        await Assert.That(sheets[1].Name?.Value).IsEqualTo("Sheet2");
    }

    [Test]
    public async Task FillSheet_DataTable_WithMixedTypes_HandlesCorrectly()
    {
        // Arrange - DataTable with various column types
        var filePath = Path.Combine(_testOutputPath, "FillSheet_DataTable_MixedTypes.xlsx");
        var dt = new DataTable();
        dt.Columns.Add("StringCol", typeof(string));
        dt.Columns.Add("IntCol", typeof(int));
        dt.Columns.Add("DecimalCol", typeof(decimal));
        dt.Columns.Add("DoubleCol", typeof(double));
        dt.Columns.Add("BoolCol", typeof(bool));
        dt.Columns.Add("DateCol", typeof(DateTime));
        dt.Columns.Add("NullableIntCol", typeof(int));

        dt.Rows.Add("Text", 42, 123.45m, 3.14159, true, new DateTime(2024, 6, 15), 100);
        dt.Rows.Add("", 0, 0m, 0.0, false, DateTime.MinValue, DBNull.Value);
        dt.Rows.Add(DBNull.Value, -1, -99.99m, -1.5, true, new DateTime(2099, 12, 31), DBNull.Value);

        // Act
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "TestSheet", dt);
            doc.Save();
        }

        // Assert
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var rows = sheetData.Elements<Row>().ToList();

        await Assert.That(rows).Count().IsEqualTo(4); // Should have 1 header row + 3 data rows

        // Verify header has all columns
        var headerRow = rows.First(r => r.RowIndex?.Value == 1U);
        var headerCells = headerRow.Elements<Cell>().ToList();
        await Assert.That(headerCells).Count().IsEqualTo(7);
    }

    [Test]
    public async Task FillSheet_WithHighColumnIndex_HandlesCorrectly()
    {
        // Arrange - Create template with columns at high indices (e.g., column ZZ)
        var filePath = Path.Combine(_testOutputPath, "FillSheet_HighColumnIndex.xlsx");
        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            var wbPart = doc.AddWorkbookPart();
            wbPart.Workbook = new Workbook(new Sheets());
            var wsPart = wbPart.AddNewPart<WorksheetPart>();
            wsPart.Worksheet = new Worksheet(new SheetData());

            var sheets = wbPart.Workbook.GetFirstChild<Sheets>()!;
            sheets.Append(new Sheet { Id = doc.WorkbookPart!.GetIdOfPart(wsPart), SheetId = 1, Name = "TestSheet" });

            var sheetData = wsPart.Worksheet?.GetFirstChild<SheetData>()!;

            // Create header at column AA (27) and AB (28)
            var headerRow = new Row { RowIndex = 1 };
            headerRow.Append(new Cell { CellReference = "AA1", DataType = CellValues.String, CellValue = new CellValue("Amount") });
            headerRow.Append(new Cell { CellReference = "AB1", DataType = CellValues.String, CellValue = new CellValue("Name") });
            sheetData.Append(headerRow);

            doc.Save();
        }

        // Act
        var data = new List<SimpleModel>
        {
            new() { Name = "Test", Amount = 999m, Date = DateTime.Now, Active = true },
        };

        using (var doc = SpreadsheetDocument.Open(filePath, true))
        {
            OpenXmlHelper.FillSheet(doc, "TestSheet", data);
            doc.Save();
        }

        // Assert
        using (var readDoc = SpreadsheetDocument.Open(filePath, false))
        {
            var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
            var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
                ?? throw new InvalidOperationException("SheetData not found");
            var headerRow = sheetData.Elements<Row>().First(r => r.RowIndex?.Value == 1U);
            var headerCells = headerRow.Elements<Cell>().ToList();

            // Verify new columns are added after AB (28), so at AC (29) onwards
            var maxExistingCol = 28;
            foreach (var cell in headerCells.Skip(2)) // Skip original AA and AB
            {
                var colIdx = OpenXmlHelper.GetColumnIndex(cell.CellReference?.Value ?? "");
                await Assert.That(colIdx).IsGreaterThan(maxExistingCol);
            }
        }
    }

    [Test]
    public async Task FillSheet_PreservesExistingDataRows_WhenAppending()
    {
        // This tests that calling FillSheet on an existing sheet with data appends correctly
        var filePath = Path.Combine(_testOutputPath, "FillSheet_PreservesExisting.xlsx");

        // First, create a doc with initial data
        var initialData = new List<SimpleModel>
        {
            new() { Name = "Initial", Amount = 100m, Date = DateTime.Now, Active = true },
        };

        using (var doc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
        {
            OpenXmlHelper.GetOrCreateWorkbookPart(doc);
            OpenXmlHelper.FillSheet(doc, "TestSheet", initialData);
            doc.Save();
        }

        // Verify initial state
        using var readDoc = SpreadsheetDocument.Open(filePath, false);
        var wsPart = readDoc.WorkbookPart?.WorksheetParts.First();
        var sheetData = wsPart?.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var rows = sheetData.Elements<Row>().ToList();
        await Assert.That(rows).Count().IsEqualTo(2); // Should have 1 header + 1 data row initially
    }

    [Test]
    public async Task GetColumnName_EdgeCases_HandlesGracefully()
    {
        // Test edge cases for GetColumnName
        await Assert.That(OpenXmlHelper.GetColumnName(0)).IsEqualTo("");
        await Assert.That(OpenXmlHelper.GetColumnName(-1)).IsEqualTo("");
        await Assert.That(OpenXmlHelper.GetColumnName(1)).IsEqualTo("A");
        await Assert.That(OpenXmlHelper.GetColumnName(16384)).IsEqualTo("XFD"); // Excel max column
    }

    [Test]
    public async Task NominationsReport_Test()
    {
        using var ms = new MemoryStream();
        using var wb = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);

        // Add a minimal workbook structure - Nominations.Run() will reinitialize it
        var wbPart = wb.AddWorkbookPart();
        wbPart.Workbook = new Workbook(new Sheets());

        var filterParamsList = new List<ReportFilterParameter>
        {
            new()
            {
                Id = 21216,
                Name = "Gas Day",
                Preview = "1/1/2025 to 1/31/2025",
                Value1 = "2025/01/01-2025/01/31",
                Value2 = null,
                DataSourceId = 12,
                FilterId = 883
            },
            new()
            {
                Id = 21217,
                Name = "Pipeline",
                Preview = "ANR PIPELINE COMPANY",
                Value1 = "4",
                Value2 = null,
                DataSourceId = 12,
                FilterId = 883
            }
        };

        using var db = ProgramShared.CreateContext();

        var nominations = new Nominations(
            db,
            wb,
            reportName: "Test",
            dataSourceId: 12,
            filterId: 883,
            filterName: "Test",
            filterParams: filterParamsList,
            userInfoId: 28
        );

        await nominations.Run();
        var sheetCount = wb.WorkbookPart!.WorksheetParts.Count();
        await Assert.That(sheetCount).IsEqualTo(31);

        var firstSheet = (wb.WorkbookPart?.Workbook?.Sheets
            ?? throw new InvalidOperationException("Sheets not found")).Elements<Sheet>().First();
        var firstWorksheetPart = (WorksheetPart)wb.WorkbookPart.GetPartById(
            firstSheet.Id?.Value ?? throw new InvalidOperationException("Sheet Id not found"));
        var firstSheetRowCount = firstWorksheetPart.Worksheet!.GetFirstChild<SheetData>()?.Elements<Row>().Count() ?? 0;

        await Assert.That(firstSheetRowCount).IsGreaterThan(5);
    }

    [Repeat(2)]
    [NotInParallel]
    [Test]
    public async Task PostProcessingForReport_Performance()
    {
        var inputFilePath = Path.Combine(GS.AssetsPath, "post-processing-test.xlsx");
        var outputFilePath = Path.Combine(_testOutputPath, $"post-processing-test-{Guid.NewGuid()}.xlsx");
        File.Copy(inputFilePath, outputFilePath, true);

        using var wb = SpreadsheetDocument.Open(outputFilePath, true);
        OpenXmlHelper.InitializeUniversalStylesheet(wb);
        OpenXmlHelper.GetOrCreateWorkbookPart(wb);

        var sheet = wb.WorkbookPart!.Workbook!.Sheets!.Elements<Sheet>().First();
        var sheetName = sheet.Name?.Value ?? throw new InvalidOperationException("Sheet name not found");

        var wsPart = OpenXmlHelper.GetOrCreateWorksheetPart(wb, sheetName);
        var sheetData = wsPart.Worksheet?.GetFirstChild<SheetData>()
            ?? throw new InvalidOperationException("SheetData not found");
        var dataRowCount = sheetData.Elements<Row>().Count() - 1; // exclude header row

        var templateFormulas = new List<OpenXmlHelper.TemplateFormulaInfo>();

        var sw = System.Diagnostics.Stopwatch.StartNew();
        OpenXmlHelper.PostProcessingForReport(wb, sheetName, templateFormulas, dataRowCount, true);
        sw.Stop();

        GS.LogInfo($"PostProcessingForReport for {dataRowCount:N0} rows took {sw.ElapsedMilliseconds}ms");

        OpenXmlHelper.SaveAllChanges(wb);

        try { File.Delete(outputFilePath); } catch { }
        await Task.CompletedTask;
    }

    private static DataTable CreateTestDataTable()
    {
        var dt = new DataTable();
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Amount", typeof(decimal));
        dt.Columns.Add("Date", typeof(DateTime));

        dt.Rows.Add("Row 1", 100.00m, new DateTime(2024, 1, 1));
        dt.Rows.Add("Row 2", 200.50m, new DateTime(2024, 2, 1));
        dt.Rows.Add("Row 3", 300.75m, new DateTime(2024, 3, 1));

        return dt;
    }
}