"""add canonical message persistence ordering

Revision ID: 6f4c2d8a91b7
Revises: d3f8a1c6b904
Create Date: 2026-09-28

Adds a database-generated monotonic sequence to messages.

created_at remains the wall-clock timestamp.
sequence_no becomes the canonical persistence ordering key.
"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

from migrations.utils.safe_ddl import drop_column_if_exists, has_column

revision: str = "6f4c2d8a91b7"
down_revision: str | None = "d3f8a1c6b904"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None

TABLE = "messages"
COLUMN = "sequence_no"


def upgrade() -> None:
    if has_column(TABLE, COLUMN):
        return

    # MySQL requires AUTO_INCREMENT to be indexed. Message.id remains the
    # existing string primary key; sequence_no is a separate unique ordering
    # key generated by the database.
    op.execute(
        sa.text(
            "ALTER TABLE messages "
            "ADD COLUMN sequence_no BIGINT NOT NULL AUTO_INCREMENT "
            "COMMENT 'Canonical monotonic persistence order for conversation messages.', "
            "ADD UNIQUE INDEX ix_messages_sequence_no (sequence_no)"
        )
    )


def downgrade() -> None:
    drop_column_if_exists(TABLE, COLUMN)