-- Pacific Logistics · slice 22 · Document expiry reminders (Iter 4 Turn 3)
--
-- Rides on top of the tables that already exist: truck_docs, truck_doc_types,
-- driver_docs, driver_doc_types. Every doc row has an expires_on date; every
-- doc_type carries a per-type warn_days that says "start reminding this
-- many days ahead of the expiry".
--
-- The sweep runs on the same 5-min cron tick as GPS + PM. It fires alerts
-- through the shared Alerts + Outbox pipeline (Iter 3.2), so SMS + email
-- go out with no additional plumbing. Cooldown of 24 h prevents daily spam.
--
-- Three severity brackets:
--   doc_expiring_soon    → 30..15 days out  (medium)
--   doc_expiring_urgent  → 14..1  days out  (high)
--   doc_expired          → past due          (critical)


-- ═══════════════════════════════════════════════════════════════════════
-- Alert rule seeds for document reminders
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO alert_rules
    (c0d, tenant_id, name, kind, severity, threshold_json, cooldown_minutes, notify_inapp, active, notes, created_by, created_on)
VALUES
    (9,  1, 'Document expiring soon',   'doc_expiring_soon',   'medium',   JSON_OBJECT('days_from', 15, 'days_to', 30), 1440, 1, 1, 'Fires when a truck or driver document is 15-30 days from expiry.',                        1, NOW()),
    (10, 1, 'Document expiring urgent', 'doc_expiring_urgent', 'high',     JSON_OBJECT('days_from', 1,  'days_to', 14), 1440, 1, 1, 'Fires when a truck or driver document is 1-14 days from expiry — needs immediate action.', 1, NOW()),
    (11, 1, 'Document expired',         'doc_expired',         'critical', JSON_OBJECT('days_over', 0),                 1440, 1, 1, 'Fires the day a document passes its expiry and every day after.',                          1, NOW());


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — Admin, Fleet, Ops, Dispatcher (read-only for Dispatcher)
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(10, 'DocExpiries', 'index',      1, NOW()),
(10, 'DocExpiries', 'sweepNow',   1, NOW()),
(20, 'DocExpiries', 'index',      1, NOW()),
(20, 'DocExpiries', 'sweepNow',   1, NOW()),
(22, 'DocExpiries', 'index',      1, NOW()),
(22, 'DocExpiries', 'sweepNow',   1, NOW()),
(21, 'DocExpiries', 'index',      1, NOW()),
(1,  'DocExpiries', 'index',      1, NOW());
