"""add session_epoch to public.users so a password reset can evict live sessions

Security audit 2026-08-13, finding #6b. Password reset previously invalidated
nothing: remember-me cookies last 30 days, so an attacker holding a stolen cookie
kept full access after the victim "secured" the account. The epoch is embedded in
the Flask-Login session id (User.get_id) and re-checked in load_user, so bumping
it invalidates every issued cookie for that user at once.

Defaults to 0 for existing rows — no forced logout on deploy.

Revision ID: sep001
Revises: gcl001
Create Date: 2026-08-15
"""
from alembic import op
import sqlalchemy as sa

revision = 'sep001'
down_revision = 'gcl001'
branch_labels = None
depends_on = None


def _public_column_exists(conn, column):
    return conn.execute(sa.text(
        "SELECT 1 FROM information_schema.columns "
        "WHERE table_schema='public' AND table_name='users' "
        "AND column_name=:c"
    ), {"c": column}).fetchone() is not None


def upgrade():
    # Guard: this file is replayed by every fresh tenant's migration run, so it
    # must be idempotent (same pattern as vat001).
    conn = op.get_bind()
    if not _public_column_exists(conn, 'session_epoch'):
        op.add_column(
            'users',
            sa.Column('session_epoch', sa.Integer(), nullable=False, server_default='0'),
            schema='public',
        )


def downgrade():
    op.drop_column('users', 'session_epoch', schema='public')