"""projects (02-domain-model.md §3).

Uniqueness is (tenant_id, client_party_id, lower(name)) — judgment #33:
the carried per-client constraint, tenant-scoped so R3's PlusROI-tenant
migration can't collide with BW-tenant rows.

income + billout adjustment columns are carried from the current app
(display-only per ADR #040) until the R4 invoice engine supersedes them.
NO sets/tasks tables and no task_id column in R1 (judgment #32).
"""

import uuid
from datetime import datetime

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

from app.models.base import Base


class Project(Base):
    __tablename__ = "projects"
    __table_args__ = (
        CheckConstraint("length(trim(name)) > 0", name="ck_projects_name_nonempty"),
        CheckConstraint(
            "status IN ('active', 'archived')", name="ck_projects_status"
        ),
        CheckConstraint(
            "(income IS NULL) = (income_currency IS NULL)",
            name="ck_projects_income_currency_pair",
        ),
        Index(
            "uq_projects_tenant_client_lower_name",
            "tenant_id",
            "client_party_id",
            text("lower(name)"),
            unique=True,
        ),
        Index("ix_projects_operator_party", "operator_party_id"),
        Index("ix_projects_client_party", "client_party_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)
    tenant_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("tenants.id"))
    # the org running the project (today's "operator")
    operator_party_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("parties.id"))
    # the end client
    client_party_id: Mapped[uuid.UUID] = mapped_column(ForeignKey("parties.id"))
    name: Mapped[str] = mapped_column(Text())
    status: Mapped[str] = mapped_column(Text(), default="active")
    # manually entered income — owner-only at the app layer (parity)
    income: Mapped[float | None] = mapped_column(Numeric(12, 2))
    income_currency: Mapped[str | None] = mapped_column(CHAR(3))
    # % markup (10 = +10%) applied at READ time — never stamped (ADR #040)
    billout_adjustment_pct: Mapped[float | None] = mapped_column(Numeric(7, 4))
    # fixed amount added once per window/row at read time; quoted in the
    # project's billout currency (all-CAD in R1 — 07 #21)
    billout_adjustment_amount: Mapped[float | None] = mapped_column(Numeric(12, 2))
    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 ProjectMilestone(Base):
    """A non-recurring project's payment schedule row (alembic 0008;
    ADR #022; docs/design/plusroi-forms.md §3). A deposit / milestone /
    final line with its own amount; ordered by ``sort_order`` within the
    project. Each milestone generates AT MOST one invoice
    (``generated_invoice_id``, SET NULL so deleting that invoice re-opens
    the milestone). CASCADE on project delete. Tenant-scoped THROUGH its
    project (no own tenant_id) — preserved for an app-owned tenant across
    the nightly via the parent-scoped ``_SCOPED_DELETES`` idiom."""

    __tablename__ = "project_milestones"
    __table_args__ = (
        Index(
            "ix_project_milestones_project_sort", "project_id", "sort_order"
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid(), primary_key=True, default=uuid.uuid4)
    project_id: Mapped[uuid.UUID] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE")
    )
    label: Mapped[str] = mapped_column(Text())
    amount: Mapped[float] = mapped_column(Numeric(12, 2))
    currency: Mapped[str | None] = mapped_column(CHAR(3))
    sort_order: Mapped[int] = mapped_column(Integer(), default=0)
    generated_invoice_id: Mapped[uuid.UUID | None] = mapped_column(
        ForeignKey("invoices.id", ondelete="SET NULL")
    )
    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()
    )