ingtrakapp/lib/services/ing_csv_import.dart

/// Parse ING Netherlands (and similar) bank CSV exports into [BankTransaction]s.
library;

import '../models/bank.dart';
import '../models/transaction_meta.dart';

/// Result of importing rows against an existing transaction list.
class CsvImportResult {
  const CsvImportResult({
    required this.parsed,
    required this.added,
    required this.skippedDuplicates,
    this.parseErrors = const [],
  });

  final int parsed;
  final List<BankTransaction> added;
  final int skippedDuplicates;
  final List<String> parseErrors;
}

/// True when [a] and [b] likely represent the same bank movement.
///
/// Matches on booking date + amount, then description / merchant fingerprint
/// so API rows and CSV rows can still dedupe when wording differs slightly.
bool isSameTransaction(BankTransaction a, BankTransaction b) {
  if (a.date != b.date) return false;
  if (a.currency.toUpperCase() != b.currency.toUpperCase()) return false;
  if (_amountCents(a.amount) != _amountCents(b.amount)) return false;
  return _descriptionsLikelyMatch(a, b);
}

/// True when a cached/API [pending] row is the unsettled form of [booked].
///
/// Banks often change booking date (and sometimes wording) when a payment
/// settles, so this allows a small date window unlike [isSameTransaction].
bool isPendingSettledBy(
  BankTransaction pending,
  BankTransaction booked, {
  int maxDayGap = 10,
}) {
  if (!pending.pending || booked.pending) return false;
  if (pending.isManual || booked.isManual) return false;
  if (pending.currency.toUpperCase() != booked.currency.toUpperCase()) {
    return false;
  }
  if (_amountCents(pending.amount) != _amountCents(booked.amount)) return false;
  if (!_descriptionsLikelyMatch(pending, booked)) return false;
  final gap = _dateDiffDays(pending.date, booked.date);
  if (gap == null) return false;
  return gap.abs() <= maxDayGap;
}

bool _descriptionsLikelyMatch(BankTransaction a, BankTransaction b) {
  if (a.descriptionKey == b.descriptionKey) return true;

  final ma = a.merchantKey;
  final mb = b.merchantKey;
  if (ma.isNotEmpty && mb.isNotEmpty) {
    if (ma == mb) return true;
    if (ma.contains(mb) || mb.contains(ma)) return true;
  }

  // CSV often puts the short name in description and details in mededelingen;
  // API may embed everything. Compare stripped merchant against full key.
  if (ma.isNotEmpty && b.descriptionKey.contains(ma)) return true;
  if (mb.isNotEmpty && a.descriptionKey.contains(mb)) return true;

  return false;
}

int _amountCents(double amount) => (amount * 100).round();

int? _dateDiffDays(String a, String b) {
  final da = DateTime.tryParse(a);
  final db = DateTime.tryParse(b);
  if (da == null || db == null) return null;
  return da.difference(db).inDays;
}

/// Coarse bucket so fuzzy [isSameTransaction] only compares likely peers.
String _dedupeBucket(BankTransaction tx) =>
    '${tx.date}|${tx.currency.toUpperCase()}|${_amountCents(tx.amount)}';

String _amountCurrencyBucket(BankTransaction tx) =>
    '${tx.currency.toUpperCase()}|${_amountCents(tx.amount)}';

/// Drop pending rows that already have a booked twin (same money + merchant).
List<BankTransaction> dropSettledPendingDuplicates(
  List<BankTransaction> txs,
) {
  final booked = [for (final tx in txs) if (!tx.pending) tx];
  if (booked.isEmpty) return txs;

  final bookedBuckets = <String, List<BankTransaction>>{};
  for (final tx in booked) {
    bookedBuckets.putIfAbsent(_amountCurrencyBucket(tx), () => []).add(tx);
  }

  final out = <BankTransaction>[];
  for (final tx in txs) {
    if (!tx.pending || tx.isManual) {
      out.add(tx);
      continue;
    }
    final peers = bookedBuckets[_amountCurrencyBucket(tx)];
    final settled =
        peers != null && peers.any((b) => isPendingSettledBy(tx, b));
    if (!settled) out.add(tx);
  }
  return out;
}

/// Merge a fresh bank API page with locally cached history.
///
/// Pending rows are ephemeral: only the current API list may keep them
/// (except admin manuals). That stops settled/cancelled pendings from
/// living forever in the on-device cache after the bank drops them.
List<BankTransaction> mergeApiTransactionsWithCache(
  List<BankTransaction> apiTxs,
  List<BankTransaction> cached,
) {
  final history = [
    for (final tx in cached)
      if (!tx.pending || tx.isManual) tx,
  ];
  return dropSettledPendingDuplicates(mergeTransactions(apiTxs, history));
}

