"""subscription application scoping

Revision ID: 000000000012
Revises: 000000000011
Create Date: 2026-02-13

TM-002: Add application_id column to subscriptions table for multi-tenant isolation.
This prevents same-user cross-application subscription access vulnerabilities.

Migration strategy:
1. Add application_id column as nullable (allows existing rows)
2. Future migration will backfill application_id from metadata or checkout sessions
3. Future migration will add NOT NULL constraint after backfill
"""

from alembic import op

revision = "000000000012"
down_revision = "000000000011"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # -----------------------------------------------------------------------
    # subscriptions: add application_id column (nullable for backfill)
    # -----------------------------------------------------------------------
    op.execute("""
        ALTER TABLE subscriptions
        ADD COLUMN IF NOT EXISTS application_id UUID
        REFERENCES applications(id) ON DELETE CASCADE;
    """)

    # Add index for application-scoped subscription lookups
    op.execute("""
        CREATE INDEX IF NOT EXISTS idx_subscriptions_app_user
        ON subscriptions (application_id, user_id)
        WHERE application_id IS NOT NULL;
    """)

    # Add index for application + stripe_subscription_id lookups (TM-002 requirement)
    op.execute("""
        CREATE INDEX IF NOT EXISTS idx_subscriptions_app_stripe_id
        ON subscriptions (application_id, stripe_subscription_id)
        WHERE application_id IS NOT NULL AND stripe_subscription_id IS NOT NULL;
    """)


def downgrade() -> None:
    # Drop indexes
    op.execute("DROP INDEX IF EXISTS idx_subscriptions_app_stripe_id;")
    op.execute("DROP INDEX IF EXISTS idx_subscriptions_app_user;")

    # Drop column
    op.execute("ALTER TABLE subscriptions DROP COLUMN IF EXISTS application_id;")