"""Study plans made of numbered question blocks.

The PREP sets were loose admin-generated quizzes. A plan groups them into ordered
blocks a learner works through. Blocks store their question ids explicitly rather
than a filter, so a block stays the same set every time it is opened — a plan you
are working through must not reshuffle under you.

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

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


def upgrade():
    op.execute("""
    CREATE TABLE IF NOT EXISTS study_plans (
        id SERIAL PRIMARY KEY,
        slug VARCHAR(120) UNIQUE NOT NULL,
        name VARCHAR(200) NOT NULL,
        description TEXT,
        exam_id INTEGER REFERENCES exams(id) ON DELETE SET NULL,
        kind VARCHAR(20) NOT NULL DEFAULT 'set',
        sort_order INTEGER NOT NULL DEFAULT 100,
        is_published INTEGER NOT NULL DEFAULT 1,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
    """)
    op.execute("""
    CREATE TABLE IF NOT EXISTS study_plan_blocks (
        id SERIAL PRIMARY KEY,
        plan_id INTEGER NOT NULL REFERENCES study_plans(id) ON DELETE CASCADE,
        position INTEGER NOT NULL,
        title VARCHAR(200) NOT NULL,
        question_ids JSONB NOT NULL DEFAULT '[]'::jsonb,
        CONSTRAINT uq_plan_block UNIQUE (plan_id, position)
    )
    """)
    op.execute("CREATE INDEX IF NOT EXISTS ix_plan_blocks_plan ON study_plan_blocks(plan_id)")
    op.execute("""
    CREATE TABLE IF NOT EXISTS study_plan_block_progress (
        id SERIAL PRIMARY KEY,
        block_id INTEGER NOT NULL REFERENCES study_plan_blocks(id) ON DELETE CASCADE,
        user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        quiz_id INTEGER REFERENCES quizzes(id) ON DELETE SET NULL,
        completed_at TIMESTAMP,
        CONSTRAINT uq_block_progress UNIQUE (block_id, user_id)
    )
    """)


def downgrade():
    op.execute("DROP TABLE IF EXISTS study_plan_block_progress")
    op.execute("DROP TABLE IF EXISTS study_plan_blocks")
    op.execute("DROP TABLE IF EXISTS study_plans")