using System.Text.RegularExpressions;
using ClosedXML.Excel;

namespace GB5Shared.DocumentMerge;

public interface IExcelMergeEngine
{
    Task<DocumentMergeResultDTO> MergeAsync(
        byte[] templateBytes,
        IReadOnlyDictionary<string, object?> fields,
        IReadOnlyDictionary<string, IReadOnlyList<IReadOnlyDictionary<string, object?>>>? tables,
        CancellationToken ct = default);
}

/// <summary>
/// ClosedXML-based Excel (.xlsx) merge engine — the same three capabilities as
/// <see cref="WordMergeEngine"/>, applied to spreadsheet templates: field merge, real repeating
/// rows (rows are physically inserted into the same worksheet, not split into separate files),
/// and conditional rows/sections. Placeholder syntax matches the Word engine exactly
/// (##FieldName##, ##REPEAT:Name##, ##IF:Field##/##ENDIF##, ##IMG:Field##) since both engines are
/// meant to feel like one capability to template authors.
/// </summary>
public sealed class ExcelMergeEngine : IExcelMergeEngine
{
    private static readonly Regex TokenRegex    = new(@"##(?<name>[A-Za-z0-9_.]+)##", RegexOptions.Compiled);
    private static readonly Regex IfStartRegex  = new(@"##IF:(?<name>[A-Za-z0-9_.]+)##", RegexOptions.Compiled);
    private static readonly Regex ImgTokenRegex = new(@"##IMG:(?<name>[A-Za-z0-9_.]+)##", RegexOptions.Compiled);
    private const string EndIfToken = "##ENDIF##";

    public Task<DocumentMergeResultDTO> MergeAsync(
        byte[] templateBytes,
        IReadOnlyDictionary<string, object?> fields,
        IReadOnlyDictionary<string, IReadOnlyList<IReadOnlyDictionary<string, object?>>>? tables,
        CancellationToken ct = default)
    {
        ct.ThrowIfCancellationRequested();

        using var input = new MemoryStream(templateBytes);
        using var workbook = new XLWorkbook(input);

        foreach (var ws in workbook.Worksheets)
        {
            if (tables is { Count: > 0 })
                foreach (var (tableName, rows) in tables)
                    ExpandRepeatRows(ws, tableName, rows, fields);

            ResolveConditionalRows(ws, fields);
            ReplaceImageTokens(ws, fields);
            ReplaceFieldTokens(ws, fields);
        }

        using var output = new MemoryStream();
        workbook.SaveAs(output);

        var result = new DocumentMergeResultDTO
        {
            Content     = output.ToArray(),
            ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            FileName    = "merged.xlsx"
        };
        return Task.FromResult(result);
    }

    // ── Repeat regions (real per-row insert, one worksheet) ──────────────────────────────────

    private static void ExpandRepeatRows(
        IXLWorksheet ws, string tableName,
        IReadOnlyList<IReadOnlyDictionary<string, object?>> rows,
        IReadOnlyDictionary<string, object?> globalFields)
    {
        var marker = $"##REPEAT:{tableName}##";

        IXLRow? templateRow = null;
        foreach (var row in ws.RowsUsed())
        {
            if (row.CellsUsed().Any(c => c.GetString().Contains(marker, StringComparison.OrdinalIgnoreCase)))
            {
                templateRow = row;
                break;
            }
        }
        if (templateRow is null)
            return; // No such repeat region in this template — not an error, just a no-op.

        var templateRowNum = templateRow.RowNumber();
        var firstCol = ws.FirstColumnUsed()?.ColumnNumber() ?? 1;
        var lastCol  = ws.LastColumnUsed()?.ColumnNumber() ?? firstCol;

        // Snapshot the template row's cell text + style BEFORE inserting rows (row numbers below
        // the template row will shift once InsertRowsBelow runs).
        var snapshot = new List<(int Col, string Text, IXLStyle Style)>();
        for (var col = firstCol; col <= lastCol; col++)
        {
            var cell = templateRow.Cell(col);
            snapshot.Add((col, cell.GetString(), cell.Style));
        }

        if (rows.Count == 0)
        {
            templateRow.Delete();
            return;
        }

        if (rows.Count > 1)
            templateRow.InsertRowsBelow(rows.Count - 1);

        for (var i = 0; i < rows.Count; i++)
        {
            var targetRow = ws.Row(templateRowNum + i);
            var merged = new Dictionary<string, object?>(globalFields, StringComparer.OrdinalIgnoreCase);
            foreach (var kv in rows[i]) merged[kv.Key] = kv.Value;

            foreach (var (col, text, style) in snapshot)
            {
                var targetCell = targetRow.Cell(col);
                targetCell.Style = style;
                targetCell.Value = ReplaceTokenString(text, merged, marker);
            }
        }
    }

    // ── Conditional rows/sections ─────────────────────────────────────────────────────────────

