SELECT
city,
route_name,
route_distance_km,
SUM(NO_OF_RIDES)NO_OF_RIDES,
SUM(bus_seats* NO_OF_RIDES) Seat_Capacity,
Ride_type,
ride_week_number ,
ride_yearmo,
station_id,
Station_SEQU,
station_name,
longitude,
latitude,
-- coalesce(sum(total_bookings),0)total_bookings ,
-- coalesce(sum(completed_bookings),0)completed_bookings ,
-- coalesce(sum(cancelled_bookings),0)cancelled_bookings ,
-- coalesce(sum(missed_bookings),0)missed_bookings ,
MAX(PICKUP_Flag)AS PICKUP_Flag,
COALESCE(SUM(PICKUP_total_bookings),0)PICKUP_total_bookings ,
COALESCE(SUM(PICKUP_completed_bookings),0)PICKUP_completed_bookings ,
COALESCE(SUM(PICKUP_cancelled_bookings),0)PICKUP_cancelled_bookings ,
COALESCE(SUM(PICKUP_missed_bookings),0)PICKUP_missed_bookings ,

MAX(DropOFF_FLag) AS DropOFF_FLag,
COALESCE(SUM(DropOFF_total_bookings),0)DropOFF_total_bookings ,
COALESCE(SUM(DropOFF_completed_bookings),0)DropOFF_completed_bookings ,
COALESCE(SUM(DropOFF_cancelled_bookings),0)DropOFF_cancelled_bookings ,
COALESCE(SUM(DropOFF_missed_bookings),0)DropOFF_missed_bookings ,

--max(case when PICKUP_Flag='PICKUP Flag' and DropOFF_FLag='DropOFF FLag' then PICKUP_and_DropOFF_FLAG end ) as PICKUP_and_DropOFF_FLAG,
--max(case when PICKUP_Flag is null and DropOFF_FLag is null then 'Has no bookings' end)as Has_no_bookings,
 ROW_NUMBER () OVER (partition BY route_name,ride_yearmo,ride_week_number ORDER BY Station_SEQU ) rnk
 FROM
(
SELECT
city,
route_name,
route_distance_km,
COUNT(DISTINCT Ride_ID) NO_OF_RIDES,
-- (bus_seats*count(distinct Ride_ID)) Seat_Capacity,
 Ride_type,
-- Ride_status,
--  backup_flag,
-- ride_date,
-- ride_time,
ride_week_number ,
-- ride_month ,
--  ride_year ,
ride_yearmo,
-- cast(DATETime(g.booking_date, "Africa/Nairobi") as date) as booking_date,
-- cast(DATETime(g.booking_date, "Africa/Nairobi") as time) as booking_time,
station_id,
off+1 Station_SEQU,
station_name,
longitude,
latitude,
COALESCE(SUM(total_bookings),0)total_bookings ,
COALESCE(SUM(completed_bookings),0)completed_bookings ,
COALESCE(SUM(cancelled_bookings),0)cancelled_bookings ,
COALESCE(SUM(missed_bookings),0)missed_bookings ,
COALESCE(SUM(PICKUP_total_bookings),0)PICKUP_total_bookings ,
COALESCE(SUM(PICKUP_completed_bookings),0)PICKUP_completed_bookings ,
COALESCE(SUM(PICKUP_cancelled_bookings),0)PICKUP_cancelled_bookings ,
COALESCE(SUM(PICKUP_missed_bookings),0)PICKUP_missed_bookings ,

COALESCE(SUM(DropOFF_total_bookings),0)DropOFF_total_bookings ,
COALESCE(SUM(DropOFF_completed_bookings),0)DropOFF_completed_bookings ,
COALESCE(SUM(DropOFF_cancelled_bookings),0)DropOFF_cancelled_bookings ,
COALESCE(SUM(DropOFF_missed_bookings),0)DropOFF_missed_bookings ,

 MAX(CASE WHEN pick_up_station_id IS NOT NULL AND drop_off_station_id IS NOT NULL THEN 'PICKUP and DropOFF' END ) AS PICKUP_and_DropOFF_FLAG,
 MAX(CASE WHEN pick_up_station_id IS NOT NULL THEN 'PICKUP Flag' END )AS PICKUP_Flag,
  MAX(CASE WHEN pick_up_station_id IS  NULL AND drop_off_station_id IS NOT NULL THEN 'DropOFF FLag' END) AS DropOFF_FLag,
  MAX(CASE WHEN pick_up_station_id IS  NULL AND drop_off_station_id IS  NULL THEN 'Has no bookings' END) AS Has_no_bookings,
-- pick_up_station_name,
--  pick_up_station_longitude,
--  pick_up_station_latitude,


-- pick_up_schedule,
--  pick_up_arrivals,
--  pick_up_departures,
--  pick_up_station_status,
--  pick_up_station_number,
--  drop_off_station_id,
--  drop_off_station_name,
--  drop_off_station_longitude,
--  drop_off_station_latitude,
-- drop_off_schedule,
--  drop_off_arrivals,
--  drop_off_departures,
-- drop_off_station_status,
--  drop_off_station_number,
--  bus_type,
-- bus_make,
 bus_seats
--  row_number () over (partition by route_name,ride_yearmo,ride_week_number order by OFF ) rnk


FROM
(

WITH ride_stations AS
(
SELECT
ci.name AS city,
routes.name AS route_name,
routes.total_distance/1000 AS route_distance_km,
g.booking_id,
g.booking_status,
rides._id Ride_ID,
rides.TYPE AS Ride_type,
rides.STATUS AS Ride_status,
CASE WHEN rides.backup IS NOT NULL THEN 'Y' ELSE 'N' END AS backup_flag,
CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS DATE) AS ride_date,
CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS TIME) AS ride_time,
EXTRACT(week FROM CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS DATE ))+1 AS ride_week_number ,
EXTRACT(MONTH FROM CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS DATE )) AS ride_month ,
EXTRACT(YEAR FROM CAST(DATETime(rideS.DATE ,"Africa/Nairobi") AS DATE )) AS ride_year ,
FORMAT_DATE('%Y%m', CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS DATE )) AS ride_yearmo,
CAST(DATETime(g.booking_date, "Africa/Nairobi") AS DATE) AS booking_date,
CAST(DATETime(g.booking_date, "Africa/Nairobi") AS TIME) AS booking_time,
station_id,
station_name,
longitude,
latitude,
d.off,
g.total_bookings ,
g.completed_bookings ,
g.cancelled_bookings ,
g.missed_bookings ,

