"""Persist attempt mode and educator-maintained lab references."""
from alembic import op

revision = "d94a26b8f302"
down_revision = "c82d19e4a601"
branch_labels = None
depends_on = None


def upgrade():
    op.execute("ALTER TABLE quiz_attempts ADD COLUMN IF NOT EXISTS mode VARCHAR(10)")
    op.execute('''CREATE TABLE IF NOT EXISTS lab_reference_values (
        id SERIAL PRIMARY KEY,
        name VARCHAR(120) NOT NULL,
        "group" VARCHAR(60) NOT NULL,
        reference_range VARCHAR(250) NOT NULL,
        units VARCHAR(80) NOT NULL,
        age_group VARCHAR(120) NOT NULL,
        specimen VARCHAR(120) NOT NULL,
        source VARCHAR(500) NOT NULL,
        source_url VARCHAR(2000),
        is_published BOOLEAN NOT NULL DEFAULT FALSE,
        updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
        updated_at TIMESTAMP NOT NULL DEFAULT NOW()
    )''')


def downgrade():
    op.execute("DROP TABLE IF EXISTS lab_reference_values")
    op.execute("ALTER TABLE quiz_attempts DROP COLUMN IF EXISTS mode")