using JdeScoping.ExcelIO.Options;
using JdeScoping.ExcelIO.Generators;
using JdeScoping.ExcelIO.Mapping;
using JdeScoping.ExcelIO.Mapping.Maps;
using JdeScoping.ExcelIO.Models.Reporting;
using JdeScoping.ExcelIO.Tests.Fixtures;
using Microsoft.Extensions.Options;
using NPOI.XSSF.UserModel;
using Shouldly;
using Xunit;

namespace JdeScoping.ExcelIO.Tests;

public class CriteriaSheetGeneratorTests
{
    private readonly CriteriaSheetGenerator _generator;

    public CriteriaSheetGeneratorTests()
    {
        var options = Microsoft.Extensions.Options.Options.Create(new ExcelExportOptions
        {
            CriteriaSheetPassword = "TestPassword"
        });

        var registry = CreateTestRegistry();
        var tableWriter = new FluentTableWriter(registry);
        _generator = new CriteriaSheetGenerator(options, tableWriter);
    }

    private static ExcelMapRegistry CreateTestRegistry()
    {
        var registry = new ExcelMapRegistry();
        registry.Register(new TimespanFilterMap());
        registry.Register(new WorkOrderFilterEntryMap());
        registry.Register(new ItemNumberFilterEntryMap());
        registry.Register(new ProfitCenterFilterEntryMap());
        registry.Register(new WorkCenterFilterEntryMap());
        registry.Register(new OperatorFilterEntryMap());
        registry.Register(new ComponentLotFilterEntryMap());
        registry.Register(new ItemOperationMisFilterEntryMap());
        return registry;
    }

    [Fact]
    public void Generate_CreatesSearchCriteriaSheet()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();

        _generator.Generate(workbook, search);

