feat: CSV-Import ueber bestehende Pipeline mit dreiwertiger Saldo-Pruefung

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
2026-07-19 21:45:53 +02:00
parent e2d713e960
commit ea4858c4de
7 changed files with 354 additions and 50 deletions
--- a/finance/tests/test_gui.py
+++ b/finance/tests/test_gui.py
@@ -62,6 +62,41 @@ def test_import_list_fragment_renders_after_login(client):
    assert "Noch keine Imports" in r.text


+def test_preview_fragment_shows_balance_ok_none_for_format_without_saldo(
+        client, tmp_path, monkeypatch):
+    # UX-Regel: Saldo-Status ist IMMER sichtbar, auch wenn das Format (hier
+    # HVB-CSV) keine Saldodaten liefert - dreiwertig statt faelschlich "rot".
+    monkeypatch.setenv("FB_INBOX_DIR", str(tmp_path / "inbox"))
+    monkeypatch.setenv("FB_UPLOADS_DIR", str(tmp_path / "uploads"))
+    from app.config import get_settings
+    get_settings.cache_clear()
+    from tests.test_import_api import _hvb_csv_bytes
+    sid = client.post("/api/imports/upload", headers=H,
+                      files={"file": ("HVB_gui.csv", _hvb_csv_bytes(), "text/csv")}
+                      ).json()["id"]
+
+    client.post("/login", data={"username": "admin", "password": "geheim"})
+    r = client.get(f"/import/{sid}/preview-fragment")
+    assert r.status_code == 200
+    assert "Saldo-Prüfung: nicht verfügbar (Format ohne Saldodaten)" in r.text
+
+
+def test_preview_fragment_shows_balance_ok_true_for_vr_csv(client, tmp_path, monkeypatch):
+    monkeypatch.setenv("FB_INBOX_DIR", str(tmp_path / "inbox"))
+    monkeypatch.setenv("FB_UPLOADS_DIR", str(tmp_path / "uploads"))
+    from app.config import get_settings
+    get_settings.cache_clear()
+    from tests.test_import_api import _vr_csv_bytes
+    sid = client.post("/api/imports/upload", headers=H,
+                      files={"file": ("VR_gui.csv", _vr_csv_bytes(), "text/csv")}
+                      ).json()["id"]
+
+    client.post("/login", data={"username": "admin", "password": "geheim"})
+    r = client.get(f"/import/{sid}/preview-fragment")
+    assert r.status_code == 200
+    assert "Saldo plausibel" in r.text
+
+
 def test_version_visible(client):
    from pathlib import Path
    version = (Path(__file__).resolve().parent.parent / "VERSION").read_text().strip()
--- a/finance/tests/test_import_api.py
+++ b/finance/tests/test_import_api.py
@@ -1,3 +1,5 @@
+import csv
+import io
 import re
 from datetime import date
 from decimal import Decimal
