"""Add request context (事由/类型/审批) to hr_leave_postings.

``TimeAccountDetail`` (the source of hr_leave_postings) only records the
accounting deduction — date, amount, booking type. The leave *reason* /
absence type / approval lives on SF's ``EmployeeTime`` (the time-off
request). The leave sync now pulls EmployeeTime and matches it back onto
the deduction postings by (userId, booking_date ∈ [start, end]),
populating these three columns. All nullable — NULL when no matching
request or the EmployeeTime entity is unavailable on the tenant.

Revision ID: 326
Revises: 325
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "326"
down_revision = "325"
branch_labels = None
depends_on = None

TABLE = "hr_leave_postings"


def _has_col(bind, col: 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": col},
    ).scalar() is not None


def _table_exists(bind) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :t"
        ),
        {"t": TABLE},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind):
        return
    if not _has_col(bind, "reason"):
        op.add_column(TABLE, sa.Column("reason", sa.Text, nullable=True))
    if not _has_col(bind, "leave_type"):
        op.add_column(TABLE, sa.Column("leave_type", sa.String(64), nullable=True))
    if not _has_col(bind, "request_status"):
        op.add_column(TABLE, sa.Column("request_status", sa.String(32), nullable=True))


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind):
        return
    for col in ("request_status", "leave_type", "reason"):
        if _has_col(bind, col):
            op.drop_column(TABLE, col)