"""Add payments table (Razorpay premium purchases).

Records one row per payment attempt for a premium purchase — gateway order/payment
ids, amount, status and the linked subscription on capture. Deliberately separate
from ``subscriptions`` so the payment provider is isolated and the subscription
model is never overloaded (see docs/03_Modules/Premium_Module.md). Additive only;
the manual activation workflow is unchanged.

Revision ID: b5d7f9a1c3e2
Revises: a2c4e6f8b0d1
Create Date: 2026-07-06

"""

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision = "b5d7f9a1c3e2"
down_revision = "a2c4e6f8b0d1"
branch_labels = None
depends_on = None

_BIGINT = sa.BigInteger().with_variant(sa.Integer(), "sqlite")
_PAYMENT_STATUS = sa.Enum(
    "created", "authorized", "captured", "failed", "refunded", name="payment_status"
)


def upgrade():
    op.create_table(
        "payments",
        sa.Column("id", _BIGINT, autoincrement=True, nullable=False),
        sa.Column("public_id", sa.CHAR(length=36), nullable=False),
        sa.Column("user_id", _BIGINT, nullable=False),
        sa.Column("subscription_id", _BIGINT, nullable=True),
        sa.Column("plan_name", sa.String(length=50), nullable=False),
        sa.Column("provider", sa.String(length=20), nullable=False),
        sa.Column("provider_order_id", sa.String(length=120), nullable=False),
        sa.Column("provider_payment_id", sa.String(length=120), nullable=True),
        sa.Column("provider_signature", sa.String(length=255), nullable=True),
        sa.Column("amount", sa.Numeric(precision=10, scale=2), nullable=False),
        sa.Column("currency", sa.String(length=8), nullable=False),
        sa.Column("status", _PAYMENT_STATUS, nullable=False),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("paid_at", sa.DateTime(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"]),
        sa.ForeignKeyConstraint(["subscription_id"], ["subscriptions.id"]),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("public_id"),
        sa.UniqueConstraint("provider_order_id"),
    )
    with op.batch_alter_table("payments", schema=None) as batch_op:
        batch_op.create_index("ix_payments_user_id", ["user_id"], unique=False)
        batch_op.create_index("ix_payments_status", ["status"], unique=False)
        batch_op.create_index(
            "ix_payments_provider_payment_id", ["provider_payment_id"], unique=False
        )
        batch_op.create_index("ix_payments_created_at", ["created_at"], unique=False)


def downgrade():
    with op.batch_alter_table("payments", schema=None) as batch_op:
        batch_op.drop_index("ix_payments_created_at")
        batch_op.drop_index("ix_payments_provider_payment_id")
        batch_op.drop_index("ix_payments_status")
        batch_op.drop_index("ix_payments_user_id")
    op.drop_table("payments")
    _PAYMENT_STATUS.drop(op.get_bind(), checkfirst=True)