"""Exams as real data, with a per-user active exam.

"Pediatrics Boards" was a hardcoded checkbox that filtered nothing. Exams are now
rows users can switch between, and question membership is a link table because a
question can count towards more than one exam.

Revision ID: v4b5c6d7e8f9
Revises: u3a4b5c6d7e8
"""
from alembic import op

revision = "v4b5c6d7e8f9"
down_revision = "u3a4b5c6d7e8"
branch_labels = None
depends_on = None


def upgrade():
    op.execute("""
    CREATE TABLE IF NOT EXISTS exams (
        id SERIAL PRIMARY KEY,
        slug VARCHAR(80) UNIQUE NOT NULL,
        name VARCHAR(160) NOT NULL,
        sort_order INTEGER NOT NULL DEFAULT 100,
        is_active INTEGER NOT NULL DEFAULT 1,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
    """)
    op.execute("""
    CREATE TABLE IF NOT EXISTS question_exam_links (
        id SERIAL PRIMARY KEY,
        question_id INTEGER NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
        exam_id INTEGER NOT NULL REFERENCES exams(id) ON DELETE CASCADE,
        CONSTRAINT uq_question_exam UNIQUE (question_id, exam_id)
    )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_question_exam_links_question_id ON question_exam_links(question_id)")
    op.execute("CREATE INDEX IF NOT EXISTS ix_question_exam_links_exam_id ON question_exam_links(exam_id)")
    op.execute("ALTER TABLE users ADD COLUMN IF NOT EXISTS active_exam_id INTEGER REFERENCES exams(id) ON DELETE SET NULL")

    op.execute("""
    INSERT INTO exams (slug, name, sort_order) VALUES
        ('pediatrics-boards', 'Pediatrics Boards', 10),
        ('usmle-step-2-ck', 'USMLE Step 2 CK', 20)
    ON CONFLICT (slug) DO NOTHING
    """)
    # Everything in the bank today was written for the paediatrics boards.
    op.execute("""
    INSERT INTO question_exam_links (question_id, exam_id)
    SELECT q.id, e.id FROM questions q
    CROSS JOIN exams e WHERE e.slug = 'pediatrics-boards'
    ON CONFLICT DO NOTHING
    """)


def downgrade():
    op.execute("ALTER TABLE users DROP COLUMN IF EXISTS active_exam_id")
    op.execute("DROP TABLE IF EXISTS question_exam_links")
    op.execute("DROP TABLE IF EXISTS exams")