"""Project membership role bindings record their grant source (design D29).

A membership may hold the same role from more than one origin (for example a
workspace-sourced admin binding and a local binding). The primary key includes the
source so both can coexist; effective roles are the distinct role ids across bindings.

The table is new in this revision chain and has no rows in any deployed database, so
source is added NOT NULL without a default: every binding must name its origin.

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

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


def upgrade() -> None:
    op.execute("CREATE TYPE p2hs_project_membership_role_source AS ENUM ('workspace', 'local')")
    op.add_column(
        "project_membership_roles",
        sa.Column(
            "source",
            postgresql.ENUM(
                "workspace", "local",
                name="p2hs_project_membership_role_source",
                create_type=False,
            ),
            nullable=False,
        ),
    )
    op.drop_constraint("pk_project_membership_roles", "project_membership_roles", type_="primary")
    op.create_primary_key(
        "pk_project_membership_roles",
        "project_membership_roles",
        ["membership_id", "role_id", "source"],
    )


def downgrade() -> None:
    # Collapse each (membership, role) pair to one row before restoring the narrower key.
    op.execute(
        """
        DELETE FROM project_membership_roles a
        USING project_membership_roles b
        WHERE a.membership_id = b.membership_id
          AND a.role_id = b.role_id
          AND a.source > b.source
        """
    )
    op.drop_constraint("pk_project_membership_roles", "project_membership_roles", type_="primary")
    op.create_primary_key(
        "pk_project_membership_roles", "project_membership_roles", ["membership_id", "role_id"]
    )
    op.drop_column("project_membership_roles", "source")
    op.execute("DROP TYPE p2hs_project_membership_role_source")