"""HR leave / time-off domain models.

Mirrors SuccessFactors EC Time-Off into Helix so the data shown in SF's
「假期余额」 (TimeAccount) and 「账户假期记录」 (TimeAccountDetail) screens
becomes first-class, self-service-visible data in Helix instead of being
collapsed into the four ``identity_persons.annual_leave_*`` snapshot
columns (which only ever covered 法定年假 + 福利年假 carry/used).

Two tables, faithful to the SF shape (one Helix row per SF entity row):

* :class:`HrLeaveAccount` — one row per SF ``TimeAccount`` = a
  (person, accountType, fiscal-year) bucket. Carries the derived
  ``balance_days`` (可用余额, = Σ bookingAmount), ``entitled_days`` and
  ``used_days``. **Each TimeAccount is its own row** — 调休假 with four
  separate SF accounts (0.68 / 1 / 1 / 0.37 天) lands as four rows, not
  a merged total (user decision 2026-06-23).
* :class:`HrLeavePosting` — one row per SF ``TimeAccountDetail`` = a
  ledger line (accrual / booking / adjustment). Carries the per-line
  amount, the running ``balance_after`` (之后的剩余假期), the SF creator
  and creation timestamp, and any comment.

Sync writes both tables idempotently keyed on ``external_code`` (the SF
``externalCode`` UUID) and soft-deletes rows no longer returned by SF.
See ``app/domains/hr/leave_service.py::HrLeaveService.sync_leave_data``.

FK note (TiDB): per the repo convention (migration 322), ``person_id``
is a **plain id column with NO FK** to ``identity_persons`` — TiDB
rejects CHAR↔VARCHAR / divergent-collation FK pairs (3780). The
migration sizes it with ``id_type_for('identity_persons')`` so the
type/charset matches the parent. The only real FK is the intra-feature
posting → account ref, whose charset is matched via ``table_kw_for`` so
both ends agree.
"""
from __future__ import annotations

import uuid
from datetime import date, datetime

from sqlalchemy import (
    Boolean,
    Date,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    Numeric,
    String,
    Text,
    UniqueConstraint,
)
from sqlalchemy.orm import Mapped, mapped_column, relationship
from sqlalchemy.sql import func

from app.core.database import Base
from app.core.sql_types import GUID as Uuid

# Days are stored as DECIMAL with 2 fractional places: SF surfaces
# fractional balances like 0.68 / 0.37 天 that the legacy DECIMAL(4,1)
# columns would have silently rounded away.
_DAYS = Numeric(8, 2)


