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;

/// <summary>
/// Power Query LoadTo operations - STANDALONE implementation.
/// Uses ListObjects.Add() pattern (same as Create) for consistency.
/// Based on Microsoft WorkbookQuery and ListObject API.
/// </summary>
public partial class PowerQueryCommands
{
    /// <summary>
    /// Applies load destination to an existing Power Query.
    /// Uses ListObjects.Add() for worksheet loading (consistent with Create).
    /// </summary>
    /// <remarks>
    /// Microsoft Docs Reference:
    /// - ListObjects.Add method - Creates Excel Table with external data source
    /// - QueryTable properties - Configure refresh behavior and formatting
    /// - Connections.Add2 method - Load to Data Model with CreateModelConnection=true
    ///
    /// IMPORTANT: Uses ListObjects.Add() (not QueryTables.Add()) for worksheet loading.
    /// This is the CORRECT approach per Microsoft docs and matches Create() behavior.
    /// </remarks>
    public OperationResult LoadTo(
        IExcelBatch batch,
        string queryName,
        PowerQueryLoadMode loadMode,
        string? targetSheet = null,
        string? targetCellAddress = null)
    {
        // Validate inputs
        bool requiresWorksheet = loadMode == PowerQueryLoadMode.LoadToTable || loadMode == PowerQueryLoadMode.LoadToBoth;

        if (requiresWorksheet && string.IsNullOrWhiteSpace(targetSheet))
        {
            targetSheet = queryName; // Default to query name
        }

        if (!string.IsNullOrWhiteSpace(targetCellAddress) && !requiresWorksheet)
        {
            throw new ArgumentException("targetCellAddress is only supported when loadMode is 'LoadToTable' or 'LoadToBoth'.", nameof(targetCellAddress));
        }

        targetCellAddress ??= "A1"; // Default cell address

        using var timeoutCts = new CancellationTokenSource(ComInteropConstants.DataOperationTimeout);

        return batch.Execute((ctx, ct) =>
        {
            Excel.Queries? queries = null;
            Excel.WorkbookQuery? query = null;
            var result = new PowerQueryLoadResult
            {
                FilePath = batch.WorkbookPath,
                QueryName = queryName,
                LoadDestination = loadMode,
                WorksheetName = targetSheet,
                TargetCellAddress = targetCellAddress
            };

            try
            {
                // STEP 1: Find the Power Query
                queries = ctx.Book.Queries;
                query = null;
                for (int i = 1; i <= queries.Count; i++)
                {
                    dynamic? q = null;
                    try
                    {
                        q = queries.Item(i);
                        string qName = q.Name?.ToString() ?? "";
                        if (qName.Equals(queryName, StringComparison.OrdinalIgnoreCase))
                        {
                            query = q;
                            q = null; // Don't release - keeping reference
                            break;
                        }
                    }
                    finally
                    {
                        ComUtilities.Release(ref q!);
                    }
                }

                if (query == null)
                {
                    throw new InvalidOperationException($"Query '{queryName}' not found.");
                }

                // STEP 2: Always clean up current destinations before applying the new load mode.
                // This ensures clean state transitions in all directions:
                // LoadToTable → DataModel (removes old ListObject + worksheet connection)
                // DataModel → LoadToTable (removes old Data Model connection)
                // LoadToBoth → ConnectionOnly (removes both destinations)
                // etc.
                UnloadFromDestinations(ctx.Book, queryName);

                // STEP 3: Apply load destination based on mode
                switch (loadMode)
                {
                    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;

                    case PowerQueryLoadMode.ConnectionOnly:
                        // UnloadFromDestinations already called above; just set result properties.
                        result.DataRefreshed = false;
                        result.RowsLoaded = 0;
                        result.TargetCellAddress = null;
                        result.Success = true;
                        break;
                }

                // Set additional result properties
                if (result.Success)
                {
                    result.ConfigurationApplied = true;
                    result.DataRefreshed = (loadMode != PowerQueryLoadMode.ConnectionOnly);
                }

                return new OperationResult { Success = true, FilePath = batch.WorkbookPath };
            }
            finally
            {
                ComUtilities.Release(ref query!);
                ComUtilities.Release(ref queries!);
            }
        }, timeoutCts.Token);
    }

