"""identity_persons: SF-verbatim label columns for Google Employee_Details.

Adds four nullable columns to ``identity_persons``, each holding the
string SF itself translates a code into (pushed VERBATIM to Google
customSchemas ``Employee_Details`` — no Helix-side value-map):

  * ``office_mail_group``  — SF ``EmailGroup`` picklist label for
    ``EmpJob.customString7`` (2659 → ``sh-office@pingcap.com``).
  * ``job_function``       — SF ``FOJobFunction.name`` for
    ``customString19`` (JF0003 → ``Engineer``).
  * ``job_family``         — SF ``cust_JobFamily.externalName`` for
    ``customString21`` (JFM0002 → ``Sales-Account Executive``).
  * ``residence_country``  — ISO-3166 alpha-2 of the work location
    (residence), derived from ``work_location_code`` prefix
    (CHN0075 → ``CN``). Distinct from ``country`` (FOCompany legal
    entity); this is the axis the g-all-china / g-all-abroad dynamic
    groups use.

All nullable; the SF sync writes them, the google_schema target maps
them to Google. Pairs with migration 363 (which seeded the
Employee_Details target on the value_map approach — superseded here:
the target mapping is re-pointed to these columns by a follow-up).

Revision ID: 364
Revises: 363
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "364"
down_revision = "363"
branch_labels = None
depends_on = None

TABLE = "identity_persons"
COLUMNS = {
    "office_mail_group": sa.String(length=128),
    "job_function": sa.String(length=128),
    "job_family": sa.String(length=128),
    "residence_country": sa.String(length=8),
}


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 name, type_ in COLUMNS.items():
        if not _column_exists(bind, TABLE, name):
            op.add_column(TABLE, sa.Column(name, type_, nullable=True))


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