-- =====================================================================
--  RETAIL PHARMACY PLATFORM
--  Schema Pack N — Scheduler v1.0
--
--  Apply AFTER packs K, L and M.
--  Target: MySQL 8.0+ / InnoDB / utf8mb4
--
--  =================================================================
--  THE GAP THIS CLOSES
--  =================================================================
--
--  Backup, invariant checking, the message outbox, refill reminders,
--  reorder suggestions and self-update are all built and all tested.
--  Nothing runs any of them. On a shop PC there is no cron, no
--  systemd timer, and nobody who will remember.
--
--  =================================================================
--  THREE THINGS A NAIVE SCHEDULER GETS WRONG
--  =================================================================
--
--   1. OVERLAP. Windows Task Scheduler will happily start a second copy
--      while the first is still running. Two backups at once on a shop
--      PC is a locked table and a frightened chemist. Hence the lock,
--      held in the database rather than in a file, because the file
--      outlives the crash that orphaned it.
--
--   2. THE THUNDERING HERD. Two thousand stores with "backup at 2am"
--      is two thousand uploads at 2:00:00. Each store gets a stable
--      offset derived from its own id, so the load spreads without any
--      central coordination.
--
--   3. ONE JOB TAKING THE REST DOWN. A failing outbox must not stop the
--      backup. Each job is isolated, and its failures are counted so a
--      job that fails forever can be backed off rather than retried
--      every minute for a year.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE scheduled_job (
  id              CHAR(26)      NOT NULL,
  store_id        CHAR(26)      NOT NULL,
  job_name        VARCHAR(40)   NOT NULL COMMENT 'outbox.drain | backup.run | ...',
  interval_secs   INT UNSIGNED  NOT NULL,
  -- Optional window, local hours. Used by jobs that must not run while
  -- the counter is busy, or that should run when someone is awake.
  window_from_hour TINYINT          NULL,
  window_to_hour   TINYINT          NULL,
  -- Stable per store, so the herd spreads without central coordination.
  jitter_secs     INT UNSIGNED  NOT NULL DEFAULT 0,
  is_enabled      TINYINT(1)    NOT NULL DEFAULT 1,

  -- Lock. Held in the database, not a file: a file lock outlives the
  -- crash that orphaned it and then blocks the job forever.
  locked_by       VARCHAR(80)       NULL COMMENT 'host:pid',
  locked_at       DATETIME(3)       NULL,
  lock_expires_at DATETIME(3)       NULL,

  last_run_at     DATETIME(3)       NULL,
  last_ok_at      DATETIME(3)       NULL,
  last_duration_ms INT UNSIGNED NOT NULL DEFAULT 0,
  next_due_at     DATETIME(3)       NULL,
  consecutive_failures SMALLINT NOT NULL DEFAULT 0,
  last_error      VARCHAR(500)      NULL,
  runs_total      BIGINT UNSIGNED NOT NULL DEFAULT 0,
  created_at      DATETIME(3)   NOT NULL,
  updated_at      DATETIME(3)   NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_sj (store_id, job_name),
  KEY ix_sj_due (store_id, is_enabled, next_due_at),
  KEY ix_sj_lock (lock_expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ---------------------------------------------------------------------
--  Run history, kept short. Enough to answer "did the backup run last
--  night and how long did it take", not enough to become a table
--  nobody prunes.
-- ---------------------------------------------------------------------
CREATE TABLE job_run (
  id            CHAR(26)      NOT NULL,
  store_id      CHAR(26)      NOT NULL,
  job_name      VARCHAR(40)   NOT NULL,
  started_at    DATETIME(3)   NOT NULL,
  finished_at   DATETIME(3)       NULL,
  duration_ms   INT UNSIGNED  NOT NULL DEFAULT 0,
  outcome       ENUM('OK','FAILED','SKIPPED','TIMEOUT') NOT NULL DEFAULT 'OK',
  detail        VARCHAR(500)      NULL,
  PRIMARY KEY (id),
  KEY ix_jr_job (store_id, job_name, started_at),
  KEY ix_jr_recent (store_id, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- =====================================================================
--  INVARIANTS asserted in tests:
--
--   1. Two workers cannot run the same job at once.
--   2. A crashed worker's lock expires and the job recovers by itself.
--   3. One failing job never stops the others.
--   4. Repeated failures back off instead of hammering.
--   5. A windowed job does not run outside its window.
--   6. Jitter is stable per store and spreads the herd.
-- =====================================================================