"""Add ``severity`` to offboarding_blockers; backfill known auto-handled types.

Round-14 audit (2026-05-08): IT was seeing every leaver flagged as
"blocked" while in reality every outstanding row was scheduled for
auto-revoke (cloud_access_grant, software_seat sso_cascade) or
manager-fallback transfer (hardware, email_group, etc.) at T+0+24h.
The blocker queue conflated "needs human action" with "system will
handle it shortly", and 6 HRIS-late-detection leavers permanently
sat in the red badge.

This migration adds a ``severity`` column with two values:

  * ``blocker``  - needs IT decision (no automatic handler, or the
                   handler failed). Default for unknown / new
                   resource_types so a freshly added scanner branch
                   without an explicit map entry surfaces in red
                   rather than silently auto-bucketing.
  * ``advisory`` - every known auto-handler covers this resource_type;
                   the row is shown under the auto-handled queue with
                   the T+0 grace countdown rather than the red badge.

Backfill: every existing row whose ``resource_type`` is one of the 17
types covered by ProcessAdvanceService._handle_one_blocker flips to
``advisory``. Rows with an unknown resource_type stay at the
``blocker`` default - either pre-2026-04 legacy values that no longer
ship in scanner output (e.g. ``cloud_account``) or future additions
that have not been classified yet.

Pairs with an index on (process_id, severity, resolution) so the
dashboard's split queries do not full-scan the table once the row count
crosses ~10k during high-leaver months.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

# revision identifiers, used by Alembic.
revision = "136"
down_revision = "135"
branch_labels = None
depends_on = None


# Resource types ProcessAdvanceService._handle_one_blocker knows how to
# auto-resolve at T+0+24h. Source of truth: the elif-chain in
# server/app/hr_lifecycle/advance_service.py.
_ADVISORY_RESOURCE_TYPES = (
    "hardware",
    "software_seat",
    "software_permission",
    "api_key",
    "domain",
    "dns_record",
    "cloud_server",
    "software_owner",
    "software_it_contact",
    "email_group",
    "cloud_scope_owner",
    "cloud_access_grant",
    "sso_grant",
    "domain_registration",
    "google_orphan_owner",
    "vpn_application",
    "ai_tool_application",
)


def upgrade() -> None:
    op.add_column(
        "offboarding_blockers",
        sa.Column(
            "severity",
            sa.String(16),
            nullable=False,
            server_default="blocker",
        ),
    )
    placeholders = ", ".join(f"'{t}'" for t in _ADVISORY_RESOURCE_TYPES)
    op.execute(
        f"UPDATE offboarding_blockers "
        f"SET severity = 'advisory' "
        f"WHERE resource_type IN ({placeholders})"
    )
    op.create_index(
        "ix_offboarding_blockers_proc_sev_res",
        "offboarding_blockers",
        ["process_id", "severity", "resolution"],
    )


def downgrade() -> None:
    op.drop_index(
        "ix_offboarding_blockers_proc_sev_res",
        table_name="offboarding_blockers",
    )
    op.drop_column("offboarding_blockers", "severity")