"""invoices: add stripe_customer_id (ORM expects it; 001 only had stripe_subscription_id).

invoice.paid webhook upserts use stripe_customer_id; missing column causes UndefinedColumn.
"""
import sqlalchemy as sa
from alembic import op
from sqlalchemy import inspect, text

revision = "070"
down_revision = "069"
branch_labels = None
depends_on = None


def upgrade() -> None:
    conn = op.get_bind()
    insp = inspect(conn)
    cols = {c["name"] for c in insp.get_columns("invoices")}
    if "stripe_customer_id" in cols:
        return

    op.add_column(
        "invoices",
        sa.Column("stripe_customer_id", sa.String(length=100), nullable=True),
    )
    op.execute(
        text("""
        UPDATE invoices AS inv
        SET stripe_customer_id = sc.stripe_customer_id
        FROM stripe_customers AS sc
        WHERE inv.workspace_id = sc.workspace_id
          AND inv.stripe_customer_id IS NULL
        """)
    )
    op.execute(
        text("""
        UPDATE invoices AS inv
        SET stripe_customer_id = sub.stripe_customer_id
        FROM (
            SELECT DISTINCT ON (workspace_id)
                workspace_id,
                stripe_customer_id
            FROM subscriptions
            WHERE stripe_customer_id IS NOT NULL
              AND TRIM(stripe_customer_id) <> ''
            ORDER BY workspace_id, created_at DESC
        ) AS sub
        WHERE inv.workspace_id = sub.workspace_id
          AND inv.stripe_customer_id IS NULL
        """)
    )
    op.execute(text("DELETE FROM invoices WHERE stripe_customer_id IS NULL"))
    op.alter_column(
        "invoices",
        "stripe_customer_id",
        existing_type=sa.String(length=100),
        nullable=False,
    )


def downgrade() -> None:
    conn = op.get_bind()
    insp = inspect(conn)
    cols = {c["name"] for c in insp.get_columns("invoices")}
    if "stripe_customer_id" not in cols:
        return
    op.drop_column("invoices", "stripe_customer_id")