"""Article CMS: review workflow, revisions, slug history, references.

Revision ID: b6c7d8e9f0a1
Revises: a5b6c7d8e9f0
"""
import sqlalchemy as sa
from alembic import op
from sqlalchemy import inspect

revision = "b6c7d8e9f0a1"
down_revision = "a5b6c7d8e9f0"
branch_labels = None
depends_on = None


def _has_table(name: str) -> bool:
    return name in inspect(op.get_bind()).get_table_names()


def _has_column(table: str, column: str) -> bool:
    return column in {c["name"] for c in inspect(op.get_bind()).get_columns(table)}


def upgrade():
    # `Base.metadata.create_all()` runs at startup as a fallback for fresh
    # deploys, so on a running box it will already have created whatever the
    # models describe. This migration is the record of the change and must apply
    # cleanly either way, so every step checks first.
    # Review sits between draft and published: generated medical writing that
    # nobody has read is exactly what must not reach a learner.
    for column in (
        sa.Column("submitted_at", sa.DateTime, nullable=True),
        sa.Column("reviewed_at", sa.DateTime, nullable=True),
        sa.Column("reviewed_by", sa.Integer,
                  sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
        # Where the facts came from. A list of sources, not in-text markers.
        sa.Column("references_json", sa.JSON, nullable=True),
        # What produced it, so machine-written drafts stay visibly distinct from
        # an educator's own writing for as long as they need to be.
        sa.Column("generated_by", sa.String(80), nullable=True),
        sa.Column("generated_at", sa.DateTime, nullable=True),
    ):
        if not _has_column("articles", column.name):
            op.add_column("articles", column)

    if not _has_table("article_revisions"):
        op.create_table(
            "article_revisions",
            sa.Column("id", sa.Integer, primary_key=True),
            sa.Column("article_id", sa.Integer,
                      sa.ForeignKey("articles.id", ondelete="CASCADE"), nullable=False, index=True),
            sa.Column("title", sa.String(300), nullable=False),
            sa.Column("summary", sa.Text, nullable=True),
            sa.Column("content", sa.Text, nullable=True),
            sa.Column("sections", sa.JSON, nullable=False),
            sa.Column("references_json", sa.JSON, nullable=True),
            sa.Column("status", sa.String(20), nullable=True),
            sa.Column("note", sa.String(200), nullable=True),
            sa.Column("created_by", sa.Integer,
                      sa.ForeignKey("users.id", ondelete="SET NULL"), nullable=True),
            sa.Column("created_at", sa.DateTime, server_default=sa.func.now()),
        )

    # Every slug an article has ever had, so a rename cannot orphan the
    # cross-references pointing at the old one.
    if not _has_table("article_slugs"):
        op.create_table(
            "article_slugs",
            sa.Column("id", sa.Integer, primary_key=True),
            sa.Column("slug", sa.String(120), nullable=False, unique=True, index=True),
            sa.Column("article_id", sa.Integer,
                      sa.ForeignKey("articles.id", ondelete="CASCADE"), nullable=False, index=True),
            sa.Column("created_at", sa.DateTime, server_default=sa.func.now()),
        )
    # Seed it with what every article is called today, or the history starts
    # empty and the first rename still breaks the links it should have caught.
    op.execute("""
        INSERT INTO article_slugs (slug, article_id)
        SELECT slug, id FROM articles
        ON CONFLICT (slug) DO NOTHING
    """)


def downgrade():
    op.drop_table("article_slugs")
    op.drop_table("article_revisions")
    for column in ("generated_at", "generated_by", "references_json",
                   "reviewed_by", "reviewed_at", "submitted_at"):
        if _has_column("articles", column):
            op.drop_column("articles", column)