
-- a view that shows all current reservations; 
create view CurrentReservationsV as
       select *
         from Reservation r
	where (r.interval).begints < current_timestamp 
	      and (r.interval).endts > current_timestamp;

-- a view that shows all reservations, together with the names of the
-- reserving member, the licenseplate, make and model of the reserved car,
-- and the home location of the vehicle; 
create view ReservationAll_SimpleV as
       select r.resnumber, (r.interval).begints, (r.interval).endts,
              v.licenseplate, v.make, v.model, m.membernr
         from reservation r join
              vehicle v on r.vehicle = v.id join
              member m on r.member = m.id;

create view ReservationAllV as
       select r.resnumber, (r.interval).begints, (r.interval).endts,
              m.membernr, m.name, v.licenseplate, v.make, v.model
         from reservation r left outer join
              vehicle v on r.vehicle = v.id left outer join
	      (select c.id, c.membernr, c.companyname as name
		 from CompanyMember c
	       union all
	       select o.id, o.membernr,
	              (o.firstname || ' ' || o.lastname) as name
		 from CoopMember o
	       union all
	       select p.id, p.membernr,
	              (p.firstname || ' ' || p.lastname) as name
		 from PersonMember p) as m
              on r.member = m.id;
select *
  from ReservationAllV
 order by resnumber;

-- * a view that shows the locations together with the number of cars at
-- this location and the number of members having this location as their
-- home location
create view LocationInfoV as
       select l.shortname, l.name, count(distinct v.id) as car_cnt,
              count(distinct m.id) as member_cnt
	 from vehicle v join
	      location l on v.home = l.id join
	      member m on m.homeLocation = l.id
	group by l.shortname, l.name;

-- the "outer" member table---this is a view showing all attributes
-- of all tables in the member specialization hierarchy; 
create view OuterMemberV as
       select p.id,
              p.type,
              p.membernr,
              p.password,
              p.homelocation,
              p.address,
              p.bankaccount,
              p.firstname || ' ' || p.lastname as name,
              p.dateofbirth,
              p.hadaccident
         from personmember p
       union all
       select c.id,
              c.type,
              c.membernr,
              c.password,
              c.homelocation,
              c.address,
              c.bankaccount,
              c.firstname || ' ' || c.lastname as name,
              c.dateofbirth,
              false as hadaccident
         from coopmember c
       union all
       select g.id,
              g.type,
              g.membernr,
              g.password,
              g.homelocation,
              g.address,
              g.bankaccount,
              g.companyName  as name,
              null as dateofbirth,
              false as hadaccident
         from companymember g;

-- * the "outer" vehicle table---this is a view showing all attributes 
-- of all the tables in the vehicle specialization hierarchy; 	
create view OuterVehicle as
       select m.id,
              m.licensePlate, 
	      m.kilometers,
	      m.type ,
	      m.model, 
	      m.make,
	      m.motorNumber,
	      m.isOperational, 
	      m.operationStart,
	      m.lastService, 
	      m.gasConsumption,
	      m.home,
	      m.insurance,
	      m.childSeat,
	      m.extras,
	      true as backseat,
	      -1 as loadingVolume,
	      -1 as numberOfSeats,
	      -1 as loadingCapacity,
	      -1 as height
	 from MotorCar m
        where type in ('CompactCar', 'Limousine', 'Sedan')	
       union all
       select c.id,
              c.licensePlate, 
	      c.kilometers,
	      c.type ,
	      c.model, 
	      c.make,
	      c.motorNumber,
	      c.isOperational, 
	      c.operationStart,
	      c.lastService, 
	      c.gasConsumption,
	      c.home,
	      c.insurance,
	      c.childSeat,
	      c.extras,
	      c.backseat,
	      -1 as loadingVolume,
	      -1 as numberOfSeats,
	      -1 as loadingCapacity,
	      -1 as height
	 from Convertible c
       union all
       select s.id,
              s.licensePlate, 
	      s.kilometers,
	      s.type ,
	      s.model, 
	      s.make,
	      s.motorNumber,
	      s.isOperational, 
	      s.operationStart,
	      s.lastService, 
	      s.gasConsumption,
	      s.home,
	      s.insurance,
	      s.childSeat,
	      s.extras,
	      true as backseat,
	      s.loadingVolume,
	      -1 as numberOfSeats,
	      -1 as loadingCapacity,
	      -1 as height
	 from StationWagon s
       union all
       select v.id,
              v.licensePlate, 
	      v.kilometers,
	      v.type ,
	      v.model, 
	      v.make,
	      v.motorNumber,
	      v.isOperational, 
	      v.operationStart,
	      v.lastService, 
	      v.gasConsumption,
	      v.home,
	      v.insurance,
	      v.childSeat,
	      v.extras,
	      true as backseat,
	      -1 as loadingVolume,
	      v.numberOfSeats,
	      -1 as loadingCapacity,
	      -1 as height
	 from Van v
       union all
       select p.id,
              p.licensePlate, 
	      p.kilometers,
	      p.type ,
	      p.model, 
	      p.make,
	      p.motorNumber,
	      p.isOperational, 
	      p.operationStart,
	      p.lastService, 
	      p.gasConsumption,
	      p.home,
	      p.insurance,
	      false as childSeat,
	      '' as extras,
	      false as backseat,
	      -1 as loadingVolume,
	      -1 as numberOfSeats,
	      p.loadingCapacity,
	      -1 as height
	 from Pickup p
       union all
       select t.licensePlate, 
	      t.kilometers,
	      t.type ,
	      t.model, 
	      t.make,
	      t.motorNumber,
	      t.isOperational, 
	      t.operationStart,
	      t.lastService, 
	      t.gasConsumption,
	      t.home,
	      t.insurance,
	      false as childSeat,
	      '' as extras,
	      false as backseat,
	      -1 as loadingVolume,
	      -1 as numberOfSeats,
	      t.loadingCapacity,
	      t. height
	 from Transporter t;

-- look at the *_JT tables for vehicles, where the 
-- specialization hierarchy has been implemented with foreign keys.
-- Define the VanV view showing all the attributes for Vans (i.e.,
-- including the inherited ones). 
create view VanV as
       select v.id,
              licensePlate,
              make,
              model,
              motorNumber,
              kilometers,
              isOperational,
              operationStart,
              lastService,
              gasConsumption,
              insurance,
              home,
              childSeat,
              extras,
              numberOfSeats
         from Vehicle_JT v inner join
              MotorCar_JT m on v.id = m.id  inner join
              Van_JT c on m.id = c.id;


-- look at the *_JT tables for members, where the 
-- specialization hierarchy has been implemented with foreign keys.
-- Define the PersonMemberV view showing all the attributes
-- for PersonMembers (i.e., including the inherited ones). 
create view PersonMemberV as
       select m.id,
              m.membernr,
              m.password,
              m.homelocation,
              pm.hadAccident,
              p.address,
              p.bankaccount,
              p.firstname,
              p.lastname,
              p.dateofbirth
         from Member_JT m join PersonMember_JT pm on m.id = pm.id join
              Person p on pm.person = p.id;

