"""payments: add payment_events table, token_expires_at and currency to contracts

Revision ID: 0042
Revises: 0041
Create Date: 2026-07-02

Changes:
  - contracts: add token_expires_at TIMESTAMPTZ NULL
  - contracts: add currency VARCHAR(3) NULL DEFAULT 'CAD'
  - Create payment_events table with full audit columns
  - Indexes: tenant+contract, unique stripe_payment_intent_id, tenant+status
"""
from alembic import op
import sqlalchemy as sa

revision = "0046"
down_revision = "0045"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()

    # ── contracts: new columns ──────────────────────────────────────────────────
    conn.execute(sa.text(
        "ALTER TABLE contracts ADD COLUMN IF NOT EXISTS token_expires_at TIMESTAMPTZ NULL"
    ))
    conn.execute(sa.text(
        "ALTER TABLE contracts ADD COLUMN IF NOT EXISTS currency VARCHAR(3) NULL DEFAULT 'CAD'"
    ))

    # ── payment_events table ────────────────────────────────────────────────────
    conn.execute(sa.text("""
        CREATE TABLE IF NOT EXISTS payment_events (
            id                          UUID            PRIMARY KEY DEFAULT gen_random_uuid(),
            tenant_id                   UUID            NOT NULL REFERENCES accounts(id),
            contract_id                 UUID            NOT NULL REFERENCES contracts(id),
            invoice_id                  UUID            NULL REFERENCES invoices(id),
            stripe_payment_intent_id    VARCHAR(100)    NOT NULL,
            stripe_charge_id            VARCHAR(100)    NULL,
            amount                      NUMERIC(12,2)   NOT NULL,
            currency                    VARCHAR(3)      NOT NULL DEFAULT 'CAD',
            status                      VARCHAR(30)     NOT NULL,
            receipt_email               VARCHAR(255)    NULL,
            receipt_sent_at             TIMESTAMPTZ     NULL,
            created_at                  TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
            updated_at                  TIMESTAMPTZ     NOT NULL DEFAULT NOW()
        )
    """))

    # Primary lookup index
    conn.execute(sa.text("""
        CREATE INDEX IF NOT EXISTS ix_payment_events_tenant_contract
        ON payment_events (tenant_id, contract_id)
    """))

    # Idempotency: detect duplicate webhook delivery
    conn.execute(sa.text("""
        CREATE UNIQUE INDEX IF NOT EXISTS ix_payment_events_stripe_intent_unique
        ON payment_events (stripe_payment_intent_id)
    """))

    # Status filtering
    conn.execute(sa.text("""
        CREATE INDEX IF NOT EXISTS ix_payment_events_tenant_status
        ON payment_events (tenant_id, status)
    """))


def downgrade() -> None:
    conn = op.get_bind()
    conn.execute(sa.text("DROP INDEX IF EXISTS ix_payment_events_tenant_status"))
    conn.execute(sa.text("DROP INDEX IF EXISTS ix_payment_events_stripe_intent_unique"))
    conn.execute(sa.text("DROP INDEX IF EXISTS ix_payment_events_tenant_contract"))
    conn.execute(sa.text("DROP TABLE IF EXISTS payment_events"))
    conn.execute(sa.text("ALTER TABLE contracts DROP COLUMN IF EXISTS currency"))
    conn.execute(sa.text("ALTER TABLE contracts DROP COLUMN IF EXISTS token_expires_at"))