-- Pacific Logistics · slice 20 · Preventive Maintenance schedules
--
-- Iter 4 Turn 2 adds the PM engine — planned maintenance triggered by
-- odometer OR calendar OR whichever-comes-first, with escalation from
-- "upcoming" → "due" → "overdue" → alerts.
--
-- Three tables:
--   pm_templates          — reusable PM definitions (10k km oil, 90d insurance…)
--   truck_pm_schedules    — one per (truck × template), holds baseline + next-due
--   pm_events             — one row per triggered cycle (state machine)
--
-- Cron sweep (piggybacks on the existing 5-min tick from Iter 3.2) reads
-- trucks.current_odometer + today's date, updates event state, and emits
-- alerts via the shared Alerts::queueNotifications() path — so SMS/email
-- go out the same outbox we already built.
--
-- Numbering assumes doc-expiry (insurance / road licence / PSV) is
-- Turn 3's job and lives in its own doc-tracker table. Turn 2 focuses
-- purely on mechanical PM.


-- ═══════════════════════════════════════════════════════════════════════
-- pm_templates — the "engine oil every 10k km / 90 days" library
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS pm_templates (
    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,
                                                    -- oil_change | oil_filter | fuel_filter | air_filter |
                                                    -- transmission_oil | diff_oil | brake_inspection |
                                                    -- brake_fluid | tyre_rotation | tyre_inspection |
                                                    -- coolant | battery | alignment | general_service |
                                                    -- greasing | other
    interval_km      INT UNSIGNED NULL,             -- fires every N km, NULL = calendar-only
    interval_days    INT UNSIGNED NULL,             -- fires every N days,  NULL = km-only
    threshold_km     INT UNSIGNED NOT NULL DEFAULT 500,   -- warn this far before due
    threshold_days   INT UNSIGNED NOT NULL DEFAULT 7,    -- warn this far before due
    priority         VARCHAR(20)  NOT NULL DEFAULT 'normal',   -- low | normal | high | critical
    default_labour_hours DECIMAL(5,2) NULL,
    default_parts_cost   DECIMAL(12,2) NULL,
    applicable_types_json JSON NULL,                -- ["truck","trailer",...] NULL = all
    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_pt_kind_active (kind, active, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- truck_pm_schedules — assign a template to a truck with baseline
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS truck_pm_schedules (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    truck_id         INT UNSIGNED NOT NULL,
    template_id      INT UNSIGNED NOT NULL,
    -- Baseline anchors the next-due calculation
    baseline_km      INT UNSIGNED NULL,             -- last service odo (NULL = use current odo on save)
    baseline_date    DATE         NULL,             -- last service date  (NULL = today on save)
    -- Computed next-due; kept denormalised for fast dashboard queries
    next_due_km      INT UNSIGNED NULL,
    next_due_date    DATE         NULL,
    active           TINYINT(1)   NOT NULL DEFAULT 1,
    notes            VARCHAR(500) 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),
    UNIQUE KEY uq_tps (truck_id, template_id, deleted),
    KEY idx_tps_truck (truck_id, active, deleted),
    KEY idx_tps_next_km  (next_due_km),
    KEY idx_tps_next_date (next_due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- pm_events — one row per triggered PM cycle
-- ═══════════════════════════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS pm_events (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    event_no         VARCHAR(20)  NOT NULL,          -- PM-YYYY-NNNN
    truck_id         INT UNSIGNED NOT NULL,
    template_id      INT UNSIGNED NOT NULL,
    schedule_id      INT UNSIGNED NOT NULL,
    triggered_at     DATETIME     NOT NULL,
    triggered_by     VARCHAR(20)  NOT NULL DEFAULT 'cron',      -- cron | manual
    -- Snapshot of what was planned
    planned_km       INT UNSIGNED NULL,
    planned_date     DATE         NULL,
    -- State machine
    status           VARCHAR(20)  NOT NULL DEFAULT 'upcoming',
                                                    -- upcoming | due | overdue | completed | skipped
    due_since        DATETIME     NULL,             -- when state first flipped to due
    overdue_since    DATETIME     NULL,             -- when state first flipped to overdue
    severity         VARCHAR(20)  NOT NULL DEFAULT 'normal',
    -- Completion
    completed_at     DATETIME     NULL,
    completed_km     INT UNSIGNED NULL,
    completed_by     INT UNSIGNED NULL,
    work_order_id    INT UNSIGNED NULL,             -- optional link into the workshop
    -- Skip
    skipped_by       INT UNSIGNED NULL,
    skip_reason      VARCHAR(255) NULL,
    -- Notification tracking
    alert_id         INT UNSIGNED NULL,             -- last alert emitted for this event
    notes            TEXT         NULL,
    created_on       DATETIME     NOT NULL,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_pme_no (event_no),
    KEY idx_pme_status_truck (status, truck_id, planned_date),
    KEY idx_pme_schedule (schedule_id),
    KEY idx_pme_triggered (triggered_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- Seed 10 canonical PM templates for a Tanzania fleet
-- Numbers are typical for Isuzu FVR / Mitsubishi Fuso / Scania P-series
-- diesel trucks running on Tanzania road conditions.
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO pm_templates
    (c0d, tenant_id, name, kind, interval_km, interval_days, threshold_km, threshold_days, priority, default_labour_hours, default_parts_cost, active, notes, created_by, created_on)
VALUES
    (1, 1, 'Engine oil & filter change',   'oil_change',        10000, 90,  500, 7,  'high',     1.0, 120000, 1, 'Standard 10k km / 3 month service. Whichever comes first.',                    1, NOW()),
    (2, 1, 'Fuel filter replacement',      'fuel_filter',       20000, 180, 500, 14, 'high',     0.5, 45000,  1, 'Critical for common-rail diesels running on TZ fuel quality.',                 1, NOW()),
    (3, 1, 'Air filter replacement',       'air_filter',        30000, 365, 500, 30, 'normal',   0.3, 30000,  1, 'Shorter interval in dusty upcountry conditions.',                              1, NOW()),
    (4, 1, 'Transmission oil change',      'transmission_oil',  40000, 730, 1000, 30, 'normal',  2.0, 180000, 1, 'Every 40k km or 2 years.',                                                     1, NOW()),
    (5, 1, 'Differential oil change',      'diff_oil',          40000, 730, 1000, 30, 'normal',  1.5, 120000, 1, 'Both front and rear diffs on 6x4 configurations.',                             1, NOW()),
    (6, 1, 'Brake pads inspection',        'brake_inspection',  20000, 180, 500, 14, 'critical', 0.5, 0,      1, 'Visual inspection; replace pads if under 3mm remaining.',                     1, NOW()),
    (7, 1, 'Brake fluid change',           'brake_fluid',       50000, 730, 1000, 30, 'high',    1.0, 25000,  1, 'Every 50k km or 2 years to protect ABS module.',                              1, NOW()),
    (8, 1, 'Tyre rotation',                'tyre_rotation',     10000, NULL, 500, NULL, 'normal', 1.0, 0,     1, 'Rotate front-to-rear to equalise wear.',                                       1, NOW()),
    (9, 1, 'Coolant / radiator flush',     'coolant',           50000, 730, 1000, 30, 'high',    1.5, 60000,  1, 'Prevents overheating on Chalinze corridor climbs.',                            1, NOW()),
    (10,1, 'Major service (60k km)',       'general_service',   60000, 365, 1000, 30, 'critical',6.0, 500000, 1, 'Full inspection: timing belt, injectors, mounts, greasing, alignment check.',  1, NOW());


-- ═══════════════════════════════════════════════════════════════════════
-- Alert rule seeds so PM notifications flow through the existing outbox
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO alert_rules
    (c0d, tenant_id, name, kind, severity, threshold_json, cooldown_minutes, notify_inapp, active, notes, created_by, created_on)
VALUES
    (7,  1, 'PM due (within threshold)',   'pm_due',      'medium',   NULL, 60,  1, 1, 'Fires when a truck comes within the threshold_km or threshold_days of a scheduled PM.', 1, NOW()),
    (8,  1, 'PM overdue',                  'pm_overdue',  'high',     NULL, 120, 1, 1, 'Fires when the odometer or date has passed the scheduled PM point.',                    1, NOW());


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — PM library is Admin+Fleet+Ops. Dispatcher gets read-only.
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- PmTemplates
(10, 'PmTemplates', 'index',   1, NOW()), (10, 'PmTemplates', 'add',      1, NOW()), (10, 'PmTemplates', 'save',    1, NOW()),
(10, 'PmTemplates', 'edit',    1, NOW()), (10, 'PmTemplates', 'update',   1, NOW()), (10, 'PmTemplates', 'toggle',  1, NOW()),
(10, 'PmTemplates', 'delete',  1, NOW()), (10, 'PmTemplates', 'view',     1, NOW()),
(20, 'PmTemplates', 'index',   1, NOW()), (20, 'PmTemplates', 'add',      1, NOW()), (20, 'PmTemplates', 'save',    1, NOW()),
(20, 'PmTemplates', 'edit',    1, NOW()), (20, 'PmTemplates', 'update',   1, NOW()), (20, 'PmTemplates', 'toggle',  1, NOW()),
(20, 'PmTemplates', 'view',    1, NOW()),
(22, 'PmTemplates', 'index',   1, NOW()), (22, 'PmTemplates', 'view',     1, NOW()),
(1,  'PmTemplates', 'index',   1, NOW()), (1,  'PmTemplates', 'view',     1, NOW()),
-- PmDashboard
(10, 'PmDashboard', 'index',       1, NOW()), (10, 'PmDashboard', 'view',       1, NOW()),
(10, 'PmDashboard', 'complete',    1, NOW()), (10, 'PmDashboard', 'skip',       1, NOW()),
(10, 'PmDashboard', 'triggerNow',  1, NOW()),
(20, 'PmDashboard', 'index',       1, NOW()), (20, 'PmDashboard', 'view',       1, NOW()),
(20, 'PmDashboard', 'complete',    1, NOW()), (20, 'PmDashboard', 'skip',       1, NOW()),
(22, 'PmDashboard', 'index',       1, NOW()), (22, 'PmDashboard', 'view',       1, NOW()),
(21, 'PmDashboard', 'index',       1, NOW()), (21, 'PmDashboard', 'view',       1, NOW()),
(1,  'PmDashboard', 'index',       1, NOW()), (1,  'PmDashboard', 'view',       1, NOW()),
-- TruckPmSchedules (nested under Trucks/view)
(10, 'TruckPmSchedules', 'assign', 1, NOW()), (10, 'TruckPmSchedules', 'save',    1, NOW()),
(10, 'TruckPmSchedules', 'edit',   1, NOW()), (10, 'TruckPmSchedules', 'update',  1, NOW()),
(10, 'TruckPmSchedules', 'delete', 1, NOW()),
(20, 'TruckPmSchedules', 'assign', 1, NOW()), (20, 'TruckPmSchedules', 'save',    1, NOW()),
(20, 'TruckPmSchedules', 'edit',   1, NOW()), (20, 'TruckPmSchedules', 'update',  1, NOW()),
(20, 'TruckPmSchedules', 'delete', 1, NOW());
