"""add guarded catalog release registry

Revision ID: f3a7c9d52b18
Revises: e8c6a4b2910f
Create Date: 2026-08-16 23:30:00
"""

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = "f3a7c9d52b18"
down_revision: Union[str, Sequence[str], None] = "e8c6a4b2910f"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        "catalog_releases",
        sa.Column("id", sa.String(length=128), nullable=False),
        sa.Column("product_id", sa.String(length=128), nullable=False),
        sa.Column("revision", sa.Integer(), nullable=False),
        sa.Column("document_json", sa.JSON(), nullable=False),
        sa.Column("document_digest", sa.LargeBinary(length=32), nullable=False),
        sa.Column("status", sa.String(length=32), nullable=False),
        sa.Column("staged_by_user_id", sa.String(length=128), nullable=False),
        sa.Column("staged_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("published_by_user_id", sa.String(length=128), nullable=True),
        sa.Column("published_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("retired_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.DateTime(timezone=True),
            server_default=sa.func.now(),
            nullable=False,
        ),
        sa.CheckConstraint(
            "revision >= 1",
            name="ck_catalog_releases_revision",
        ),
        sa.CheckConstraint(
            "status IN ('staged', 'active', 'retired')",
            name="ck_catalog_releases_status",
        ),
        sa.CheckConstraint(
            "(status = 'staged' AND published_by_user_id IS NULL "
            "AND published_at IS NULL AND retired_at IS NULL) OR "
            "(status = 'active' AND published_by_user_id IS NOT NULL "
            "AND published_at IS NOT NULL AND retired_at IS NULL) OR "
            "(status = 'retired' AND published_by_user_id IS NOT NULL "
            "AND published_at IS NOT NULL AND retired_at IS NOT NULL)",
            name="ck_catalog_releases_lifecycle",
        ),
        sa.ForeignKeyConstraint(
            ["published_by_user_id"],
            ["users.id"],
        ),
        sa.ForeignKeyConstraint(
            ["staged_by_user_id"],
            ["users.id"],
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "product_id",
            "revision",
            name="uq_catalog_releases_product_revision",
        ),
    )
    op.create_index(
        "ix_catalog_releases_product_revision",
        "catalog_releases",
        ["product_id", "revision"],
        unique=False,
    )
    op.create_index(
        "uq_catalog_releases_active_product",
        "catalog_releases",
        ["product_id"],
        unique=True,
        sqlite_where=sa.text("status = 'active'"),
        postgresql_where=sa.text("status = 'active'"),
    )


def downgrade() -> None:
    op.drop_index(
        "uq_catalog_releases_active_product",
        table_name="catalog_releases",
    )
    op.drop_index(
        "ix_catalog_releases_product_revision",
        table_name="catalog_releases",
    )
    op.drop_table("catalog_releases")