"""Source-aware suspension holds for project memberships (design D30).

A membership can be held by more than one authority at once (a Workspace policy hold
and a local owner hold). Each hold is a row per source; a membership is suspended while
any hold exists. ProjectMembership.status is kept as the materialized view of that.

Existing suspended rows have unknown provenance. They are backfilled as source=legacy,
which never clears automatically: reinstating a legacy hold is a deliberate action.

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

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


def upgrade() -> None:
    op.execute(
        "CREATE TYPE p2hs_project_membership_suspension_source "
        "AS ENUM ('workspace', 'local', 'legacy')"
    )
    op.create_table(
        "project_membership_suspensions",
        sa.Column(
            "membership_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("project_memberships.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "source",
            postgresql.ENUM(
                "workspace", "local", "legacy",
                name="p2hs_project_membership_suspension_source",
                create_type=False,
            ),
            nullable=False,
        ),
        sa.Column("reason_code", sa.Text(), nullable=False, server_default="unspecified"),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
        sa.Column(
            "created_by_membership_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("project_memberships.id", ondelete="SET NULL"),
            nullable=True,
        ),
        sa.Column("metadata", postgresql.JSONB(), nullable=True),
        sa.PrimaryKeyConstraint(
            "membership_id", "source", name="pk_project_membership_suspensions"
        ),
    )
    op.execute(
        """
        INSERT INTO project_membership_suspensions (membership_id, source, reason_code)
        SELECT id, 'legacy', 'pre_v2_status'
        FROM project_memberships
        WHERE status = 'suspended'
        """
    )


def downgrade() -> None:
    # Status is retained as the materialized state, so reverting loses only the provenance.
    op.drop_table("project_membership_suspensions")
    op.execute("DROP TYPE p2hs_project_membership_suspension_source")