"""PlusROI books: expenses journal + client/project commercial defaults
(R2, ADR #016; 08 §2/§3).

These land the receivables/payables model the PlusROI settlement runs on,
imported from the legacy Google Sheet by
``app/services/migrate/sheet_books.py``:

  - ``Expense`` — the payables journal. Either partner can front an
    expense (``paid_by_party_id`` = PlusROI or Bowden Works); the monthly
    settlement nets it out. ``source`` distinguishes sheet-imported rows
    (rebuilt nightly, never hand-edited) from ``manual`` app entries.
  - ``ClientProfile`` — per-tenant commercial defaults for a client org
    (currency, terms, default commission, credit limit, archive flag).
    Keyed (tenant, client party) so the same org can be a client of
    several tenants with different terms.
  - ``ProjectBilling`` — the 1:1 billing profile for a project (billing
    type/amount/currency, invoice timing, default commission).

The receivables ledger itself is ``invoices`` (enriched additively in
alembic 0007 — see ``app/models/invoice.py``), not a new table: BW's
existing invoice flows and the R1 invoice shape are reused.
"""

import uuid
from datetime import date, datetime

from sqlalchemy import (
    CHAR,
    Boolean,
    CheckConstraint,
    Date,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    Text,
    Uuid,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.models.base import Base


class Expense(Base):
    """The payables journal (08 §3). Sheet-imported rows carry
    ``source='sheet'`` and are rebuilt on every nightly transform run;
    ``source='manual'`` rows are app-entered and survive a rebuild only
    once the app owns them (R2+)."""

    __tablename__ = "expenses"
    __table_args__ = (
        CheckConstraint("source IN ('sheet', 'manual')", name="ck_expenses_source"),
        Index("ix_expenses_tenant_date", "tenant_id", "expense_date"),
        Index(
            "ix_expenses_tenant_settlement_month", "tenant_id", "settlement_month"
        ),
        Index("ix_expenses_vendor_party", "vendor_party_id"),
        Index("ix_expenses_project", "project_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"))
    project_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("projects.id", ondelete="SET NULL")
    )
    vendor_party_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("parties.id", ondelete="SET NULL")
    )
    amount: Mapped[float] = mapped_column(Numeric(12, 2))
    currency: Mapped[str] = mapped_column(CHAR(3))
    expense_date: Mapped[date | None] = mapped_column(Date())
    external_invoice_id: Mapped[str | None] = mapped_column(Text())
    # the sheet "Paid By" — PlusROI or Bowden Works party; the settlement
    # credits the fronting partner (08 §3)
    paid_by_party_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("parties.id", ondelete="SET NULL")
    )
    category: Mapped[str | None] = mapped_column(Text())
    type: Mapped[str | None] = mapped_column(Text())
    relevant_period: Mapped[str | None] = mapped_column(Text())
    # first-of-month settlement tag (the sheet's "BW Invoice" value)
    settlement_month: Mapped[date | None] = mapped_column(Date())
    notes: Mapped[str | None] = mapped_column(Text())
    source: Mapped[str] = mapped_column(Text(), default="manual")
    created_by: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL")
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


class ClientProfile(Base):
    """Per-tenant commercial defaults for a client org (08 §1 Clients
    registry). PK (tenant, client party): the same org can be a client of
    multiple tenants with different terms. ``archived`` mirrors the sheet's
    ⛔ flag; the party itself stays ``is_active`` (history references it)."""

    __tablename__ = "client_profiles"

    tenant_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("tenants.id"), primary_key=True
    )
    client_party_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("parties.id"), primary_key=True
    )
    currency: Mapped[str | None] = mapped_column(CHAR(3))
    terms: Mapped[str | None] = mapped_column(Text())
    invoice_timing: Mapped[str | None] = mapped_column(Text())
    default_commission_pct: Mapped[float | None] = mapped_column(Numeric(7, 4))
    commission_party_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("parties.id", ondelete="SET NULL")
    )
    credit_limit: Mapped[float | None] = mapped_column(Numeric(12, 2))
    contacts: Mapped[str | None] = mapped_column(Text())
    archived: Mapped[bool] = mapped_column(Boolean(), default=False)
    notes: Mapped[str | None] = mapped_column(Text())
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class ProjectBilling(Base):
    """The 1:1 billing profile for a project (08 §1 Projects registry).
    Separate from ``projects`` so the billing facts (type/amount/currency,
    invoice timing, default commission) can grow without widening the core
    project row. Cascades on project delete.

    The CLEAN recurrence model (alembic 0008, ADR #022) — ``is_recurring``
    + ``cycle_months`` + ``billing_timing`` — replaces the sheet's
    conflated free-text ``billing_type``, which stays for back-compat and
    import mapping. A recurring project carries a default ``amount`` per
    cycle; a non-recurring one carries a ``project_milestones`` schedule
    instead (``amount`` left NULL)."""

    __tablename__ = "project_billing"
    __table_args__ = (
        CheckConstraint(
            "billing_timing IN ('month_end', 'mid_month')",
            name="ck_project_billing_timing",
        ),
    )

    project_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE"), primary_key=True
    )
    billing_type: Mapped[str | None] = mapped_column(Text())
    amount: Mapped[float | None] = mapped_column(Numeric(12, 2))
    currency: Mapped[str | None] = mapped_column(CHAR(3))
    invoice_timing: Mapped[str | None] = mapped_column(Text())
    terms: Mapped[str | None] = mapped_column(Text())
    start_date: Mapped[date | None] = mapped_column(Date())
    default_commission_pct: Mapped[float | None] = mapped_column(Numeric(7, 4))
    commission_party_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("parties.id", ondelete="SET NULL")
    )
    expected_next: Mapped[str | None] = mapped_column(Text())
    notes: Mapped[str | None] = mapped_column(Text())
    # --- recurrence model (alembic 0008; ADR #022) -----------------------
    is_recurring: Mapped[bool] = mapped_column(Boolean(), default=False)
    #: recurrence period in months (1 monthly, 3 quarterly, 12 annual)
    cycle_months: Mapped[int | None] = mapped_column(Integer())
    #: month_end | mid_month — the clean timing, distinct from the
    #: free-text ``invoice_timing`` carried from the sheet
    billing_timing: Mapped[str | None] = mapped_column(Text())
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
    )


class TenantVendor(Base):
    """The per-tenant vendor marker (alembic 0008; ADR #022). Slice 1's
    Vendors form could only list a vendor once an expense referenced it
    (a vendor has no membership row). This marker makes a freshly created
    vendor immediately listable AND holds this tenant's vendor defaults
    (category / paid-by) the Expense form pre-fills.

    Deliberately NOT an engagement: an engagement is a commercial edge in
    the entity graph that can grant a party tenant data access; a payee we
    merely pay must never gain that. PK (tenant, vendor party): the same
    payee party can be a vendor of several tenants with different defaults.
    Tenant-scoped — preserved for an app-owned tenant across the nightly
    (``_SCOPED_DELETES``)."""

    __tablename__ = "tenant_vendors"

    tenant_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("tenants.id"), primary_key=True
    )
    vendor_party_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("parties.id", ondelete="CASCADE"), primary_key=True
    )
    default_category: Mapped[str | None] = mapped_column(Text())
    default_paid_by_party_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("parties.id", ondelete="SET NULL")
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )