"""cloud_shards — add ingress routing metadata + cloud_shard_migration_status table.

Adds gateway_base_url, worker_name, d1_database_name to cloud_shards so the
ingress router can forward to the correct per-shard gateway deployment.

Adds cloud_shard_migration_status for fleet-wide D1 schema version tracking;
each shard's gateway reports its applied migration version after wrangler apply.

Revision ID: 053
Revises: 052
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import UUID as PGUUID

revision = "053"
down_revision = "052"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── cloud_shards: ingress routing metadata ────────────────────────────────

    op.add_column("cloud_shards", sa.Column("gateway_base_url", sa.Text(), nullable=True))
    op.add_column("cloud_shards", sa.Column("worker_name", sa.Text(), nullable=True))
    op.add_column("cloud_shards", sa.Column("d1_database_name", sa.Text(), nullable=True))

    # ── cloud_shard_migration_status ──────────────────────────────────────────

    op.create_table(
        "cloud_shard_migration_status",
        sa.Column("id", PGUUID(as_uuid=True), primary_key=True),
        sa.Column(
            "shard_id",
            PGUUID(as_uuid=True),
            sa.ForeignKey("cloud_shards.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("migration_version", sa.Text(), nullable=False),
        sa.Column("applied_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column(
            "status",
            sa.Text(),
            nullable=False,
            server_default="applied",
        ),
        sa.Column("error", sa.Text(), nullable=True),
        sa.Column("reported_by", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now()),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
        ),
        sa.UniqueConstraint(
            "shard_id", "migration_version",
            name="uq_cloud_shard_migration_shard_version",
        ),
    )
    op.create_index(
        "ix_cloud_shard_migration_status_shard_id",
        "cloud_shard_migration_status",
        ["shard_id"],
    )
    op.create_index(
        "ix_cloud_shard_migration_status_status",
        "cloud_shard_migration_status",
        ["status"],
    )


def downgrade() -> None:
    op.drop_table("cloud_shard_migration_status")
    op.drop_column("cloud_shards", "d1_database_name")
    op.drop_column("cloud_shards", "worker_name")
    op.drop_column("cloud_shards", "gateway_base_url")