"""HR domain models."""

import uuid
from datetime import date, datetime

from sqlalchemy import (
    JSON,
    Boolean,
    Date,
    DateTime,
    Enum,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    text,
)
from sqlalchemy.dialects import mysql
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


class HrEmployee(Base):
    """Legacy employee table retained for backward compatibility."""

    __tablename__ = "hr_employees"

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
    )
    employee_no: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    user_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("identity_persons.id"),
        nullable=False,
    )
    hire_date: Mapped[date | None] = mapped_column(Date)
    leave_date: Mapped[date | None] = mapped_column(Date)
    status: Mapped[str] = mapped_column(String(16), nullable=False, default="active")
    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(),
    )


class HrEmployeeNumber(Base):
    """工号分配台账 — the authoritative allocation ledger for employee
    numbers (工号, format ``P`` + zero-padded-5 sequence, e.g. ``P02388``).

    This is a standalone allocation registry, intentionally NOT linked to
    ``identity_persons``: HR allocates a 工号 to a name before (or without)
    the person ever existing in HRIS. Creating a row needs only a name, an
    optional hire date and a remark; the system allocates the next number.

    The next number is ``MAX(seq) + 1`` over THIS table only (after the
    one-time seed from ``identity_persons.employee_no`` in migration 249).
    ``seq`` is the integer behind the formatted ``employee_no`` so MAX is a
    cheap integer aggregate and we never string-sort (``P09999`` vs
    ``P100000``). Rows imported with a non ``P<digits>`` 工号 (legacy formats
    copied verbatim at seed time) carry ``seq = NULL`` and never drive the
    sequence.
    """

    __tablename__ = "hr_employee_numbers"
    __table_args__ = (
        # employee_no is the user-facing unique key — a GLOBAL unique index
        # across ALL rows (MySQL/TiDB have no partial indexes). A soft-deleted
        # row therefore still occupies its number; allocation (_current_max_seq
        # spans all rows) and CSV-import dedup (DB-authoritative per-row
        # savepoint) both account for that so a soft-deleted number is never
        # silently re-minted. employee_no is never NULL, so a plain unique
        # index is fine.
        Index("uq_hr_employee_numbers_no", "employee_no", unique=True),
        Index("ix_hr_employee_numbers_seq", "seq"),
        Index("ix_hr_employee_numbers_created_at", "created_at"),
        Index("ix_hr_employee_numbers_hire_date", "hire_date"),
        Index("ix_hr_employee_numbers_source", "source"),
        Index("ix_hr_employee_numbers_bu", "bu"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    employee_no: Mapped[str] = mapped_column(String(16), nullable=False)
    # Integer behind ``employee_no`` ("P02388" -> 2388). NULL for legacy
    # non-``P<digits>`` values copied at seed time (kept for the record but
    # excluded from the MAX(seq) allocation math).
    seq: Mapped[int | None] = mapped_column(Integer)
    name: Mapped[str | None] = mapped_column(String(128))
    hire_date: Mapped[date | None] = mapped_column(Date)
    remark: Mapped[str | None] = mapped_column(String(255))
    # 一级部门 (BU) — top-level org node name (e.g. "Engineering Group").
    # A free label, not a FK: it mirrors the BU derivation used across the
    # platform (top segment of the person's primary-membership org_path,
    # see ``app/identity/bu_helpers.py``). NULL when unset / not derivable
    # (a 工号 can be allocated to a name before any HRIS record exists).
    bu: Mapped[str | None] = mapped_column(String(128))
    # Provenance: 'manual' (created in the UI), 'import' (CSV), 'seed'
    # (migration 249 backfill from identity_persons).
    source: Mapped[str] = mapped_column(String(16), nullable=False, server_default="manual")
    # 创建人 (申请人). NULL for seed rows (system-generated).
    created_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id")
    )
    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))


