-- Pacific Logistics · slice 03 · Drivers + Driver Documents
--
-- Mirrors the Trucks slice pattern: identity table + doc types lookup + doc
-- instances. Kept intentionally separate from the platform's `users` table
-- because a driver may exist in the system long before (or without ever)
-- being given a mobile-app login. Iter 3 will add driver_login_credentials
-- as a separate slice once the driver mobile app is standing up.

CREATE TABLE IF NOT EXISTS drivers (
    c0d                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id          INT UNSIGNED NOT NULL DEFAULT 1,
    employee_no        VARCHAR(30)   NULL,
    full_name          VARCHAR(120)  NOT NULL,
    id_no              VARCHAR(40)   NULL,          -- NIDA / national ID no
    phone_1            VARCHAR(30)   NULL,
    phone_2            VARCHAR(30)   NULL,
    email              VARCHAR(120)  NULL,
    dob                DATE          NULL,
    gender             VARCHAR(10)   NULL,          -- male | female | other
    address            VARCHAR(255)  NULL,
    city               VARCHAR(60)   NULL,
    country            VARCHAR(60)   NOT NULL DEFAULT 'TANZANIA',
    next_of_kin_name   VARCHAR(120)  NULL,
    next_of_kin_phone  VARCHAR(30)   NULL,
    licence_no         VARCHAR(40)   NULL,
    licence_class      VARCHAR(20)   NULL,          -- BCE / C / D etc
    licence_expires    DATE          NULL,
    hire_date          DATE          NULL,
    status             VARCHAR(20)   NOT NULL DEFAULT 'available',
                                                    -- available | on_trip | on_leave | suspended | terminated
    branch_id          INT UNSIGNED  NULL,
    photo_url          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),
    KEY idx_drivers_name    (full_name),
    KEY idx_drivers_status  (status, deleted),
    KEY idx_drivers_licence (licence_expires),
    KEY idx_drivers_deleted (deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS driver_doc_types (
    c0d           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id     INT UNSIGNED NOT NULL DEFAULT 1,
    slug          VARCHAR(40)  NOT NULL,
    display_name  VARCHAR(80)  NOT NULL,
    has_expiry    TINYINT(1)   NOT NULL DEFAULT 1,
    warn_days     INT          NOT NULL DEFAULT 30,
    required      TINYINT(1)   NOT NULL DEFAULT 1,
    active        TINYINT(1)   NOT NULL DEFAULT 1,
    sort_order    INT          NOT NULL DEFAULT 0,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_driver_doc_types_slug (tenant_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO driver_doc_types (tenant_id, slug, display_name, has_expiry, warn_days, required, sort_order) VALUES
(1, 'licence',      "Driver's Licence",              1,  30, 1, 10),
(1, 'national_id',  'National ID (NIDA)',            0,   0, 1, 20),
(1, 'medical',      'Medical Fitness Certificate',   1,  30, 1, 30),
(1, 'contract',     'Employment Contract',           0,   0, 0, 40),
(1, 'ppe_issue',    'PPE Issue Record',              0,   0, 0, 50),
(1, 'training',     'Defensive-Driving Training',    1, 365, 0, 60),
(1, 'psv_licence',  'PSV / Commercial Endorsement',  1,  30, 0, 70),
(1, 'passport',     'Passport',                      1,  90, 0, 80);


CREATE TABLE IF NOT EXISTS driver_docs (
    c0d            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id      INT UNSIGNED NOT NULL DEFAULT 1,
    driver_id      INT UNSIGNED NOT NULL,
    doc_type_id    INT UNSIGNED NOT NULL,
    doc_no         VARCHAR(80)   NULL,
    issuer         VARCHAR(120)  NULL,
    issued_on      DATE          NULL,
    expires_on     DATE          NULL,
    file_url       VARCHAR(255)  NULL,
    file_name      VARCHAR(255)  NULL,
    file_size      INT UNSIGNED  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),
    KEY idx_driver_docs_driver (driver_id, deleted),
    KEY idx_driver_docs_expiry (expires_on, deleted),
    KEY idx_driver_docs_type   (doc_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
