"""identity_persons.first_name + last_name (for unambiguous email order).

Revision ID: 243
Revises: 242
Create Date: 2026-06-02

Renumbered 240 → 243 after rebase 2026-06-02 — see migration 242
header note for the chain context.

Add SF ``User.firstName`` / ``User.lastName`` as first-class columns on
``identity_persons``. Previously the Google account provisioner derived
the email local-part by pinyinising ``display_name`` — for a Chinese
employee like 陈伟 (姓=陈, 名=伟) that produced ``chen.wei`` (family.given),
the opposite of the standard ``given.family`` Western convention the
org's mailbox naming follows. Storing the SF-authoritative split lets
the email builder write ``wei.chen`` deterministically without trying
to detect a compound family name like 欧阳 / 司马 / 上官 from the pinyin
tokens alone.

Both columns are nullable: existing rows backfill on the next HR sync
that touches them, and the email builder falls back to the legacy
pinyin path (with the corrected order swap) when either is empty.

Idempotent: ``ADD COLUMN`` guarded with information_schema. Downgrade
drops both columns.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "243"
down_revision = "242"
branch_labels = None
depends_on = None


def _column_exists(table: str, column: str) -> bool:
    conn = op.get_bind()
    result = conn.execute(
        sa.text(
            "SELECT COUNT(*) FROM information_schema.columns "
            "WHERE table_schema = DATABASE() "
            "AND table_name = :t AND column_name = :c"
        ),
        {"t": table, "c": column},
    )
    return (result.scalar() or 0) > 0


def upgrade() -> None:
    if not _column_exists("identity_persons", "first_name"):
        op.add_column(
            "identity_persons",
            sa.Column("first_name", sa.String(length=128), nullable=True),
        )
    if not _column_exists("identity_persons", "last_name"):
        op.add_column(
            "identity_persons",
            sa.Column("last_name", sa.String(length=128), nullable=True),
        )


def downgrade() -> None:
    if _column_exists("identity_persons", "last_name"):
        op.drop_column("identity_persons", "last_name")
    if _column_exists("identity_persons", "first_name"):
        op.drop_column("identity_persons", "first_name")