"""Articles belong to exams; exams carry a published blueprint.

Three gaps closed at once. An article could only reach an exam by inference
through its category, which cannot say that the same article belongs to a basic
science step and a clinical one with different views in each. And nothing
recorded that a real paper is 12% preventive care and 2% rheumatology, so a
forty-question block was forty coin flips.

Revision ID: a7b8c9d0e1f2
Revises: f6a7b8c9d0e1
"""
import sqlalchemy as sa
from alembic import op

revision = "a7b8c9d0e1f2"
down_revision = "f6a7b8c9d0e1"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "article_exam_links",
        sa.Column("id", sa.Integer(), primary_key=True, index=True),
        sa.Column("article_id", sa.Integer(), sa.ForeignKey("articles.id", ondelete="CASCADE"), nullable=False),
        sa.Column("exam_id", sa.Integer(), sa.ForeignKey("exams.id", ondelete="CASCADE"), nullable=False),
        sa.UniqueConstraint("article_id", "exam_id", name="uq_article_exam"),
    )
    op.create_index("ix_article_exam_links_article_id", "article_exam_links", ["article_id"])
    op.create_index("ix_article_exam_links_exam_id", "article_exam_links", ["exam_id"])

    op.create_table(
        "exam_blueprints",
        sa.Column("id", sa.Integer(), primary_key=True, index=True),
        sa.Column("exam_id", sa.Integer(), sa.ForeignKey("exams.id", ondelete="CASCADE"), nullable=False),
        sa.Column("parent_id", sa.Integer(), sa.ForeignKey("exam_blueprints.id", ondelete="CASCADE"), nullable=True),
        sa.Column("code", sa.String(16), nullable=False),
        sa.Column("title", sa.String(240), nullable=False),
        sa.Column("weight", sa.Numeric(5, 2), nullable=True),
        sa.Column("sort_order", sa.Integer(), server_default="0"),
        sa.Column("created_at", sa.DateTime(), nullable=True),
        sa.UniqueConstraint("exam_id", "code", name="uq_exam_blueprint_code"),
    )
    op.create_index("ix_exam_blueprints_exam_id", "exam_blueprints", ["exam_id"])
    op.create_index("ix_exam_blueprints_parent_id", "exam_blueprints", ["parent_id"])

    op.create_table(
        "blueprint_category_links",
        sa.Column("id", sa.Integer(), primary_key=True, index=True),
        sa.Column("blueprint_id", sa.Integer(), sa.ForeignKey("exam_blueprints.id", ondelete="CASCADE"), nullable=False),
        sa.Column("category_id", sa.Integer(), sa.ForeignKey("question_categories.id", ondelete="CASCADE"), nullable=False),
        sa.UniqueConstraint("blueprint_id", "category_id", name="uq_blueprint_category"),
    )
    op.create_index("ix_blueprint_category_links_blueprint_id", "blueprint_category_links", ["blueprint_id"])
    op.create_index("ix_blueprint_category_links_category_id", "blueprint_category_links", ["category_id"])


def downgrade() -> None:
    op.drop_table("blueprint_category_links")
    op.drop_table("exam_blueprints")
    op.drop_table("article_exam_links")