Autonomous Fleet Rerouting &
Geospatial Disruption Defense

SELECT 
    t.truck_id,
    at.trip_id,
    at.origin_city,
    at.destination_city,
    d.title AS disruption_event,
    d.severity
FROM active_trips at
JOIN trucks t ON at.truck_id = t.truck_id
JOIN location_disruptions d 
    ON ST_Intersects(at.full_route_geometry, d.affected_geofence)
WHERE at.status = 'ACTIVE'
  -- Temporal ETA Window Check: Only flag if arrival coincides with disruption
  AND (at.started_at + INTERVAL '12 hours') 
      BETWEEN d.start_time AND d.expected_end_time;

Spatial indexing on PostGIS geometries enables millisecond lookups across hundreds of simultaneous fleet trips without polling third-party APIs repeatedly.

# 1. Standard Request (Passes through Gwalior - BLOCKED)
GET /route/v1/driving/78.0081,27.1767;83.2185,17.6868?steps=true

# 2. SupplyNet Detour: Injects Mehgaon Waypoint (Coordinates: 78.3812, 26.4921)
GET /route/v1/driving/78.0081,27.1767;78.3812,26.4921;83.2185,17.6868?overview=full&geometries=geojson

# Response Extracts:
{
  "code": "Ok",
  "routes": [{
    "distance": 1842500,  // +18.5 km
    "duration": 104400,   // +25 mins
    "weight_name": "routability",
    "geometry": { "type": "LineString", "coordinates": [...] }
  }]
}

CREATE TABLE trip_city_checkpoints (
    checkpoint_id SERIAL PRIMARY KEY,
    trip_id INT REFERENCES active_trips(trip_id) ON DELETE CASCADE,
    sequence_order INT NOT NULL,
    city_name VARCHAR(100) NOT NULL,
    location GEOMETRY(Point, 4326) NOT NULL,
    status VARCHAR(20) DEFAULT 'PENDING' -- 'PASSED', 'NEXT', 'BYPASSED'
);

CREATE TABLE reroute_logs (
    log_id SERIAL PRIMARY KEY,
    trip_id INT REFERENCES active_trips(trip_id),
    affected_city VARCHAR(100),
    bypass_city VARCHAR(100),
    llm_reasoning TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);