tracksolid_timescale_grafana_prod/09_trips_enrichment.sql

-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Migration 09 — tracksolid.trips Enrichment
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- The polling endpoint jimi.device.track.mileage does not return start/end
-- coordinates, fuel, idle, or trip sequence. This migration adds the columns
-- needed to enrich every poll-ingested trip from data we already have:
--   • start/end coordinates and full route polyline reconstructed from
--     position_history at insert time (see ingest_movement_rev.py::poll_trips)
--   • reverse-geocoded human-readable addresses (Nominatim)
--   • denormalised vehicle_plate so trip displays don't need a join
--   • waypoint count for audit / data-quality checks
--
-- Adds a v_trips_enriched view exposing daily_seq (Nth trip for IMEI on this
-- Africa/Nairobi date) — replaces reliance on the device-supplied trip_seq
-- which is only populated when the rarely-firing /pushtripreport webhook
-- delivers a payload.
--
-- Run after migration 08. Safe to re-run (ADD COLUMN IF NOT EXISTS,
-- CREATE INDEX IF NOT EXISTS, CREATE OR REPLACE VIEW).
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

BEGIN;

-- ── 1. New columns on tracksolid.trips ──────────────────────────────────────

ALTER TABLE tracksolid.trips
    ADD COLUMN IF NOT EXISTS route_geom      geometry(LineString, 4326),
    ADD COLUMN IF NOT EXISTS start_address   TEXT,
    ADD COLUMN IF NOT EXISTS end_address     TEXT,
    ADD COLUMN IF NOT EXISTS vehicle_plate   TEXT,
    ADD COLUMN IF NOT EXISTS waypoints_count INTEGER;

COMMENT ON COLUMN tracksolid.trips.route_geom IS
    'Full GPS route polyline built at ingest from position_history points '
    'where gps_time BETWEEN start_time AND end_time. NULL when fewer than '
    '2 fixes are available for the trip window.';
COMMENT ON COLUMN tracksolid.trips.start_address IS
    'Reverse-geocoded human-readable address near start_geom (Nominatim). '
    'NULL on lookup failure; address is best-effort, not authoritative.';
COMMENT ON COLUMN tracksolid.trips.end_address IS
    'Reverse-geocoded human-readable address near end_geom (Nominatim). '
    'NULL on lookup failure; address is best-effort, not authoritative.';
COMMENT ON COLUMN tracksolid.trips.vehicle_plate IS
    'Denormalised tracksolid.devices.vehicle_number cached at trip-insert '
    'time. Avoids a join for trip displays; refreshed only on next ingest.';
COMMENT ON COLUMN tracksolid.trips.waypoints_count IS
    'Number of position_history fixes that contributed to route_geom. '
    'Audit aid: 0 or 1 means route_geom is NULL or degenerate.';

-- ── 2. Spatial indexes for replay / map queries ─────────────────────────────

CREATE INDEX IF NOT EXISTS idx_trips_route_geom
    ON tracksolid.trips USING GIST (route_geom);
CREATE INDEX IF NOT EXISTS idx_trips_start_geom
    ON tracksolid.trips USING GIST (start_geom);
CREATE INDEX IF NOT EXISTS idx_trips_end_geom
    ON tracksolid.trips USING GIST (end_geom);

-- ── 3. v_trips_enriched view ────────────────────────────────────────────────
-- Adds trip_date_eat (Africa/Nairobi local date) and daily_seq (Nth trip on
-- that date for the IMEI) without depending on the device-supplied trip_seq.

CREATE OR REPLACE VIEW tracksolid.v_trips_enriched AS
SELECT
    t.*,
    (t.start_time AT TIME ZONE 'Africa/Nairobi')::date AS trip_date_eat,
    ROW_NUMBER() OVER (
        PARTITION BY t.imei, (t.start_time AT TIME ZONE 'Africa/Nairobi')::date
        ORDER BY t.start_time
    ) AS daily_seq
FROM tracksolid.trips t;

COMMENT ON VIEW tracksolid.v_trips_enriched IS
    'tracksolid.trips with computed daily_seq (Nth trip per IMEI per local '
    'Africa/Nairobi day) and trip_date_eat. Replaces reliance on the '
    'device-supplied trip_seq column, which is NULL for poll-ingested trips.';

-- ── 4. Read access for grafana_ro ───────────────────────────────────────────

GRANT SELECT ON tracksolid.v_trips_enriched TO grafana_ro;

COMMIT;