"""identity_persons: HRIS extra fields (seniority_date / probation_end_date / fte).

Revision ID: 240
Revises: 239
Create Date: 2026-06-02

Pairs with the SF sync expansion that also flips
``work_start_date`` source from ``EmpEmployment.originalStartDate``
to ``EmpEmployment.firstDateWorked`` (the real 参加工作日期 / 社会工龄 N 起点).

Adds three new identity_persons columns:

* ``seniority_date`` (DATE, nullable) — SF EmpEmployment.seniorityDate.
  司龄 S 起点, distinct from ``hire_date`` in rehire / employment-segment
  edge cases (3/500 employees in prod survey 2026-06-02). When equal to
  ``hire_date`` (the common case) the field is harmless duplication; when
  different it carries the canonical 司龄 anchor SF tracks across re-hires
  / promotions / employment-segment resets.

* ``probation_end_date`` (DATE, nullable) — SF EmpJob.probationPeriodEndDate.
  72% populated in prod survey. Needed for: 转正 流程, 试用期 福利年假
  起算 (some policies exclude probation), IT 设备 长期分配 vs 临时分配.

* ``fte`` (NUMERIC(3,2), nullable) — SF EmpJob.fte (full-time equivalent).
  100% populated; 99.6% are 1.0 (full-time). Needed for 年假 proration
  when a person is part-time (fte=0.5 etc.).

Idempotent — every DDL goes through ``_column_exists``. Downgrade drops
the three columns.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "240"
down_revision = "239"
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", "seniority_date"):
        op.add_column(
            "identity_persons",
            sa.Column("seniority_date", sa.Date(), nullable=True),
        )
    if not _column_exists("identity_persons", "probation_end_date"):
        op.add_column(
            "identity_persons",
            sa.Column("probation_end_date", sa.Date(), nullable=True),
        )
    if not _column_exists("identity_persons", "fte"):
        op.add_column(
            "identity_persons",
            sa.Column("fte", sa.Numeric(3, 2), nullable=True),
        )


def downgrade() -> None:
    if _column_exists("identity_persons", "fte"):
        op.drop_column("identity_persons", "fte")
    if _column_exists("identity_persons", "probation_end_date"):
        op.drop_column("identity_persons", "probation_end_date")
    if _column_exists("identity_persons", "seniority_date"):
        op.drop_column("identity_persons", "seniority_date")