"""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)