// =============================================================================
// DIAGNOSTIC TESTS - Direct Excel COM API Behavior for Data Model
// =============================================================================
// Purpose: Understand what Excel COM API actually does for Data Model operations
// These tests document the REAL behavior of Excel's Data Model/Power Pivot COM API
// =============================================================================

// Suppress invalid-dynamic-call warnings - this diagnostic test file intentionally uses
// dynamic COM interop patterns to explore Excel's behavior. The Range[cell] pattern is
// standard COM interop for Excel and cannot be statically analyzed.
#pragma warning disable CS1061 // Member access on dynamic type - expected for COM interop exploration

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

namespace Sbroenne.ExcelMcp.Diagnostics.Tests.Integration.Diagnostics;

/// <summary>
/// Diagnostic tests for Data Model (Power Pivot) COM API behavior.
/// These tests use raw COM calls to understand Excel's actual behavior.
/// </summary>
[Trait("Category", "Integration")]
[Trait("Speed", "Slow")]
[Trait("Layer", "Diagnostics")]
[Trait("Feature", "DataModel")]
[Trait("RequiresExcel", "true")]
[Trait("RunType", "OnDemand")]
public class DataModelComApiBehaviorTests : IClassFixture<TempDirectoryFixture>, IDisposable
{
    private readonly string _tempDir;
    private readonly ITestOutputHelper _output;
    private dynamic? _excel;
    private dynamic? _workbook;
    private readonly string _testFile;

    // Simple M code for creating Data Model tables
    private const string SalesQuery = """
        let
            Source = #table(
                {"Product", "Amount", "Quantity"},
                {{"Widget", 100, 5}, {"Gadget", 200, 3}, {"Gizmo", 150, 7}}
            )
        in
            Source
        """;

    private const string ProductsQuery = """
        let
            Source = #table(
                {"ProductName", "Category", "Price"},
                {{"Widget", "Electronics", 20}, {"Gadget", "Electronics", 66.67}, {"Gizmo", "Tools", 21.43}}
            )
        in
            Source
        """;

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

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

        // Create new workbook
        _workbook = _excel.Workbooks.Add();
        _workbook.SaveAs(_testFile);