    /// <summary>
    /// Loads query data to a worksheet using ListObjects.Add (correct approach for Power Query).
    /// SHARED IMPLEMENTATION - Used by both Create and LoadTo.
    /// </summary>
    /// <remarks>
    /// This is extracted from Create.cs for reuse. Both Create and LoadTo should use
    /// the same ListObjects.Add() pattern for consistency.
    /// Matches Excel UI behavior: Creates worksheet if it doesn't exist, or loads to existing worksheet.
    /// </remarks>
    private static bool LoadQueryToWorksheet(
        dynamic workbook,
        string queryName,
        string sheetName,
        string targetCellAddress,
        dynamic result,
        CancellationToken cancellationToken)
    {
        dynamic? worksheets = null;
        dynamic? sheet = null;
        dynamic? destination = null;
        dynamic? connections = null;
        dynamic? connection = null;
        dynamic? listObjects = null;
        dynamic? listObject = null;
        dynamic? queryTable = null;

        try
        {
            worksheets = workbook.Worksheets;

            // Check if worksheet exists (Excel UI behavior: validate occupied cells on existing sheets)
            bool worksheetExists = false;
            for (int i = 1; i <= worksheets.Count; i++)
            {
                dynamic? ws = null;
                try
                {
                    ws = worksheets.Item(i);
                    string wsName = ws.Name?.ToString() ?? "";
                    if (wsName.Equals(sheetName, StringComparison.OrdinalIgnoreCase))
                    {
                        worksheetExists = true;
                        sheet = ws;
                        ws = null; // Keep reference, don't release
                        break;
                    }
                }
                finally
                {
                    ComUtilities.Release(ref ws!);
                }
            }

            // Create new worksheet if doesn't exist
            if (!worksheetExists)
            {
                sheet = worksheets.Add();
                sheet.Name = sheetName;
            }

            if (sheet == null)
            {
                throw new InvalidOperationException($"Cannot access worksheet '{sheetName}'");
            }

            // Get destination range
            destination = sheet.Range[targetCellAddress];

            // For existing worksheets, check if target area would overlap with existing tables
            // Excel allows loading over cell data, but NOT over existing tables/PivotTables
            if (worksheetExists)
            {
                // Check if any ListObjects (tables) would overlap with this location
                // Excel error: "A table cannot overlap a range that contains a pivot table report, query results, protected cells or another table."
                dynamic? existingTables = null;
                try
                {
                    existingTables = sheet.ListObjects;
                    int tableCount = existingTables.Count;

                    if (tableCount > 0)
                    {
                        // Get destination cell row/column for comparison
                        int destRow = Convert.ToInt32(destination.Row);
                        int destCol = Convert.ToInt32(destination.Column);

                        for (int i = 1; i <= tableCount; i++)
                        {
                            dynamic? table = null;
                            dynamic? tableRange = null;
                            try
                            {
                                table = existingTables.Item(i);
                                tableRange = table.Range;

                                int tableStartRow = Convert.ToInt32(tableRange.Row);
                                int tableStartCol = Convert.ToInt32(tableRange.Column);
                                int tableEndRow = tableStartRow + Convert.ToInt32(tableRange.Rows.Count) - 1;
                                int tableEndCol = tableStartCol + Convert.ToInt32(tableRange.Columns.Count) - 1;

                                // Check if destination cell would overlap with existing table
                                if (destRow >= tableStartRow && destRow <= tableEndRow &&
                                    destCol >= tableStartCol && destCol <= tableEndCol)
                                {
                                    throw new InvalidOperationException($"Cell {targetCellAddress} on sheet '{sheetName}' overlaps with existing table.");
                                }
                            }
                            finally
                            {
                                ComUtilities.Release(ref tableRange);
                                ComUtilities.Release(ref table);
                            }
                        }
                    }
                }
                finally
                {
                    ComUtilities.Release(ref existingTables);
                }

                // Also check if target cell contains data (Excel UI validation)
                dynamic? cellValue = destination.Value2;
                bool cellHasData = cellValue != null && !string.IsNullOrWhiteSpace(cellValue.ToString());
                ComUtilities.Release(ref cellValue!);

                if (cellHasData)
                {
                    throw new InvalidOperationException($"Target cell '{targetCellAddress}' on worksheet '{sheetName}' already contains data. Choose a different targetCellAddress or clear the existing data first.");
                }
            }

            // Step 1: Create connection with Connections.Add2() using proper naming
            // This ensures the connection is named "Query - {queryName}" instead of generic "Connection", "Connection1", etc.
            connections = workbook.Connections;
            string connectionName = $"Query - {queryName}";
            string connectionDescription = $"Connection to the '{queryName}' query in the workbook.";
            string connectionString = $"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location={queryName}";
            string commandText = $"SELECT * FROM [{queryName}]";

            connection = connections.Add2(
                Name: connectionName,
                Description: connectionDescription,
                ConnectionString: connectionString,
                CommandText: commandText,
                lCmdtype: 2,                    // xlCmdSql
                CreateModelConnection: false,   // Worksheet loading, NOT Data Model
                ImportRelationships: false
            );

            // Step 2: Add ListObject using the connection object (not connection string)
            // This reuses the properly-named connection instead of creating a new generic one
            listObjects = sheet.ListObjects;
            listObject = listObjects.Add(
                0,                  // SourceType: 0 = xlSrcExternal
                connection,         // Source: connection object (reuses existing named connection)
                Type.Missing,       // LinkSource
                1,                  // XlListObjectHasHeaders: xlYes
                destination         // Destination: starting cell
            );

            // Configure the QueryTable behind the ListObject
            queryTable = listObject.QueryTable;
            queryTable.CommandType = 2; // xlCmdSql
            queryTable.CommandText = $"SELECT * FROM [{queryName}]";
            queryTable.AdjustColumnWidth = true;
            queryTable.PreserveFormatting = true;
            queryTable.BackgroundQuery = false; // Synchronous
            queryTable.RefreshStyle = 1; // xlInsertDeleteCells
            queryTable.PreserveColumnInfo = false; // Allow schema changes on refresh

            // Refresh to materialize the table.
            // Do NOT use EnterLongOperation here: synchronous QueryTable refresh depends on inbound
            // Excel callbacks to complete. Rejecting those callbacks can deadlock the load.
            OleMessageFilter.SetPendingCancellationToken(cancellationToken);
            try
            {
                queryTable.Refresh(false); // Synchronous refresh
            }
            finally
            {
                OleMessageFilter.ClearPendingCancellationToken();
            }

            // Name the table after the query for predictable M-code referencing.
            // Without this, Excel auto-assigns a generic name ("Table1", "Table2", etc.)
            // which makes Excel.CurrentWorkbook(){[Name=queryName]}[Content] lookups unreliable.
            try { listObject.Name = queryName; }
            catch { /* Non-critical — if rename fails (e.g. name conflict), load still succeeded. */ }

            // Capture results - use ListObject Range for total rows, subtract header
            dynamic? listObjectRange = listObject.Range;
            int totalRows = listObjectRange != null ? Convert.ToInt32(listObjectRange.Rows.Count) : 0;
            result.TargetCellAddress = targetCellAddress;
            result.RowsLoaded = totalRows > 0 ? totalRows - 1 : 0; // Subtract header row
            result.Success = true;

            ComUtilities.Release(ref listObjectRange!);
            return true;
        }
        finally
        {
            ComUtilities.Release(ref queryTable);
            ComUtilities.Release(ref listObject);
            ComUtilities.Release(ref listObjects);
            ComUtilities.Release(ref connection);
            ComUtilities.Release(ref connections);
            ComUtilities.Release(ref destination);
            ComUtilities.Release(ref sheet);
            ComUtilities.Release(ref worksheets);
        }
    }

