WITH technician_trip_statuses_with_priority AS (
  SELECT technician_trip_statuses.technician_trip_id as technician_trip_id, technician_trip_statuses.extended_status_code,t1.id AS trip_id,
  CASE
    WHEN extended_status_code = 6 THEN 0
    WHEN extended_status_code = 1 THEN 1
    WHEN extended_status_code = 3 THEN 4
    WHEN extended_status_code = 4 THEN 5
    WHEN extended_status_code = 7 THEN 6
    WHEN extended_status_code = 0 THEN 7
    WHEN extended_status_code = 5 THEN 8
    WHEN extended_status_code = 8 THEN 2
    WHEN extended_status_code = 2 THEN 3
    ELSE 0
  END AS priority,
  CASE
    WHEN extended_status_code = 0 THEN 6
    WHEN extended_status_code = 1 THEN 1
    WHEN extended_status_code = 2 THEN 2
    WHEN extended_status_code = 3 THEN 3
    WHEN extended_status_code = 4 THEN 4
    WHEN extended_status_code = 5 THEN 7
    WHEN extended_status_code = 6 THEN 0
    WHEN extended_status_code = 7 THEN 5
    WHEN extended_status_code = 8 THEN 8
    ELSE
      CASE
        WHEN t1.start_date IS NULL THEN 6
        WHEN (NOW() at time zone 'utc') > (t1.start_date + INTERVAL '24' HOUR) THEN 0
        ELSE 6
      END
  END AS trip_status
  FROM technician_trip_statuses FULL OUTER JOIN technician_trips t2 ON t2.id = technician_trip_statuses.technician_trip_id
  FULL OUTER JOIN trips t1 ON t1.id = t2.trip_id
)

SELECT trip_id,trip_status AS extended_status_code,priority 
FROM technician_trip_statuses_with_priority FULL OUTER JOIN trips ON trips.id = technician_trip_statuses_with_priority.trip_id 
WHERE priority = (SELECT MIN(priority) from technician_trip_statuses_with_priority where technician_trip_statuses_with_priority.trip_id = trips.id);