using System;
using System.Collections.Generic;
using System.Linq;
using System.Globalization;
using System.Text;
using System.Threading;
using ClosedXML.Excel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace ExcelMatcher;

public sealed class TableData
{
    public string FilePath { get; init; } = "";
    public string SheetName { get; init; } = "";
    public List<string> Columns { get; init; } = new();
    public List<string[]> Rows { get; init; } = new();
    public int RowCount => Rows.Count;
}

public static class ExcelReader
{
    public static List<TableData> LoadWorkbook(string path)
    {
        var ext = Path.GetExtension(path).ToLowerInvariant();
        return ext is ".xls" or ".xlt" ? LoadXls(path) : LoadXlsx(path);
    }

    private static List<TableData> LoadXlsx(string path)
    {
        var result = new List<TableData>();
        using var wb = new XLWorkbook(path);
        foreach (var ws in wb.Worksheets)
        {
            result.Add(LoadSheet(path, ws));
        }
        return result;
    }

    private static List<TableData> LoadXls(string path)
    {
        var result = new List<TableData>();
        using var fs = File.OpenRead(path);
        var wb = new HSSFWorkbook(fs);
        var fmt = new DataFormatter();
        for (int i = 0; i < wb.NumberOfSheets; i++)
        {
            var ws = wb.GetSheetAt(i);
            if (ws is null) continue;
            result.Add(LoadXlsSheet(path, ws, fmt));
        }
        return result;
    }

    private static string CellText(ISheet ws, int r, int c, DataFormatter fmt)
    {
        var cell = ws.GetRow(r)?.GetCell(c);
        return cell is null ? "" : (fmt.FormatCellValue(cell) ?? "");
    }

    private static TableData LoadXlsSheet(string path, ISheet ws, DataFormatter fmt)
    {
        int firstRow = -1, lastRow = -1, firstCol = -1, lastCol = -1;

        for (int r = 0; r <= ws.LastRowNum; r++)
        {
            var row = ws.GetRow(r);
            if (row is null) continue;
            int minCol = -1, maxCol = -1;
            foreach (var cell in row.Cells)
            {
                if (string.IsNullOrWhiteSpace(fmt.FormatCellValue(cell))) continue;
                int c = cell.ColumnIndex;
                if (minCol == -1 || c < minCol) minCol = c;
                if (maxCol == -1 || c > maxCol) maxCol = c;
            }
            if (minCol == -1) continue;
            if (firstRow == -1) firstRow = r;
            lastRow = r;
            if (firstCol == -1 || minCol < firstCol) firstCol = minCol;
            if (lastCol == -1 || maxCol > lastCol) lastCol = maxCol;
        }

        if (firstRow == -1)
            return new TableData { FilePath = path, SheetName = ws.SheetName };

        int colCount = lastCol - firstCol + 1;
        var cols = new List<string>();
        for (int c = 0; c < colCount; c++)
        {
            var header = CellText(ws, firstRow, firstCol + c, fmt);
            cols.Add(string.IsNullOrEmpty(header) ? $"Колонка {c + 1}" : header);
        }

        var rows = new List<string[]>();
        for (int r = firstRow + 1; r <= lastRow; r++)
        {
            var row = new string[colCount];
            bool any = false;
            for (int c = 0; c < colCount; c++)
            {
                var cell = CellText(ws, r, firstCol + c, fmt);
                row[c] = cell;
                if (!string.IsNullOrWhiteSpace(cell)) any = true;
            }
            if (any) rows.Add(row);
        }

        return new TableData
        {
            FilePath = path,
            SheetName = ws.SheetName,
            Columns = cols,
            Rows = rows
        };
    }

