-- Pacific Logistics · slice 16 · Alerts engine + notification outbox
--
-- Turn 2 of Iter 3 adds:
--   alert_rules      — configurable thresholds (over_speed, geofence_exit,
--                      fuel_drop, ignition_off_geofence, device_offline, …)
--   alerts           — one row per triggered rule (dedup'd within a cooldown window)
--   sms_outbox       — SMS to be dispatched by cron/tick.php (Beem Africa API)
--   email_outbox     — emails to be dispatched by cron/tick.php (SMTP)
--   cron_runs        — audit trail of every cron tick (watermark, counts, error)
--
-- Design notes for shared hosting:
--   * cron runs every 5 min (Namecheap minimum). Watermark on cron_runs.watermark
--     lets each tick pick up exactly where the previous one left off — no gaps,
--     no double-emission.
--   * Outbox rows are queued synchronously (fast INSERT), sent asynchronously by
--     cron. A page render never blocks on Beem or SMTP.
--   * All rule config lives in DB (threshold_json), not PHP, so Ops can tune
--     without a deploy.


-- ═══════════════════════════════════════════════════════════════════════
-- alert_rules — configurable thresholds
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS alert_rules (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    name             VARCHAR(160) NOT NULL,
    kind             VARCHAR(40)  NOT NULL,
                                                    -- over_speed | fuel_drop | ignition_after_hours |
                                                    -- geofence_enter | geofence_exit |
                                                    -- device_offline | harsh_brake | idle_too_long |
                                                    -- unauthorised_movement | fuel_theft
    severity         VARCHAR(20)  NOT NULL DEFAULT 'medium',
                                                    -- info | low | medium | high | critical
    threshold_json   JSON         NULL,
                                                    -- kind-specific: {speed_kph:80}, {drop_l:20,within_min:10},
                                                    -- {geofence_id:5}, {offline_minutes:30}, {hour_from:20,hour_to:5}
    scope_truck_ids  JSON         NULL,             -- NULL = all trucks; [12,34,56] = restricted
    scope_geofence_ids JSON       NULL,             -- for geofence rules — which fences to watch
    cooldown_minutes INT UNSIGNED NOT NULL DEFAULT 15,
                                                    -- suppress dedup'd alert emissions per (rule, device)
    notify_sms_to    VARCHAR(255) NULL,             -- CSV of MSISDNs
    notify_email_to  VARCHAR(500) NULL,             -- CSV of email addresses
    notify_inapp     TINYINT(1)   NOT NULL DEFAULT 1,
                                                    -- always create dashboard alert row
    active           TINYINT(1)   NOT NULL DEFAULT 1,
    notes            TEXT         NULL,
    created_by       INT UNSIGNED NULL,
    created_on       DATETIME     NULL,
    edited_by        INT UNSIGNED NULL,
    edited_on        DATETIME     NULL,
    deleted          TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (c0d),
    KEY idx_ar_kind_active (kind, active, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- alerts — one row per triggered rule
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS alerts (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    rule_id          INT UNSIGNED NULL,             -- NULL = ad-hoc / manual
    kind             VARCHAR(40)  NOT NULL,
    severity         VARCHAR(20)  NOT NULL DEFAULT 'medium',
    -- Source of truth (what fired this)
    device_id        INT UNSIGNED NULL,
    truck_id         INT UNSIGNED NULL,
    driver_id        INT UNSIGNED NULL,
    geofence_id      INT UNSIGNED NULL,
    trip_id          INT UNSIGNED NULL,
    position_id      INT UNSIGNED NULL,
    event_id         INT UNSIGNED NULL,             -- link to gps_events row if triggered by one
    ts               DATETIME     NOT NULL,         -- when the underlying event happened
    lat              DECIMAL(10,6) NULL,
    lng              DECIMAL(10,6) NULL,
    title            VARCHAR(200) NOT NULL,
    message          TEXT         NOT NULL,
    payload_json     JSON         NULL,
    -- Notification tracking
    sms_sent         TINYINT(1)   NOT NULL DEFAULT 0,
    email_sent       TINYINT(1)   NOT NULL DEFAULT 0,
    -- Ops workflow
    status           VARCHAR(20)  NOT NULL DEFAULT 'open',
                                                    -- open | acknowledged | resolved | dismissed
    acknowledged_by  INT UNSIGNED NULL,
    acknowledged_at  DATETIME     NULL,
    resolution_note  TEXT         NULL,
    created_on       DATETIME     NOT NULL,
    PRIMARY KEY (c0d),
    KEY idx_al_status_ts (status, ts),
    KEY idx_al_truck_ts  (truck_id, ts),
    KEY idx_al_kind_ts   (kind, ts),
    KEY idx_al_rule      (rule_id, ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- sms_outbox — Beem Africa SMS queue
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS sms_outbox (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    msisdn           VARCHAR(20)  NOT NULL,         -- E.164 with + prefix
    message          TEXT         NOT NULL,         -- keep < 160 chars for single-segment
    -- Origin
    alert_id         INT UNSIGNED NULL,
    source_type      VARCHAR(40)  NULL,             -- alert | trip_dispatch | expense_approval | manual
    source_id        INT UNSIGNED NULL,
    -- Dispatch state
    status           VARCHAR(20)  NOT NULL DEFAULT 'queued',
                                                    -- queued | sending | sent | failed | cancelled
    attempts         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts     TINYINT UNSIGNED NOT NULL DEFAULT 3,
    last_error       VARCHAR(500) NULL,
    provider         VARCHAR(40)  NOT NULL DEFAULT 'beem',
    provider_msg_id  VARCHAR(80)  NULL,             -- Beem message-id for reconciliation
    scheduled_for    DATETIME     NULL,             -- NULL = send ASAP
    sent_at          DATETIME     NULL,
    created_by       INT UNSIGNED NULL,
    created_on       DATETIME     NOT NULL,
    PRIMARY KEY (c0d),
    KEY idx_so_status (status, created_on),
    KEY idx_so_alert  (alert_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- email_outbox — SMTP queue
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS email_outbox (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    to_email         VARCHAR(180) NOT NULL,
    to_name          VARCHAR(160) NULL,
    cc_emails        VARCHAR(500) NULL,             -- CSV
    subject          VARCHAR(255) NOT NULL,
    body_html        MEDIUMTEXT   NOT NULL,
    body_text        MEDIUMTEXT   NULL,
    attachments_json JSON         NULL,             -- [{name, path}] — server-side paths
    alert_id         INT UNSIGNED NULL,
    source_type      VARCHAR(40)  NULL,
    source_id        INT UNSIGNED NULL,
    status           VARCHAR(20)  NOT NULL DEFAULT 'queued',
    attempts         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    max_attempts     TINYINT UNSIGNED NOT NULL DEFAULT 3,
    last_error       VARCHAR(500) NULL,
    scheduled_for    DATETIME     NULL,
    sent_at          DATETIME     NULL,
    created_by       INT UNSIGNED NULL,
    created_on       DATETIME     NOT NULL,
    PRIMARY KEY (c0d),
    KEY idx_eo_status (status, created_on),
    KEY idx_eo_alert  (alert_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- cron_runs — audit of every cron tick
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS cron_runs (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    worker           VARCHAR(40)  NOT NULL,         -- tick | nightly
    started_at       DATETIME     NOT NULL,
    finished_at      DATETIME     NULL,
    watermark_before DATETIME     NULL,             -- max ts scanned last time
    watermark_after  DATETIME     NULL,             -- max ts scanned this time
    positions_scanned INT UNSIGNED NOT NULL DEFAULT 0,
    events_scanned   INT UNSIGNED NOT NULL DEFAULT 0,
    alerts_created   INT UNSIGNED NOT NULL DEFAULT 0,
    sms_sent         INT UNSIGNED NOT NULL DEFAULT 0,
    sms_failed       INT UNSIGNED NOT NULL DEFAULT 0,
    emails_sent      INT UNSIGNED NOT NULL DEFAULT 0,
    emails_failed    INT UNSIGNED NOT NULL DEFAULT 0,
    ok               TINYINT(1)   NOT NULL DEFAULT 1,
    error            VARCHAR(500) NULL,
    PRIMARY KEY (c0d),
    KEY idx_cr_worker_started (worker, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- Seed default alert rules — Ops can tune later
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO alert_rules (c0d, tenant_id, name, kind, severity, threshold_json, cooldown_minutes, notify_inapp, active, notes, created_by, created_on) VALUES
(1, 1, 'Over-speed > 80 kph',           'over_speed',        'medium',   JSON_OBJECT('speed_kph', 80),                     15, 1, 1, 'Fires when a position reports speed above 80 kph. Adjust for highway vs urban.', 1, NOW()),
(2, 1, 'Reckless speeding > 100 kph',   'over_speed',        'high',     JSON_OBJECT('speed_kph', 100),                     5, 1, 1, 'Higher-severity alert for anything above 100 kph.',                              1, NOW()),
(3, 1, 'Sudden fuel drop > 20L',        'fuel_drop',         'high',     JSON_OBJECT('drop_l', 20, 'within_min', 10),      30, 1, 1, 'Fuel loss > 20L within 10 minutes — probable siphon.',                          1, NOW()),
(4, 1, 'Ignition on after hours',       'ignition_after_hours','medium', JSON_OBJECT('hour_from', 20, 'hour_to', 5),        60, 1, 1, 'Truck engine start between 20:00 and 05:00 local time.',                        1, NOW()),
(5, 1, 'Device offline > 30 min',       'device_offline',    'low',      JSON_OBJECT('offline_minutes', 30),                60, 1, 1, 'Tracker has not reported for 30+ minutes during operating hours.',              1, NOW()),
(6, 1, 'Geofence exit',                 'geofence_exit',     'medium',   NULL,                                              15, 1, 1, 'Fires whenever a truck exits a geofence with trigger_alert=1.',                 1, NOW());


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — Alerts is Fleet/Ops/Admin scope. Geofences already granted in slice 15.
-- Cron controller is public (token-authed, not perm-gated).
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- AlertRules (Admin, Fleet, Ops)
(10, 'AlertRules', 'index',  1, NOW()), (10, 'AlertRules', 'add',    1, NOW()), (10, 'AlertRules', 'save',    1, NOW()),
(10, 'AlertRules', 'edit',   1, NOW()), (10, 'AlertRules', 'update', 1, NOW()), (10, 'AlertRules', 'delete',  1, NOW()),
(10, 'AlertRules', 'toggle', 1, NOW()),
(20, 'AlertRules', 'index',  1, NOW()), (20, 'AlertRules', 'edit',   1, NOW()), (20, 'AlertRules', 'update',  1, NOW()),
(20, 'AlertRules', 'toggle', 1, NOW()),
(22, 'AlertRules', 'index',  1, NOW()), (22, 'AlertRules', 'edit',   1, NOW()), (22, 'AlertRules', 'update',  1, NOW()),
(22, 'AlertRules', 'toggle', 1, NOW()),
(1,  'AlertRules', 'index',  1, NOW()),
-- Alerts (Admin, Fleet, Ops, Dispatcher, Exec)
(10, 'Alerts', 'index',        1, NOW()), (10, 'Alerts', 'view',      1, NOW()),
(10, 'Alerts', 'acknowledge',  1, NOW()), (10, 'Alerts', 'resolve',   1, NOW()), (10, 'Alerts', 'dismiss', 1, NOW()),
(20, 'Alerts', 'index',        1, NOW()), (20, 'Alerts', 'view',      1, NOW()),
(20, 'Alerts', 'acknowledge',  1, NOW()), (20, 'Alerts', 'resolve',   1, NOW()),
(22, 'Alerts', 'index',        1, NOW()), (22, 'Alerts', 'view',      1, NOW()),
(22, 'Alerts', 'acknowledge',  1, NOW()), (22, 'Alerts', 'resolve',   1, NOW()),
(21, 'Alerts', 'index',        1, NOW()), (21, 'Alerts', 'view',      1, NOW()), (21, 'Alerts', 'acknowledge', 1, NOW()),
(1,  'Alerts', 'index',        1, NOW()), (1,  'Alerts', 'view',      1, NOW());
