"""canonical_systems — the global area vocabulary, promoted from a constant
to a table (milestone 307 step 1, decision note 3026)

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

The eight standard area names already existed as `STANDARD_SYSTEMS`, a tuple in
services/systems.py that milestone 297 seeds into a project at inception. A
constant cannot be referenced: a rule that applies across projects has nothing
to point at, because `systems.project_id` is NOT NULL and a family rule cannot
be chained to one project's row. This makes the vocabulary a table so it can be
a foreign key, and adds the nullable `systems.canonical_id` that maps a
project's local System onto it.

Deliberately no `user_id`: the catalog is GLOBAL so a shared project inherits
the vocabulary rather than re-earning it. `record_systems` is untouched — it
joins note_id/system_id and never sees this table, so no association data
moves, and no System's own `name` is rewritten.

The seed rows are written here verbatim rather than imported from the service:
a migration is a historical record and must keep running unchanged after the
service's list moves on.
"""
import sqlalchemy as sa
from alembic import op

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


# (name, slug, description) — the milestone-297 vocabulary, with the slug the
# service computes (canonical_slug: lowercase, "&" -> "and", non-alphanumerics
# collapsed to "-"). Charters stay generic on purpose: a project refines its
# own System's description, never this one. Nothing here names an app, a repo,
# a vendor or a house convention — the catalog ships to every install (rule 115).
_SEED = (
    ("CI & Release", "ci-and-release",
     "How the project is verified and shipped: pipelines, runners, image/artifact builds, release tagging and rollback."),
    ("Auth & Access", "auth-and-access",
     "Who may do what: identity, sessions/tokens, permissions and the scoping of every read and write to the right users."),
    ("Data Model & Storage", "data-model-and-storage",
     "What is stored and how it is shaped: the schema, migrations, serialisation and the services that own a table's lifecycle."),
    ("API Surface", "api-surface",
     "The doors into the capability: HTTP routes, tool/RPC surfaces, request parsing, error envelopes and their contracts."),
    ("UI & Design", "ui-and-design",
     "What people see and touch: views, components, client state, and the design tokens/recipes they are built from."),
    ("Import & Export", "import-and-export",
     "Data crossing the boundary: backups, exports, imports, sync with other systems, file formats."),
    ("Background Jobs", "background-jobs",
     "Work that runs without a request: schedulers, queues, periodic ticks, retention and maintenance."),
    ("Observability", "observability",
     "How the system reports on itself: logging, metrics, audit trails, health and diagnostics."),
)


def upgrade() -> None:
    canonical_systems = op.create_table(
        "canonical_systems",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("name", sa.Text(), nullable=False),
        sa.Column("slug", sa.Text(), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("order_index", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.text("now()")),
        sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("deleted_batch_id", sa.Text(), nullable=True),
    )
    # Unique among LIVE rows only, so a soft-deleted entry doesn't block
    # recreating or restoring the same area (the rules/topics convention).
    op.create_index(
        "uq_canonical_systems_slug", "canonical_systems", ["slug"],
        unique=True, postgresql_where=sa.text("deleted_at IS NULL"),
    )
    op.bulk_insert(
        canonical_systems,
        [
            {"name": name, "slug": slug, "description": description, "order_index": index}
            for index, (name, slug, description) in enumerate(_SEED)
        ],
    )

    op.add_column(
        "systems",
        sa.Column("canonical_id", sa.Integer(), nullable=True),
    )
    # SET NULL, not CASCADE: retiring a catalog entry must never delete a
    # project's System along with it.
    op.create_foreign_key(
        "fk_systems_canonical_id", "systems", "canonical_systems",
        ["canonical_id"], ["id"], ondelete="SET NULL",
    )
    op.create_index("ix_systems_canonical_id", "systems", ["canonical_id"])

    # Existing Systems are left UNMAPPED on purpose. An exact-slug match would
    # be safe, but a near miss ("CI & runners" vs "CI & Release") is a judgment
    # call — those go through the propose/confirm path so a human approves each
    # one, rather than being decided by a migration nobody reviews.


def downgrade() -> None:
    op.drop_index("ix_systems_canonical_id", table_name="systems")
    op.drop_constraint("fk_systems_canonical_id", "systems", type_="foreignkey")
    op.drop_column("systems", "canonical_id")
    op.drop_index("uq_canonical_systems_slug", table_name="canonical_systems")
    op.drop_table("canonical_systems")