feat: CRUD-API Konten/Buchungen/Kategorien/Regeln

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

finance/app/main.py

@@ -1,13 +1,18 @@
 import hmac
 
-from fastapi import Depends, FastAPI, Form, Request
+from fastapi import FastAPI, Form
 from fastapi.responses import HTMLResponse, RedirectResponse
 
 from app import auth
 from app.config import get_settings
+from app.routers import accounts, categories, transactions
 
 app = FastAPI(title="Finanzberatungs-Tool")
 
+app.include_router(accounts.router)
+app.include_router(transactions.router)
+app.include_router(categories.router)
+
 
 @app.get("/login", response_class=HTMLResponse)
 def login_form():
@@ -34,8 +39,3 @@ def logout():
     resp = RedirectResponse("/login", status_code=303)
     resp.delete_cookie(auth.COOKIE)
     return resp
-
-
-@app.get("/api/accounts", dependencies=[Depends(auth.require_auth)])
-def _placeholder_accounts():  # wird in Task 9 durch Router ersetzt
-    return []

finance/app/routers/accounts.py

@@ -0,0 +1,58 @@
+from decimal import Decimal
+
+from fastapi import APIRouter, Depends, HTTPException
+from pydantic import BaseModel, ConfigDict
+from sqlalchemy import select
+from sqlalchemy.orm import Session
+
+from app.auth import require_auth
+from app.db import get_session
+from app.models.tables import Account
+from app.services.balances import account_balance
+
+router = APIRouter(prefix="/api/accounts", tags=["accounts"],
+                   dependencies=[Depends(require_auth)])
+
+
+class AccountIn(BaseModel):
+    bank: str
+    iban: str
+    name: str
+    type: str = "giro"
+
+
+class AccountOut(AccountIn):
+    model_config = ConfigDict(from_attributes=True)
+    id: int
+    balance: Decimal = Decimal("0")
+
+
+@router.get("", response_model=list[AccountOut])
+def list_accounts(session: Session = Depends(get_session)):
+    out = []
+    for acc in session.execute(select(Account)).scalars():
+        item = AccountOut.model_validate(acc)
+        item.balance = account_balance(session, acc)
+        out.append(item)
+    return out
+
+
+@router.get("/{account_id}", response_model=AccountOut)
+def get_account(account_id: int, session: Session = Depends(get_session)):
+    acc = session.get(Account, account_id)
+    if acc is None:
+        raise HTTPException(404, "Konto nicht gefunden")
+    item = AccountOut.model_validate(acc)
+    item.balance = account_balance(session, acc)
+    return item
+
+
+@router.post("", response_model=AccountOut, status_code=201)
+def create_account(data: AccountIn, session: Session = Depends(get_session)):
+    if session.execute(select(Account).where(Account.iban == data.iban)).scalar():
+        raise HTTPException(409, "IBAN existiert bereits")
+    acc = Account(**data.model_dump())
+    session.add(acc)
+    session.commit()
+    session.refresh(acc)
+    return AccountOut.model_validate(acc)

finance/app/routers/categories.py

