using Sbroenne.ExcelMcp.ComInterop;
using Sbroenne.ExcelMcp.ComInterop.Session;
using Sbroenne.ExcelMcp.Core.Models;

namespace Sbroenne.ExcelMcp.Core.Commands.PivotTable;

/// <summary>
/// PivotTable field management operations
/// </summary>
public partial class PivotTableCommands
{
    /// <summary>
    /// Lists all fields in a PivotTable
    /// </summary>
    public PivotFieldListResult ListFields(IExcelBatch batch, string pivotTableName)
    {
        return batch.Execute((ctx, ct) =>
        {
            dynamic? pivot = null;
            dynamic? pivotFields = null;
            dynamic? cubeFields = null;

            pivot = FindPivotTable(ctx.Book, pivotTableName);

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

            try
            {
                // For OLAP PivotTables, use CubeFields instead of PivotFields
                if (isOlap)
                {
                    return ListCubeFieldsAsync(cubeFields, batch.WorkbookPath);
                }
                else
                {
                    // Regular PivotTable - use PivotFields
                    pivotFields = pivot.PivotFields;
                    return ListRegularFieldsAsync(pivotFields, batch.WorkbookPath);
                }
            }
            finally
            {
                ComUtilities.Release(ref cubeFields);
                ComUtilities.Release(ref pivotFields);
                ComUtilities.Release(ref pivot);
            }
        });
    }

    /// <summary>
    /// Lists fields from OLAP/Data Model PivotTable using CubeFields
    /// </summary>
    private static PivotFieldListResult ListCubeFieldsAsync(dynamic cubeFields, string filePath)
    {
        var fields = new List<PivotFieldInfo>();

        int fieldCount = cubeFields.Count;

        for (int i = 1; i <= fieldCount; i++)
        {
            dynamic? cubeField = null;
            try
            {
                cubeField = cubeFields.Item(i);

                // Get field name
                string fieldName;
                try
                {
                    fieldName = cubeField.Name?.ToString() ?? $"CubeField{i}";
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    fieldName = $"CubeField{i}";
                }

                // Get orientation - for CubeFields, check if it has a corresponding PivotField
                int orientation = XlPivotFieldOrientation.xlHidden;
                try
                {
                    dynamic? pivotField = cubeField.PivotFields?.Item(1);
                    if (pivotField != null)
                    {
                        orientation = Convert.ToInt32(pivotField.Orientation);
                        ComUtilities.Release(ref pivotField);
                    }
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    orientation = XlPivotFieldOrientation.xlHidden;
                }

                var fieldInfo = new PivotFieldInfo
                {
                    Name = fieldName,
                    Area = orientation switch
                    {
                        XlPivotFieldOrientation.xlRowField => PivotFieldArea.Row,
                        XlPivotFieldOrientation.xlColumnField => PivotFieldArea.Column,
                        XlPivotFieldOrientation.xlPageField => PivotFieldArea.Filter,
                        XlPivotFieldOrientation.xlDataField => PivotFieldArea.Value,
                        _ => PivotFieldArea.Hidden
                    },
                    CustomName = string.Empty,
                    Position = 0,
                    DataType = "Cube"
                };

                fields.Add(fieldInfo);
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                // Skip field if COM access fails - continue with other fields
            }
            finally
            {
                ComUtilities.Release(ref cubeField);
            }
        }

        return new PivotFieldListResult
        {
            Success = true,
            Fields = fields,
            FilePath = filePath
        };
    }

