"""identity_persons.hrbp_person_id — cached HR Business Partner.

Revision ID: 111
Revises: 110
Create Date: 2026-05-02

Adds a self-referential FK on ``identity_persons`` so each person can
carry a cached pointer to their HR Business Partner (HRBP). Source of
truth is Feishu's per-user ``custom_attrs`` (the HRBP-typed entry),
resolved on demand and written back here so subsequent SLA escalations
don't have to round-trip Feishu twice (leaver → HRBP user_id → HRBP
email → match) on every dispatch.

Usage: the HRBP-targeted SLA URGENT/BREACH dispatch (see
``leaver_blocked.sla_service``) reads this column when present, falls
back to a live Feishu lookup when NULL, and writes the resolved id
back so the next dispatch is a single SELECT. Cache invalidation is
implicit — the next failed dispatch re-resolves and overwrites.

NULL means "not yet resolved" or "no HRBP configured for this person";
both are valid (system accounts, contractors). The SLA dispatcher
falls back to the it_admin pool in either case.

Why a column instead of a side table: HRBP is 1:1 with person, mutates
rarely (HR re-orgs are quarterly at most), and is on the hot path of
every SLA tick. A column keeps the read to a single PK lookup. A
separate ``person_hrbp_assignments`` table would only be justified if
we needed history; we don't — Feishu is the system of record and we
can always re-resolve.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "111"
down_revision = "110"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "identity_persons",
        sa.Column("hrbp_person_id", sa.CHAR(36), nullable=True),
    )
    op.create_index(
        "idx_identity_persons_hrbp",
        "identity_persons",
        ["hrbp_person_id"],
    )
    # Self-referential FK — same pattern as ``manager_person_id``.
    # Named so the down-revision can drop it cleanly.
    op.create_foreign_key(
        "fk_identity_persons_hrbp",
        "identity_persons",
        "identity_persons",
        ["hrbp_person_id"],
        ["id"],
        ondelete="SET NULL",
    )


def downgrade() -> None:
    op.drop_constraint(
        "fk_identity_persons_hrbp", "identity_persons", type_="foreignkey",
    )
    op.drop_index("idx_identity_persons_hrbp", table_name="identity_persons")
    op.drop_column("identity_persons", "hrbp_person_id")