@@ -0,0 +1,75 @@
+from fastapi import APIRouter, Depends, HTTPException
+from pydantic import BaseModel, ConfigDict
+from sqlalchemy import select
+from sqlalchemy.orm import Session
+
+from app.auth import require_auth
+from app.db import get_session
+from app.models.tables import Category, CategoryRule
+
+router = APIRouter(prefix="/api", tags=["categories"],
+                   dependencies=[Depends(require_auth)])
+
+
+class CategoryIn(BaseModel):
+    name: str
+
+
+class CategoryOut(CategoryIn):
+    model_config = ConfigDict(from_attributes=True)
+    id: int
+
+
+class CategoryRuleIn(BaseModel):
+    pattern: str
+    category_id: int
+    priority: int = 100
+
+
+class CategoryRuleOut(CategoryRuleIn):
+    model_config = ConfigDict(from_attributes=True)
+    id: int
+
+
+@router.get("/categories", response_model=list[CategoryOut])
+def list_categories(session: Session = Depends(get_session)):
+    return [CategoryOut.model_validate(c)
+            for c in session.execute(select(Category)).scalars()]
+
+
+@router.post("/categories", response_model=CategoryOut, status_code=201)
+def create_category(data: CategoryIn, session: Session = Depends(get_session)):
+    if session.execute(select(Category).where(Category.name == data.name)).scalar():
+        raise HTTPException(409, "Kategorie existiert bereits")
+    cat = Category(**data.model_dump())
+    session.add(cat)
+    session.commit()
+    session.refresh(cat)
+    return CategoryOut.model_validate(cat)
+
+
+@router.get("/category-rules", response_model=list[CategoryRuleOut])
+def list_category_rules(session: Session = Depends(get_session)):
+    rules = session.execute(
+        select(CategoryRule).order_by(CategoryRule.priority)).scalars()
+    return [CategoryRuleOut.model_validate(r) for r in rules]
+
+
+@router.post("/category-rules", response_model=CategoryRuleOut, status_code=201)
+def create_category_rule(data: CategoryRuleIn, session: Session = Depends(get_session)):
+    if session.get(Category, data.category_id) is None:
+        raise HTTPException(404, "Kategorie nicht gefunden")
+    rule = CategoryRule(**data.model_dump())
+    session.add(rule)
+    session.commit()
+    session.refresh(rule)
+    return CategoryRuleOut.model_validate(rule)
+
+
+@router.delete("/category-rules/{rule_id}", status_code=204)
+def delete_category_rule(rule_id: int, session: Session = Depends(get_session)):
+    rule = session.get(CategoryRule, rule_id)
+    if rule is None:
+        raise HTTPException(404, "Regel nicht gefunden")
+    session.delete(rule)
+    session.commit()

finance/app/routers/transactions.py

