using Sbroenne.ExcelMcp.ComInterop;
using Sbroenne.ExcelMcp.ComInterop.Session;

namespace Sbroenne.ExcelMcp.Core.Commands.PivotTable;

/// <summary>
/// PivotTable management commands - main partial class with shared state and helper methods
/// </summary>
public partial class PivotTableCommands : IPivotTableCommands, IPivotTableFieldCommands, IPivotTableCalcCommands
{
    #region Helper Methods

    /// <summary>
    /// Finds a PivotTable by name in the workbook.
    /// Delegates to CoreLookupHelpers.FindPivotTable for the actual lookup.
    /// </summary>
    /// <param name="workbook">The workbook to search</param>
    /// <param name="pivotTableName">Name of the PivotTable to find</param>
    /// <returns>The PivotTable object if found</returns>
    /// <exception cref="InvalidOperationException">Thrown if PivotTable is not found</exception>
    private static dynamic FindPivotTable(dynamic workbook, string pivotTableName)
        => CoreLookupHelpers.FindPivotTable(workbook, pivotTableName);

    /// <summary>
    /// Executes a strategy-based operation on a PivotTable.
    /// Centralizes the common pattern: find pivot → get strategy → execute → release.
    /// </summary>
    /// <typeparam name="TResult">The result type returned by the strategy operation</typeparam>
    /// <param name="batch">Excel batch session</param>
    /// <param name="pivotTableName">Name of the PivotTable</param>
    /// <param name="operation">The strategy operation to execute (receives strategy and pivot)</param>
    /// <returns>The result from the strategy operation</returns>
    private static TResult ExecuteWithStrategy<TResult>(
        IExcelBatch batch,
        string pivotTableName,
        Func<IPivotTableFieldStrategy, dynamic, TResult> operation)
    {
        return batch.Execute((ctx, ct) =>
        {
            dynamic? pivot = null;
            try
            {
                pivot = FindPivotTable(ctx.Book, pivotTableName);
                var strategy = PivotTableFieldStrategyFactory.GetStrategy(pivot);
                return operation(strategy, pivot);
            }
            finally
            {
                ComUtilities.Release(ref pivot);
            }
        });
    }

    /// <summary>
    /// Gets all field names from a PivotTable
    /// </summary>
    private static List<string> GetFieldNames(dynamic pivotTable)
    {
        var fieldNames = new List<string>();
        dynamic? pivotFields = null;
        try
        {
            pivotFields = pivotTable.PivotFields;
            for (int i = 1; i <= pivotFields.Count; i++)
            {
                dynamic? field = null;
                try
                {
                    field = pivotFields.Item(i);
                    fieldNames.Add(field.SourceName?.ToString() ?? field.Name?.ToString() ?? $"Field{i}");
                }
                finally
                {
                    ComUtilities.Release(ref field);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref pivotFields);
        }
        return fieldNames;
    }

    /// <summary>
    /// Gets a field for manipulation, handling both OLAP and regular PivotTables.
    /// For OLAP PivotTables, accesses via CubeFields and returns the corresponding PivotField.
    /// For regular PivotTables, accesses via PivotFields directly.
    /// </summary>
    /// <param name="pivot">The PivotTable object</param>
    /// <param name="fieldName">Name of the field to retrieve</param>
    /// <param name="isOlap">Output parameter indicating if this is an OLAP PivotTable</param>
    /// <returns>The field object that can be manipulated (PivotField)</returns>
    /// <exception cref="InvalidOperationException">Thrown if field is not found</exception>
    /// <remarks>
    /// Microsoft docs: "In OLAP PivotTables, PivotFields do not exist until the corresponding
    /// CubeField is added to the PivotTable." This method handles both architectures.
    /// </remarks>
    private static dynamic GetFieldForManipulation(dynamic pivot, string fieldName, out bool isOlap)
    {
        isOlap = false;
        dynamic? cubeFields = null;

        try
        {
            // Check if this is an OLAP/Data Model PivotTable
            isOlap = PivotTableHelpers.TryGetCubeFields(pivot, out cubeFields);

            if (isOlap)
            {
                // OLAP PivotTable - access via CubeFields
                // CubeField names are hierarchical like "[TableName].[FieldName]" or "[Measures].[MeasureName]"
                // EXACT MATCH ONLY - no partial matching to avoid disambiguation bugs
                dynamic? cubeField = null;
                try
                {
                    // Exact match only - the LLM knows the exact field names
                    try
                    {
                        cubeField = cubeFields.Item(fieldName);
                    }
                    catch (System.Runtime.InteropServices.COMException)
                    {
                        // Field not found by exact name
                        cubeField = null;
                    }

                    if (cubeField == null)
                    {
                        throw new InvalidOperationException($"CubeField '{fieldName}' not found in Data Model PivotTable. Use the exact CubeField name (e.g., '[Measures].[ACR]' or '[TableName].[ColumnName]').");
                    }

                    // Get or create the PivotField from the CubeField
                    // Per Microsoft docs: CubeField.PivotFields returns collection of PivotFields for this CubeField
                    dynamic? pivotFields = cubeField.PivotFields;
                    if (pivotFields == null || pivotFields.Count == 0)
                    {
                        // No PivotField exists yet - field hasn't been added to PivotTable
                        // Call CreatePivotFields() to create the PivotFields collection
                        // Per Microsoft docs: "In OLAP PivotTables, PivotFields do not exist until
                        // the corresponding CubeField is added to the PivotTable. The CreatePivotFields()
                        // method enables users to create all PivotFields of a CubeField."
                        ComUtilities.Release(ref pivotFields);
                        cubeField.CreatePivotFields(); // Create PivotFields before manipulation

                        // Now get the newly created PivotFields collection
                        pivotFields = cubeField.PivotFields;
                        if (pivotFields == null || pivotFields.Count == 0)
                        {
                            // Still no PivotFields - this shouldn't happen after CreatePivotFields()
                            ComUtilities.Release(ref pivotFields);
                            throw new InvalidOperationException($"Failed to create PivotFields for CubeField '{fieldName}'");
                        }
                    }

                    // Release PivotFields collection - we don't need it
                    ComUtilities.Release(ref pivotFields);

                    // CRITICAL: Return the CubeField, not the PivotField!
                    // For OLAP, we must set Orientation on the CubeField, not on the PivotField.
                    // Microsoft docs: "CubeField.Orientation returns or sets... the location of the field"
                    // Setting PivotField.Orientation fails with "Unable to set the Orientation property"
                    // DON'T release cubeField - caller needs it!
                    return cubeField;
                }
                finally
                {
                    // Only release cubeField if we didn't return it or a child object
                    // Since we return cubeField, we should NOT release it here
                    // if (cubeField != null)
                    //     ComUtilities.Release(ref cubeField);
                }
            }
            else
            {
                // Regular PivotTable - access via PivotFields directly
                dynamic? pivotFields = null;
                try
                {
                    pivotFields = pivot.PivotFields;
                    return pivotFields.Item(fieldName); // COM will throw if not found
                }
                catch (Exception ex)
                {
                    throw new InvalidOperationException($"Field '{fieldName}' not found in PivotTable", ex);
                }
                finally
                {
                    ComUtilities.Release(ref pivotFields);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref cubeFields);
        }
    }

    #endregion
}


