"""add focused platform staff security

Revision ID: e6f7a8b9c0d2
Revises: d5e6f7a8b9c0
"""
from collections.abc import Sequence
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision: str = "e6f7a8b9c0d2"
down_revision: str | None = "d5e6f7a8b9c0"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None

def upgrade() -> None:
    op.add_column("users", sa.Column("is_platform_admin", sa.Boolean(), server_default=sa.false(), nullable=False))
    op.add_column("users", sa.Column("platform_role", sa.String(60), nullable=True))
    op.add_column("users", sa.Column("platform_permissions", postgresql.JSONB(), server_default=sa.text("'[]'::jsonb"), nullable=False))
    op.add_column("users", sa.Column("platform_staff_status", sa.String(20), nullable=True))
    op.add_column("users", sa.Column("platform_session_version", sa.Integer(), server_default="0", nullable=False))
    op.add_column("users", sa.Column("platform_last_login_at", sa.DateTime(timezone=True), nullable=True))
    op.create_table("platform_role_definitions",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("key", sa.String(60), nullable=False, unique=True),
        sa.Column("name", sa.String(100), nullable=False), sa.Column("description", sa.Text()), sa.Column("permissions", postgresql.JSONB(), nullable=False),
        sa.Column("is_system", sa.Boolean(), nullable=False), sa.Column("is_active", sa.Boolean(), nullable=False),
        sa.Column("created_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL")),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False))
    op.create_index("ix_platform_role_definitions_key", "platform_role_definitions", ["key"], unique=True)
    op.create_table("platform_staff_invitations",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("email", sa.String(320), nullable=False),
        sa.Column("role_key", sa.String(60), nullable=False), sa.Column("permission_overrides", postgresql.JSONB(), nullable=False),
        sa.Column("token_hash", sa.String(128), nullable=False, unique=True), sa.Column("invited_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False), sa.Column("accepted_at", sa.DateTime(timezone=True)),
        sa.Column("accepted_by", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="SET NULL")), sa.Column("revoked_at", sa.DateTime(timezone=True)),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False))
    op.create_index("ix_platform_staff_invitations_email", "platform_staff_invitations", ["email"])
    op.create_index("ix_platform_staff_invitations_expires_at", "platform_staff_invitations", ["expires_at"])
    op.create_table("platform_audit_logs",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True), sa.Column("actor_user_id", postgresql.UUID(as_uuid=True), sa.ForeignKey("users.id", ondelete="RESTRICT"), nullable=False),
        sa.Column("action", sa.String(80), nullable=False), sa.Column("entity_type", sa.String(80), nullable=False), sa.Column("entity_id", postgresql.UUID(as_uuid=True)),
        sa.Column("reason", sa.Text()), sa.Column("details", postgresql.JSONB(), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), nullable=False))
    for column in ("actor_user_id", "action", "entity_type", "entity_id", "created_at"):
        op.create_index(f"ix_platform_audit_logs_{column}", "platform_audit_logs", [column])

def downgrade() -> None:
    op.drop_table("platform_audit_logs"); op.drop_table("platform_staff_invitations"); op.drop_table("platform_role_definitions")
    for column in ("platform_last_login_at", "platform_session_version", "platform_staff_status", "platform_permissions", "platform_role", "is_platform_admin"):
        op.drop_column("users", column)