-- Pacific Logistics · slice 13 · Trip Request cargo items + Trip stops
--
-- Two lightweight additions to close UAT gaps A1 and A3:
--
--   1. trip_request_cargo_items — optional per-item breakdown of what the
--      truck is carrying (bags of cement + pallets of FMCG + drums of oil,
--      etc.). Header weight/volume on trip_requests still work for simple
--      single-load runs; items are optional detail.
--
--   2. trip_stops — ordered stops per request AND per trip. A stop starts life
--      on a Trip Request (planning). On dispatch the row is COPIED to a new
--      row with trip_id set (execution stops). Original request-side rows
--      stay put for audit — "planned vs actual" is one SELECT away.
--
--      Kinds: pickup | delivery | waypoint | weighbridge | fuel_stop |
--             border | overnight_rest | customer_visit
--
--      Planned times are set on the request. Actual arrival/departure are
--      updated by dispatcher (web) or driver (mobile in Iter 3).

CREATE TABLE IF NOT EXISTS trip_request_cargo_items (
    c0d                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    trip_request_id    INT UNSIGNED NOT NULL,
    seq_no             INT           NOT NULL DEFAULT 1,
    item_name          VARCHAR(160)  NOT NULL,
    qty                DECIMAL(12,3) NOT NULL DEFAULT 1.000,
    uom                VARCHAR(20)   NOT NULL DEFAULT 'units',
                                                    -- units | bags | pallets | containers | kg | tons | liters | boxes | drums
    weight_kg          DECIMAL(12,2) NULL,
    volume_m3          DECIMAL(10,2) NULL,
    packaging          VARCHAR(60)   NULL,          -- pallets | shrink-wrapped | loose | crated | drums
    handling_notes     VARCHAR(255)  NULL,          -- fragile | hazardous | cold_chain | keep_upright
    unit_value         DECIMAL(14,2) NULL,          -- optional value declaration for insurance
    currency           VARCHAR(6)    NOT NULL DEFAULT 'TZS',
    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_trci_req (trip_request_id, deleted, seq_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS trip_stops (
    c0d                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id           INT UNSIGNED NOT NULL DEFAULT 1,
    trip_request_id     INT UNSIGNED NULL,          -- set on planning; null on dispatched execution rows
    trip_id             INT UNSIGNED NULL,          -- set on dispatch (copied from request)
    seq_no              INT           NOT NULL DEFAULT 1,
    kind                VARCHAR(20)   NOT NULL DEFAULT 'waypoint',
                                                    -- pickup | delivery | waypoint | weighbridge |
                                                    -- fuel_stop | border | overnight_rest | customer_visit
    location_name       VARCHAR(160)  NULL,
    address             VARCHAR(255)  NULL,
    city                VARCHAR(80)   NULL,
    country             VARCHAR(60)   NULL,
    lat                 DECIMAL(10,6) NULL,
    lng                 DECIMAL(10,6) NULL,
    contact_name        VARCHAR(120)  NULL,
    contact_phone       VARCHAR(30)   NULL,
    planned_arrival     DATETIME      NULL,
    planned_departure   DATETIME      NULL,
    actual_arrival      DATETIME      NULL,
    actual_departure    DATETIME      NULL,
    stop_notes          VARCHAR(255)  NULL,
    -- Execution notes (set at the stop)
    arrival_odometer    INT UNSIGNED  NULL,
    departure_odometer  INT UNSIGNED  NULL,
    signature_url       VARCHAR(255)  NULL,         -- POD signature (Iter 3 mobile)
    photo_url           VARCHAR(255)  NULL,         -- stop-arrival photo (Iter 3 mobile)
    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_ts_request (trip_request_id, deleted, seq_no),
    KEY idx_ts_trip    (trip_id, deleted, seq_no),
    KEY idx_ts_kind    (kind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — additive actions on TripRequests + Trips controllers.
-- Roles that already have write access on the parent object keep write
-- access on its children.
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- TripRequests · new sub-actions
(10, 'TripRequests', 'addCargoItem',    1, NOW()), (10, 'TripRequests', 'deleteCargoItem', 1, NOW()),
(10, 'TripRequests', 'addStop',         1, NOW()), (10, 'TripRequests', 'deleteStop',      1, NOW()),
(20, 'TripRequests', 'addCargoItem',    1, NOW()), (20, 'TripRequests', 'deleteCargoItem', 1, NOW()),
(20, 'TripRequests', 'addStop',         1, NOW()), (20, 'TripRequests', 'deleteStop',      1, NOW()),
(21, 'TripRequests', 'addCargoItem',    1, NOW()), (21, 'TripRequests', 'deleteCargoItem', 1, NOW()),
(21, 'TripRequests', 'addStop',         1, NOW()), (21, 'TripRequests', 'deleteStop',      1, NOW()),
-- Trips · new stop actions (dispatcher / ops manager can annotate execution)
(10, 'Trips', 'addStop',        1, NOW()), (10, 'Trips', 'updateStop',     1, NOW()),
(10, 'Trips', 'deleteStop',     1, NOW()), (10, 'Trips', 'arriveStop',     1, NOW()),
(10, 'Trips', 'departStop',     1, NOW()),
(20, 'Trips', 'addStop',        1, NOW()), (20, 'Trips', 'updateStop',     1, NOW()),
(20, 'Trips', 'deleteStop',     1, NOW()), (20, 'Trips', 'arriveStop',     1, NOW()),
(20, 'Trips', 'departStop',     1, NOW()),
(21, 'Trips', 'addStop',        1, NOW()), (21, 'Trips', 'updateStop',     1, NOW()),
(21, 'Trips', 'deleteStop',     1, NOW()), (21, 'Trips', 'arriveStop',     1, NOW()),
(21, 'Trips', 'departStop',     1, NOW());
