-- Pacific Logistics · slice 07 · Trips + Status Log (final Iter 1 slice)
--
-- A `trip` is an execution record — one truck, one driver, one journey. Trips
-- are almost always created from an APPROVED trip_request via the Dispatch
-- board; ad-hoc trips are supported but discouraged.
--
-- Lifecycle (Iter 1 — Iter 3 mobile app will add finer-grained states):
--     dispatched → in_transit → delivered → closed
--                                    └──────↑
--                       any → cancelled  (with reason)
--
-- Each transition writes a row to trip_status_log so the audit trail is
-- independent of the trips table itself.

CREATE TABLE IF NOT EXISTS trips (
    c0d                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id           INT UNSIGNED NOT NULL DEFAULT 1,
    trip_no             VARCHAR(20)   NOT NULL DEFAULT '',   -- TRIP-00001
    trip_request_id     INT UNSIGNED  NULL,                  -- source request (NULL for ad-hoc)
    driver_id           INT UNSIGNED  NOT NULL,
    truck_id            INT UNSIGNED  NOT NULL,
    -- snapshots (denormalised on create — request may later be edited/deleted)
    customer_id         INT UNSIGNED  NULL,
    route_id            INT UNSIGNED  NULL,
    cargo_type          VARCHAR(40)   NULL,
    cargo_description   VARCHAR(255)  NULL,
    weight_kg           DECIMAL(12,2) NULL,
    origin_address      VARCHAR(255)  NULL,
    destination_address VARCHAR(255)  NULL,
    -- schedule
    planned_pickup      DATETIME      NULL,
    planned_delivery    DATETIME      NULL,
    actual_start        DATETIME      NULL,          -- when driver started
    actual_end          DATETIME      NULL,          -- when trip closed
    -- distance
    odometer_start      INT UNSIGNED  NULL,
    odometer_end        INT UNSIGNED  NULL,
    planned_km          DECIMAL(9,2)  NULL,
    actual_km           DECIMAL(9,2)  NULL,
    -- money
    quote_amount        DECIMAL(14,2) NULL,          -- snapshot from request
    invoice_amount      DECIMAL(14,2) NULL,          -- what we actually invoice
    currency            VARCHAR(6)    NOT NULL DEFAULT 'TZS',
    -- lifecycle
    status              VARCHAR(30)   NOT NULL DEFAULT 'dispatched',
                                                    -- dispatched | in_transit | delivered | closed | cancelled
    dispatched_by       INT UNSIGNED  NULL,
    dispatched_at       DATETIME      NULL,
    started_by          INT UNSIGNED  NULL,
    started_at          DATETIME      NULL,
    delivered_by        INT UNSIGNED  NULL,
    delivered_at        DATETIME      NULL,
    closed_by           INT UNSIGNED  NULL,
    closed_at           DATETIME      NULL,
    cancelled_by        INT UNSIGNED  NULL,
    cancelled_at        DATETIME      NULL,
    cancel_reason       VARCHAR(255)  NULL,
    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),
    UNIQUE KEY uq_trip_no        (trip_no),
    KEY       idx_trips_driver   (driver_id, status, deleted),
    KEY       idx_trips_truck    (truck_id,  status, deleted),
    KEY       idx_trips_status   (status, deleted),
    KEY       idx_trips_request  (trip_request_id),
    KEY       idx_trips_customer (customer_id),
    KEY       idx_trips_pickup   (planned_pickup)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS trip_status_log (
    c0d          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    trip_id      INT UNSIGNED NOT NULL,
    from_status  VARCHAR(30)  NULL,
    to_status    VARCHAR(30)  NOT NULL,
    changed_by   INT UNSIGNED NULL,
    changed_at   DATETIME     NOT NULL,
    note         VARCHAR(255) NULL,
    PRIMARY KEY (c0d),
    KEY idx_tsl_trip (trip_id, changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ---- Grants ----
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- Trips actions: index / view / dispatch / start / deliver / close / cancel / delete / board / boardDataAj
-- Administrator (10) — full
(10, 'Trips', 'index',       1, NOW()), (10, 'Trips', 'view',        1, NOW()),
(10, 'Trips', 'dispatch',    1, NOW()), (10, 'Trips', 'start',       1, NOW()),
(10, 'Trips', 'deliver',     1, NOW()), (10, 'Trips', 'close',       1, NOW()),
(10, 'Trips', 'cancel',      1, NOW()), (10, 'Trips', 'delete',      1, NOW()),
(10, 'Trips', 'board',       1, NOW()), (10, 'Trips', 'boardDataAj', 1, NOW()),
-- Ops Manager (20) — full
(20, 'Trips', 'index',       1, NOW()), (20, 'Trips', 'view',        1, NOW()),
(20, 'Trips', 'dispatch',    1, NOW()), (20, 'Trips', 'start',       1, NOW()),
(20, 'Trips', 'deliver',     1, NOW()), (20, 'Trips', 'close',       1, NOW()),
(20, 'Trips', 'cancel',      1, NOW()), (20, 'Trips', 'delete',      1, NOW()),
(20, 'Trips', 'board',       1, NOW()), (20, 'Trips', 'boardDataAj', 1, NOW()),
-- Dispatcher (21) — everything except delete
(21, 'Trips', 'index',       1, NOW()), (21, 'Trips', 'view',        1, NOW()),
(21, 'Trips', 'dispatch',    1, NOW()), (21, 'Trips', 'start',       1, NOW()),
(21, 'Trips', 'deliver',     1, NOW()), (21, 'Trips', 'close',       1, NOW()),
(21, 'Trips', 'cancel',      1, NOW()),
(21, 'Trips', 'board',       1, NOW()), (21, 'Trips', 'boardDataAj', 1, NOW()),
-- Fleet Manager (22) — read + board (to see who's out)
(22, 'Trips', 'index',       1, NOW()), (22, 'Trips', 'view',        1, NOW()),
(22, 'Trips', 'board',       1, NOW()), (22, 'Trips', 'boardDataAj', 1, NOW()),
-- Executive (1), Accountant (3), Finance Manager (2) — read
(1,  'Trips', 'index', 1, NOW()), (1,  'Trips', 'view', 1, NOW()), (1,  'Trips', 'board', 1, NOW()), (1,  'Trips', 'boardDataAj', 1, NOW()),
(3,  'Trips', 'index', 1, NOW()), (3,  'Trips', 'view', 1, NOW()),
(2,  'Trips', 'index', 1, NOW()), (2,  'Trips', 'view', 1, NOW());
