-- @database: hxa_bo
--
-- Heartbeat + outcome of every background worker run.
--
-- WHY: the workers are the blind spot of this platform. The media counter flush
-- sat wedged on one poisoned row for days, and the notification flush burned its
-- backlog against an unconfigured OneSignal — both silently, because a cron
-- writes to a log file nobody reads. The dashboard could show every KPI while
-- the pipeline feeding it was dead.
--
-- So the point is NOT to keep pretty logs; it is to answer two questions:
--   1. did this worker run when it was supposed to?   (`started_at` per worker)
--   2. did it do anything, and did it fail?           (`status`, `summary`)
--
-- Remote workers (bin/*_worker.py run from another machine) can only report over
-- HTTP, which is why this is fed by `POST /admin/workers/runs` rather than by a
-- direct write.
--
-- Retention is self-maintaining: each insert trims its OWN worker's tail beyond
-- WORKER_RUN_KEEP_DAYS. A worker ticking every minute would otherwise put half a
-- million rows a year in here, and bounding it per worker means no extra cron to
-- forget about — and no worker can crowd out another's history.

CREATE TABLE IF NOT EXISTS `worker_run` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    -- Logical worker name, e.g. `notifications-flush`, `describe`, `session-stats`.
    -- Free-form on purpose: a worker is declared by reporting, not by a registry
    -- someone has to remember to update.
    `worker`      VARCHAR(64)  NOT NULL,
    -- Which deployment reported it. The describe queue is shared across
    -- environments, so `dev` and `prod` runs must not be read as one series.
    `environment` VARCHAR(32)  NOT NULL DEFAULT '',
    -- Machine that ran it — remote workers do not live on the app server.
    `host`        VARCHAR(64)  NOT NULL DEFAULT '',

    `started_at`  DATETIME     NOT NULL,
    `duration_ms` INT UNSIGNED NOT NULL DEFAULT 0,
    -- ok      : ran and did work
    -- idle    : ran, found nothing to do (still a heartbeat — the tick happened)
    -- failed  : ran and something broke
    -- skipped : did not run (another instance held the lock)
    `status`      ENUM('ok','idle','failed','skipped') NOT NULL,
    -- Counters the worker chose to report (drained, pushed, failed, …). JSON so
    -- each worker keeps its own shape without a schema change per worker.
    `summary`     JSON         NULL,
    -- Human-readable failure reason. Truncated by the writer, never trusted raw.
    `message`     TEXT         NULL,
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    -- The health view: latest run per worker, and the retention trim.
    KEY `idx_worker_started` (`worker`, `started_at`),
    -- "What failed recently, all workers confounded".
    KEY `idx_status_started` (`status`, `started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