    private static TableData LoadSheet(string path, IXLWorksheet ws)
    {
        var cols = new List<string>();
        var rows = new List<string[]>();

        var firstUsed = ws.FirstCellUsed();
        var lastUsed = ws.LastCellUsed();
        if (firstUsed == null || lastUsed == null)
            return new TableData { FilePath = path, SheetName = ws.Name };

        int firstRow = firstUsed.Address.RowNumber;
        int lastRow = lastUsed.Address.RowNumber;
        int firstCol = firstUsed.Address.ColumnNumber;
        int lastCol = lastUsed.Address.ColumnNumber;
        int colCount = lastCol - firstCol + 1;

        for (int c = 0; c < colCount; c++)
        {
            var header = ws.Cell(firstRow, firstCol + c).GetString().Trim();
            cols.Add(string.IsNullOrEmpty(header) ? $"Колонка {c + 1}" : header);
        }

        for (int r = firstRow + 1; r <= lastRow; r++)
        {
            var row = new string[colCount];
            bool any = false;
            for (int c = 0; c < colCount; c++)
            {
                var cell = ws.Cell(r, firstCol + c).GetString();
                row[c] = cell;
                if (!string.IsNullOrWhiteSpace(cell)) any = true;
            }
            if (any) rows.Add(row);
        }

        return new TableData
        {
            FilePath = path,
            SheetName = ws.Name,
            Columns = cols,
            Rows = rows
        };
    }
}

public enum MatchMode
{
    KeepMatches,
    KeepOnlyMain,
    KeepOnlySecondary,
    KeepBoth
}

public enum MatchKind
{
    Exact,
    Partial,
    Fuzzy
}

public sealed class MatchOptions
{
    public bool[] MainUsed = Array.Empty<bool>();
    public bool[] SecondaryUsed = Array.Empty<bool>();
    public bool IgnoreCaseAndSpaces = true;
    public bool ExtendedNormalize = false;
    public MatchKind Kind = MatchKind.Exact;
    public int FuzzyThreshold = 85;
    public bool UseOr = false;
    public IReadOnlyDictionary<string, string>? Synonyms;
    public CancellationToken Token = default;

    public MatchOptions Clone() => (MatchOptions)MemberwiseClone();
}

public sealed class MatchPair
{
    public string[] RowA { get; init; } = Array.Empty<string>();
    public string[] RowB { get; init; } = Array.Empty<string>();
    public string Type { get; init; } = "";
    public double Score { get; init; }
    public int BCount { get; init; }
}

public sealed class MatchResult
{
    public List<string[]> Matches { get; init; } = new();
    public List<string[]> OnlyMain { get; init; } = new();
    public List<string[]> OnlySecondary { get; init; } = new();
    public List<MatchPair> Pairs { get; init; } = new();
}

public sealed class MergeOutput
{
    public List<string> Columns { get; init; } = new();
    public List<string[]> Rows { get; init; } = new();
    public int[] SecondaryColumnMap { get; init; } = Array.Empty<int>();
}

public static class TableMatcher
{
    private static List<int> KeyIndexes(bool[] used)
    {
        return used.Select((u, i) => (u, i)).Where(x => x.u).Select(x => x.i).ToList();
    }

    public static double Similarity(string a, string b)
    {
        if (a == b) return 1;
        if (a.Length == 0) return 0;
        if (b.Length == 0) return 0;
        int dist = Levenshtein(a, b);
        int maxLen = Math.Max(a.Length, b.Length);
        return 1.0 - (double)dist / maxLen;
    }

    private static int Levenshtein(string a, string b)
    {
        var prev = new int[b.Length + 1];
        var curr = new int[b.Length + 1];
        for (int j = 0; j <= b.Length; j++) prev[j] = j;
        for (int i = 1; i <= a.Length; i++)
        {
            curr[0] = i;
            for (int j = 1; j <= b.Length; j++)
            {
                int cost = a[i - 1] == b[j - 1] ? 0 : 1;
                curr[j] = Math.Min(Math.Min(curr[j - 1] + 1, prev[j] + 1), prev[j - 1] + cost);
            }
            (prev, curr) = (curr, prev);
        }
        return prev[b.Length];
    }

    private static string NormalizeBase(string s, IReadOnlyDictionary<string, string>? synonyms)
    {
        var t = s;
        if (synonyms is not null)
        {
            foreach (var kv in synonyms)
                t = t.Replace(kv.Key, kv.Value, StringComparison.OrdinalIgnoreCase);
        }
        return t.Replace(" ", "").Replace("\t", "").ToLowerInvariant().Replace('ё', 'e');
    }

    private static string NormalizeCore(string s)
    {
        var sb = new StringBuilder(s.Length);
        foreach (char ch in s.ToLowerInvariant())
        {
            if (char.IsLetterOrDigit(ch)) sb.Append(ch);
        }
        var clean = sb.ToString().Replace('ё', 'е');
        foreach (var p in new[] { "ооо", "зао", "оао", "тоо", "пао" })
        {
            if (clean.StartsWith(p, StringComparison.Ordinal) && clean.Length > p.Length + 1)
            {
                clean = clean.Substring(p.Length);
                break;
            }
        }
        return clean;
    }

