"""Add users.verified_at (email-verification timestamp).

Records when an account's email was verified. Pairs with the existing
``is_verified`` boolean, which the email-verification OTP flow now drives:
public registrations start unverified and confirm an emailed code before login.

Existing accounts predate verification and are treated as already verified, so
they keep working: any ``is_verified`` row missing a timestamp is backfilled to
its ``created_at``.

Revision ID: d9f2a4c6b8e1
Revises: c5e7a9b1d3f0
Create Date: 2026-07-04

"""
from alembic import op
import sqlalchemy as sa


revision = "d9f2a4c6b8e1"
down_revision = "c5e7a9b1d3f0"
branch_labels = None
depends_on = None


def upgrade():
    with op.batch_alter_table("users", schema=None) as batch_op:
        batch_op.add_column(sa.Column("verified_at", sa.DateTime(), nullable=True))

    # Backfill: existing verified accounts get a verified_at so the profile /
    # admin views show a sensible timestamp instead of "unknown".
    op.execute(
        "UPDATE users SET verified_at = created_at "
        "WHERE is_verified = 1 AND verified_at IS NULL"
    )


def downgrade():
    with op.batch_alter_table("users", schema=None) as batch_op:
        batch_op.drop_column("verified_at")