"""Project authorization v2: canonical assignment tier on project auth roles.

assignment_tier names the grant/revoke permission family that governs a role
(admin, staff, member). It is classification data, not privilege, and it is never
inferred from permissions_json or the role name. Legacy roles that do not match a
known preset exactly stay NULL, which makes them unassignable until classified.

Backfill matches presets on name, exact permission set, and codegen origin together.
The owner-equivalent role is left NULL; it is never generically assigned.

Revision ID: 191
Revises: 190
"""
import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision = "191"
down_revision = "190"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("CREATE TYPE p2hs_project_auth_assignment_tier AS ENUM ('admin', 'staff', 'member')")
    op.add_column(
        "project_auth_roles",
        sa.Column(
            "assignment_tier",
            postgresql.ENUM(
                "admin", "staff", "member",
                name="p2hs_project_auth_assignment_tier",
                create_type=False,
            ),
            nullable=True,
        ),
    )
    op.execute(
        """
        UPDATE project_auth_roles
        SET assignment_tier = CASE
            WHEN name = 'admin' AND created_by = 'codegen' AND permissions_json = '["staff"]'::jsonb
                THEN 'admin'::p2hs_project_auth_assignment_tier
            WHEN name = 'member' AND created_by = 'codegen' AND permissions_json = '[]'::jsonb
                THEN 'member'::p2hs_project_auth_assignment_tier
            WHEN name = 'staff' AND created_by = 'codegen' AND permissions_json = '["staff"]'::jsonb
                THEN 'staff'::p2hs_project_auth_assignment_tier
        END
        WHERE NOT protected
        """
    )
    op.create_check_constraint(
        "ck_project_auth_roles_protected_untiered",
        "project_auth_roles",
        "NOT protected OR assignment_tier IS NULL",
    )


def downgrade() -> None:
    op.drop_constraint(
        "ck_project_auth_roles_protected_untiered", "project_auth_roles", type_="check"
    )
    op.drop_column("project_auth_roles", "assignment_tier")
    op.execute("DROP TYPE p2hs_project_auth_assignment_tier")