    private static string NormValue(string s, bool ignore, MatchOptions o)
    {
        if (!ignore) return s;
        return o.ExtendedNormalize ? NormalizeCore(s) : NormalizeBase(s, o.Synonyms);
    }

    private static string[] RowParts(string[] row, List<int> keyIndexes, int take, MatchOptions o)
    {
        var parts = new string[take];
        for (int i = 0; i < take; i++)
            parts[i] = keyIndexes[i] < row.Length ? NormValue(row[keyIndexes[i]], o.IgnoreCaseAndSpaces, o) : "";
        return parts;
    }

    private static bool IsEmptyParts(string[] parts) => parts.All(string.IsNullOrEmpty);

    private static string JoinKey(string[] parts) => string.Concat(parts.Select(part => $"{part.Length}:{part};"));

    private static bool PartsContain(string[] a, string[] b)
    {
        for (int i = 0; i < a.Length; i++)
        {
            var x = a[i];
            var y = b[i];
            if (string.IsNullOrEmpty(x) || string.IsNullOrEmpty(y)) return false;
            if (x.Contains(y, StringComparison.Ordinal) || y.Contains(x, StringComparison.Ordinal)) continue;
            return false;
        }
        return true;
    }

    private static bool AnyColContains(string[] a, string[] b)
    {
        for (int i = 0; i < a.Length; i++)
        {
            var x = a[i];
            var y = b[i];
            if (string.IsNullOrEmpty(x) || string.IsNullOrEmpty(y)) continue;
            if (x.Contains(y, StringComparison.Ordinal) || y.Contains(x, StringComparison.Ordinal)) return true;
        }
        return false;
    }

    private static double ColumnSim(string[] a, string[] b, bool orMode)
    {
        double sum = 0;
        int n = 0;
        double best = 0;
        for (int i = 0; i < a.Length; i++)
        {
            if (a[i].Length == 0 && b[i].Length == 0 && !orMode)
            {
                sum += 1;
                n++;
                continue;
            }
            if (a[i].Length == 0 || b[i].Length == 0)
            {
                if (orMode) continue;
                sum += 0;
                n++;
                continue;
            }
            var s = Similarity(a[i], b[i]);
            if (orMode)
            {
                if (s > best) best = s;
            }
            else
            {
                sum += s;
                n++;
            }
        }
        if (orMode) return best;
        return n == 0 ? 0 : sum / n;
    }

    private sealed class Candidate
    {
        public List<(string[] Row, double Score)> BRows = new();
        public string Type = "";
    }

