"""Add student_verification_history table (immutable verification audit trail).

Appends one row per SUBMITTED / UPDATED / APPROVED / REJECTED event so the admin
timeline and the student's own history can be rendered without recomputing
state. The existing admin audit log only records admin actions (not student
submissions or the submitted values), so a dedicated table is required. No
existing table is changed.

Revision ID: b3d5f7a9c1e4
Revises: a1c3e5b7d9f2
Create Date: 2026-07-05

"""
from alembic import op
import sqlalchemy as sa


revision = "b3d5f7a9c1e4"
down_revision = "a1c3e5b7d9f2"
branch_labels = None
depends_on = None

_BIGINT = sa.BigInteger().with_variant(sa.Integer(), "sqlite")
_ACTION = sa.Enum("submitted", "approved", "rejected", "updated", name="verification_history_action")
_STATUS = sa.Enum("not_verified", "pending", "verified", "rejected", name="verification_history_status")


def upgrade():
    op.create_table(
        "student_verification_history",
        sa.Column("user_id", _BIGINT, nullable=False),
        sa.Column("action", _ACTION, nullable=False),
        sa.Column("status", _STATUS, nullable=False),
        sa.Column("submitted_phone", sa.String(length=20), nullable=True),
        sa.Column("submitted_university", sa.String(length=150), nullable=True),
        sa.Column("submitted_student_id", sa.String(length=60), nullable=True),
        sa.Column("reviewed_by", _BIGINT, nullable=True),
        sa.Column("review_reason", sa.String(length=300), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("id", _BIGINT, autoincrement=True, nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], name="fk_svh_user_id"),
        sa.PrimaryKeyConstraint("id"),
    )
    with op.batch_alter_table("student_verification_history", schema=None) as batch_op:
        batch_op.create_index("ix_svh_user_created", ["user_id", "created_at"], unique=False)


def downgrade():
    op.drop_table("student_verification_history")