fleet-platform/db/migrations/20260601000009_serve_fn_live_view_v2.sql

-- migrate:up

-- 12-colour palette (stable across runs via hashtext).
-- Used when a vehicle has a cost_centre; null cost_centre → default blue.
CREATE OR REPLACE FUNCTION serve._cost_centre_color(name text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
    SELECT CASE WHEN name IS NULL OR name = '' THEN '#3b82f6'
           ELSE
                (ARRAY[
                    '#10b981', '#3b82f6', '#f59e0b', '#ec4899',
                    '#8b5cf6', '#14b8a6', '#f97316', '#06b6d4',
                    '#a855f7', '#84cc16', '#e11d48', '#6366f1'
                ])[mod(abs(hashtext(name)), 12) + 1]
           END
$$;

DROP FUNCTION IF EXISTS serve.fn_live_view(jsonb);

CREATE OR REPLACE FUNCTION serve.fn_live_view(filters jsonb)
RETURNS jsonb
LANGUAGE plpgsql STABLE
AS $$
DECLARE
    fresh_window interval := COALESCE((filters->>'fresh_window')::interval, interval '24 hours');
    offline_after interval := COALESCE((filters->>'offline_after')::interval, interval '5 minutes');
    move_speed_kmh numeric := COALESCE((filters->>'move_speed_kmh')::numeric, 5);
    p_cost_centre text := filters->>'cost_centre';
    p_assigned_city text := filters->>'assigned_city';
    p_vehicle_numbers text[] := CASE
        WHEN filters ? 'vehicle_numbers'
        THEN ARRAY(SELECT jsonb_array_elements_text(filters->'vehicle_numbers'))
        ELSE NULL
    END;
    result jsonb;
BEGIN
    WITH candidates AS (
        SELECT
            lp.imei,
            lp.occurred_at,
            lp.geom,
            lp.speed_kmh,
            lp.direction_deg,
            lp.mc_type,
            lp.current_mileage_km,
            lp.gps_signal,
            lp.satellites,
            lp.device_name,
            lp.pos_type,
            d.device_type,
            d.activation_at,
            v.vehicle_id,
            v.plate,
            v.cost_centre,
            v.assigned_city
          FROM state.live_positions lp
          JOIN domain.devices d  ON d.imei = lp.imei
          JOIN domain.vehicles v ON v.vehicle_id = d.vehicle_id
         WHERE d.lifecycle = 'active'
           AND (p_cost_centre   IS NULL OR v.cost_centre   = p_cost_centre)
           AND (p_assigned_city IS NULL OR v.assigned_city = p_assigned_city)
           AND (p_vehicle_numbers IS NULL OR v.plate = ANY (p_vehicle_numbers))
    ),
    ranked AS (
        SELECT
            c.*,
            ROW_NUMBER() OVER (
                PARTITION BY c.vehicle_id
                ORDER BY
                    CASE c.device_type WHEN 'tracker' THEN 0 ELSE 1 END,
                    CASE WHEN c.occurred_at > now() - fresh_window THEN 0 ELSE 1 END,
                    c.occurred_at DESC,
                    c.activation_at DESC NULLS LAST
            ) AS rn
          FROM candidates c
    ),
    deduped AS (SELECT * FROM ranked WHERE rn = 1),
    enriched AS (
        SELECT
            d.*,
            CASE
                WHEN d.occurred_at <= now() - offline_after THEN 'offline'
                WHEN d.speed_kmh IS NOT NULL AND d.speed_kmh > move_speed_kmh THEN 'moving'
                ELSE 'parked'
            END AS operational_state,
            serve._cost_centre_color(d.cost_centre) AS cost_centre_color,
            EXTRACT(EPOCH FROM (now() - d.occurred_at))::int AS age_sec
          FROM deduped d
    ),
    summary AS (
        SELECT jsonb_build_object(
            'total_active', count(*),
            'moving',       count(*) FILTER (WHERE operational_state = 'moving'),
            'parked',       count(*) FILTER (WHERE operational_state = 'parked'),
            'offline',      count(*) FILTER (WHERE operational_state = 'offline'),
            'below_freshness_slo', count(*) FILTER (
                WHERE occurred_at <= now() - interval '90 seconds'
            ),
            'as_of', to_char(now() AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
        ) AS s
          FROM enriched
    ),
    features AS (
        SELECT COALESCE(jsonb_agg(
            jsonb_build_object(
                'type', 'Feature',
                'geometry', ST_AsGeoJSON(e.geom)::jsonb,
                'properties', jsonb_build_object(
                    'vehicle_id',         e.vehicle_id,
                    'plate',              e.plate,
                    'plate_short',        right(e.plate, 4),
                    'imei',               e.imei,
                    'device_type',        e.device_type,
                    'device_name',        e.device_name,
                    'mc_type',            e.mc_type,
                    'pos_type',           e.pos_type,
                    'cost_centre',        e.cost_centre,
                    'cost_centre_color',  e.cost_centre_color,
                    'assigned_city',      e.assigned_city,
                    'occurred_at',        to_char(e.occurred_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
                    'age_sec',            e.age_sec,
                    'speed_kmh',          e.speed_kmh,
                    'heading_deg',        e.direction_deg,
                    'gps_signal',         e.gps_signal,
                    'satellites',         e.satellites,
                    'current_mileage_km', e.current_mileage_km,
                    'operational_state',  e.operational_state,
                    'style_class',        'vehicle-' || e.operational_state,
                    'marker_color',       CASE WHEN e.operational_state = 'moving'
                                               THEN e.cost_centre_color
                                               ELSE '#9ca3af' END,
                    'show_arrow',         (e.operational_state = 'moving' AND e.direction_deg IS NOT NULL)
                )
            )
        ), '[]'::jsonb) AS feats
          FROM enriched e
    ),
    slo_block AS (
        SELECT COALESCE(jsonb_object_agg(
            metric,
            jsonb_build_object(
                'threshold', threshold,
                'current',   current_value,
                'status',    status
            )
        ), '{}'::jsonb) AS ss
          FROM slo.v_current_status
    )
    SELECT jsonb_build_object(
        'summary', (SELECT s FROM summary),
        'geojson', jsonb_build_object(
            'type', 'FeatureCollection',
            'features', (SELECT feats FROM features)
        ),
        'slo_status', (SELECT ss FROM slo_block)
    )
      INTO result;

    RETURN result;
END;
$$;

-- migrate:down

DROP FUNCTION IF EXISTS serve.fn_live_view(jsonb);
-- restore v1 (keep prior live behavior)
CREATE OR REPLACE FUNCTION serve.fn_live_view(filters jsonb)
RETURNS jsonb LANGUAGE plpgsql STABLE AS $$
BEGIN
    RETURN '{}'::jsonb;
END;
$$;
DROP FUNCTION IF EXISTS serve._cost_centre_color(text);