"""Add country/city/mobile/personal_email/preferred_language to identity_persons.

Revision ID: 048
Revises: 047
Create Date: 2026-04-22

Five HRIS-sourced scalar columns added to ``identity_persons``:
  * ``country``             — derived via FoLocation master data lookup
    (keyed on EmpJob.location → FoLocation.country)
  * ``city``                — same source, FoLocation.city
  * ``mobile_phone``        — SF PerPhone.phoneNumber (phoneType='1043')
  * ``personal_email``      — SF PerEmail.emailAddress (emailType='1047')
  * ``preferred_language``  — SF User.defaultLocale (e.g. ``en_US``)

``mobile_phone`` and ``personal_email`` are PII — the UI masks them and
reveal requires an audit-logged API call. The columns store plaintext
so the reveal endpoint doesn't need decryption round-trips; access
control is at the API layer.

All five columns are nullable — SF configurations vary by instance,
and old rows won't be backfilled until the next scheduled HRIS sync.
"""
import sqlalchemy as sa

from alembic import op

revision = "048"
down_revision = "047"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Plain ``op.add_column`` so MySQL / TiDB use native ``ALTER TABLE
    # ADD COLUMN`` — batch_alter_table would rebuild the table via a
    # copy-move strategy that MySQL doesn't need and that also drops
    # newer columns (e.g. hashed_password from migration 007) if its
    # internal reflection is stale.
    op.add_column("identity_persons", sa.Column("country", sa.String(64), nullable=True))
    op.add_column("identity_persons", sa.Column("city", sa.String(128), nullable=True))
    op.add_column("identity_persons", sa.Column("mobile_phone", sa.String(32), nullable=True))
    op.add_column("identity_persons", sa.Column("personal_email", sa.String(255), nullable=True))
    op.add_column("identity_persons", sa.Column("preferred_language", sa.String(16), nullable=True))


def downgrade() -> None:
    op.drop_column("identity_persons", "preferred_language")
    op.drop_column("identity_persons", "personal_email")
    op.drop_column("identity_persons", "mobile_phone")
    op.drop_column("identity_persons", "city")
    op.drop_column("identity_persons", "country")