@@ -0,0 +1,116 @@
+from datetime import date
+from decimal import Decimal
+
+from fastapi import APIRouter, Depends, HTTPException
+from pydantic import BaseModel, ConfigDict
+from sqlalchemy import or_, select
+from sqlalchemy.orm import Session
+
+from app.auth import require_auth
+from app.db import get_session
+from app.models.tables import Transaction
+from app.parsers.validate import dedup_hash
+from app.services.categorize import apply_rules
+
+router = APIRouter(prefix="/api/transactions", tags=["transactions"],
+                   dependencies=[Depends(require_auth)])
+
+
+class TransactionIn(BaseModel):
+    account_id: int
+    booking_date: date
+    value_date: date | None = None
+    amount: Decimal
+    purpose: str = ""
+    counterparty: str = ""
+    force: bool = False
+
+
+class TransactionOut(BaseModel):
+    model_config = ConfigDict(from_attributes=True)
+    id: int
+    account_id: int
+    statement_id: int | None = None
+    booking_date: date
+    value_date: date | None = None
+    amount: Decimal
+    purpose: str
+    counterparty: str
+    category_id: int | None = None
+    status: str
+    dedup_hash: str
+    is_duplicate: bool
+
+
+class TransactionPatch(BaseModel):
+    category_id: int | None = None
+
+
+@router.get("", response_model=list[TransactionOut])
+def list_transactions(
+    account_id: int | None = None,
+    date_from: date | None = None,
+    date_to: date | None = None,
+    category_id: int | None = None,
+    q: str | None = None,
+    status: str = "confirmed",
+    limit: int = 200,
+    offset: int = 0,
+    session: Session = Depends(get_session),
+):
+    stmt = select(Transaction)
+    if status:
+        stmt = stmt.where(Transaction.status == status)
+    if account_id is not None:
+        stmt = stmt.where(Transaction.account_id == account_id)
+    if date_from is not None:
+        stmt = stmt.where(Transaction.booking_date >= date_from)
+    if date_to is not None:
+        stmt = stmt.where(Transaction.booking_date <= date_to)
+    if category_id is not None:
+        stmt = stmt.where(Transaction.category_id == category_id)
+    if q:
+        like = f"%{q}%"
+        stmt = stmt.where(or_(Transaction.purpose.ilike(like),
+                              Transaction.counterparty.ilike(like)))
+    stmt = stmt.order_by(Transaction.booking_date, Transaction.id).offset(offset).limit(limit)
+    return [TransactionOut.model_validate(t) for t in session.execute(stmt).scalars()]
+
+
+@router.post("", response_model=TransactionOut, status_code=201)
+def create_transaction(data: TransactionIn, session: Session = Depends(get_session)):
+    h = dedup_hash(data.account_id, data.booking_date, data.amount, data.purpose)
+    existing = session.execute(
+        select(Transaction).where(Transaction.dedup_hash == h)).scalar()
+    if existing is not None and not data.force:
+        raise HTTPException(409, "Buchung existiert bereits (Duplikat)")
+    tx = Transaction(
+        account_id=data.account_id,
+        booking_date=data.booking_date,
+        value_date=data.value_date,
+        amount=data.amount,
+        purpose=data.purpose,
+        counterparty=data.counterparty,
+        status="confirmed",
+        dedup_hash=h,
+        is_duplicate=existing is not None,
+    )
+    session.add(tx)
+    session.commit()
+    session.refresh(tx)
+    apply_rules(session, [tx])
+    session.commit()
+    session.refresh(tx)
+    return TransactionOut.model_validate(tx)
+
+
+@router.patch("/{tx_id}", response_model=TransactionOut)
+def patch_transaction(tx_id: int, data: TransactionPatch,
+                      session: Session = Depends(get_session)):
+    tx = session.get(Transaction, tx_id)
+    if tx is None:
+        raise HTTPException(404, "Buchung nicht gefunden")
+    tx.category_id = data.category_id
+    session.commit()
+    session.refresh(tx)
+    return TransactionOut.model_validate(tx)

finance/app/services/balances.py

@@ -0,0 +1,26 @@
+from decimal import Decimal
+
+from sqlalchemy import func, select
+from sqlalchemy.orm import Session
+
+from app.models.tables import Account, Statement, Transaction
+
+
+def account_balance(session: Session, account: Account) -> Decimal:
+    stmt = session.execute(
+        select(Statement).where(Statement.account_id == account.id,
+                                Statement.status == "confirmed")
+        .order_by(Statement.period_end.desc())).scalars().first()
+    q = select(func.coalesce(func.sum(Transaction.amount), 0)).where(
+        Transaction.account_id == account.id, Transaction.status == "confirmed")
+    if stmt is not None:
+        q = q.where(Transaction.booking_date > stmt.period_end)
+        base = stmt.closing_balance
+    else:
+        base = Decimal("0")
+    return Decimal(base) + Decimal(session.execute(q).scalar_one())
+
+
+def total_balance(session: Session) -> Decimal:
+    accounts = session.execute(select(Account)).scalars().all()
+    return sum((account_balance(session, a) for a in accounts), Decimal("0"))

finance/app/services/categorize.py

@@ -0,0 +1,20 @@
+from sqlalchemy import select
+from sqlalchemy.orm import Session
+
+from app.models.tables import CategoryRule, Transaction
+
+
+def apply_rules(session: Session, transactions: list[Transaction]) -> int:
+    rules = session.execute(
+        select(CategoryRule).order_by(CategoryRule.priority)).scalars().all()
+    hits = 0
+    for tx in transactions:
+        if tx.category_id is not None:
+            continue
+        haystack = f"{tx.purpose} {tx.counterparty}".lower()
+        for rule in rules:
+            if rule.pattern.lower() in haystack:
+                tx.category_id = rule.category_id
+                hits += 1
+                break
+    return hits