"""add_rbac_tables

Revision ID: 55c5d2499eee
Revises: c8d7e5a4b321
Create Date: 2026-02-02 13:24:50.554624

"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic_postgresql_enum import TableReference
from sqlalchemy.dialects import postgresql

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "55c5d2499eee"
down_revision: str | None = "c8d7e5a4b321"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    sa.Enum("PLATFORM", "CUSTOM", name="scopesource").create(op.get_bind())
    op.create_table(
        "group",
        sa.Column("id", sa.UUID(), nullable=False),
        sa.Column("name", sa.String(length=128), nullable=False),
        sa.Column("description", sa.String(length=512), nullable=True),
        sa.Column("organization_id", sa.UUID(), nullable=False),
        sa.Column("created_by", sa.UUID(), nullable=True),
        sa.Column(
            "created_at",
            sa.TIMESTAMP(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.TIMESTAMP(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["created_by"],
            ["user.id"],
            name=op.f("fk_group_created_by_user"),
            ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organization.id"],
            name=op.f("fk_group_organization_id_organization"),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_group")),
        sa.UniqueConstraint(
            "organization_id", "name", name=op.f("uq_group_organization_id_name")
        ),
    )
    op.create_index(op.f("ix_group_name"), "group", ["name"], unique=False)
    op.create_index(
        op.f("ix_group_organization_id"), "group", ["organization_id"], unique=False
    )
    op.create_table(
        "role",
        sa.Column("id", sa.UUID(), nullable=False),
        sa.Column("name", sa.String(length=128), nullable=False),
        sa.Column("slug", sa.String(length=64), nullable=True),
        sa.Column("description", sa.String(length=512), nullable=True),
        sa.Column("organization_id", sa.UUID(), nullable=False),
        sa.Column("created_by", sa.UUID(), nullable=True),
        sa.Column(
            "created_at",
            sa.TIMESTAMP(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.TIMESTAMP(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["created_by"],
            ["user.id"],
            name=op.f("fk_role_created_by_user"),
            ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organization.id"],
            name=op.f("fk_role_organization_id_organization"),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_role")),
        sa.UniqueConstraint(
            "organization_id", "slug", name=op.f("uq_role_organization_id_slug")
        ),
    )
    op.create_index(op.f("ix_role_name"), "role", ["name"], unique=False)
    op.create_index(
        op.f("ix_role_organization_id"), "role", ["organization_id"], unique=False
    )
    op.create_index(op.f("ix_role_slug"), "role", ["slug"], unique=False)
    op.create_table(
        "scope",
        sa.Column("id", sa.UUID(), nullable=False),
        sa.Column("name", sa.String(length=255), nullable=False),
        sa.Column("resource", sa.String(length=128), nullable=False),
        sa.Column("action", sa.String(length=64), nullable=False),
        sa.Column("description", sa.String(length=512), nullable=True),
        sa.Column(
            "source",
            postgresql.ENUM(
                "PLATFORM", "CUSTOM", name="scopesource", create_type=False
            ),
            nullable=False,
        ),
        sa.Column("source_ref", sa.String(length=255), nullable=True),
        sa.Column("organization_id", sa.UUID(), nullable=True),
        sa.Column(
            "created_at",
            sa.TIMESTAMP(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.Column(
            "updated_at",
            sa.TIMESTAMP(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organization.id"],
            name=op.f("fk_scope_organization_id_organization"),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_scope")),
        sa.UniqueConstraint(
            "organization_id", "name", name=op.f("uq_scope_organization_id_name")
        ),
    )
    op.create_index(op.f("ix_scope_name"), "scope", ["name"], unique=False)
    op.create_index(
        "ix_scope_name_system_unique",
        "scope",
        ["name"],
        unique=True,
        postgresql_where=sa.text("organization_id IS NULL"),
    )
    op.create_index(
        op.f("ix_scope_organization_id"), "scope", ["organization_id"], unique=False
    )
    op.create_index(op.f("ix_scope_source"), "scope", ["source"], unique=False)
    op.create_table(
        "group_role_assignment",
        sa.Column("id", sa.UUID(), nullable=False),
        sa.Column("organization_id", sa.UUID(), nullable=False),
        sa.Column("group_id", sa.UUID(), nullable=False),
        sa.Column("workspace_id", sa.UUID(), nullable=True),
        sa.Column("role_id", sa.UUID(), nullable=False),
        sa.Column(
            "assigned_at",
            sa.TIMESTAMP(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.Column("assigned_by", sa.UUID(), nullable=True),
        sa.ForeignKeyConstraint(
            ["assigned_by"],
            ["user.id"],
            name=op.f("fk_group_role_assignment_assigned_by_user"),
            ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["group_id"],
            ["group.id"],
            name=op.f("fk_group_role_assignment_group_id_group"),
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organization.id"],
            name=op.f("fk_group_role_assignment_organization_id_organization"),
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["role_id"],
            ["role.id"],
            name=op.f("fk_group_role_assignment_role_id_role"),
            ondelete="RESTRICT",
        ),
        sa.ForeignKeyConstraint(
            ["workspace_id"],
            ["workspace.id"],
            name=op.f("fk_group_role_assignment_workspace_id_workspace"),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_group_role_assignment")),
        sa.UniqueConstraint(
            "group_id",
            "workspace_id",
            name=op.f("uq_group_role_assignment_group_id_workspace_id"),
        ),
    )
    op.create_index(
        "ix_group_role_assignment_group_org_unique",
        "group_role_assignment",
        ["group_id"],
        unique=True,
        postgresql_where=sa.text("workspace_id IS NULL"),
    )
    op.create_index(
        op.f("ix_group_role_assignment_role_id"),
        "group_role_assignment",
        ["role_id"],
        unique=False,
    )
    op.create_table(
        "group_member",
        sa.Column("user_id", sa.UUID(), nullable=False),
        sa.Column("group_id", sa.UUID(), nullable=False),
        sa.Column(
            "added_at",
            sa.TIMESTAMP(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["group_id"],
            ["group.id"],
            name=op.f("fk_group_member_group_id_group"),
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["user.id"],
            name=op.f("fk_group_member_user_id_user"),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("user_id", "group_id", name=op.f("pk_group_member")),
    )
    op.create_table(
        "role_scope",
        sa.Column("role_id", sa.UUID(), nullable=False),
        sa.Column("scope_id", sa.UUID(), nullable=False),
        sa.ForeignKeyConstraint(
            ["role_id"],
            ["role.id"],
            name=op.f("fk_role_scope_role_id_role"),
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["scope_id"],
            ["scope.id"],
            name=op.f("fk_role_scope_scope_id_scope"),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("role_id", "scope_id", name=op.f("pk_role_scope")),
    )
    op.create_table(
        "user_role_assignment",
        sa.Column("id", sa.UUID(), nullable=False),
        sa.Column("organization_id", sa.UUID(), nullable=False),
        sa.Column("user_id", sa.UUID(), nullable=False),
        sa.Column("workspace_id", sa.UUID(), nullable=True),
        sa.Column("role_id", sa.UUID(), nullable=False),
        sa.Column(
            "assigned_at",
            sa.TIMESTAMP(timezone=True),
            server_default=sa.text("now()"),
            nullable=False,
        ),
        sa.Column("assigned_by", sa.UUID(), nullable=True),
        sa.ForeignKeyConstraint(
            ["assigned_by"],
            ["user.id"],
            name=op.f("fk_user_role_assignment_assigned_by_user"),
            ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["organization_id"],
            ["organization.id"],
            name=op.f("fk_user_role_assignment_organization_id_organization"),
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["role_id"],
            ["role.id"],
            name=op.f("fk_user_role_assignment_role_id_role"),
            ondelete="RESTRICT",
        ),
        sa.ForeignKeyConstraint(
            ["user_id"],
            ["user.id"],
            name=op.f("fk_user_role_assignment_user_id_user"),
            ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["workspace_id"],
            ["workspace.id"],
            name=op.f("fk_user_role_assignment_workspace_id_workspace"),
            ondelete="CASCADE",
        ),
        sa.PrimaryKeyConstraint("id", name=op.f("pk_user_role_assignment")),
        sa.UniqueConstraint(
            "user_id",
            "workspace_id",
            name=op.f("uq_user_role_assignment_user_id_workspace_id"),
        ),
    )
    op.create_index(
        op.f("ix_user_role_assignment_role_id"),
        "user_role_assignment",
        ["role_id"],
        unique=False,
    )
    op.create_index(
        "ix_user_role_assignment_user_org_unique",
        "user_role_assignment",
        ["user_id"],
        unique=True,
        postgresql_where=sa.text("workspace_id IS NULL"),
    )
    op.sync_enum_values(  # type: ignore[attr-defined]
        enum_schema="public",
        enum_name="workspacerole",
        new_values=["VIEWER", "EDITOR", "ADMIN"],
        affected_columns=[
            TableReference(
                table_schema="public", table_name="invitation", column_name="role"
            ),
            TableReference(
                table_schema="public",
                table_name="membership",
                column_name="role",
                existing_server_default="'EDITOR'::workspacerole",
            ),
        ],
        enum_values_to_rename=[],
    )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.sync_enum_values(  # type: ignore[attr-defined]
        enum_schema="public",
        enum_name="workspacerole",
        new_values=["EDITOR", "ADMIN"],
        affected_columns=[
            TableReference(
                table_schema="public", table_name="invitation", column_name="role"
            ),
            TableReference(
                table_schema="public",
                table_name="membership",
                column_name="role",
                existing_server_default="'EDITOR'::workspacerole",
            ),
        ],
        enum_values_to_rename=[],
    )
    op.drop_index(
        "ix_user_role_assignment_user_org_unique",
        table_name="user_role_assignment",
        postgresql_where=sa.text("workspace_id IS NULL"),
    )
    op.drop_index(
        op.f("ix_user_role_assignment_role_id"), table_name="user_role_assignment"
    )
    op.drop_table("user_role_assignment")
    op.drop_table("role_scope")
    op.drop_table("group_member")
    op.drop_index(
        op.f("ix_group_role_assignment_role_id"), table_name="group_role_assignment"
    )
    op.drop_index(
        "ix_group_role_assignment_group_org_unique",
        table_name="group_role_assignment",
        postgresql_where=sa.text("workspace_id IS NULL"),
    )
    op.drop_table("group_role_assignment")
    op.drop_index(op.f("ix_scope_source"), table_name="scope")
    op.drop_index(op.f("ix_scope_organization_id"), table_name="scope")
    op.drop_index(
        "ix_scope_name_system_unique",
        table_name="scope",
        postgresql_where=sa.text("organization_id IS NULL"),
    )
    op.drop_index(op.f("ix_scope_name"), table_name="scope")
    op.drop_table("scope")
    op.drop_index(op.f("ix_role_slug"), table_name="role")
    op.drop_index(op.f("ix_role_organization_id"), table_name="role")
    op.drop_index(op.f("ix_role_name"), table_name="role")
    op.drop_table("role")
    op.drop_index(op.f("ix_group_organization_id"), table_name="group")
    op.drop_index(op.f("ix_group_name"), table_name="group")
    op.drop_table("group")
    sa.Enum("PLATFORM", "CUSTOM", name="scopesource").drop(op.get_bind())
    # ### end Alembic commands ###
