"""HR leave absences — persist SF EmployeeTime time-off requests.

The leave sync already fetched ``EmployeeTime`` rows (to stamp reason/type
onto ``hr_leave_postings``) but discarded them. This table persists the
request span itself (startDate / endDate / approvalStatus) so the External
API can answer "is this person on leave on date X" — which the accounting
``hr_leave_postings`` table structurally cannot (no span; pending requests
produce no posting).

Convention (mirrors migration 324): ``person_id`` is a plain id column
(NO FK to identity_persons — TiDB-FK-safe), sized via
``id_type_for('identity_persons')`` + ``table_kw_for`` for the parent
charset. No intra-feature FK on this table.

Revision ID: 453
Revises: 452
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

from app.core.migration_helpers import id_type_for, table_kw_for

revision = "453"
down_revision = "452"
branch_labels = None
depends_on = None

ABSENCES = "hr_leave_absences"

_DAYS = sa.Numeric(8, 2)


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


def upgrade() -> None:
    bind = op.get_bind()
    person_id_type = id_type_for("identity_persons")
    tkw = table_kw_for("identity_persons")

    if not _table_exists(bind, ABSENCES):
        op.create_table(
            ABSENCES,
            sa.Column("id", person_id_type, nullable=False),
            sa.Column("external_code", sa.String(64), nullable=False),
            sa.Column("person_id", person_id_type, nullable=False),
            sa.Column("hris_person_id", sa.String(64), nullable=True),
            sa.Column("hris_instance_id", sa.String(36), nullable=True),
            sa.Column("start_date", sa.Date, nullable=True),
            sa.Column("end_date", sa.Date, nullable=True),
            sa.Column("time_type", sa.String(64), nullable=True),
            sa.Column("quantity_days", _DAYS, nullable=True),
            sa.Column("approval_status", sa.String(32), nullable=True),
            sa.Column("synced_at", sa.DateTime(timezone=True), nullable=False,
                      server_default=sa.func.now()),
            sa.Column("created_at", sa.DateTime(timezone=True), nullable=False,
                      server_default=sa.func.now()),
            sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False,
                      server_default=sa.func.now()),
            sa.Column("deleted_at", sa.DateTime(timezone=True), nullable=True),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("external_code",
                                name="uq_hr_leave_absences_external_code"),
            **tkw,
        )
        op.create_index(
            "ix_hr_leave_absences_person_span", ABSENCES,
            ["person_id", "start_date", "end_date"],
        )
        op.create_index(
            "ix_hr_leave_absences_hris_person", ABSENCES, ["hris_person_id"],
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, ABSENCES):
        op.drop_table(ABSENCES)