"""rules gain a trigger, a tier, canon tags and typed edges (milestone 307
step 3, decision note 3026)

Revision ID: 0088
Revises: 0087
Create Date: 2026-08-26

A rule could not say WHEN it applies, WHICH area it is about, or WHAT other
rule it belongs with. All three were being written as prose instead — a
project's System description restating rule text, a rule's `why` naming the
note that caused it, and two halves of one shape merged into a single row
because either could surface without the other.

Four additions, each replacing something that was already being said in words:

- `when_to_apply` — the trigger. Nullable HERE and required at the service
  layer, because existing rules have none and a migration cannot invent one.
- `tier` — `always_on` (preloaded, as everything is today) or `conditional`
  (reachable, surfaced when its trigger fires). Defaults to `always_on`, so
  this migration changes NOTHING about which rules bind: an install upgrades
  and every rule keeps arriving exactly as it did.
- `arose_from_id` — the record that caused the rule, the edge notes and tasks
  already have.
- `rule_systems` / `rule_relations` — the canon tag and the typed edges.
"""
import sqlalchemy as sa
from alembic import op

revision = "0088"
down_revision = "0087"
branch_labels = None
depends_on = None

# Kept in one place so upgrade and the CHECK agree by construction (rule 36:
# a whitelisted value means DROP + ADD CONSTRAINT in the same migration —
# there is no prior constraint here, so the pair is created together).
_TIERS = ("always_on", "conditional")
_RELATION_KINDS = ("co_surfaces", "overrides", "elaborates")


def _in_list(column: str, values: tuple[str, ...]) -> str:
    return f"{column} IN (" + ", ".join(f"'{v}'" for v in values) + ")"


def upgrade() -> None:
    op.add_column("rules", sa.Column("when_to_apply", sa.Text(), nullable=True))
    op.add_column(
        "rules",
        sa.Column("tier", sa.Text(), nullable=False, server_default="always_on"),
    )
    op.create_check_constraint("ck_rules_tier", "rules", _in_list("tier", _TIERS))

    # SET NULL, not CASCADE: the record that prompted a rule can be trashed
    # without taking the rule with it — provenance is a claim about history,
    # and losing the source does not repeal the rule.
    op.add_column("rules", sa.Column("arose_from_id", sa.BigInteger(), nullable=True))
    op.create_foreign_key(
        "fk_rules_arose_from_id", "rules", "notes",
        ["arose_from_id"], ["id"], ondelete="SET NULL",
    )

    # Which global AREA a rule is about. Points at the canonical catalog, never
    # at a project's `systems` row — a rule that spans projects cannot be
    # chained to one project's vocabulary (0087).
    op.create_table(
        "rule_systems",
        sa.Column("rule_id", sa.BigInteger(), sa.ForeignKey("rules.id", ondelete="CASCADE"), primary_key=True),
        sa.Column("canonical_id", sa.Integer(), sa.ForeignKey("canonical_systems.id", ondelete="CASCADE"), primary_key=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
    )
    op.create_index("ix_rule_systems_canonical_id", "rule_systems", ["canonical_id"])

    # Typed edges between rules. Each kind exists because its absence forced a
    # workaround: co_surfaces (merging two rules into one row), overrides (a
    # stricter project rule written as a duplicate), elaborates (a local
    # addendum sitting beside its parent with nothing to say it is one).
    op.create_table(
        "rule_relations",
        sa.Column("id", sa.BigInteger(), primary_key=True),
        sa.Column("from_rule_id", sa.BigInteger(), sa.ForeignKey("rules.id", ondelete="CASCADE"), nullable=False),
        sa.Column("to_rule_id", sa.BigInteger(), sa.ForeignKey("rules.id", ondelete="CASCADE"), nullable=False),
        sa.Column("kind", sa.Text(), nullable=False),
        sa.Column("note", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.CheckConstraint(_in_list("kind", _RELATION_KINDS), name="ck_rule_relations_kind"),
        # A rule cannot relate to itself, and one pair carries a given kind
        # once — a second row would surface the same rule twice.
        sa.CheckConstraint("from_rule_id <> to_rule_id", name="ck_rule_relations_not_self"),
        sa.UniqueConstraint("from_rule_id", "to_rule_id", "kind", name="uq_rule_relations_edge"),
    )
    op.create_index("ix_rule_relations_from", "rule_relations", ["from_rule_id"])
    op.create_index("ix_rule_relations_to", "rule_relations", ["to_rule_id"])


def downgrade() -> None:
    op.drop_index("ix_rule_relations_to", table_name="rule_relations")
    op.drop_index("ix_rule_relations_from", table_name="rule_relations")
    op.drop_table("rule_relations")
    op.drop_index("ix_rule_systems_canonical_id", table_name="rule_systems")
    op.drop_table("rule_systems")
    op.drop_constraint("fk_rules_arose_from_id", "rules", type_="foreignkey")
    op.drop_column("rules", "arose_from_id")
    op.drop_constraint("ck_rules_tier", "rules", type_="check")
    op.drop_column("rules", "tier")
    op.drop_column("rules", "when_to_apply")