    private static List<Candidate> MatchMain(TableData main, TableData secondary, MatchOptions o, out List<string[]> onlySecondary)
    {
        var mainIdx = KeyIndexes(o.MainUsed);
        var secIdx = KeyIndexes(o.SecondaryUsed);
        if (mainIdx.Count != secIdx.Count)
            throw new ArgumentException("Выберите одинаковое число ключевых колонок в обеих таблицах.");
        int keyCount = mainIdx.Count;
        onlySecondary = new List<string[]>();

        if (keyCount == 0)
        {
            onlySecondary.AddRange(secondary.Rows);
            return main.Rows.Select(_ => new Candidate()).ToList();
        }

        var token = o.Token;
        bool or = o.UseOr;
        var candidates = new List<Candidate>(main.RowCount);

        if (o.Kind == MatchKind.Exact)
        {
            if (!or)
            {
                var mainKeys = new HashSet<string>(StringComparer.Ordinal);
                foreach (var row in main.Rows)
                {
                    var parts = RowParts(row, mainIdx, keyCount, o);
                    if (parts.All(part => !string.IsNullOrEmpty(part))) mainKeys.Add(JoinKey(parts));
                }

                var bByKey = new Dictionary<string, List<string[]>>(StringComparer.Ordinal);
                foreach (var row in secondary.Rows)
                {
                    token.ThrowIfCancellationRequested();
                    var parts = RowParts(row, secIdx, keyCount, o);
                    if (parts.Any(string.IsNullOrEmpty)) continue;
                    var k = JoinKey(parts);
                    if (bByKey.TryGetValue(k, out var rows))
                        rows.Add(row);
                    else
                        bByKey[k] = new List<string[]> { row };
                }

                foreach (var row in main.Rows)
                {
                    token.ThrowIfCancellationRequested();
                    var parts = RowParts(row, mainIdx, keyCount, o);
                    if (parts.Any(string.IsNullOrEmpty))
                    {
                        candidates.Add(new Candidate());
                        continue;
                    }
                    if (bByKey.TryGetValue(JoinKey(parts), out var hit))
                    {
                        candidates.Add(new Candidate
                        {
                            Type = "точное",
                            BRows = hit.Select(row => (row, 100.0)).ToList()
                        });
                    }
                    else
                    {
                        candidates.Add(new Candidate());
                    }
                }

                foreach (var row in secondary.Rows)
                {
                    token.ThrowIfCancellationRequested();
                    var parts = RowParts(row, secIdx, keyCount, o);
                    if (parts.Any(string.IsNullOrEmpty) || !mainKeys.Contains(JoinKey(parts)))
                        onlySecondary.Add(row);
                }
            }
            else
            {
                var mainSets = new HashSet<string>[keyCount];
                for (int j = 0; j < keyCount; j++) mainSets[j] = new HashSet<string>(StringComparer.Ordinal);
                foreach (var row in main.Rows)
                {
                    var parts = RowParts(row, mainIdx, keyCount, o);
                    if (IsEmptyParts(parts)) continue;
                    for (int j = 0; j < keyCount; j++)
                        if (parts[j].Length > 0) mainSets[j].Add(parts[j]);
                }

                var bByVal = new Dictionary<string, List<int>>[keyCount];
                for (int j = 0; j < keyCount; j++) bByVal[j] = new Dictionary<string, List<int>>(StringComparer.Ordinal);
                for (int rowIndex = 0; rowIndex < secondary.Rows.Count; rowIndex++)
                {
                    token.ThrowIfCancellationRequested();
                    var parts = RowParts(secondary.Rows[rowIndex], secIdx, keyCount, o);
                    if (IsEmptyParts(parts)) continue;
                    for (int j = 0; j < keyCount; j++)
                    {
                        if (parts[j].Length == 0) continue;
                        var v = parts[j];
                        if (bByVal[j].TryGetValue(v, out var rowIndexes))
                            rowIndexes.Add(rowIndex);
                        else
                            bByVal[j][v] = new List<int> { rowIndex };
                    }
                }

                foreach (var row in main.Rows)
                {
                    token.ThrowIfCancellationRequested();
                    var parts = RowParts(row, mainIdx, keyCount, o);
                    if (IsEmptyParts(parts))
                    {
                        candidates.Add(new Candidate());
                        continue;
                    }
                    var matchedRows = new HashSet<int>();
                    for (int j = 0; j < keyCount; j++)
                    {
                        if (parts[j].Length == 0) continue;
                        if (bByVal[j].TryGetValue(parts[j], out var rowIndexes))
                            foreach (var rowIndex in rowIndexes) matchedRows.Add(rowIndex);
                    }
                    var candidate = new Candidate { Type = "точное" };
                    foreach (var rowIndex in matchedRows.OrderBy(index => index))
                        candidate.BRows.Add((secondary.Rows[rowIndex], 100.0));
                    candidates.Add(candidate);
                }

                foreach (var row in secondary.Rows)
                {
                    token.ThrowIfCancellationRequested();
                    var parts = RowParts(row, secIdx, keyCount, o);
                    if (IsEmptyParts(parts))
                    {
                        onlySecondary.Add(row);
                        continue;
                    }
                    bool matched = false;
                    for (int j = 0; j < keyCount; j++)
                    {
                        if (parts[j].Length > 0 && mainSets[j].Contains(parts[j]))
                        {
                            matched = true;
                            break;
                        }
                    }
                    if (!matched) onlySecondary.Add(row);
                }
            }
            return candidates;
        }

        // Частичное / Нечёткое: строим списки частей и сканируем строку за строкой.
        var secRows = secondary.Rows
            .Select(row => (Row: row, Parts: RowParts(row, secIdx, keyCount, o)))
            .ToList();

        var hits = new bool[secRows.Count];

        foreach (var row in main.Rows)
        {
            token.ThrowIfCancellationRequested();
            var parts = RowParts(row, mainIdx, keyCount, o);
            if (IsEmptyParts(parts) || (!or && parts.Any(string.IsNullOrEmpty)))
            {
                candidates.Add(new Candidate());
                continue;
            }

            var candidate = new Candidate { Type = o.Kind == MatchKind.Partial ? "частичное" : "нечёткое" };

            if (o.Kind == MatchKind.Partial)
            {
                for (int i = 0; i < secRows.Count; i++)
                {
                    var b = secRows[i];
                    bool ok = or ? AnyColContains(parts, b.Parts) : PartsContain(parts, b.Parts);
                    if (!ok) continue;
                    hits[i] = true;
                    candidate.BRows.Add((b.Row, 100.0));
                }
                candidates.Add(candidate);
            }
            else
            {
                double threshold = o.FuzzyThreshold / 100.0;
                for (int i = 0; i < secRows.Count; i++)
                {
                    var b = secRows[i];
                    if (!or && b.Parts.Any(string.IsNullOrEmpty)) continue;
                    double sim = ColumnSim(parts, b.Parts, or);
                    if (sim >= threshold)
                    {
                        hits[i] = true;
                        candidate.BRows.Add((b.Row, Math.Round(sim * 100, 1)));
                    }
                }
                candidates.Add(candidate);
            }
        }

        for (int i = 0; i < secRows.Count; i++)
        {
            token.ThrowIfCancellationRequested();
            if (!hits[i]) onlySecondary.Add(secRows[i].Row);
        }

        return candidates;
    }