    private static void ResolveConditionalRows(IXLWorksheet ws, IReadOnlyDictionary<string, object?> fields)
    {
        var rows = ws.RowsUsed().OrderBy(r => r.RowNumber()).ToList();
        var rowsToDelete = new List<int>();

        int?    blockStartRow = null;
        string? blockField    = null;

        void FinishBlock(int endRow)
        {
            var truthy = IsTruthy(blockField!, fields);
            if (!truthy)
            {
                for (var r = blockStartRow!.Value; r <= endRow; r++)
                    rowsToDelete.Add(r);
            }
            else
            {
                StripMarkerFromRow(ws.Row(blockStartRow!.Value), $"##IF:{blockField}##");
                StripMarkerFromRow(ws.Row(endRow), EndIfToken);
            }
            blockStartRow = null;
            blockField    = null;
        }

        foreach (var row in rows)
        {
            var rowText = string.Join(" ", row.CellsUsed().Select(c => c.GetString()));

            if (blockStartRow is null)
            {
                var m = IfStartRegex.Match(rowText);
                if (!m.Success) continue;

                blockStartRow = row.RowNumber();
                blockField    = m.Groups["name"].Value;

                if (rowText.Contains(EndIfToken, StringComparison.OrdinalIgnoreCase))
                    FinishBlock(row.RowNumber()); // single-row conditional

                continue;
            }

            if (rowText.Contains(EndIfToken, StringComparison.OrdinalIgnoreCase))
                FinishBlock(row.RowNumber());
        }

        // Delete bottom-to-top so earlier row numbers in the pending list stay valid.
        foreach (var r in rowsToDelete.Distinct().OrderByDescending(x => x))
            ws.Row(r).Delete();
    }

    private static void StripMarkerFromRow(IXLRow row, string marker)
    {
        foreach (var cell in row.CellsUsed().ToList())
        {
            var text = cell.GetString();
            if (text.Contains(marker, StringComparison.OrdinalIgnoreCase))
                cell.Value = text.Replace(marker, string.Empty, StringComparison.OrdinalIgnoreCase);
        }
    }

    // ── Image merge fields ────────────────────────────────────────────────────────────────────

    private static void ReplaceImageTokens(IXLWorksheet ws, IReadOnlyDictionary<string, object?> fields)
    {
        foreach (var cell in ws.CellsUsed().ToList())
        {
            var text = cell.GetString();
            var matches = ImgTokenRegex.Matches(text);
            if (matches.Count == 0) continue;

            foreach (Match m in matches)
            {
                var name = m.Groups["name"].Value;
                if (fields.TryGetValue(name, out var v) && v is byte[] { Length: > 0 } imageBytes)
                {
                    using var ms = new MemoryStream(imageBytes);
                    ws.AddPicture(ms).MoveTo(cell);
                }
            }

            // Strip the raw token text regardless of whether an image was placed.
            cell.Value = ImgTokenRegex.Replace(text, string.Empty);
        }
    }

    // ── Field-token substitution ──────────────────────────────────────────────────────────────

    private static void ReplaceFieldTokens(IXLWorksheet ws, IReadOnlyDictionary<string, object?> fields)
    {
        foreach (var cell in ws.CellsUsed().ToList())
        {
            var text = cell.GetString();
            if (string.IsNullOrEmpty(text) || !text.Contains("##", StringComparison.Ordinal)) continue;

            var replaced = ReplaceTokenString(text, fields);
            if (replaced != text) cell.Value = replaced;
        }
    }

    private static string ReplaceTokenString(
        string text, IReadOnlyDictionary<string, object?> fields, string? literalMarkerToStrip = null)
    {
        if (string.IsNullOrEmpty(text)) return text;

        if (!string.IsNullOrEmpty(literalMarkerToStrip))
            text = text.Replace(literalMarkerToStrip, string.Empty, StringComparison.OrdinalIgnoreCase);

        return TokenRegex.Replace(text, m =>
            fields.TryGetValue(m.Groups["name"].Value, out var v) ? FormatValue(v) : m.Value);
    }

    // ── Shared helpers (kept identical to WordMergeEngine's semantics) ───────────────────────

    private static bool IsTruthy(string fieldName, IReadOnlyDictionary<string, object?> fields)
    {
        if (!fields.TryGetValue(fieldName, out var v) || v is null) return false;
        return v switch
        {
            bool b     => b,
            string s   => !string.IsNullOrWhiteSpace(s) && s != "0" && !s.Equals("false", StringComparison.OrdinalIgnoreCase),
            int i      => i != 0,
            long l     => l != 0,
            decimal d  => d != 0,
            double db  => db != 0,
            _          => true
        };
    }

    private static string FormatValue(object? value) => value switch
    {
        null        => string.Empty,
        string s    => s,
        DateTime dt => dt.ToString("dd-MMM-yyyy"),
        bool b      => b ? "Yes" : "No",
        _           => Convert.ToString(value, System.Globalization.CultureInfo.InvariantCulture) ?? string.Empty
    };
}