-- ============================================================
-- AGG-UAE ERP — Migration 59 — Phase M7-p4 (nightly auto-recompute)
--   SCHEDULER_CONFIG key/value store
-- ============================================================
--
-- Closes the last open M7-p4 item: the nightly 02:00 actuals-
-- reconciliation cron (CLAUDE.md §3 invariant 62 — "nightly 02:00
-- cron + manual trigger"). The manual half (the Recompute button +
-- silent-recompute hooks) shipped in M7-p2/p3/p6a; this migration
-- lands the persistence the nightly cron + its in-app toggle need.
--
-- Design — OPT-IN, never auto-runs until the user starts it:
--   * The cron script agg-api/bin/nightly-recompute.php is registered
--     in cPanel Cron Jobs to fire at 02:00, but it reads the flag
--     `nightly_auto_recompute_enabled` FIRST and exits immediately
--     (logging "disabled — skipping") while the flag is '0'. So a
--     registered cron has ZERO effect until the supervisor flips the
--     toggle on the mobile Auto Schedule screen.
--   * The toggle (GET/POST /v1/schedule/auto-recompute) writes this
--     row. Default '0' = OFF; the stranded-warning manual flow is
--     unchanged until the user opts in.
--
-- The same table holds the cron's audit breadcrumbs so the mobile
-- toggle can show "last auto-run <time> (<status>)":
--   last_nightly_run_at      — ISO datetime of the last cron pass
--   last_nightly_run_status  — 'ok' | 'warn' | 'error' | 'skipped'
--   last_nightly_run_message — short human summary
-- These are written by the cron script via upsert; not seeded here.
--
-- Generic key/value shape so future scheduler-wide toggles (e.g. a
-- DB-editable mirror of the .env headcount-scaling flag) can land
-- without another migration.
--
-- Idempotent — CREATE TABLE IF NOT EXISTS + INSERT … ON DUPLICATE
-- KEY UPDATE (no-op when the seed row already exists). Slim table,
-- no innodb_strict_mode dance.
--
-- Depends on: nothing beyond a working DB. The cron script + the
-- auto_recompute.php handler read/write this table; scheduler.php
-- itself is untouched.

CREATE TABLE IF NOT EXISTS SCHEDULER_CONFIG (
    config_key   VARCHAR(64)  NOT NULL PRIMARY KEY,
    config_value VARCHAR(255) NULL,
    updated_at   DATETIME     NULL DEFAULT NULL,
    updated_by   VARCHAR(100) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed the opt-in flag OFF. Re-runs leave any existing value intact.
INSERT INTO SCHEDULER_CONFIG (config_key, config_value, updated_at, updated_by)
VALUES ('nightly_auto_recompute_enabled', '0', NOW(), 'migration-59')
ON DUPLICATE KEY UPDATE config_key = config_key;

-- ─────────────────────────────────────────────────────────────
-- Verification
-- ─────────────────────────────────────────────────────────────

SELECT config_key, config_value, updated_at, updated_by
  FROM SCHEDULER_CONFIG
 ORDER BY config_key;