using Sbroenne.ExcelMcp.ComInterop;

namespace Sbroenne.ExcelMcp.Core.Commands;

/// <summary>
/// Shared lookup helpers for finding Excel objects (PivotTables, Tables, etc.) across the workbook.
/// These utilities centralize common lookup patterns to avoid code duplication.
/// </summary>
public static class CoreLookupHelpers
{
    #region PivotTable Lookup

    /// <summary>
    /// Tries to find a PivotTable by name across all worksheets.
    /// </summary>
    /// <param name="workbook">The workbook to search</param>
    /// <param name="pivotTableName">Name of the PivotTable to find</param>
    /// <param name="pivotTable">The found PivotTable object (caller must release), or null if not found</param>
    /// <returns>True if found, false otherwise</returns>
    /// <remarks>
    /// Caller is responsible for releasing the returned COM object using ComUtilities.Release().
    /// </remarks>
    public static bool TryFindPivotTable(dynamic workbook, string pivotTableName, out dynamic? pivotTable)
    {
        pivotTable = null;
        dynamic? sheets = null;

        try
        {
            sheets = workbook.Worksheets;
            int sheetCount = Convert.ToInt32(sheets.Count);

            for (int i = 1; i <= sheetCount; i++)
            {
                dynamic? sheet = null;
                dynamic? pivotTables = null;

                try
                {
                    sheet = sheets.Item(i);
                    pivotTables = sheet.PivotTables();
                    int ptCount = Convert.ToInt32(pivotTables.Count);

                    for (int j = 1; j <= ptCount; j++)
                    {
                        dynamic? pt = null;

                        try
                        {
                            pt = pivotTables.Item(j);
                            string ptName = pt.Name?.ToString() ?? string.Empty;

                            if (ptName.Equals(pivotTableName, StringComparison.OrdinalIgnoreCase))
                            {
                                // Found - release intermediate objects but NOT the found PivotTable
                                ComUtilities.Release(ref pivotTables!);
                                ComUtilities.Release(ref sheet!);
                                ComUtilities.Release(ref sheets!);
                                pivotTable = pt;
                                return true;
                            }
                        }
                        finally
                        {
                            // Only release if not the found item
                            if (pt != null && pivotTable == null)
                            {
                                ComUtilities.Release(ref pt!);
                            }
                        }
                    }
                }
                finally
                {
                    ComUtilities.Release(ref pivotTables);
                    ComUtilities.Release(ref sheet);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref sheets);
        }

        return false;
    }

    /// <summary>
    /// Finds a PivotTable by name across all worksheets, throwing if not found.
    /// </summary>
    /// <param name="workbook">The workbook to search</param>
    /// <param name="pivotTableName">Name of the PivotTable to find</param>
    /// <returns>The PivotTable object (caller must release)</returns>
    /// <exception cref="InvalidOperationException">Thrown if PivotTable is not found</exception>
    /// <remarks>
    /// Caller is responsible for releasing the returned COM object using ComUtilities.Release().
    /// </remarks>
    public static dynamic FindPivotTable(dynamic workbook, string pivotTableName)
    {
        if (!TryFindPivotTable(workbook, pivotTableName, out dynamic? pivotTable) || pivotTable == null)
        {
            throw new InvalidOperationException($"PivotTable '{pivotTableName}' not found.");
        }

        return pivotTable!;
    }

    #endregion

    #region Table Lookup

    /// <summary>
    /// Tries to find an Excel Table (ListObject) by name in the workbook.
    /// </summary>
    /// <param name="workbook">The workbook to search</param>
    /// <param name="tableName">Name of the table to find</param>
    /// <param name="table">The found table object (caller must release), or null if not found</param>
    /// <returns>True if found, false otherwise</returns>
    /// <remarks>
    /// Caller is responsible for releasing the returned COM object using ComUtilities.Release().
    /// </remarks>
    public static bool TryFindTable(dynamic workbook, string tableName, out dynamic? table)
    {
        table = null;
        dynamic? sheets = null;

        try
        {
            sheets = workbook.Worksheets;
            int sheetCount = Convert.ToInt32(sheets.Count);

            for (int i = 1; i <= sheetCount; i++)
            {
                dynamic? sheet = null;
                dynamic? listObjects = null;

                try
                {
                    sheet = sheets.Item(i);
                    listObjects = sheet.ListObjects;
                    int tableCount = Convert.ToInt32(listObjects.Count);

                    for (int j = 1; j <= tableCount; j++)
                    {
                        dynamic? tbl = null;

                        try
                        {
                            tbl = listObjects.Item(j);
                            string tblName = tbl.Name?.ToString() ?? string.Empty;

                            if (tblName.Equals(tableName, StringComparison.OrdinalIgnoreCase))
                            {
                                // Found - release intermediate objects but NOT the found table
                                ComUtilities.Release(ref listObjects!);
                                ComUtilities.Release(ref sheet!);
                                ComUtilities.Release(ref sheets!);
                                table = tbl;
                                return true;
                            }
                        }
                        finally
                        {
                            // Only release if not the found item
                            if (tbl != null && table == null)
                            {
                                ComUtilities.Release(ref tbl!);
                            }
                        }
                    }
                }
                finally
                {
                    ComUtilities.Release(ref listObjects);
                    ComUtilities.Release(ref sheet);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref sheets);
        }

        return false;
    }

    /// <summary>
    /// Finds an Excel Table (ListObject) by name in the workbook, throwing if not found.
    /// </summary>
    /// <param name="workbook">The workbook to search</param>
    /// <param name="tableName">Name of the table to find</param>
    /// <returns>The table object (caller must release)</returns>
    /// <exception cref="InvalidOperationException">Thrown if table is not found</exception>
    /// <remarks>
    /// Caller is responsible for releasing the returned COM object using ComUtilities.Release().
    /// </remarks>
    public static dynamic FindTable(dynamic workbook, string tableName)
    {
        if (!TryFindTable(workbook, tableName, out dynamic? table) || table == null)
        {
            throw new InvalidOperationException($"Table '{tableName}' not found.");
        }

        return table!;
    }

    /// <summary>
    /// Checks if a table with the given name exists in the workbook.
    /// </summary>
    /// <param name="workbook">The workbook to search</param>
    /// <param name="tableName">Name of the table to check</param>
    /// <returns>True if table exists, false otherwise</returns>
    public static bool TableExists(dynamic workbook, string tableName)
    {
        if (TryFindTable(workbook, tableName, out dynamic? table))
        {
            ComUtilities.Release(ref table);
            return true;
        }

        return false;
    }

    #endregion
}


