DatabaseCreation: views.sql

create or replace view vw_unassigned_requests as
select r.id,
       r.customer_user_id,
       r.start_latitude,
       r.start_longitude,
       r.end_latitude,
       r.end_longitude,
       r.timestamp,
       r.number_of_children,
       r.status,
       r.female_driver,
       r.luggage,
       r.luggage_count,
       u.username as customer_username
from Request r
         join AppUser u on r.customer_user_id = u.id
where r.status = 'pending'::request_status;

create or replace view vw_live_offers_for_customer as
select o.id               as offer_id,
       request_id,
       o.price,
       o.eta,
       o.customer_user_id as customer_id,
       cu.username        as customer_username,
       cu.first_name      as customer_fist_name,
       cu.last_name       as customer_last_name,
       du.first_name      as driver_first_name,
       du.last_name       as driver_last_name
from offer o
         join appuser du on o.driver_user_id = du.id
         join appuser cu on o.customer_user_id = cu.id
where o.status = 'pending'::offer_status;

create or replace view vw_available_drivers as
select d.user_id, u.first_name, u.last_name, u.phone_number
from Driver d
         join AppUser u on d.user_id = u.id
         join DriverLicense dl on d.driver_license_id = dl.id
         join driver_vehicle dc on dc.id_driver = d.user_id
         left join Ride r on r.driver_user_id = u.id
where (dl.expire_date > CURRENT_DATE or dl.expire_date is null and dc.time_to is null)
  and r.status != 'in_progress'::ride_status;

create or replace view all_drivers as
with drivers_from_company as (select u.id, first_name, last_name, phone_number, email, True as from_company
                              from Appuser u
                                       inner join Driver d on u.id = d.user_id),
     drivers_freelance as (select u.id, first_name, last_name, phone_number, email, False as from_company
                           from Appuser u
                                    inner join Freelancedriver d on u.id = d.driver_user_id)
select *
from (select * from drivers_from_company union select * from drivers_freelance) drivers_temp;

create or replace view customer_payments_ride as
select c.user_id, cp.amount, cp.payment_method, r.end_time
from customer c
         inner join customerpayment cp on c.user_id = cp.customer_user_id
         inner join payment p on p.id = cp.payment_id
         inner join ride r on p.completed_ride_id = r.id;

create or replace view reports_on_drivers_rides as
select r.title, r.reason, r.message, vehicle.vin, driver.user_id
from Report r
         inner join Ride ride on ride.id = r.ride_id
         inner join Driver driver on driver.user_id = ride.driver_user_id
         inner join Vehicle vehicle on vehicle.vin = ride.vehicle_vin;

create or replace view reviews_on_drivers as
select d.user_id, review.rating, review.comment
from Review review
         inner join Ride r on r.id = review.ride_id
         inner join Driver d on d.user_id = r.driver_user_id;

create or replace view user_message_driver as
select c.user_id as user_id, m.message, d.user_id as driver_id
from chatmessage m
         inner join customer c on m.user_id_from = c.user_id
         inner join ride r on r.id = m.ride_id
         inner join driver d on d.user_id = r.driver_user_id;

create or replace view vw_live_ride_monitor as
select r.id                                                    as ride_id,
       r.start_time,
       cus.first_name                                          as customer_first_name,
       cus.last_name                                           as customer_last_name,
       drv.id                                                  as driver_id,
       drv.first_name                                          as driver_first_name,
       drv.last_name                                           as driver_last_name,
       req.number_of_adult_passengers + req.number_of_children as total_passengers,
       req.luggage_count,
       req.baby_seat_count,
       o.ETA,
       o.dispatcher_user_id,
       c.id                                                    as company_id,
       c.name                                                  as company_name
from Ride r
         join Request req on r.request_id = req.id
         join Offer o on r.offer_id = o.id
         join AppUser drv on r.driver_user_id = drv.id
         join AppUser cus on req.customer_user_id = cus.id
         join EmploymentHistory eh on eh.employee_user_id = o.dispatcher_user_id and eh.end_date is null
         join Company c on c.id = eh.company_id
where r.status = 'in_progress';