class HrEmployeeNumberOverride(Base):
    """工号维护记录 — the audit trail AND the source of the manual
    "next 工号" floor.

    HR can manually bump the next number the allocator hands out (e.g. to
    skip a reserved range or realign with HRIS). Each manual change appends
    one row here recording the operator, the reason, and the before/after
    next number. The CURRENT floor is the ``next_seq`` of the most-recent
    row (by ``created_at``); allocation computes the next number as
    ``max(MAX(seq)+1, latest_override.next_seq)`` so a floor only ever moves
    the sequence FORWARD past already-allocated numbers, never re-mints one.

    Set-time validation guarantees ``next_seq > current MAX(seq)``, so once
    allocations catch up to a floor it becomes inert (``MAX(seq)+1`` wins)
    and the row remains purely as a maintenance-history entry.
    """

    __tablename__ = "hr_employee_number_overrides"
    __table_args__ = (
        Index("ix_hr_emp_no_overrides_created_at", "created_at"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    # The seq / canonical number HR set as the new floor for the next
    # allocation.
    next_seq: Mapped[int] = mapped_column(Integer, nullable=False)
    next_employee_no: Mapped[str] = mapped_column(String(16), nullable=False)
    # What the next number WOULD have been just before this change — kept so
    # the maintenance log reads as a clean before→after diff.
    previous_next_seq: Mapped[int | None] = mapped_column(Integer)
    previous_next_employee_no: Mapped[str | None] = mapped_column(String(16))
    reason: Mapped[str] = mapped_column(String(500), nullable=False)
    created_by: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id")
    )
    # Microsecond precision (DATETIME(6)): the current next-number floor is
    # the most-recent override's ``next_seq``, so ordering must be
    # tie-free. Whole-second DATETIME(0) (the platform default) would let
    # two changes in the same second tie and make "latest wins" ambiguous.
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True).with_variant(mysql.DATETIME(fsp=6), "mysql"),
        nullable=False,
        server_default=text("CURRENT_TIMESTAMP(6)"),
    )


class HrSyncBatch(Base):
    """A staged HRIS sync batch awaiting IT Admin review."""

    __tablename__ = "hr_sync_batches"
    __table_args__ = (
        Index("ix_hr_sync_batches_status", "status"),
        Index("ix_hr_sync_batches_instance", "hris_instance_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    triggered_by: Mapped[str] = mapped_column(String(255), nullable=False)
    # ``manual`` = admin clicked "同步" in UI;
    # ``scheduled`` = worker auto-tick;
    # ``webhook`` = SF pushed event.
    trigger_source: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default="manual"
    )
    # ``full`` = every active user; ``incremental`` = only users with
    # lastModifiedDateTime >= since; ``window`` = hire+termination
    # events in a date range.
    #
    # NOTE: ``full`` no longer distinguishes "initial seed" from "drift
    # reconciliation" — see ``is_seed`` below. Conflating them is what
    # made the weekly reconciliation discard everything it recovered.
    sync_mode: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default="window"
    )
    # Migration 565. ``sync_mode='full'`` used to stand in for BOTH
    # "this HRIS instance is being seeded for the first time" and "this
    # is the weekly reconciliation that exists to catch what the 30-day
    # incremental window cannot see". Only the first should suppress the
    # lifecycle coordinators; conflating them meant every org move, new
    # hire and termination the WEEKLY full sync recovered was applied to
    # the person row and then dropped on the floor — no role_change
    # event, no cloud-grant review, no provisioning, no offboarding.
    #
    # Computed by ``_full_sync_is_seed`` at batch-creation time (no
    # persons AND no lifecycle events for this instance) and overridable
    # by the operator, because a deliberate historical re-import is a
    # decision someone makes, not something the word "full" should
    # decide for them.
    is_seed: Mapped[bool] = mapped_column(
        Boolean, nullable=False, server_default=text("0"), default=False,
    )
    # FK to integrations.id — which SF instance this batch synced.
    # Nullable for legacy rows and for single-instance deployments that
    # predate multi-region support.
    # Column only — FK added via migration 036. See the identical
    # comment on ``IdentityPerson.hris_instance_id`` for why the FK is
    # kept out of Python metadata.
    hris_instance_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    start_date: Mapped[date] = mapped_column(Date, nullable=False)
    end_date: Mapped[date] = mapped_column(Date, nullable=False)
    # Upper bound of ``lastModifiedDateTime`` covered by an incremental
    # sync — written on confirm so the next tick can start from here.
    since_datetime: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    until_datetime: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # ``applying`` (migration 293): the admin confirmed and the heavy
    # per-event apply is running asynchronously on a background task
    # (see ``SuccessFactorsLifecycleService.start_async_confirm``). A
    # full bootstrap batch (~500 employees / ~500 events plus org-wide
    # HRBP/Budget-Owner + manager-backfill tail passes) takes minutes —
    # far longer than the frontend's HTTP timeout — so the apply runs
    # off the request thread and the UI polls this status to terminal.
    status: Mapped[str] = mapped_column(
        Enum(
            "pending_review",
            "confirmed",
            "cancelled",
            "failed",
            # Appended last (migration 293) so the ALTER is an in-place
            # metadata-only change on MySQL/TiDB (inserting mid-list would
            # rewrite the table). Enum order is not semantically meaningful
            # here — nothing orders by ``status``.
            "applying",
            name="hr_sync_batch_status",
        ),
        nullable=False,
        default="pending_review",
    )
    events_staged: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    # Counts filled at confirm time so the sync history page can show
    # "X new, Y updated, Z deactivated" without rescanning staged rows.
    new_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    updated_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    reactivated_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    deactivated_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    skipped_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    error_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    error_message: Mapped[str | None] = mapped_column(String(1024))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )
    reviewed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    reviewed_by: Mapped[str | None] = mapped_column(String(255))
    # Async-apply bookkeeping (migration 293). ``apply_started_at`` is
    # stamped when the batch flips to ``applying``; ``apply_heartbeat_at``
    # is bumped by the apply loop every few events so the restart-safety
    # reconcile cron (``hris.resume_stuck_apply``) can tell a healthy
    # in-flight run (recent heartbeat) from one whose backend process
    # died mid-apply (stale heartbeat) and only resume the latter.
    apply_started_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    apply_heartbeat_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    staged_events: Mapped[list["HrSyncStagedEvent"]] = relationship(
        "HrSyncStagedEvent",
        back_populates="batch",
        cascade="all, delete-orphan",
        order_by="HrSyncStagedEvent.created_at",
    )


