-- Pacific Logistics · slice 15 · GPS Foundation
--
-- Data model for the tracking pipeline:
--
--   gps_devices     — one per Teltonika unit (or other tracker); linked to a truck
--   gps_positions   — high-volume telemetry stream (one row per fix, ~1/min per truck)
--   gps_events      — filtered/aggregated events (over_speed, harsh_brake,
--                     ignition_on/off, geofence enter/exit, fuel_drop)
--   geofences       — polygon zones (depot, customer sites, no-go corridors)
--   geofence_events — enter/exit records tagged to a device + geofence
--
-- Ingestion runs via a public webhook at /GpsIngest/webhook?token=... — the
-- Traccar bridge (or direct Codec 8E listener) POSTs each position here.
-- No session auth — token in URL is verified against config.
--
-- Volumes: gps_positions is intentionally simple (no partitioning yet) since
-- Pacific starts with ~10-20 trucks. Once we cross 100 trucks with 1-min ping
-- rate we'll partition by month. That's an Iter 4/5 problem.

CREATE TABLE IF NOT EXISTS gps_devices (
    c0d             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id       INT UNSIGNED NOT NULL DEFAULT 1,
    device_code     VARCHAR(60)  NOT NULL,                 -- IMEI, unique_id, or vendor ident
    device_type     VARCHAR(40)  NOT NULL DEFAULT 'teltonika_fmc920',
                                                    -- teltonika_fmc920 | teltonika_fmb920 |
                                                    -- traccar_generic | streamax_x1n | other
    protocol        VARCHAR(30)  NOT NULL DEFAULT 'codec_8e',
                                                    -- codec_8e | osmand | traccar | other
    truck_id        INT UNSIGNED NULL,               -- fitted to which truck (NULL = spare)
    sim_msisdn      VARCHAR(30)  NULL,               -- SIM phone number
    sim_iccid       VARCHAR(30)  NULL,
    installed_on    DATE         NULL,
    firmware_ver    VARCHAR(40)  NULL,
    fuel_sensor     TINYINT(1)   NOT NULL DEFAULT 0, -- Escort TD-500 or similar wired in
    dashcam         TINYINT(1)   NOT NULL DEFAULT 0, -- Streamax X1N or similar
    status          VARCHAR(20)  NOT NULL DEFAULT 'active',  -- active | inactive | offline | retired
    last_seen_at    DATETIME     NULL,
    last_position_id INT UNSIGNED NULL,              -- cache: latest gps_positions row for this device
    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_device_code (device_code),
    KEY idx_gd_truck  (truck_id),
    KEY idx_gd_status (status, deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS gps_positions (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id        INT UNSIGNED NOT NULL,
    truck_id         INT UNSIGNED NULL,              -- denormalised for fast slicing
    ts               DATETIME     NOT NULL,          -- device_time / fix_time
    server_ts        DATETIME     NOT NULL,          -- when we received it
    lat              DECIMAL(10,6) NOT NULL,
    lng              DECIMAL(10,6) NOT NULL,
    altitude_m       DECIMAL(8,2)  NULL,
    speed_kph        DECIMAL(6,2)  NULL,
    heading_deg      DECIMAL(6,2)  NULL,
    valid            TINYINT(1)   NOT NULL DEFAULT 1,
    -- Vehicle-side attributes (as available on the device)
    ignition         TINYINT(1)   NULL,
    odometer_can_km  INT UNSIGNED NULL,
    fuel_level_l     DECIMAL(10,2) NULL,
    engine_rpm       INT UNSIGNED NULL,
    engine_temp_c    DECIMAL(5,1)  NULL,
    battery_v        DECIMAL(5,2)  NULL,
    -- Raw attributes JSON (for Codec 8E extended IO — parked here for later)
    attrs_json       JSON         NULL,
    PRIMARY KEY (c0d),
    KEY idx_gp_device_ts (device_id, ts),
    KEY idx_gp_truck_ts  (truck_id, ts),
    KEY idx_gp_server_ts (server_ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS gps_events (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    device_id        INT UNSIGNED NOT NULL,
    truck_id         INT UNSIGNED NULL,
    trip_id          INT UNSIGNED NULL,              -- if the event happens during a trip
    ts               DATETIME     NOT NULL,
    kind             VARCHAR(30)  NOT NULL,
                                                    -- over_speed | harsh_brake | harsh_accel | idle |
                                                    -- ignition_on | ignition_off | geofence_enter |
                                                    -- geofence_exit | fuel_fill | fuel_drop | tow |
                                                    -- crash | panic
    severity         VARCHAR(20)  NOT NULL DEFAULT 'info', -- info | low | medium | high | critical
    lat              DECIMAL(10,6) NULL,
    lng              DECIMAL(10,6) NULL,
    speed_kph        DECIMAL(6,2)  NULL,
    payload_json     JSON          NULL,             -- kind-specific extra data
    position_id      INT UNSIGNED  NULL,
    created_on       DATETIME      NOT NULL,
    PRIMARY KEY (c0d),
    KEY idx_ge_device_ts (device_id, ts),
    KEY idx_ge_truck_ts  (truck_id, ts),
    KEY idx_ge_kind      (kind, ts),
    KEY idx_ge_trip      (trip_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS geofences (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id        INT UNSIGNED NOT NULL DEFAULT 1,
    name             VARCHAR(160) NOT NULL,
    kind             VARCHAR(30)  NOT NULL DEFAULT 'depot',
                                                    -- depot | customer_site | project_site | no_go |
                                                    -- route_corridor | fuel_station | weighbridge
    -- GeoJSON polygon stored verbatim; renderer / matcher parses at read time.
    -- For MVP we allow a simple center + radius circle format too:
    -- {"type":"circle","center":[lat,lng],"radius_m":250}
    geometry_json    JSON          NULL,
    center_lat       DECIMAL(10,6) NULL,             -- computed cache for fast prefilter
    center_lng       DECIMAL(10,6) NULL,
    radius_m         INT UNSIGNED  NULL,             -- for simple circle geofences
    color            VARCHAR(10)   NULL DEFAULT '#1E3A5F',
    active           TINYINT(1)   NOT NULL DEFAULT 1,
    customer_id      INT UNSIGNED NULL,              -- if this is a customer site
    trigger_alert    TINYINT(1)   NOT NULL DEFAULT 0, -- fire alerts on enter/exit
    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_gf_active (active, deleted),
    KEY idx_gf_kind (kind)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS geofence_events (
    c0d              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    geofence_id      INT UNSIGNED NOT NULL,
    device_id        INT UNSIGNED NOT NULL,
    truck_id         INT UNSIGNED NULL,
    trip_id          INT UNSIGNED NULL,
    ts               DATETIME     NOT NULL,
    kind             VARCHAR(10)  NOT NULL,          -- enter | exit
    dwell_minutes    INT UNSIGNED NULL,              -- computed on exit
    position_id      INT UNSIGNED NULL,
    PRIMARY KEY (c0d),
    KEY idx_gfe_geofence (geofence_id, ts),
    KEY idx_gfe_device   (device_id, ts),
    KEY idx_gfe_trip     (trip_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ═══════════════════════════════════════════════════════════════════════
-- Grants — GPS device registry is Admin + Ops + Fleet + Dispatcher scope.
-- Geofences same pattern. Ingestion endpoint is public (token-authed).
-- ═══════════════════════════════════════════════════════════════════════
INSERT IGNORE INTO user_groups_perms (user_group_id, controller_allowed, action_allowed, created_by, created_on) VALUES
-- GpsDevices
(10, 'GpsDevices', 'index',  1, NOW()), (10, 'GpsDevices', 'view',   1, NOW()),
(10, 'GpsDevices', 'add',    1, NOW()), (10, 'GpsDevices', 'save',   1, NOW()),
(10, 'GpsDevices', 'edit',   1, NOW()), (10, 'GpsDevices', 'update', 1, NOW()),
(10, 'GpsDevices', 'delete', 1, NOW()),
(20, 'GpsDevices', 'index',  1, NOW()), (20, 'GpsDevices', 'view',   1, NOW()),
(20, 'GpsDevices', 'add',    1, NOW()), (20, 'GpsDevices', 'save',   1, NOW()),
(20, 'GpsDevices', 'edit',   1, NOW()), (20, 'GpsDevices', 'update', 1, NOW()),
(22, 'GpsDevices', 'index',  1, NOW()), (22, 'GpsDevices', 'view',   1, NOW()),
(22, 'GpsDevices', 'add',    1, NOW()), (22, 'GpsDevices', 'save',   1, NOW()),
(22, 'GpsDevices', 'edit',   1, NOW()), (22, 'GpsDevices', 'update', 1, NOW()),
(21, 'GpsDevices', 'index',  1, NOW()), (21, 'GpsDevices', 'view',   1, NOW()),
(1,  'GpsDevices', 'index',  1, NOW()), (1,  'GpsDevices', 'view',   1, NOW()),
-- Geofences (Turn 2 will build the polygon editor; add grants now)
(10, 'Geofences', 'index',  1, NOW()), (10, 'Geofences', 'view',   1, NOW()),
(10, 'Geofences', 'add',    1, NOW()), (10, 'Geofences', 'save',   1, NOW()),
(10, 'Geofences', 'edit',   1, NOW()), (10, 'Geofences', 'update', 1, NOW()),
(10, 'Geofences', 'delete', 1, NOW()),
(20, 'Geofences', 'index',  1, NOW()), (20, 'Geofences', 'view',   1, NOW()),
(20, 'Geofences', 'add',    1, NOW()), (20, 'Geofences', 'save',   1, NOW()),
(20, 'Geofences', 'edit',   1, NOW()), (20, 'Geofences', 'update', 1, NOW()),
(21, 'Geofences', 'index',  1, NOW()), (21, 'Geofences', 'view',   1, NOW()),
(1,  'Geofences', 'index',  1, NOW()), (1,  'Geofences', 'view',   1, NOW());

-- GpsIngest is a public controller (no perm gating — token-based auth in the action)
-- so no grants needed. Router allows it by convention (checked in controller).