COALESCE((CASE WHEN g.pick_up = d.station_id THEN g.total_bookings END),0)PICKUP_total_bookings ,
COALESCE((CASE WHEN g.pick_up = d.station_id THEN g.completed_bookings END),0)PICKUP_completed_bookings ,
COALESCE((CASE WHEN g.pick_up = d.station_id THEN g.cancelled_bookings END),0)PICKUP_cancelled_bookings ,
COALESCE((CASE WHEN g.pick_up = d.station_id THEN g.missed_bookings END),0)PICKUP_missed_bookings ,


COALESCE((CASE WHEN g.drop_off = d.station_id THEN g.total_bookings END),0)DropOFF_total_bookings ,
COALESCE((CASE WHEN g.drop_off = d.station_id THEN g.completed_bookings END),0)DropOFF_completed_bookings ,
COALESCE((CASE WHEN g.drop_off = d.station_id THEN g.cancelled_bookings END),0)DropOFF_cancelled_bookings ,
COALESCE((CASE WHEN g.drop_off = d.station_id THEN g.missed_bookings END),0)DropOFF_missed_bookings ,


MAX(CASE WHEN g.pick_up = d.station_id THEN pick_up END) AS pick_up_station_id,
MAX(CASE WHEN g.pick_up = d.station_id THEN station_name END) AS pick_up_station_name,
MAX(CASE WHEN g.pick_up = d.station_id THEN longitude END )AS pick_up_station_longitude,
MAX(CASE WHEN g.pick_up = d.station_id THEN latitude END )AS pick_up_station_latitude,


MAX(CASE WHEN g.pick_up = d.station_id THEN (DATETime(A.schedule ,"Africa/Nairobi")  )  END) AS pick_up_schedule,
MAX(CASE WHEN g.pick_up = d.station_id THEN (DATETime(B.arrivals,"Africa/Nairobi")  )  END )AS pick_up_arrivals,
MAX(CASE WHEN g.pick_up = d.station_id THEN (DATETime(C.departures,"Africa/Nairobi")   )  END )AS pick_up_departures,
MAX(CASE WHEN g.pick_up = d.station_id THEN IF(time_DIFF(TIME( B.arrivals),  TIME(A.schedule), MINUTE) <=0 ,"Early","Late")  END )AS pick_up_station_status,
MAX(CASE WHEN g.pick_up = d.station_id THEN A.rowNum END) AS pick_up_station_number,


