-- Slice 8Z-v · Cron sweeps — auto-confirm receipts, retention timer, retirement overdue nudges
--
-- Adds tracking columns so each sweep can stay idempotent (never double-notify, never double-confirm),
-- plus a "retirement_overdue_reminder_days" setting so we can throttle re-nudges (default 7 days).
--
-- The sweep logic lives in app/models/CronSweeps.php; the scheduler in scripts/cron_supervisor.php.

-- ── 1. Tracking columns ───────────────────────────────────────────────
SET @sch := DATABASE();

-- lpo_payment_terms.retention_notified_at — set when we tell Procurement retention is claimable.
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='lpo_payment_terms' AND column_name='retention_notified_at');
SET @sql := IF(@c=0,
    "ALTER TABLE lpo_payment_terms ADD COLUMN retention_notified_at DATETIME NULL",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- lpos.retirement_overdue_notified_at — set on first overdue ping.
-- lpos.retirement_overdue_last_notified_at — bumped on every re-nudge for throttle math.
SET @c := (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=@sch AND table_name='lpos' AND column_name='retirement_overdue_notified_at');
SET @sql := IF(@c=0,
    "ALTER TABLE lpos
        ADD COLUMN retirement_overdue_notified_at      DATETIME NULL,
        ADD COLUMN retirement_overdue_last_notified_at DATETIME NULL",
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ── 2. Finance setting: re-nudge throttle ──────────────────────────────
INSERT INTO finance_settings (setting_key, setting_value, description) VALUES
    ('retirement_overdue_reminder_days', '7', 'Days between re-nudges once a retirement is overdue. First nudge is immediate on due-date pass.')
ON DUPLICATE KEY UPDATE description = VALUES(description);
