using System.Globalization;
using System.Text;

namespace Sbroenne.ExcelMcp.ComInterop.Formatting;

/// <summary>
/// Translates DAX formula argument separators between US (English) format (comma) and the
/// locale-specific separator that Excel's Analysis Services engine expects.
/// </summary>
/// <remarks>
/// <para><b>Why This Is Needed:</b></para>
/// <para>
/// Power Pivot's Analysis Services engine uses the SYSTEM CULTURE (not Excel's International
/// settings) to interpret DAX formula separators. This is a critical distinction because:
/// </para>
/// <list type="bullet">
/// <item>Excel's International property may report different settings than the system culture</item>
/// <item>Power Pivot interprets commas based on the system's NumberDecimalSeparator</item>
/// <item>If the system uses comma for decimals (European locales), DAX commas must be semicolons</item>
/// </list>
/// <para><b>Example Problem (Fixed by this translator):</b></para>
/// <para>
/// On a system with culture en-DE (English with German regional settings):
/// </para>
/// <list type="bullet">
/// <item>Excel.International reports: ListSeparator=',' DecimalSeparator='.'</item>
/// <item>System culture (en-DE) has: NumberDecimalSeparator=','</item>
/// <item>Power Pivot uses SYSTEM culture, so it sees comma as decimal separator</item>
/// <item>Formula "DATEADD(Date[Date], -1, MONTH)" becomes "DATEADD(Date[Date], -1. MONTH)" (corrupted!)</item>
/// </list>
/// <para><b>Solution:</b></para>
/// <para>
/// This translator checks the SYSTEM culture's decimal separator. If it's a comma,
/// we translate all DAX function argument commas to semicolons, regardless of what
/// Excel's International property reports.
/// </para>
/// <para><b>Usage:</b></para>
/// <code>
/// var translator = new DaxFormulaTranslator(excelApp);
/// string daxFormula = translator.TranslateToLocale("CALCULATE([ACR], DATEADD(Date[Date], -1, MONTH))");
/// // Returns "CALCULATE([ACR]; DATEADD(Date[Date]; -1; MONTH))" on European systems
/// </code>
/// </remarks>
public sealed class DaxFormulaTranslator
{
    // XlApplicationInternational enum values (for reference, but we prefer system culture)
    private const int XlListSeparator = 5;
    private const int XlDecimalSeparator = 3;

    /// <summary>The Excel International list separator (for logging/diagnostics)</summary>
    public string ExcelListSeparator { get; }

    /// <summary>The Excel International decimal separator (for logging/diagnostics)</summary>
    public string ExcelDecimalSeparator { get; }

    /// <summary>The system culture's decimal separator - THIS is what Power Pivot uses</summary>
    public string SystemDecimalSeparator { get; }

    /// <summary>The system culture's list separator (for diagnostics)</summary>
    public string SystemListSeparator { get; }

    /// <summary>The separator to use for DAX function arguments.
    /// Uses semicolon if system decimal separator is comma (European locales),
    /// otherwise uses the Excel list separator.
    /// </summary>
    public string DaxArgumentSeparator { get; }

    /// <summary>True if system uses comma as decimal separator (meaning DAX commas must become semicolons)</summary>
    public bool SystemUsesCommaDecimal { get; }

    /// <summary>True if translation is needed (system uses comma for decimal, so DAX commas must be semicolons)</summary>
    public bool RequiresTranslation => SystemUsesCommaDecimal;

    /// <summary>
    /// True if the system has an invalid configuration where both decimal and list separator are the same.
    /// This is a Windows Regional Settings misconfiguration that will cause DAX errors.
    /// </summary>
    /// <remarks>
    /// This can happen with the en-DE locale (English with German regional settings) where
    /// Windows may default to comma for both decimal and list separator. The solution is to
    /// change the Windows list separator to semicolon in Regional Settings > Additional settings.
    /// </remarks>
    public bool HasSeparatorConflict => SystemDecimalSeparator == SystemListSeparator;

    /// <summary>
    /// Creates a new DaxFormulaTranslator by reading locale settings from both Excel Application
    /// and the system culture. Power Pivot uses the SYSTEM culture, not Excel's International settings.
    /// </summary>
    /// <param name="excelApp">The Excel.Application COM object (dynamic)</param>
    public DaxFormulaTranslator(dynamic excelApp)
    {
        // Read Excel's International settings (for logging/diagnostics)
        ExcelListSeparator = GetInternationalValue(excelApp, XlListSeparator) ?? ",";
        ExcelDecimalSeparator = GetInternationalValue(excelApp, XlDecimalSeparator) ?? ".";

        // CRITICAL: Power Pivot uses the SYSTEM culture, not Excel's International settings!
        // Get the system culture's decimal and list separators
        SystemDecimalSeparator = CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator;
        SystemListSeparator = CultureInfo.CurrentCulture.TextInfo.ListSeparator;
        SystemUsesCommaDecimal = SystemDecimalSeparator == ",";

        // Determine the correct DAX argument separator:
        // - If SYSTEM uses comma for decimal, we MUST use semicolon (regardless of what Excel reports)
        // - Otherwise, use the Excel list separator (which is comma for US/UK locales)
        DaxArgumentSeparator = SystemUsesCommaDecimal ? ";" : ExcelListSeparator;
    }

