"""separate system and tenant audit scopes

Revision ID: b1c2d3e4f5a6
Revises: a0b1c2d3e4f5
Create Date: 2026-06-15 13:30:00.000000
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "b1c2d3e4f5a6"
down_revision = "a0b1c2d3e4f5"
branch_labels = None
depends_on = None

SYSTEM_ACTIONS = (
    "tenant.created",
    "tenant.updated",
    "system_role.created",
    "system_role.updated",
    "system_role.deleted",
    "system_account.created",
    "system_account.updated",
    "system_access.updated",
    "system_memberships.updated",
    "system_settings.updated",
    "governance_template.created",
    "governance_template.updated",
    "governance_template.deleted",
)


def upgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    if "audit_log" not in inspector.get_table_names():
        return
    columns = {column["name"] for column in inspector.get_columns("audit_log")}
    if "scope" not in columns:
        op.add_column(
            "audit_log",
            sa.Column("scope", sa.String(length=20), nullable=False, server_default="tenant"),
        )
    indexes = {index["name"] for index in sa.inspect(bind).get_indexes("audit_log")}
    if "ix_audit_log_scope" not in indexes:
        op.create_index("ix_audit_log_scope", "audit_log", ["scope"])

    placeholders = ", ".join(f":action_{index}" for index, _ in enumerate(SYSTEM_ACTIONS))
    params = {f"action_{index}": action for index, action in enumerate(SYSTEM_ACTIONS)}
    bind.execute(
        sa.text(f"UPDATE audit_log SET scope = 'system' WHERE action IN ({placeholders})"),  # nosemgrep: python.sqlalchemy.security.audit.avoid-sqlalchemy-text.avoid-sqlalchemy-text
        params,
    )
    bind.execute(sa.text("UPDATE audit_log SET scope = 'tenant' WHERE scope IS NULL OR scope NOT IN ('tenant', 'system')"))


def downgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    if "audit_log" not in inspector.get_table_names():
        return
    indexes = {index["name"] for index in inspector.get_indexes("audit_log")}
    if "ix_audit_log_scope" in indexes:
        op.drop_index("ix_audit_log_scope", table_name="audit_log")
    columns = {column["name"] for column in sa.inspect(bind).get_columns("audit_log")}
    if "scope" in columns:
        with op.batch_alter_table("audit_log") as batch_op:
            batch_op.drop_column("scope")
