using System.Runtime.InteropServices;

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

namespace Sbroenne.ExcelMcp.Core.Commands.PivotTable;

/// <summary>
/// PivotTable slicer operations (CreateSlicer, ListSlicers, SetSlicerSelection, DeleteSlicer)
/// </summary>
public partial class PivotTableCommands
{
    /// <summary>
    /// Creates a slicer for a PivotTable field
    /// </summary>
    public SlicerResult CreateSlicer(IExcelBatch batch, string pivotTableName,
        string fieldName, string slicerName, string destinationSheet, string position)
    {
        return batch.Execute((ctx, ct) =>
        {
            dynamic? pivot = null;
            dynamic? slicerCaches = null;
            dynamic? slicerCache = null;
            dynamic? slicers = null;
            dynamic? slicer = null;
            dynamic? destSheet = null;
            dynamic? destRange = null;

            try
            {
                pivot = FindPivotTable(ctx.Book, pivotTableName);
                slicerCaches = ctx.Book.SlicerCaches;

                // Check if a SlicerCache already exists for this field+PivotTable
                // If so, we add a new visual Slicer to the existing cache
                slicerCache = FindExistingSlicerCache(slicerCaches, pivot, fieldName);

                if (slicerCache == null)
                {
                    // Create new SlicerCache for this field
                    // SlicerCaches.Add(source, sourceField, name, slicerCacheType)
                    // source = PivotTable object
                    // sourceField = field name string
                    // name = cache name (optional, auto-generated if not provided)

                    // For regular PivotTables, use field name directly
                    // For OLAP, may need the hierarchical name
                    slicerCache = slicerCaches.Add2(pivot, fieldName);
                }

                // Get destination sheet and calculate position from cell reference
                destSheet = ctx.Book.Worksheets[destinationSheet];
                destRange = destSheet.Range[position];

                // Get position in points from the cell reference
                double top = Convert.ToDouble(destRange.Top);
                double left = Convert.ToDouble(destRange.Left);

                // Add visual Slicer to the cache
                // Slicers.Add(SlicerDestination, Level, Name, Caption, Top, Left, Width, Height)
                // For non-OLAP sources, Level should be Type.Missing or omitted
                slicers = slicerCache.Slicers;
                slicer = slicers.Add(destSheet, Type.Missing, slicerName, slicerName, top, left);

                // Build result
                var result = BuildSlicerResult(slicer, slicerCache, fieldName);
                result.Success = true;
                result.WorkflowHint = $"Slicer '{slicerName}' created for field '{fieldName}'. Use SetSlicerSelection to filter data, or connect additional PivotTables to this slicer.";

                return result;
            }
            finally
            {
                ComUtilities.Release(ref destRange);
                ComUtilities.Release(ref destSheet);
                ComUtilities.Release(ref slicer);
                ComUtilities.Release(ref slicers);
                ComUtilities.Release(ref slicerCache);
                ComUtilities.Release(ref slicerCaches);
                ComUtilities.Release(ref pivot);
            }
        });
    }

    /// <summary>
    /// Lists all slicers in the workbook, optionally filtered by PivotTable
    /// </summary>
    public SlicerListResult ListSlicers(IExcelBatch batch, string? pivotTableName = null)
    {
        return batch.Execute((ctx, ct) =>
        {
            var result = new SlicerListResult { Success = true };
            dynamic? slicerCaches = null;
            dynamic? targetPivot = null;

            try
            {
                slicerCaches = ctx.Book.SlicerCaches;

                // If filtering by PivotTable, find it first
                if (!string.IsNullOrEmpty(pivotTableName))
                {
                    targetPivot = FindPivotTable(ctx.Book, pivotTableName);
                }

                for (int cacheIndex = 1; cacheIndex <= slicerCaches.Count; cacheIndex++)
                {
                    dynamic? cache = null;
                    dynamic? slicers = null;

                    try
                    {
                        cache = slicerCaches.Item(cacheIndex);

                        // If filtering by PivotTable, check if this cache is connected
                        if (targetPivot != null && !IsSlicerCacheConnectedToPivot(cache, targetPivot))
                        {
                            continue;
                        }

                        slicers = cache.Slicers;
                        for (int slicerIndex = 1; slicerIndex <= slicers.Count; slicerIndex++)
                        {
                            dynamic? slicer = null;
                            try
                            {
                                slicer = slicers.Item(slicerIndex);
                                var slicerInfo = BuildSlicerInfo(slicer, cache);
                                result.Slicers.Add(slicerInfo);
                            }
                            finally
                            {
                                ComUtilities.Release(ref slicer);
                            }
                        }
                    }
                    finally
                    {
                        ComUtilities.Release(ref slicers);
                        ComUtilities.Release(ref cache);
                    }
                }

                return result;
            }
            finally
            {
                ComUtilities.Release(ref targetPivot);
                ComUtilities.Release(ref slicerCaches);
            }
        });
    }

