Files
2026-11-17 21:48:36 +01:00

181 lines
7.3 KiB
Python

"""Import pipeline, debt matching and reporting helpers."""
import hashlib
import math
from collections import defaultdict
from datetime import date
from decimal import Decimal
from dateutil.relativedelta import relativedelta
from sqlalchemy import select
from sqlalchemy.orm import Session
from categorize import _compile, categorize
from importers import ParsedTx
from models import Account, Category, Debt, DebtPayment, Transaction
def own_ibans(db: Session) -> set[str]:
return {a.iban.replace(" ", "").upper() for a in db.scalars(select(Account)) if a.iban}
def ingest(db: Session, account: Account, parsed: list[ParsedTx], source: str = "csv",
balance: Decimal | None = None, balance_date: date | None = None) -> dict:
"""Insert parsed transactions, skipping ones already present.
The dedupe key is (account, date, amount, n-th occurrence that day) so the same transaction
imported from a CSV and from the bank API is only stored once.
"""
seen: dict[str, int] = defaultdict(int)
keyed = []
for p in sorted(parsed, key=lambda p: p.date):
base = f"{account.id}|{p.date.isoformat()}|{p.amount:.2f}|{p.currency}"
h = hashlib.sha256(f"{base}|{seen[base]}".encode()).hexdigest()
seen[base] += 1
keyed.append((h, p))
existing = set()
hashes = [h for h, _ in keyed]
for i in range(0, len(hashes), 500):
existing |= set(db.scalars(select(Transaction.hash).where(Transaction.hash.in_(hashes[i:i + 500]))))
new: list[Transaction] = []
for h, p in keyed:
if h in existing:
continue
tx = Transaction(account_id=account.id, date=p.date, amount=p.amount, currency=p.currency,
counterparty=p.counterparty[:200], counter_iban=(p.counter_iban or None),
description=p.description, raw_type=p.raw_type[:60], source=source, hash=h)
db.add(tx)
new.append(tx)
db.flush()
categorize(db, new, own_ibans(db))
matched = match_debt_payments(db, new)
if balance is not None and (account.balance_date is None or (balance_date and balance_date >= account.balance_date)):
account.balance, account.balance_date = balance, balance_date
db.commit()
return {"inserted": len(new), "duplicates": len(keyed) - len(new), "debt_payments": matched}
# ---------------------------------------------------------------- debts
def match_debt_payments(db: Session, txs: list[Transaction]) -> int:
debts = [d for d in db.scalars(select(Debt).where(Debt.closed.is_(False))) if d.match_pattern]
if not debts or not txs:
return 0
debt_cat = db.scalar(select(Category).where(Category.name == "Debt"))
linked = set(db.scalars(select(DebtPayment.transaction_id).where(DebtPayment.transaction_id.is_not(None))))
n = 0
for tx in txs:
if tx.id in linked:
continue
for d in debts:
if tx.date < d.start_date:
continue
if (d.direction == "i_owe") != (tx.amount < 0):
continue
if not _compile(d.match_pattern).search(f"{tx.counterparty} {tx.description} {tx.counter_iban or ''}"):
continue
db.add(DebtPayment(debt_id=d.id, date=tx.date, amount=abs(tx.amount), transaction_id=tx.id,
notes="auto-matched"))
tx.debt_id = d.id
if debt_cat and not tx.category_locked and d.direction == "i_owe":
tx.category_id = debt_cat.id
n += 1
break
db.flush()
return n
def _due_dates(d: Debt):
first = d.start_date + relativedelta(months=1)
if d.due_day:
first = first.replace(day=1) + relativedelta(day=d.due_day) # clamps to month end
i = 0
while True:
dd = first + relativedelta(months=i)
if d.due_day:
dd = dd.replace(day=1) + relativedelta(day=d.due_day)
yield dd
i += 1
def debt_status(d: Debt, today: date | None = None) -> dict:
today = today or date.today()
paid = sum((p.amount for p in d.payments), Decimal("0"))
remaining = max(d.principal - paid, Decimal("0"))
out = {"paid": paid, "remaining": remaining, "pct": float(paid / d.principal * 100) if d.principal else 100.0,
"expected": None, "diff": None, "missed": 0, "next_due": None, "payoff": None, "status": "open",
"last_payment": d.payments[-1].date if d.payments else None}
if remaining <= 0 or d.closed:
out["status"] = "paid off"
return out
if d.monthly_payment and d.monthly_payment > 0:
n_due = 0
for dd in _due_dates(d):
if dd > today:
out["next_due"] = dd
break
n_due += 1
if n_due * d.monthly_payment >= d.principal:
break
expected = min(n_due * d.monthly_payment, d.principal)
diff = paid - expected
out["expected"], out["diff"] = expected, diff
months_left = math.ceil(remaining / d.monthly_payment)
out["payoff"] = today + relativedelta(months=int(months_left))
half = d.monthly_payment / 2
if diff <= -half:
out["status"] = "behind"
out["missed"] = math.ceil(-diff / d.monthly_payment)
elif diff >= half:
out["status"] = "ahead"
else:
out["status"] = "on track"
return out
# ---------------------------------------------------------------- reporting
def _spend_rows(db: Session, start: date, end: date, account_id: int | None = None):
q = (select(Transaction, Category, Account)
.join(Account, Transaction.account_id == Account.id)
.outerjoin(Category, Transaction.category_id == Category.id)
.where(Transaction.date >= start, Transaction.date < end, Transaction.currency == "EUR"))
if account_id:
q = q.where(Transaction.account_id == account_id)
for tx, cat, acc in db.execute(q):
if cat is not None and cat.kind == "transfer":
continue
share = Decimal(acc.share_pct) / 100
yield tx, cat, tx.amount * share
def monthly_totals(db: Session, months: int = 12, account_id: int | None = None) -> list[dict]:
first = date.today().replace(day=1) - relativedelta(months=months - 1)
buckets = {(first + relativedelta(months=i)).strftime("%Y-%m"): {"income": Decimal(0), "expense": Decimal(0)}
for i in range(months)}
for tx, cat, amt in _spend_rows(db, first, date.today() + relativedelta(days=1), account_id):
b = buckets.get(tx.date.strftime("%Y-%m"))
if b is None:
continue
if amt >= 0:
b["income"] += amt
else:
b["expense"] += -amt
return [{"month": k, **v} for k, v in buckets.items()]
def category_totals(db: Session, start: date, end: date, account_id: int | None = None) -> list[dict]:
totals: dict[str, dict] = {}
for tx, cat, amt in _spend_rows(db, start, end, account_id):
if amt >= 0:
continue
name = cat.name if cat else "Uncategorised"
t = totals.setdefault(name, {"name": name, "color": cat.color if cat else "#cccccc",
"total": Decimal(0), "count": 0, "id": cat.id if cat else None})
t["total"] += -amt
t["count"] += 1
return sorted(totals.values(), key=lambda x: x["total"], reverse=True)