@@ -295,3 +297,184 @@ def test_upload_corrupt_pdf_produces_error_statement_not_500(client, tmp_path, m
    # Datei bleibt im Posteingang liegen, wird nicht ins Uploads-Verzeichnis verschoben.
    assert (tmp_path / "inbox" / "kaputt.pdf").exists()
    assert not (tmp_path / "uploads" / "kaputt.pdf").exists()
+
+
+# ---------------------------------------------------------------------------
+# CSV-Import (Ausbaustufe 3, Task 3): rein SYNTHETISCHE Mini-CSVs, nach der
+# in docs/superpowers/plans/2026-07-19-ausbaustufe-3.md dokumentierten
+# Formatreferenz konstruiert. KEINE echten Kontodaten.
+# ---------------------------------------------------------------------------
+
+def _dkb_csv_bytes(kontostand_date="30.06.2026", kontostand_amount="1.000,00 €") -> bytes:
+    buf = io.StringIO()
+    w = csv.writer(buf, delimiter=";", quoting=csv.QUOTE_ALL)
+    w.writerow(["Girokonto", "DE12500105170001234567"])
+    w.writerow(["Zeitraum:", "01.06.2026 - 30.06.2026"])
+    w.writerow([f"Kontostand vom {kontostand_date}:", kontostand_amount])
+    w.writerow([])
+    w.writerow(["Buchungsdatum", "Wertstellung", "Status", "Zahlungspflichtige*r",
+               "Zahlungsempfänger*in", "Verwendungszweck", "Umsatztyp", "IBAN",
+               "Betrag (€)", "Gläubiger-ID", "Mandatsreferenz", "Kundenreferenz"])
+    # Datei ist absteigend sortiert (neueste Buchung zuerst).
+    w.writerow(["15.06.26", "15.06.26", "Gebucht", "Max Mustermann", "Beispiel GmbH",
+               "Testzweck Miete", "Lastschrift", "DE12500105170001234567",
+               "-50,00", "", "", ""])
+    w.writerow(["10.06.26", "10.06.26", "Storniert", "Max Mustermann", "Nicht Gebucht GmbH",
+               "sollte ignoriert werden", "Lastschrift", "DE12500105170001234567",
+               "-999,00", "", "", ""])
+    w.writerow(["01.06.26", "01.06.26", "Gebucht", "Arbeitgeber XY", "Max Mustermann",
+               "Testzweck Gehalt", "Gutschrift", "DE12500105170001234567",
+               "1500,00", "", "", ""])
+    return ("" + buf.getvalue()).encode("utf-8")
+
+
+def _vr_csv_bytes() -> bytes:
+    buf = io.StringIO()
+    w = csv.writer(buf, delimiter=";")
+    w.writerow(["Bezeichnung Auftragskonto", "IBAN Auftragskonto", "BIC Auftragskonto",
+               "Bankname Auftragskonto", "Buchungstag", "Valutadatum",
+               "Name Zahlungsbeteiligter", "IBAN Zahlungsbeteiligter",
+               "BIC (SWIFT-Code) Zahlungsbeteiligter", "Buchungstext", "Verwendungszweck",
+               "Betrag", "Waehrung", "Saldo nach Buchung", "Bemerkung",
+               "Gekennzeichneter Umsatz", "Glaeubiger ID", "Mandatsreferenz"])
+    # Datei ist absteigend sortiert (neueste Buchung zuerst).
+    w.writerow(["Girokonto", "DE02600501010000123456", "SOLADEST600", "VR Bank Test",
+               "15.06.2026", "15.06.2026", "Test Empfaenger", "DE00000000000000000000",
+               "", "Ueberweisung", "Testzweck 2", "100,00", "EUR", "1.100,00", "", "", "", ""])
+    w.writerow(["Girokonto", "DE02600501010000123456", "SOLADEST600", "VR Bank Test",
+               "01.06.2026", "01.06.2026", "Test Arbeitgeber", "DE00000000000000000000",
+               "", "Gutschrift", "Testzweck 1", "1000,00", "EUR", "1.000,00", "", "", "", ""])
+    text = ("" + buf.getvalue()).replace("\n", "\r\n")
+    return text.encode("utf-8")
+
+
+def _hvb_csv_bytes(kontonummer="1234567") -> bytes:
+    buf = io.StringIO()
+    w = csv.writer(buf, delimiter=";")
+    w.writerow(["Kontonummer", "Buchungsdatum", "Valuta", "Verwendungszweck", "Betrag",
+               "Waehrung"])
+    # Datei ist aufsteigend sortiert (aelteste Buchung zuerst).
+    w.writerow([kontonummer, "01.06.2026", "01.06.2026", "Testzweck A", "500,00", "EUR"])
+    w.writerow([kontonummer, "15.06.2026", "15.06.2026", "Testzweck B", "-20,00", "EUR"])
+    return buf.getvalue().encode("utf-16")
+
+
+def _upload_csv(client, name: str, content: bytes):
+    return client.post("/api/imports/upload", headers=H,
+                       files={"file": (name, content, "text/csv")})
+
+
+def test_upload_csv_vr_balance_ok_true_and_confirm(client, tmp_path, monkeypatch):
+    monkeypatch.setenv("FB_INBOX_DIR", str(tmp_path / "inbox"))
+    monkeypatch.setenv("FB_UPLOADS_DIR", str(tmp_path / "uploads"))
+    from app.config import get_settings
+    get_settings.cache_clear()
+    r = _upload_csv(client, "VR_test.csv", _vr_csv_bytes())
+    assert r.status_code == 201
+    body = r.json()
+    assert body["status"] == "draft"
+    assert body["bank"] == "vr_csv"
+    sid = body["id"]
+    prev = client.get(f"/api/imports/{sid}/preview", headers=H).json()
+    assert prev["balance_ok"] is True
+    assert len(prev["transactions"]) == 2
+    assert client.post(f"/api/imports/{sid}/confirm", headers=H).status_code == 200
+    accounts = client.get("/api/accounts", headers=H).json()
+    acc = next(a for a in accounts if a["iban"] == "DE02600501010000123456")
+    assert acc["anchor_balance"] == "1100.00"
+    assert acc["anchor_date"] == "2026-06-15"
+
+
+def test_upload_csv_hvb_balance_ok_none(client, tmp_path, monkeypatch):
+    monkeypatch.setenv("FB_INBOX_DIR", str(tmp_path / "inbox"))
+    monkeypatch.setenv("FB_UPLOADS_DIR", str(tmp_path / "uploads"))
+    from app.config import get_settings
+    get_settings.cache_clear()
+    r = _upload_csv(client, "HVB_test.csv", _hvb_csv_bytes())
+    assert r.status_code == 201
+    body = r.json()
+    assert body["status"] == "draft"
+    assert body["bank"] == "hvb_csv"
+    sid = body["id"]
+    prev = client.get(f"/api/imports/{sid}/preview", headers=H).json()
+    assert prev["balance_ok"] is None
+    assert len(prev["transactions"]) == 2
+    assert client.post(f"/api/imports/{sid}/confirm", headers=H).status_code == 200
+    # HVB traegt die Kontonummer als iban im Konto (dokumentierte Einschraenkung).
+    accounts = client.get("/api/accounts", headers=H).json()
+    acc = next(a for a in accounts if a["iban"] == "1234567")
+    assert acc["anchor_date"] is None  # HVB liefert keinen Anker
+
+
+def test_upload_csv_dkb_balance_ok_none_and_sets_account_anchor(client, tmp_path, monkeypatch):
+    monkeypatch.setenv("FB_INBOX_DIR", str(tmp_path / "inbox"))
+    monkeypatch.setenv("FB_UPLOADS_DIR", str(tmp_path / "uploads"))
+    from app.config import get_settings
+    get_settings.cache_clear()
+    r = _upload_csv(client, "DKB_test.csv", _dkb_csv_bytes())
+    assert r.status_code == 201
+    body = r.json()
+    assert body["status"] == "draft"
+    assert body["bank"] == "dkb_csv"
+    sid = body["id"]
+    prev = client.get(f"/api/imports/{sid}/preview", headers=H).json()
+    assert prev["balance_ok"] is None
+    # Storno-Zeile ("Storniert") darf nicht importiert werden.
+    assert len(prev["transactions"]) == 2
+    assert client.post(f"/api/imports/{sid}/confirm", headers=H).status_code == 200
+    accounts = client.get("/api/accounts", headers=H).json()
+    acc = next(a for a in accounts if a["iban"] == "DE12500105170001234567")
+    assert acc["anchor_date"] == "2026-06-30"
+    assert acc["anchor_balance"] == "1000.00"
+
+
+def test_upload_csv_dkb_does_not_downgrade_newer_manual_anchor(client, db, tmp_path, monkeypatch):
+    monkeypatch.setenv("FB_INBOX_DIR", str(tmp_path / "inbox"))
+    monkeypatch.setenv("FB_UPLOADS_DIR", str(tmp_path / "uploads"))
+    from app.config import get_settings
+    get_settings.cache_clear()
+    from app.models.tables import Account
+    # Konto existiert bereits mit einem manuellen Anker NACH dem CSV-Kontostand-Datum
+    # (30.06.2026) - dieser neuere, manuell gesetzte Anker darf durch den CSV-Import
+    # NICHT ueberschrieben/zurueckdatiert werden.
+    acc = Account(bank="dkb_csv", iban="DE12500105170001234567", name="DKB Giro",
+                 type="giro", anchor_date=date(2026, 7, 10), anchor_balance=Decimal("42.00"))
+    db.add(acc)
+    db.commit()
+
+    r = _upload_csv(client, "DKB_test.csv", _dkb_csv_bytes())
+    assert r.status_code == 201
+    sid = r.json()["id"]
+    assert client.post(f"/api/imports/{sid}/confirm", headers=H).status_code == 200
+
+    accounts = client.get("/api/accounts", headers=H).json()
+    acc_after = next(a for a in accounts if a["iban"] == "DE12500105170001234567")
+    assert acc_after["anchor_date"] == "2026-07-10"
+    assert acc_after["anchor_balance"] == "42.00"
+
+
+def test_upload_rejects_non_pdf_non_csv(client, tmp_path, monkeypatch):
+    monkeypatch.setenv("FB_INBOX_DIR", str(tmp_path / "inbox"))
+    monkeypatch.setenv("FB_UPLOADS_DIR", str(tmp_path / "uploads"))
+    from app.config import get_settings
+    get_settings.cache_clear()
+    r = client.post("/api/imports/upload", headers=H,
+                    files={"file": ("x.txt", b"not a csv or pdf", "text/plain")})
+    assert r.status_code == 400
+    assert "PDF" in r.text and "CSV" in r.text
+
+
+def test_scan_inbox_picks_up_csv(client, tmp_path, monkeypatch):
+    inbox = tmp_path / "inbox"
+    monkeypatch.setenv("FB_INBOX_DIR", str(inbox))
+    monkeypatch.setenv("FB_UPLOADS_DIR", str(tmp_path / "uploads"))
+    from app.config import get_settings
+    get_settings.cache_clear()
+    inbox.mkdir(parents=True)
+    (inbox / "VR_scan.csv").write_bytes(_vr_csv_bytes())
+    r = client.post("/api/imports/scan-inbox", headers=H)
+    assert r.status_code == 200
+    results = r.json()
+    assert len(results) == 1
+    assert results[0]["bank"] == "vr_csv"
+    assert results[0]["status"] == "draft"