    /// <summary>
    /// Sets the selection for a slicer
    /// </summary>
    public SlicerResult SetSlicerSelection(IExcelBatch batch, string slicerName,
        List<string> selectedItems, bool clearFirst = true)
    {
        return batch.Execute((ctx, ct) =>
        {
            dynamic? slicerCaches = null;
            dynamic? targetCache = null;
            dynamic? targetSlicer = null;
            dynamic? slicerItems = null;

            try
            {
                slicerCaches = ctx.Book.SlicerCaches;

                // Find the slicer by name
                var searchResult = FindSlicerByName(slicerCaches, slicerName);
                targetCache = searchResult.Cache;
                targetSlicer = searchResult.Slicer;

                if (targetSlicer == null || targetCache == null)
                {
                    return new SlicerResult
                    {
                        Success = false,
                        ErrorMessage = $"Slicer '{slicerName}' not found in workbook"
                    };
                }

                // Get slicer items from the cache
                slicerItems = targetCache.SlicerItems;

                // Build set of items to select for fast lookup
                var itemsToSelect = new HashSet<string>(selectedItems, StringComparer.OrdinalIgnoreCase);

                // If no items specified, select all (clear filter)
                bool selectAll = selectedItems.Count == 0;

                // Iterate through slicer items and set selection
                for (int i = 1; i <= slicerItems.Count; i++)
                {
                    dynamic? item = null;
                    try
                    {
                        item = slicerItems.Item(i);
                        string itemName = item.Name?.ToString() ?? string.Empty;

                        if (selectAll)
                        {
                            item.Selected = true;
                        }
                        else if (clearFirst)
                        {
                            // Clear first mode: select only specified items
                            item.Selected = itemsToSelect.Contains(itemName);
                        }
                        else
                        {
                            // Additive mode: add to existing selection
                            if (itemsToSelect.Contains(itemName))
                            {
                                item.Selected = true;
                            }
                        }
                    }
                    finally
                    {
                        ComUtilities.Release(ref item);
                    }
                }

                // Build result with updated state
                string fieldName = GetSlicerCacheFieldName(targetCache);
                var result = BuildSlicerResult(targetSlicer, targetCache, fieldName);
                result.Success = true;
                result.WorkflowHint = selectAll
                    ? $"Slicer '{slicerName}' filter cleared - all items are now visible."
                    : $"Slicer '{slicerName}' selection updated to {selectedItems.Count} item(s).";

                return result;
            }
            finally
            {
                ComUtilities.Release(ref slicerItems);
                ComUtilities.Release(ref targetSlicer);
                ComUtilities.Release(ref targetCache);
                ComUtilities.Release(ref slicerCaches);
            }
        });
    }