/// Merge [extra] into [existing], skipping duplicates. Newest date first.
List<BankTransaction> mergeTransactions(
  List<BankTransaction> existing,
  List<BankTransaction> extra,
) {
  if (extra.isEmpty) return existing;
  final buckets = <String, List<BankTransaction>>{};
  for (final tx in existing) {
    buckets.putIfAbsent(_dedupeBucket(tx), () => []).add(tx);
  }

  final out = List<BankTransaction>.of(existing);
  for (final tx in extra) {
    final key = _dedupeBucket(tx);
    final peers = buckets[key];
    if (peers != null && peers.any((e) => isSameTransaction(e, tx))) {
      continue;
    }
    out.add(tx);
    buckets.putIfAbsent(key, () => []).add(tx);
  }
  out.sort((a, b) {
    final byDate = b.date.compareTo(a.date);
    if (byDate != 0) return byDate;
    return a.description.compareTo(b.description);
  });
  return out;
}

/// Keep only rows from [candidates] that are not already in [existing].
List<BankTransaction> filterNewTransactions(
  List<BankTransaction> existing,
  List<BankTransaction> candidates,
) {
  if (candidates.isEmpty) return const [];
  if (existing.isEmpty) return List<BankTransaction>.of(candidates);

  final buckets = <String, List<BankTransaction>>{};
  for (final tx in existing) {
    buckets.putIfAbsent(_dedupeBucket(tx), () => []).add(tx);
  }

  return [
    for (final tx in candidates)
      if (!(buckets[_dedupeBucket(tx)]
              ?.any((e) => isSameTransaction(e, tx)) ??
          false))
        tx,
  ];
}

/// Parse an ING (NL/EN) CSV/semicolon export.
///
/// Expected columns (Dutch or English):
/// Date, Name/Description, Account, Counterparty, Code, Debit/Credit (Af/Bij),
/// Amount, Transaction type, Notifications, …
List<BankTransaction> parseIngCsv(String raw) {
  final text = raw.replaceFirst(RegExp(r'^\uFEFF'), '').trim();
  if (text.isEmpty) return const [];

  final lines = text.split(RegExp(r'\r?\n'));
  if (lines.isEmpty) return const [];

  final headerLine = lines.first;
  final delimiter = _detectDelimiter(headerLine);
  final headers = _parseCsvLine(headerLine, delimiter)
      .map(_normalizeHeader)
      .toList(growable: false);

  final dateIdx = _indexOfAny(headers, const [
    'datum',
    'date',
  ]);
  final nameIdx = _indexOfAny(headers, const [
    'naam / omschrijving',
    'naam/omschrijving',
    'name / description',
    'name/description',
    'omschrijving',
    'description',
  ]);
  final afBijIdx = _indexOfAny(headers, const [
    'af bij',
    'af/bij',
    'debit/credit',
    'debit / credit',
  ]);
  final amountIdx = _indexOfAny(headers, const [
    'bedrag (eur)',
    'bedrag',
    'amount (eur)',
    'amount',
  ]);
  final notesIdx = _indexOfAny(headers, const [
    'mededelingen',
    'notifications',
    'notification',
  ]);
  final currencyIdx = _indexOfAny(headers, const [
    'valuta',
    'currency',
  ]);

  if (dateIdx < 0 || amountIdx < 0) {
    throw const FormatException(
      'CSV needs at least Date/Datum and Amount/Bedrag columns '
      '(ING Netherlands export).',
    );
  }

  final out = <BankTransaction>[];
  for (var i = 1; i < lines.length; i++) {
    final line = lines[i].trim();
    if (line.isEmpty) continue;
    final cols = _parseCsvLine(line, delimiter);
    if (cols.length <= amountIdx || cols.length <= dateIdx) continue;

    final date = _parseIngDate(cols[dateIdx]);
    if (date == null) continue;

    final amountRaw = _parseIngAmount(cols[amountIdx]);
    if (amountRaw == null) continue;

    final afBij = afBijIdx >= 0 && afBijIdx < cols.length
        ? cols[afBijIdx].trim().toLowerCase()
        : '';
    final signed = _applyDebitCredit(amountRaw, afBij);

    final name = nameIdx >= 0 && nameIdx < cols.length ? cols[nameIdx].trim() : '';
    final notes =
        notesIdx >= 0 && notesIdx < cols.length ? cols[notesIdx].trim() : '';
    final description = [
      if (name.isNotEmpty) name,
      if (notes.isNotEmpty) notes,
    ].join(' · ');
    if (description.isEmpty) continue;

    final currency = currencyIdx >= 0 && currencyIdx < cols.length
        ? cols[currencyIdx].trim().toUpperCase()
        : 'EUR';

    out.add(
      BankTransaction(
        date: date,
        description: description,
        amount: signed,
        currency: currency.isEmpty ? 'EUR' : currency,
        pending: false,
      ),
    );
  }
  return out;
}

