-- Pacific Logistics · slice 04 · Driver ↔ Truck Assignments
--
-- One driver → one active truck at a time.  A truck may have multiple active
-- drivers (shift work, relief cover). Prior assignments stay on record with
-- assigned_to filled in — this table IS the assignment history register.
--
-- Business rule (enforced by the model, not by DB constraint, per rsflow style):
--   • When a NEW assignment is created for a driver, any existing OPEN
--     assignment for that same driver is auto-closed with assigned_to = the
--     new assignment's start date and end_reason = 'Reassignment'.
--   • assigned_to IS NULL  ⇒  assignment is currently active.

CREATE TABLE IF NOT EXISTS driver_truck_assignments (
    c0d            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id      INT UNSIGNED NOT NULL DEFAULT 1,
    driver_id      INT UNSIGNED NOT NULL,
    truck_id       INT UNSIGNED NOT NULL,
    assigned_from  DATE          NOT NULL,
    assigned_to    DATE          NULL,             -- NULL = still active
    role_type      VARCHAR(20)   NOT NULL DEFAULT 'primary',  -- primary | relief | trip
    reason         VARCHAR(160)  NULL,             -- why the assignment was made
    notes          TEXT          NULL,
    ended_by       INT UNSIGNED  NULL,             -- user who closed the assignment
    end_reason     VARCHAR(160)  NULL,             -- why it was closed
    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_dta_driver (driver_id, deleted, assigned_to),
    KEY idx_dta_truck  (truck_id,  deleted, assigned_to),
    KEY idx_dta_active (assigned_to, deleted)      -- fast lookup of open assignments
) 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
-- Administrator (10) — full
(10, 'DriverAssignments', 'index',   1, NOW()),
(10, 'DriverAssignments', 'add',     1, NOW()),
(10, 'DriverAssignments', 'view',    1, NOW()),
(10, 'DriverAssignments', 'end',     1, NOW()),
(10, 'DriverAssignments', 'delete',  1, NOW()),
-- Ops Manager (20) — full (owns dispatch and daily driver-truck decisions)
(20, 'DriverAssignments', 'index',   1, NOW()),
(20, 'DriverAssignments', 'add',     1, NOW()),
(20, 'DriverAssignments', 'view',    1, NOW()),
(20, 'DriverAssignments', 'end',     1, NOW()),
(20, 'DriverAssignments', 'delete',  1, NOW()),
-- Fleet Manager (22) — full (workshop needs to know who is on which truck)
(22, 'DriverAssignments', 'index',   1, NOW()),
(22, 'DriverAssignments', 'add',     1, NOW()),
(22, 'DriverAssignments', 'view',    1, NOW()),
(22, 'DriverAssignments', 'end',     1, NOW()),
(22, 'DriverAssignments', 'delete',  1, NOW()),
-- Dispatcher (21) — read
(21, 'DriverAssignments', 'index',   1, NOW()),
(21, 'DriverAssignments', 'view',    1, NOW()),
-- HR Officer (24) — read
(24, 'DriverAssignments', 'index',   1, NOW()),
(24, 'DriverAssignments', 'view',    1, NOW()),
-- Executive (1) — read
(1,  'DriverAssignments', 'index',   1, NOW()),
(1,  'DriverAssignments', 'view',    1, NOW()),
-- Accountant (3) — read
(3,  'DriverAssignments', 'index',   1, NOW()),
(3,  'DriverAssignments', 'view',    1, NOW()),
-- Finance Manager (2) — read
(2,  'DriverAssignments', 'index',   1, NOW()),
(2,  'DriverAssignments', 'view',    1, NOW());
