feat: Planungs- und Szenario-API mit Projektion und Vorschlaegen

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

finance/app/main.py

@@ -5,7 +5,8 @@ from fastapi.responses import HTMLResponse, RedirectResponse
 
 from app import auth
 from app.config import get_settings
-from app.routers import accounts, categories, imports, transactions
+from app.routers import (accounts, categories, imports, planning, scenarios,
+                         transactions)
 
 app = FastAPI(title="Finanzberatungs-Tool")
 
@@ -13,6 +14,8 @@ app.include_router(accounts.router)
 app.include_router(transactions.router)
 app.include_router(categories.router)
 app.include_router(imports.router)
+app.include_router(planning.router)
+app.include_router(scenarios.router)
 
 
 @app.get("/login", response_class=HTMLResponse)

finance/app/routers/planning.py

@@ -0,0 +1,243 @@
+from datetime import date
+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.engine.loans import loan_schedule
+from app.models.tables import Category, Loan, PlannedItem, RecurringItem
+from app.services.suggestions import suggest_recurring
+
+router = APIRouter(prefix="/api", tags=["planning"],
+                   dependencies=[Depends(require_auth)])
+
+
+# ---------------------------------------------------------------- Recurring
+class RecurringIn(BaseModel):
+    name: str
+    amount: Decimal
+    rhythm: str
+    due_day: int
+    start_date: date | None = None
+    end_date: date | None = None
+    category_id: int | None = None
+
+
+class RecurringOut(RecurringIn):
+    model_config = ConfigDict(from_attributes=True)
+    id: int
+
+
+class RecurringPatch(BaseModel):
+    name: str | None = None
+    amount: Decimal | None = None
+    rhythm: str | None = None
+    due_day: int | None = None
+    start_date: date | None = None
+    end_date: date | None = None
+    category_id: int | None = None
+
+
+class SuggestionOut(BaseModel):
+    name: str
+    amount: Decimal
+    rhythm: str
+    due_day: int
+    category_id: int | None = None
+
+
+def _check_category(session: Session, category_id: int | None) -> None:
+    if category_id is not None and session.get(Category, category_id) is None:
+        raise HTTPException(404, "Kategorie nicht gefunden")
+
+
+@router.get("/recurring", response_model=list[RecurringOut])
+def list_recurring(session: Session = Depends(get_session)):
+    return [RecurringOut.model_validate(r)
+            for r in session.execute(select(RecurringItem)).scalars()]
+
+
+@router.post("/recurring", response_model=RecurringOut, status_code=201)
+def create_recurring(data: RecurringIn, session: Session = Depends(get_session)):
+    _check_category(session, data.category_id)
+    item = RecurringItem(**data.model_dump())
+    session.add(item)
+    session.commit()
+    session.refresh(item)
+    return RecurringOut.model_validate(item)
+
+
+# Muss vor /recurring/{item_id} stehen, sonst faengt die {id}-Route den Pfad ab.
+@router.get("/recurring/suggestions", response_model=list[SuggestionOut])
+def recurring_suggestions(session: Session = Depends(get_session)):
+    return suggest_recurring(session)
+
+
+@router.patch("/recurring/{item_id}", response_model=RecurringOut)
+def patch_recurring(item_id: int, data: RecurringPatch,
+                    session: Session = Depends(get_session)):
+    item = session.get(RecurringItem, item_id)
+    if item is None:
+        raise HTTPException(404, "Fixposten nicht gefunden")
+    fields = data.model_dump(exclude_unset=True)
+    if "category_id" in fields:
+        _check_category(session, fields["category_id"])
+    for key, value in fields.items():
+        setattr(item, key, value)
+    session.commit()
+    session.refresh(item)
+    return RecurringOut.model_validate(item)
+
+
+@router.delete("/recurring/{item_id}", status_code=204)
+def delete_recurring(item_id: int, session: Session = Depends(get_session)):
+    item = session.get(RecurringItem, item_id)
+    if item is None:
+        raise HTTPException(404, "Fixposten nicht gefunden")
+    session.delete(item)
+    session.commit()
+
+
+# ------------------------------------------------------------------ Planned
+class PlannedIn(BaseModel):
+    name: str
+    amount: Decimal
+    due: date
+    category_id: int | None = None
+
+
+class PlannedOut(PlannedIn):
+    model_config = ConfigDict(from_attributes=True)
+    id: int
+
+
+class PlannedPatch(BaseModel):
+    name: str | None = None
+    amount: Decimal | None = None
+    due: date | None = None
+    category_id: int | None = None
+
+
+@router.get("/planned", response_model=list[PlannedOut])
+def list_planned(session: Session = Depends(get_session)):
+    return [PlannedOut.model_validate(p)
+            for p in session.execute(select(PlannedItem)).scalars()]
+
+
+@router.post("/planned", response_model=PlannedOut, status_code=201)
+def create_planned(data: PlannedIn, session: Session = Depends(get_session)):
+    _check_category(session, data.category_id)
+    item = PlannedItem(**data.model_dump())
+    session.add(item)
+    session.commit()
+    session.refresh(item)
+    return PlannedOut.model_validate(item)
+
+
+@router.patch("/planned/{item_id}", response_model=PlannedOut)
+def patch_planned(item_id: int, data: PlannedPatch,
+                  session: Session = Depends(get_session)):
+    item = session.get(PlannedItem, item_id)
+    if item is None:
+        raise HTTPException(404, "Einmalposten nicht gefunden")
+    fields = data.model_dump(exclude_unset=True)
+    if "category_id" in fields:
+        _check_category(session, fields["category_id"])
+    for key, value in fields.items():
+        setattr(item, key, value)
+    session.commit()
+    session.refresh(item)
+    return PlannedOut.model_validate(item)
+
+
+@router.delete("/planned/{item_id}", status_code=204)
+def delete_planned(item_id: int, session: Session = Depends(get_session)):
+    item = session.get(PlannedItem, item_id)
+    if item is None:
+        raise HTTPException(404, "Einmalposten nicht gefunden")
+    session.delete(item)
+    session.commit()
+
+
+# --------------------------------------------------------------------- Loans
+class LoanIn(BaseModel):
+    name: str
+    principal: Decimal
+    annual_rate_pct: Decimal
+    term_months: int
+    payout_date: date
+    repayment_type: str = "annuity"
+
+
+class LoanOut(LoanIn):
+    model_config = ConfigDict(from_attributes=True)
+    id: int
+
+
+class LoanPatch(BaseModel):
+    name: str | None = None
+    principal: Decimal | None = None
+    annual_rate_pct: Decimal | None = None
+    term_months: int | None = None
+    payout_date: date | None = None
+    repayment_type: str | None = None
+
+
+class InstallmentOut(BaseModel):
+    due: date
+    payment: Decimal
+    interest: Decimal
+    principal: Decimal
+    remaining: Decimal
+
+
+@router.get("/loans", response_model=list[LoanOut])
+def list_loans(session: Session = Depends(get_session)):
+    return [LoanOut.model_validate(l)
+            for l in session.execute(select(Loan)).scalars()]
+
+
+@router.post("/loans", response_model=LoanOut, status_code=201)
+def create_loan(data: LoanIn, session: Session = Depends(get_session)):
+    loan = Loan(**data.model_dump())
+    session.add(loan)
+    session.commit()
+    session.refresh(loan)
+    return LoanOut.model_validate(loan)
+
+
+@router.patch("/loans/{loan_id}", response_model=LoanOut)
+def patch_loan(loan_id: int, data: LoanPatch, session: Session = Depends(get_session)):
+    loan = session.get(Loan, loan_id)
+    if loan is None:
+        raise HTTPException(404, "Kredit nicht gefunden")
+    for key, value in data.model_dump(exclude_unset=True).items():
+        setattr(loan, key, value)
+    session.commit()
+    session.refresh(loan)
+    return LoanOut.model_validate(loan)
+
+
+@router.delete("/loans/{loan_id}", status_code=204)
+def delete_loan(loan_id: int, session: Session = Depends(get_session)):
+    loan = session.get(Loan, loan_id)
+    if loan is None:
+        raise HTTPException(404, "Kredit nicht gefunden")
+    session.delete(loan)
+    session.commit()
+
+
+@router.get("/loans/{loan_id}/schedule", response_model=list[InstallmentOut])
+def get_loan_schedule(loan_id: int, session: Session = Depends(get_session)):
+    loan = session.get(Loan, loan_id)
+    if loan is None:
+        raise HTTPException(404, "Kredit nicht gefunden")
+    plan = loan_schedule(Decimal(loan.principal), Decimal(loan.annual_rate_pct),
+                         loan.term_months, loan.payout_date, loan.repayment_type)
+    return [InstallmentOut(due=i.due, payment=i.payment, interest=i.interest,
+                           principal=i.principal, remaining=i.remaining)
+           for i in plan]

