"""Add premium subscription tables (subscriptions, premium_requests)

Phase 1 of the premium subscription system: a manual, admin-activated workflow.
``premium_requests`` records a student's out-of-band payment for review;
approving one creates an active row in ``subscriptions``.

Revision ID: d7e9b1f4a3c2
Revises: c1a2b3d4e5f6
Create Date: 2026-07-03 00:00:00.000000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "d7e9b1f4a3c2"
down_revision = "c1a2b3d4e5f6"
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        "subscriptions",
        sa.Column("user_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("plan_name", sa.String(length=50), nullable=False),
        sa.Column(
            "status",
            sa.Enum("active", "expired", "cancelled", name="subscription_status"),
            nullable=False,
        ),
        sa.Column("start_date", sa.DateTime(), nullable=False),
        sa.Column("end_date", sa.DateTime(), nullable=False),
        sa.Column("activated_by", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=True),
        sa.Column("id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), autoincrement=True, nullable=False),
        sa.Column("public_id", sa.CHAR(length=36), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], name="fk_subscriptions_user_id"),
        sa.ForeignKeyConstraint(["activated_by"], ["users.id"], name="fk_subscriptions_activated_by"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("public_id"),
    )
    with op.batch_alter_table("subscriptions", schema=None) as batch_op:
        batch_op.create_index("ix_subscriptions_user_id", ["user_id"], unique=False)
        batch_op.create_index("ix_subscriptions_status", ["status"], unique=False)
        batch_op.create_index("ix_subscriptions_end_date", ["end_date"], unique=False)

    op.create_table(
        "premium_requests",
        sa.Column("user_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("plan_name", sa.String(length=50), nullable=False),
        sa.Column("payment_method", sa.String(length=30), nullable=False),
        sa.Column("transaction_reference", sa.String(length=120), nullable=True),
        sa.Column("remarks", sa.Text(), nullable=True),
        sa.Column(
            "status",
            sa.Enum("pending", "approved", "rejected", name="premium_request_status"),
            nullable=False,
        ),
        sa.Column("requested_at", sa.DateTime(), nullable=False),
        sa.Column("reviewed_at", sa.DateTime(), nullable=True),
        sa.Column("reviewed_by", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=True),
        sa.Column("id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), autoincrement=True, nullable=False),
        sa.Column("public_id", sa.CHAR(length=36), nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], name="fk_premium_requests_user_id"),
        sa.ForeignKeyConstraint(["reviewed_by"], ["users.id"], name="fk_premium_requests_reviewed_by"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("public_id"),
    )
    with op.batch_alter_table("premium_requests", schema=None) as batch_op:
        batch_op.create_index("ix_premium_requests_user_id", ["user_id"], unique=False)
        batch_op.create_index("ix_premium_requests_status", ["status"], unique=False)
        batch_op.create_index("ix_premium_requests_requested_at", ["requested_at"], unique=False)


def downgrade():
    # Dropping each table removes its indexes and foreign keys together.
    op.drop_table("premium_requests")
    op.drop_table("subscriptions")