    /// <summary>
    /// Deletes a slicer from the workbook
    /// </summary>
    public OperationResult DeleteSlicer(IExcelBatch batch, string slicerName)
    {
        return batch.Execute((ctx, ct) =>
        {
            dynamic? slicerCaches = null;
            dynamic? targetCache = null;
            dynamic? targetSlicer = null;

            try
            {
                slicerCaches = ctx.Book.SlicerCaches;

                // Find the slicer by name
                var searchResult = FindSlicerByName(slicerCaches, slicerName);
                targetCache = searchResult.Cache;
                targetSlicer = searchResult.Slicer;

                if (targetSlicer == null)
                {
                    return new OperationResult
                    {
                        Success = false,
                        ErrorMessage = $"Slicer '{slicerName}' not found in workbook"
                    };
                }

                // Delete the visual slicer
                targetSlicer.Delete();

                // Note: The SlicerCache will be automatically deleted if this was the last slicer
                // connected to it. Excel handles this automatically.

                return new OperationResult { Success = true };
            }
            finally
            {
                ComUtilities.Release(ref targetSlicer);
                ComUtilities.Release(ref targetCache);
                ComUtilities.Release(ref slicerCaches);
            }
        });
    }

    #region Slicer Helper Methods

    /// <summary>
    /// Result of searching for a slicer by name (avoids dynamic tuple deconstruction)
    /// </summary>
    private readonly struct SlicerSearchResult
    {
        public dynamic? Cache { get; init; }
        public dynamic? Slicer { get; init; }
    }

    /// <summary>
    /// Finds an existing SlicerCache for a field on a specific PivotTable
    /// </summary>
    private static dynamic? FindExistingSlicerCache(dynamic slicerCaches, dynamic pivot, string fieldName)
    {
        for (int i = 1; i <= slicerCaches.Count; i++)
        {
            dynamic? cache = null;
            try
            {
                cache = slicerCaches.Item(i);

                // Check if cache is for the same field
                string cacheFieldName = GetSlicerCacheFieldName(cache);
                if (!string.Equals(cacheFieldName, fieldName, StringComparison.OrdinalIgnoreCase))
                {
                    ComUtilities.Release(ref cache);
                    continue;
                }

                // Check if this cache is connected to our PivotTable
                if (IsSlicerCacheConnectedToPivot(cache, pivot))
                {
                    return cache; // Don't release - returning to caller
                }

                ComUtilities.Release(ref cache);
            }
            catch (COMException)
            {
                // COM property access may fail for certain cache types - continue searching
                ComUtilities.Release(ref cache);
            }
        }

        return null;
    }

    /// <summary>
    /// Gets the source field name from a SlicerCache
    /// </summary>
    private static string GetSlicerCacheFieldName(dynamic cache)
    {
        dynamic? sourceField = null;
        try
        {
            // Try to get SourceName first (OLAP), then fall back to checking PivotField
            try
            {
                string? sourceName = cache.SourceName?.ToString();
                if (!string.IsNullOrEmpty(sourceName))
                    return sourceName;
            }
            catch (COMException)
            {
                // SourceName property not available for this cache type - fall back to Name
            }

            // For regular slicers, get from PivotTables collection
            dynamic? pivotTables = null;
            try
            {
                pivotTables = cache.PivotTables;
                if (pivotTables != null && pivotTables.Count > 0)
                {
                    dynamic? pt = null;
                    try
                    {
                        pt = pivotTables.Item(1);
                        // The cache Name often contains the field name
                        string cacheName = cache.Name?.ToString() ?? string.Empty;
                        // SlicerCache names are typically "Slicer_FieldName" format
                        if (cacheName.StartsWith("Slicer_", StringComparison.OrdinalIgnoreCase))
                        {
                            return cacheName[7..]; // Remove "Slicer_" prefix
                        }
                        return cacheName;
                    }
                    finally
                    {
                        ComUtilities.Release(ref pt);
                    }
                }
            }
            finally
            {
                ComUtilities.Release(ref pivotTables);
            }

            return cache.Name?.ToString() ?? "Unknown";
        }
        finally
        {
            ComUtilities.Release(ref sourceField);
        }
    }

