using Sbroenne.ExcelMcp.ComInterop;
using Sbroenne.ExcelMcp.ComInterop.Session;
using Sbroenne.ExcelMcp.Core.Models;
using Excel = Microsoft.Office.Interop.Excel;


namespace Sbroenne.ExcelMcp.Core.Commands.Range;

/// <summary>
/// Range resolution and helper methods for RangeCommands
/// </summary>
public static class RangeHelpers
{
    /// <summary>
    /// Resolves a range address to a Range COM object.
    /// Supports both regular ranges (Sheet1!A1:D10) and named ranges.
    /// Returns null if resolution fails.
    /// </summary>
    public static dynamic? ResolveRange(dynamic book, string sheetName, string rangeAddress, out string? specificError)
    {
        specificError = null;

        // Named range (empty sheetName)
        if (string.IsNullOrEmpty(sheetName))
        {
            try
            {
                dynamic names = book.Names;
                dynamic name = names.Item(rangeAddress);
                return name.RefersToRange;
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                specificError = $"Named range '{rangeAddress}' not found.";
                return null;
            }
        }

        // Regular range (sheet + address)
        // First check if sheet exists
        Excel.Worksheet? sheet = null;
        try
        {
            sheet = ComUtilities.FindSheet(book, sheetName);
            if (sheet == null)
            {
                specificError = $"Sheet '{sheetName}' not found.";
                return null;
            }

            // Sheet exists, now try to get the range
            try
            {
                return sheet.Range[rangeAddress];
            }
            catch (Exception ex)
            {
                specificError = $"Sheet '{sheetName}' exists, but range '{rangeAddress}' is invalid. " +
                               $"Error: {ex.Message}. " +
                               $"Verify the range address format (e.g., 'A1:E10', 'A1', 'A:A').";
                return null;
            }
        }
        finally
        {
            ComUtilities.Release(ref sheet);
        }
    }

    /// <summary>
    /// Resolves a range address to a Range COM object (backward compatibility).
    /// Supports both regular ranges (Sheet1!A1:D10) and named ranges.
    /// </summary>
    public static dynamic? ResolveRange(dynamic book, string sheetName, string rangeAddress)
    {
        string? ignoredError;
        return ResolveRange(book, sheetName, rangeAddress, out ignoredError);
    }

    /// <summary>
    /// Gets appropriate error message for range resolution failure
    /// </summary>
    public static string GetResolveError(string sheetName, string rangeAddress)
    {
        if (string.IsNullOrEmpty(sheetName))
        {
            return $"Named range '{rangeAddress}' not found.";
        }
        return $"Sheet '{sheetName}' or range '{rangeAddress}' not found.";
    }

    /// <summary>
    /// Converts a value to a proper Excel cell value, handling System.Text.Json.JsonElement.
    /// MCP framework deserializes JSON arrays to JsonElement objects which cannot be marshalled to COM Variant.
    /// This helper detects JsonElement and converts to proper C# types before COM assignment.
    /// </summary>
    /// <param name="value">Value from MCP JSON deserialization or direct C# types</param>
    /// <returns>Proper C# type (string, long, double, bool) for COM marshalling</returns>
    public static object ConvertToCellValue(object? value)
    {
        if (value == null)
            return string.Empty;

        // Handle System.Text.Json.JsonElement (from MCP JSON deserialization)
        if (value is System.Text.Json.JsonElement jsonElement)
        {
            return jsonElement.ValueKind switch
            {
                System.Text.Json.JsonValueKind.String => jsonElement.GetString() ?? string.Empty,
                System.Text.Json.JsonValueKind.Number => jsonElement.TryGetInt64(out var i64) ? i64 : jsonElement.GetDouble(),
                System.Text.Json.JsonValueKind.True => true,
                System.Text.Json.JsonValueKind.False => false,
                System.Text.Json.JsonValueKind.Null => string.Empty,
                _ => jsonElement.ToString() ?? string.Empty
            };
        }

        // Already a proper type (from CLI or tests)
        return value;
    }
}

/// <summary>
/// Internal helper methods for RangeCommands partial class
/// </summary>
public partial class RangeCommands
{
    /// <summary>
    /// Helper for clear operations (resolve range, apply action, release)
    /// </summary>
    private static OperationResult ClearRange(
        IExcelBatch batch,
        string sheetName,
        string rangeAddress,
        string action,
        Action<dynamic> clearAction)
    {
        var result = new OperationResult { FilePath = batch.WorkbookPath, Action = action };

        return batch.Execute((ctx, ct) =>
        {
            dynamic? range = null;
            try
            {
                range = RangeHelpers.ResolveRange(ctx.Book, sheetName, rangeAddress);
                if (range == null)
                {
                    throw new InvalidOperationException(RangeHelpers.GetResolveError(sheetName, rangeAddress));
                }

                clearAction(range);
                result.Success = true;
                return result;
            }
            finally
            {
                ComUtilities.Release(ref range);
            }
        });
    }

    /// <summary>
    /// Helper for copy operations (resolve source + target ranges, apply copy action, release both)
    /// </summary>
    private static OperationResult CopyRange(
        IExcelBatch batch,
        string sourceSheet,
        string sourceRange,
        string targetSheet,
        string targetRange,
        string action,
        Action<dynamic, dynamic> copyAction)
    {
        var result = new OperationResult { FilePath = batch.WorkbookPath, Action = action };

        return batch.Execute((ctx, ct) =>
        {
            dynamic? srcRange = null;
            dynamic? tgtRange = null;
            try
            {
                srcRange = RangeHelpers.ResolveRange(ctx.Book, sourceSheet, sourceRange, out string? srcError);
                if (srcRange == null)
                {
                    throw new InvalidOperationException(srcError ?? RangeHelpers.GetResolveError(sourceSheet, sourceRange));
                }

                tgtRange = RangeHelpers.ResolveRange(ctx.Book, targetSheet, targetRange, out string? tgtError);
                if (tgtRange == null)
                {
                    throw new InvalidOperationException(tgtError ?? RangeHelpers.GetResolveError(targetSheet, targetRange));
                }

                copyAction(srcRange, tgtRange);
                result.Success = true;
                return result;
            }
            finally
            {
                ComUtilities.Release(ref srcRange);
                ComUtilities.Release(ref tgtRange);
            }
        });
    }

    /// <summary>
    /// Helper for insert/delete row/column operations (resolve range, get EntireRow/EntireColumn, apply action, release)
    /// </summary>
    private static OperationResult ModifyRowsOrColumns(
        IExcelBatch batch,
        string sheetName,
        string rangeAddress,
        string action,
        Func<dynamic, dynamic> accessor,
        Action<dynamic> operation)
    {
        var result = new OperationResult { FilePath = batch.WorkbookPath, Action = action };

        return batch.Execute((ctx, ct) =>
        {
            dynamic? range = null;
            dynamic? target = null;
            try
            {
                range = RangeHelpers.ResolveRange(ctx.Book, sheetName, rangeAddress, out string? specificError);
                if (range == null)
                {
                    throw new InvalidOperationException(specificError ?? RangeHelpers.GetResolveError(sheetName, rangeAddress));
                }

                target = accessor(range);
                operation(target);
                result.Success = true;
                return result;
            }
            finally
            {
                ComUtilities.Release(ref target);
                ComUtilities.Release(ref range);
            }
        });
    }
}