    public static MatchResult RunAll(TableData main, TableData secondary, MatchOptions o)
    {
        var candidates = MatchMain(main, secondary, o, out var onlySecondary);
        var matches = new List<string[]>();
        var pairs = new List<MatchPair>();
        var onlyMain = new List<string[]>();

        int n = Math.Min(candidates.Count, main.RowCount);
        for (int i = 0; i < n; i++)
        {
            o.Token.ThrowIfCancellationRequested();
            var c = candidates[i];
            if (c.BRows.Count == 0)
            {
                onlyMain.Add(main.Rows[i]);
                continue;
            }
            matches.Add(main.Rows[i]);
            foreach (var matched in c.BRows)
            {
                pairs.Add(new MatchPair
                {
                    RowA = main.Rows[i],
                    RowB = matched.Row,
                    Type = c.Type,
                    Score = matched.Score,
                    BCount = c.BRows.Count
                });
            }
        }

        return new MatchResult
        {
            Matches = matches,
            Pairs = pairs,
            OnlyMain = onlyMain,
            OnlySecondary = onlySecondary
        };
    }

    public static MatchResult RunAll(TableData main, TableData secondary, bool[] mainUsed, bool[] secondaryUsed, bool ignore)
        => RunAll(main, secondary, new MatchOptions { MainUsed = mainUsed, SecondaryUsed = secondaryUsed, IgnoreCaseAndSpaces = ignore });

    public static MatchResult RunAll(TableData main, TableData secondary, bool[] mainUsed, bool[] secondaryUsed, bool ignore, bool partial)
        => RunAll(main, secondary, new MatchOptions
        {
            MainUsed = mainUsed,
            SecondaryUsed = secondaryUsed,
            IgnoreCaseAndSpaces = ignore,
            Kind = partial ? MatchKind.Partial : MatchKind.Exact
        });

