// Copyright (c) Sbroenne. All rights reserved.
// Licensed under the MIT License.

using System.Runtime.InteropServices;
using Sbroenne.ExcelMcp.Core.Tests.Helpers;
using Xunit;
using Xunit.Abstractions;

namespace Sbroenne.ExcelMcp.Diagnostics.Tests.Diagnostics;

/// <summary>
/// Diagnostic tests to understand Excel's native behavior regarding RefreshTable().
/// These tests use RAW Excel COM API without our abstraction layer to determine:
/// 1. Which operations require RefreshTable() to take effect
/// 2. Which operations work immediately without RefreshTable()
///
/// Purpose: Inform optimization decisions by understanding Excel's true behavior.
/// </summary>
[Trait("Category", "Integration")]
[Trait("Layer", "Diagnostics")]
[Trait("Speed", "Slow")]
[Trait("RequiresExcel", "true")]
[Trait("RunType", "OnDemand")]
public class PivotTableRefreshBehaviorTests : IClassFixture<TempDirectoryFixture>, IDisposable
{
    private readonly string _tempDir;
    private readonly ITestOutputHelper _output;
    private dynamic? _excel;
    private dynamic? _workbook;
    private readonly string _testFile;

    // Excel constants
    private const int xlRowField = 1;
    private const int xlColumnField = 2;
    private const int xlDataField = 4;
    private const int xlPageField = 3;
    private const int xlHidden = 0;
    private const int xlSum = -4157;
    private const int xlCount = -4112;
    private const int xlAverage = -4106;
    private const int xlDatabase = 1;

    public PivotTableRefreshBehaviorTests(TempDirectoryFixture fixture, ITestOutputHelper output)
    {
        _tempDir = fixture.TempDir;
        _output = output;
        _testFile = Path.Combine(_tempDir, $"RefreshBehavior_{Guid.NewGuid():N}.xlsx");

        // Create Excel instance directly (no abstraction)
        var excelType = Type.GetTypeFromProgID("Excel.Application");
        _excel = Activator.CreateInstance(excelType!);
        _excel.Visible = false;
        _excel.DisplayAlerts = false;

        // Create workbook with test data
        _workbook = _excel.Workbooks.Add();
        SetupTestData();
    }

    private void SetupTestData()
    {
        dynamic sheet = _workbook.Worksheets[1];
        sheet.Name = "Data";

        // Create simple sales data
        sheet.Range["A1"].Value2 = "Region";
        sheet.Range["B1"].Value2 = "Product";
        sheet.Range["C1"].Value2 = "Sales";
        sheet.Range["D1"].Value2 = "Quantity";

        sheet.Range["A2"].Value2 = "North";
        sheet.Range["B2"].Value2 = "Widget";
        sheet.Range["C2"].Value2 = 100;
        sheet.Range["D2"].Value2 = 10;

        sheet.Range["A3"].Value2 = "South";
        sheet.Range["B3"].Value2 = "Gadget";
        sheet.Range["C3"].Value2 = 200;
        sheet.Range["D3"].Value2 = 20;

        sheet.Range["A4"].Value2 = "North";
        sheet.Range["B4"].Value2 = "Gadget";
        sheet.Range["C4"].Value2 = 150;
        sheet.Range["D4"].Value2 = 15;

        sheet.Range["A5"].Value2 = "South";
        sheet.Range["B5"].Value2 = "Widget";
        sheet.Range["C5"].Value2 = 175;
        sheet.Range["D5"].Value2 = 17;
    }

    private dynamic CreatePivotTable(string name)
    {
        dynamic dataSheet = _workbook.Worksheets["Data"];
        dynamic sourceRange = dataSheet.Range["A1:D5"];

        // Add a new sheet for the PivotTable
        dynamic pivotSheet = _workbook.Worksheets.Add();
        pivotSheet.Name = $"Pivot_{name}";

        // Create PivotCache and PivotTable
        dynamic pivotCache = _workbook.PivotCaches().Create(xlDatabase, sourceRange);
        dynamic pivotTable = pivotCache.CreatePivotTable(pivotSheet.Range["A3"], name);

        return pivotTable;
    }

