"""Prevent concurrent deploys for one project environment.

Revision ID: 201
Revises: 200
"""
from alembic import op

revision = "201"
down_revision = "200"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        WITH ranked AS (
            SELECT id, row_number() OVER (
                PARTITION BY project_id, environment
                ORDER BY created_at DESC, id DESC
            ) AS rn
            FROM project_deploys
            WHERE status IN ('pending', 'deploying')
        )
        UPDATE project_deploys AS deploy
        SET status = 'failed',
            deployed_at = COALESCE(deploy.deployed_at, NOW()),
            deployment_metadata = COALESCE(deploy.deployment_metadata, '{}'::jsonb)
                || jsonb_build_object(
                    'error', 'superseded_by_active_deploy_guard',
                    'superseded_at', NOW()
                )
        FROM ranked
        WHERE deploy.id = ranked.id AND ranked.rn > 1
        """
    )
    op.execute(
        """
        CREATE UNIQUE INDEX uq_project_deploy_active
        ON project_deploys (project_id, environment)
        WHERE status IN ('pending', 'deploying')
        """
    )


def downgrade() -> None:
    op.drop_index("uq_project_deploy_active", table_name="project_deploys")