-- Pacific Logistics · slice 01 · Trucks + Truck Documents
--
-- Introduces the fleet's vehicle register and its compliance-document register.
-- Both are tenant-scoped from day one so the same schema serves single-tenant
-- Pacific and future multi-tenant SaaS without a data migration.
--
-- Convention: primary key is `c0d` to stay consistent with rsflow base tables
-- that Pacific inherits. Soft delete via `deleted` flag; created/edited audit
-- fields on every mutable table.

CREATE TABLE IF NOT EXISTS truck_types (
    c0d           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id     INT UNSIGNED NOT NULL DEFAULT 1,
    slug          VARCHAR(30)  NOT NULL,
    display_name  VARCHAR(60)  NOT NULL,
    description   VARCHAR(255) NULL,
    active        TINYINT(1)   NOT NULL DEFAULT 1,
    sort_order    INT          NOT NULL DEFAULT 0,
    created_on    DATETIME     NULL,
    PRIMARY KEY (c0d),
    UNIQUE KEY uq_truck_types_slug (tenant_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO truck_types (tenant_id, slug, display_name, description, sort_order, created_on) VALUES
(1, 'dump',    'Dump Truck',          'In-town construction materials, short-cycle', 10, NOW()),
(1, 'semi',    'Regional Semi-Truck', 'Long-haul cargo, cross-country',              20, NOW()),
(1, 'pickup',  'Pickup / Utility',    'Light utility vehicle',                       30, NOW()),
(1, 'trailer', 'Trailer',             'Standalone trailer',                          40, NOW());


CREATE TABLE IF NOT EXISTS trucks (
    c0d                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id          INT UNSIGNED NOT NULL DEFAULT 1,
    reg_no             VARCHAR(30)   NOT NULL,
    truck_type_id      INT UNSIGNED  NULL,
    make               VARCHAR(60)   NULL,
    model              VARCHAR(60)   NULL,
    year               SMALLINT UNSIGNED NULL,
    chassis_no         VARCHAR(60)   NULL,
    engine_no          VARCHAR(60)   NULL,
    colour             VARCHAR(30)   NULL,
    tare_kg            INT UNSIGNED  NULL,
    capacity_kg        INT UNSIGNED  NULL,
    fuel_tank_liters   INT UNSIGNED  NULL,
    current_odometer   INT UNSIGNED  NOT NULL DEFAULT 0,
    ownership          VARCHAR(20)   NOT NULL DEFAULT 'owned',    -- owned | leased | contracted
    acquired_on        DATE          NULL,
    status             VARCHAR(20)   NOT NULL DEFAULT 'active',   -- active | maintenance | inactive | retired
    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_trucks_reg     (tenant_id, reg_no),
    KEY idx_trucks_status  (status, deleted),
    KEY idx_trucks_type    (truck_type_id),
    KEY idx_trucks_deleted (deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS truck_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,  -- start alerting this many days before expiry
    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_truck_doc_types_slug (tenant_id, slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO truck_doc_types (tenant_id, slug, display_name, has_expiry, warn_days, required, sort_order) VALUES
(1, 'registration', 'Vehicle Registration Card', 1, 60, 1, 10),
(1, 'insurance',    'Insurance Policy',          1, 30, 1, 20),
(1, 'inspection',   'Annual Inspection',         1, 30, 1, 30),
(1, 'road_permit',  'Road Permit',               1, 30, 0, 40),
(1, 'tra_licence',  'TRA Transport Licence',     1, 30, 1, 50),
(1, 'weighbridge',  'Weighbridge Certificate',   1, 60, 0, 60),
(1, 'fitness',      'Fitness Certificate',       1, 30, 0, 70);


CREATE TABLE IF NOT EXISTS truck_docs (
    c0d            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id      INT UNSIGNED NOT NULL DEFAULT 1,
    truck_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,
    cost_amount    DECIMAL(14,2) NULL,
    cost_currency  VARCHAR(6)    NOT NULL DEFAULT 'TZS',
    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_truck_docs_truck  (truck_id, deleted),
    KEY idx_truck_docs_expiry (expires_on, deleted),
    KEY idx_truck_docs_type   (doc_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Grant Administrator (user_group_id = 10 in rsflow seed) full access to the
-- Trucks controller. Additional grants for Ops Manager, Fleet Manager,
-- Dispatcher, HR will be added once those user groups are seeded
-- (Pacific slice 03 / RBAC).
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
(10, 'Trucks', 'index',       1, NOW()),
(10, 'Trucks', 'add',         1, NOW()),
(10, 'Trucks', 'view',        1, NOW()),
(10, 'Trucks', 'edit',        1, NOW()),
(10, 'Trucks', 'delete',      1, NOW()),
(10, 'Trucks', 'uploadDoc',   1, NOW()),
(10, 'Trucks', 'deleteDoc',   1, NOW()),
(10, 'Trucks', 'viewDoc',     1, NOW());
