144 lines
6.2 KiB
Python
144 lines
6.2 KiB
Python
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")
|
|
# Konto-Saldo-Anker (Ausbaustufe 3 Task 1): ersetzt die bisherige
|
|
# Statement-closing-Logik als Basis der Saldenrechnung. Beide Felder sind
|
|
# nur gemeinsam gesetzt oder gemeinsam NULL (durchgesetzt in
|
|
# app.routers.accounts.AccountPatch).
|
|
anchor_date: Mapped[date | None] = mapped_column(Date, nullable=True)
|
|
anchor_balance: Mapped[Decimal | None] = mapped_column(MONEY, nullable=True)
|
|
|
|
|
|
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)
|