MAX(CASE WHEN g.drop_off = d.station_id THEN drop_off END )AS drop_off_station_id,
MAX(CASE WHEN g.drop_off = d.station_id THEN station_name END) AS drop_off_station_name,
MAX(CASE WHEN g.drop_off = d.station_id THEN longitude END) AS drop_off_station_longitude,
MAX(CASE WHEN g.drop_off = d.station_id THEN latitude END) AS drop_off_station_latitude,


MAX(CASE WHEN g.drop_off = d.station_id THEN (DATETime(A.schedule ,"Africa/Nairobi") )  END) AS drop_off_schedule,
MAX(CASE WHEN g.drop_off = d.station_id THEN (DATETime(B.arrivals,"Africa/Nairobi") )   END) AS drop_off_arrivals,
MAX(CASE WHEN g.drop_off = d.station_id THEN (DATETime(C.departures,"Africa/Nairobi") )    END) AS drop_off_departures,
MAX(CASE WHEN g.drop_off = d.station_id THEN IF(time_DIFF(TIME( B.arrivals),  TIME(A.schedule), MINUTE) <=0 ,"Early","Late")   END) AS drop_off_station_status,
MAX(CASE WHEN g.drop_off = d.station_id THEN A.rowNum END) AS drop_off_station_number,


-- routes.name as route_name,
-- routes.total_distance/1000 as route_distance_km,
-- D.station_id,
-- D.station_name,
-- longitude ,
-- latitude,
bustype.name AS bus_type,
bustype.make AS bus_make,
bustype.seats AS bus_seats
-- E.total_bookings Pickup_total_bookings,
-- E.completed_bookings Pickup_completed_bookings,
-- E.cancelled_bookings Pickup_cancelled_bookings,
-- E.missed_bookings Pickup_missed_bookings,
-- F.total_bookings Dropoff_total_bookings,
-- F.completed_bookings Dropoff_completed_bookings,
-- F.cancelled_bookings Dropoff_cancelled_bookings,
-- F.missed_bookings Dropoff_missed_bookings,

-- case when e.pick_up is not null then 'Y' else 'N' end pick_up_flag ,
-- case when f.drop_off is not null then 'Y' else 'N' end drop_off_flag ,
-- e.pick_up as pick,
-- f.drop_off as drop,
-- round((
-- sum(coalesce(g.completed_bookings,0) + coalesce(g.missed_bookings,0)) over(partition by Ride_ID) / nullif(max(seats) over(partition by Ride_ID),0) ) * 100,2) as ride_utitlization,



-- DATETime(A.schedule ,"Asia/Omsk") as schedule,
-- DATETime(B.arrivals,"Asia/Omsk") as arrivals,
-- DATETime(C.departures,"Asia/Omsk") as departures,
-- if(B.arrivals<A.schedule,"Early","Late") as station_status,
-- A.rowNum as station_number

FROM (SELECT * FROM `swvl-161014.swvl.rides` WHERE
-- status ='completed'
--and
CAST(DATE AS DATE ) BETWEEN   CAST('2019-10-27' AS DATE) AND CAST('2019-11-27' AS DATE)
)AS rides
JOIN `swvl-161014.swvl.routes` routes ON routes._id = rides.route
JOIN `swvl-161014.swvl.captains` captains ON captains._id = rides.captain
JOIN `swvl-161014.swvl.cities` ci ON ci._id = rides.city
LEFT JOIN `swvl-161014.swvl.bustypes` bustype ON bustype._id = rides.bus_type
-----------------------------------------------------stations-------------------------------------------------------------------------------------------
INNER JOIN (
          SELECT
          rides._id AS ride_id,
          stations_data.station AS station_id,
          stations.name AS station_name,longitude,  latitude,
          off,
          CASE WHEN stations_data.STATUS <> 'skipped' THEN SUM(1) OVER(PARTITION BY CASE WHEN stations_data.STATUS <> 'skipped' THEN rides._id ELSE NULL END ORDER BY off) ELSE NULL END RNK,
          RANK() OVER(PARTITION BY rides._id ORDER BY off DESC) RNKdesc,
          ROW_NUMBER() OVER (PARTITION BY rides._id) AS rowNum
          FROM `swvl-161014.swvl.rides` AS rides, UNNEST(stations_data) AS stations_data  WITH OFFSET off
          JOIN `swvl-161014.swvl.stations` stations ON stations._id = stations_data.station
          WHERE DATE(DATE) < (CURRENT_DATE())
          ) D ON D.ride_id = rides._id --and D.rowNum = A.rowNum
