WITH first_check_in_log AS (
  SELECT technician_trip_id, tradesmen_id, created_at AS check_in_time
  FROM work_order_logs
  WHERE created_at in (select MIN(created_at) from work_order_logs GROUP BY technician_trip_id) and log_type='check_in'
  GROUP BY technician_trip_id, tradesmen_id,created_at
  ORDER BY created_at ASC
 ), last_check_out_log AS (
  SELECT technician_trip_id, tradesmen_id, created_at AS check_out_time
  FROM work_order_logs
  WHERE created_at in (select MAX(created_at) from work_order_logs GROUP BY technician_trip_id) and log_type='check_out'
  GROUP BY technician_trip_id, tradesmen_id,created_at
  ORDER BY created_at ASC
 )

SELECT t1.id AS technician_trip_id,
  CASE
    WHEN (t2.start_date IS NULL OR t2.end_date IS NULL) THEN 0
    WHEN ((NOW() at time zone 'utc') < (t2.start_date - INTERVAL '24' HOUR)) THEN
      CASE
        WHEN t1.dispatch_date IS NULL THEN 0
        ELSE 5
      END
    WHEN ((NOW() at time zone 'utc') >= (t2.start_date - INTERVAL '24' HOUR)) and ((NOW() at time zone 'utc') <= t2.start_date) THEN
      CASE
        WHEN t1.dispatch_date IS NULL THEN 7
        ELSE 5
      END
    WHEN ((NOW() at time zone 'utc') >= t2.start_date) and ((NOW() at time zone 'utc') <= (t2.end_date + INTERVAL '20' MINUTE)) THEN
      CASE
        WHEN (s1.check_in_time IS NOT NULL AND s2.check_out_time IS NULL) THEN
           CASE
             WHEN ((s1.check_in_time > t2.start_date) and (s1.check_in_time < (t2.start_date + INTERVAL '20' MINUTE))) THEN 3
             WHEN ((s1.check_in_time > (t2.start_date + INTERVAL '20' MINUTE))) THEN 1
           END
        WHEN (s1.check_in_time IS NOT NULL AND s2.check_out_time IS NOT NULL) THEN
          CASE
            WHEN (((s1.check_in_time > t2.start_date) and (s1.check_in_time < (t2.start_date + INTERVAL '20' MINUTE))) and s2.check_out_time >= t2.end_date and s2.check_out_time <= (t2.end_date + INTERVAL '20' MINUTE)) THEN 4
            ELSE 8
          END
        WHEN s1.check_in_time IS NULL THEN
           CASE
             WHEN ((NOW() at time zone 'utc') > (t2.end_date + INTERVAL '20' MINUTE)) THEN 1
             ELSE 5
           END
      END
    WHEN ((NOW() at time zone 'utc') > (t2.end_date + INTERVAL '20' MINUTE)) THEN
      CASE
        WHEN (s1.check_in_time IS NOT NULL AND s2.check_out_time IS NOT NULL) THEN
           CASE
              WHEN (((s1.check_in_time > t2.start_date) and (s1.check_in_time < (t2.start_date + INTERVAL '20' MINUTE))) and s2.check_out_time >= t2.end_date and s2.check_out_time <= (t2.end_date + INTERVAL '20' MINUTE)) THEN 4
              WHEN s2.check_out_time < t2.end_date THEN 8
              WHEN s1.check_in_time > (t2.start_date  + INTERVAL '20' MINUTE) THEN 1
              WHEN s2.check_out_time > (t2.end_date + INTERVAL '20' MINUTE) THEN
                CASE
                   WHEN (s1.check_in_time < t2.start_date + INTERVAL '20' MINUTE) THEN 3
                   WHEN (s1.check_in_time > t2.start_date) and (s1.check_in_time < (t2.start_date + INTERVAL '20' MINUTE)) THEN 3
                   WHEN s1.check_in_time > t2.start_date + INTERVAL '20' MINUTE THEN 1
                END
           END
        WHEN (s1.check_in_time IS NOT NULL AND s2.check_out_time IS NULL) THEN 2
        WHEN s1.check_in_time IS NULL THEN 6
      END
  END AS extended_status_code
FROM first_check_in_log s1
FULL OUTER JOIN technician_trips t1 ON t1.id = s1.technician_trip_id AND t1.deleted_at IS NULL
FULL OUTER JOIN last_check_out_log s2 ON t1.id = s2.technician_trip_id AND t1.deleted_at IS NULL
INNER JOIN trips t2 ON t2.id = t1.trip_id AND t2.deleted_at IS NULL;