finance/app/routers/scenarios.py

@@ -0,0 +1,169 @@
+from datetime import date
+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.config import get_settings
+from app.db import get_session
+from app.models.tables import (Loan, ProjectionPoint, Scenario, ScenarioLoan,
+                               ScenarioModifier)
+from app.services.projection_service import run_projection
+
+router = APIRouter(prefix="/api/scenarios", tags=["scenarios"],
+                   dependencies=[Depends(require_auth)])
+
+
+class ScenarioIn(BaseModel):
+    name: str
+    description: str = ""
+    include_recurring: bool = True
+    include_planned: bool = True
+
+
+class ScenarioOut(ScenarioIn):
+    model_config = ConfigDict(from_attributes=True)
+    id: int
+
+
+class ScenarioPatch(BaseModel):
+    name: str | None = None
+    description: str | None = None
+    include_recurring: bool | None = None
+    include_planned: bool | None = None
+
+
+class ModifierIn(BaseModel):
+    target_type: str
+    target_id: int
+    kind: str
+    value: Decimal = Decimal("0")
+
+
+class ModifierOut(ModifierIn):
+    model_config = ConfigDict(from_attributes=True)
+    id: int
+    scenario_id: int
+
+
+class SeriesPointOut(BaseModel):
+    day: date
+    balance: Decimal
+
+
+class ProjectionOut(BaseModel):
+    low_point_date: date
+    low_point_balance: Decimal
+    below_zero_date: date | None
+    below_threshold_date: date | None
+    series: list[SeriesPointOut]
+
+
+def _get_scenario(session: Session, scenario_id: int) -> Scenario:
+    scenario = session.get(Scenario, scenario_id)
+    if scenario is None:
+        raise HTTPException(404, "Szenario nicht gefunden")
+    return scenario
+
+
+@router.get("", response_model=list[ScenarioOut])
+def list_scenarios(session: Session = Depends(get_session)):
+    return [ScenarioOut.model_validate(s)
+            for s in session.execute(select(Scenario)).scalars()]
+
+
+@router.post("", response_model=ScenarioOut, status_code=201)
+def create_scenario(data: ScenarioIn, session: Session = Depends(get_session)):
+    if session.execute(select(Scenario).where(Scenario.name == data.name)).scalar():
+        raise HTTPException(409, "Szenario existiert bereits")
+    scenario = Scenario(**data.model_dump())
+    session.add(scenario)
+    session.commit()
+    session.refresh(scenario)
+    return ScenarioOut.model_validate(scenario)
+
+
+@router.patch("/{scenario_id}", response_model=ScenarioOut)
+def patch_scenario(scenario_id: int, data: ScenarioPatch,
+                   session: Session = Depends(get_session)):
+    scenario = _get_scenario(session, scenario_id)
+    for key, value in data.model_dump(exclude_unset=True).items():
+        setattr(scenario, key, value)
+    session.commit()
+    session.refresh(scenario)
+    return ScenarioOut.model_validate(scenario)
+
+
+@router.delete("/{scenario_id}", status_code=204)
+def delete_scenario(scenario_id: int, session: Session = Depends(get_session)):
+    scenario = _get_scenario(session, scenario_id)
+    session.delete(scenario)
+    session.commit()
+
+
+@router.post("/{scenario_id}/loans/{loan_id}", status_code=204)
+def add_scenario_loan(scenario_id: int, loan_id: int,
+                      session: Session = Depends(get_session)):
+    _get_scenario(session, scenario_id)
+    if session.get(Loan, loan_id) is None:
+        raise HTTPException(404, "Kredit nicht gefunden")
+    if session.get(ScenarioLoan, (scenario_id, loan_id)) is None:
+        session.add(ScenarioLoan(scenario_id=scenario_id, loan_id=loan_id))
+        session.commit()
+
+
+@router.delete("/{scenario_id}/loans/{loan_id}", status_code=204)
+def remove_scenario_loan(scenario_id: int, loan_id: int,
+                         session: Session = Depends(get_session)):
+    _get_scenario(session, scenario_id)
+    link = session.get(ScenarioLoan, (scenario_id, loan_id))
+    if link is not None:
+        session.delete(link)
+        session.commit()
+
+
+@router.post("/{scenario_id}/modifiers", response_model=ModifierOut, status_code=201)
+def add_modifier(scenario_id: int, data: ModifierIn,
+                 session: Session = Depends(get_session)):
+    _get_scenario(session, scenario_id)
+    modifier = ScenarioModifier(scenario_id=scenario_id, **data.model_dump())
+    session.add(modifier)
+    session.commit()
+    session.refresh(modifier)
+    return ModifierOut.model_validate(modifier)
+
+
+@router.delete("/{scenario_id}/modifiers/{mod_id}", status_code=204)
+def delete_modifier(scenario_id: int, mod_id: int,
+                    session: Session = Depends(get_session)):
+    _get_scenario(session, scenario_id)
+    modifier = session.get(ScenarioModifier, mod_id)
+    if modifier is None or modifier.scenario_id != scenario_id:
+        raise HTTPException(404, "Modifikator nicht gefunden")
+    session.delete(modifier)
+    session.commit()
+
+
+@router.post("/{scenario_id}/project", response_model=ProjectionOut)
+def project_scenario(scenario_id: int, horizon_days: int | None = None,
+                     start_date: date | None = None,
+                     session: Session = Depends(get_session)):
+    scenario = _get_scenario(session, scenario_id)
+    settings = get_settings()
+    resolved_horizon = horizon_days if horizon_days is not None else settings.horizon_days
+    resolved_start = start_date if start_date is not None else date.today()
+    result = run_projection(session, scenario, resolved_horizon, resolved_start)
+    points = session.execute(
+        select(ProjectionPoint).where(ProjectionPoint.scenario_id == scenario_id)
+        .order_by(ProjectionPoint.day)
+    ).scalars().all()
+    return ProjectionOut(
+        low_point_date=result.low_point_date,
+        low_point_balance=result.low_point_balance,
+        below_zero_date=result.below_zero_date,
+        below_threshold_date=result.below_threshold_date,
+        series=[SeriesPointOut(day=p.day, balance=p.balance) for p in points],
+    )