    [Fact]
    public void AddRowField_WithoutRefresh_VerifyOrientationTakesEffect()
    {
        // Arrange
        var pivot = CreatePivotTable("TestPivot1");
        dynamic field = pivot.PivotFields("Region");

        // Act - Set orientation WITHOUT calling RefreshTable()
        field.Orientation = xlRowField;
        // NO pivot.RefreshTable();

        // Assert - Check if orientation took effect
        int actualOrientation = Convert.ToInt32(field.Orientation);
        _output.WriteLine($"AddRowField WITHOUT RefreshTable:");
        _output.WriteLine($"  Expected Orientation: {xlRowField} (xlRowField)");
        _output.WriteLine($"  Actual Orientation:   {actualOrientation}");
        _output.WriteLine($"  Match: {actualOrientation == xlRowField}");

        // Check if data appears in the PivotTable
        dynamic pivotSheet = _workbook.Worksheets.Item($"Pivot_TestPivot1");
        string cellA4Value = pivotSheet.Range["A4"].Value2?.ToString() ?? "(empty)";
        _output.WriteLine($"  Cell A4 value: {cellA4Value}");

        Assert.Equal(xlRowField, actualOrientation);
    }

    [Fact]
    public void AddRowField_WithRefresh_VerifyOrientationTakesEffect()
    {
        // Arrange
        var pivot = CreatePivotTable("TestPivot2");
        dynamic field = pivot.PivotFields("Region");

        // Act - Set orientation WITH RefreshTable()
        field.Orientation = xlRowField;
        pivot.RefreshTable();

        // Assert
        int actualOrientation = Convert.ToInt32(field.Orientation);
        _output.WriteLine($"AddRowField WITH RefreshTable:");
        _output.WriteLine($"  Expected Orientation: {xlRowField} (xlRowField)");
        _output.WriteLine($"  Actual Orientation:   {actualOrientation}");
        _output.WriteLine($"  Match: {actualOrientation == xlRowField}");

        dynamic pivotSheet = _workbook.Worksheets.Item($"Pivot_TestPivot2");
        string cellA4Value = pivotSheet.Range["A4"].Value2?.ToString() ?? "(empty)";
        _output.WriteLine($"  Cell A4 value: {cellA4Value}");

        Assert.Equal(xlRowField, actualOrientation);
    }

    [Fact]
    public void AddValueField_WithoutRefresh_VerifyDataAppears()
    {
        // Arrange
        var pivot = CreatePivotTable("TestPivot3");
        dynamic rowField = pivot.PivotFields("Region");
        rowField.Orientation = xlRowField;

        dynamic valueField = pivot.PivotFields("Sales");

        // Act - Add value field WITHOUT RefreshTable()
        valueField.Orientation = xlDataField;
        valueField.Function = xlSum;
        // NO pivot.RefreshTable();

        // Assert
        int actualOrientation = Convert.ToInt32(valueField.Orientation);
        _output.WriteLine($"AddValueField WITHOUT RefreshTable:");
        _output.WriteLine($"  Expected Orientation: {xlDataField} (xlDataField)");
        _output.WriteLine($"  Actual Orientation:   {actualOrientation}");

        // Check if sum values appear
        dynamic pivotSheet = _workbook.Worksheets.Item($"Pivot_TestPivot3");
        var cellB4 = pivotSheet.Range["B4"].Value2;
        _output.WriteLine($"  Cell B4 (should have sum): {cellB4}");

        Assert.Equal(xlDataField, actualOrientation);
    }

    [Fact]
    public void RemoveField_WithoutRefresh_VerifyFieldHidden()
    {
        // Arrange
        var pivot = CreatePivotTable("TestPivot4");
        dynamic field = pivot.PivotFields("Region");
        field.Orientation = xlRowField;
        pivot.RefreshTable(); // Initial setup with refresh

        // Act - Remove field WITHOUT RefreshTable()
        field.Orientation = xlHidden;
        // NO pivot.RefreshTable();

        // Assert
        int actualOrientation = Convert.ToInt32(field.Orientation);
        _output.WriteLine($"RemoveField WITHOUT RefreshTable:");
        _output.WriteLine($"  Expected Orientation: {xlHidden} (xlHidden)");
        _output.WriteLine($"  Actual Orientation:   {actualOrientation}");
        _output.WriteLine($"  Match: {actualOrientation == xlHidden}");

        Assert.Equal(xlHidden, actualOrientation);
    }

