"""PlusROI books: invoices ledger enrichment + expenses journal +
client/project commercial-defaults tables (R2, ADR #016).

The PlusROI partnership (rian ↔ Rob Cooper, 50/50 revenue share; Danielle
3% bookkeeping) runs its books on a Google Sheet with an Invoices ledger
(receivables), an Expenses ledger (payables), and a monthly settlement.
`with` is replacing that sheet. This migration lands the schema the
sheet→DB importer (`app/services/migrate/sheet_books.py`) writes into:

  1. invoices — ADDITIVE nullable columns (the receivables enrichment:
     fee/commission/settlement-month/original-face-currency/external id/…)
     and a WIDENED status CHECK for the sheet lifecycle (Pending Approval /
     Ready to Invoice / Invoiced / Paid / Cancelled / Delayed Payment). The
     existing open/sent/paid values are UNTOUCHED (BW time-invoice flows
     depend on them). Books are CAD (`invoices.currency` stays CAD +
     `manual_total` = the CAD book value); the USD face survives in
     `original_amount`/`original_currency` (08 §2 realized-FX practice).
  2. expenses — a NEW first-class payables journal (paid-by either
     partner nets out in the settlement — 08 §3).
  3. client_profiles — per-tenant commercial defaults for a client org.
  4. project_billing — the 1:1 billing profile for a project.

Additive to the R1 tables (no column moves off invoices), so the diff
harness stays green by construction. Downgrade drops the new tables +
columns and restores the original 3-value status CHECK.

Revision ID: 0007
Revises: 0006
Create Date: 2026-07-11
"""

from collections.abc import Sequence

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

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

