moneyfy/backend/app/models/core.py

"""Stammdaten: Konten, Kategorien, Firmen, Logos, Benutzer."""

from datetime import date, datetime
from decimal import Decimal

from sqlalchemy import (
    Boolean,
    CheckConstraint,
    Date,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db.base import Base, CreatedAtMixin, Money, TimestampMixin
from app.models.enums import (
    AccountType,
    EntryKind,
    LogoSource,
    LogoStatus,
    pg_enum,
)


class Account(Base, TimestampMixin):
    """Ein Konto.

    Der Saldo wird aus Eröffnungssaldo plus allen bestätigten Buchungen fortgeschrieben.
    """

    __tablename__ = "account"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    type: Mapped[AccountType] = mapped_column(
        pg_enum(AccountType, "account_type"), nullable=False, default=AccountType.CHECKING
    )
    iban_last4: Mapped[str | None] = mapped_column(String(4), nullable=True)
    opening_balance: Mapped[Decimal] = mapped_column(Money, nullable=False, default=Decimal("0.00"))
    opening_balance_date: Mapped[date] = mapped_column(Date, nullable=False)
    color: Mapped[str] = mapped_column(String(9), nullable=False, default="#3b82f6")
    icon: Mapped[str] = mapped_column(String(64), nullable=False, default="wallet")
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    __table_args__ = (
        UniqueConstraint("name", name="uq_account_name"),
        Index("ix_account_is_active", "is_active"),
    )


class Category(Base):
    """Zweistufiger Kategoriebaum – Unterkategorien haben genau einen Elternknoten."""

    __tablename__ = "category"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    parent_id: Mapped[int | None] = mapped_column(
        ForeignKey("category.id", ondelete="RESTRICT"), nullable=True
    )
    name: Mapped[str] = mapped_column(String(120), nullable=False)
    kind: Mapped[EntryKind] = mapped_column(pg_enum(EntryKind, "entry_kind"), nullable=False)
    color: Mapped[str] = mapped_column(String(9), nullable=False, default="#64748b")
    icon: Mapped[str] = mapped_column(String(64), nullable=False, default="circle")
    is_fixed_cost: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_archived: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)

    parent: Mapped["Category | None"] = relationship(
        remote_side="Category.id", back_populates="children"
    )
    children: Mapped[list["Category"]] = relationship(
        back_populates="parent", cascade="save-update, merge", order_by="Category.sort_order"
    )

    __table_args__ = (
        UniqueConstraint("parent_id", "name", name="uq_category_parent_id"),
        Index("ix_category_kind", "kind"),
    )


class LogoAsset(Base):
    """Im Volume abgelegtes Logo. Auslieferung erfolgt ausschließlich lokal."""

    __tablename__ = "logo_asset"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    sha256: Mapped[str] = mapped_column(String(64), nullable=False, unique=True)
    mime: Mapped[str] = mapped_column(String(64), nullable=False)
    width: Mapped[int | None] = mapped_column(Integer, nullable=True)
    height: Mapped[int | None] = mapped_column(Integer, nullable=True)
    file_path: Mapped[str] = mapped_column(String(255), nullable=False)
    source_url: Mapped[str | None] = mapped_column(Text, nullable=True)
    fetched_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class Merchant(Base, CreatedAtMixin):
    """Firma bzw. Zahlungsempfänger inklusive Markenlogo und -farbe."""

    __tablename__ = "merchant"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(160), nullable=False)
    normalized_name: Mapped[str] = mapped_column(String(160), nullable=False, unique=True)
    domain: Mapped[str | None] = mapped_column(String(255), nullable=True)
    aliases: Mapped[list[str]] = mapped_column(
        ARRAY(Text), nullable=False, default=list, server_default="{}"
    )
    logo_asset_id: Mapped[int | None] = mapped_column(
        ForeignKey("logo_asset.id", ondelete="SET NULL"), nullable=True
    )
    brand_color: Mapped[str | None] = mapped_column(String(9), nullable=True)
    brand_color_dark: Mapped[str | None] = mapped_column(String(9), nullable=True)
    logo_source: Mapped[LogoSource | None] = mapped_column(
        pg_enum(LogoSource, "logo_source"), nullable=True
    )
    logo_status: Mapped[LogoStatus] = mapped_column(
        pg_enum(LogoStatus, "logo_status"), nullable=False, default=LogoStatus.PENDING
    )

    logo_asset: Mapped[LogoAsset | None] = relationship(lazy="joined")

    __table_args__ = (Index("ix_merchant_normalized_name", "normalized_name"),)


class AppSetting(Base, TimestampMixin):
    """Anwendungsweite Einstellungen. Es gibt genau eine Zeile mit `id = 1`.

    `month_start_day` legt den Gehaltstag fest: Ab diesem Tag rechnet moneyfy
    einen neuen Monat. Ein Wert jenseits der Monatslänge trifft den Monatsletzten.
    """

    __tablename__ = "app_setting"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, default=1)
    month_start_day: Mapped[int] = mapped_column(Integer, nullable=False, default=1)

    __table_args__ = (
        CheckConstraint("id = 1", name="single_row"),
        CheckConstraint("month_start_day BETWEEN 1 AND 31", name="month_start_day_range"),
    )


class AppUser(Base, CreatedAtMixin):
    """Single-User-Betrieb; `external_subject` ist für eine spätere OIDC-Anbindung vorgesehen."""

    __tablename__ = "app_user"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    username: Mapped[str] = mapped_column(String(120), nullable=False, unique=True)
    email: Mapped[str | None] = mapped_column(String(255), nullable=True)
    password_hash: Mapped[str | None] = mapped_column(Text, nullable=True)
    must_change_password: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    external_subject: Mapped[str | None] = mapped_column(String(255), nullable=True, unique=True)
    last_login_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    __table_args__ = (
        CheckConstraint(
            "password_hash IS NOT NULL OR external_subject IS NOT NULL",
            name="local_or_external_login",
        ),
    )


class RefreshToken(Base, CreatedAtMixin):
    """Ausgegebenes Refresh-Token.

    Für die Rotation beim Refresh wird serverseitiger Zustand benötigt: Ein Token
    ist nur gültig, solange seine `jti` hier ungesperrt hinterlegt ist. Beim Refresh
    wird der alte Eintrag gesperrt und ein neuer angelegt.
    """

    __tablename__ = "refresh_token"

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    jti: Mapped[str] = mapped_column(String(64), nullable=False, unique=True)
    user_id: Mapped[int] = mapped_column(
        ForeignKey("app_user.id", ondelete="CASCADE"), nullable=False
    )
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    user: Mapped[AppUser] = relationship()

    __table_args__ = (Index("ix_refresh_token_user_id", "user_id"),)