    [Fact]
    public void SetNumberFormat_WithoutRefresh_VerifyFormatApplied()
    {
        // Arrange
        var pivot = CreatePivotTable("TestPivot5");
        dynamic rowField = pivot.PivotFields("Region");
        rowField.Orientation = xlRowField;

        dynamic valueField = pivot.PivotFields("Sales");
        valueField.Orientation = xlDataField;
        valueField.Function = xlSum;
        pivot.RefreshTable(); // Initial setup

        // Act - Set number format WITHOUT RefreshTable()
        string formatBefore = valueField.NumberFormat?.ToString() ?? "(none)";
        valueField.NumberFormat = "$#,##0.00";
        // NO pivot.RefreshTable();

        // Assert
        string formatAfter = valueField.NumberFormat?.ToString() ?? "(none)";
        _output.WriteLine($"SetNumberFormat WITHOUT RefreshTable:");
        _output.WriteLine($"  Format Before: {formatBefore}");
        _output.WriteLine($"  Format After:  {formatAfter}");
        _output.WriteLine($"  Match Expected: {formatAfter == "$#,##0.00"}");

        Assert.Equal("$#,##0.00", formatAfter);
    }

    [Fact]
    public void ChangeFunction_WithoutRefresh_VerifyFunctionChanged()
    {
        // Arrange
        var pivot = CreatePivotTable("TestPivot6");
        dynamic rowField = pivot.PivotFields("Region");
        rowField.Orientation = xlRowField;

        dynamic valueField = pivot.PivotFields("Sales");
        valueField.Orientation = xlDataField;
        valueField.Function = xlSum;
        pivot.RefreshTable(); // Initial setup

        // Capture value with SUM
        dynamic pivotSheet = _workbook.Worksheets.Item($"Pivot_TestPivot6");
        var sumValue = pivotSheet.Range["B4"].Value2;
        _output.WriteLine($"Value with SUM: {sumValue}");

        // Act - Change function WITHOUT RefreshTable()
        valueField.Function = xlCount;
        // NO pivot.RefreshTable();

        // Assert
        int actualFunction = Convert.ToInt32(valueField.Function);
        var countValue = pivotSheet.Range["B4"].Value2;

        _output.WriteLine($"ChangeFunction WITHOUT RefreshTable:");
        _output.WriteLine($"  Expected Function: {xlCount} (xlCount)");
        _output.WriteLine($"  Actual Function:   {actualFunction}");
        _output.WriteLine($"  Value after COUNT: {countValue}");
        _output.WriteLine($"  Values different:  {!Equals(sumValue, countValue)}");

        Assert.Equal(xlCount, actualFunction);
    }

    [Fact]
    public void MultipleOperations_WithoutRefresh_VerifyAllApplied()
    {
        // Arrange
        var pivot = CreatePivotTable("TestPivot7");

        // Act - Multiple operations WITHOUT RefreshTable() between them
        dynamic regionField = pivot.PivotFields("Region");
        regionField.Orientation = xlRowField;

        dynamic productField = pivot.PivotFields("Product");
        productField.Orientation = xlColumnField;

        dynamic salesField = pivot.PivotFields("Sales");
        salesField.Orientation = xlDataField;
        salesField.Function = xlSum;
        salesField.NumberFormat = "$#,##0";

        // NO RefreshTable() at all

        // Assert
        _output.WriteLine($"Multiple Operations WITHOUT any RefreshTable:");
        _output.WriteLine($"  Region Orientation:  {regionField.Orientation} (expected {xlRowField})");
        _output.WriteLine($"  Product Orientation: {productField.Orientation} (expected {xlColumnField})");
        _output.WriteLine($"  Sales Orientation:   {salesField.Orientation} (expected {xlDataField})");
        _output.WriteLine($"  Sales NumberFormat:  {salesField.NumberFormat}");

        Assert.Equal(xlRowField, Convert.ToInt32(regionField.Orientation));
        Assert.Equal(xlColumnField, Convert.ToInt32(productField.Orientation));
        Assert.Equal(xlDataField, Convert.ToInt32(salesField.Orientation));
    }

    [Fact]
    public void Persistence_WithoutRefresh_VerifySaveAndReopen()
    {
        // Arrange
        var pivot = CreatePivotTable("TestPivot8");

        dynamic regionField = pivot.PivotFields("Region");
        regionField.Orientation = xlRowField;

        dynamic salesField = pivot.PivotFields("Sales");
        salesField.Orientation = xlDataField;
        salesField.NumberFormat = "0.00%";

        // NO RefreshTable()

        // Save and close
        _workbook.SaveAs(_testFile);
        _workbook.Close(false);
        Marshal.ReleaseComObject(_workbook);
        _workbook = null;

        // Reopen
        _workbook = _excel.Workbooks.Open(_testFile);
        dynamic reopenedPivot = _workbook.Worksheets["Pivot_TestPivot8"].PivotTables("TestPivot8");

        // Assert - Check if settings persisted
        dynamic reopenedRegion = reopenedPivot.PivotFields("Region");
        dynamic reopenedSales = reopenedPivot.PivotFields("Sales");

        _output.WriteLine($"Persistence WITHOUT RefreshTable:");
        _output.WriteLine($"  Region Orientation after reopen: {reopenedRegion.Orientation}");
        _output.WriteLine($"  Sales Orientation after reopen:  {reopenedSales.Orientation}");
        _output.WriteLine($"  Sales NumberFormat after reopen: {reopenedSales.NumberFormat}");

        // The question: do these persist without RefreshTable()?
        // FINDING: Row field orientation DOES persist, but value field orientation does NOT
        // This proves RefreshTable() IS needed for value fields to persist properly
        Assert.Equal(xlRowField, Convert.ToInt32(reopenedRegion.Orientation));
        // Value field did NOT persist - this is expected without RefreshTable()
        Assert.Equal(xlHidden, Convert.ToInt32(reopenedSales.Orientation)); // 0 = Hidden, not 4 = DataField
    }

    [Fact]
    public void Persistence_WithRefresh_VerifySaveAndReopen()
    {
        // Arrange
        var pivot = CreatePivotTable("TestPivot9");

        dynamic regionField = pivot.PivotFields("Region");
        regionField.Orientation = xlRowField;

        dynamic salesField = pivot.PivotFields("Sales");
        salesField.Orientation = xlDataField;

        // CRITICAL: RefreshTable after structure changes, BEFORE setting visual properties
        pivot.RefreshTable();

        // Now set visual properties
        salesField.NumberFormat = "0.00%";

        // Save and close
        _workbook.SaveAs(_testFile);
        _workbook.Close(false);
        Marshal.ReleaseComObject(_workbook);
        _workbook = null;

        // Reopen
        _workbook = _excel.Workbooks.Open(_testFile);
        dynamic reopenedPivot = _workbook.Worksheets["Pivot_TestPivot9"].PivotTables("TestPivot9");

        // Assert - Check if settings persisted
        dynamic reopenedRegion = reopenedPivot.PivotFields("Region");
        dynamic reopenedSales = reopenedPivot.PivotFields("Sum of Sales");  // Name changes after refresh!

        _output.WriteLine($"Persistence WITH RefreshTable (before visual props):");
        _output.WriteLine($"  Region Orientation after reopen: {reopenedRegion.Orientation}");
        _output.WriteLine($"  Sum of Sales Orientation after reopen:  {reopenedSales.Orientation}");
        _output.WriteLine($"  Sum of Sales NumberFormat after reopen: {reopenedSales.NumberFormat}");

        Assert.Equal(xlRowField, Convert.ToInt32(reopenedRegion.Orientation));
        Assert.Equal(xlDataField, Convert.ToInt32(reopenedSales.Orientation));
        Assert.Equal("0.00%", reopenedSales.NumberFormat?.ToString());
    }

    [Fact]
    public void FunctionChange_WithoutRefresh_VerifyPersistence()
    {
        // Arrange - Create PivotTable with value field using SUM
        var pivot = CreatePivotTable("TestPivot10");
        dynamic rowField = pivot.PivotFields("Region");
        rowField.Orientation = xlRowField;

        dynamic valueField = pivot.PivotFields("Sales");
        valueField.Orientation = xlDataField;
        valueField.Function = xlSum;
        pivot.RefreshTable(); // Initial setup - structure must be refreshed

        // Act - Change function WITHOUT RefreshTable()
        dynamic dataField = pivot.DataFields[1];  // Get from DataFields collection
        dataField.Function = xlAverage;
        // NO pivot.RefreshTable();

        // Save and close
        _workbook.SaveAs(_testFile);
        _workbook.Close(false);
        Marshal.ReleaseComObject(_workbook);
        _workbook = null;

        // Reopen
        _workbook = _excel.Workbooks.Open(_testFile);
        dynamic reopenedPivot = _workbook.Worksheets["Pivot_TestPivot10"].PivotTables("TestPivot10");
        dynamic reopenedDataField = reopenedPivot.DataFields[1];
        int reopenedFunction = Convert.ToInt32(reopenedDataField.Function);

        _output.WriteLine($"Function Change WITHOUT RefreshTable - Persistence Test:");
        _output.WriteLine($"  Expected Function: {xlAverage} (xlAverage)");
        _output.WriteLine($"  Actual Function:   {reopenedFunction}");
        _output.WriteLine($"  Persisted: {reopenedFunction == xlAverage}");

        // Question: Does function change persist without RefreshTable()?
        // If this fails, RefreshTable() IS required after SetFieldFunction
        Assert.Equal(xlAverage, reopenedFunction);
    }

    [Fact]
    public void Filter_WithoutRefresh_VerifyPersistence()
    {
        // Arrange - Create PivotTable with row field
        var pivot = CreatePivotTable("TestPivot11");
        dynamic rowField = pivot.PivotFields("Region");
        rowField.Orientation = xlRowField;
        pivot.RefreshTable(); // Initial setup

        // Act - Apply filter WITHOUT RefreshTable()
        // Set only "North" visible
        dynamic items = rowField.PivotItems();
        for (int i = 1; i <= items.Count; i++)
        {
            dynamic item = items[i];
            string itemName = item.Name?.ToString() ?? "";
            item.Visible = (itemName == "North");
        }
        // NO pivot.RefreshTable();

        // Save and close
        _workbook.SaveAs(_testFile);
        _workbook.Close(false);
        Marshal.ReleaseComObject(_workbook);
        _workbook = null;

        // Reopen
        _workbook = _excel.Workbooks.Open(_testFile);
        dynamic reopenedPivot = _workbook.Worksheets["Pivot_TestPivot11"].PivotTables("TestPivot11");
        dynamic reopenedField = reopenedPivot.PivotFields("Region");
        dynamic reopenedItems = reopenedField.PivotItems();

        int visibleCount = 0;
        string visibleItemName = "";
        for (int i = 1; i <= reopenedItems.Count; i++)
        {
            dynamic item = reopenedItems[i];
            if (item.Visible)
            {
                visibleCount++;
                visibleItemName = item.Name?.ToString() ?? "";
            }
        }

        _output.WriteLine($"Filter WITHOUT RefreshTable - Persistence Test:");
        _output.WriteLine($"  Expected: Only 'North' visible");
        _output.WriteLine($"  Visible count: {visibleCount}");
        _output.WriteLine($"  Visible item: {visibleItemName}");

        // Question: Does filter persist without RefreshTable()?
        // If this fails, RefreshTable() IS required after SetFieldFilter
        Assert.Equal(1, visibleCount);
        Assert.Equal("North", visibleItemName);
    }

    public void Dispose()
    {
        GC.SuppressFinalize(this);
        try
        {
            if (_workbook != null)
            {
                _workbook.Close(false);
                Marshal.ReleaseComObject(_workbook);
            }
        }
#pragma warning disable CA1031 // Intentional: cleanup code must not throw
        catch (Exception) { /* Ignore cleanup errors */ }
#pragma warning restore CA1031

        try
        {
            if (_excel != null)
            {
                _excel.Quit();
                Marshal.ReleaseComObject(_excel);
            }
        }
#pragma warning disable CA1031 // Intentional: cleanup code must not throw
        catch (Exception) { /* Ignore cleanup errors */ }
#pragma warning restore CA1031

        // Clean up test file
        try
        {
            if (File.Exists(_testFile))
                File.Delete(_testFile);
        }
#pragma warning disable CA1031 // Intentional: cleanup code must not throw
        catch (Exception) { /* Ignore file cleanup errors */ }
#pragma warning restore CA1031

        GC.Collect();
        GC.WaitForPendingFinalizers();
    }
}