class HrLeaveAccount(Base):
    """One SF ``TimeAccount`` ledger bucket, mirrored into Helix."""

    __tablename__ = "hr_leave_accounts"
    __table_args__ = (
        UniqueConstraint("external_code", name="uq_hr_leave_accounts_external_code"),
        Index("ix_hr_leave_accounts_person", "person_id"),
        Index("ix_hr_leave_accounts_hris_person", "hris_person_id"),
        Index("ix_hr_leave_accounts_type", "account_type"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)

    # SF TimeAccount.externalCode (UUID string) — the idempotency key the
    # sync upserts on and the FK the postings join back to.
    external_code: Mapped[str] = mapped_column(String(64), nullable=False)

    # Owner. Plain id column (NO FK) sized to identity_persons.id — see
    # module docstring for the TiDB-FK-safe convention.
    person_id: Mapped[str] = mapped_column(String(36), nullable=False)
    # SF userId — kept denormalised so the sync can match SF rows to
    # persons without a join and so a row survives a person re-key.
    hris_person_id: Mapped[str | None] = mapped_column(String(64))
    # Which HRIS region produced this row (multi-instance fan-out).
    hris_instance_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)

    # SF accountType code (e.g. CHN_AnnualLeave / CHN_PL / CHN_SickLeave
    # / CHN_CompLeave). Tenant-configured; not an enum.
    account_type: Mapped[str] = mapped_column(String(64), nullable=False)
    # Human label resolved from SF TimeAccountType (法定年假 / 福利年假 /
    # 带薪病假 / 调休假). NULL when SF didn't expose the label entity — the
    # frontend then falls back to a code→i18n map, then the raw code.
    account_type_label: Mapped[str | None] = mapped_column(String(128))

    # Fiscal year the bucket belongs to (derived from start_date.year).
    fiscal_year: Mapped[int | None] = mapped_column(Integer)
    start_date: Mapped[date | None] = mapped_column(Date)
    end_date: Mapped[date | None] = mapped_column(Date)
    booking_start_date: Mapped[date | None] = mapped_column(Date)
    booking_end_date: Mapped[date | None] = mapped_column(Date)
    account_closed: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="0",
    )

    # Derived from the posting lines (Σ over this account's details):
    #   balance_days  = Σ bookingAmount               (可用余额)
    #   entitled_days = Σ bookingAmount where amount>0 (累计授予)
    #   used_days     = Σ |bookingAmount| of leave-taken booking types
    balance_days: Mapped[float | None] = mapped_column(_DAYS)
    entitled_days: Mapped[float | None] = mapped_column(_DAYS)
    used_days: Mapped[float | None] = mapped_column(_DAYS)

    # True when start_date <= today <= end_date — the "本年度" bucket the
    # self-service card highlights. Computed at sync time against the
    # sync's reference date.
    is_current: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default="0",
    )

    synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )
    # Soft-delete: an account that stops being returned by SF (rare —
    # closed buckets usually persist) is tombstoned, not hard-deleted,
    # so its postings keep their FK and history stays auditable.
    deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    postings: Mapped[list[HrLeavePosting]] = relationship(
        "HrLeavePosting",
        back_populates="account",
        cascade="all, delete-orphan",
        order_by="HrLeavePosting.booking_date",
    )


class HrLeavePosting(Base):
    """One SF ``TimeAccountDetail`` ledger line (账户假期记录 行)."""

    __tablename__ = "hr_leave_postings"
    __table_args__ = (
        UniqueConstraint("external_code", name="uq_hr_leave_postings_external_code"),
        Index("ix_hr_leave_postings_account", "leave_account_id"),
        Index("ix_hr_leave_postings_person_date", "person_id", "booking_date"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)

    # SF TimeAccountDetail.externalCode — the idempotency key.
    external_code: Mapped[str] = mapped_column(String(64), nullable=False)

    leave_account_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("hr_leave_accounts.id", ondelete="CASCADE"),
        nullable=False,
    )
    # SF TimeAccount_externalCode — kept so the sync can map a detail row
    # to its account before the account UUID→id resolution.
    account_external_code: Mapped[str | None] = mapped_column(String(64))

    person_id: Mapped[str] = mapped_column(String(36), nullable=False)

    # ACCRUAL / EMPLOYEE_TIME / DEDUCTION / MANUAL_ADJUSTMENT … (发布类型).
    booking_type: Mapped[str] = mapped_column(String(32), nullable=False)
    booking_amount: Mapped[float] = mapped_column(_DAYS, nullable=False)  # 数额
    booking_unit: Mapped[str | None] = mapped_column(String(16))
    booking_date: Mapped[date | None] = mapped_column(Date)  # 发布日期
    accrual_period_id: Mapped[str | None] = mapped_column(String(64))

    # Running balance AFTER this line (之后的剩余假期). Computed at sync
    # time by accumulating bookingAmount over the account's lines sorted
    # by (booking_date, created_at_source). balance_before is derived on
    # read as ``balance_after - booking_amount``.
    balance_after: Mapped[float | None] = mapped_column(Numeric(9, 2))

    created_by_source: Mapped[str | None] = mapped_column(String(128))  # 创建者
    created_at_source: Mapped[datetime | None] = mapped_column(  # 创建日期
        DateTime(timezone=True),
    )
    comment: Mapped[str | None] = mapped_column(Text)  # 评论

    # Request context, matched from SF ``EmployeeTime`` (the time-off
    # request) by (userId, booking_date ∈ [start, end]) at sync time —
    # TimeAccountDetail itself carries none of this. ``reason`` = the
    # employee's free-text 事由 (EmployeeTime.comment), ``leave_type`` =
    # the absence-type code, ``request_status`` = approval status. All
    # NULL when no matching request / the EmployeeTime entity is
    # unavailable on the tenant. Added migration 326.
    reason: Mapped[str | None] = mapped_column(Text)
    leave_type: Mapped[str | None] = mapped_column(String(64))
    request_status: Mapped[str | None] = mapped_column(String(32))

    synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )
    deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    account: Mapped[HrLeaveAccount] = relationship(
        "HrLeaveAccount", back_populates="postings",
    )


