-- Pacific Logistics · slice 23 · Fleet Analytics (Iter 4 Turn 4)
--
-- Zero new tables. All the analytics ride on the analytical tags we've
-- been putting on journal_lines from day one: truck_id, driver_id, trip_id,
-- customer_id, expense_id, fuel_entry_id, work_order_id.
--
-- Slice adds:
--   * 2 composite indexes on journal_lines to keep the per-truck and
--     per-customer rollups fast (crucial once the JE table grows past
--     a few hundred thousand rows).
--   * Grants for the new FleetAnalytics controller.


-- ═══════════════════════════════════════════════════════════════════════
-- Indexes for the rollup queries
-- ═══════════════════════════════════════════════════════════════════════
-- The model's queries filter WHERE truck_id IS NOT NULL + join by
-- journal_entry_id + filter by entry_date. Composite indexes on those
-- axes let MySQL avoid full table scans once JE volume grows.
-- MySQL 8: ALGORITHM=INSTANT keeps this near-zero-downtime.

-- Skip if already present — we can't use CREATE INDEX IF NOT EXISTS < 8.0.29
-- but MySQL emits a warning (not error) for duplicate KEY. Wrap in a proc.
DELIMITER $$
DROP PROCEDURE IF EXISTS pl_add_idx_if_missing $$
CREATE PROCEDURE pl_add_idx_if_missing(IN tbl VARCHAR(64), IN idx VARCHAR(64), IN spec VARCHAR(200))
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('ALTER TABLE ', tbl, ' ADD INDEX ', idx, ' ', spec);
        PREPARE stmt FROM @s; EXECUTE stmt; DEALLOCATE PREPARE stmt;
    END IF;
END $$
DELIMITER ;

CALL pl_add_idx_if_missing('journal_lines', 'idx_jl_truck_je',    '(truck_id, journal_entry_id)');
CALL pl_add_idx_if_missing('journal_lines', 'idx_jl_customer_je', '(customer_id, journal_entry_id)');
CALL pl_add_idx_if_missing('journal_lines', 'idx_jl_driver_je',   '(driver_id, journal_entry_id)');
CALL pl_add_idx_if_missing('journal_entries', 'idx_je_date',      '(entry_date)');

DROP PROCEDURE pl_add_idx_if_missing;


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — Fleet analytics is Admin + Exec + Fleet + Ops + Accountant.
-- Dispatcher gets read-only on the summary.
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(10, 'FleetAnalytics', 'index',            1, NOW()), (10, 'FleetAnalytics', 'truckPnl',         1, NOW()),
(10, 'FleetAnalytics', 'customerRevenue',  1, NOW()), (10, 'FleetAnalytics', 'driverScorecards', 1, NOW()),
(20, 'FleetAnalytics', 'index',            1, NOW()), (20, 'FleetAnalytics', 'truckPnl',         1, NOW()),
(20, 'FleetAnalytics', 'customerRevenue',  1, NOW()), (20, 'FleetAnalytics', 'driverScorecards', 1, NOW()),
(30, 'FleetAnalytics', 'index',            1, NOW()), (30, 'FleetAnalytics', 'truckPnl',         1, NOW()),
(30, 'FleetAnalytics', 'customerRevenue',  1, NOW()),
(22, 'FleetAnalytics', 'index',            1, NOW()), (22, 'FleetAnalytics', 'truckPnl',         1, NOW()),
(22, 'FleetAnalytics', 'driverScorecards', 1, NOW()),
(21, 'FleetAnalytics', 'index',            1, NOW()),
(1,  'FleetAnalytics', 'index',            1, NOW()), (1,  'FleetAnalytics', 'truckPnl',         1, NOW()),
(1,  'FleetAnalytics', 'customerRevenue',  1, NOW()), (1,  'FleetAnalytics', 'driverScorecards', 1, NOW());
