# Bank Reconciliation — Django Implementation Guide

Practical implementation notes for extending a two-source Coop↔Uniconta reconciliation webapp with bank CSV support.

## Model extensions

Add these fields to `Transaction` (one migration):
```python
account_number = models.CharField(max_length=50, blank=True, db_index=True)
bank_text = models.TextField(blank=True)
valuta_date = models.DateField(null=True, blank=True)
```

Also extend `SourceFile.source` choices and `Transaction.source`/`match_type` choices to include `("bank", "Bank")` and `("bank_total", "Bank-samlet")`.

## Bank CSV parser

Key implementation in `apps/import_/parsers.py`:

- **Encodings**: Try `latin-1` first, then `utf-8`. Most Danish bank exports use latin-1.
- **Delimiter**: `;` (semicolon)
- **Header mapping**: Fuzzy-match by lowercasing, stripping null bytes and quotes:
  - `dato` → date column
  - `valør`/`valuta` → value date column
  - `tekst`/`beskrivelse` → text column
  - `bel` (prefix) → amount column
  - `saldo` → balance column
  - `reference`/`bilagsrefer` → reference column
- **Date parse**: `DD.MM.YYYY` → split by dots → `datetime(int(parts[2]), int(parts[1]), int(parts[0])).date()`
- **Amount parse**:
  1. Strip `\xa0` (nbsp), `\u202f` (narrow nbsp), `" DKK"`
  2. If `.` and `,` both present → remove dots, replace comma with dot
  3. If only comma → replace with dot
  4. Convert to `Decimal`
- **Bulk create**: Use `Transaction.objects.bulk_create(txns_to_create)` for performance
- **Account extraction**: Regex `r"(\d{8,})"` on text field to find kundenummer/kontonummer

## Bank ↔ Coop match (many-to-one)

Group bank payments by `(account_number, year, month)` and match against the sum of unmatched Coop transactions in the same bucket.

```python
bank_txns = Transaction.objects.filter(source="bank", year=year).values(
    "account_number", "year", "month"
).annotate(total=Sum("amount"), count=Count("id"))

for bt in bank_txns:
    coop_sum = Transaction.objects.filter(
        source="coop", year=bt["year"], month=bt["month"],
        match_status="unmatched", account_number=bt["account_number"],
    ).aggregate(s=Sum("amount"))["s"]

    if coop_sum is not None and abs(bt["total"] - coop_sum) <= TOLERANCE:
        # Create MatchGroup linking bank bucket to Coop bucket
        ...
```

**Note**: Bank amounts are positive (indbetalinger), Coop RE/Bankoverførsel amounts are negative. Compare absolute values. Set `match_type="bank_total"`.

## Views/templates changes

- **Dashboard**: Add `bank_count`, `bank_sum` cards; add `bank_count` / `bank_sum` columns to monthly overview table
- **Upload form**: Add `<option value="bank">Bank (Kontobevaegelser .csv)</option>`
- **Reconciliation list**: Add `bank` to source filter; render bank badge with `.badge-warning`
- **Run match page**: Document match phases: Reference → ZV↔Faktura → RE/Betaling → General → Many-to-one → Bank-total

## Discovered pitfalls

1. **Migrations not detected**: If `makemigrations` reports "No changes detected", check whether a prior manual migration already captured the changes. Run `migrate --check` first.
2. **Docker file path mismatch**: When testing via `docker compose exec`, container filesystem may differ from host. Use the web UI upload form for file import testing.
3. **Bank text homogeneity**: All transactions from the same counterparty often have identical text (e.g. `"Coop  0010014808 -SE MEDD."`). Text similarity is useless for matching. Rely on `account_number` + date+amount grouping.
4. **Bank negatives are rare but critical**: Negative bank amounts (~1-2%) represent bonus/modregning/retur. Do NOT match these against positive Coop invoices. Flag separately for bonus reconciliation.
5. **Bank total may differ slightly from Coop sum**: Normal difference is ±0.1% (fees, rounding). Use tolerance matching (e.g. ±5 DKK) for bank-total matches.