finance/app/services/projection_service.py

@@ -0,0 +1,58 @@
+from datetime import date, timedelta
+from datetime import datetime, timezone
+from decimal import Decimal
+
+from sqlalchemy import delete, select
+from sqlalchemy.orm import Session
+
+from app.config import get_settings
+from app.engine.loans import loan_schedule
+from app.engine.projection import project
+from app.engine.scenario import (PlainModifier, PlainPlanned, PlainRecurring,
+                                 build_cashflows)
+from app.models.tables import (Loan, PlannedItem, ProjectionPoint,
+                               ProjectionResult, RecurringItem, Scenario,
+                               ScenarioLoan, ScenarioModifier)
+from app.services.balances import total_balance
+
+
+def run_projection(session: Session, scenario: Scenario, horizon_days: int,
+                   start_date: date) -> ProjectionResult:
+    end = start_date + timedelta(days=horizon_days)
+    recurring = []
+    if scenario.include_recurring:
+        recurring = [PlainRecurring(r.id, r.name, Decimal(r.amount), r.rhythm,
+                                    r.due_day, r.start_date, r.end_date, r.category_id)
+                     for r in session.execute(select(RecurringItem)).scalars()]
+    planned = []
+    if scenario.include_planned:
+        planned = [PlainPlanned(p.name, Decimal(p.amount), p.due, p.category_id)
+                   for p in session.execute(select(PlannedItem)).scalars()]
+    loans = session.execute(
+        select(Loan).join(ScenarioLoan, ScenarioLoan.loan_id == Loan.id)
+        .where(ScenarioLoan.scenario_id == scenario.id)).scalars().all()
+    schedules = [loan_schedule(Decimal(l.principal), Decimal(l.annual_rate_pct),
+                               l.term_months, l.payout_date, l.repayment_type)
+                 for l in loans]
+    payouts = [(l.payout_date, Decimal(l.principal)) for l in loans]
+    modifiers = [PlainModifier(m.target_type, m.target_id, m.kind, Decimal(m.value))
+                 for m in session.execute(select(ScenarioModifier).where(
+                     ScenarioModifier.scenario_id == scenario.id)).scalars()]
+    flows = build_cashflows(recurring, planned, schedules, payouts, modifiers,
+                            start_date, end)
+    proj = project(total_balance(session), start_date, flows, horizon_days,
+                   threshold=get_settings().warn_threshold)
+    session.execute(delete(ProjectionPoint).where(
+        ProjectionPoint.scenario_id == scenario.id))
+    for d, bal in proj.series:
+        session.add(ProjectionPoint(scenario_id=scenario.id, day=d, balance=bal))
+    result = session.get(ProjectionResult, scenario.id) or ProjectionResult(
+        scenario_id=scenario.id, computed_at=datetime.now(timezone.utc),
+        low_point_date=proj.low_point[0], low_point_balance=proj.low_point[1])
+    result.computed_at = datetime.now(timezone.utc)
+    result.low_point_date, result.low_point_balance = proj.low_point
+    result.below_zero_date = proj.first_below_zero
+    result.below_threshold_date = proj.first_below_threshold
+    session.add(result)
+    session.commit()
+    return result

