"""Add limited-slot pricing campaigns (founding-customer offer).

Revision ID: 168
Revises: 167
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID

revision = "168"
down_revision = "167"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "pricing_campaigns",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column("slug", sa.String(50), nullable=False, unique=True),
        sa.Column("name", sa.String(100), nullable=False),
        sa.Column("max_redemptions", sa.Integer(), nullable=False),
        sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("starts_at", sa.DateTime(timezone=True)),
        sa.Column("ends_at", sa.DateTime(timezone=True)),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.CheckConstraint("max_redemptions >= 0", name="ck_pricing_campaigns_max_redemptions"),
    )
    op.create_table(
        "pricing_campaign_prices",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "campaign_id", UUID(as_uuid=True),
            sa.ForeignKey("pricing_campaigns.id", ondelete="CASCADE"), nullable=False,
        ),
        sa.Column(
            "subscription_plan_id", UUID(as_uuid=True),
            sa.ForeignKey("subscription_plans.id", ondelete="CASCADE"), nullable=False,
        ),
        sa.Column("interval", sa.String(20), nullable=False),
        sa.Column("stripe_price_id", sa.String(100), nullable=False, unique=True),
        sa.Column("price_minor", sa.BigInteger(), nullable=False),
        sa.Column("currency", sa.String(10), nullable=False, server_default="usd"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.UniqueConstraint(
            "campaign_id", "subscription_plan_id", "interval",
            name="uq_pricing_campaign_prices_plan_interval",
        ),
    )
    op.create_table(
        "pricing_campaign_redemptions",
        sa.Column("id", UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "campaign_id", UUID(as_uuid=True),
            sa.ForeignKey("pricing_campaigns.id", ondelete="CASCADE"), nullable=False,
        ),
        sa.Column(
            "campaign_price_id", UUID(as_uuid=True),
            sa.ForeignKey("pricing_campaign_prices.id", ondelete="RESTRICT"), nullable=False,
        ),
        sa.Column("subject_key", sa.String(320), nullable=False),
        sa.Column(
            "workspace_id", UUID(as_uuid=True),
            sa.ForeignKey("workspaces.id", ondelete="SET NULL"),
        ),
        sa.Column("status", sa.String(20), nullable=False, server_default="reserved"),
        sa.Column("stripe_checkout_session_id", sa.String(100), unique=True),
        sa.Column("stripe_subscription_id", sa.String(100)),
        sa.Column("expires_at", sa.DateTime(timezone=True)),
        sa.Column("confirmed_at", sa.DateTime(timezone=True)),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.CheckConstraint(
            "status IN ('reserved', 'confirmed', 'released')",
            name="ck_pricing_campaign_redemptions_status",
        ),
    )
    # One live (reserved or confirmed) redemption per subject per campaign.
    op.create_index(
        "uq_pricing_campaign_redemptions_active_subject",
        "pricing_campaign_redemptions",
        ["campaign_id", "subject_key"],
        unique=True,
        postgresql_where=sa.text("status IN ('reserved', 'confirmed')"),
    )
    op.create_index(
        "ix_pricing_campaign_redemptions_campaign_status",
        "pricing_campaign_redemptions",
        ["campaign_id", "status"],
    )


def downgrade() -> None:
    op.drop_index("ix_pricing_campaign_redemptions_campaign_status", table_name="pricing_campaign_redemptions")
    op.drop_index("uq_pricing_campaign_redemptions_active_subject", table_name="pricing_campaign_redemptions")
    op.drop_table("pricing_campaign_redemptions")
    op.drop_table("pricing_campaign_prices")
    op.drop_table("pricing_campaigns")