    /// <summary>
    /// Checks if a SlicerCache is connected to a specific PivotTable.
    /// Returns false for Table slicers (cache.List == true) since they don't connect to PivotTables.
    /// </summary>
    private static bool IsSlicerCacheConnectedToPivot(dynamic cache, dynamic targetPivot)
    {
        // Per MS docs: List property is true for Table slicers, false for PivotTable slicers
        // https://learn.microsoft.com/en-us/office/vba/api/excel.slicercache.list
        // Table slicers don't connect to PivotTables
        if (cache.List == true)
        {
            return false;
        }

        dynamic? pivotTables = null;
        try
        {
            pivotTables = cache.PivotTables;
            string targetName = targetPivot.Name?.ToString() ?? string.Empty;

            for (int i = 1; i <= pivotTables.Count; i++)
            {
                dynamic? pt = null;
                try
                {
                    pt = pivotTables.Item(i);
                    string ptName = pt.Name?.ToString() ?? string.Empty;
                    if (string.Equals(ptName, targetName, StringComparison.OrdinalIgnoreCase))
                    {
                        return true;
                    }
                }
                finally
                {
                    ComUtilities.Release(ref pt);
                }
            }
            return false;
        }
        finally
        {
            ComUtilities.Release(ref pivotTables);
        }
    }

    /// <summary>
    /// Finds a slicer by name across all SlicerCaches
    /// </summary>
    private static SlicerSearchResult FindSlicerByName(dynamic slicerCaches, string slicerName)
    {
        for (int cacheIndex = 1; cacheIndex <= slicerCaches.Count; cacheIndex++)
        {
            dynamic? cache = null;
            dynamic? slicers = null;

            try
            {
                cache = slicerCaches.Item(cacheIndex);
                slicers = cache.Slicers;

                for (int slicerIndex = 1; slicerIndex <= slicers.Count; slicerIndex++)
                {
                    dynamic? slicer = null;
                    try
                    {
                        slicer = slicers.Item(slicerIndex);
                        string name = slicer.Name?.ToString() ?? string.Empty;

                        if (string.Equals(name, slicerName, StringComparison.OrdinalIgnoreCase))
                        {
                            // Found it - return both cache and slicer (don't release)
                            ComUtilities.Release(ref slicers);
                            return new SlicerSearchResult { Cache = cache, Slicer = slicer };
                        }
                        ComUtilities.Release(ref slicer);
                    }
                    catch (COMException)
                    {
                        // COM access may fail for certain slicer types - continue searching
                        ComUtilities.Release(ref slicer);
                    }
                }

                ComUtilities.Release(ref slicers);
                ComUtilities.Release(ref cache);
            }
            catch (COMException)
            {
                // COM access may fail for certain cache types - continue searching
                ComUtilities.Release(ref slicers);
                ComUtilities.Release(ref cache);
            }
        }

        return new SlicerSearchResult { Cache = null, Slicer = null };
    }

    /// <summary>
    /// Builds a SlicerInfo from COM objects
    /// </summary>
    private static SlicerInfo BuildSlicerInfo(dynamic slicer, dynamic cache)
    {
        var info = new SlicerInfo
        {
            Name = slicer.Name?.ToString() ?? string.Empty,
            Caption = slicer.Caption?.ToString() ?? string.Empty,
            FieldName = GetSlicerCacheFieldName(cache),
            ColumnCount = Convert.ToInt32(slicer.NumberOfColumns)
        };

        // Get sheet name and position
        dynamic? parent = null;
        try
        {
            parent = slicer.Parent;
            info.SheetName = parent.Name?.ToString() ?? string.Empty;
        }
        finally
        {
            ComUtilities.Release(ref parent);
        }

        // Get position (top-left cell) - per Microsoft docs, TopLeftCell is on Shape object
        // https://learn.microsoft.com/en-us/office/vba/api/excel.shape.topleftcell
        dynamic? shape = null;
        dynamic? topLeftCell = null;
        try
        {
            shape = slicer.Shape;
            topLeftCell = shape.TopLeftCell;
            info.Position = topLeftCell?.Address?.ToString()?.Replace("$", "") ?? string.Empty;
        }
        finally
        {
            ComUtilities.Release(ref topLeftCell);
            ComUtilities.Release(ref shape);
        }

        // Get selected and available items from cache
        var items = GetSlicerItems(cache);
        info.SelectedItems = items.Selected;
        info.AvailableItems = items.Available;

        // Get connected PivotTables
        info.ConnectedPivotTables = GetConnectedPivotTableNames(cache);

        return info;
    }

