-- Pacific Logistics · slice 11 · Fuel Management with GL auto-post
--
-- Every refuel is captured here by the Fuel Officer. On save the model
-- immediately posts a journal:
--     Dr 5010 Fuel Expense                (per-truck / per-driver / per-trip tagged)
--     Cr Bank/Cash/FuelCard/MobileMoney   (per payment_method map)
--
-- Fuel-sensor data lives in a companion table populated by the GPS ingestion
-- daemon (Iter 3). The Fuel Officer console will match sensor fill events to
-- fuel_entries and flag variances. For Iter 2 the sensor table exists so the
-- model can persist and reference it, but the UI just shows a placeholder.
--
-- Voiding a fuel entry posts a reversing JE — no in-place edits after post.

CREATE TABLE IF NOT EXISTS fuel_entries (
    c0d                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id              INT UNSIGNED NOT NULL DEFAULT 1,
    entry_no               VARCHAR(20)   NOT NULL DEFAULT '',   -- FUEL-00001
    entry_date             DATE          NOT NULL,
    entry_time             TIME          NULL,
    -- Who / what
    truck_id               INT UNSIGNED  NOT NULL,
    driver_id              INT UNSIGNED  NULL,
    trip_id                INT UNSIGNED  NULL,
    -- Station
    station_name           VARCHAR(120)  NULL,
    station_location       VARCHAR(160)  NULL,
    fuel_type              VARCHAR(20)   NOT NULL DEFAULT 'diesel',   -- diesel | petrol | other
    -- Numbers
    liters                 DECIMAL(10,3) NOT NULL DEFAULT 0.000,
    price_per_liter        DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    gross_amount           DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    currency               VARCHAR(6)    NOT NULL DEFAULT 'TZS',
    odometer_reading       INT UNSIGNED  NULL,
    -- Payment
    payment_method         VARCHAR(30)   NOT NULL DEFAULT 'cash',
    payment_reference      VARCHAR(80)   NULL,
    receipt_no             VARCHAR(80)   NULL,
    receipt_url            VARCHAR(255)  NULL,
    receipt_file_name      VARCHAR(255)  NULL,
    -- Sensor reconciliation (populated in Iter 3 when GPS ingestion is live)
    sensor_event_id        INT UNSIGNED  NULL,
    sensor_fill_liters     DECIMAL(10,3) NULL,
    sensor_variance_l      DECIMAL(10,3) NULL,
    variance_status        VARCHAR(20)   NOT NULL DEFAULT 'pending',
                                                                 -- pending | matched | investigating | adjusted | no_sensor
    variance_note          VARCHAR(255)  NULL,
    -- Accounting
    status                 VARCHAR(20)   NOT NULL DEFAULT 'posted',
                                                                 -- posted | voided
    je_id                  INT UNSIGNED  NULL,
    void_je_id             INT UNSIGNED  NULL,
    void_reason            VARCHAR(255)  NULL,
    voided_by              INT UNSIGNED  NULL,
    voided_at              DATETIME      NULL,
    notes                  TEXT          NULL,
    -- Audit
    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_fuel_entry_no (entry_no),
    KEY idx_fuel_truck    (truck_id, entry_date),
    KEY idx_fuel_driver   (driver_id, entry_date),
    KEY idx_fuel_trip     (trip_id),
    KEY idx_fuel_status   (status, deleted),
    KEY idx_fuel_variance (variance_status),
    KEY idx_fuel_date     (entry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Sensor events — populated by GPS ingestion (Iter 3). Table exists now so
-- FuelEntries model can reference it and the schema is stable.
CREATE TABLE IF NOT EXISTS fuel_sensor_events (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    device_id        VARCHAR(40)  NULL,               -- Teltonika IMEI or similar
    truck_id         INT UNSIGNED NOT NULL,
    event_ts         DATETIME     NOT NULL,
    event_kind       VARCHAR(20)  NOT NULL,           -- fill | drop | drain | idle
    level_before_l   DECIMAL(10,3) NULL,
    level_after_l    DECIMAL(10,3) NULL,
    delta_l          DECIMAL(10,3) NULL,
    lat              DECIMAL(10,6) NULL,
    lng              DECIMAL(10,6) NULL,
    matched_fuel_entry_id INT UNSIGNED NULL,
    matched_at       DATETIME     NULL,
    matched_by       INT UNSIGNED NULL,
    created_on       DATETIME     NULL,
    PRIMARY KEY (c0d),
    KEY idx_fse_truck (truck_id, event_ts),
    KEY idx_fse_kind  (event_kind, event_ts),
    KEY idx_fse_matched (matched_fuel_entry_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
-- Administrator (10) — full
(10, 'FuelEntries', 'index',  1, NOW()), (10, 'FuelEntries', 'view',   1, NOW()),
(10, 'FuelEntries', 'add',    1, NOW()), (10, 'FuelEntries', 'save',   1, NOW()),
(10, 'FuelEntries', 'edit',   1, NOW()), (10, 'FuelEntries', 'update', 1, NOW()),
(10, 'FuelEntries', 'void',   1, NOW()), (10, 'FuelEntries', 'delete', 1, NOW()),
-- Fuel Officer (23) — full owner
(23, 'FuelEntries', 'index',  1, NOW()), (23, 'FuelEntries', 'view',   1, NOW()),
(23, 'FuelEntries', 'add',    1, NOW()), (23, 'FuelEntries', 'save',   1, NOW()),
(23, 'FuelEntries', 'edit',   1, NOW()), (23, 'FuelEntries', 'update', 1, NOW()),
(23, 'FuelEntries', 'void',   1, NOW()),
-- Ops Manager (20) — full (can record + void)
(20, 'FuelEntries', 'index',  1, NOW()), (20, 'FuelEntries', 'view',   1, NOW()),
(20, 'FuelEntries', 'add',    1, NOW()), (20, 'FuelEntries', 'save',   1, NOW()),
(20, 'FuelEntries', 'edit',   1, NOW()), (20, 'FuelEntries', 'update', 1, NOW()),
(20, 'FuelEntries', 'void',   1, NOW()),
-- Fleet Manager (22) — read + record (workshop-side awareness)
(22, 'FuelEntries', 'index',  1, NOW()), (22, 'FuelEntries', 'view',   1, NOW()),
(22, 'FuelEntries', 'add',    1, NOW()), (22, 'FuelEntries', 'save',   1, NOW()),
-- Dispatcher (21) — read (needs fuel context)
(21, 'FuelEntries', 'index',  1, NOW()), (21, 'FuelEntries', 'view',   1, NOW()),
-- Accountant (3) — read for reconciliation
(3,  'FuelEntries', 'index',  1, NOW()), (3,  'FuelEntries', 'view',   1, NOW()),
-- Finance Manager (2) — read
(2,  'FuelEntries', 'index',  1, NOW()), (2,  'FuelEntries', 'view',   1, NOW()),
-- Executive (1) — read
(1,  'FuelEntries', 'index',  1, NOW()), (1,  'FuelEntries', 'view',   1, NOW());