String _normalizeHeader(String value) {
  return value
      .trim()
      .toLowerCase()
      .replaceAll('"', '')
      .replaceAll(RegExp(r'\s+'), ' ');
}

int _indexOfAny(List<String> headers, List<String> candidates) {
  for (final candidate in candidates) {
    final index = headers.indexOf(candidate);
    if (index >= 0) return index;
  }
  return -1;
}

String _detectDelimiter(String headerLine) {
  final semis = ';'.allMatches(headerLine).length;
  final commas = ','.allMatches(headerLine).length;
  return semis > commas ? ';' : ',';
}

List<String> _parseCsvLine(String line, String delimiter) {
  final out = <String>[];
  final buffer = StringBuffer();
  var inQuotes = false;

  for (var i = 0; i < line.length; i++) {
    final ch = line[i];
    if (ch == '"') {
      if (inQuotes && i + 1 < line.length && line[i + 1] == '"') {
        buffer.write('"');
        i++;
      } else {
        inQuotes = !inQuotes;
      }
      continue;
    }
    if (ch == delimiter && !inQuotes) {
      out.add(buffer.toString());
      buffer.clear();
      continue;
    }
    buffer.write(ch);
  }
  out.add(buffer.toString());
  return out;
}

/// Accepts `YYYYMMDD`, `YYYY-MM-DD`, `DD-MM-YYYY`, `DD/MM/YYYY`.
String? _parseIngDate(String raw) {
  final value = raw.trim().replaceAll('"', '');
  if (value.isEmpty) return null;

  final yyyymmdd = RegExp(r'^(\d{4})(\d{2})(\d{2})$').firstMatch(value);
  if (yyyymmdd != null) {
    return '${yyyymmdd[1]}-${yyyymmdd[2]}-${yyyymmdd[3]}';
  }

  final iso = RegExp(r'^(\d{4})-(\d{2})-(\d{2})').firstMatch(value);
  if (iso != null) {
    return '${iso[1]}-${iso[2]}-${iso[3]}';
  }

  final dmy = RegExp(r'^(\d{1,2})[-/](\d{1,2})[-/](\d{4})$').firstMatch(value);
  if (dmy != null) {
    final d = dmy[1]!.padLeft(2, '0');
    final m = dmy[2]!.padLeft(2, '0');
    return '${dmy[3]}-$m-$d';
  }

  final parsed = DateTime.tryParse(value);
  if (parsed == null) return null;
  final m = parsed.month.toString().padLeft(2, '0');
  final d = parsed.day.toString().padLeft(2, '0');
  return '${parsed.year}-$m-$d';
}

double? _parseIngAmount(String raw) {
  var value = raw.trim().replaceAll('"', '').replaceAll('\u00a0', '');
  if (value.isEmpty) return null;
  // European: 1.234,56 or 12,34 — strip thousands dots when comma present.
  if (value.contains(',') && value.contains('.')) {
    value = value.replaceAll('.', '').replaceAll(',', '.');
  } else if (value.contains(',')) {
    value = value.replaceAll(',', '.');
  }
  return double.tryParse(value);
}

double _applyDebitCredit(double amount, String afBij) {
  final abs = amount.abs();
  if (afBij == 'af' || afBij == 'debit' || afBij == 'd') return -abs;
  if (afBij == 'bij' || afBij == 'credit' || afBij == 'c') return abs;
  // Already signed amounts (some exports).
  return amount;
}

/// Convenience: parse + keep only rows not already present.
CsvImportResult importIngCsvAgainst({
  required String raw,
  required List<BankTransaction> existing,
}) {
  final parsed = parseIngCsv(raw);
  final added = filterNewTransactions(existing, parsed);
  return CsvImportResult(
    parsed: parsed.length,
    added: added,
    skippedDuplicates: parsed.length - added.length,
  );
}

/// Expose merchant helper for tests without re-export noise.
String debugMerchantKey(String description) =>
    merchantKeyFromDescription(description);