"""Add CHECK constraint on users.role for RBAC roles

Revision ID: 007_add_role_check_constraint
Revises: 006_add_knowledge_base
Create Date: 2026-02-19

"""
from typing import Sequence, Union

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "007_add_role_check_constraint"
down_revision: Union[str, None] = "006_add_knowledge_base"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

VALID_ROLES = ("super_admin", "oversight_admin", "agency_admin", "basic_user")


def upgrade() -> None:
    # Normalise any legacy role values to basic_user before adding constraint
    op.execute(
        f"UPDATE users SET role = 'basic_user' "
        f"WHERE role NOT IN {VALID_ROLES!r}"
    )
    op.create_check_constraint(
        "ck_users_role",
        "users",
        f"role IN {VALID_ROLES!r}",
    )


def downgrade() -> None:
    op.drop_constraint("ck_users_role", "users", type_="check")
