WITH last_work_order_services AS (
  SELECT DISTINCT ON (technician_trip_id) *
  FROM work_order_services
  ORDER BY technician_trip_id, position DESC
)

SELECT 
  tt.id AS technician_trip_id,
  t.id AS trip_id,
  tt.deleted_at AS deleted_at,
  CASE tt.status
    WHEN 'open' THEN
      CASE dispatch_date
        WHEN NULL THEN
          CASE
            WHEN t.start_date IS NOT NULL AND (NOW() at time zone 'utc') >= (t.start_date - INTERVAL '24' HOUR) THEN 0
            ELSE 1
          END
        ELSE
          CASE ws.technician_trip_id
            WHEN NULL THEN 
              CASE 
                WHEN t.end_date IS NOT NULL AND (NOW() at time zone 'utc') >= t.end_date THEN 2
                WHEN w.inspection_job IS FALSE AND (NOW() at time zone 'utc') >= (t.start_date + INTERVAL '20' MINUTE)  THEN 3
                ELSE 6
              END
            ELSE 6  
          END
      END
    WHEN 'in_progress' THEN
      CASE 
       WHEN (NOW() at time zone 'utc') >= (ws.check_in_time + INTERVAL '8' HOUR) THEN 4
       ELSE 5
      END
    WHEN 'completed' THEN 9
    WHEN 'unable_to_inspect' THEN 8
    WHEN 'must_return' THEN 7
  END AS extended_status_code
FROM technician_trips tt
  INNER JOIN trips t ON t.id = tt.trip_id
  INNER JOIN work_orders w ON w.id = t.work_order_id
  LEFT OUTER JOIN last_work_order_services ws ON ws.technician_trip_id = tt.id
