using System.Runtime.InteropServices;
using Microsoft.CSharp.RuntimeBinder;
using Sbroenne.ExcelMcp.ComInterop;
using Sbroenne.ExcelMcp.Core.DataModel;
using Sbroenne.ExcelMcp.Core.Models;
using Excel = Microsoft.Office.Interop.Excel;

namespace Sbroenne.ExcelMcp.Core.Commands;

/// <summary>
/// Private helper methods for DataModel commands
/// </summary>
public partial class DataModelCommands
{
    private static DataModelAdoDiagnostics CollectAdoDiagnostics(dynamic? adoConnection)
    {
        if (adoConnection == null)
        {
            return new DataModelAdoDiagnostics();
        }

        var connectionString = TryGetAdoConnectionString(adoConnection);
        return new DataModelAdoDiagnostics
        {
            ConnectionString = connectionString,
            ProviderName = DataModelAdoDiagnostics.ExtractProviderName(connectionString)
        };
    }

    private static string? TryGetAdoConnectionString(dynamic adoConnection)
    {
        try
        {
            return adoConnection.ConnectionString?.ToString();
        }
        catch (Exception ex) when (ex is COMException or RuntimeBinderException)
        {
            return null;
        }
    }

    /// <summary>
    /// Gets all measure names from the Data Model
    /// </summary>
    /// <param name="model">Model COM object</param>
    /// <returns>List of measure names</returns>
    private static List<string> GetModelMeasureNames(Excel.Model model)
    {
        var names = new List<string>();
        Excel.ModelMeasures? measures = null;
        try
        {
            // Get measures collection from MODEL (not from tables!)
            // Reference: https://learn.microsoft.com/en-us/office/vba/api/excel.model.modelmeasures
            measures = model.ModelMeasures;

            for (int m = 1; m <= measures.Count; m++)
            {
                Excel.ModelMeasure? measure = null;
                try
                {
                    measure = measures.Item(m);
                    names.Add(measure.Name?.ToString() ?? "");
                }
                finally
                {
                    ComUtilities.Release(ref measure);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref measures);
        }
        return names;
    }

    /// <summary>
    /// Gets the table name that contains a specific measure
    /// </summary>
    /// <param name="model">Model COM object</param>
    /// <param name="measureName">Measure name to find</param>
    /// <returns>Table name if found, null otherwise</returns>
    private static string? GetMeasureTableName(Excel.Model model, string measureName)
    {
        Excel.ModelMeasures? measures = null;
        try
        {
            // Get measures collection from MODEL (not from table!)
            // All measures are at model level with AssociatedTable property
            // Reference: https://learn.microsoft.com/en-us/office/vba/api/excel.model.modelmeasures
            measures = model.ModelMeasures;

            for (int m = 1; m <= measures.Count; m++)
            {
                Excel.ModelMeasure? measure = null;
                try
                {
                    measure = measures.Item(m);
                    string name = measure.Name?.ToString() ?? "";
                    if (name.Equals(measureName, StringComparison.OrdinalIgnoreCase))
                    {
                        // Get the associated table name
                        Excel.ModelTable? associatedTable = null;
                        try
                        {
                            associatedTable = measure.AssociatedTable;
                            return associatedTable?.Name?.ToString() ?? string.Empty;
                        }
                        finally
                        {
                            ComUtilities.Release(ref associatedTable);
                        }
                    }
                }
                finally
                {
                    ComUtilities.Release(ref measure);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref measures);
        }
        return null;
    }

    /// <summary>
    /// Finds a column in a model table by name (case-insensitive)
    /// </summary>
    /// <param name="table">Table COM object</param>
    /// <param name="columnName">Column name to find</param>
    /// <returns>Column COM object or null if not found</returns>
    private static Excel.ModelTableColumn? FindModelTableColumn(Excel.ModelTable table, string columnName)
    {
        Excel.ModelTableColumns? columns = null;
        try
        {
            columns = table.ModelTableColumns;
            int count = columns.Count;

            for (int i = 1; i <= count; i++)
            {
                Excel.ModelTableColumn? column = null;
                try
                {
                    column = columns.Item(i);
                    string currentName = column.Name?.ToString() ?? "";

                    if (currentName.Equals(columnName, StringComparison.OrdinalIgnoreCase))
                    {
                        // Found match - don't release, caller will use it
                        var foundColumn = column;
                        column = null;  // Prevent release in finally
                        return foundColumn;
                    }
                }
                finally
                {
                    // Only release if we didn't return it
                    if (column != null)
                    {
                        ComUtilities.Release(ref column);
                    }
                }
            }

            return null;
        }
        finally
        {
            ComUtilities.Release(ref columns);
        }
    }

    /// <summary>
    /// Gets the appropriate format object from the model for measure creation
    /// </summary>
    /// <param name="model">Model COM object</param>
    /// <param name="formatType">Format type (Currency, Decimal, Percentage, General)</param>
    /// <returns>FormatInformation COM object (never null - always returns at least ModelFormatGeneral)</returns>
    private static object GetFormatObject(Excel.Model model, string? formatType)
    {
        // CRITICAL FIX: FormatInformation parameter is REQUIRED by Excel COM API
        // Microsoft docs state it's required, but behavior is inconsistent:
        // - Fresh Data Model files: Accept Type.Missing (works)
        // - Reopened Data Model files: Require actual format object (fails with Type.Missing)
        // Solution: Always return a format object - use ModelFormatGeneral as default
        // See: docs/KNOWN-ISSUES.md for investigation details

        if (string.IsNullOrEmpty(formatType) || formatType.Equals("General", StringComparison.OrdinalIgnoreCase))
        {
            return model.ModelFormatGeneral;  // Default format
        }

        try
        {
            return formatType.ToLowerInvariant() switch
            {
                "currency" => (object)model.ModelFormatCurrency,
                "decimal" => (object)model.ModelFormatDecimalNumber,
                "percentage" => (object)model.ModelFormatPercentageNumber,
                "wholenumber" => (object)model.ModelFormatWholeNumber,
                _ => (object)model.ModelFormatGeneral  // Fallback to General for unknown types
            };
        }
        catch (Exception ex) when (ex is COMException or RuntimeBinderException)
        {
            // COM format object not available - use General as safe fallback
            return model.ModelFormatGeneral;
        }
    }

    /// <summary>
    /// Extracts format information from a ModelFormat* COM object as a structured object.
    /// FormatInformation returns one of: ModelFormatGeneral, ModelFormatCurrency,
    /// ModelFormatDecimalNumber, ModelFormatPercentageNumber, ModelFormatWholeNumber,
    /// ModelFormatScientificNumber, ModelFormatBoolean, ModelFormatDate.
    /// These don't have a FormatString property - they have type-specific properties like DecimalPlaces, Symbol.
    /// </summary>
    /// <param name="formatInfo">The FormatInformation COM object from a ModelMeasure</param>
    /// <returns>Structured format info with Type, Symbol, DecimalPlaces, UseThousandSeparator as applicable</returns>
    private static MeasureFormatInfo GetFormatInfo(dynamic formatInfo)
    {
        var result = new MeasureFormatInfo { Type = "General" };

        try
        {
            // Try to detect the format type by checking for type-specific properties
            // Each ModelFormat* type has different properties available

            // Check for Currency (has Symbol and DecimalPlaces)
            // COM property probing: access throws COMException or RuntimeBinderException if property doesn't exist on this format type
            try
            {
                string? symbol = formatInfo.Symbol?.ToString();
                if (!string.IsNullOrEmpty(symbol))
                {
                    result.Type = "Currency";
                    result.Symbol = symbol;
                    result.DecimalPlaces = Convert.ToInt32(formatInfo.DecimalPlaces);
                    return result;
                }
            }
            catch (Exception ex) when (ex is COMException or RuntimeBinderException) { /* Not a currency format - property doesn't exist */ }

            // Check for Percentage (has DecimalPlaces and UseThousandSeparator)
            try
            {
                // Percentage format has UseThousandSeparator but no Symbol
                bool useThousands = formatInfo.UseThousandSeparator;
                int decimals = Convert.ToInt32(formatInfo.DecimalPlaces);
                // If we got here without exception, it's likely Percentage or Decimal
                result.Type = "Percentage";
                result.DecimalPlaces = decimals;
                result.UseThousandSeparator = useThousands;
                return result;
            }
            catch (Exception ex) when (ex is COMException or RuntimeBinderException) { /* Not a percentage format - property doesn't exist */ }

            // Check for DecimalNumber or WholeNumber (has DecimalPlaces)
            try
            {
                int decimals = Convert.ToInt32(formatInfo.DecimalPlaces);
                result.Type = decimals == 0 ? "WholeNumber" : "Decimal";
                result.DecimalPlaces = decimals;
                return result;
            }
            catch (Exception ex) when (ex is COMException or RuntimeBinderException) { /* Not a decimal format - property doesn't exist */ }

            // Default to General if we can't determine the type
            return result;
        }
        catch (Exception ex) when (ex is COMException or RuntimeBinderException)
        {
            // COM object access failed entirely - return default General format
            return result;
        }
    }

    /// <summary>
    /// Finds a relationship between two tables by column names
    /// </summary>
    /// <param name="model">Model COM object</param>
    /// <param name="fromTable">From table name</param>
    /// <param name="fromColumn">From column name</param>
    /// <param name="toTable">To table name</param>
    /// <param name="toColumn">To column name</param>
    /// <returns>Relationship COM object or null if not found</returns>
    private static Excel.ModelRelationship? FindRelationship(Excel.Model model, string fromTable, string fromColumn, string toTable, string toColumn)
    {
        Excel.ModelRelationships? relationships = null;
        try
        {
            relationships = model.ModelRelationships;
            int count = relationships.Count;

            for (int i = 1; i <= count; i++)
            {
                Excel.ModelRelationship? relationship = null;
                try
                {
                    relationship = relationships.Item(i);

                    // Get relationship details
                    string currentFromTable = (relationship.ForeignKeyColumn?.Parent as Excel.ModelTable)?.Name?.ToString() ?? "";
                    string currentFromColumn = relationship.ForeignKeyColumn?.Name?.ToString() ?? "";
                    string currentToTable = (relationship.PrimaryKeyColumn?.Parent as Excel.ModelTable)?.Name?.ToString() ?? "";
                    string currentToColumn = relationship.PrimaryKeyColumn?.Name?.ToString() ?? "";

                    // Match all four components (case-insensitive)
                    if (currentFromTable.Equals(fromTable, StringComparison.OrdinalIgnoreCase) &&
                        currentFromColumn.Equals(fromColumn, StringComparison.OrdinalIgnoreCase) &&
                        currentToTable.Equals(toTable, StringComparison.OrdinalIgnoreCase) &&
                        currentToColumn.Equals(toColumn, StringComparison.OrdinalIgnoreCase))
                    {
                        // Found match - don't release, caller will use it
                        var foundRelationship = relationship;
                        relationship = null;  // Prevent release in finally
                        return foundRelationship;
                    }
                }
                finally
                {
                    // Only release if we didn't return it
                    if (relationship != null)
                    {
                        ComUtilities.Release(ref relationship);
                    }
                }
            }

            return null;
        }
        finally
        {
            ComUtilities.Release(ref relationships);
        }
    }

    /// <summary>
    /// Checks if the Data Model has any tables
    /// NOTE: Every workbook has a Model object, but it may be empty
    /// </summary>
    private static bool HasDataModelTables(Excel.Workbook workbook)
    {
        Excel.Model? model = null;
        Excel.ModelTables? modelTables = null;
        try
        {
            model = workbook.Model;
            if (model == null) return false;

            modelTables = model.ModelTables;
            return modelTables != null && modelTables.Count > 0;
        }
        finally
        {
            ComUtilities.Release(ref modelTables);
            ComUtilities.Release(ref model);
        }
    }

    /// <summary>
    /// Finds a table in the Data Model by name
    /// </summary>
    private static Excel.ModelTable? FindModelTable(Excel.Model model, string tableName)
    {
        Excel.ModelTables? modelTables = null;
        try
        {
            modelTables = model.ModelTables;
            for (int i = 1; i <= modelTables.Count; i++)
            {
                Excel.ModelTable? table = null;
                try
                {
                    table = modelTables.Item(i);
                    string name = table.Name?.ToString() ?? "";
                    if (name.Equals(tableName, StringComparison.OrdinalIgnoreCase))
                    {
                        var result = table;
                        table = null; // Don't release - returning it
                        return result;
                    }
                }
                finally
                {
                    ComUtilities.Release(ref table);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref modelTables);
        }
        return null;
    }

    /// <summary>
    /// Finds a DAX measure by name in the Data Model
    /// </summary>
    private static Excel.ModelMeasure? FindModelMeasure(Excel.Model model, string measureName)
    {
        Excel.ModelMeasures? measures = null;
        try
        {
            // Get measures collection from MODEL (not from table!)
            // All measures are at model level with AssociatedTable property
            // Reference: https://learn.microsoft.com/en-us/office/vba/api/excel.model.modelmeasures
            measures = model.ModelMeasures;
            int count = measures.Count;

            for (int i = 1; i <= count; i++)
            {
                Excel.ModelMeasure? measure = null;
                try
                {
                    measure = measures.Item(i);
                    string name = measure.Name?.ToString() ?? "";
                    if (name.Equals(measureName, StringComparison.OrdinalIgnoreCase))
                    {
                        // Found match - don't release, caller will use it
                        var result = measure;
                        measure = null; // Prevent release in finally
                        return result;
                    }
                }
                finally
                {
                    // Only release if we didn't return it
                    if (measure != null)
                    {
                        ComUtilities.Release(ref measure);
                    }
                }
            }

            return null;
        }
        finally
        {
            ComUtilities.Release(ref measures);
        }
    }

    /// <summary>
    /// Safely iterates through all tables in the Data Model
    /// </summary>
    private static void ForEachTable(Excel.Model model, Action<Excel.ModelTable, int> action)
    {
        Excel.ModelTables? modelTables = null;
        try
        {
            modelTables = model.ModelTables;
            int count = modelTables.Count;

            for (int i = 1; i <= count; i++)
            {
                Excel.ModelTable? table = null;
                try
                {
                    table = modelTables.Item(i);
                    action(table, i);
                }
                finally
                {
                    ComUtilities.Release(ref table);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref modelTables);
        }
    }

    /// <summary>
    /// Safely iterates through all measures in the Data Model
    /// </summary>
    private static void ForEachMeasure(Excel.Model model, Action<Excel.ModelMeasure, int> action)
    {
        Excel.ModelMeasures? measures = null;
        try
        {
            // Get measures collection from MODEL (not from table!)
            measures = model.ModelMeasures;
            int count = measures.Count;

            for (int i = 1; i <= count; i++)
            {
                Excel.ModelMeasure? measure = null;
                try
                {
                    measure = measures.Item(i);
                    action(measure, i);
                }
                finally
                {
                    ComUtilities.Release(ref measure);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref measures);
        }
    }

    /// <summary>
    /// Safely iterates through all relationships in the Data Model
    /// </summary>
    private static void ForEachRelationship(Excel.Model model, Action<Excel.ModelRelationship, int> action)
    {
        Excel.ModelRelationships? relationships = null;
        try
        {
            relationships = model.ModelRelationships;
            int count = relationships.Count;

            for (int i = 1; i <= count; i++)
            {
                Excel.ModelRelationship? relationship = null;
                try
                {
                    relationship = relationships.Item(i);
                    action(relationship, i);
                }
                finally
                {
                    ComUtilities.Release(ref relationship);
                }
            }
        }
        finally
        {
            ComUtilities.Release(ref relationships);
        }
    }
}


