"""Add musehub_coord_records table for the coordination bus.

Agents on different machines push local coordination records (reservations,
intents, releases, heartbeats, dependencies, tasks, claims) to this table so
that distributed agent swarms can share state without filesystem access.

Key design choices:
- ``id`` is a monotonically increasing integer PK used as the SSE cursor —
  more reliable than timestamps for ordering within the same millisecond.
- ``(repo_id, kind, record_uuid)`` unique constraint enforces write-once
  semantics at the DB level (the service layer skips on IntegrityError).
- ``ix_coord_repo_id_cursor`` optimizes the primary pull query pattern:
  "give me all records for repo X with id > Y in insertion order".
- ``ix_coord_repo_kind_id`` optimizes filtered pulls:
  "give me all heartbeats for repo X with id > Y".
- ``ON DELETE CASCADE`` on the FK ensures coord records are removed when
  their parent repo is deleted.

Revision ID: 0010
Revises: 0009
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision: str = "0010"
down_revision: str = "0009"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "musehub_coord_records",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("repo_id", sa.String(36), nullable=False),
        sa.Column("kind", sa.String(32), nullable=False),
        sa.Column("record_uuid", sa.String(36), nullable=False),
        sa.Column("run_id", sa.String(255), nullable=False, server_default=""),
        sa.Column("payload", sa.JSON(), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(
            ["repo_id"],
            ["musehub_repos.repo_id"],
            name="fk_coord_repo_id",
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "repo_id", "kind", "record_uuid",
            name="uq_coord_repo_kind_uuid",
        ),
    )
    op.create_index(
        "ix_coord_repo_id_cursor",
        "musehub_coord_records",
        ["repo_id", "id"],
    )
    op.create_index(
        "ix_coord_repo_kind_id",
        "musehub_coord_records",
        ["repo_id", "kind", "id"],
    )
    # Index on repo_id alone for fast repo-scoped queries without kind filter.
    op.create_index(
        "ix_coord_records_repo_id",
        "musehub_coord_records",
        ["repo_id"],
    )


def downgrade() -> None:
    op.drop_index("ix_coord_records_repo_id", table_name="musehub_coord_records")
    op.drop_index("ix_coord_repo_kind_id", table_name="musehub_coord_records")
    op.drop_index("ix_coord_repo_id_cursor", table_name="musehub_coord_records")
    op.drop_table("musehub_coord_records")