    /// <summary>
    /// Builds a SlicerResult from COM objects
    /// </summary>
    private static SlicerResult BuildSlicerResult(dynamic slicer, dynamic cache, string fieldName)
    {
        var result = new SlicerResult
        {
            Name = slicer.Name?.ToString() ?? string.Empty,
            Caption = slicer.Caption?.ToString() ?? string.Empty,
            FieldName = fieldName
        };

        // Get sheet name and position
        dynamic? parent = null;
        try
        {
            parent = slicer.Parent;
            result.SheetName = parent.Name?.ToString() ?? string.Empty;
        }
        finally
        {
            ComUtilities.Release(ref parent);
        }

        // Get position - per Microsoft docs, TopLeftCell is on Shape object
        // https://learn.microsoft.com/en-us/office/vba/api/excel.shape.topleftcell
        dynamic? shape = null;
        dynamic? topLeftCell = null;
        try
        {
            shape = slicer.Shape;
            topLeftCell = shape.TopLeftCell;
            result.Position = topLeftCell?.Address?.ToString()?.Replace("$", "") ?? string.Empty;
        }
        finally
        {
            ComUtilities.Release(ref topLeftCell);
            ComUtilities.Release(ref shape);
        }

        // Get items
        var items = GetSlicerItems(cache);
        result.SelectedItems = items.Selected;
        result.AvailableItems = items.Available;

        // Get connected PivotTables
        result.ConnectedPivotTables = GetConnectedPivotTableNames(cache);

        return result;
    }

    /// <summary>
    /// Result of getting slicer items (avoids dynamic tuple deconstruction)
    /// </summary>
    private readonly struct SlicerItemsResult
    {
        public List<string> Selected { get; init; }
        public List<string> Available { get; init; }
    }

    /// <summary>
    /// Gets selected and available items from a SlicerCache
    /// </summary>
    private static SlicerItemsResult GetSlicerItems(dynamic cache)
    {
        var selected = new List<string>();
        var available = new List<string>();
        dynamic? slicerItems = null;

        try
        {
            slicerItems = cache.SlicerItems;

            for (int i = 1; i <= slicerItems.Count; i++)
            {
                dynamic? item = null;
                try
                {
                    item = slicerItems.Item(i);
                    string itemName = item.Name?.ToString() ?? string.Empty;

                    if (!string.IsNullOrEmpty(itemName))
                    {
                        available.Add(itemName);
                        if (item.Selected)
                        {
                            selected.Add(itemName);
                        }
                    }
                }
                finally
                {
                    ComUtilities.Release(ref item);
                }
            }
        }
        catch (COMException)
        {
            // SlicerItems collection may not be accessible for certain cache types
        }
        finally
        {
            ComUtilities.Release(ref slicerItems);
        }

        return new SlicerItemsResult { Selected = selected, Available = available };
    }

    /// <summary>
    /// Gets names of PivotTables connected to a SlicerCache.
    /// Returns empty list for Table slicers (cache.List == true).
    /// </summary>
    private static List<string> GetConnectedPivotTableNames(dynamic cache)
    {
        var names = new List<string>();

        // Per MS docs: List property is true for Table slicers
        // Table slicers don't have PivotTables collection
        if (cache.List == true)
        {
            return names;
        }

        dynamic? pivotTables = null;
        try
        {
            pivotTables = cache.PivotTables;

            for (int i = 1; i <= pivotTables.Count; i++)
            {
                dynamic? pt = null;
                try
                {
                    pt = pivotTables.Item(i);
                    string name = pt.Name?.ToString() ?? string.Empty;
                    if (!string.IsNullOrEmpty(name))
                    {
                        names.Add(name);
                    }
                }
                finally
                {
                    ComUtilities.Release(ref pt);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref pivotTables);
        }

        return names;
    }

    #endregion
}


