feat: Datenmodell (SQLAlchemy) und Alembic-Migration

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

finance/app/models/__init__.py

@@ -0,0 +1 @@
+from app.models import tables  # noqa: F401

finance/app/models/tables.py

@@ -0,0 +1,137 @@
+from datetime import date, datetime
+from decimal import Decimal
+
+from sqlalchemy import Boolean, Date, DateTime, ForeignKey, Integer, Numeric, String, Text
+from sqlalchemy.orm import Mapped, mapped_column
+
+from app.db import Base
+
+MONEY = Numeric(12, 2)
+
+
+class Account(Base):
+    __tablename__ = "accounts"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    bank: Mapped[str] = mapped_column(String(50))
+    iban: Mapped[str] = mapped_column(String(34), unique=True)
+    name: Mapped[str] = mapped_column(String(100))
+    type: Mapped[str] = mapped_column(String(20), default="giro")
+
+
+class Category(Base):
+    __tablename__ = "categories"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    name: Mapped[str] = mapped_column(String(100), unique=True)
+
+
+class CategoryRule(Base):
+    __tablename__ = "category_rules"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    pattern: Mapped[str] = mapped_column(String(200))
+    category_id: Mapped[int] = mapped_column(ForeignKey("categories.id"))
+    priority: Mapped[int] = mapped_column(Integer, default=100)
+
+
+class Statement(Base):
+    __tablename__ = "statements"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    filename: Mapped[str] = mapped_column(String(255))
+    bank: Mapped[str] = mapped_column(String(50))
+    account_id: Mapped[int | None] = mapped_column(ForeignKey("accounts.id"), nullable=True)
+    period_start: Mapped[date | None] = mapped_column(Date, nullable=True)
+    period_end: Mapped[date | None] = mapped_column(Date, nullable=True)
+    opening_balance: Mapped[Decimal | None] = mapped_column(MONEY, nullable=True)
+    closing_balance: Mapped[Decimal | None] = mapped_column(MONEY, nullable=True)
+    status: Mapped[str] = mapped_column(String(20), default="draft")
+    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
+
+
+class Transaction(Base):
+    __tablename__ = "transactions"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    account_id: Mapped[int] = mapped_column(ForeignKey("accounts.id"))
+    statement_id: Mapped[int | None] = mapped_column(ForeignKey("statements.id"), nullable=True)
+    booking_date: Mapped[date] = mapped_column(Date)
+    value_date: Mapped[date | None] = mapped_column(Date, nullable=True)
+    amount: Mapped[Decimal] = mapped_column(MONEY)
+    purpose: Mapped[str] = mapped_column(Text, default="")
+    counterparty: Mapped[str] = mapped_column(String(200), default="")
+    category_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id"), nullable=True)
+    status: Mapped[str] = mapped_column(String(20), default="draft")
+    dedup_hash: Mapped[str] = mapped_column(String(64), index=True)
+    is_duplicate: Mapped[bool] = mapped_column(Boolean, default=False)
+
+
+class RecurringItem(Base):
+    __tablename__ = "recurring_items"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    name: Mapped[str] = mapped_column(String(200))
+    amount: Mapped[Decimal] = mapped_column(MONEY)
+    rhythm: Mapped[str] = mapped_column(String(20))
+    due_day: Mapped[int] = mapped_column(Integer)
+    start_date: Mapped[date | None] = mapped_column(Date, nullable=True)
+    end_date: Mapped[date | None] = mapped_column(Date, nullable=True)
+    category_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id"), nullable=True)
+
+
+class PlannedItem(Base):
+    __tablename__ = "planned_items"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    name: Mapped[str] = mapped_column(String(200))
+    amount: Mapped[Decimal] = mapped_column(MONEY)
+    due: Mapped[date] = mapped_column(Date)
+    category_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id"), nullable=True)
+
+
+class Loan(Base):
+    __tablename__ = "loans"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    name: Mapped[str] = mapped_column(String(200))
+    principal: Mapped[Decimal] = mapped_column(MONEY)
+    annual_rate_pct: Mapped[Decimal] = mapped_column(Numeric(5, 2))
+    term_months: Mapped[int] = mapped_column(Integer)
+    payout_date: Mapped[date] = mapped_column(Date)
+    repayment_type: Mapped[str] = mapped_column(String(20), default="annuity")
+
+
+class Scenario(Base):
+    __tablename__ = "scenarios"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    name: Mapped[str] = mapped_column(String(200), unique=True)
+    description: Mapped[str] = mapped_column(Text, default="")
+    include_recurring: Mapped[bool] = mapped_column(Boolean, default=True)
+    include_planned: Mapped[bool] = mapped_column(Boolean, default=True)
+
+
+class ScenarioLoan(Base):
+    __tablename__ = "scenario_loans"
+    scenario_id: Mapped[int] = mapped_column(ForeignKey("scenarios.id"), primary_key=True)
+    loan_id: Mapped[int] = mapped_column(ForeignKey("loans.id"), primary_key=True)
+
+
+class ScenarioModifier(Base):
+    __tablename__ = "scenario_modifiers"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    scenario_id: Mapped[int] = mapped_column(ForeignKey("scenarios.id"))
+    target_type: Mapped[str] = mapped_column(String(20))
+    target_id: Mapped[int] = mapped_column(Integer)
+    kind: Mapped[str] = mapped_column(String(20))
+    value: Mapped[Decimal] = mapped_column(MONEY, default=Decimal("0"))
+
+
+class ProjectionPoint(Base):
+    __tablename__ = "projection_points"
+    id: Mapped[int] = mapped_column(primary_key=True)
+    scenario_id: Mapped[int] = mapped_column(ForeignKey("scenarios.id"), index=True)
+    day: Mapped[date] = mapped_column(Date)
+    balance: Mapped[Decimal] = mapped_column(MONEY)
+
+
+class ProjectionResult(Base):
+    __tablename__ = "projection_results"
+    scenario_id: Mapped[int] = mapped_column(ForeignKey("scenarios.id"), primary_key=True)
+    computed_at: Mapped[datetime] = mapped_column(DateTime)
+    low_point_date: Mapped[date] = mapped_column(Date)
+    low_point_balance: Mapped[Decimal] = mapped_column(MONEY)
+    below_zero_date: Mapped[date | None] = mapped_column(Date, nullable=True)
+    below_threshold_date: Mapped[date | None] = mapped_column(Date, nullable=True)