"""Add ``management`` role for read-only company-leadership view.

Revision ID: 099
Revises: 098
Create Date: 2026-05-02

Background
----------
Per user direction 2026-05-02: company leadership (CEO / VP / heads of
function) needed a way to see the full Helix admin surface — assets,
HR, cloud, audit, finance, security, operations insights — without
also being on the receiving end of routine operational notifications.
``global_admin`` does the former but also opts the holder into every
admin fan-out broadcast (cloud scope-request approvals, leaver SLA
escalations, provisioning failures, HR digest emails, etc.) — too
noisy for an executive who isn't running the day-to-day.

The ``management`` role solves both at once:

* **Read scope** — ``rbac.py`` adds ``MANAGEMENT_ROLE`` to every
  ``can_read_*`` / ``can_view_*`` / ``can_access_audit`` helper. A
  management holder lands on the same admin pages as ``global_admin``.
* **Write scope** — deliberately NOT in any ``can_manage_*`` helper.
  100% read-only at the backend gate; the frontend hides write buttons
  via the same ``canManage*`` mirror.
* **Notifications** — the 7 admin fan-out queries in the codebase
  (``scope_request_service``, ``cmdb/lifecycle_service``,
  ``provisioning/service``, ``offboarding/escalation``,
  ``hr_lifecycle/digest_service``, ``leaver_blocked/sla_service``)
  are all hard-coded ``("global_admin", "it_admin")``. We deliberately
  do NOT touch them, so management holders are silently excluded from
  the broadcast cohort. Personal notifications (their own offboarding,
  their own cloud grants, etc.) still arrive normally.

Migration steps
---------------

1. INSERT the ``management`` row into ``auth_roles`` if it doesn't
   already exist. Idempotent.

Downgrade deletes the role and any user-role assignments via
``auth_user_roles`` cascade.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "099"
down_revision = "098"
branch_labels = None
depends_on = None


def upgrade() -> None:
    bind = op.get_bind()

    exists = bind.execute(sa.text(
        "SELECT 1 FROM auth_roles WHERE name = 'management' LIMIT 1"
    )).first()
    if exists is None:
        # Dashed UUID format matches the SQLAlchemy GUID type (see
        # migration 092 docstring for the FK gotcha this avoids).
        bind.execute(sa.text(
            "INSERT INTO auth_roles (id, name, description, is_system) "
            "VALUES (UUID(), 'management', "
            "'Management (read-only leadership view — sees everything, "
            "receives no operational broadcasts)', 1)"
        ))


def downgrade() -> None:
    bind = op.get_bind()
    bind.execute(sa.text(
        "DELETE FROM auth_user_roles WHERE role_id IN ("
        "  SELECT id FROM auth_roles WHERE name = 'management'"
        ")"
    ))
    bind.execute(sa.text(
        "DELETE FROM auth_roles WHERE name = 'management'"
    ))