WITH time_interval AS (
  SELECT
    EXTRACT(HOUR FROM interval_timestamp) AS hour,
    DATE(interval_timestamp) AS interval_date,
    CASE 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='00:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'01:00:00') THEN '00:00-01:00'
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='01:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'02:00:00') THEN '01:00-02:00'
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='02:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'03:00:00') THEN '02:00-03:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='03:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'04:00:00') THEN '03:00-04:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='04:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'05:00:00') THEN '04:00-05:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='05:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'06:00:00') THEN '05:00-06:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='06:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'07:00:00') THEN '06:00-07:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='07:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'08:00:00') THEN '07:00-08:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='08:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'09:00:00') THEN '08:00-09:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='09:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'10:00:00') THEN '09:00-10:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='10:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'11:00:00') THEN '10:00-11:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='11:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'12:00:00') THEN '11:00-12:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='12:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'13:00:00') THEN '12:00-13:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='13:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'14:00:00') THEN '13:00-14:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='14:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'15:00:00') THEN '14:00-15:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='15:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'16:00:00') THEN '15:00-16:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='16:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'17:00:00') THEN '16:00-17:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='17:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'18:00:00') THEN '17:00-18:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='18:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'19:00:00') THEN '18:00-19:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='19:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'20:00:00') THEN '19:00-20:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='20:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'21:00:00') THEN '20:00-21:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='21:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'22:00:00') THEN '21:00-22:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='22:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<'23:00:00') THEN '22:00-23:00' 
      WHEN(EXTRACT(TIME FROM interval_timestamp)>='23:00:00') AND (EXTRACT(TIME FROM interval_timestamp)<='23:59:59') THEN '23:00-00:00' 
    END AS call_interval
  FROM
    `telefoni-develop-9.telefoni_develop_9_ey_r_appspot_com.INTERVAL_SAATLIK`
  WHERE
    interval_timestamp BETWEEN '2023-12-11 01:00:00' AND '2023-12-20 23:00:00'
),
asl AS (
  SELECT
    activecallkey AS activecallkey,
    SUM(duration) AS total_duration
  FROM
    `telefoni-develop-9.develop_9_alo_tech_com.AGENT_STATUS_LOG`
  WHERE
    start BETWEEN '2023-12-11 01:00:00' AND '2023-12-20 23:00:00'
    AND
    status IN ('talking','ringing','hold','aftercallwork','wrapup')
    AND
    namespace ='develop-9.alo-tech.com'
  GROUP BY 
    activecallkey
),
cdr AS (
  SELECT
    activecallkey AS activecallkey,
    answered AS answered,
    namespace AS namespace,
    calldate AS calldate,
    DATE(calldate) AS call_date,
    inbound AS inbound,
    duration AS duration,
    queue.wrapupduration AS queue_wrapupduration,
    queue.key AS queue_key,
    CASE 
      WHEN(EXTRACT(TIME FROM calldate)>='00:00:00') AND (EXTRACT(TIME FROM calldate)<'01:00:00') THEN '00:00-01:00'
      WHEN(EXTRACT(TIME FROM calldate)>='01:00:00') AND (EXTRACT(TIME FROM calldate)<'02:00:00') THEN '01:00-02:00'
      WHEN(EXTRACT(TIME FROM calldate)>='02:00:00') AND (EXTRACT(TIME FROM calldate)<'03:00:00') THEN '02:00-03:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='03:00:00') AND (EXTRACT(TIME FROM calldate)<'04:00:00') THEN '03:00-04:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='04:00:00') AND (EXTRACT(TIME FROM calldate)<'05:00:00') THEN '04:00-05:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='05:00:00') AND (EXTRACT(TIME FROM calldate)<'06:00:00') THEN '05:00-06:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='06:00:00') AND (EXTRACT(TIME FROM calldate)<'07:00:00') THEN '06:00-07:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='07:00:00') AND (EXTRACT(TIME FROM calldate)<'08:00:00') THEN '07:00-08:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='08:00:00') AND (EXTRACT(TIME FROM calldate)<'09:00:00') THEN '08:00-09:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='09:00:00') AND (EXTRACT(TIME FROM calldate)<'10:00:00') THEN '09:00-10:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='10:00:00') AND (EXTRACT(TIME FROM calldate)<'11:00:00') THEN '10:00-11:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='11:00:00') AND (EXTRACT(TIME FROM calldate)<'12:00:00') THEN '11:00-12:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='12:00:00') AND (EXTRACT(TIME FROM calldate)<'13:00:00') THEN '12:00-13:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='13:00:00') AND (EXTRACT(TIME FROM calldate)<'14:00:00') THEN '13:00-14:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='14:00:00') AND (EXTRACT(TIME FROM calldate)<'15:00:00') THEN '14:00-15:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='15:00:00') AND (EXTRACT(TIME FROM calldate)<'16:00:00') THEN '15:00-16:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='16:00:00') AND (EXTRACT(TIME FROM calldate)<'17:00:00') THEN '16:00-17:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='17:00:00') AND (EXTRACT(TIME FROM calldate)<'18:00:00') THEN '17:00-18:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='18:00:00') AND (EXTRACT(TIME FROM calldate)<'19:00:00') THEN '18:00-19:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='19:00:00') AND (EXTRACT(TIME FROM calldate)<'20:00:00') THEN '19:00-20:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='20:00:00') AND (EXTRACT(TIME FROM calldate)<'21:00:00') THEN '20:00-21:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='21:00:00') AND (EXTRACT(TIME FROM calldate)<'22:00:00') THEN '21:00-22:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='22:00:00') AND (EXTRACT(TIME FROM calldate)<'23:00:00') THEN '22:00-23:00' 
      WHEN(EXTRACT(TIME FROM calldate)>='23:00:00') AND (EXTRACT(TIME FROM calldate)<='23:59:59') THEN '23:00-00:00' 
    END AS call_interval
  FROM
    `telefoni-develop-9.develop_9_alo_tech_com.CDR`
  WHERE
    calldate BETWEEN '2023-12-11 01:00:00' AND '2023-12-20 23:00:00'
      AND
          queue.key IN ("ahRofnRlbGVmb25pLWRldmVsb3AtOXINCxIFUXVldWUiAjEwDKIBFmRldmVsb3AtOS5hbG8tdGVjaC5jb20","ahRofnRlbGVmb25pLWRldmVsb3AtOXISCxIFUXVldWUYgICAmIbjiQkMogEWZGV2ZWxvcC05LmFsby10ZWNoLmNvbQ","ahRofnRlbGVmb25pLWRldmVsb3AtOXISCxIFUXVldWUYgICAhLrJngoMogEWZGV2ZWxvcC05LmFsby10ZWNoLmNvbQ"
    )
      AND
      namespace ='develop-9.alo-tech.com'
    AND
    activecallkey IS NOT NULL
),
report_level AS (
  SELECT
    time_interval.interval_date AS interval_date,
    time_interval.call_interval AS call_interval,
    (COUNT(DISTINCT CASE WHEN inbound=TRUE AND answered = TRUE THEN cdr.activecallkey ELSE NULL END) + COUNT(DISTINCT CASE WHEN inbound=FALSE THEN cdr.activecallkey ELSE NULL END)) AS total_answered_call_count,
    SUM(asl.total_duration) AS total_status_duration,
    SUM(CASE WHEN inbound=TRUE AND answered=TRUE THEN asl.total_duration ELSE 0 END) + SUM(CASE WHEN inbound=TRUE AND answered=TRUE THEN cdr.queue_wrapupduration ELSE 0 END) AS total_inbound_aht_duration,
    SUM(CASE WHEN inbound=FALSE THEN asl.total_duration ELSE 0 END) + SUM(CASE WHEN inbound=FALSE THEN cdr.queue_wrapupduration ELSE 0 END) AS total_outbound_aht_duration,
    COUNT(DISTINCT CASE WHEN inbound=TRUE AND answered=TRUE THEN cdr.activecallkey END) AS total_inbound_call_count,
    COUNT(DISTINCT CASE WHEN inbound=FALSE THEN cdr.activecallkey END) AS total_outbound_call_count
  FROM
    time_interval
  LEFT JOIN
    cdr
  ON
    time_interval.interval_date = cdr.call_date
    AND 
    time_interval.call_interval = cdr.call_interval
  LEFT JOIN
    asl
  ON
    cdr.activecallkey = asl.activecallkey
  GROUP BY
    interval_date,
    call_interval
)
SELECT
  interval_date AS interval_date,
  call_interval AS call_interval,
  total_answered_call_count AS total_answered_call_count,
  total_inbound_call_count AS total_inbound_call_count,
  total_outbound_call_count AS total_outbound_call_count,
  IFNULL(ROUND(total_inbound_aht_duration,2),0) AS total_inbound_aht_duration,
  IFNULL(ROUND(total_outbound_aht_duration,2),0)  AS total_outbound_aht_duration,
  IFNULL(ROUND(SAFE_DIVIDE((total_inbound_aht_duration + total_outbound_aht_duration),(total_inbound_call_count + total_outbound_call_count)),2),0) AS total_aht,
  IFNULL(ROUND(SAFE_DIVIDE(total_inbound_aht_duration,total_inbound_call_count),2),0) AS inbound_aht,
  IFNULL(ROUND(SAFE_DIVIDE(total_outbound_aht_duration,total_outbound_call_count),2),0) AS outbound_aht
FROM
  report_level
ORDER BY
  interval_date DESC,
  call_interval ASC