    /// <summary>
    /// Loads query data to the Data Model using Connections.Add2.
    /// SHARED IMPLEMENTATION - Used by both Create and LoadTo.
    /// </summary>
    /// <param name="workbook">The workbook to load into.</param>
    /// <param name="queryName">The Power Query name.</param>
    /// <param name="result">Result object to populate.</param>
    /// <param name="connectionNameSuffix">Optional suffix for connection name to avoid conflicts (e.g., " (Data Model)").</param>
    private static bool LoadQueryToDataModel(
        dynamic workbook,
        string queryName,
        dynamic result,
        string? connectionNameSuffix = null,
        CancellationToken cancellationToken = default)
    {
        dynamic? connections = null;
        dynamic? connection = null;

        try
        {
            connections = workbook.Connections;

            // Use suffix if provided (for LoadToBoth to avoid conflict with worksheet connection)
            string connectionName = string.IsNullOrEmpty(connectionNameSuffix)
                ? $"Query - {queryName}"
                : $"Query - {queryName}{connectionNameSuffix}";
            string description = $"Connection to the '{queryName}' query in the workbook.";
            string connectionString = $"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location={queryName}";
            string commandText = $"\"{queryName}\"";

            connection = connections.Add2(
                Name: connectionName,
                Description: description,
                ConnectionString: connectionString,
                CommandText: commandText,
                lCmdtype: 6, // Data Model command type
                CreateModelConnection: true, // CRITICAL: This loads to Data Model
                ImportRelationships: false
            );

            // Refresh the connection to actually load data into the Data Model.
            // Without this call, the connection is registered but no data is materialized —
            // the table never appears in the Data Model even though success is returned.
            // Do NOT use EnterLongOperation here: synchronous connection.Refresh() can require
            // inbound callbacks from Excel/MashupHost to complete.
            OleMessageFilter.SetPendingCancellationToken(cancellationToken);
            try
            {
                connection.Refresh();
            }
            finally
            {
                OleMessageFilter.ClearPendingCancellationToken();
            }

            result.RowsLoaded = -1; // Data Model doesn't expose row count
            result.TargetCellAddress = null;
            result.Success = true;
            return true;
        }
        finally
        {
            ComUtilities.Release(ref connection);
            ComUtilities.Release(ref connections);
        }
    }

    /// <summary>
    /// Gets an existing worksheet or creates a new one.
    /// SHARED HELPER - Used by Create and LoadTo.
    /// </summary>
    private static dynamic? GetOrCreateWorksheet(dynamic worksheets, string sheetName)
    {

        // Try to find existing worksheet
        int count = worksheets.Count;
        dynamic? sheet;
        for (int i = 1; i <= count; i++)
        {
            dynamic? candidate = null;
            try
            {
                candidate = worksheets.Item(i);
                string name = candidate.Name ?? "";

                if (name.Equals(sheetName, StringComparison.OrdinalIgnoreCase))
                {
                    sheet = candidate;
                    candidate = null; // Prevent release in finally
                    return sheet;
                }
            }
            finally
            {
                if (candidate != null)
                {
                    ComUtilities.Release(ref candidate);
                }
            }
        }

        // Sheet not found, create new one
        sheet = worksheets.Add();
        sheet.Name = sheetName;
        return sheet;
    }
}