        workbook.GetSheet("Search Criteria").ShouldNotBeNull();
    }

    [Fact]
    public void Generate_ContainsSearchName()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.Name = "Test Search Name";

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.GetCellText(worksheet, 1, 1).ShouldBe("Search Name");
        ExcelTestHelpers.GetCellText(worksheet, 1, 2).ShouldBe("Test Search Name");
    }

    [Fact]
    public void Generate_ContainsUserName()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.UserName = "testuser";

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.GetCellText(worksheet, 2, 1).ShouldBe("User Name");
        ExcelTestHelpers.GetCellText(worksheet, 2, 2).ShouldBe("testuser");
    }

    [Fact]
    public void Generate_ContainsTimestamps()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.SubmitDt = new DateTime(2024, 1, 15, 10, 30, 0);
        search.StartDt = new DateTime(2024, 1, 15, 10, 31, 0);
        search.EndDt = new DateTime(2024, 1, 15, 10, 35, 0);

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.GetCellText(worksheet, 4, 1).ShouldBe("Submit timestamp");
        ExcelTestHelpers.GetCellText(worksheet, 4, 2).ShouldContain("Jan 15, 2024");
        ExcelTestHelpers.GetCellText(worksheet, 5, 1).ShouldBe("Start timestamp");
        ExcelTestHelpers.GetCellText(worksheet, 5, 2).ShouldContain("Jan 15, 2024");
        ExcelTestHelpers.GetCellText(worksheet, 6, 1).ShouldBe("Completed timestamp");
        ExcelTestHelpers.GetCellText(worksheet, 6, 2).ShouldContain("Jan 15, 2024");
    }

    [Fact]
    public void Generate_ContainsTimespanFilterTable()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.MinimumDt = new DateTime(2024, 1, 1);
        search.MaximumDt = new DateTime(2024, 12, 31);

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.TableExists(worksheet, "Timespan_Filter").ShouldBeTrue();
    }

    [Fact]
    public void Generate_ContainsWorkOrderFilterTable()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.WorkOrderFilter.Add(new WorkOrderFilterEntry { WorkOrderNumber = 12345, ItemNumber = "ITEM-001" });

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.TableExists(worksheet, "Work_Order_Filter").ShouldBeTrue();
    }

    [Fact]
    public void Generate_ContainsItemNumberFilterTable()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.ItemNumberFilter.Add(new ItemNumberFilterEntry { ItemNumber = "ITEM-001" });

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.TableExists(worksheet, "Item_Number_Filter").ShouldBeTrue();
    }

    [Fact]
    public void Generate_ContainsProfitCenterFilterTable()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.ProfitCenterFilter.Add(new ProfitCenterFilterEntry { Code = "PC01", Description = "Profit Center 1" });

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.TableExists(worksheet, "Profit_Center_Filter").ShouldBeTrue();
    }

    [Fact]
    public void Generate_ContainsWorkCenterFilterTable()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.WorkCenterFilter.Add(new WorkCenterFilterEntry { Code = "WC01", Description = "Work Center 1" });

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.TableExists(worksheet, "Work_Center_Filter").ShouldBeTrue();
    }

    [Fact]
    public void Generate_ContainsOperatorFilterTable()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.OperatorFilter.Add(new OperatorFilterEntry { UserId = "OP01", FullName = "Operator 1" });

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.TableExists(worksheet, "Operator_Filter").ShouldBeTrue();
    }

    [Fact]
    public void Generate_ContainsComponentLotFilterTable()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.ComponentLotFilter.Add(new ComponentLotFilterEntry { LotNumber = "LOT001" });

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.TableExists(worksheet, "Component_Lot_Filter").ShouldBeTrue();
    }

    [Fact]
    public void Generate_ContainsItemOperationMisFilterTable()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.ItemOperationMisFilter.Add(new ItemOperationMisFilterEntry
        {
            ItemNumber = "ITEM-001",
            OperationNumber = "10",
            MisNumber = "MIS-001"
        });

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.TableExists(worksheet, "Item_Operation_MIS_Filter").ShouldBeTrue();
    }

    [Fact]
    public void Generate_ContainsExtractMisDataIndicator_WhenTrue()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.ExtractMisData = true;

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        var yesNo = FindYesNo(worksheet);
        yesNo.ShouldBe("YES");
    }

    [Fact]
    public void Generate_ContainsExtractMisDataIndicator_WhenFalse()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.ExtractMisData = false;

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        var yesNo = FindYesNo(worksheet);
        yesNo.ShouldBe("NO");
    }

    [Fact]
    public void Generate_AppliesHeaderFormatting()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        var headerCell = ExcelTestHelpers.GetCell(worksheet, 1, 1)!;
        (headerCell.CellStyle.FontIndex >= 0 &&
         workbook.GetFontAt(headerCell.CellStyle.FontIndex).IsBold).ShouldBeTrue();
        ExcelTestHelpers.GetFillForegroundRgb(headerCell).ShouldBe([0xDC, 0xDC, 0xDC]);
    }

    [Fact]
    public void Generate_AppliesProtection()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.IsSheetProtected(worksheet).ShouldBeTrue();
    }

    [Fact]
    public void Generate_TablesHaveLight18Style()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.WorkOrderFilter.Add(new WorkOrderFilterEntry { WorkOrderNumber = 12345, ItemNumber = "ITEM-001" });

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        var table = ExcelTestHelpers.GetTableByName(worksheet, "Work_Order_Filter");
        table.StyleName.ShouldBe("TableStyleLight18");
    }

    [Fact]
    public void Generate_FilterTables_Have2BlankRowSpacing()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.WorkOrderFilter.Add(new WorkOrderFilterEntry { WorkOrderNumber = 12345, ItemNumber = "ITEM-001" });
        search.ItemNumberFilter.Add(new ItemNumberFilterEntry { ItemNumber = "ITEM-001" });

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        var woTable = ExcelTestHelpers.GetTableByName(worksheet, "Work_Order_Filter");
        var itemTable = ExcelTestHelpers.GetTableByName(worksheet, "Item_Number_Filter");

        var gap = itemTable.StartCellReference.Row - woTable.EndCellReference.Row;
        gap.ShouldBeGreaterThanOrEqualTo(3);
    }

    [Fact]
    public void Generate_NullTimestamps_ShowEmptyValue()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.SubmitDt = null;
        search.StartDt = null;
        search.EndDt = null;

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.GetCellText(worksheet, 4, 2).ShouldBe(string.Empty);
        ExcelTestHelpers.GetCellText(worksheet, 5, 2).ShouldBe(string.Empty);
        ExcelTestHelpers.GetCellText(worksheet, 6, 2).ShouldBe(string.Empty);
    }

    [Fact]
    public void Generate_ColumnsAreAutoFitWithPadding()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.Name = "A Very Long Search Name That Needs Extra Width";

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        ExcelTestHelpers.GetColumnWidthChars(worksheet, 1).ShouldBeGreaterThan(0);
        ExcelTestHelpers.GetColumnWidthChars(worksheet, 2).ShouldBeGreaterThan(0);
    }

    [Fact]
    public void Generate_MultipleFiltersWithData_CreatesAllTables()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateFullSearchModel();

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        var tableCount = ((XSSFSheet)worksheet).GetTables().Count;

        tableCount.ShouldBe(8);
        ExcelTestHelpers.TableExists(worksheet, "Timespan_Filter").ShouldBeTrue();
        ExcelTestHelpers.TableExists(worksheet, "Work_Order_Filter").ShouldBeTrue();
        ExcelTestHelpers.TableExists(worksheet, "Item_Number_Filter").ShouldBeTrue();
        ExcelTestHelpers.TableExists(worksheet, "Profit_Center_Filter").ShouldBeTrue();
        ExcelTestHelpers.TableExists(worksheet, "Work_Center_Filter").ShouldBeTrue();
        ExcelTestHelpers.TableExists(worksheet, "Component_Lot_Filter").ShouldBeTrue();
        ExcelTestHelpers.TableExists(worksheet, "Operator_Filter").ShouldBeTrue();
        ExcelTestHelpers.TableExists(worksheet, "Item_Operation_MIS_Filter").ShouldBeTrue();
    }

    [Fact]
    public void Generate_TimestampFormat_IncludesESTSuffix()
    {
        using var workbook = new XSSFWorkbook();
        var search = CreateMinimalSearchModel();
        search.SubmitDt = new DateTime(2024, 1, 15, 10, 30, 45);

        _generator.Generate(workbook, search);

        var worksheet = workbook.GetSheet("Search Criteria");
        var timestampValue = ExcelTestHelpers.GetCellText(worksheet, 4, 2);
        timestampValue.ShouldContain("EST");
        timestampValue.ShouldContain("10:30:45");
    }

    private static string FindYesNo(NPOI.SS.UserModel.ISheet worksheet)
    {
        for (var row = 0; row <= worksheet.LastRowNum; row++)
        {
            var npoiRow = worksheet.GetRow(row);
            if (npoiRow == null)
            {
                continue;
            }

            for (var col = 0; col < npoiRow.LastCellNum; col++)
            {
                var text = ExcelTestHelpers.GetCellText(worksheet, row + 1, col + 1);
                if (text == "YES" || text == "NO")
                {
                    return text;
                }
            }
        }

        return string.Empty;
    }

    private static SearchModel CreateMinimalSearchModel()
    {
        return new SearchModel
        {
            Id = 1,
            Name = "Test Search",
            UserName = "testuser",
            SubmitDt = DateTime.Now.AddHours(-1),
            StartDt = DateTime.Now.AddMinutes(-30),
            EndDt = DateTime.Now,
            ExtractMisData = false
        };
    }

    private static SearchModel CreateFullSearchModel()
    {
        return new SearchModel
        {
            Id = 1,
            Name = "Full Search",
            UserName = "testuser",
            SubmitDt = DateTime.Now.AddHours(-1),
            StartDt = DateTime.Now.AddMinutes(-30),
            EndDt = DateTime.Now,
            MinimumDt = new DateTime(2024, 1, 1),
            MaximumDt = new DateTime(2024, 12, 31),
            ExtractMisData = true,
            WorkOrderFilter = [new WorkOrderFilterEntry { WorkOrderNumber = 12345, ItemNumber = "ITEM-001" }],
            ItemNumberFilter = [new ItemNumberFilterEntry { ItemNumber = "ITEM-001" }],
            ProfitCenterFilter = [new ProfitCenterFilterEntry { Code = "PC01", Description = "Profit Center 1" }],
            WorkCenterFilter = [new WorkCenterFilterEntry { Code = "WC01", Description = "Work Center 1" }],
            OperatorFilter = [new OperatorFilterEntry { UserId = "OP01", FullName = "Operator 1" }],
            ComponentLotFilter = [new ComponentLotFilterEntry { LotNumber = "LOT001" }],
            ItemOperationMisFilter = [new ItemOperationMisFilterEntry { ItemNumber = "ITEM-001", OperationNumber = "10", MisNumber = "MIS-001" }]
        };
    }
}