"""Scheduled jobs — DB models.

``scheduled_jobs`` holds one row per recurring task (HRIS incremental
sync, leaver overdue check, domain expiry alert, ...). ``scheduled_job_runs``
captures the per-execution log that the admin UI shows as a history
table. Keeping runs in their own table (rather than an unbounded
JSON blob on the job row) lets us query "last 20 runs of this job"
cheaply and purge old history when needed.
"""
from __future__ import annotations

import uuid
from datetime import datetime
from typing import Any

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

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

#: Terminal + transient values of ``ScheduledJobRun.status``.
#:
#: ``interrupted`` (2026-09-05) is the honest answer to "the process went
#: away mid-run". Before it existed, a worker killed by a deploy left its
#: run row at ``running`` — the drain deliberately wrote nothing — and six
#: hours later the stale-row reaper stamped it ``failed`` with "worker
#: likely died mid-run". Measured on production: **386 of the 452 scheduler
#: failures in 30 days were that**, against 15 deploys a day and an
#: eight-second drain window, and every one of them had already re-run
#: successfully within fifteen minutes of the next boot. Those rows made the
#: red queue useless and the failure rate a fiction.
#:
#: An interrupted run is deliberately **not** counted in ``run_count`` or
#: ``failure_count``. Those two columns feed the Prometheus identity
#: ``helix_scheduled_job_runs_total{status='ok'} = run_count -
#: failure_count``; a third terminal class that bumped only one of them
#: would silently move the ``ok`` counter. They describe *completed*
#: attempts, and an interrupted run completed nothing.
RUN_STATUS_OK = "ok"
RUN_STATUS_FAILED = "failed"
RUN_STATUS_RUNNING = "running"
RUN_STATUS_SKIPPED = "skipped"
RUN_STATUS_INTERRUPTED = "interrupted"

#: Statuses that describe a finished attempt whose outcome is evidence
#: about the handler. Success-rate denominators and SLO evaluation use
#: exactly this set — ``skipped`` never attempted anything, ``running``
#: has not finished, and ``interrupted`` was cut short by a process
#: lifecycle event rather than by anything the handler did.
TERMINAL_RUN_STATUSES = (RUN_STATUS_OK, RUN_STATUS_FAILED)

#: Values of ``ScheduledJob.trigger_type``.
#:
#: ``chained`` (2026-09-06) means "run me when my parent finishes"
#: instead of "run me at this time". It carries no cron and no interval,
#: and the worker attaches no APScheduler trigger for it — the parent's
#: completion is the trigger, with a reconciler as the safety net.
#:
#: Why it exists: at least eight ordering dependencies in this codebase
#: were expressed as a gap between two cron minute fields, with the
#: intent written only in a docstring ("排在 X 之后", "runs after the
#: rollup"). That is a promise the platform cannot keep — the moment the
#: predecessor overruns its gap, the successor reads stale data and
#: nothing anywhere says so. See ``scheduled_jobs/chain.py``.
TRIGGER_INTERVAL = "interval"
TRIGGER_CRON = "cron"
TRIGGER_CHAINED = "chained"