finance/app/services/suggestions.py

@@ -0,0 +1,52 @@
+import statistics
+from collections import Counter, defaultdict
+from decimal import Decimal
+
+from sqlalchemy import select
+from sqlalchemy.orm import Session
+
+from app.models.tables import RecurringItem, Transaction
+
+
+def _max_consecutive_months(months: list[tuple[int, int]]) -> int:
+    if not months:
+        return 0
+    best = current = 1
+    for prev, cur in zip(months, months[1:]):
+        prev_idx = prev[0] * 12 + prev[1]
+        cur_idx = cur[0] * 12 + cur[1]
+        current = current + 1 if cur_idx == prev_idx + 1 else 1
+        best = max(best, current)
+    return best
+
+
+def suggest_recurring(session: Session) -> list[dict]:
+    txs = session.execute(
+        select(Transaction).where(Transaction.status == "confirmed")
+    ).scalars().all()
+    groups: dict[tuple, list[Transaction]] = defaultdict(list)
+    for t in txs:
+        groups[(t.account_id, t.counterparty, t.amount)].append(t)
+
+    existing = {(r.name, Decimal(r.amount))
+               for r in session.execute(select(RecurringItem)).scalars()}
+
+    suggestions: list[dict] = []
+    for (_account_id, counterparty, amount), items in groups.items():
+        months = sorted({(t.booking_date.year, t.booking_date.month) for t in items})
+        if _max_consecutive_months(months) < 3:
+            continue
+        name = counterparty
+        if (name, Decimal(amount)) in existing:
+            continue
+        due_day = int(statistics.median(sorted(t.booking_date.day for t in items)))
+        cat_counts = Counter(t.category_id for t in items if t.category_id is not None)
+        category_id = cat_counts.most_common(1)[0][0] if cat_counts else None
+        suggestions.append({
+            "name": name,
+            "amount": Decimal(amount),
+            "rhythm": "monthly",
+            "due_day": due_day,
+            "category_id": category_id,
+        })
+    return suggestions

