"""identity_persons — HRIS work-relationship roles (HRBP + Budget Owner).

Adds two nullable self-referential person-id columns populated by the
SuccessFactors sync from the ``EmpJobRelationships`` (工作关系) entity:

* ``hris_hrbp_person_id``        — SF relationshipType ``2448`` (HRBP)
* ``hris_budget_owner_person_id``— SF relationshipType ``2447`` (调薪负责人,
  which 汇联易 surfaces as the department "Budget Owner" role)

Verified live against the PingCAP SF tenant (sf-cn-prod, 2026-06-16):
shuai.feng (P0989) → HRBP=P01115 (朱赟), 调薪负责人=P0843 (倪捷), matching
the 汇联易 department roles exactly. These feed the 汇联易 dept sync's
``departmentPositionList`` (positionCode 6105=HRBP, 6102=Budget Owner).

Deliberately NO DB-level FK constraint (mirrors ``hris_instance_id``):
self-referential CHAR(36) → identity_persons.id FKs have repeatedly tripped
TiDB collation 3780 on deploy; the value is a resolved person id consumed
in code, no ORM relationship is needed, so a plain column is both safer and
sufficient. CHAR(36) via ``id_type_for`` matches the parent id column type.
"""
from __future__ import annotations

import sqlalchemy as sa

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

revision = "291"
down_revision = "290"
branch_labels = None
depends_on = None

TABLE = "identity_persons"
COLS = ("hris_hrbp_person_id", "hris_budget_owner_person_id")


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"
        ),
        {"t": table, "c": column},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    for col in COLS:
        if not _column_exists(bind, TABLE, col):
            op.add_column(
                TABLE,
                sa.Column(col, id_type_for("identity_persons"), nullable=True),
            )


def downgrade() -> None:
    bind = op.get_bind()
    for col in COLS:
        if _column_exists(bind, TABLE, col):
            op.drop_column(TABLE, col)