-- Pacific Logistics · slice 06 · Routes + Trip Requests
--
-- Sets up the two prerequisites for Dispatch:
--   • routes         — the library of pre-defined route patterns (e.g. "Dar
--                       to Arusha", "Twiga Batching Plant to Site 4")
--   • trip_requests  — a customer or internal request for cargo movement.
--     A trip request can reference a defined route OR carry free-form origin
--     and destination addresses. It progresses through statuses:
--         draft → pending_review → approved → dispatched
--                                       └→ rejected
--                                       └→ cancelled
--
-- The `dispatched` transition happens when Turn 2 builds the `trips` table
-- and a Trip is created from an approved Trip Request.

CREATE TABLE IF NOT EXISTS routes (
    c0d                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id          INT UNSIGNED NOT NULL DEFAULT 1,
    code               VARCHAR(30)   NULL,          -- e.g. DAR-ARU, TWG-SITE4
    name               VARCHAR(160)  NOT NULL,      -- human-readable
    route_type         VARCHAR(20)   NOT NULL DEFAULT 'town',
                                                    -- town | regional | intercity | cross_border
    origin_city        VARCHAR(80)   NULL,
    destination_city   VARCHAR(80)   NULL,
    distance_km        DECIMAL(9,2)  NULL,
    avg_duration_hrs   DECIMAL(6,2)  NULL,
    stops_summary      TEXT          NULL,          -- newline-separated stop names, no separate table for iter 1
    notes              TEXT          NULL,
    active             TINYINT(1)    NOT NULL DEFAULT 1,
    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_routes_type   (route_type, active),
    KEY idx_routes_active (active, deleted),
    KEY idx_routes_code   (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS trip_requests (
    c0d                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id            INT UNSIGNED NOT NULL DEFAULT 1,
    request_no           VARCHAR(20)   NOT NULL DEFAULT '',   -- TR-00001
    customer_id          INT UNSIGNED  NULL,                  -- NULL = internal request (e.g. own site delivery)
    route_id             INT UNSIGNED  NULL,                  -- optional link to library route
    cargo_type           VARCHAR(40)   NOT NULL DEFAULT 'general',
                                                              -- general | construction_materials | bulk_liquid | containerised | hazardous | refrigerated | other
    cargo_description    VARCHAR(255)  NULL,
    weight_kg            DECIMAL(12,2) NULL,
    volume_m3            DECIMAL(10,2) NULL,
    packages_count       INT UNSIGNED  NULL,
    origin_address       VARCHAR(255)  NULL,
    destination_address  VARCHAR(255)  NULL,
    pickup_date          DATE          NULL,
    pickup_time          TIME          NULL,
    delivery_date        DATE          NULL,
    delivery_time        TIME          NULL,
    quote_amount         DECIMAL(14,2) NULL,
    currency             VARCHAR(6)    NOT NULL DEFAULT 'TZS',
    priority             VARCHAR(20)   NOT NULL DEFAULT 'normal', -- low | normal | high | urgent
    status               VARCHAR(30)   NOT NULL DEFAULT 'draft',
                                                              -- draft | pending_review | approved | rejected | cancelled | dispatched
    requested_by         INT UNSIGNED  NULL,                  -- system user who logged the request
    requested_at         DATETIME      NULL,
    approved_by          INT UNSIGNED  NULL,
    approved_at          DATETIME      NULL,
    rejected_by          INT UNSIGNED  NULL,
    rejected_at          DATETIME      NULL,
    reject_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_request_no (request_no),
    KEY idx_tr_customer  (customer_id, deleted),
    KEY idx_tr_status    (status, deleted),
    KEY idx_tr_priority  (priority),
    KEY idx_tr_pickup    (pickup_date),
    KEY idx_tr_route     (route_id)
) 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
-- Routes ---------------------------------------------------------------------
-- Administrator (10) — full
(10, 'Routes', 'index', 1, NOW()), (10, 'Routes', 'add', 1, NOW()), (10, 'Routes', 'save', 1, NOW()),
(10, 'Routes', 'view',  1, NOW()), (10, 'Routes', 'edit', 1, NOW()), (10, 'Routes', 'update', 1, NOW()),
(10, 'Routes', 'delete',1, NOW()),
-- Ops Manager (20) — full
(20, 'Routes', 'index', 1, NOW()), (20, 'Routes', 'add', 1, NOW()), (20, 'Routes', 'save', 1, NOW()),
(20, 'Routes', 'view',  1, NOW()), (20, 'Routes', 'edit', 1, NOW()), (20, 'Routes', 'update', 1, NOW()),
(20, 'Routes', 'delete',1, NOW()),
-- Dispatcher (21) — full (owns route definitions)
(21, 'Routes', 'index', 1, NOW()), (21, 'Routes', 'add', 1, NOW()), (21, 'Routes', 'save', 1, NOW()),
(21, 'Routes', 'view',  1, NOW()), (21, 'Routes', 'edit', 1, NOW()), (21, 'Routes', 'update', 1, NOW()),
-- Fleet Manager (22) — read (needs to see routes for scheduling)
(22, 'Routes', 'index', 1, NOW()), (22, 'Routes', 'view',  1, NOW()),
-- Executive (1), Accountant (3), Finance Manager (2) — read
(1,  'Routes', 'index', 1, NOW()), (1,  'Routes', 'view', 1, NOW()),
(3,  'Routes', 'index', 1, NOW()), (3,  'Routes', 'view', 1, NOW()),
(2,  'Routes', 'index', 1, NOW()), (2,  'Routes', 'view', 1, NOW()),

-- TripRequests ---------------------------------------------------------------
-- Administrator (10) — full including approve/reject
(10, 'TripRequests', 'index',   1, NOW()), (10, 'TripRequests', 'add',    1, NOW()), (10, 'TripRequests', 'save',   1, NOW()),
(10, 'TripRequests', 'view',    1, NOW()), (10, 'TripRequests', 'edit',   1, NOW()), (10, 'TripRequests', 'update', 1, NOW()),
(10, 'TripRequests', 'delete',  1, NOW()), (10, 'TripRequests', 'submit', 1, NOW()), (10, 'TripRequests', 'approve',1, NOW()),
(10, 'TripRequests', 'reject',  1, NOW()), (10, 'TripRequests', 'cancel', 1, NOW()),
-- Ops Manager (20) — full incl. approve/reject
(20, 'TripRequests', 'index',   1, NOW()), (20, 'TripRequests', 'add',    1, NOW()), (20, 'TripRequests', 'save',   1, NOW()),
(20, 'TripRequests', 'view',    1, NOW()), (20, 'TripRequests', 'edit',   1, NOW()), (20, 'TripRequests', 'update', 1, NOW()),
(20, 'TripRequests', 'delete',  1, NOW()), (20, 'TripRequests', 'submit', 1, NOW()), (20, 'TripRequests', 'approve',1, NOW()),
(20, 'TripRequests', 'reject',  1, NOW()), (20, 'TripRequests', 'cancel', 1, NOW()),
-- Dispatcher (21) — create + edit + submit + cancel; approve is Ops Manager's call
(21, 'TripRequests', 'index',   1, NOW()), (21, 'TripRequests', 'add',    1, NOW()), (21, 'TripRequests', 'save',   1, NOW()),
(21, 'TripRequests', 'view',    1, NOW()), (21, 'TripRequests', 'edit',   1, NOW()), (21, 'TripRequests', 'update', 1, NOW()),
(21, 'TripRequests', 'submit',  1, NOW()), (21, 'TripRequests', 'cancel', 1, NOW()),
-- Executive (1), Accountant (3), Finance Manager (2) — read
(1,  'TripRequests', 'index', 1, NOW()), (1,  'TripRequests', 'view', 1, NOW()),
(3,  'TripRequests', 'index', 1, NOW()), (3,  'TripRequests', 'view', 1, NOW()),
(2,  'TripRequests', 'index', 1, NOW()), (2,  'TripRequests', 'view', 1, NOW());