    public static MergeOutput Merge(TableData main, TableData secondary, MatchOptions o, bool mergeColumnsByHeader = true)
    {
        var candidates = MatchMain(main, secondary, o, out _);
        var columns = new List<string>(main.Columns);
        var secondaryColumnMap = new int[secondary.Columns.Count];
        var commonMainColumn = Enumerable.Repeat(-1, secondary.Columns.Count).ToArray();
        var usedMainColumns = new HashSet<int>();
        var conflictingSecondaryColumns = new bool[secondary.Columns.Count];

        if (mergeColumnsByHeader)
        {
            for (int j = 0; j < secondary.Columns.Count; j++)
            {
                commonMainColumn[j] = Enumerable.Range(0, main.Columns.Count)
                    .FirstOrDefault(c => !usedMainColumns.Contains(c) &&
                        string.Equals(main.Columns[c].Trim(), secondary.Columns[j].Trim(), StringComparison.OrdinalIgnoreCase), -1);
                if (commonMainColumn[j] >= 0) usedMainColumns.Add(commonMainColumn[j]);
            }
        }

        // If same-named fields contain different non-empty values in any matched
        // pair, keep the B value in its own column instead of silently discarding it.
        for (int i = 0; i < candidates.Count && i < main.RowCount; i++)
        {
            foreach (var match in candidates[i].BRows)
            {
                for (int j = 0; j < secondary.Columns.Count; j++)
                {
                    int a = commonMainColumn[j];
                    if (a < 0) continue;
                    string av = a < main.Rows[i].Length ? main.Rows[i][a] : "";
                    string bv = j < match.Row.Length ? match.Row[j] : "";
                    if (!string.IsNullOrWhiteSpace(av) && !string.IsNullOrWhiteSpace(bv) &&
                        !string.Equals(av, bv, StringComparison.Ordinal))
                        conflictingSecondaryColumns[j] = true;
                }
            }
        }

        int AddUniqueColumn(string header)
        {
            string baseHeader = string.IsNullOrWhiteSpace(header) ? "Колонка B" : header;
            string unique = baseHeader;
            int suffix = 2;
            while (columns.Contains(unique, StringComparer.OrdinalIgnoreCase))
                unique = $"{baseHeader} ({suffix++})";
            columns.Add(unique);
            return columns.Count - 1;
        }

        for (int j = 0; j < secondary.Columns.Count; j++)
        {
            if (mergeColumnsByHeader && commonMainColumn[j] >= 0 && !conflictingSecondaryColumns[j])
            {
                secondaryColumnMap[j] = commonMainColumn[j];
                continue;
            }

            string header = secondary.Columns[j];
            if (!mergeColumnsByHeader || commonMainColumn[j] >= 0 || columns.Contains(header, StringComparer.OrdinalIgnoreCase))
                header = "B: " + header;
            secondaryColumnMap[j] = AddUniqueColumn(header);
        }

        var rows = new List<string[]>();
        for (int i = 0; i < candidates.Count && i < main.RowCount; i++)
        {
            o.Token.ThrowIfCancellationRequested();
            foreach (var match in candidates[i].BRows)
            {
                var merged = new string[columns.Count];
                Array.Fill(merged, "");
                for (int c = 0; c < main.Columns.Count; c++)
                    if (c < main.Rows[i].Length) merged[c] = main.Rows[i][c];
                for (int j = 0; j < secondaryColumnMap.Length; j++)
                {
                    string bv = j < match.Row.Length ? match.Row[j] : "";
                    int destination = secondaryColumnMap[j];
                    if (commonMainColumn[j] >= 0 && !conflictingSecondaryColumns[j])
                    {
                        if (string.IsNullOrWhiteSpace(merged[destination])) merged[destination] = bv;
                    }
                    else
                    {
                        merged[destination] = bv;
                    }
                }
                rows.Add(merged);
            }
        }

        return new MergeOutput { Columns = columns, Rows = rows, SecondaryColumnMap = secondaryColumnMap };
    }

    public static MergeOutput Merge(
        TableData main,
        TableData secondary,
        bool[] mainUsed,
        bool[] secondaryUsed,
        bool ignoreCaseAndSpaces,
        bool partial = false,
        bool mergeColumnsByHeader = true)
        => Merge(main, secondary, new MatchOptions
        {
            MainUsed = mainUsed,
            SecondaryUsed = secondaryUsed,
            IgnoreCaseAndSpaces = ignoreCaseAndSpaces,
            Kind = partial ? MatchKind.Partial : MatchKind.Exact
        }, mergeColumnsByHeader);

    public static (TableData Table, int Removed) Dedupe(TableData table, bool[] used, bool ignore, MatchOptions? template = null)
    {
        var o = template?.Clone() ?? new MatchOptions();
        var seen = new HashSet<string>(StringComparer.Ordinal);
        var rows = new List<string[]>();
        int removed = 0;
        foreach (var row in table.Rows)
        {
            o.Token.ThrowIfCancellationRequested();
            var parts = row.Select(value => value ?? "").ToArray();
            if (!seen.Add(JoinKey(parts)))
            {
                removed++;
                continue;
            }
            rows.Add(row);
        }

        return (new TableData
        {
            FilePath = table.FilePath,
            SheetName = table.SheetName,
            Columns = table.Columns,
            Rows = rows
        }, removed);
    }

