"""The precedent register, and the proposal that names its precedent (Stream K12.3; the plan
`.logs/planning/review-process-golive-2026-09-18.md` B2, B5, B6).

Schema only. A new table `precedents`: one row per kind of judgement a person has answered, with
the question, the answer, the person's note verbatim, the first case, the union of the shapes
decided under it, and the counts the attention floor reads. On `proposals`: the slug a row names
(`precedent`), whether it sets or follows it, the lead it follows, the shape the loader computed
from its cited text, and what in that shape the register had never seen (`unlike`). On
`proposal_passes`: the survey a pass wrote before proposing, its verdict, and the listed-words
fingerprint it stamped. `precedent` joins the entity types so an overturn is a ledger decision.

Why: attention was a label that gated nothing and the only precedent record was a set of keys
computed from words in reason text. Under bulk approval a new kind of question the pass
under-rated sailed through silently, and nothing independent of the pass could catch it.

Revision ID: k12a1b2c3d4e
Revises: c2d3e4f5a6b7
Create Date: 2026-09-18
"""

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision = "k12a1b2c3d4e"
down_revision = "c2d3e4f5a6b7"
branch_labels = None
depends_on = None

BEFORE = ("brand", "product_line", "product_variant", "listing", "attribute_wording", "suggestion", "place")
AFTER = (*BEFORE, "precedent")
JSONB = postgresql.JSONB(astext_type=sa.Text())


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


def upgrade() -> None:
    op.create_table(
        "precedents",
        sa.Column("id", sa.BigInteger(), primary_key=True),
        sa.Column("uid", sa.Uuid(as_uuid=True), nullable=False),
        sa.Column("slug", sa.String(120), nullable=False),
        sa.Column("vertical", sa.String(40)),
        sa.Column("question", sa.Text()),
        sa.Column("answer", sa.Text()),
        sa.Column("note", sa.Text()),
        sa.Column("first_case", sa.String(500)),
        sa.Column("set_by", sa.String(64)),
        sa.Column("set_at", sa.DateTime(timezone=True)),
        sa.Column("decision_id", sa.BigInteger(), sa.ForeignKey("decisions.id", name="fk_precedents_decision")),
        sa.Column("status", sa.String(12), nullable=False, server_default="active"),
        sa.Column("overturned_by", sa.String(64)),
        sa.Column("overturned_at", sa.DateTime(timezone=True)),
        sa.Column("overturn_note", sa.Text()),
        sa.Column("shape", JSONB, nullable=False, server_default="{}"),
        sa.Column("decisions_count", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("streak", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("resets_count", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
        sa.UniqueConstraint("uid", name="uq_precedents_uid"),
        sa.UniqueConstraint("slug", name="uq_precedents_slug"),
        sa.CheckConstraint(_in("status", ("active", "overturned")), name="ck_precedents_status"),
    )
    op.create_index("ix_precedents_status", "precedents", ["status", "vertical"])
    op.add_column("proposals", sa.Column("precedent", sa.String(120)))
    op.add_column("proposals", sa.Column("precedent_status", sa.String(8)))
    op.add_column("proposals", sa.Column("follows", sa.String(500)))
    op.add_column("proposals", sa.Column("shape", JSONB))
    op.add_column("proposals", sa.Column("unlike", JSONB))
    op.create_index("ix_proposals_precedent", "proposals", ["brand_slug", "precedent"], postgresql_where=sa.text("status = 'open'"))
    op.add_column("proposal_passes", sa.Column("survey", JSONB))
    op.add_column("proposal_passes", sa.Column("verdict", sa.String(12)))
    op.add_column("proposal_passes", sa.Column("fingerprint", sa.String(64)))
    for table in ("proposals", "decisions"):
        op.drop_constraint(f"ck_{table}_entity_type", table, type_="check")
        op.create_check_constraint(f"ck_{table}_entity_type", table, _in("entity_type", AFTER))


def downgrade() -> None:
    # A precedent decision cannot survive the narrower list; the ledger is append-only, so the
    # downgrade refuses rather than deletes when any exists.
    bind = op.get_bind()
    n = bind.execute(sa.text("SELECT count(*) FROM decisions WHERE entity_type = 'precedent'")).scalar()
    if n:
        raise RuntimeError(f"{n} precedent decision(s) in the ledger; the ledger is append-only, so this downgrade cannot run")
    for table in ("proposals", "decisions"):
        op.drop_constraint(f"ck_{table}_entity_type", table, type_="check")
        op.create_check_constraint(f"ck_{table}_entity_type", table, _in("entity_type", BEFORE))
    op.drop_column("proposal_passes", "fingerprint")
    op.drop_column("proposal_passes", "verdict")
    op.drop_column("proposal_passes", "survey")
    op.drop_index("ix_proposals_precedent", table_name="proposals")
    for column in ("unlike", "shape", "follows", "precedent_status", "precedent"):
        op.drop_column("proposals", column)
    op.drop_index("ix_precedents_status", table_name="precedents")
    op.drop_table("precedents")