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

namespace Sbroenne.ExcelMcp.Core.Commands;

/// <summary>
/// Power Query Create operation
/// </summary>
public partial class PowerQueryCommands
{
    /// <summary>
    /// Creates new Power Query from M code with specified load destination.
    /// M code is preserved exactly by default. Remote formatting is only used when explicitly requested.
    /// </summary>
    /// <param name="batch">Excel batch session</param>
    /// <param name="queryName">Name for the new query</param>
    /// <param name="mCode">Power Query M code</param>
    /// <param name="loadMode">Where to load the data (default: LoadToTable)</param>
    /// <param name="targetSheet">Target worksheet name (defaults to queryName if not specified)</param>
    /// <param name="targetCellAddress">Target cell address (e.g., "A1", "B5")</param>
    /// <exception cref="ArgumentException">Thrown when inputs are invalid</exception>
    /// <exception cref="InvalidOperationException">Thrown when query already exists or creation fails</exception>
    public OperationResult Create(
        IExcelBatch batch,
        string queryName,
        string mCode,
        PowerQueryLoadMode loadMode = PowerQueryLoadMode.LoadToTable,
        string? targetSheet = null,
        string? targetCellAddress = null,
        bool formatMCode = false)
    {
        // Validate inputs
        if (string.IsNullOrWhiteSpace(queryName))
        {
            throw new ArgumentException("Query name cannot be empty", nameof(queryName));
        }

        if (string.IsNullOrWhiteSpace(mCode))
        {
            throw new ArgumentException("M code cannot be empty", nameof(mCode));
        }

        string mCodeToSave = formatMCode
            ? MCodeFormatter.FormatAsync(mCode).GetAwaiter().GetResult()
            : mCode;

        // Resolve target sheet name (default to query name)
        if (loadMode == PowerQueryLoadMode.LoadToTable || loadMode == PowerQueryLoadMode.LoadToBoth)
        {
            targetSheet ??= queryName;
        }

        // Resolve target cell address (default to A1)
        targetCellAddress ??= "A1";

        return batch.Execute((ctx, ct) =>
        {
            Excel.Queries? queries = null;
            Excel.WorkbookQuery? query = null;

            try
            {
                queries = ctx.Book.Queries;

                // Check if query already exists
                Excel.WorkbookQuery? existingQuery = FindQueryByName(queries, queryName);
                if (existingQuery != null)
                {
                    ComUtilities.Release(ref existingQuery);
                    throw new InvalidOperationException($"Query '{queryName}' already exists");
                }

                // Step 1: Create the query (always creates in ConnectionOnly mode initially)
                query = queries.Add(queryName, mCodeToSave);

                // Step 2: Apply load destination based on mode
                var result = new PowerQueryCreateResult
                {
                    FilePath = batch.WorkbookPath,
                    QueryName = queryName,
                    LoadDestination = loadMode,
                    WorksheetName = targetSheet,
                    TargetCellAddress = targetCellAddress,
                    QueryCreated = true
                };

                switch (loadMode)
                {
                    case PowerQueryLoadMode.ConnectionOnly:
                        // Query created, no data loading needed
                        result.DataLoaded = false;
                        result.RowsLoaded = 0;
                        result.TargetCellAddress = null;
                        result.Success = true;
                        break;

                    case PowerQueryLoadMode.LoadToTable:
                        LoadQueryToWorksheet(ctx.Book, queryName, targetSheet!, targetCellAddress!, result, ct);
                        break;

                    case PowerQueryLoadMode.LoadToDataModel:
                        LoadQueryToDataModel(ctx.Book, queryName, result, cancellationToken: ct);
                        break;

                    case PowerQueryLoadMode.LoadToBoth:
                        // For LoadToBoth, create TWO separate properly-named connections:
                        // 1. Worksheet connection: "Query - {name}" (created by LoadQueryToWorksheet)
                        // 2. Data Model connection: "Query - {name} (Data Model)" (with suffix to avoid conflict)
                        LoadQueryToWorksheet(ctx.Book, queryName, targetSheet!, targetCellAddress!, result, ct);
                        LoadQueryToDataModel(ctx.Book, queryName, result, " (Data Model)", ct);
                        break;
                }

                return new OperationResult { Success = true, FilePath = batch.WorkbookPath };
            }
            finally
            {
                ComUtilities.Release(ref query);
                ComUtilities.Release(ref queries);
            }
        }, cancellationToken: default);
    }

    /// <summary>
    /// Finds a query by name in the queries collection.
    /// Returns null if not found.
    /// </summary>
    private static Excel.WorkbookQuery? FindQueryByName(Excel.Queries queriesCollection, string queryName)
    {
        try
        {
            int count = queriesCollection.Count;
            for (int i = 1; i <= count; i++)
            {
                Excel.WorkbookQuery? query = null;
                try
                {
                    query = queriesCollection.Item(i);
                    string name = query.Name ?? "";

                    if (name.Equals(queryName, StringComparison.OrdinalIgnoreCase))
                    {
                        return query; // Caller must release
                    }
                }
                finally
                {
                    if (query != null)
                    {
                        ComUtilities.Release(ref query);
                    }
                }
            }
        }
        catch (System.Runtime.InteropServices.COMException)
        {
            // Query not found or error accessing collection
        }

        return null;
    }
}


