-- 002_trips_viz_view.sql
-- Thin metadata view: one row per trip, joined to its device's cost_centre.
-- No path here — path is built per-trip inside the RPCs (003/004) so a
-- naive SELECT * never triggers an ST_MakeLine across the whole hypertable.
--
-- 'Unassigned' bucket: NULL or whitespace-only cost_centre is coalesced.

CREATE OR REPLACE VIEW public.trips_viz_v1 AS
SELECT
  t.id                                                       AS trip_id,
  t.imei,
  d.vehicle_name,
  d.vehicle_number,
  COALESCE(NULLIF(TRIM(d.cost_centre), ''), 'Unassigned')    AS cost_centre,
  t.start_time,
  t.end_time,
  t.distance_km,
  t.avg_speed_kmh,
  t.max_speed_kmh
FROM tracksolid.trips    t
JOIN tracksolid.devices  d ON d.imei = t.imei
WHERE t.start_time IS NOT NULL
  AND t.end_time   IS NOT NULL;

GRANT SELECT ON public.trips_viz_v1 TO viz_anon;