"""offboarding_processes.default_handover_person_id — per-process default successor.

Revision ID: 211
Revises: 210
Create Date: 2026-05-28

Why this exists
---------------
The per-blocker "指定接手人" flow (POST /offboarding/blockers/{id}/handover)
requires the admin to re-select a person on every applicable blocker. In
practice the successor is almost always the leaver's direct manager and
the admin types the same name 5+ times in a row.

This migration adds a single nullable column ``default_handover_person_id``
on ``offboarding_processes`` that holds a per-process default. On
process creation the service writes the leaver's ``manager_id`` into
this column (HRIS-derived); the admin can blank it or change it from
the detail page. The per-blocker handover modal uses this value as the
prefill — actual ``successor_person_id`` is still passed in the request
body so audit attribution stays at the blocker level.

Schema notes
------------
* FK to ``identity_persons.id`` with ``ON DELETE SET NULL`` — a hard-
  deleted person never cascade-deletes an offboarding process, and the
  default cleanly clears itself (matching the api_key_assignments
  precedent in migration 193).
* Column type mirrors ``identity_persons.id``'s DATA_TYPE + collation at
  runtime via ``normalize_fk_column_collation`` — same TiDB strict-FK
  guard the 192/193 chain established. Without this, prod TiDB rejects
  the FK with error 3780 (Referencing column incompatible).
* No index — the column is read on a single-row fetch by process id and
  written rarely; a secondary index would be cargo-cult.

Backfill
--------
Existing in-flight offboarding processes have no default. Backfill from
``identity_persons.manager_id`` (only when both rows exist and the
manager is non-null + non-deleted). Admin can still override on a
case-by-case basis. Completed/cancelled processes are skipped — they
have no live UI surface that would consume the prefill.

Idempotency
-----------
Column existence probed via ``information_schema.COLUMNS``; FK existence
probed via ``information_schema.REFERENTIAL_CONSTRAINTS``. Safe to re-run.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, normalize_fk_column_collation

revision = "211"
down_revision = "210"
branch_labels = None
depends_on = None


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


def _fk_exists(bind, table: str, fk_name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.REFERENTIAL_CONSTRAINTS "
            "WHERE CONSTRAINT_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND CONSTRAINT_NAME = :n LIMIT 1"
        ),
        {"t": table, "n": fk_name},
    ).scalar() is not None


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

    if not _column_exists(bind, "offboarding_processes", "default_handover_person_id"):
        op.add_column(
            "offboarding_processes",
            sa.Column(
                "default_handover_person_id",
                id_type_for("identity_persons"),
                nullable=True,
            ),
        )

    # Mirror collation before FK creation (TiDB strict-FK guard).
    normalize_fk_column_collation(
        table="offboarding_processes",
        column="default_handover_person_id",
        parent_table="identity_persons",
        nullable=True,
    )

    fk_name = "fk_offboarding_processes_default_handover_person"
    if not _fk_exists(bind, "offboarding_processes", fk_name):
        # Defensively null any orphan ref before adding the FK. The
        # column was just created so this is a no-op on a fresh DB;
        # belt-and-suspenders for the case where a hand-edited row
        # already exists.
        bind.execute(
            sa.text(
                """
                UPDATE offboarding_processes o
                LEFT JOIN identity_persons p
                  ON p.id = o.default_handover_person_id
                   SET o.default_handover_person_id = NULL
                 WHERE o.default_handover_person_id IS NOT NULL
                   AND p.id IS NULL
                """
            )
        )
        op.create_foreign_key(
            fk_name,
            "offboarding_processes",
            "identity_persons",
            ["default_handover_person_id"],
            ["id"],
            ondelete="SET NULL",
        )

    # Backfill from manager_id on in-flight processes only. Completed /
    # cancelled rows have no live UI surface that would consume the
    # prefill — leaving them NULL avoids a misleading historical write.
    # The HRIS manager_id column is itself nullable; the join filters
    # NULLs naturally.
    bind.execute(
        sa.text(
            """
            UPDATE offboarding_processes o
            INNER JOIN identity_persons leaver ON leaver.id = o.person_id
            INNER JOIN identity_persons mgr     ON mgr.id    = leaver.manager_person_id
               SET o.default_handover_person_id = leaver.manager_person_id
             WHERE o.default_handover_person_id IS NULL
               AND o.status NOT IN ('completed', 'cancelled')
               AND leaver.manager_person_id IS NOT NULL
            """
        )
    )


def downgrade() -> None:
    bind = op.get_bind()
    fk_name = "fk_offboarding_processes_default_handover_person"
    if _fk_exists(bind, "offboarding_processes", fk_name):
        op.drop_constraint(
            fk_name, "offboarding_processes", type_="foreignkey",
        )
    if _column_exists(bind, "offboarding_processes", "default_handover_person_id"):
        op.drop_column("offboarding_processes", "default_handover_person_id")