-------------------------------------------------------------schedule-----------------------------------------------------------------------------------
LEFT JOIN (    SELECT
          rides._id,
          schedule,
          off,
          RANK() OVER(PARTITION BY rides._id ORDER BY off) RNK,
          RANK() OVER(PARTITION BY rides._id ORDER BY off DESC) RNKdesc,
          ROW_NUMBER() OVER (PARTITION BY rides._id ORDER BY schedule ASC) AS rowNum
          FROM `swvl-161014.swvl.rides` AS rides, UNNEST(rides.schedule) AS schedule WITH OFFSET off
          WHERE DATE(DATE) < (CURRENT_DATE())
          ) A ON D.ride_id = A._id AND D.off = A.off
-------------------------------------------------------------arrivals-----------------------------------------------------------------------------------
LEFT JOIN (    SELECT
          rides._id,
          arrivals,
          off,
          off+1 rnkorder,
          RANK() OVER(PARTITION BY rides._id ORDER BY off) RNK,
          RANK() OVER(PARTITION BY rides._id ORDER BY off DESC) RNKdesc,
          ROW_NUMBER() OVER (PARTITION BY rides._id ORDER BY arrivals ASC) AS rowNum
          FROM `swvl-161014.swvl.rides` AS rides, UNNEST(rides.arrivals) AS arrivals WITH OFFSET off
          WHERE DATE(DATE) < (CURRENT_DATE())
          ) B ON rides._id = B._id AND D.rnk= B.rnkorder
-------------------------------------------------------------departures-----------------------------------------------------------------------------------
LEFT JOIN (
          SELECT
          rides._id,
          departures,
          off,
          off+1 rnkorder,
          RANK() OVER(PARTITION BY rides._id ORDER BY off) RNK,
          RANK() OVER(PARTITION BY rides._id ORDER BY off DESC) RNKdesc,
          ROW_NUMBER() OVER (PARTITION BY rides._id ORDER BY departures ASC) AS rowNum
          FROM `swvl-161014.swvl.rides` AS rides, UNNEST(rides.departures) AS departures WITH OFFSET off
          WHERE DATE(DATE) < (CURRENT_DATE())
          ) C ON rides._id = C._id AND D.rnk - 1 = C.rnkorder
------------------------------------------------------------------------------------------------------------------------------------------------
-- LEFT JOIN (
--           select
--           rides._id as ride_id,
--           stations_data.station as station_id,
--           stations.name as station_name,longitude,   latitude,
--           off,
--           CASE WHEN stations_data.status <> 'skipped' THEN SUM(1) OVER(PARTITION BY CASE WHEN stations_data.status <> 'skipped' THEN rides._id ELSE NULL END ORDER BY off) ELSE NULL END RNK,
--           RANK() OVER(PARTITION BY rides._id ORDER BY off DESC) RNKdesc,
--           row_number() over (PARTITION BY rides._id) as rowNum
--           FROM `swvl-161014.swvl.rides` as rides, UNNEST(stations_data) as stations_data  WITH OFFSET off
--           JOIN `swvl-161014.swvl.stations` stations on stations._id = stations_data.station
--           WHERE date(date) < (CURRENT_DATE())
--           ) D on D.ride_id = rides._id and D.rowNum = A.rowNum
-- LEFT JOIN (
--           select
--           ride,
-- --           _id as booking_id,
-- --           date as booking_date,
--           pick_up,
-- --           status as booking_status,
--           sum(seat_count) as total_bookings,
--           sum(if(status = 'completed',seat_count,null)) as completed_bookings,
--           sum(if(status = 'cancelled',seat_count,null)) as cancelled_bookings,
--           sum(if(status = 'missed',seat_count,null)) as missed_bookings
--           FROM `swvl-161014.swvl.bookings`
--           WHERE date(date) < (CURRENT_DATE())
--           GROUP by 1,2--,3,4,5
--           ) E on E.ride = rides._id AND E.pick_up = D.station_id
-- LEFT JOIN (
--            select
--           ride,
--           drop_off ,
--           sum(seat_count) as total_bookings,
--           sum(if(status = 'completed',seat_count,null)) as completed_bookings,
--           sum(if(status = 'cancelled',seat_count,null)) as cancelled_bookings,
--           sum(if(status = 'missed',seat_count,null)) as missed_bookings
--           FROM `swvl-161014.swvl.bookings`
--           WHERE date(date) < (CURRENT_DATE())
--           GROUP by 1,2
--           ) f on f.ride = rides._id AND f.drop_off = D.station_id
LEFT OUTER JOIN (
           SELECT
          ride,
          _id AS booking_id,
          DATE AS booking_date,
          STATUS AS booking_status,
          pick_up,
          drop_off ,
          SUM(seat_count) AS total_bookings,
          SUM(IF(STATUS = 'completed',seat_count,NULL)) AS completed_bookings,
          SUM(IF(STATUS = 'cancelled',seat_count,NULL)) AS cancelled_bookings,
          SUM(IF(STATUS = 'missed',seat_count,NULL)) AS missed_bookings
          FROM `swvl-161014.swvl.bookings`
          WHERE DATE(DATE) < (CURRENT_DATE())
--           and _id='5c3c9ff289e82f00190ae926'
          GROUP BY 1,2,3,4,5,6
          ) g ON  rides._id =g.ride AND (D.station_id=g.pick_up OR  D.station_id =g.drop_off  )