class HrSyncStagedEvent(Base):
    """A single staged event within a sync batch."""

    __tablename__ = "hr_sync_staged_events"
    __table_args__ = (
        Index("ix_hr_sync_staged_events_batch_id", "batch_id"),
        Index("ix_hr_sync_staged_events_status", "status"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    batch_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("hr_sync_batches.id", ondelete="CASCADE"), nullable=False
    )
    event_type: Mapped[str] = mapped_column(String(20), nullable=False)  # 'onboard' or 'offboard'
    user_id: Mapped[str] = mapped_column(String(128), nullable=False)
    person_data: Mapped[dict | None] = mapped_column(JSON)
    employment_data: Mapped[dict | None] = mapped_column(JSON)
    action: Mapped[str] = mapped_column(String(20), nullable=False)  # 'create', 'reactivate', 'update', 'deactivate', 'skip'
    existing_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    status: Mapped[str] = mapped_column(String(20), nullable=False, default="pending")  # 'pending', 'confirmed', 'skipped', 'failed'
    # Per-event failure reason (mirrors HrSyncBatch.error_message). When a
    # single event's apply fails, the batch still finalizes with
    # error_count>0; without this column the "who/why failed" was only in the
    # worker log. Stamped by confirm_batch's per-event except handler.
    error_message: Mapped[str | None] = mapped_column(String(1024))
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), nullable=False, server_default=func.now()
    )

    batch: Mapped["HrSyncBatch"] = relationship("HrSyncBatch", back_populates="staged_events")


