"""Offboarding process — per-asset hardware disposition map.

Revision ID: 187
Revises: 186
Create Date: 2026-05-19

Renumbered from 184 → 187 during merge alongside migration 186 (was
183) — see that file's docstring for context.

Why this exists
---------------
The T+0 leaver force-transfer used to mass-transfer every hardware
asset still attributed to the leaver up the manager cascade
(``_resolve_hardware_recipient``). In practice that funnels devices
onto a handful of IT admins — see PR-#XXX (held_by_office model) for
the why. With the office-held mode landed (migration 183), the leaver
handler needs a per-asset decision:

  * ``keep`` — leaver keeps the device (mark ``held_by_leaver``).
    Used when the asset was officially approved to stay with the
    departing employee.
  * ``recycle_to_office`` — return to the leaver's home office
    (new default; ``holder_kind=office``).
  * ``transfer_to_person`` — hand to a specific successor (legacy
    behaviour, but now an explicit opt-in, not a fallback).

That decision is captured per-process per-asset in this new column,
populated from the Helix offboarding console UI before the T+0 grace
expiry. When the column is NULL or an entry for an asset is missing,
``_handle_one_blocker`` falls back to ``recycle_to_office`` with the
leaver's ``work_location_code`` resolved against
``asset_office_addresses.city`` — i.e. the safe-by-default behaviour
that stops the bleeding of "all assets land on the same admin".

Shape (validated by ``HardwareDispositionRequest`` Pydantic schema):

  {
    "<asset_id>": {
      "action": "keep" | "recycle_to_office" | "transfer_to_person",
      "office_id": "<uuid|null>",      # only for recycle_to_office
      "person_id": "<uuid|null>",      # only for transfer_to_person
      "note": "<optional admin note>"
    },
    ...
  }

Stored as JSON (not a separate table) because:
1. The blast radius is per-offboarding-process, not cross-process,
   so no global query patterns benefit from row-per-asset normalisation.
2. We never JOIN or filter on individual asset entries from SQL —
   the only consumer is the T+0 advance handler, which loads the
   whole process row anyway.
3. JSON keeps the migration trivial and round-trips through SQLAlchemy
   without an extra association table.

Idempotency
-----------
Column-add only. Default NULL = "no decision yet, fall back to
auto-route at T+0". Existing processes already past T+0 are
untouched; the column stays NULL.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "187"
down_revision = "186"
branch_labels = None
depends_on = None


def _column_exists(bind, table: str, column: str) -> bool:
    row = bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": table, "c": column},
    ).fetchone()
    return row is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _column_exists(bind, "offboarding_processes", "hardware_disposition"):
        op.add_column(
            "offboarding_processes",
            sa.Column("hardware_disposition", sa.JSON(), nullable=True),
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _column_exists(bind, "offboarding_processes", "hardware_disposition"):
        op.drop_column("offboarding_processes", "hardware_disposition")