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"))
    # Nur fuer kind="ende" gesetzt: Datum, an dem der Ziel-Posten innerhalb
    # dieses Szenarios endet (Ausbaustufe 5).
    end_date: Mapped[date | None] = mapped_column(Date, nullable=True)


class ScenarioPlannedItem(Base):
    """Einmalzahlung, die NUR in einem bestimmten Szenario zaehlt -
    unabhaengig von Scenario.include_planned (das steuert nur die globalen
    PlannedItems). Ausbaustufe 5."""
    __tablename__ = "scenario_planned_items"
    id: Mapped[int] = mapped_column(primary_key=True)
    scenario_id: Mapped[int] = mapped_column(ForeignKey("scenarios.id"), index=True)
    name: Mapped[str] = mapped_column(String(200))
    amount: Mapped[Decimal] = mapped_column(MONEY)
    due: Mapped[date] = mapped_column(Date)


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)