CREATE COLLECTION vehicles (
  vehicle_id VARCHAR PRIMARY KEY,
  location   GEOMETRY SPATIAL_INDEX,
  heading    FLOAT,
  speed_kmh  FLOAT,
  updated_at TIMESTAMP
) WITH (engine='spatial');

CREATE COLLECTION zones (
  zone_id   VARCHAR PRIMARY KEY,
  name      VARCHAR,
  zone_type VARCHAR,
  boundary  GEOMETRY SPATIAL_INDEX,
  active    BOOLEAN
) WITH (engine='spatial');

-- Update a vehicle's live position on each GPS ping
INSERT INTO vehicles (vehicle_id, location, heading, speed_kmh, updated_at)
VALUES ($vehicle_id, ST_Point($lon, $lat), $heading, $speed, now())
ON CONFLICT (vehicle_id) DO UPDATE SET
  location   = EXCLUDED.location,
  heading    = EXCLUDED.heading,
  speed_kmh  = EXCLUDED.speed_kmh,
  updated_at = EXCLUDED.updated_at;

-- All vehicles within 5 km of a point, recently seen
SELECT
  vehicle_id,
  ST_Distance(location, ST_Point($lon, $lat)) AS distance_m,
  speed_kmh,
  heading
FROM vehicles
WHERE ST_DWithin(location, ST_Point($lon, $lat), 5000)
  AND updated_at >= now() - INTERVAL '2 minutes'
ORDER BY distance_m;

-- Which active zone contains this delivery point?
SELECT z.zone_id, z.name, z.zone_type
FROM zones z
WHERE ST_Contains(z.boundary, ST_Point($delivery_lon, $delivery_lat))
  AND z.active = true;

-- Deliveries per zone in the last day (spatial join)
SELECT z.name, count(d.id) AS deliveries
FROM deliveries d
JOIN zones z ON ST_Contains(z.boundary, d.delivery_point)
WHERE d.delivered_at >= now() - INTERVAL '1 day'
GROUP BY z.name
ORDER BY deliveries DESC;

CREATE COLLECTION position_history (
  ts          TIMESTAMP TIME_KEY,
  vehicle_id  VARCHAR,
  location    GEOMETRY SPATIAL_INDEX,
  speed_kmh   FLOAT,
  odometer_km FLOAT
) WITH (engine='timeseries', partition_by='1d', retention='365d');

INSERT INTO position_history (ts, vehicle_id, location, speed_kmh, odometer_km)
VALUES (now(), $vehicle_id, ST_Point($lon, $lat), $speed, $odometer);

-- Reconstruct a vehicle's route for a shift
SELECT ts, location, speed_kmh
FROM position_history
WHERE vehicle_id = $vehicle_id
  AND ts BETWEEN $shift_start AND $shift_end
ORDER BY ts;

-- Average speed per hour to surface traffic patterns
SELECT time_bucket('1h', ts) AS hour, vehicle_id, avg(speed_kmh) AS avg_speed
FROM position_history
WHERE ts >= now() - INTERVAL '7 days'
GROUP BY hour, vehicle_id;

CREATE COLLECTION road_network;   -- holds the nodes; edges are an overlay

-- Each road segment is a weighted edge
GRAPH INSERT EDGE IN 'road_network' FROM $node_a TO $node_b TYPE 'road'
  PROPERTIES { distance_m: 120, travel_time_s: 14 };

-- Shortest hop path between two nodes along 'road' edges
GRAPH PATH FROM $depot_node TO $dest_node MAX_DEPTH 50 LABEL 'road';

-- Weighted shortest paths from a depot to every reachable node (Dijkstra)
GRAPH ALGO SSSP ON road_network FROM $depot_node;

WITH zone_deliveries AS (
  SELECT
    d.id            AS delivery_id,
    d.address_point,
    z.zone_id,
    z.depot_point
  FROM deliveries d
  JOIN zones z ON ST_Contains(z.boundary, d.address_point)
  WHERE d.scheduled_date = current_date
    AND d.status = 'pending'
)
SELECT
  zone_id,
  row_number() OVER (
    PARTITION BY zone_id
    ORDER BY ST_Distance(address_point, depot_point)
  ) AS visit_order,
  delivery_id,
  address_point
FROM zone_deliveries
ORDER BY zone_id, visit_order;

-- The rider app subscribes to one vehicle's position stream
LIVE SELECT vehicle_id, location, speed_kmh FROM vehicles
WHERE vehicle_id = $vehicle_id;

-- On each GPS ping the subscriber receives the new row, recomputes the ETA
-- against the routing graph, and pushes it to the app.