"""add guarded signing key rotation lifecycle

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

from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


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


def upgrade() -> None:
    with op.batch_alter_table("signing_key_metadata") as batch_op:
        batch_op.add_column(
            sa.Column("activated_at", sa.DateTime(timezone=True), nullable=True)
        )
        batch_op.add_column(
            sa.Column("retired_at", sa.DateTime(timezone=True), nullable=True)
        )
    op.execute(
        "UPDATE signing_key_metadata SET activated_at = not_before "
        "WHERE status = 'active' AND activated_at IS NULL"
    )
    op.execute(
        "UPDATE signing_key_metadata SET retired_at = expires_at "
        "WHERE status IN ('retired', 'compromised') AND retired_at IS NULL"
    )
    with op.batch_alter_table("signing_key_metadata") as batch_op:
        batch_op.create_check_constraint(
            "ck_signing_key_metadata_status",
            "status IN ('staged', 'active', 'retired', 'compromised')",
        )
        batch_op.create_check_constraint(
            "ck_signing_key_metadata_validity",
            "expires_at IS NULL OR expires_at > not_before",
        )
        batch_op.create_check_constraint(
            "ck_signing_key_metadata_lifecycle",
            "(status = 'staged' AND activated_at IS NULL AND retired_at IS NULL) OR "
            "(status = 'active' AND activated_at IS NOT NULL AND retired_at IS NULL) OR "
            "(status IN ('retired', 'compromised') AND retired_at IS NOT NULL)",
        )
        batch_op.create_check_constraint(
            "ck_signing_key_metadata_retirement",
            "status NOT IN ('retired', 'compromised') OR "
            "(expires_at IS NOT NULL AND expires_at <= retired_at)",
        )
    op.create_index(
        "uq_signing_key_metadata_active_purpose",
        "signing_key_metadata",
        ["purpose"],
        unique=True,
        sqlite_where=sa.text("status = 'active'"),
        postgresql_where=sa.text("status = 'active'"),
    )


def downgrade() -> None:
    op.drop_index(
        "uq_signing_key_metadata_active_purpose",
        table_name="signing_key_metadata",
    )
    with op.batch_alter_table("signing_key_metadata") as batch_op:
        batch_op.drop_constraint(
            "ck_signing_key_metadata_retirement",
            type_="check",
        )
        batch_op.drop_constraint(
            "ck_signing_key_metadata_lifecycle",
            type_="check",
        )
        batch_op.drop_constraint(
            "ck_signing_key_metadata_validity",
            type_="check",
        )
        batch_op.drop_constraint(
            "ck_signing_key_metadata_status",
            type_="check",
        )
        batch_op.drop_column("retired_at")
        batch_op.drop_column("activated_at")