"""`deferred` joins the proposal statuses: rian's third answer, and the note that feeds the next
pass (Stream K11.4; REVIEW-PROCESS.md v4 §2, rian's ruling of 17 Sep).

Schema-only, one CHECK list widened. Before this a row could only be approved or
rejected-with-a-sentence, and **nothing ever read the sentence**: every pass started from zero and
asked the set/coffret question identically forever. A judgement rian has not settled -- his own
worked example -- had nowhere to live except "no", which is a different and wrong answer.

`deferred` reuses the columns a resolution already has (`resolution_note`, `resolved_at`,
`resolved_by`), so no column is added: the note IS `resolution_note`. A deferred row leaves the
queue, is never applied, and is re-proposed only by a pass that has read its note
(`proposals.deferred_notes`).

`ck_proposals_decided` is untouched and still holds: a deferred row has no `decision_id`, and
`(status = 'approved') = (decision_id IS NOT NULL)` reads false = false.

The downgrade narrows the list again, so it first resolves any deferred row back to `open` and
clears the note it can no longer explain -- otherwise the constraint could not be created and the
downgrade would fail on exactly the hosts that had used the feature.

Revision ID: e3f4a5b6c7d8
Revises: d2e3f4a5b6c7
Create Date: 2026-09-17
"""

import sqlalchemy as sa

from alembic import op

revision = "e3f4a5b6c7d8"
down_revision = "d2e3f4a5b6c7"
branch_labels = None
depends_on = None

BEFORE = ("open", "approved", "rejected", "withdrawn", "stale", "parked")
AFTER = (*BEFORE, "deferred")


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


def upgrade() -> None:
    op.drop_constraint("ck_proposals_status", "proposals", type_="check")
    op.create_check_constraint("ck_proposals_status", "proposals", _in("status", AFTER))


def downgrade() -> None:
    op.execute("UPDATE proposals SET status = 'open', resolution_note = NULL, "
               "resolved_at = NULL, resolved_by = NULL WHERE status = 'deferred'")
    op.drop_constraint("ck_proposals_status", "proposals", type_="check")
    op.create_check_constraint("ck_proposals_status", "proposals", _in("status", BEFORE))