-- ===========================================================================
-- Migration: scheduled-job run log (for the admin System health panel)
-- Date: 2026-07-28
--
-- Each scheduled job (payments-reconcile, billing-sweep, daily-snapshot,
-- vaccination-reminders) records a row when it runs — CLI cron OR the HTTP
-- /cron/* endpoints. The admin System page reads the latest row per job to show
-- a green / stale / failed indicator, so a silently-dead cron (e.g. payments
-- stuck as pending) is caught before it costs money. Idempotent.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS cron_runs (
  id          INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
  job         VARCHAR(50)   NOT NULL,           -- e.g. 'payments-reconcile'
  status      ENUM('ok','error') NOT NULL DEFAULT 'ok',
  detail      VARCHAR(255)  NULL,               -- short summary or error message
  duration_ms INT UNSIGNED  NULL,
  ran_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_cron_job (job, id)
) ENGINE=InnoDB;