using System.Runtime.InteropServices;
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 evaluate operation - executes M code and returns results without creating a permanent query
/// </summary>
public partial class PowerQueryCommands
{
    /// <inheritdoc />
    public PowerQueryEvaluateResult Evaluate(IExcelBatch batch, string mCode)
    {
        var result = new PowerQueryEvaluateResult
        {
            FilePath = batch.WorkbookPath,
            MCode = mCode
        };

        // Validate M code
        if (string.IsNullOrWhiteSpace(mCode))
        {
            throw new ArgumentException("M code is required for evaluate action", nameof(mCode));
        }

        // Generate unique temporary names
        var uniqueId = Guid.NewGuid().ToString("N")[..8];
        var tempQueryName = $"__pq_eval_{uniqueId}";
        var tempSheetName = $"__pq_eval_{uniqueId}";

        return batch.Execute((ctx, ct) =>
        {
            Excel.Queries? queriesCollection = null;
            Excel.WorkbookQuery? query = null;
            dynamic? worksheets = null;
            dynamic? tempSheet = null;
            dynamic? listObjects = null;
            dynamic? listObject = null;
            dynamic? queryTable = null;
            dynamic? range = null;
            dynamic? usedRange = null;

            try
            {
                // STEP 1: Create temporary query with the M code
                queriesCollection = ctx.Book.Queries;
                query = queriesCollection.Add(tempQueryName, mCode);

                // STEP 2: Create temporary worksheet and load data to it
                worksheets = ctx.Book.Worksheets;
                tempSheet = worksheets.Add();
                tempSheet.Name = tempSheetName;

                // STEP 3: Load query to worksheet using QueryTable
                // Connection string for Power Query - must include Extended Properties
                string connectionString = $"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location={tempQueryName};Extended Properties=\"\"";
                range = tempSheet.Range["A1"];

                listObjects = tempSheet.ListObjects;
                listObject = listObjects.Add(
                    0,                  // SourceType: 0 = xlSrcExternal
                    connectionString,   // Source: connection string
                    Type.Missing,       // LinkSource
                    1,                  // XlListObjectHasHeaders: xlYes = 1
                    range               // Destination: starting cell
                );

                // Get the QueryTable to refresh (this executes the M code)
                queryTable = listObject.QueryTable;

                // Configure QueryTable to select from the query
                queryTable.CommandType = 2; // xlCmdSql
                queryTable.CommandText = $"SELECT * FROM [{tempQueryName}]";
                queryTable.BackgroundQuery = false; // Synchronous

                // STEP 4: Refresh to execute the M code (errors will throw via QueryTable.Refresh)
                // This path uses the same synchronous worksheet refresh mechanism as PowerQuery refresh/load.
                // Do NOT use EnterLongOperation here: rejecting inbound COM callbacks can deadlock the
                // MashupHost → Excel → STA callback chain that completes the synchronous refresh.
                OleMessageFilter.SetPendingCancellationToken(ct);
                try
                {
                    queryTable.Refresh(false); // false = synchronous
                }
                finally
                {
                    OleMessageFilter.ClearPendingCancellationToken();
                }

                // STEP 5: Read the results from the worksheet
                // Get the data range from the ListObject
                dynamic? dataBodyRange = null;
                dynamic? headerRowRange = null;
                try
                {
                    // Get column names from header row
                    headerRowRange = listObject.HeaderRowRange;
                    if (headerRowRange != null)
                    {
                        dynamic? headerValues = headerRowRange.Value2;
                        if (headerValues != null)
                        {
                            if (headerValues is object[,] headers2D)
                            {
                                for (int col = 1; col <= headers2D.GetLength(1); col++)
                                {
                                    result.Columns.Add(headers2D[1, col]?.ToString() ?? $"Column{col}");
                                }
                            }
                            else if (headerValues is object[] headers1D)
                            {
                                for (int i = 0; i < headers1D.Length; i++)
                                {
                                    result.Columns.Add(headers1D[i]?.ToString() ?? $"Column{i + 1}");
                                }
                            }
                            else
                            {
                                // Single cell
                                result.Columns.Add(headerValues?.ToString() ?? "Column1");
                            }
                        }
                    }
                    result.ColumnCount = result.Columns.Count;

                    // Get data rows
                    dataBodyRange = listObject.DataBodyRange;
                    if (dataBodyRange != null)
                    {
                        dynamic? dataValues = dataBodyRange.Value2;
                        if (dataValues != null)
                        {
                            if (dataValues is object[,] data2D)
                            {
                                int rowCount = data2D.GetLength(0);
                                int colCount = data2D.GetLength(1);

                                for (int row = 1; row <= rowCount; row++)
                                {
                                    var rowData = new List<object?>();
                                    for (int col = 1; col <= colCount; col++)
                                    {
                                        rowData.Add(ConvertCellValue(data2D[row, col]));
                                    }
                                    result.Rows.Add(rowData);
                                }
                            }
                            else if (dataValues is object[] data1D)
                            {
                                // Single row
                                var rowData = new List<object?>();
                                foreach (var val in data1D)
                                {
                                    rowData.Add(ConvertCellValue(val));
                                }
                                result.Rows.Add(rowData);
                            }
                            else
                            {
                                // Single cell
                                result.Rows.Add([ConvertCellValue(dataValues)]);
                            }
                        }
                    }
                    result.RowCount = result.Rows.Count;
                }
                finally
                {
                    ComUtilities.Release(ref headerRowRange);
                    ComUtilities.Release(ref dataBodyRange);
                }

                result.Success = true;
            }
            finally
            {
                // STEP 6: Cleanup - delete temporary objects
                // Order matters: delete table, sheet, query, connections

                // Delete the ListObject (table)
                try
                {
                    if (listObject != null)
                    {
                        listObject.Delete();
                    }
                }
                catch (COMException) { /* ignore cleanup errors */ }

                ComUtilities.Release(ref queryTable);
                ComUtilities.Release(ref listObject);
                ComUtilities.Release(ref listObjects);
                ComUtilities.Release(ref range);
                ComUtilities.Release(ref usedRange);

                // Delete the temporary worksheet
                try
                {
                    if (tempSheet != null)
                    {
                        // Suppress alerts to avoid "Are you sure you want to delete?" prompt
                        dynamic? app = null;
                        try
                        {
                            app = ctx.Book.Application;
                            bool originalAlerts = app.DisplayAlerts;
                            app.DisplayAlerts = false;
                            try
                            {
                                tempSheet.Delete();
                            }
                            finally
                            {
                                app.DisplayAlerts = originalAlerts;
                            }
                        }
                        finally
                        {
                            ComUtilities.Release(ref app);
                        }
                    }
                }
                catch (COMException) { /* ignore cleanup errors */ }

                ComUtilities.Release(ref tempSheet);
                ComUtilities.Release(ref worksheets);

                // Delete the temporary query
                try
                {
                    if (query != null)
                    {
                        query.Delete();
                    }
                }
                catch (COMException) { /* ignore cleanup errors */ }

                ComUtilities.Release(ref query);
                ComUtilities.Release(ref queriesCollection);

                // Clean up any lingering connections
                dynamic? connections = null;
                try
                {
                    connections = ctx.Book.Connections;
                    for (int i = connections.Count; i >= 1; i--)
                    {
                        dynamic? conn = null;
                        try
                        {
                            conn = connections.Item(i);
                            string connName = conn.Name?.ToString() ?? "";
                            if (connName.Contains(tempQueryName))
                            {
                                conn.Delete();
                            }
                        }
                        catch (COMException) { /* ignore cleanup errors */ }
                        finally
                        {
                            ComUtilities.Release(ref conn);
                        }
                    }
                }
                catch (COMException) { /* ignore cleanup errors */ }
                finally
                {
                    ComUtilities.Release(ref connections);
                }
            }

            return result;
        });
    }

    /// <summary>
    /// Converts Excel cell values to JSON-friendly types
    /// </summary>
    private static object? ConvertCellValue(object? value)
    {
        if (value == null || value == DBNull.Value)
            return null;

        return value switch
        {
            DateTime dt => dt.ToString("O"), // ISO 8601 format
            double d => d,
            int i => i,
            long l => l,
            bool b => b,
            string s => s,
            _ => value.ToString()
        };
    }
}