finance/tests/test_planning_api.py

@@ -0,0 +1,47 @@
+from datetime import date
+from decimal import Decimal
+
+from app.models.tables import Account, Statement
+
+H = {"Authorization": "Bearer test-key"}
+
+
+def _seed_balance(db, amount="1000.00"):
+    acc = Account(bank="dkb", iban="DE01", name="G", type="giro")
+    db.add(acc)
+    db.flush()
+    db.add(Statement(filename="s.pdf", bank="dkb", account_id=acc.id,
+                     period_start=date(2026, 6, 1), period_end=date(2026, 6, 30),
+                     opening_balance=Decimal("0"), closing_balance=Decimal(amount),
+                     status="confirmed"))
+    db.commit()
+
+
+def test_scenario_projection(client, db):
+    _seed_balance(db)
+    client.post("/api/recurring", headers=H, json={
+        "name": "Miete", "amount": "-600.00", "rhythm": "monthly", "due_day": 1})
+    sc = client.post("/api/scenarios", headers=H, json={"name": "Basis"}).json()
+    r = client.post(f"/api/scenarios/{sc['id']}/project", headers=H,
+                    params={"horizon_days": 92, "start_date": "2026-07-15"})
+    assert r.status_code == 200
+    body = r.json()
+    # 3 Mietzahlungen (01.08., 01.09., 01.10.): 400 -> -200 -> -800
+    assert Decimal(body["low_point_balance"]) == Decimal("-800.00")
+    assert body["below_zero_date"] == "2026-09-01"
+    assert len(body["series"]) == 92
+
+
+def test_loan_in_scenario_keeps_balance_positive(client, db):
+    _seed_balance(db)
+    client.post("/api/recurring", headers=H, json={
+        "name": "Miete", "amount": "-600.00", "rhythm": "monthly", "due_day": 1})
+    loan = client.post("/api/loans", headers=H, json={
+        "name": "K1", "principal": "5000.00", "annual_rate_pct": "6.0",
+        "term_months": 48, "payout_date": "2026-07-20",
+        "repayment_type": "annuity"}).json()
+    sc = client.post("/api/scenarios", headers=H, json={"name": "Kredit"}).json()
+    client.post(f"/api/scenarios/{sc['id']}/loans/{loan['id']}", headers=H)
+    body = client.post(f"/api/scenarios/{sc['id']}/project", headers=H,
+                       params={"horizon_days": 92, "start_date": "2026-07-15"}).json()
+    assert Decimal(body["low_point_balance"]) > Decimal("0")