-- Pacific Logistics · slice 31 · GPS Dashboard (Iter 5 Turn 5)
--
-- Consumer for the existing GpsIngest endpoint.
--
-- Adds:
--   * Indexes tuned for the dashboard's hot paths
--   * Grants for Executive, Ops Manager, Dispatcher, Fleet Manager,
--     Administrator, and Field Supervisor
--
-- No new tables — the dashboard reads from gps_devices, gps_positions,
-- gps_events, geofences, trucks, drivers, trips.

DELIMITER $$
DROP PROCEDURE IF EXISTS pl_add_index_if_missing_31 $$
CREATE PROCEDURE pl_add_index_if_missing_31(IN tbl VARCHAR(64), IN idx VARCHAR(64), IN spec VARCHAR(500))
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.STATISTICS
         WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl AND INDEX_NAME = idx
    ) THEN
        SET @s = CONCAT('CREATE INDEX ', idx, ' ON ', tbl, ' ', spec);
        PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    END IF;
END $$
DELIMITER ;

-- Positions get queried by (device_id, ts DESC) for tracks and (truck_id, ts DESC)
-- for the live map. Composite covering indexes speed both dramatically.
CALL pl_add_index_if_missing_31('gps_positions', 'idx_gps_pos_device_ts', '(device_id, ts DESC)');
CALL pl_add_index_if_missing_31('gps_positions', 'idx_gps_pos_truck_ts',  '(truck_id, ts DESC)');

-- Events by ts for the alerts feed
CALL pl_add_index_if_missing_31('gps_events', 'idx_gps_ev_ts',       '(ts DESC)');
CALL pl_add_index_if_missing_31('gps_events', 'idx_gps_ev_truck_ts', '(truck_id, ts DESC)');
CALL pl_add_index_if_missing_31('gps_events', 'idx_gps_ev_trip_ts',  '(trip_id, ts DESC)');

DROP PROCEDURE pl_add_index_if_missing_31;


-- Grants — Fleet + Ops roles + Admin see everything, Field Supervisor
-- sees live map + alerts only.
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- Executive
(1,  'GpsDashboard', 'index',         1, NOW()),
(1,  'GpsDashboard', 'truck',         1, NOW()),
(1,  'GpsDashboard', 'trip',          1, NOW()),
(1,  'GpsDashboard', 'alerts',        1, NOW()),
(1,  'GpsDashboard', 'livePositions', 1, NOW()),
(1,  'GpsDashboard', 'truckTrack',    1, NOW()),
(1,  'GpsDashboard', 'tripPoints',    1, NOW()),
-- Ops Manager (20)
(20, 'GpsDashboard', 'index',         1, NOW()),
(20, 'GpsDashboard', 'truck',         1, NOW()),
(20, 'GpsDashboard', 'trip',          1, NOW()),
(20, 'GpsDashboard', 'alerts',        1, NOW()),
(20, 'GpsDashboard', 'livePositions', 1, NOW()),
(20, 'GpsDashboard', 'truckTrack',    1, NOW()),
(20, 'GpsDashboard', 'tripPoints',    1, NOW()),
-- Dispatcher (21)
(21, 'GpsDashboard', 'index',         1, NOW()),
(21, 'GpsDashboard', 'truck',         1, NOW()),
(21, 'GpsDashboard', 'trip',          1, NOW()),
(21, 'GpsDashboard', 'alerts',        1, NOW()),
(21, 'GpsDashboard', 'livePositions', 1, NOW()),
(21, 'GpsDashboard', 'truckTrack',    1, NOW()),
(21, 'GpsDashboard', 'tripPoints',    1, NOW()),
-- Fleet Manager (22)
(22, 'GpsDashboard', 'index',         1, NOW()),
(22, 'GpsDashboard', 'truck',         1, NOW()),
(22, 'GpsDashboard', 'trip',          1, NOW()),
(22, 'GpsDashboard', 'alerts',        1, NOW()),
(22, 'GpsDashboard', 'livePositions', 1, NOW()),
(22, 'GpsDashboard', 'truckTrack',    1, NOW()),
(22, 'GpsDashboard', 'tripPoints',    1, NOW()),
-- Field Supervisor (6) — live map + alerts only
(6,  'GpsDashboard', 'index',         1, NOW()),
(6,  'GpsDashboard', 'alerts',        1, NOW()),
(6,  'GpsDashboard', 'livePositions', 1, NOW()),
-- Administrator (10)
(10, 'GpsDashboard', 'index',         1, NOW()),
(10, 'GpsDashboard', 'truck',         1, NOW()),
(10, 'GpsDashboard', 'trip',          1, NOW()),
(10, 'GpsDashboard', 'alerts',        1, NOW()),
(10, 'GpsDashboard', 'livePositions', 1, NOW()),
(10, 'GpsDashboard', 'truckTrack',    1, NOW()),
(10, 'GpsDashboard', 'tripPoints',    1, NOW());


SELECT ug.user_group_name, COUNT(*) AS actions
  FROM user_groups_perms ugp
  JOIN user_groups ug ON ug.c0d = ugp.user_group_id
 WHERE ugp.controller_allowed = 'GpsDashboard'
 GROUP BY ug.c0d, ug.user_group_name;