WHERE
ci.name IN ('Nairobi')
AND DATE(DATE) < (CURRENT_DATE())
GROUP BY  ci.name ,
g.booking_id,
g.booking_status,
g.pick_up,
g.drop_off ,
off,
rides._id ,
rides.TYPE ,
rides.STATUS ,
CASE WHEN rides.backup IS NOT NULL THEN 'Y' ELSE 'N' END ,
CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS DATE),
CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS TIME) ,
EXTRACT(week FROM CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS DATE ))+1  ,
EXTRACT(MONTH FROM CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS DATE ))  ,
EXTRACT(YEAR FROM CAST(DATETime(rideS.DATE ,"Africa/Nairobi") AS DATE ))  ,
FORMAT_DATE('%Y%m', CAST(DATETime(rideS.DATE , "Africa/Nairobi") AS DATE )) ,
CAST(DATETime(g.booking_date, "Africa/Nairobi") AS DATE) ,
CAST(DATETime(g.booking_date, "Africa/Nairobi") AS TIME) ,
routes.name ,
routes.total_distance/1000 ,
bustype.name ,
bustype.make ,
bustype.seats,
station_id,
station_name,
longitude,
latitude,
g.total_bookings ,
g.completed_bookings ,
g.cancelled_bookings ,
g.missed_bookings
)
SELECT
* , round((
SUM(COALESCE(completed_bookings,0) + COALESCE(missed_bookings,0)) OVER(partition BY Ride_ID) / NULLIF(MAX(bus_seats) OVER(partition BY Ride_ID),0) ) * 100,2) AS ride_utitlization,

    DATETIME_DIFF(CAST (pick_up_departures AS DATETIME), CAST (pick_up_schedule AS DATETIME), MINUTE) AS wait_time_at_pick_up_station,
    DATETIME_DIFF(CAST (drop_off_departures AS DATETIME), CAST (drop_off_schedule AS DATETIME), MINUTE) AS wait_time_at_drop_off_station
FROM ride_stations
-- where booking_status <> 'cancelled'
-- and  Ride_ID = '5d9aaba5597bb100101f17ff' --and booking_id = '5d9cc12e09bf66001a27ae26'
)
GROUP BY
city,
route_name,
route_distance_km,
 Ride_type,
-- Ride_status,
off ,
--  backup_flag,
-- ride_date,
-- ride_time,
ride_week_number ,
-- ride_month ,
--  ride_year ,
ride_yearmo,
station_id,
station_name,
longitude,
latitude,
--  pick_up_station_id,
-- pick_up_station_name,
--  pick_up_station_longitude,
--  pick_up_station_latitude,


-- pick_up_schedule,
--  pick_up_arrivals,
--  pick_up_departures,
--  pick_up_station_status,
--  pick_up_station_number,
--  drop_off_station_id,
--  drop_off_station_name,
--  drop_off_station_longitude,
--  drop_off_station_latitude,
-- drop_off_schedule,
--  drop_off_arrivals,
--  drop_off_departures,
-- drop_off_station_status,
--  drop_off_station_number,
--  bus_type,
-- bus_make,
 bus_seats
 )GROUP BY  city,
route_name,
route_distance_km,
 Ride_type,
ride_week_number ,
ride_yearmo,
station_id,
 Station_SEQU,
station_name,
longitude,
latitude