    public static (int EmptyKeys, int Total) KeyCoverage(TableData table, bool[] used, MatchOptions? o = null)
    {
        var idx = KeyIndexes(used);
        int empty = 0;
        foreach (var row in table.Rows)
        {
            bool allEmpty;
            if (idx.Count == 0)
            {
                allEmpty = row.All(string.IsNullOrEmpty);
            }
            else
            {
                allEmpty = true;
                foreach (var j in idx)
                {
                    if (j >= row.Length || string.IsNullOrWhiteSpace(row[j]))
                        continue;
                    allEmpty = false;
                    break;
                }
            }
            if (allEmpty) empty++;
        }
        return (empty, table.RowCount);
    }
}

public static class ExcelExporter
{
    public static void ExportAll(string path, TableData main, TableData secondary, MatchResult result, MergeOutput? merged = null)
    {
        using var wb = new XLWorkbook();

        var wsMerged = wb.Worksheets.Add("Итог (совмещено)");
        var wsPairs = wb.Worksheets.Add("Пары A↔B");
        var wsMatches = wb.Worksheets.Add("Совпадения");
        var wsOnlyMain = wb.Worksheets.Add("Только в A");
        var wsOnlySecondary = wb.Worksheets.Add("Только в B");
        var wsAnalytics = wb.Worksheets.Add("Аналитика");

        if (merged is null || merged.Rows.Count == 0)
            merged = new MergeOutput { Columns = main.Columns, Rows = result.Matches };

        WriteSheet(wsMerged, merged.Columns, merged.Rows);

        WritePairs(wsPairs, main, secondary, result);

        WriteMatches(wsMatches, main, result);

        WriteSheet(wsOnlyMain, main.Columns, result.OnlyMain);
        WriteSheet(wsOnlySecondary, secondary.Columns, result.OnlySecondary);

        WriteAnalytics(wsAnalytics, main, secondary, result);

        wb.SaveAs(path);
    }

    private static void WriteMatches(IXLWorksheet ws, TableData main, MatchResult result)
    {
        var headers = new List<string>(main.Columns);
        bool withMeta = result.Pairs.Count > 0 || main.Columns.Count == 0;
        if (withMeta)
        {
            headers.Add("Тип");
            headers.Add("Сходство %");
            headers.Add("B-совпадений");
        }

        for (int c = 0; c < headers.Count; c++)
        {
            ws.Cell(1, c + 1).Value = headers[c];
            ws.Cell(1, c + 1).Style.Font.Bold = true;
            ws.Cell(1, c + 1).Style.Fill.BackgroundColor = XLColor.FromHtml("#DDEEFF");
        }

        int rows = result.Matches.Count;
        for (int i = 0; i < rows; i++)
        {
            var row = result.Matches[i];
            int baseCols = main.Columns.Count;
            for (int c = 0; c < baseCols; c++)
            {
                SetCell(ws, i + 2, c + 1, c < row.Length ? row[c] : "");
            }
            if (withMeta)
            {
                var rowPairs = result.Pairs.Where(p => ReferenceEquals(p.RowA, row)).ToList();
                var type = rowPairs.FirstOrDefault()?.Type ?? "";
                var score = rowPairs.Count == 0 ? 0 : rowPairs.Max(p => p.Score);
                var count = rowPairs.Count;
                SetCell(ws, i + 2, baseCols + 1, type);
                SetCell(ws, i + 2, baseCols + 2, type == "нечёткое" ? score.ToString("0.#", CultureInfo.InvariantCulture) : "");
                SetCell(ws, i + 2, baseCols + 3, count == 0 ? "" : count.ToString(CultureInfo.InvariantCulture));
            }
        }

        ws.Columns().AdjustToContents();
        if (rows == 0) ws.Row(2).Cell(1).Value = "Нет данных";
    }

