"""PlusROI phase-3 write forms: the recurrence model + milestone schedules,
USD-payment realized-FX on invoices, and the vendor-listability table.

Drives `docs/design/plusroi-forms.md` §3 + the "New schema — alembic 0008"
section (ADR #022). All additive — no column moves off any harness-read
table — so the diff harness stays green by construction (as with 0007):

  1. `project_billing` gains the CLEAN recurrence model —
     `is_recurring` (bool), `cycle_months` (int), `billing_timing`
     (`month_end`|`mid_month`) — replacing the sheet's conflated
     "type". The existing `invoice_timing`/`billing_type` stay for
     back-compat/import mapping.
  2. NEW `project_milestones` — a non-recurring project's payment
     schedule (deposit / milestone / final). Each milestone generates at
     most one invoice (`generated_invoice_id`, SET NULL). CASCADE on
     project delete.
  3. `invoices` gains `collected_amount` + `collected_currency` — the
     realized CAD actually collected, recorded at Paid for a USD invoice
     (`manual_total` stays the CAD book value; `original_amount`/
     `_currency` stays the USD face — 08 §2 realized-FX practice).
  4. NEW `tenant_vendors` — the per-tenant vendor marker slice 1
     deferred: a vendor created with no expenses yet must still be
     listable + hold this tenant's defaults (category / paid-by). A
     marker table (NOT an engagement — an engagement would grant the
     vendor tenant data access it must never have).

Both NEW tables are tenant-scoped and MUST be preserved for an app-owned
tenant across the nightly BW rebuild — they join `transforms._SCOPED_DELETES`
(project_milestones scoped through its project's tenant; tenant_vendors by
its own tenant_id) AND `TRUNCATE_TABLES` (ADR #021/#022). They also HAVE to
be in the truncate set on the fast path: each references a truncated table
(project_milestones→projects/invoices, tenant_vendors→tenants), so omitting
them would make `TRUNCATE ... projects/tenants` fail.

Downgrade drops both new tables + every added column and is a clean revert
to 0007.

Revision ID: 0008
Revises: 0007
Create Date: 2026-07-12
"""

from collections.abc import Sequence

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision: str = "0008"
down_revision: str | None = "0007"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def _created_at() -> sa.Column:
    return sa.Column(
        "created_at",
        sa.DateTime(timezone=True),
        nullable=False,
        server_default=sa.text("now()"),
    )


def _updated_at() -> sa.Column:
    return sa.Column(
        "updated_at",
        sa.DateTime(timezone=True),
        nullable=False,
        server_default=sa.text("now()"),
    )


def upgrade() -> None:
    # --- 1. project_billing: the clean recurrence model ------------------
    op.add_column(
        "project_billing",
        sa.Column(
            "is_recurring",
            sa.Boolean(),
            nullable=False,
            server_default=sa.text("false"),
        ),
    )
    op.add_column(
        "project_billing", sa.Column("cycle_months", sa.Integer(), nullable=True)
    )
    op.add_column(
        "project_billing", sa.Column("billing_timing", sa.Text(), nullable=True)
    )
    op.create_check_constraint(
        "ck_project_billing_timing",
        "project_billing",
        "billing_timing IN ('month_end', 'mid_month')",
    )

    # --- 2. project_milestones: the non-recurring payment schedule -------
    op.create_table(
        "project_milestones",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "project_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("label", sa.Text(), nullable=False),
        sa.Column("amount", sa.Numeric(12, 2), nullable=False),
        sa.Column("currency", sa.CHAR(3), nullable=True),
        sa.Column(
            "sort_order",
            sa.Integer(),
            nullable=False,
            server_default=sa.text("0"),
        ),
        # a milestone generates AT MOST one invoice; SET NULL so deleting
        # the invoice re-opens the milestone for regeneration
        sa.Column(
            "generated_invoice_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("invoices.id", ondelete="SET NULL"),
            nullable=True,
        ),
        _created_at(),
        _updated_at(),
    )
    op.create_index(
        "ix_project_milestones_project_sort",
        "project_milestones",
        ["project_id", "sort_order"],
    )

    # --- 3. invoices: USD-payment realized-FX ----------------------------
    op.add_column(
        "invoices", sa.Column("collected_amount", sa.Numeric(12, 2), nullable=True)
    )
    op.add_column(
        "invoices", sa.Column("collected_currency", sa.CHAR(3), nullable=True)
    )

    # --- 4. tenant_vendors: the per-tenant vendor marker + defaults ------
    op.create_table(
        "tenant_vendors",
        sa.Column(
            "tenant_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("tenants.id"),
            primary_key=True,
        ),
        sa.Column(
            "vendor_party_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("parties.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column("default_category", sa.Text(), nullable=True),
        sa.Column(
            "default_paid_by_party_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("parties.id", ondelete="SET NULL"),
            nullable=True,
        ),
        _created_at(),
    )


def downgrade() -> None:
    op.drop_table("tenant_vendors")

    op.drop_column("invoices", "collected_currency")
    op.drop_column("invoices", "collected_amount")

    op.drop_index(
        "ix_project_milestones_project_sort", table_name="project_milestones"
    )
    op.drop_table("project_milestones")

    op.drop_constraint(
        "ck_project_billing_timing", "project_billing", type_="check"
    )
    op.drop_column("project_billing", "billing_timing")
    op.drop_column("project_billing", "cycle_months")
    op.drop_column("project_billing", "is_recurring")