# The sheet lifecycle (08 §2) mapped onto invoices.status. The first three
# are R1's existing vocabulary (BW time-invoice flows) — kept verbatim.
_STATUS_OLD = "status IN ('open', 'sent', 'paid')"
_STATUS_NEW = (
    "status IN ('open', 'sent', 'paid',"
    " 'pending_approval', 'ready', 'cancelled', 'delayed')"
)


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:
    # --- invoices: additive receivables-ledger enrichment ----------------
    op.add_column("invoices", sa.Column("fee", sa.Numeric(12, 2), nullable=True))
    op.add_column(
        "invoices", sa.Column("commission_pct", sa.Numeric(7, 4), nullable=True)
    )
    op.add_column(
        "invoices", sa.Column("commission_amount", sa.Numeric(12, 2), nullable=True)
    )
    op.add_column(
        "invoices",
        sa.Column(
            "commission_party_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("parties.id", ondelete="SET NULL"),
            nullable=True,
        ),
    )
    # first-of-month settlement tag — the sheet's "BW Invoice" grouping key
    op.add_column(
        "invoices", sa.Column("settlement_month", sa.Date(), nullable=True)
    )
    op.add_column(
        "invoices", sa.Column("relevant_period", sa.Text(), nullable=True)
    )
    # original-currency FACE value (retained when the receipt was USD)
    op.add_column(
        "invoices", sa.Column("original_amount", sa.Numeric(12, 2), nullable=True)
    )
    op.add_column(
        "invoices", sa.Column("original_currency", sa.CHAR(3), nullable=True)
    )
    op.add_column(
        "invoices", sa.Column("invoice_timing", sa.Text(), nullable=True)
    )
    op.add_column("invoices", sa.Column("billing_type", sa.Text(), nullable=True))
    # the sheet "Invoice ID" / Wave number — NO unique (the sheet carries
    # dupes; the importer reconciliation warns instead of failing)
    op.add_column("invoices", sa.Column("external_id", sa.Text(), nullable=True))
    op.add_column("invoices", sa.Column("due_date", sa.Date(), nullable=True))

    # widen the status CHECK (keep open/sent/paid; add the sheet states)
    op.drop_constraint("ck_invoices_status", "invoices", type_="check")
    op.create_check_constraint("ck_invoices_status", "invoices", _STATUS_NEW)

    op.create_index(
        "ix_invoices_tenant_settlement_month",
        "invoices",
        ["tenant_id", "settlement_month"],
    )

    # --- expenses: the payables journal (08 §3) --------------------------
    op.create_table(
        "expenses",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column(
            "tenant_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("tenants.id"),
            nullable=False,
        ),
        sa.Column(
            "project_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column(
            "vendor_party_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("parties.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("amount", sa.Numeric(12, 2), nullable=False),
        sa.Column("currency", sa.CHAR(3), nullable=False),
        sa.Column("expense_date", sa.Date(), nullable=True),
        sa.Column("external_invoice_id", sa.Text(), nullable=True),
        # the sheet "Paid By": PlusROI-or-BowdenWorks party (settlement
        # credits the fronting partner — 08 §3)
        sa.Column(
            "paid_by_party_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("parties.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("category", sa.Text(), nullable=True),
        sa.Column("type", sa.Text(), nullable=True),
        sa.Column("relevant_period", sa.Text(), nullable=True),
        sa.Column("settlement_month", sa.Date(), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column(
            "source",
            sa.Text(),
            nullable=False,
            server_default=sa.text("'manual'"),
        ),
        sa.Column(
            "created_by",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            nullable=True,
        ),
        _created_at(),
        sa.CheckConstraint(
            "source IN ('sheet', 'manual')", name="ck_expenses_source"
        ),
    )
    op.create_index(
        "ix_expenses_tenant_date", "expenses", ["tenant_id", "expense_date"]
    )
    op.create_index(
        "ix_expenses_tenant_settlement_month",
        "expenses",
        ["tenant_id", "settlement_month"],
    )
    op.create_index("ix_expenses_vendor_party", "expenses", ["vendor_party_id"])
    op.create_index("ix_expenses_project", "expenses", ["project_id"])

    # --- client_profiles: per-tenant commercial defaults for a client ----
    op.create_table(
        "client_profiles",
        sa.Column(
            "tenant_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("tenants.id"),
            primary_key=True,
        ),
        sa.Column(
            "client_party_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("parties.id"),
            primary_key=True,
        ),
        sa.Column("currency", sa.CHAR(3), nullable=True),
        sa.Column("terms", sa.Text(), nullable=True),
        sa.Column("invoice_timing", sa.Text(), nullable=True),
        sa.Column("default_commission_pct", sa.Numeric(7, 4), nullable=True),
        sa.Column(
            "commission_party_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("parties.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("credit_limit", sa.Numeric(12, 2), nullable=True),
        sa.Column("contacts", sa.Text(), nullable=True),
        sa.Column(
            "archived",
            sa.Boolean(),
            nullable=False,
            server_default=sa.text("false"),
        ),
        sa.Column("notes", sa.Text(), nullable=True),
        _created_at(),
        _updated_at(),
    )

    # --- project_billing: the 1:1 billing profile ------------------------
    op.create_table(
        "project_billing",
        sa.Column(
            "project_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column("billing_type", sa.Text(), nullable=True),
        sa.Column("amount", sa.Numeric(12, 2), nullable=True),
        sa.Column("currency", sa.CHAR(3), nullable=True),
        sa.Column("invoice_timing", sa.Text(), nullable=True),
        sa.Column("terms", sa.Text(), nullable=True),
        sa.Column("start_date", sa.Date(), nullable=True),
        sa.Column("default_commission_pct", sa.Numeric(7, 4), nullable=True),
        sa.Column(
            "commission_party_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("parties.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("expected_next", sa.Text(), nullable=True),
        sa.Column("notes", sa.Text(), nullable=True),
        _created_at(),
        _updated_at(),
    )


def downgrade() -> None:
    op.drop_table("project_billing")
    op.drop_table("client_profiles")
    op.drop_index("ix_expenses_project", table_name="expenses")
    op.drop_index("ix_expenses_vendor_party", table_name="expenses")
    op.drop_index("ix_expenses_tenant_settlement_month", table_name="expenses")
    op.drop_index("ix_expenses_tenant_date", table_name="expenses")
    op.drop_table("expenses")

    op.drop_index("ix_invoices_tenant_settlement_month", table_name="invoices")
    op.drop_constraint("ck_invoices_status", "invoices", type_="check")
    op.create_check_constraint("ck_invoices_status", "invoices", _STATUS_OLD)
    for col in (
        "due_date",
        "external_id",
        "billing_type",
        "invoice_timing",
        "original_currency",
        "original_amount",
        "relevant_period",
        "settlement_month",
        "commission_party_id",
        "commission_amount",
        "commission_pct",
        "fee",
    ):
        op.drop_column("invoices", col)