    /// <summary>
    /// Lists fields from regular PivotTable using PivotFields
    /// </summary>
    private static PivotFieldListResult ListRegularFieldsAsync(dynamic pivotFields, string filePath)
    {
        var fields = new List<PivotFieldInfo>();

        int fieldCount = pivotFields.Count;

        for (int i = 1; i <= fieldCount; i++)
        {
            dynamic? field = null;
            try
            {
                field = pivotFields.Item(i);

                // Get field name with defensive handling (can throw on Data Model fields)
                string fieldName;
                try
                {
                    fieldName = field.SourceName?.ToString() ?? field.Name?.ToString() ?? $"Field{i}";
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    fieldName = $"Field{i}";
                }

                // Get orientation with defensive handling
                int orientation;
                try
                {
                    orientation = Convert.ToInt32(field.Orientation);
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    orientation = XlPivotFieldOrientation.xlHidden;
                }

                var fieldInfo = new PivotFieldInfo
                {
                    Name = fieldName,
                    Area = orientation switch
                    {
                        XlPivotFieldOrientation.xlRowField => PivotFieldArea.Row,
                        XlPivotFieldOrientation.xlColumnField => PivotFieldArea.Column,
                        XlPivotFieldOrientation.xlPageField => PivotFieldArea.Filter,
                        XlPivotFieldOrientation.xlDataField => PivotFieldArea.Value,
                        _ => PivotFieldArea.Hidden
                    }
                };

                // CustomName - defensive
                try
                {
                    fieldInfo.CustomName = field.Caption?.ToString() ?? string.Empty;
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    fieldInfo.CustomName = string.Empty;
                }

                // Position - defensive
                try
                {
                    fieldInfo.Position = orientation != XlPivotFieldOrientation.xlHidden ? Convert.ToInt32(field.Position) : 0;
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    fieldInfo.Position = 0;
                }

                // DataType - defensive
                try
                {
                    fieldInfo.DataType = PivotTableHelpers.DetectFieldDataType(field);
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    fieldInfo.DataType = "Unknown";
                }

                // Get function for value fields - defensive
                if (orientation == XlPivotFieldOrientation.xlDataField)
                {
                    try
                    {
                        int comFunction = Convert.ToInt32(field.Function);
                        fieldInfo.Function = PivotTableHelpers.GetAggregationFunctionFromCom(comFunction);
                    }
                    catch (System.Runtime.InteropServices.COMException)
                    {
                        fieldInfo.Function = AggregationFunction.Sum; // Default
                    }
                }

                fields.Add(fieldInfo);
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                // Skip field if COM access fails - continue with other fields
            }
            finally
            {
                ComUtilities.Release(ref field);
            }
        }

        return new PivotFieldListResult
        {
            Success = true,
            Fields = fields,
            FilePath = filePath
        };
    }

    /// <summary>
    /// Adds a field to the Row area
    /// </summary>
    public PivotFieldResult AddRowField(IExcelBatch batch, string pivotTableName,
        string fieldName, int? position = null)
        => ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
            (strategy, pivot) => strategy.AddRowField(pivot, fieldName, position, batch.WorkbookPath));

    /// <summary>
    /// Adds a field to the Column area
    /// </summary>
    public PivotFieldResult AddColumnField(IExcelBatch batch, string pivotTableName,
        string fieldName, int? position = null)
        => ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
            (strategy, pivot) => strategy.AddColumnField(pivot, fieldName, position, batch.WorkbookPath));

    /// <summary>
    /// Adds a field to the Values area with aggregation
    /// </summary>
    public PivotFieldResult AddValueField(IExcelBatch batch, string pivotTableName,
        string fieldName, AggregationFunction aggregationFunction = AggregationFunction.Sum,
        string? customName = null)
        => ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
            (strategy, pivot) => strategy.AddValueField(pivot, fieldName, aggregationFunction, customName, batch.WorkbookPath));

    /// <summary>
    /// Adds a field to the Filter area
    /// </summary>
    public PivotFieldResult AddFilterField(IExcelBatch batch, string pivotTableName,
        string fieldName)
        => ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
            (strategy, pivot) => strategy.AddFilterField(pivot, fieldName, batch.WorkbookPath));

    /// <summary>
    /// Removes a field from any area
    /// </summary>
    public PivotFieldResult RemoveField(IExcelBatch batch, string pivotTableName,
        string fieldName)
        => ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
            (strategy, pivot) => strategy.RemoveField(pivot, fieldName, batch.WorkbookPath));

    /// <summary>
    /// Sets the aggregation function for a value field
    /// </summary>
    public PivotFieldResult SetFieldFunction(IExcelBatch batch, string pivotTableName,
        string fieldName, AggregationFunction aggregationFunction)
        => ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
            (strategy, pivot) => strategy.SetFieldFunction(pivot, fieldName, aggregationFunction, batch.WorkbookPath));

    /// <summary>
    /// Sets custom name for a field
    /// </summary>
    public PivotFieldResult SetFieldName(IExcelBatch batch, string pivotTableName,
        string fieldName, string customName)
        => ExecuteWithStrategy<PivotFieldResult>(batch, pivotTableName,
            (strategy, pivot) => strategy.SetFieldName(pivot, fieldName, customName, batch.WorkbookPath));

    /// <summary>
    /// Sets number format for a value field
    /// </summary>
    public PivotFieldResult SetFieldFormat(IExcelBatch batch, string pivotTableName,
        string fieldName, string numberFormat)
    {
        return batch.Execute((ctx, ct) =>
        {
            dynamic? pivot = null;

            pivot = FindPivotTable(ctx.Book, pivotTableName);

            try
            {
                // Translate US format codes to locale-specific codes
                var translatedFormat = ctx.FormatTranslator.TranslateToLocale(numberFormat);

                // Use Strategy Pattern to delegate to appropriate implementation
                var strategy = PivotTableFieldStrategyFactory.GetStrategy(pivot);
                return strategy.SetFieldFormat(pivot, fieldName, translatedFormat, batch.WorkbookPath);
            }
            finally
            {
                ComUtilities.Release(ref pivot);
            }
        });
    }
}