class HrLeaveAbsence(Base):
    """One SF ``EmployeeTime`` time-off REQUEST (the leave span itself).

    Distinct from :class:`HrLeavePosting`: a posting is an *accounting*
    ledger line (a deduction that only exists once a leave is approved and
    booked), so it can never answer "is this person on leave on date X" —
    it carries no request span and pending requests produce no posting.
    ``EmployeeTime`` is the request: it has ``startDate`` / ``endDate`` and
    the true ``approvalStatus`` (including PENDING). The leave sync already
    fetched these rows (to stamp reason/type onto postings) but discarded
    them; this table persists them so the External API can serve an
    availability feed (e.g. an on-call scheduler checking whether a person
    is out).

    Idempotency key ``external_code`` (SF ``externalCode``). Rows SF stops
    returning for a synced person are soft-deleted. ``person_id`` is a
    plain id column (NO FK, TiDB-safe) sized to identity_persons — see the
    module docstring.

    Deliberately does NOT store the free-text ``comment`` (事由): the
    availability feed must never expose why someone is out, and the leave
    *type* is coarsened to a category at the API boundary so a specific
    absence type (sick / parental) is never leaked to a scheduler.
    """

    __tablename__ = "hr_leave_absences"
    __table_args__ = (
        UniqueConstraint("external_code", name="uq_hr_leave_absences_external_code"),
        Index("ix_hr_leave_absences_person_span", "person_id", "start_date", "end_date"),
        Index("ix_hr_leave_absences_hris_person", "hris_person_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)

    # SF EmployeeTime.externalCode — the idempotency key.
    external_code: Mapped[str] = mapped_column(String(64), nullable=False)

    # Owner. Plain id column (NO FK) sized to identity_persons.id.
    person_id: Mapped[str] = mapped_column(String(36), nullable=False)
    # SF userId — the match key from the SF row to the person.
    hris_person_id: Mapped[str | None] = mapped_column(String(64))
    hris_instance_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)

    # The leave span (inclusive). Both nullable defensively — a row SF
    # returns without a parseable span is stored but never matches a
    # window query (guarded in the sync + endpoint).
    start_date: Mapped[date | None] = mapped_column(Date)
    end_date: Mapped[date | None] = mapped_column(Date)

    # SF timeType absence-type code (CHN_ANNL / CHN_PSL / …). Kept for
    # internal inspection; the External API coarsens it to a category and
    # never returns the raw type.
    time_type: Mapped[str | None] = mapped_column(String(64))
    # SF quantityInDays (0.5 for a half-day). DECIMAL to preserve halves.
    quantity_days: Mapped[float | None] = mapped_column(_DAYS)
    # SF approvalStatus: APPROVED / PENDING / PENDING_CANCELLATION / … The
    # External API decides which statuses mean "unavailable"; NULL (a
    # tenant that stripped the field) is treated conservatively as
    # unknown-but-unavailable there.
    approval_status: Mapped[str | None] = mapped_column(String(32))

    synced_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now(),
        onupdate=func.now(),
    )
    deleted_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))