"""Keep the last few versions of each question, so an edit can be undone.

Capped per question rather than kept forever: the value is undoing a recent
mistake, and an uncapped history of full question bodies grows without bound.

Revision ID: a9b0c1d2e3f4
Revises: z8f9a0b1c2d3
"""
from alembic import op

revision = "a9b0c1d2e3f4"
down_revision = "z8f9a0b1c2d3"
branch_labels = None
depends_on = None


def upgrade():
    op.execute("""
    CREATE TABLE IF NOT EXISTS question_versions (
        id SERIAL PRIMARY KEY,
        question_id INTEGER NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
        snapshot JSONB NOT NULL,
        edited_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_qv_question ON question_versions(question_id, created_at DESC)")


def downgrade():
    op.execute("DROP TABLE IF EXISTS question_versions")