"""assistant_messages: one message per (conversation, seq)

Revision ID: 519
Revises: 518

``seq`` exists because two messages of one turn land in the same clock second,
so ``created_at`` alone cannot order a transcript — and an out-of-order
transcript is unusable as an audit record. The sequence was assigned from
``conversation.turn_count`` under a Redis turn lock, and the lock's own docstring
names this as what it protects.

But that lock **fails open** on a Redis fault, and so does the event-dedup
claim. During a Redis outage both gates wave two concurrent turns through, they
read the same ``turn_count``, and they write the same pair of ``seq`` values. The
table only had a non-unique index, so the database accepted it: the transcript
silently interleaved and nothing afterwards could tell which reply answered which
question.

This is the half of that hazard the database can own. Verified against production
before adding: 78 rows, **zero** duplicate ``(conversation_id, seq)`` pairs, so
the constraint applies with no cleanup. It replaces the plain index rather than
sitting beside it — a unique index serves the same ``(conversation_id, seq)``
lookups the old one did.
"""
from alembic import op

revision = "519"
down_revision = "518"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # CREATE before DROP, and the order is load-bearing. ``conversation_id``
    # carries a foreign key, and ``idx_assistant_msg_conv`` was the index MySQL
    # relied on to enforce it — dropping it first fails with errno 1553,
    # "Cannot drop index needed in a foreign key constraint". The new unique
    # index covers the same leading column, so once it exists the old one is
    # genuinely redundant and drops cleanly.
    op.create_unique_constraint(
        "uq_assistant_msg_conv_seq",
        "assistant_messages",
        ["conversation_id", "seq"],
    )
    op.drop_index("idx_assistant_msg_conv", table_name="assistant_messages")


def downgrade() -> None:
    # Mirror image: recreate the plain index before releasing the unique one,
    # for the same foreign-key reason.
    op.create_index(
        "idx_assistant_msg_conv",
        "assistant_messages",
        ["conversation_id", "seq"],
    )
    op.drop_constraint(
        "uq_assistant_msg_conv_seq", "assistant_messages", type_="unique",
    )