"""Add international-assignment (外派) fields to identity_persons.

Revision ID: 392
Revises: 391
Create Date: 2026-07-07

PingCAP models global assignments as custom fields on SF EmpJob (field IDs
confirmed via live $metadata labels 2026-07-07):
  customString44 → 外派前国家 (home country, ISO-3)
  customString38 → 外派后国家 (host country, ISO-3)
  customDate2    → 外派开始日期 (assignment start)
  customDate4    → 外派结束日期 (assignment end)
  customString39 → 外派状态 (assignment status — RAW picklist code; the SF
                   status picklist is not cleanly resolvable over OData, so
                   the raw code is stored for fidelity, mirroring
                   sf_downstream_push_code)

Supplementary context surfaced in HR reports / person detail; does NOT
repoint directory placement. All nullable, no FK (TiDB-safe), sparse —
write-when-present, never NULL-out. ISO codes String(8) match
``residence_country`` / ``mobile_country_code``.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "392"
down_revision = "391"
branch_labels = None
depends_on = None

_TABLE = "identity_persons"
_COLS: list[tuple[str, sa.types.TypeEngine]] = [
    ("assignment_home_country", sa.String(8)),
    ("assignment_host_country", sa.String(8)),
    ("assignment_start_date", sa.Date()),
    ("assignment_end_date", sa.Date()),
    ("assignment_status_code", sa.String(16)),
]


def _has_column(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 name, col_type in _COLS:
        if not _has_column(bind, _TABLE, name):
            op.add_column(_TABLE, sa.Column(name, col_type, nullable=True))


def downgrade() -> None:
    bind = op.get_bind()
    for name, _ in reversed(_COLS):
        if _has_column(bind, _TABLE, name):
            op.drop_column(_TABLE, name)