#Create a function that sums the total amount of days cars have been booked and returns the sum.
DELIMITER //
DROP FUNCTION IF EXISTS FunTWO;
CREATE FUNCTION FunTwo(Vecile INT)
RETURNS INT DETERMINISTIC
BEGIN
  DECLARE ind INT DEFAULT 0;
  SET ind = (SELECT SUM(DATEDIFF(EndDate,StartDate)) FROM Booking WHERE Vecile = CarNumber);
  RETURN if(ind>0,ind,-1);
END //
DELIMITER ;
SELECT FunTwo(3) AS Totaltimethecarhasbeenbooked;
#Extend the previous function so that if there is an input parameter that matches a cars unique number, then it should only return the sum of that car. If the number doesn't match or it is -1, it returns the total sum as before.
#Stored Procedures
#Create a stored procedure that collects all the cars that are available between two dates. The inputs to the procedure is starting and ending dates, and prints all the car numbers that are available to be booked between the two dates.


DELIMITER //
DROP PROCEDURE AvaliableCars;
CREATE PROCEDURE AvaliableCars(StarD DATE, EndD DATE)
  BEGIN
    SELECT * FROM Cars
    WHERE CarNr NOT IN
          (SELECT Carnumber FROM Booking WHERE (StartDate BETWEEN StarD AND EndD)OR
                                               (EndDate BETWEEN StarD AND EndD) OR
                                               (StarD >= StartDate AND EndD  <= EndDate));
  END //

DELIMITER ;

SELECT * FROM Booking;
CALL AvaliableCars('2018-01-02', '2018-01-15');
#Create a stored procedure that handles the booking of renting a car. The input to the procedure is the starting and ending dates, the cars number,  and the customer number. If it is successful it should return 0, if it is unsuccessful in booking it should return 1.
DROP PROCEDURE IF EXISTS ProTwo;
CREATE PROCEDURE ProTwo(Car INT, Cusid INT, StartD DATE, EndD DATE)
BEGIN
  IF SELECT COUNT(*) AS AV FROM Booking WHERE Car = CarNumber AND Cusid = CustomerNr AND (StartD  BETWEEN StartDate AND EndDate)AND
                                               (EndD  BETWEEN StartDate AND EndDate) = 1; THEN
  END IF
END;

CALL ProTwo(6,1,'2018-01-02', '2018-01-14');
#DELIMITER //
#DROP PROCEDURE IF EXISTS ProTwo;
#CREATE PROCEDURE ProTwo(Car INT, Cusid INT, StartD DATE, EndD DATE)
#BEGIN
#  DECLARE Var INT DEFAULT 0;
#  SET Var = (SELECT COUNT(*) AS AV FROM Booking WHERE Car = CarNumber AND Cusid = CustomerNr AND (StartD  BETWEEN StartDate AND EndDate)AND
#                                               (EndD  BETWEEN StartDate AND EndDate));
#  if(Var=0, )
#END//
#

CALL ProTwo(6,1,'2018-01-02', '2018-01-14');
SELECT * FROM Booking;

  #Triggers
#Add an additional column to Customers that contains the amount of times a customer has booked a car. Then create an after insert trigger on the Bookings table that increments the newly created column in Customers whenever they do a successful booking of a car.
ALTER TABLE Customers ADD COLUMN NRBookings INT DEFAULT 0;
SELECT * FROM Customers;
DELIMITER //
DROP TRIGGER IF EXISTS TrigONE;
CREATE TRIGGER TrigONE AFTER INSERT ON Booking FOR EACH ROW
  BEGIN
    UPDATE Customers SET Customers.NRBookings = Customers.NRBookings + 1 WHERE CustomerNr=NEW.CustomerNr;
  END//

  DELIMITER ;
#Would it be possible to do this trigger with a BEFORE trigger? Why/Why not?

  #inte klarat att lösa
#Show all cars that has never been booked.
#Alter the previous view, with the condition that the cars have to be available for at least 3 days of renting.