class ScheduledJob(Base):
    """A recurring job owned by the platform scheduler."""

    __tablename__ = "scheduled_jobs"
    __table_args__ = (
        UniqueConstraint("name", name="uq_scheduled_jobs_name"),
        Index("ix_scheduled_jobs_enabled", "enabled"),
        # The chain reconciler's hot query is "who is chained behind this
        # job", run once per parent per tick.
        Index("ix_scheduled_jobs_chain_after", "chain_after_job_id"),
    )

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

    # Stable handle used in logs + admin UI. Snake-case, namespaced
    # ("hris.incremental_sync"). Changing this breaks the link to
    # historical runs, so mint a new row instead of renaming.
    name: Mapped[str] = mapped_column(String(128), nullable=False)
    display_name: Mapped[str] = mapped_column(String(128), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)

    # Registry lookup key — points at a function registered via
    # @register_scheduled_job. Usually matches ``name`` but kept
    # separate so we can change the display name without re-pointing
    # at the handler.
    handler_key: Mapped[str] = mapped_column(String(128), nullable=False)

    # ``interval`` = run every N seconds; ``cron`` = cron expression.
    trigger_type: Mapped[str] = mapped_column(String(16), nullable=False)
    interval_seconds: Mapped[int | None] = mapped_column(Integer)
    cron_expr: Mapped[str | None] = mapped_column(String(128))
    timezone: Mapped[str] = mapped_column(String(32), nullable=False, server_default="UTC")

    # ``chained`` rows run when this parent finishes rather than on a
    # clock. Self-referential and nullable; ``ON DELETE SET NULL`` so
    # deleting a parent orphans the child (it stops running and shows up
    # in the hygiene census) rather than cascading the delete — losing a
    # job row is never the right answer to losing its predecessor.
    chain_after_job_id: Mapped[uuid.UUID | None] = mapped_column(
        Uuid,
        ForeignKey("scheduled_jobs.id", ondelete="SET NULL"),
        nullable=True,
    )

    enabled: Mapped[bool] = mapped_column(Boolean, nullable=False, server_default="1")

    # Per-job knobs the handler reads (e.g. {"lookback_hours": 25}).
    # Keep the surface area small — complex config belongs in
    # platform_settings or an integration row, not here.
    config: Mapped[dict[str, Any] | None] = mapped_column(JSON)

    # Last-run summary — written on completion. ``last_status`` is
    # "ok" | "failed" | "running" | "skipped". ``last_result_summary``
    # is the handler's returned dict serialized; ``last_error`` holds
    # the exception message for "failed".
    last_run_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    last_status: Mapped[str | None] = mapped_column(String(16))
    last_duration_ms: Mapped[int | None] = mapped_column(Integer)
    last_error: Mapped[str | None] = mapped_column(Text)
    last_result_summary: Mapped[dict[str, Any] | None] = mapped_column(JSON)
    # NOTE: there is deliberately no ``next_run_at`` column (dropped in
    # migration 620). Nothing ever wrote it — the scheduler holds its
    # next-fire state in APScheduler, not in this row — so it published a
    # permanently-empty field that read like data. The single row that
    # ever had a value (``hr.recover_orphaned_left``, seeded
    # ``next_run_at = NOW()`` by migration 245) rendered a timestamp
    # frozen at deploy time, which is worse than blank. The authoritative
    # answer to "when does this run next" is ``service.schedule_forecast``,
    # which computes fire times from the live trigger (cron / interval /
    # chained) using the same maths as the worker.
    run_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")
    failure_count: Mapped[int] = mapped_column(Integer, nullable=False, server_default="0")

    # Notification dedup. ``last_failure_signature`` is a stable hash of
    # the *kind* of failure (e.g. provider class + first line of the
    # error, with UUIDs/timestamps stripped) so the dispatcher can tell
    # "this is the same problem repeating" from "new problem". On a
    # repeat, the dispatcher skips the notification — we never want a
    # cron that fails every 5 minutes to spam the IT admin's bell 288
    # times a day. ``last_failure_notified_at`` records when the most
    # recent notification went out so dashboards can render "last
    # alerted at …".
    last_failure_signature: Mapped[str | None] = mapped_column(String(64))
    last_failure_notified_at: Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True),
    )

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

    runs: Mapped[list[ScheduledJobRun]] = relationship(
        "ScheduledJobRun",
        back_populates="job",
        cascade="all, delete-orphan",
        order_by="ScheduledJobRun.started_at.desc()",
    )


class ScheduledJobRun(Base):
    """Per-execution log entry.

    Written at the start of every run (with ``status='running'``) and
    updated on completion. Keeps a bounded history so the admin can
    see the last 20 or 50 runs of each job without parsing stdout logs.
    """

    __tablename__ = "scheduled_job_runs"
    __table_args__ = (
        Index(
            "ix_scheduled_job_runs_job_started",
            "job_id", "started_at",
        ),
        # Default UI / dedup query is "failed AND acknowledged_at IS
        # NULL" across every job — a cross-job index lets that scan
        # avoid the ``job_started`` per-job index.
        Index(
            "ix_scheduled_job_runs_status_ack",
            "status", "acknowledged_at",
        ),
    )

    id: Mapped[uuid.UUID] = mapped_column(Uuid, primary_key=True, default=uuid.uuid4)
    job_id: Mapped[uuid.UUID] = mapped_column(
        Uuid, ForeignKey("scheduled_jobs.id", ondelete="CASCADE"), nullable=False,
    )
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    duration_ms: Mapped[int | None] = mapped_column(Integer)
    status: Mapped[str] = mapped_column(String(16), nullable=False)
    # ``scheduler`` | ``manual:<user_id>`` | ``startup``
    triggered_by: Mapped[str] = mapped_column(String(64), nullable=False)
    error_message: Mapped[str | None] = mapped_column(Text)
    result_summary: Mapped[dict[str, Any] | None] = mapped_column(JSON)

    # Acknowledgement metadata. We deliberately keep ``status='failed'``
    # on acknowledged rows — rewriting failed → ok would lie to the
    # audit trail and break the failure_count / dashboards. The UI
    # filters acknowledged rows out of the default red queue instead,
    # and the notification dedup logic treats an unacked-prior-failure
    # as the silencing condition (acked → next failure re-notifies).
    acknowledged_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    # ``identity_persons.id`` is ``VARCHAR(36)`` (migration 001), so the
    # FK column matches that convention instead of using the GUID
    # CHAR(36) type — TiDB rejects CHAR↔VARCHAR FK pairs even when the
    # collation matches.
    acknowledged_by_id: Mapped[str | None] = mapped_column(
        String(36),
        ForeignKey("identity_persons.id", ondelete="SET NULL"),
    )
    ack_reason: Mapped[str | None] = mapped_column(Text)

    job: Mapped[ScheduledJob] = relationship("ScheduledJob", back_populates="runs")