"""identity_persons — HRIS work-relationship role: HR Ops.

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

* ``hris_hrops_person_id`` — SF relationshipType ``2450`` (HR Ops)

Verified live against the PingCAP SF tenant (sf-cn-prod, 2026-07-06):
shuai.feng (P0989) → HR Ops = P0067 (肖静 Jenny Xiao); cross-checked that
46 people point to P0067 via relationshipType 2450, confirming 2450 = HR
Ops (distinct from 2448=HRBP / 2447=调薪负责人). HR Ops is a second HR
contact per employee; the new-hire "account created" card now also fans out
to this person (alongside the HRBP + IT contact).

Deliberately NO DB-level FK constraint (mirrors ``hris_hrbp_person_id`` /
migration 291): self-referential CHAR(36) → identity_persons.id FKs trip
TiDB collation 3780 on deploy; the value is a resolved person id consumed in
code. CHAR(36) via ``id_type_for`` matches the parent id column type.

Revision ID: 388
Revises: 387
"""
from __future__ import annotations

import sqlalchemy as sa

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

revision = "388"
down_revision = "387"
branch_labels = None
depends_on = None

TABLE = "identity_persons"
COL = "hris_hrops_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()
    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()
    if _column_exists(bind, TABLE, COL):
        op.drop_column(TABLE, COL)