"""An article that was ever published goes to the trash instead of vanishing

Three columns on `articles`:

* `first_published_at` — stamped the first time it is published and never
  cleared. It is what decides whether deleting is reversible.
* `deleted_at` / `deleted_by` — the trash itself.

The rule it exists for: a draft nobody ever saw is deleted outright, because
there is nothing to restore and a trash full of abandoned stubs is a second
list to maintain. Anything the world has seen — anything with a
`first_published_at` — is only ever marked, because somewhere there is a
learner's note against one of its sections, a question linked to it, and a link
somebody sent to a colleague.

Backfilled from `reviewed_at` where an article is published now: the exact
first-publication moment is not recorded anywhere, and any published article
must have a non-null stamp or the rule reads it as a never-published draft and
deletes it for good.

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

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


COLUMNS = {
    "first_published_at": sa.Column("first_published_at", sa.DateTime(), nullable=True),
    "deleted_at": sa.Column("deleted_at", sa.DateTime(), nullable=True),
    "deleted_by": sa.Column("deleted_by", sa.Integer(), nullable=True),
}


def upgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    # `Base.metadata.create_all()` still runs at startup, so a fresh deploy may
    # already have these. Idempotent by inspection rather than by try/except.
    existing = {column["name"] for column in inspector.get_columns("articles")}
    for name, column in COLUMNS.items():
        if name not in existing:
            op.add_column("articles", column)

    indexes = {index["name"] for index in inspector.get_indexes("articles")}
    if "ix_articles_deleted_at" not in indexes:
        op.create_index("ix_articles_deleted_at", "articles", ["deleted_at"])

    op.execute("""
        UPDATE articles
           SET first_published_at = COALESCE(reviewed_at, updated_at, created_at)
         WHERE status = 'published' AND first_published_at IS NULL
    """)


def downgrade() -> None:
    op.drop_index("ix_articles_deleted_at", table_name="articles")
    for name in COLUMNS:
        op.drop_column("articles", name)