"""rules gain an edit history — rule_versions (milestone 323 step 1)

Revision ID: 0093
Revises: 0092
Create Date: 2026-08-29

The sibling `note_versions` has had for a long time. A note's every meaningful
edit is snapshotted, and the design-system note calls that history "the
changelog". A RULE — which binds behaviour on every session that loads it —
had nothing: an edit destroyed what it used to say, with no record anywhere.

Rescoping rule 79 on 2026-08-29 is what surfaced it. The superseded statement
had to be hand-copied into a task log to survive the edit (#3237), which is
not a process, it is a person remembering. The more consequential record had
the weaker protection.

Three things are deliberately NOT copied from note_versions, and each is a
guard that exists there for a reason that does not hold here:

- **No pruning, and no MAX_VERSIONS.** That cap defends against note autosave
  filling every slot. Rules have no autosave; every edit is a deliberate
  update_rule. A rule is edited a handful of times in its life, and capping
  invites losing the one edit somebody needed.
- **No pin columns.** `pin_kind`/`pin_label` exist so a note's version can
  survive that pruning. With nothing pruning, a pin protects a row that was
  never at risk.
- **No minimum interval.** 300 seconds between snapshots is also an autosave
  defence; here it would only ever discard a second deliberate edit.

`user_id` is the ACTOR rather than the owner, and is SET NULL rather than
CASCADE: deleting a user must not erase the history of the rules they edited.
The edit still happened and the rule still binds because of it.

No CHECK constraint, so rule 36 does not apply. Nothing is backfilled — a
migration cannot invent the text a rule used to have, and inventing "the
current text, as of now" would be worse than an empty history, because it
would look like a record of an edit that never occurred.
"""
import sqlalchemy as sa
from alembic import op

revision = "0093"
down_revision = "0092"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "rule_versions",
        sa.Column("id", sa.BigInteger(), primary_key=True),
        sa.Column(
            "rule_id",
            sa.BigInteger(),
            sa.ForeignKey("rules.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "user_id",
            sa.BigInteger(),
            sa.ForeignKey("users.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("title", sa.Text(), nullable=False, server_default=""),
        sa.Column("statement", sa.Text(), nullable=False, server_default=""),
        sa.Column("why", sa.Text(), nullable=True),
        sa.Column("how_to_apply", sa.Text(), nullable=True),
        sa.Column("when_to_apply", sa.Text(), nullable=True),
        sa.Column("tier", sa.Text(), nullable=True),
        sa.Column("verify_with", sa.Text(), nullable=True),
        sa.Column("expires_when", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
    )
    # The only query this table serves is "the history of THIS rule, newest
    # first" — unlike 0092's columns, which are read by an operator-initiated
    # sweep over the whole set. Every read here is keyed on rule_id, so the
    # index earns its write cost immediately rather than on a hunch.
    op.create_index("ix_rule_versions_rule_id", "rule_versions", ["rule_id"])


def downgrade() -> None:
    op.drop_index("ix_rule_versions_rule_id", table_name="rule_versions")
    op.drop_table("rule_versions")