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

namespace Sbroenne.ExcelMcp.Core.Commands.Table;

/// <summary>
/// TableCommands partial class - Structured Reference operations
/// </summary>
public partial class TableCommands
{
    /// <summary>
    /// Gets structured reference information for a table region or column
    /// </summary>
    public TableStructuredReferenceResult GetStructuredReference(
        IExcelBatch batch,
        string tableName,
        TableRegion region,
        string? columnName = null)
    {
        // Security: Validate table name
        ValidateTableName(tableName);

        var result = new TableStructuredReferenceResult { FilePath = batch.WorkbookPath };
        return batch.Execute((ctx, ct) =>
        {
            dynamic? table = null;
            try
            {
                table = FindTable(ctx.Book, tableName);

                result.TableName = tableName;
                result.Region = region;
                result.ColumnName = columnName;

                // Get sheet name
                dynamic? range = null;
                dynamic? sheet = null;
                try
                {
                    range = table.Range;
                    sheet = range.Worksheet;
                    result.SheetName = sheet.Name;
                }
                finally
                {
                    ComUtilities.Release(ref sheet);
                    ComUtilities.Release(ref range);
                }

                // Build structured reference
                result.StructuredReference = BuildStructuredReference(tableName, region, columnName);

                // Get region range
                dynamic? regionRange = null;
                try
                {
                    regionRange = GetRegionRange(table, region, columnName);
                    result.RangeAddress = regionRange.Address;
                    result.RowCount = regionRange.Rows.Count;
                    result.ColumnCount = regionRange.Columns.Count;
                }
                finally
                {
                    ComUtilities.Release(ref regionRange);
                }

                result.Success = true;

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

    /// <summary>
    /// Builds a structured reference formula string for a table region
    /// </summary>
    private static string BuildStructuredReference(string tableName, TableRegion region, string? columnName)
    {
        if (!string.IsNullOrEmpty(columnName))
        {
            // Column-specific reference
            return region switch
            {
                TableRegion.All => $"{tableName}[[#All],[{columnName}]]",
                TableRegion.Data => $"{tableName}[[{columnName}]]", // Default is data
                TableRegion.Headers => $"{tableName}[[#Headers],[{columnName}]]",
                TableRegion.Totals => $"{tableName}[[#Totals],[{columnName}]]",
                TableRegion.ThisRow => $"{tableName}[[@],[{columnName}]]",
                _ => $"{tableName}[[{columnName}]]"
            };
        }
        else
        {
            // Entire region reference
            return region switch
            {
                TableRegion.All => $"{tableName}[#All]",
                TableRegion.Data => $"{tableName}[#Data]",
                TableRegion.Headers => $"{tableName}[#Headers]",
                TableRegion.Totals => $"{tableName}[#Totals]",
                TableRegion.ThisRow => $"{tableName}[@]",
                _ => $"{tableName}[#All]"
            };
        }
    }

    /// <summary>
    /// Gets the Excel Range object for a specific table region
    /// </summary>
    private static dynamic GetRegionRange(dynamic table, TableRegion region, string? columnName)
    {
        dynamic regionRange = region switch
        {
            TableRegion.All => table.Range,
            TableRegion.Data => table.DataBodyRange,
            TableRegion.Headers => table.HeaderRowRange,
            TableRegion.Totals => table.TotalsRowRange,
            TableRegion.ThisRow => table.Range, // Full range for ThisRow context
            _ => table.Range
        };

        // If column specified, get intersection with column
        if (!string.IsNullOrEmpty(columnName))
        {
            dynamic? columns = null;
            dynamic? column = null;
            try
            {
                columns = table.ListColumns;
                column = columns.Item(columnName);
                dynamic columnRange = column.Range;

                // Intersect with region range
                dynamic? app = null;
                dynamic? intersection = null;
                try
                {
                    app = table.Application;
                    intersection = app.Intersect(regionRange, columnRange);
                    return intersection; // Return intersection
                }
                catch (System.Runtime.InteropServices.COMException)
                {
                    // If intersection fails, return column range
                    return columnRange;
                }
                finally
                {
                    ComUtilities.Release(ref app);
                    // Don't release intersection here - caller will do it
                }
            }
            finally
            {
                ComUtilities.Release(ref column);
                ComUtilities.Release(ref columns);
            }
        }

        return regionRange; // Return region range directly
    }
}