        _output.WriteLine($"Test file: {_testFile}");
    }

    public void Dispose()
    {
        try
        {
            if (_workbook != null)
            {
                _workbook.Close(false);
                ComUtilities.Release(ref _workbook);
            }
            if (_excel != null)
            {
                _excel.Quit();
                ComUtilities.Release(ref _excel);
            }
        }
        catch (Exception ex)
        {
            _output.WriteLine($"Cleanup error: {ex.Message}");
        }
        GC.SuppressFinalize(this);
        GC.Collect();
        GC.WaitForPendingFinalizers();
    }

    // =========================================================================
    // SCENARIO 1: Access Data Model
    // =========================================================================

    [Fact]
    public void Scenario1_AccessDataModel()
    {
        _output.WriteLine("=== SCENARIO 1: Access Data Model ===");

        dynamic? model = null;
        dynamic? modelTables = null;
        dynamic? modelRelationships = null;

        try
        {
            model = _workbook.Model;
            _output.WriteLine($"Model object obtained: {model != null}");

            modelTables = model.ModelTables;
            _output.WriteLine($"Initial ModelTables count: {modelTables.Count}");

            modelRelationships = model.ModelRelationships;
            _output.WriteLine($"Initial ModelRelationships count: {modelRelationships.Count}");

            // Check model name/properties
            try
            {
                string modelName = model.Name;
                _output.WriteLine($"Model name: {modelName}");
            }
            catch
            {
                _output.WriteLine("Model.Name not accessible");
            }
        }
        finally
        {
            ComUtilities.Release(ref modelRelationships);
            ComUtilities.Release(ref modelTables);
            ComUtilities.Release(ref model);
        }

        _output.WriteLine("=== SCENARIO 1 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 2: Create Table in Data Model (via Power Query)
    // =========================================================================

    [Fact]
    public void Scenario2_CreateTableInDataModel()
    {
        _output.WriteLine("=== SCENARIO 2: Create Table in Data Model ===");

        dynamic? queries = null;
        dynamic? query = null;
        dynamic? connections = null;
        dynamic? model = null;
        dynamic? modelTables = null;

        try
        {
            // First create a Power Query
            queries = _workbook.Queries;
            query = queries.Add("Sales", SalesQuery);
            _output.WriteLine("Power Query 'Sales' created");

            // Now load to Data Model using Add2 with CreateModelConnection=true
            connections = _workbook.Connections;

            string connString = $"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Sales";

            _output.WriteLine("\n--- Adding connection with CreateModelConnection=true ---");
            dynamic? conn = null;
            try
            {
                conn = connections.Add2(
                    "Query - Sales",                   // Name
                    "Power Query - Sales",             // Description
                    connString,                        // ConnectionString
                    "SELECT * FROM [Sales]",           // CommandText
                    2,                                 // lCmdtype (xlCmdSql = 2)
                    true,                              // CreateModelConnection - LOAD TO DATA MODEL
                    false                              // ImportRelationships
                );
                _output.WriteLine("Connection added with CreateModelConnection=true");

                // Refresh to load data into model
                conn.Refresh();
                _output.WriteLine("Connection refreshed");

                ComUtilities.Release(ref conn);
            }
            catch (COMException ex)
            {
                _output.WriteLine($"Add2 failed: 0x{ex.HResult:X8} - {ex.Message}");
                ComUtilities.Release(ref conn);
            }

            // Check if table appeared in Data Model
            model = _workbook.Model;
            modelTables = model.ModelTables;
            _output.WriteLine($"\nModelTables count after load: {modelTables.Count}");

            for (int i = 1; i <= modelTables.Count; i++)
            {
                dynamic? table = modelTables[i];
                _output.WriteLine($"  Table {i}: {table.Name}");

                // List columns
                dynamic? columns = table.ModelTableColumns;
                _output.WriteLine($"    Columns: {columns.Count}");
                for (int j = 1; j <= columns.Count; j++)
                {
                    dynamic? col = columns[j];
                    _output.WriteLine($"      - {col.Name} ({col.DataType})");
                    ComUtilities.Release(ref col);
                }
                ComUtilities.Release(ref columns);
                ComUtilities.Release(ref table);
            }
        }
        finally
        {
            ComUtilities.Release(ref modelTables);
            ComUtilities.Release(ref model);
            ComUtilities.Release(ref connections);
            ComUtilities.Release(ref query);
            ComUtilities.Release(ref queries);
        }

        _output.WriteLine("=== SCENARIO 2 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 3: Add DAX Measure
    // =========================================================================

    [Fact]
    public void Scenario3_AddDaxMeasure()
    {
        _output.WriteLine("=== SCENARIO 3: Add DAX Measure ===");

        dynamic? model = null;
        dynamic? modelTables = null;
        dynamic? measures = null;

        try
        {
            // First ensure we have a table in the model
            CreateDataModelTable("Sales", SalesQuery);

            model = _workbook.Model;
            modelTables = model.ModelTables;

            if (modelTables.Count == 0)
            {
                _output.WriteLine("No tables in Data Model. Skipping measure test.");
                return;
            }

            dynamic? table = modelTables[1];
            string tableName = table.Name;
            _output.WriteLine($"Adding measure to table: {tableName}");

            // Get measures collection
            measures = model.ModelMeasures;
            int measureCountBefore = measures.Count;
            _output.WriteLine($"Measures before add: {measureCountBefore}");

            // Add a measure
            _output.WriteLine("\n--- Adding DAX measure ---");
            dynamic? formatInfo = null;
            try
            {
                // ModelMeasures.Add signature: (MeasureName, AssociatedTable, Formula, FormatInformation, [Description])
                // FormatInformation is REQUIRED - get from Model.ModelFormatGeneral property
                formatInfo = model.ModelFormatGeneral;
                dynamic? measure = measures.Add(
                    "TotalAmount",                           // MeasureName
                    table,                                   // AssociatedTable
                    "SUM(Query[Amount])",                    // Formula (use table name "Query" from M code)
                    formatInfo,                              // FormatInformation (REQUIRED)
                    "Total of all amounts"                   // Description (optional)
                );

                _output.WriteLine($"Measure added: {measure.Name}");
                _output.WriteLine($"Formula: {measure.Formula}");

                ComUtilities.Release(ref measure);
            }
            catch (COMException ex)
            {
                _output.WriteLine($"Add measure failed: 0x{ex.HResult:X8}");
                _output.WriteLine($"Message: {ex.Message}");
            }
            finally
            {
                ComUtilities.Release(ref formatInfo);
            }

            // Verify
            ComUtilities.Release(ref measures);
            measures = model.ModelMeasures;
            _output.WriteLine($"\nMeasures after add: {measures.Count}");

            ComUtilities.Release(ref table);
        }
        finally
        {
            ComUtilities.Release(ref measures);
            ComUtilities.Release(ref modelTables);
            ComUtilities.Release(ref model);
        }

        _output.WriteLine("=== SCENARIO 3 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 4: Update DAX Measure
    // =========================================================================

    [Fact]
    public void Scenario4_UpdateDaxMeasure()
    {
        _output.WriteLine("=== SCENARIO 4: Update DAX Measure ===");

        dynamic? model = null;
        dynamic? measures = null;

        try
        {
            // Setup: Create table and measure
            CreateDataModelTable("Sales", SalesQuery);
            CreateMeasure("TotalAmount", "SUM(Sales[Amount])");

            model = _workbook.Model;
            measures = model.ModelMeasures;

            if (measures.Count == 0)
            {
                _output.WriteLine("No measures found. Skipping update test.");
                return;
            }

            // Find and update the measure
            dynamic? measure = null;
            for (int i = 1; i <= measures.Count; i++)
            {
                dynamic? m = measures[i];
                if (m.Name == "TotalAmount")
                {
                    measure = m;
                    break;
                }
                ComUtilities.Release(ref m);
            }

            if (measure == null)
            {
                _output.WriteLine("Measure 'TotalAmount' not found");
                return;
            }

            _output.WriteLine($"Original formula: {measure.Formula}");

            // Update formula
            _output.WriteLine("\n--- Updating measure formula ---");
            try
            {
                measure.Formula = "SUM(Sales[Amount]) * 1.1";
                _output.WriteLine($"New formula: {measure.Formula}");
            }
            catch (COMException ex)
            {
                _output.WriteLine($"Update failed: 0x{ex.HResult:X8} - {ex.Message}");
            }

            // Update description
            try
            {
                measure.Description = "Updated: Total with 10% markup";
                _output.WriteLine($"New description: {measure.Description}");
            }
            catch (COMException ex)
            {
                _output.WriteLine($"Description update failed: {ex.Message}");
            }

            ComUtilities.Release(ref measure);
        }
        finally
        {
            ComUtilities.Release(ref measures);
            ComUtilities.Release(ref model);
        }

        _output.WriteLine("=== SCENARIO 4 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 5: Delete DAX Measure
    // =========================================================================

    [Fact]
    public void Scenario5_DeleteDaxMeasure()
    {
        _output.WriteLine("=== SCENARIO 5: Delete DAX Measure ===");

        dynamic? model = null;
        dynamic? measures = null;

        try
        {
            // Setup
            CreateDataModelTable("Sales", SalesQuery);
            CreateMeasure("ToDelete", "SUM(Sales[Amount])");

            model = _workbook.Model;
            measures = model.ModelMeasures;

            int countBefore = measures.Count;
            _output.WriteLine($"Measures before delete: {countBefore}");

            // Find and delete the measure
            for (int i = 1; i <= measures.Count; i++)
            {
                dynamic? m = measures[i];
                if (m.Name == "ToDelete")
                {
                    _output.WriteLine("\n--- Deleting measure 'ToDelete' ---");
                    m.Delete();
                    _output.WriteLine("Measure deleted");
                    ComUtilities.Release(ref m);
                    break;
                }
                ComUtilities.Release(ref m);
            }

            // Verify
            ComUtilities.Release(ref measures);
            measures = model.ModelMeasures;
            _output.WriteLine($"Measures after delete: {measures.Count}");

            Assert.Equal(countBefore - 1, (int)measures.Count);
        }
        finally
        {
            ComUtilities.Release(ref measures);
            ComUtilities.Release(ref model);
        }

        _output.WriteLine("=== SCENARIO 5 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 6: Create Relationship Between Tables
    // =========================================================================

    [Fact]
    public void Scenario6_CreateRelationship()
    {
        _output.WriteLine("=== SCENARIO 6: Create Relationship ===");

        dynamic? model = null;
        dynamic? modelTables = null;
        dynamic? relationships = null;

        try
        {
            // Create two tables with related columns
            CreateDataModelTable("Sales", SalesQuery);
            CreateDataModelTable("Products", ProductsQuery);

            model = _workbook.Model;
            modelTables = model.ModelTables;
            relationships = model.ModelRelationships;

            _output.WriteLine($"Tables in model: {modelTables.Count}");
            _output.WriteLine($"Relationships before: {relationships.Count}");

            if (modelTables.Count < 2)
            {
                _output.WriteLine("Need at least 2 tables for relationship test");
                return;
            }

            // Find the tables and columns
            dynamic? salesTable = null;
            dynamic? productsTable = null;
            dynamic? salesProductCol = null;
            dynamic? productsNameCol = null;

            for (int i = 1; i <= modelTables.Count; i++)
            {
                dynamic? t = modelTables[i];
                string name = t.Name;
                if (name == "Sales")
                    salesTable = t;
                else if (name == "Products")
                    productsTable = t;
                else
                    ComUtilities.Release(ref t);
            }

            if (salesTable != null && productsTable != null)
            {
                // Get the columns
                dynamic? salesCols = salesTable.ModelTableColumns;
                for (int i = 1; i <= salesCols.Count; i++)
                {
                    dynamic? col = salesCols[i];
                    if (col.Name == "Product")
                    {
                        salesProductCol = col;
                        break;
                    }
                    ComUtilities.Release(ref col);
                }
                ComUtilities.Release(ref salesCols);

                dynamic? prodCols = productsTable.ModelTableColumns;
                for (int i = 1; i <= prodCols.Count; i++)
                {
                    dynamic? col = prodCols[i];
                    if (col.Name == "ProductName")
                    {
                        productsNameCol = col;
                        break;
                    }
                    ComUtilities.Release(ref col);
                }
                ComUtilities.Release(ref prodCols);

                if (salesProductCol != null && productsNameCol != null)
                {
                    _output.WriteLine("\n--- Creating relationship ---");
                    try
                    {
                        dynamic? rel = relationships.Add(
                            salesProductCol,     // ForeignKeyColumn (many side)
                            productsNameCol      // PrimaryKeyColumn (one side)
                        );

                        _output.WriteLine($"Relationship created");
                        _output.WriteLine($"  From: {rel.ForeignKeyColumn.Name} in {rel.ForeignKeyTable.Name}");
                        _output.WriteLine($"  To: {rel.PrimaryKeyColumn.Name} in {rel.PrimaryKeyTable.Name}");
                        _output.WriteLine($"  Active: {rel.Active}");

                        ComUtilities.Release(ref rel);
                    }
                    catch (COMException ex)
                    {
                        _output.WriteLine($"Create relationship failed: 0x{ex.HResult:X8}");
                        _output.WriteLine($"Message: {ex.Message}");
                    }
                }
                else
                {
                    _output.WriteLine("Could not find matching columns for relationship");
                }

                ComUtilities.Release(ref salesProductCol);
                ComUtilities.Release(ref productsNameCol);
            }

            // Verify
            ComUtilities.Release(ref relationships);
            relationships = model.ModelRelationships;
            _output.WriteLine($"\nRelationships after: {relationships.Count}");

            ComUtilities.Release(ref salesTable);
            ComUtilities.Release(ref productsTable);
        }
        finally
        {
            ComUtilities.Release(ref relationships);
            ComUtilities.Release(ref modelTables);
            ComUtilities.Release(ref model);
        }

        _output.WriteLine("=== SCENARIO 6 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 7: Delete Relationship
    // =========================================================================

    [Fact]
    public void Scenario7_DeleteRelationship()
    {
        _output.WriteLine("=== SCENARIO 7: Delete Relationship ===");

        dynamic? model = null;
        dynamic? relationships = null;

        try
        {
            // Setup: Create tables and relationship
            CreateDataModelTable("Sales", SalesQuery);
            CreateDataModelTable("Products", ProductsQuery);
            CreateRelationshipBetweenTables();

            model = _workbook.Model;
            relationships = model.ModelRelationships;

            int countBefore = relationships.Count;
            _output.WriteLine($"Relationships before delete: {countBefore}");

            if (countBefore == 0)
            {
                _output.WriteLine("No relationships to delete");
                return;
            }

            // Delete first relationship
            dynamic? rel = relationships[1];
            _output.WriteLine($"\n--- Deleting relationship ---");
            rel.Delete();
            _output.WriteLine("Relationship deleted");
            ComUtilities.Release(ref rel);

            // Verify
            ComUtilities.Release(ref relationships);
            relationships = model.ModelRelationships;
            _output.WriteLine($"Relationships after delete: {relationships.Count}");
        }
        finally
        {
            ComUtilities.Release(ref relationships);
            ComUtilities.Release(ref model);
        }

        _output.WriteLine("=== SCENARIO 7 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 8: Delete Query That Loaded to Data Model
    // =========================================================================

    [Fact]
    public void Scenario8_DeleteQueryWithDataModelTable()
    {
        _output.WriteLine("=== SCENARIO 8: Delete Query That Loaded to Data Model ===");

        dynamic? queries = null;
        dynamic? model = null;
        dynamic? modelTables = null;

        try
        {
            // Create query and load to data model
            CreateDataModelTable("OrphanTest", SalesQuery);

            queries = _workbook.Queries;
            model = _workbook.Model;
            modelTables = model.ModelTables;

            int queryCountBefore = queries.Count;
            int tableCountBefore = modelTables.Count;

            _output.WriteLine($"Queries before delete: {queryCountBefore}");
            _output.WriteLine($"Model tables before delete: {tableCountBefore}");

            // Find and delete the query
            dynamic? query = null;
            for (int i = 1; i <= queries.Count; i++)
            {
                dynamic? q = queries[i];
                if (q.Name == "OrphanTest")
                {
                    query = q;
                    break;
                }
                ComUtilities.Release(ref q);
            }

            if (query != null)
            {
                _output.WriteLine("\n--- Deleting query 'OrphanTest' ---");
                query.Delete();
                _output.WriteLine("Query deleted");
                ComUtilities.Release(ref query);
            }

            // KEY QUESTION: What happens to the Data Model table?
            ComUtilities.Release(ref modelTables);
            modelTables = model.ModelTables;

            _output.WriteLine($"\nQueries after delete: {queries.Count}");
            _output.WriteLine($"Model tables after delete: {modelTables.Count}");

            if (modelTables.Count == tableCountBefore)
            {
                _output.WriteLine("DATA MODEL TABLE SURVIVES! Query deletion does NOT remove model table.");

                // List remaining tables
                for (int i = 1; i <= modelTables.Count; i++)
                {
                    dynamic? t = modelTables[i];
                    _output.WriteLine($"  Orphaned table: {t.Name}");
                    ComUtilities.Release(ref t);
                }
            }
            else
            {
                _output.WriteLine("DATA MODEL TABLE REMOVED! Query deletion removes model table too.");
            }
        }
        finally
        {
            ComUtilities.Release(ref modelTables);
            ComUtilities.Release(ref model);
            ComUtilities.Release(ref queries);
        }

        _output.WriteLine("=== SCENARIO 8 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 9: Measure Referencing Deleted Table
    // =========================================================================

    [Fact]
    public void Scenario9_MeasureWithDeletedTable()
    {
        _output.WriteLine("=== SCENARIO 9: Measure Referencing Deleted Table ===");

        dynamic? queries = null;
        dynamic? model = null;
        dynamic? measures = null;

        try
        {
            // Create table and measure
            CreateDataModelTable("ToDelete", SalesQuery);
            CreateMeasure("OrphanedMeasure", "SUM(ToDelete[Amount])");

            model = _workbook.Model;
            measures = model.ModelMeasures;
            queries = _workbook.Queries;

            _output.WriteLine($"Measures before table delete: {measures.Count}");

            // Delete the query (which may or may not delete the model table)
            _output.WriteLine("\n--- Deleting source query ---");
            for (int i = 1; i <= queries.Count; i++)
            {
                dynamic? q = queries[i];
                if (q.Name == "ToDelete")
                {
                    q.Delete();
                    _output.WriteLine("Query deleted");
                    ComUtilities.Release(ref q);
                    break;
                }
                ComUtilities.Release(ref q);
            }

            // Check measures
            ComUtilities.Release(ref measures);
            measures = model.ModelMeasures;
            _output.WriteLine($"Measures after table delete: {measures.Count}");

            // Try to access the measure
            for (int i = 1; i <= measures.Count; i++)
            {
                dynamic? m = measures[i];
                try
                {
                    _output.WriteLine($"Measure: {m.Name}");
                    _output.WriteLine($"  Formula: {m.Formula}");
                    _output.WriteLine($"  Table: {m.AssociatedTable?.Name ?? "(null)"}");
                }
                catch (COMException ex)
                {
                    _output.WriteLine($"  ERROR accessing measure: {ex.Message}");
                }
                ComUtilities.Release(ref m);
            }
        }
        finally
        {
            ComUtilities.Release(ref measures);
            ComUtilities.Release(ref model);
            ComUtilities.Release(ref queries);
        }

        _output.WriteLine("=== SCENARIO 9 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 10: Multiple Measures on Same Table
    // =========================================================================

    [Fact]
    public void Scenario10_MultipleMeasures()
    {
        _output.WriteLine("=== SCENARIO 10: Multiple Measures on Same Table ===");

        dynamic? model = null;
        dynamic? measures = null;

        try
        {
            CreateDataModelTable("Sales", SalesQuery);

            CreateMeasure("TotalAmount", "SUM(Sales[Amount])");
            CreateMeasure("TotalQty", "SUM(Sales[Quantity])");
            CreateMeasure("AvgAmount", "AVERAGE(Sales[Amount])");
            CreateMeasure("CountRows", "COUNTROWS(Sales)");

            model = _workbook.Model;
            measures = model.ModelMeasures;

            _output.WriteLine($"Total measures created: {measures.Count}");

            for (int i = 1; i <= measures.Count; i++)
            {
                dynamic? m = measures[i];
                _output.WriteLine($"  {m.Name}: {m.Formula}");
                ComUtilities.Release(ref m);
            }
        }
        finally
        {
            ComUtilities.Release(ref measures);
            ComUtilities.Release(ref model);
        }

        _output.WriteLine("=== SCENARIO 10 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 11: Model Refresh
    // =========================================================================

    [Fact]
    public void Scenario11_ModelRefresh()
    {
        _output.WriteLine("=== SCENARIO 11: Model Refresh ===");

        dynamic? model = null;

        try
        {
            CreateDataModelTable("Sales", SalesQuery);

            model = _workbook.Model;

            _output.WriteLine("--- Attempting model.Refresh() ---");
            try
            {
                model.Refresh();
                _output.WriteLine("model.Refresh() succeeded");
            }
            catch (COMException ex)
            {
                _output.WriteLine($"model.Refresh() failed: 0x{ex.HResult:X8}");
                _output.WriteLine($"Message: {ex.Message}");
            }

            // Alternative: Refresh via connection
            _output.WriteLine("\n--- Attempting connection refresh ---");
            dynamic? connections = _workbook.Connections;
            for (int i = 1; i <= connections.Count; i++)
            {
                dynamic? conn = connections[i];
                try
                {
                    conn.Refresh();
                    _output.WriteLine($"Connection '{conn.Name}' refreshed");
                }
                catch (COMException ex)
                {
                    _output.WriteLine($"Connection '{conn.Name}' refresh failed: {ex.Message}");
                }
                ComUtilities.Release(ref conn);
            }
            ComUtilities.Release(ref connections);
        }
        finally
        {
            ComUtilities.Release(ref model);
        }

        _output.WriteLine("=== SCENARIO 11 COMPLETE ===\n");
    }

    // =========================================================================
    // SCENARIO 12: CUBEVALUE Recalculation After Data Model Refresh
    // Issue #313: CUBEVALUE formulas return error codes after refresh
    // =========================================================================

    [Fact]
    public void Scenario12_CubeValueRecalculationAfterRefresh()
    {
        _output.WriteLine("=== SCENARIO 12: CUBEVALUE Recalculation After Refresh ===");
        _output.WriteLine("This test verifies if CUBEVALUE formulas automatically recalculate after Data Model refresh.");

        dynamic? model = null;
        dynamic? sheet = null;
        dynamic? range = null;

        try
        {
            // Step 1: Create Data Model with a table
            _output.WriteLine("\n--- Step 1: Create Data Model table ---");
            CreateDataModelTable("Sales", SalesQuery);

            // Step 2: Create a DAX measure
            _output.WriteLine("\n--- Step 2: Create DAX measure ---");
            CreateMeasure("TotalAmount", "SUM(Sales[Amount])");

            // Step 3: Check current calculation mode
            _output.WriteLine("\n--- Step 3: Check calculation mode ---");
            int calcMode = _excel.Calculation;
            _output.WriteLine($"Current calculation mode: {calcMode} (xlCalculationAutomatic=-4105, xlCalculationManual=-4135)");

            // Step 4: Add CUBEVALUE formula to worksheet
            _output.WriteLine("\n--- Step 4: Add CUBEVALUE formula ---");
            sheet = _workbook.Worksheets[1];

            // First, let's check what cube connections exist
            _output.WriteLine("Checking available cube connections...");
            try
            {
                dynamic? connections = _workbook.Connections;
                for (int i = 1; i <= connections.Count; i++)
                {
                    dynamic? conn = connections[i];
                    _output.WriteLine($"  Connection {i}: '{conn.Name}' Type={conn.Type}");
                    ComUtilities.Release(ref conn);
                }
                ComUtilities.Release(ref connections);
            }
            catch (COMException ex)
            {
                _output.WriteLine($"  Error listing connections: {ex.Message}");
            }

            // Also check the Model's connection
            try
            {
                _output.WriteLine($"Model.Name: {model?.Name ?? "(model is null)"}");
            }
#pragma warning disable CA1031 // Intentional: diagnostic test logs all exceptions
            catch (Exception ex)
#pragma warning restore CA1031
            {
                _output.WriteLine($"  Error getting model name: {ex.Message}");
            }

            // Try different CUBEVALUE connection names
            // CUBEVALUE format: =CUBEVALUE(connection, member_expression)
            // The connection should be the Data Model connection name
            string cubeFormula = "=CUBEVALUE(\"ThisWorkbookDataModel\",\"[Measures].[TotalAmount]\")";
            range = sheet.Range["A1"];
            range.Formula = cubeFormula;
            _output.WriteLine($"Set formula in A1: {cubeFormula}");

            // Also try the alternative connection format in A2
            dynamic? rangeA2 = sheet.Range["A2"];
            string cubeFormula2 = "=CUBEVALUE(\"Query - Sales\",\"[Measures].[TotalAmount]\")";
            rangeA2.Formula = cubeFormula2;
            _output.WriteLine($"Set formula in A2: {cubeFormula2}");
            ComUtilities.Release(ref rangeA2);

            // Try different measure formats in A3-A5
            dynamic? rangeA3 = sheet.Range["A3"];
            rangeA3.Formula = "=CUBEVALUE(\"ThisWorkbookDataModel\",\"TotalAmount\")";
            _output.WriteLine($"Set formula in A3: =CUBEVALUE(\"ThisWorkbookDataModel\",\"TotalAmount\")");
            ComUtilities.Release(ref rangeA3);

            dynamic? rangeA4 = sheet.Range["A4"];
            rangeA4.Formula = "=CUBEVALUE(\"ThisWorkbookDataModel\",\"[Sales].[TotalAmount]\")";
            _output.WriteLine($"Set formula in A4: =CUBEVALUE(\"ThisWorkbookDataModel\",\"[Sales].[TotalAmount]\")");
            ComUtilities.Release(ref rangeA4);

            // Try a CUBEMEMBER first approach
            dynamic? rangeA5 = sheet.Range["A5"];
            rangeA5.Formula = "=CUBEMEMBER(\"ThisWorkbookDataModel\",\"[Measures].[TotalAmount]\")";
            _output.WriteLine($"Set formula in A5: =CUBEMEMBER(\"ThisWorkbookDataModel\",\"[Measures].[TotalAmount]\")");
            ComUtilities.Release(ref rangeA5);

            // Step 5: Read value BEFORE refresh
            _output.WriteLine("\n--- Step 5: Read A1 value BEFORE any explicit recalculation ---");
            object valueBefore = range.Value2;
            _output.WriteLine($"A1 Value (raw): {valueBefore}");
            _output.WriteLine($"A1 Value type: {valueBefore?.GetType().Name ?? "null"}");

            // Check for error codes
            if (valueBefore is int or double)
            {
                double numVal = Convert.ToDouble(valueBefore, System.Globalization.CultureInfo.InvariantCulture);
                if (numVal < 0)
                {
                    _output.WriteLine($"⚠️ NEGATIVE VALUE - likely Excel error code!");
                    DescribeExcelErrorCode(numVal);
                }
                else
                {
                    _output.WriteLine($"✅ Numeric value: {numVal} (expected ~450 from SUM of 100+200+150)");
                }
            }

            // Step 6: Refresh Data Model
            _output.WriteLine("\n--- Step 6: Refresh Data Model ---");
            model = _workbook.Model;
            try
            {
                model.Refresh();
                _output.WriteLine("model.Refresh() succeeded");
            }
            catch (COMException ex)
            {
                _output.WriteLine($"model.Refresh() failed: 0x{ex.HResult:X8} - {ex.Message}");
                // Try connection refresh as fallback
                _output.WriteLine("Attempting connection refresh instead...");
                RefreshAllConnections();
            }

            // Step 7: Read value AFTER refresh (without explicit Calculate)
            _output.WriteLine("\n--- Step 7: Read A1 value AFTER refresh (no explicit Calculate) ---");
            object valueAfterRefresh = range.Value2;
            _output.WriteLine($"A1 Value (raw): {valueAfterRefresh}");
            _output.WriteLine($"A1 Value type: {valueAfterRefresh?.GetType().Name ?? "null"}");

            if (valueAfterRefresh is int or double)
            {
                double numVal = Convert.ToDouble(valueAfterRefresh, System.Globalization.CultureInfo.InvariantCulture);
                if (numVal < 0)
                {
                    _output.WriteLine($"⚠️ STILL ERROR CODE after refresh!");
                    DescribeExcelErrorCode(numVal);
                }
                else
                {
                    _output.WriteLine($"✅ Numeric value: {numVal}");
                }
            }

            // Step 8: Call Application.Calculate and re-read
            _output.WriteLine("\n--- Step 8: Call Application.Calculate() ---");
            _excel.Calculate();
            _output.WriteLine("Application.Calculate() called");

            object valueAfterCalculate = range.Value2;
            _output.WriteLine($"A1 Value (raw): {valueAfterCalculate}");
            _output.WriteLine($"A1 Value type: {valueAfterCalculate?.GetType().Name ?? "null"}");

            if (valueAfterCalculate is int or double)
            {
                double numVal = Convert.ToDouble(valueAfterCalculate, System.Globalization.CultureInfo.InvariantCulture);
                if (numVal < 0)
                {
                    _output.WriteLine($"⚠️ STILL ERROR CODE after Calculate!");
                    DescribeExcelErrorCode(numVal);
                }
                else
                {
                    _output.WriteLine($"✅ Numeric value: {numVal}");
                }
            }

            // Step 9: Call Application.CalculateFull and re-read
            _output.WriteLine("\n--- Step 9: Call Application.CalculateFull() ---");
            _excel.CalculateFull();
            _output.WriteLine("Application.CalculateFull() called");

            object valueAfterFullCalc = range.Value2;
            _output.WriteLine($"A1 Value (raw): {valueAfterFullCalc}");
            _output.WriteLine($"A1 Value type: {valueAfterFullCalc?.GetType().Name ?? "null"}");

            if (valueAfterFullCalc is int or double)
            {
                double numVal = Convert.ToDouble(valueAfterFullCalc, System.Globalization.CultureInfo.InvariantCulture);
                if (numVal < 0)
                {
                    _output.WriteLine($"⚠️ STILL ERROR CODE after CalculateFull!");
                    DescribeExcelErrorCode(numVal);
                }
                else
                {
                    _output.WriteLine($"✅ Numeric value: {numVal} (expected ~450)");
                }
            }

            // Step 10: Also check A2 (Query - Sales connection)
            _output.WriteLine("\n--- Step 10: Check A2-A5 (different formula formats) ---");
            dynamic? rangeA2Check = sheet.Range["A2"];
            dynamic? rangeA3Check = sheet.Range["A3"];
            dynamic? rangeA4Check = sheet.Range["A4"];
            dynamic? rangeA5Check = sheet.Range["A5"];

            object a2Value = rangeA2Check.Value2;
            object a3Value = rangeA3Check.Value2;
            object a4Value = rangeA4Check.Value2;
            object a5Value = rangeA5Check.Value2;

            _output.WriteLine($"A2 (Query - Sales + [Measures].[TotalAmount]): {FormatValue(a2Value)}");
            _output.WriteLine($"A3 (ThisWorkbookDataModel + TotalAmount): {FormatValue(a3Value)}");
            _output.WriteLine($"A4 (ThisWorkbookDataModel + [Sales].[TotalAmount]): {FormatValue(a4Value)}");
            _output.WriteLine($"A5 (CUBEMEMBER [Measures].[TotalAmount]): {FormatValue(a5Value)}");

            ComUtilities.Release(ref rangeA2Check);
            ComUtilities.Release(ref rangeA3Check);
            ComUtilities.Release(ref rangeA4Check);
            ComUtilities.Release(ref rangeA5Check);

            // Step 11: Try CalculateFullRebuild (Ctrl+Alt+Shift+F9)
            _output.WriteLine("\n--- Step 11: Call Application.CalculateFullRebuild() ---");
            try
            {
                _excel.CalculateFullRebuild();
                _output.WriteLine("Application.CalculateFullRebuild() called");

                object valueAfterRebuild = range.Value2;
                _output.WriteLine($"A1 Value (raw): {valueAfterRebuild}");
                if (valueAfterRebuild is int or double)
                {
                    double numVal = Convert.ToDouble(valueAfterRebuild, System.Globalization.CultureInfo.InvariantCulture);
                    if (numVal < 0)
                    {
                        _output.WriteLine($"⚠️ STILL ERROR CODE after CalculateFullRebuild!");
                        DescribeExcelErrorCode(numVal);
                    }
                    else
                    {
                        _output.WriteLine($"✅ Numeric value: {numVal}");
                    }
                }
            }
            catch (COMException ex)
            {
                _output.WriteLine($"CalculateFullRebuild failed: {ex.Message}");
            }

            // Step 12: Save workbook
            // NOTE: The "close and reopen" part was never implemented, so we just save
            _output.WriteLine("\n--- Step 12: Save workbook ---");
            _workbook.Save();
            _output.WriteLine("Workbook saved");

            // Check value after save
            object valueAfterSave = range.Value2;
            _output.WriteLine($"A1 Value after save: {valueAfterSave}");

            // Summary
            _output.WriteLine("\n--- Summary ---");
            _output.WriteLine($"Before refresh:       {valueBefore}");
            _output.WriteLine($"After refresh:        {valueAfterRefresh}");
            _output.WriteLine($"After Calculate():    {valueAfterCalculate}");
            _output.WriteLine($"After CalculateFull():{valueAfterFullCalc}");
            _output.WriteLine($"After Save:           {valueAfterSave}");
            _output.WriteLine("");
            _output.WriteLine("CONCLUSION: If all values are negative error codes, CUBEVALUE");
            _output.WriteLine("formulas may require the workbook to be opened in visible Excel");
            _output.WriteLine("with Data Model initialized before formulas can calculate.");

            // Step 13: Try with Excel Visible
            _output.WriteLine("\n--- Step 13: Set Excel.Visible = true and recalculate ---");
            _excel.Visible = true;
            Thread.Sleep(2000);  // Give Excel time to render
            _excel.CalculateFullRebuild();
            Thread.Sleep(1000);
            object valueAfterVisible = range.Value2;
            _output.WriteLine($"A1 Value with Excel Visible: {valueAfterVisible}");
            if (valueAfterVisible is int or double)
            {
                double numVal = Convert.ToDouble(valueAfterVisible, System.Globalization.CultureInfo.InvariantCulture);
                if (numVal < 0)
                {
                    _output.WriteLine($"⚠️ STILL ERROR CODE even with Excel visible!");
                    DescribeExcelErrorCode(numVal);
                }
                else
                {
                    _output.WriteLine($"✅ SUCCESS! Value: {numVal} - Visible Excel fixed it!");
                }
            }
            _excel.Visible = false;  // Hide again for cleanup
        }
        catch (COMException ex)
        {
            _output.WriteLine($"Test failed with COM exception: 0x{ex.HResult:X8}");
            _output.WriteLine($"Message: {ex.Message}");
        }
        finally
        {
            ComUtilities.Release(ref range);
            ComUtilities.Release(ref sheet);
            ComUtilities.Release(ref model);
        }

        _output.WriteLine("=== SCENARIO 12 COMPLETE ===\n");
    }

    private static string FormatValue(object? value)
    {
        if (value == null) return "null";
        if (value is int or double)
        {
            double numVal = Convert.ToDouble(value, System.Globalization.CultureInfo.InvariantCulture);
            if (numVal < 0)
            {
                int code = Convert.ToInt32(numVal);
                string errName = code switch
                {
                    -2146826288 => "#NULL!",
                    -2146826281 => "#DIV/0!",
                    -2146826246 => "#VALUE!",
                    -2146826259 => "#REF!",
                    -2146826252 => "#NAME?",
                    -2146826265 => "#NUM!",
                    -2146826245 => "#N/A",
                    _ => $"Error {code}"
                };
                return $"{code} ({errName})";
            }
            return $"{numVal} ✅";
        }
        return value.ToString() ?? "empty";
    }

    private void DescribeExcelErrorCode(double errorCode)
    {
        int code = Convert.ToInt32(errorCode);
        string description = code switch
        {
            -2146826288 => "#NULL! - Incorrect range operator or missing intersection",
            -2146826281 => "#DIV/0! - Division by zero",
            -2146826246 => "#VALUE! - Wrong argument type",
            -2146826259 => "#REF! - Invalid cell reference",
            -2146826252 => "#NAME? - Unrecognized formula name",
            -2146826265 => "#NUM! - Invalid numeric value",
            -2146826245 => "#N/A - Value not found, CUBE member not found, or Data Model not refreshed/calculated",
            _ => $"Unknown error code: {code}"
        };
        _output.WriteLine($"  Error code {code} = {description}");
    }

    private void RefreshAllConnections()
    {
        dynamic? connections = null;
        try
        {
            connections = _workbook.Connections;
            for (int i = 1; i <= connections.Count; i++)
            {
                dynamic? conn = connections[i];
                try
                {
                    conn.Refresh();
                    _output.WriteLine($"  Refreshed connection: {conn.Name}");
                }
                catch (COMException ex)
                {
                    _output.WriteLine($"  Failed to refresh {conn.Name}: {ex.Message}");
                }
                finally
                {
                    ComUtilities.Release(ref conn);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref connections);
        }
    }

    // =========================================================================
    // Helper Methods
    // =========================================================================

    private void CreateDataModelTable(string queryName, string mCode)
    {
        dynamic? queries = null;
        dynamic? query = null;
        dynamic? connections = null;
        dynamic? conn = null;

        try
        {
            queries = _workbook.Queries;

            // Check if query already exists
            for (int i = 1; i <= queries.Count; i++)
            {
                dynamic? q = queries[i];
                if (q.Name == queryName)
                {
                    ComUtilities.Release(ref q);
                    _output.WriteLine($"Query '{queryName}' already exists");
                    return;
                }
                ComUtilities.Release(ref q);
            }

            query = queries.Add(queryName, mCode);

            connections = _workbook.Connections;
            string connString = $"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location={queryName}";

            conn = connections.Add2(
                $"Query - {queryName}",
                $"Power Query - {queryName}",
                connString,
                $"SELECT * FROM [{queryName}]",
                2,      // xlCmdSql
                true,   // CreateModelConnection
                false   // ImportRelationships
            );
            conn.Refresh();
            _output.WriteLine($"Created and loaded '{queryName}' to Data Model");
        }
        catch (COMException ex)
        {
            _output.WriteLine($"CreateDataModelTable failed: {ex.Message}");
        }
        finally
        {
            ComUtilities.Release(ref conn);
            ComUtilities.Release(ref connections);
            ComUtilities.Release(ref query);
            ComUtilities.Release(ref queries);
        }
    }

    private void CreateMeasure(string name, string formula)
    {
        dynamic? model = null;
        dynamic? modelTables = null;
        dynamic? measures = null;
        dynamic? table = null;
        dynamic? formatInfo = null;

        try
        {
            model = _workbook.Model;
            modelTables = model.ModelTables;

            if (modelTables.Count == 0)
            {
                _output.WriteLine($"Cannot create measure '{name}': No tables in model");
                return;
            }

            table = modelTables[1];
            measures = model.ModelMeasures;

            // ModelMeasures.Add signature: (MeasureName, AssociatedTable, Formula, FormatInformation, [Description])
            // FormatInformation is REQUIRED - get from Model.ModelFormatGeneral property
            formatInfo = model.ModelFormatGeneral;
            dynamic? measure = measures.Add(name, table, formula, formatInfo);
            _output.WriteLine($"Created measure '{name}'");
            ComUtilities.Release(ref measure);
        }
        catch (COMException ex)
        {
            _output.WriteLine($"CreateMeasure failed: {ex.Message}");
        }
        finally
        {
            ComUtilities.Release(ref formatInfo);
            ComUtilities.Release(ref table);
            ComUtilities.Release(ref measures);
            ComUtilities.Release(ref modelTables);
            ComUtilities.Release(ref model);
        }
    }

    private void CreateRelationshipBetweenTables()
    {
        dynamic? model = null;
        dynamic? modelTables = null;
        dynamic? relationships = null;

        try
        {
            model = _workbook.Model;
            modelTables = model.ModelTables;
            relationships = model.ModelRelationships;

            dynamic? salesTable = null;
            dynamic? productsTable = null;

            for (int i = 1; i <= modelTables.Count; i++)
            {
                dynamic? t = modelTables[i];
                string name = t.Name;
                if (name == "Sales")
                    salesTable = t;
                else if (name == "Products")
                    productsTable = t;
                else
                    ComUtilities.Release(ref t);
            }

            if (salesTable == null || productsTable == null)
            {
                _output.WriteLine("Cannot create relationship: Missing tables");
                ComUtilities.Release(ref salesTable);
                ComUtilities.Release(ref productsTable);
                return;
            }

            // Find columns
            dynamic? salesProductCol = null;
            dynamic? productsNameCol = null;

            dynamic? salesCols = salesTable.ModelTableColumns;
            for (int i = 1; i <= salesCols.Count; i++)
            {
                dynamic? col = salesCols[i];
                if (col.Name == "Product")
                {
                    salesProductCol = col;
                    break;
                }
                ComUtilities.Release(ref col);
            }
            ComUtilities.Release(ref salesCols);

            dynamic? prodCols = productsTable.ModelTableColumns;
            for (int i = 1; i <= prodCols.Count; i++)
            {
                dynamic? col = prodCols[i];
                if (col.Name == "ProductName")
                {
                    productsNameCol = col;
                    break;
                }
                ComUtilities.Release(ref col);
            }
            ComUtilities.Release(ref prodCols);

            if (salesProductCol != null && productsNameCol != null)
            {
                dynamic? rel = relationships.Add(salesProductCol, productsNameCol);
                _output.WriteLine("Created relationship Sales[Product] -> Products[ProductName]");
                ComUtilities.Release(ref rel);
            }

            ComUtilities.Release(ref salesProductCol);
            ComUtilities.Release(ref productsNameCol);
            ComUtilities.Release(ref salesTable);
            ComUtilities.Release(ref productsTable);
        }
        catch (COMException ex)
        {
            _output.WriteLine($"CreateRelationship failed: {ex.Message}");
        }
        finally
        {
            ComUtilities.Release(ref relationships);
            ComUtilities.Release(ref modelTables);
            ComUtilities.Release(ref model);
        }
    }

    // =========================================================================
    // SCENARIO 13: CUBEVALUE with fresh Excel instance (Hidden vs Visible)
    // Runs CUBEVALUE test with a completely fresh Excel instance
    // =========================================================================

    [Theory]
    [InlineData(false, "Hidden")]
    [InlineData(true, "Visible")]
    public void Scenario13_CubeValueFreshExcelInstance(bool excelVisible, string visibilityLabel)
    {
        _output.WriteLine($"=== SCENARIO 13: CUBEVALUE Fresh Excel Instance ({visibilityLabel}) ===");
        _output.WriteLine($"Excel.Visible = {excelVisible}");
        _output.WriteLine("");

        dynamic? excel = null;
        dynamic? workbook = null;
        dynamic? model = null;
        dynamic? sheet = null;
        dynamic? range = null;
        string testFile = Path.Combine(_tempDir, $"CubeValue_{visibilityLabel}_{Guid.NewGuid():N}.xlsx");

        try
        {
            // Step 1: Create fresh Excel instance
            _output.WriteLine("--- Step 1: Create fresh Excel instance ---");
            var excelType = Type.GetTypeFromProgID("Excel.Application");
            excel = Activator.CreateInstance(excelType!);
            excel.Visible = excelVisible;
            excel.DisplayAlerts = false;
            _output.WriteLine($"Excel instance created. Visible={excel.Visible}");

            if (excelVisible)
            {
                // Give Excel time to fully initialize when visible
                Thread.Sleep(2000);
            }

            // Step 2: Create workbook
            _output.WriteLine("\n--- Step 2: Create workbook ---");
            workbook = excel.Workbooks.Add();
            workbook.SaveAs(testFile);
            _output.WriteLine($"Workbook created: {testFile}");

            // Step 3: Create Power Query and load to Data Model
            _output.WriteLine("\n--- Step 3: Create Power Query with Data Model load ---");
            string queryName = "Sales";
            string mCode = """
                let
                    Source = #table(
                        {"Product", "Amount", "Quantity"},
                        {{"Widget", 100, 5}, {"Gadget", 200, 3}, {"Gizmo", 150, 7}}
                    )
                in
                    Source
                """;

            dynamic? queries = workbook.Queries;
            dynamic? query = queries.Add(queryName, mCode);
            _output.WriteLine($"Query '{queryName}' created");

            // Create connection and load to Data Model
            string connectionString = $"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location={queryName}";
            string commandText = $"SELECT * FROM [{queryName}]";

            dynamic? connections = workbook.Connections;
            dynamic? conn = connections.Add2(
                $"Query - {queryName}",
                $"Power Query - {queryName}",
                connectionString,
                commandText,
                2,    // xlCmdSql
                true, // CreateModelConnection - LOAD TO DATA MODEL
                false // ImportRelationships
            );
            _output.WriteLine("Connection created with CreateModelConnection=true");

            // Refresh to load data
            conn.Refresh();
            _output.WriteLine("Connection refreshed");
            ComUtilities.Release(ref conn);
            ComUtilities.Release(ref connections);
            ComUtilities.Release(ref query);
            ComUtilities.Release(ref queries);

            if (excelVisible)
            {
                Thread.Sleep(1000);
            }

            // Step 4: Create DAX measure
            _output.WriteLine("\n--- Step 4: Create DAX measure ---");
            model = workbook.Model;
            dynamic? modelTables = model.ModelTables;
            _output.WriteLine($"ModelTables.Count: {modelTables.Count}");
            dynamic? targetTable = null;
            string targetTableName = "";

            for (int i = 1; i <= modelTables.Count; i++)
            {
                dynamic? t = modelTables[i];
                string tName = t.Name?.ToString() ?? "(null)";
                _output.WriteLine($"  Table[{i}]: {tName}");
                if (targetTable == null)
                {
                    targetTable = t;
                    targetTableName = tName;
                }
                else
                {
                    ComUtilities.Release(ref t);
                }
            }
            ComUtilities.Release(ref modelTables);

            if (targetTable != null)
            {
                dynamic? measures = model.ModelMeasures;
                dynamic? formatInfo = model.ModelFormatGeneral;  // REQUIRED
                try
                {
                    // Use the actual table name in the DAX formula
                    string daxFormula = $"SUM({targetTableName}[Amount])";
                    dynamic? measure = measures.Add(
                        "TotalAmount",
                        targetTable,
                        daxFormula,
                        formatInfo
                    );
                    _output.WriteLine($"Created measure 'TotalAmount' = {daxFormula}");
                    ComUtilities.Release(ref measure);
                }
                catch (COMException ex)
                {
                    _output.WriteLine($"Failed to create measure: 0x{ex.HResult:X} - {ex.Message}");
                }
                finally
                {
                    ComUtilities.Release(ref formatInfo);
                    ComUtilities.Release(ref measures);
                }
            }
            else
            {
                _output.WriteLine("ERROR: No tables found in model!");
            }
            ComUtilities.Release(ref targetTable);

            // Step 5: Discover Data Model connection name
            _output.WriteLine("\n--- Step 5: Discover Data Model connection name ---");
            dynamic? dataModelConn = null;
            string dataModelConnName = "ThisWorkbookDataModel";
            try
            {
                dataModelConn = model.DataModelConnection;
                dataModelConnName = dataModelConn?.Name?.ToString() ?? "ThisWorkbookDataModel";
                _output.WriteLine($"Model.DataModelConnection.Name: {dataModelConnName}");
            }
#pragma warning disable CA1031 // Intentional: diagnostic test logs all exceptions
            catch (Exception ex)
#pragma warning restore CA1031
            {
                _output.WriteLine($"Could not get DataModelConnection: {ex.Message}");
            }
            finally
            {
                ComUtilities.Release(ref dataModelConn);
            }

            // Also list all connections
            _output.WriteLine("All connections in workbook:");
            dynamic? conns = workbook.Connections;
            for (int i = 1; i <= conns.Count; i++)
            {
                dynamic? c = conns[i];
                string cName = c?.Name?.ToString() ?? "(null)";
                int cType = Convert.ToInt32(c?.Type ?? 0);
                bool inModel = false;
                try { inModel = c.InModel; } catch (COMException) { /* InModel property may not exist on all connection types */ }
                _output.WriteLine($"  [{i}] Name: '{cName}', Type: {cType}, InModel: {inModel}");
                ComUtilities.Release(ref c);
            }
            ComUtilities.Release(ref conns);

            // Step 5b: Try to create a proper Model Workbook Connection (per MS docs)
            _output.WriteLine("\n--- Step 5b: Try Model.CreateModelWorkbookConnection ---");
            dynamic? modelWorkbookConn = null;
            try
            {
                // Get the first table from the model to use as parameter
                dynamic? modelTables2 = model.ModelTables;
                dynamic? firstTable = modelTables2[1];
                modelWorkbookConn = model.CreateModelWorkbookConnection(firstTable);
                string connName = modelWorkbookConn?.Name?.ToString() ?? "(null)";
                _output.WriteLine($"Created Model Workbook Connection: '{connName}'");
                ComUtilities.Release(ref firstTable);
                ComUtilities.Release(ref modelTables2);
            }
            catch (COMException ex)
            {
                _output.WriteLine($"CreateModelWorkbookConnection failed: 0x{ex.HResult:X8} - {ex.Message}");
            }
            finally
            {
                ComUtilities.Release(ref modelWorkbookConn);
            }

            // List connections again after attempting to create model connection
            _output.WriteLine("Connections after CreateModelWorkbookConnection:");
            conns = workbook.Connections;
            for (int i = 1; i <= conns.Count; i++)
            {
                dynamic? c = conns[i];
                string cName = c?.Name?.ToString() ?? "(null)";
                int cType = Convert.ToInt32(c?.Type ?? 0);
                _output.WriteLine($"  [{i}] Name: '{cName}', Type: {cType}");
                ComUtilities.Release(ref c);
            }
            ComUtilities.Release(ref conns);

            // Step 6: Add CUBEVALUE formula
            _output.WriteLine("\n--- Step 6: Add CUBEVALUE formulas (testing different syntax) ---");
            sheet = workbook.Worksheets[1];

            // Build formulas with discovered names
            // Important: DAX measures are referenced as [Measures].[MeasureName]
            // According to MS docs, the special Data Model connection is named "Workbook Data Model" (with spaces)
            string[] formulas = new[]
            {
                $"=CUBEVALUE(\"{dataModelConnName}\",\"[Measures].[TotalAmount]\")",
                $"=CUBEVALUE(\"{dataModelConnName}\",\"{targetTableName}[TotalAmount]\")",
                "=CUBEVALUE(\"Workbook Data Model\",\"[Measures].[TotalAmount]\")",
                "=CUBEVALUE(\"ThisWorkbookDataModel\",\"[Measures].[TotalAmount]\")",
                "=CUBEVALUE(\"Query - Sales\",\"[Measures].[TotalAmount]\")",
                $"=CUBEVALUE(\"{dataModelConnName}\",\"[{targetTableName}].[Measures].[TotalAmount]\")",
            };

            string[] formulaDescriptions = new[]
            {
                $"DataModelConnection.Name ({dataModelConnName})",
                "TableName[MeasureName] pattern",
                "Workbook Data Model (MS docs name)",
                "ThisWorkbookDataModel (hardcoded)",
                "Query - Sales connection",
                "[TableName].[Measures].[MeasureName] pattern",
            };

            for (int i = 0; i < formulas.Length; i++)
            {
                string cell = $"A{i + 1}";
                range = sheet.Range[cell];
                range.Formula = formulas[i];
                _output.WriteLine($"{cell}: {formulaDescriptions[i]}");
                _output.WriteLine($"     Formula: {formulas[i]}");
                ComUtilities.Release(ref range);
            }

            // Also test CUBEMEMBER to see if that works
            _output.WriteLine("\n--- Also testing CUBEMEMBER function ---");
            dynamic? memberRange = sheet.Range["B1"];
            string cubeMemberFormula = $"=CUBEMEMBER(\"{dataModelConnName}\",\"[Measures].[TotalAmount]\")";
            memberRange.Formula = cubeMemberFormula;
            _output.WriteLine($"B1: CUBEMEMBER formula: {cubeMemberFormula}");
            ComUtilities.Release(ref memberRange);

            range = sheet.Range["A1"];  // Reset for value reading

            // Step 6b: Read values immediately
            _output.WriteLine("\n--- Step 6b: Read values immediately ---");
            for (int i = 0; i < formulas.Length; i++)
            {
                string cell = $"A{i + 1}";
                dynamic? r = sheet.Range[cell];
                object val = r.Value2;
                _output.WriteLine($"{cell}: {FormatValue(val)} - {formulaDescriptions[i]}");
                ComUtilities.Release(ref r);
            }

            // Check CUBEMEMBER
            dynamic? memberRangeCheck = sheet.Range["B1"];
            object memberVal = memberRangeCheck.Value2;
            _output.WriteLine($"B1 (CUBEMEMBER): {FormatValue(memberVal)}");
            ComUtilities.Release(ref memberRangeCheck);

            // Step 7: Refresh Data Model
            _output.WriteLine("\n--- Step 7: Refresh Data Model ---");
            try
            {
                model.Refresh();
                _output.WriteLine("model.Refresh() succeeded");
            }
            catch (COMException ex)
            {
                _output.WriteLine($"model.Refresh() failed: 0x{ex.HResult:X8}");
            }

            if (excelVisible)
            {
                Thread.Sleep(1000);
            }

            _output.WriteLine("Values after Data Model refresh:");
            for (int i = 0; i < formulas.Length; i++)
            {
                string cell = $"A{i + 1}";
                dynamic? r = sheet.Range[cell];
                object val = r.Value2;
                _output.WriteLine($"  {cell}: {FormatValue(val)}");
                ComUtilities.Release(ref r);
            }

            // Step 8: Calculate
            _output.WriteLine("\n--- Step 8: Application.Calculate() ---");
            try
            {
                excel.Calculate();
                _output.WriteLine("Calculate() succeeded");
            }
            catch (COMException ex)
            {
                _output.WriteLine($"Calculate() failed: 0x{ex.HResult:X8} - {ex.Message}");
            }

            if (excelVisible)
            {
                Thread.Sleep(500);
            }

            _output.WriteLine("Values after Calculate:");
            for (int i = 0; i < formulas.Length; i++)
            {
                string cell = $"A{i + 1}";
                dynamic? r = sheet.Range[cell];
                object val = r.Value2;
                _output.WriteLine($"  {cell}: {FormatValue(val)}");
                ComUtilities.Release(ref r);
            }

            // Step 9: CalculateFull
            _output.WriteLine("\n--- Step 9: Application.CalculateFull() ---");
            try
            {
                excel.CalculateFull();
                _output.WriteLine("CalculateFull() succeeded");
            }
            catch (COMException ex)
            {
                _output.WriteLine($"CalculateFull() failed: 0x{ex.HResult:X8} - {ex.Message}");
            }

            if (excelVisible)
            {
                Thread.Sleep(500);
            }

            _output.WriteLine("Values after CalculateFull:");
            for (int i = 0; i < formulas.Length; i++)
            {
                string cell = $"A{i + 1}";
                dynamic? r = sheet.Range[cell];
                object val = r.Value2;
                _output.WriteLine($"  {cell}: {FormatValue(val)}");
                ComUtilities.Release(ref r);
            }

            // Step 10: CalculateFullRebuild
            _output.WriteLine("\n--- Step 10: Application.CalculateFullRebuild() ---");
            try
            {
                excel.CalculateFullRebuild();
                _output.WriteLine("CalculateFullRebuild() succeeded");
            }
            catch (COMException ex)
            {
                _output.WriteLine($"CalculateFullRebuild() failed: 0x{ex.HResult:X8} - {ex.Message}");
            }

            if (excelVisible)
            {
                Thread.Sleep(500);
            }

            _output.WriteLine("Values after CalculateFullRebuild:");
            object? bestValue = null;
            for (int i = 0; i < formulas.Length; i++)
            {
                string cell = $"A{i + 1}";
                dynamic? r = sheet.Range[cell];
                object val = r.Value2;
                _output.WriteLine($"  {cell}: {FormatValue(val)} - {formulaDescriptions[i]}");
                if (val is int or double && bestValue == null)
                {
                    bestValue = val;
                }
                ComUtilities.Release(ref r);
            }

            // Also check CUBEMEMBER
            dynamic? memberRangeFinal = sheet.Range["B1"];
            object memberValFinal = memberRangeFinal.Value2;
            _output.WriteLine($"  B1 (CUBEMEMBER): {FormatValue(memberValFinal)}");
            ComUtilities.Release(ref memberRangeFinal);

            // Summary
            _output.WriteLine("\n=== SUMMARY ===");
            _output.WriteLine($"Excel.Visible: {excelVisible}");
            _output.WriteLine($"Data Model Connection: {dataModelConnName}");
            _output.WriteLine($"Target Table: {targetTableName}");
            _output.WriteLine($"Best numeric result: {(bestValue != null ? bestValue.ToString() : "None - all formulas returned errors")}");

            // Determine success
            bool success = bestValue != null;
            _output.WriteLine(success ? "\n✅ At least one CUBEVALUE formula returned a numeric value" : "\n❌ ALL CUBEVALUE formulas still show errors");
        }
        catch (COMException ex)
        {
            _output.WriteLine($"\n❌ Test failed with COM exception: 0x{ex.HResult:X8}");
            _output.WriteLine($"Message: {ex.Message}");
        }
        finally
        {
            _output.WriteLine("\n--- Cleanup ---");
            ComUtilities.Release(ref range);
            ComUtilities.Release(ref sheet);
            ComUtilities.Release(ref model);

            if (workbook != null)
            {
                try
                {
                    workbook.Close(false);
                }
#pragma warning disable CA1031 // Intentional: cleanup code must not throw
                catch (Exception) { /* Ignore cleanup errors */ }
#pragma warning restore CA1031
                ComUtilities.Release(ref workbook);
            }

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

            // 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
        }

        _output.WriteLine($"=== SCENARIO 13 ({visibilityLabel}) COMPLETE ===\n");
    }
}