class HrLifecycleEvent(Base):
    """Lifecycle events imported from HRIS."""

    __tablename__ = "hr_lifecycle_events"
    __table_args__ = (
        # UNIQUE on the natural key the code already treats as unique
        # (``_upsert_lifecycle_event`` does select-then-insert on exactly
        # these three columns). Was a plain non-unique Index, which let two
        # concurrent SuccessFactors webhook redeliveries of the same
        # (person, event_type, effective_date) both pass the existence check
        # and both INSERT → offboarding/onboarding fired twice. The unique
        # constraint + a begin_nested/IntegrityError guard in the upsert
        # makes the race-loser skip coordinator dispatch. (migration 312)
        Index(
            "ix_hr_lifecycle_events_person_type_date",
            "person_id",
            "event_type",
            "effective_date",
            unique=True,
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        primary_key=True,
        default=uuid.uuid4,
    )
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid,
        ForeignKey("identity_persons.id"),
        nullable=False,
    )
    event_type: Mapped[str] = mapped_column(String(32), nullable=False)
    detail: Mapped[dict | None] = mapped_column(JSON)
    effective_date: Mapped[date] = mapped_column(Date, nullable=False)
    # Which Helix path materialized this row. See migration 239 for the
    # full taxonomy. Drives the "sync becomes reconciliation" gate in
    # ``HrSyncService.confirm_batch``: when a webhook-sourced row for
    # the same (person, type, effective_date) already exists, the sync
    # path skips coordinator hook dispatch and only refreshes person
    # fields. Default ``'sync_confirm'`` so legacy code paths still
    # land in a meaningful bucket.
    trigger_source: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default="sync_confirm"
    )
    # When the lifecycle coordinators (provisioning / offboarding / welcome
    # mail …) actually ran for this row. NULL = never dispatched, which is
    # what re-arms dispatch on the next apply after a failed or crashed
    # attempt (#2220 — the ledger row is committed BEFORE dispatch, so
    # ``event_created`` alone can never express "committed but not yet
    # dispatched"). Migration 439 backfilled every pre-existing row with
    # ``created_at`` so the change doesn't re-dispatch history.
    dispatched_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
        nullable=True,
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True).with_variant(mysql.DATETIME(fsp=6), "mysql"),
        nullable=False,
        server_default=text("CURRENT_TIMESTAMP(6)"),
    )


# ── Mover review packet (migration 568) ─────────────────────────────
#
# PR #3315 made role changes fire at all. This is what they fire INTO.
#
# Before it, a role change reviewed exactly one thing: the person's
# cloud access grants. Measured on production, that is a small fraction
# of what a person actually holds — one senior engineer's real holdings
# are 238 items across ten categories (44 domains, 111 DNS records, 18
# software ownerships, 8 approval flows, 13 seats, 9 cloud grants …),
# every one of which kept pointing at them after they moved.
#
# The packet is a QUESTION, never an action. Issue #623's standing
# policy — no auto-revoke on an org move, because legal asked for a
# human decision after an auto-revoke hit the wrong person mid-quarter
# — is unchanged here and is not re-litigated by this table.

#: The packet is open until every reviewable item carries a decision.
ROLE_REVIEW_OPEN = "open"
ROLE_REVIEW_RESOLVED = "resolved"
#: Superseded by a later move for the same person. The old packet's
#: decisions are history, not homework — closing it is what stops a
#: reviewer answering questions about an org position the person has
#: already left again.
ROLE_REVIEW_SUPERSEDED = "superseded"
ROLE_REVIEW_STATUSES = (
    ROLE_REVIEW_OPEN, ROLE_REVIEW_RESOLVED, ROLE_REVIEW_SUPERSEDED,
)

#: How far the person moved. Deterministic — derived from the before /
#: after blob, never from the model. It sets the default expectation for
#: every item in the packet, because "changed manager inside the same
#: department" and "moved from Engineering to Sales" are not the same
#: question about the same 238 things, and pretending they are is how a
#: review surface teaches people to click through it.
MOVE_DEPARTMENT = "department_change"
MOVE_MANAGER = "manager_change"
MOVE_TITLE = "title_change"
MOVE_KINDS = (MOVE_DEPARTMENT, MOVE_MANAGER, MOVE_TITLE)

#: Per-item decisions. ``transfer`` is distinct from ``revoke``: an
#: ownership row (a domain, a system, an approval flow) has nothing to
#: revoke — somebody still has to be the answer to "who owns this".
ITEM_DECISION_KEEP = "keep"
ITEM_DECISION_REVOKE = "revoke"
ITEM_DECISION_TRANSFER = "transfer"
ITEM_DECISIONS = (
    ITEM_DECISION_KEEP, ITEM_DECISION_REVOKE, ITEM_DECISION_TRANSFER,
)


