"""An article can claim a category, and the claim keeps its links up to date

`question_article_links` stays the only thing anything reads. This table holds
the *reason* some of those rows exist: "the Cardiology article covers the
Cardiology questions" is a standing statement, not a snapshot of who was filed
where in September.

Rows are materialised from a claim when it is staked, and again for a question
the moment it is filed into a claimed category. One source of truth to read,
one place that writes it.

Revision ID: k1b2c3d4e5f6
Revises: j0a1b2c3d4e5
"""
import sqlalchemy as sa
from alembic import op

revision = "k1b2c3d4e5f6"
down_revision = "j0a1b2c3d4e5"
branch_labels = None
depends_on = None


def upgrade() -> None:
    inspector = sa.inspect(op.get_bind())
    # `Base.metadata.create_all()` runs at startup, so a fresh deploy may have
    # built this already.
    if "article_topic_claims" in inspector.get_table_names():
        return
    op.create_table(
        "article_topic_claims",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("article_id", sa.Integer(),
                  sa.ForeignKey("articles.id", ondelete="CASCADE"), nullable=False),
        sa.Column("category_id", sa.Integer(),
                  sa.ForeignKey("question_categories.id", ondelete="CASCADE"), nullable=False),
        sa.Column("section_id", sa.String(length=64), nullable=True),
        sa.Column("include_subtopics", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.Column("user_id", sa.Integer(),
                  sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=True),
        sa.UniqueConstraint("article_id", "category_id", "section_id",
                            name="uq_article_topic_claim"),
    )
    op.create_index("ix_article_topic_claims_article_id", "article_topic_claims", ["article_id"])
    op.create_index("ix_article_topic_claims_category_id", "article_topic_claims", ["category_id"])


def downgrade() -> None:
    op.drop_table("article_topic_claims")