"""identity_persons: gender / employee_type_code / office_code for Feishu sync.

Adds three HRIS attributes pulled from SuccessFactors and projected to
Feishu by the ``feishu_contact`` identity-sync target (HRIS → Helix →
Feishu, replacing the legacy 北森 → Authing → 飞书 chain):

  * ``gender``              ← SF PerPersonal.gender (``M`` / ``F``)
  * ``employee_type_code``  ← SF EmpJob.customString25 (1070 正式 …)
  * ``office_code``         ← SF EmpJob.customString7 (2659 sh-office …)

All stored RAW (the picklist code); the Feishu target's value-maps do
the human/enum translation — same convention as ``country`` holding the
ISO-3 code. All nullable, no FK (TiDB-safe).

Revision ID: 344
Revises: 343
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "344"
down_revision = "343"
branch_labels = None
depends_on = None

TABLE = "identity_persons"
COLUMNS = ("gender", "employee_type_code", "office_code")


def _column_exists(bind, table: str, column: str) -> bool:
    return bool(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())


def upgrade() -> None:
    bind = op.get_bind()
    for column in COLUMNS:
        if not _column_exists(bind, TABLE, column):
            op.add_column(TABLE, sa.Column(column, sa.String(16), nullable=True))


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