"""Project authorization v2 schema: project memberships, scoped roles, owner authority.

Additive only. Existing project_auth_roles rows default to scope 'project' and
protected=false; existing project_auth_config rows default to owner_authority
'workspace'. No reader changes in this revision.

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

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


def upgrade() -> None:
    op.execute("CREATE TYPE p2hs_project_auth_role_scope AS ENUM ('project', 'account')")
    op.execute("CREATE TYPE p2hs_project_auth_owner_authority AS ENUM ('workspace', 'app')")
    op.execute("CREATE TYPE p2hs_project_membership_source AS ENUM ('workspace', 'local')")
    op.execute("CREATE TYPE p2hs_project_membership_status AS ENUM ('active', 'suspended')")

    op.add_column(
        "project_auth_roles",
        sa.Column(
            "scope_type",
            postgresql.ENUM("project", "account", name="p2hs_project_auth_role_scope", create_type=False),
            nullable=False,
            server_default="project",
        ),
    )
    op.add_column(
        "project_auth_roles",
        sa.Column("protected", sa.Boolean(), nullable=False, server_default=sa.false()),
    )
    op.create_check_constraint(
        "ck_project_auth_roles_protected_scope",
        "project_auth_roles",
        "NOT protected OR scope_type = 'project'",
    )

    op.add_column(
        "project_auth_config",
        sa.Column(
            "owner_authority",
            postgresql.ENUM("workspace", "app", name="p2hs_project_auth_owner_authority", create_type=False),
            nullable=False,
            server_default="workspace",
        ),
    )

    op.create_table(
        "project_memberships",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "project_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("projects.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("email_normalized", sa.Text(), nullable=False),
        sa.Column("cloud_app_user_id", sa.Text(), nullable=True),
        sa.Column(
            "source",
            postgresql.ENUM("workspace", "local", name="p2hs_project_membership_source", create_type=False),
            nullable=False,
        ),
        sa.Column(
            "status",
            postgresql.ENUM("active", "suspended", name="p2hs_project_membership_status", create_type=False),
            nullable=False,
            server_default="active",
        ),
        sa.Column("is_owner", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("authorization_version", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint("project_id", "email_normalized", name="uq_project_memberships_project_email"),
        sa.CheckConstraint("NOT is_owner OR status = 'active'", name="ck_project_memberships_owner_active"),
    )
    op.create_index(
        "uq_project_memberships_one_owner",
        "project_memberships",
        ["project_id"],
        unique=True,
        postgresql_where=sa.text("is_owner"),
    )
    op.create_index("ix_project_memberships_cloud_app_user_id", "project_memberships", ["cloud_app_user_id"])

    op.create_table(
        "project_membership_roles",
        sa.Column(
            "membership_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("project_memberships.id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column(
            "role_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("project_auth_roles.id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.PrimaryKeyConstraint("membership_id", "role_id", name="pk_project_membership_roles"),
    )
    op.create_index("ix_project_membership_roles_role_id", "project_membership_roles", ["role_id"])


def downgrade() -> None:
    op.drop_index("ix_project_membership_roles_role_id", table_name="project_membership_roles")
    op.drop_table("project_membership_roles")

    op.drop_index("ix_project_memberships_cloud_app_user_id", table_name="project_memberships")
    op.drop_index("uq_project_memberships_one_owner", table_name="project_memberships")
    op.drop_table("project_memberships")

    op.drop_column("project_auth_config", "owner_authority")

    op.drop_constraint("ck_project_auth_roles_protected_scope", "project_auth_roles", type_="check")
    op.drop_column("project_auth_roles", "protected")
    op.drop_column("project_auth_roles", "scope_type")

    op.execute("DROP TYPE p2hs_project_membership_status")
    op.execute("DROP TYPE p2hs_project_membership_source")
    op.execute("DROP TYPE p2hs_project_auth_owner_authority")
    op.execute("DROP TYPE p2hs_project_auth_role_scope")