class HrRoleChangeReview(Base):
    """One review packet per role change that warrants one."""

    __tablename__ = "hr_role_change_reviews"
    __table_args__ = (
        # One packet per (person, effective_date) — the same key the
        # lifecycle event is unique on, so a replayed coordinator call
        # cannot mint a second packet for a move already under review.
        Index(
            "uq_hr_role_change_reviews_person_date",
            "person_id", "effective_date", unique=True,
        ),
        Index("ix_hr_role_change_reviews_status", "status", "created_at"),
        Index("ix_hr_role_change_reviews_reviewer", "reviewer_person_id"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    person_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"), nullable=False,
    )
    #: The ``role_change`` ledger row this packet answers. Nullable only
    #: for the pathological case where the event write lost an insert
    #: race — the packet is still worth having.
    lifecycle_event_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("hr_lifecycle_events.id"),
    )
    effective_date: Mapped[date] = mapped_column(Date, nullable=False)
    move_kind: Mapped[str] = mapped_column(String(24), nullable=False)
    status: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default=ROLE_REVIEW_OPEN,
    )

    #: Both sides of the move, copied at packet time. Duplicated from
    #: the lifecycle event's detail blob on purpose: this table is read
    #: by a reviewer months later, and resolving a dept CODE to a name
    #: then requires the org tree as it was, which nothing keeps.
    before_dept_code: Mapped[str | None] = mapped_column(String(64))
    after_dept_code: Mapped[str | None] = mapped_column(String(64))
    before_dept_path: Mapped[str | None] = mapped_column(String(512))
    after_dept_path: Mapped[str | None] = mapped_column(String(512))
    before_manager_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    after_manager_person_id: Mapped[uuid.UUID | None] = mapped_column(Uuid)
    before_job_title: Mapped[str | None] = mapped_column(String(128))
    after_job_title: Mapped[str | None] = mapped_column(String(128))

    #: Who the packet was routed to — the NEW manager, falling back to
    #: the IT admin pool when the move left the person without one.
    #: Recorded rather than derived: "who was asked" is the audit
    #: question, and the person's manager will have changed again by the
    #: time anyone looks.
    reviewer_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    notified_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    #: Set when this packet was deliberately taken OUT of the notify
    #: queue without ever having been sent. Separate from
    #: ``notified_at`` on purpose: that column's meaning is "a card for
    #: this packet really went to somebody", and an audit that cannot
    #: distinguish "was told" from "was withheld" is an audit that
    #: cannot answer the only question worth asking after an incident.
    #:
    #: Migration 610 stamps every un-notified packet that existed on
    #: 2026-09-05, because the notify path at that time reported a
    #: mover's whole INVENTORY as if it were the consequence of the
    #: move. Re-enabling notifications must not retroactively fire that
    #: backlog at the managers it was withheld from.
    notify_suppressed_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )
    notify_suppressed_reason: Mapped[str | None] = mapped_column(String(64))

    #: What kind of evidence stands behind ``delta_items`` — one of
    #: ``policy`` / ``provenance`` / ``peer`` / ``none``. See
    #: ``app.hr_lifecycle.mover_review_settings`` for what each means and
    #: which of them may unlock a push.
    #:
    #: Written as ``none`` until the delta engine lands, and that is the
    #: point: the send path asks ``settings.may_push(baseline_source,
    #: delta_items)``, so a packet whose numbers are pure inventory can
    #: never be pushed — not even by an operator who turns every switch
    #: on. This column is what makes 「没有基线,就不要推送」 an invariant
    #: rather than an intention.
    baseline_source: Mapped[str] = mapped_column(
        String(16), nullable=False, server_default="none",
    )

    #: How many reviewable holdings this MOVE actually calls into
    #: question — as opposed to ``reviewable_items``, which is how many
    #: the person happens to hold.
    #:
    #: The distinction is the whole 2026-09-05 incident: a card said
    #: 「63 项待你确认」 when the honest answer was "we do not know, and
    #: probably zero". Zero here means nobody is asked.
    delta_items: Mapped[int] = mapped_column(
        Integer, nullable=False, server_default="0",
    )

    #: The delta engine's full working, as JSON: per-``delta_kind``
    #: tallies, how much of the packet each signal could speak about,
    #: and the provisioning gap the move opened.
    #:
    #: Stored rather than recomputed on read because the answer depends
    #: on the policy table and the org tree AT COMPUTE TIME, and both
    #: move. A page that recomputed would quietly restate history.
    delta_summary: Mapped[dict | None] = mapped_column(JSON)
    delta_computed_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )

    total_items: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    reviewable_items: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    decided_items: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    #: Resource types whose scan raised. A packet that silently dropped a
    #: category reads as "not much to decide" — exactly the wrong
    #: conclusion. Surfaced on the page and in the card.
    failed_scan_types: Mapped[list | None] = mapped_column(JSON)

    #: Model narrative. NULL until generated; a packet is fully usable
    #: without it, and the deterministic per-type grouping is what the
    #: reviewer actually acts on. ``ai_failure_code`` records WHY it is
    #: absent so the page can say so instead of rendering a blank.
    ai_summary_zh: Mapped[str | None] = mapped_column(Text)
    ai_summary_en: Mapped[str | None] = mapped_column(Text)
    ai_model: Mapped[str | None] = mapped_column(String(64))
    ai_generated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    ai_failure_code: Mapped[str | None] = mapped_column(String(64))

    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(),
    )
    resolved_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    items: Mapped[list["HrRoleChangeReviewItem"]] = relationship(
        "HrRoleChangeReviewItem",
        back_populates="review",
        cascade="all, delete-orphan",
    )


