"""Add mobile_country_code to identity_persons.

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

ISO-3166 alpha-2 of the MOBILE number itself, synced from SF
``PerPhone.countryCode`` — a ``CountryCode`` picklist option whose
``externalCode`` is the ISO-2 (verified live 2026-07-07: option 1734 →
``externalCode='SG'``, label ``+65 (SG)``).

Motivation: an international-assignment employee (e.g. home country CN, on
assignment to SG) carries a host-country phone (+65). Deriving the dialing
code from the person's home ``country`` (CN → +86) produces an invalid
number the downstream vendor (Feishu) rejects with ``41004 mobile is
invalid``. Storing the phone's OWN country lets the phone formatter use the
correct dialing code (+65), falling back to ``country`` only when absent.

Nullable, no FK (TiDB-safe), sparse — write-when-present, never NULL-out.
``String(8)`` matches the sibling ISO-code column ``residence_country``.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

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

_TABLE = "identity_persons"
_COL = "mobile_country_code"


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()
    if not _has_column(bind, _TABLE, _COL):
        op.add_column(_TABLE, sa.Column(_COL, sa.String(8), nullable=True))


def downgrade() -> None:
    bind = op.get_bind()
    if _has_column(bind, _TABLE, _COL):
        op.drop_column(_TABLE, _COL)