    /// <summary>
    /// Translates a US (English) DAX formula to the locale-specific format Excel expects.
    /// Converts comma argument separators to the locale-specific list separator.
    /// </summary>
    /// <param name="usDaxFormula">US DAX formula with comma separators (e.g., "DATEADD(Date[Date], -1, MONTH)")</param>
    /// <returns>Locale-specific DAX formula (e.g., "DATEADD(Date[Date]; -1; MONTH)" on German Excel)</returns>
    /// <remarks>
    /// <para>Translation rules:</para>
    /// <list type="bullet">
    /// <item>Commas inside function parentheses are translated to locale list separator</item>
    /// <item>Content inside strings (single or double quotes) is preserved</item>
    /// <item>Content inside square brackets (column references like [Column Name]) is preserved</item>
    /// <item>Commas outside function calls are preserved (though rare in DAX)</item>
    /// </list>
    /// </remarks>
    public string TranslateToLocale(string usDaxFormula)
    {
        if (string.IsNullOrEmpty(usDaxFormula))
            return usDaxFormula;

        // If system uses period as decimal (US/UK locales), no translation needed
        // The comma in the formula is already the correct list separator
        if (!RequiresTranslation)
            return usDaxFormula;

        // Check if formula contains commas at all
        if (!usDaxFormula.Contains(','))
            return usDaxFormula;

        return TranslateFormula(usDaxFormula);
    }

    /// <summary>
    /// Translates DAX formula by converting comma separators to locale-specific separators.
    /// </summary>
    private string TranslateFormula(string formula)
    {
        var result = new StringBuilder(formula.Length);
        int parenDepth = 0;

        for (int i = 0; i < formula.Length; i++)
        {
            char c = formula[i];

            // Skip content in double quotes (string literals)
            if (c == '"')
            {
                int quoteEnd = FindClosingQuote(formula, i, '"');
                result.Append(formula.AsSpan(i, quoteEnd - i + 1));
                i = quoteEnd;
                continue;
            }

            // Skip content in single quotes (string literals in DAX)
            if (c == '\'')
            {
                int quoteEnd = FindClosingQuote(formula, i, '\'');
                result.Append(formula.AsSpan(i, quoteEnd - i + 1));
                i = quoteEnd;
                continue;
            }

            // Skip content in square brackets (column references like [Column Name, With Comma])
            if (c == '[')
            {
                int bracketEnd = FindClosingBracket(formula, i);
                result.Append(formula.AsSpan(i, bracketEnd - i + 1));
                i = bracketEnd;
                continue;
            }

            // Track parentheses depth
            if (c == '(')
            {
                parenDepth++;
                result.Append(c);
                continue;
            }

            if (c == ')')
            {
                parenDepth--;
                result.Append(c);
                continue;
            }

            // Translate commas inside function calls (parenDepth > 0)
            if (c == ',' && parenDepth > 0)
            {
                result.Append(DaxArgumentSeparator);
                continue;
            }

            // All other characters pass through unchanged
            result.Append(c);
        }

        return result.ToString();
    }

    /// <summary>
    /// Finds the closing quote character, handling escaped quotes.
    /// </summary>
    private static int FindClosingQuote(string formula, int startIndex, char quoteChar)
    {
        for (int i = startIndex + 1; i < formula.Length; i++)
        {
            if (formula[i] == quoteChar)
            {
                // Check for escaped quote (doubled quote character)
                if (i + 1 < formula.Length && formula[i + 1] == quoteChar)
                {
                    i++; // Skip the escaped quote
                    continue;
                }
                return i;
            }
        }
        // No closing quote found - return end of string
        return formula.Length - 1;
    }

    /// <summary>
    /// Finds the closing bracket ']', handling nested brackets.
    /// </summary>
    private static int FindClosingBracket(string formula, int startIndex)
    {
        int depth = 0;
        for (int i = startIndex; i < formula.Length; i++)
        {
            if (formula[i] == '[')
            {
                depth++;
            }
            else if (formula[i] == ']')
            {
                depth--;
                if (depth == 0)
                    return i;
            }
        }
        // No closing bracket found - return end of string
        return formula.Length - 1;
    }

    /// <summary>
    /// Gets a value from Excel's International property.
    /// </summary>
    private static string? GetInternationalValue(dynamic excelApp, int index)
    {
        try
        {
            // Access the International property with the index
            // Excel COM: excelApp.International(index) returns the locale-specific value
            object? value = excelApp.International[index];
            return value?.ToString();
        }
        catch (Exception ex) when (ex is System.Runtime.InteropServices.COMException or Microsoft.CSharp.RuntimeBinder.RuntimeBinderException)
        {
            // International property access failed for this index
            return null;
        }
    }

    /// <summary>
    /// Returns a summary of the locale settings for debugging/logging.
    /// </summary>
    public override string ToString()
    {
        var conflict = HasSeparatorConflict ? " [CONFLICT: decimal=list!]" : "";
        return $"DaxFormulaTranslator: SystemDecimal='{SystemDecimalSeparator}' SystemList='{SystemListSeparator}' ExcelList='{ExcelListSeparator}' ExcelDecimal='{ExcelDecimalSeparator}' DaxArgSeparator='{DaxArgumentSeparator}' RequiresTranslation={RequiresTranslation}{conflict}";
    }
}