class HrRoleChangeReviewItem(Base):
    """One thing the person holds, and what was decided about it."""

    __tablename__ = "hr_role_change_review_items"
    __table_args__ = (
        # The natural key. Makes re-scanning a packet idempotent: a
        # second scan re-finds the same holdings and must not duplicate
        # them or lose the decisions already made about them.
        Index(
            "uq_hr_rc_review_items_resource",
            "review_id", "resource_type", "resource_id", unique=True,
        ),
        Index("ix_hr_rc_review_items_pending", "review_id", "decision"),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    review_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("hr_role_change_reviews.id"), nullable=False,
    )
    #: The offboarding taxonomy's resource type — deliberately the SAME
    #: vocabulary, so leaver and mover describe a person's holdings in
    #: one language and a new category cannot exist in one and not the
    #: other without a test failing.
    resource_type: Mapped[str] = mapped_column(String(32), nullable=False)
    resource_id: Mapped[uuid.UUID] = mapped_column(Uuid, nullable=False)
    label: Mapped[str] = mapped_column(String(512), nullable=False)
    #: ``review`` (needs a decision) | ``informational`` (context only —
    #: hardware follows the employee through a move, per #623).
    disposition: Mapped[str] = mapped_column(String(16), nullable=False)
    detail: Mapped[dict | None] = mapped_column(JSON)

    decision: Mapped[str | None] = mapped_column(String(16))
    decided_by_person_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid, ForeignKey("identity_persons.id"),
    )
    decided_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    decision_note: Mapped[str | None] = mapped_column(String(500))

    #: What the delta engine concluded about this holding — see
    #: ``app.hr_lifecycle.mover_delta`` for the vocabulary. Two of the
    #: values count toward the packet's ``delta_items``; the rest exist
    #: so the page can distinguish "checked, and it is fine" from "no
    #: signal could speak about this", which are very different things
    #: to show somebody.
    delta_kind: Mapped[str | None] = mapped_column(String(24))
    #: Why that verdict — the matched policy, the department the holding
    #: was granted under, the share of new peers who hold it. Rendered
    #: as the reason line under each row; an unexplained verdict on
    #: somebody's access is not worth showing.
    delta_evidence: Mapped[dict | None] = mapped_column(JSON)

    #: The model's suggestion and its stated reason, stored ALONGSIDE the
    #: human decision rather than in it. Nothing downstream reads these
    #: — they exist so a reviewer can see what was suggested and so a
    #: later audit can ask whether the suggestions were any good.
    ai_recommendation: Mapped[str | None] = mapped_column(String(16))
    ai_reason: Mapped[str | None] = mapped_column(String(500))

    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(),
    )

    review: Mapped["HrRoleChangeReview"] = relationship(
        "HrRoleChangeReview", back_populates="items",
    )