    private static void WritePairs(IXLWorksheet ws, TableData main, TableData secondary, MatchResult result)
    {
        var headers = new List<string>();
        foreach (var h in main.Columns) headers.Add("A: " + h);
        headers.Add("Тип");
        headers.Add("Сходство %");
        headers.Add("B-совпадений");
        foreach (var h in secondary.Columns) headers.Add("B: " + h);

        for (int c = 0; c < headers.Count; c++)
        {
            ws.Cell(1, c + 1).Value = headers[c];
            ws.Cell(1, c + 1).Style.Font.Bold = true;
            ws.Cell(1, c + 1).Style.Fill.BackgroundColor = XLColor.FromHtml("#DDEEFF");
        }

        int aCols = main.Columns.Count;
        int bCols = secondary.Columns.Count;
        for (int i = 0; i < result.Pairs.Count; i++)
        {
            var p = result.Pairs[i];
            var rowA = p.RowA;
            var rowB = p.RowB;
            for (int c = 0; c < aCols; c++)
                SetCell(ws, i + 2, c + 1, c < rowA.Length ? rowA[c] : "");
            SetCell(ws, i + 2, aCols + 1, p.Type);
            SetCell(ws, i + 2, aCols + 2, p.Type == "нечёткое" ? p.Score.ToString("0.#", CultureInfo.InvariantCulture) : "");
            SetCell(ws, i + 2, aCols + 3, p.BCount == 0 ? "" : p.BCount.ToString(CultureInfo.InvariantCulture));
            for (int c = 0; c < bCols; c++)
                SetCell(ws, i + 2, aCols + 3 + c + 1, c < rowB.Length ? rowB[c] : "");
        }

        ws.Columns().AdjustToContents();
        if (result.Pairs.Count == 0) ws.Row(2).Cell(1).Value = "Нет данных";
    }

    private static void WriteAnalytics(IXLWorksheet ws, TableData main, TableData secondary, MatchResult result)
    {
        ws.Cell(1, 1).Value = "Показатель";
        ws.Cell(1, 2).Value = "Значение";
        ws.Cell(1, 1).Style.Font.Bold = true;
        ws.Cell(1, 2).Style.Font.Bold = true;

        int r = 2;
        void Row(string name, object value)
        {
            ws.Cell(r, 1).Value = name;
            ws.Cell(r, 2).Value = Convert.ToString(value, CultureInfo.InvariantCulture) ?? "";
            r++;
        }

        Row("Всего в A", main.RowCount);
        Row("Всего в B", secondary.RowCount);
        Row("Совпадений (строк A)", result.Matches.Count);
        Row("Только в A", result.OnlyMain.Count);
        Row("Только в B", result.OnlySecondary.Count);
        Row("Пар A↔B", result.Pairs.Count);

        var exact = result.Pairs.Count(p => p.Type == "точное");
        var partial = result.Pairs.Count(p => p.Type == "частичное");
        var fuzzy = result.Pairs.Count(p => p.Type == "нечёткое");
        Row("Из них точных", exact);
        Row("Из них частичных", partial);
        Row("Из них нечётких", fuzzy);

        var countsPerMainRow = result.Pairs.GroupBy(p => p.RowA).Select(group => group.First().BCount).ToList();
        double avg = countsPerMainRow.Count == 0 ? 0 : Math.Round(countsPerMainRow.Average(), 2);
        int maxBC = countsPerMainRow.Count == 0 ? 0 : countsPerMainRow.Max();
        Row("Среднее B-совпадений (N→M)", avg);
        Row("Максимум B-совпадений (N→M)", maxBC);

        ws.Columns().AdjustToContents();
    }

    private static void WriteSheet(IXLWorksheet ws, List<string> columns, List<string[]> rows)
    {
        int colCount = columns.Count;
        for (int c = 0; c < colCount; c++)
        {
            ws.Cell(1, c + 1).Value = columns[c];
            ws.Cell(1, c + 1).Style.Font.Bold = true;
            ws.Cell(1, c + 1).Style.Fill.BackgroundColor = XLColor.FromHtml("#DDEEFF");
        }

        for (int i = 0; i < rows.Count; i++)
        {
            var row = rows[i];
            for (int c = 0; c < row.Length && c < colCount; c++)
            {
                SetCell(ws, i + 2, c + 1, row[c]);
            }
        }

        ws.Columns().AdjustToContents();
        if (rows.Count == 0) ws.Row(2).Cell(1).Value = "Нет данных";
    }

    private static void SetCell(IXLWorksheet ws, int row, int col, string value)
    {
        var cell = ws.Cell(row, col);
        if (double.TryParse(value, NumberStyles.Float | NumberStyles.AllowExponent, CultureInfo.InvariantCulture, out var d) &&
            d.ToString(CultureInfo.InvariantCulture) == value)
            cell.Value = d;
        else
            cell.Value = value;
    }
}