-- -----------------------------------------------------------------------------

-- 1: Skrifið Stored Procedure sem finnur öll laus sæti í ákveðnu flugi.

-- -----------------------------------------------------------------------------


delimiter $$



drop procedure if exists freeSeats $$

create procedure freeSeats(booking_id int(11), plane_id char(6))

begin

	
	declare passengers int;
	
	declare max_seats int;

	

	select count(seatingID) into passengers from seating
	
	join bookings on bookings.bookingNumber = seating.bookingNumber
	
	where bookings.flightCode = booking_id;

	

	select count(seatID) into max_seats from seats
	
	where aircraftID = plane_id;

	

	select max_seats - passengers;


end $$



delimiter ;




-- -----------------------------------------------------------------------------

-- 2: Skrifið Function/Stored Procedure sem sækir random eitt sæti af lausum sætum í ákveðnu flugi.

-- -----------------------------------------------------------------------------


delimiter $$



drop procedure if exists randSeat $$

create procedure randSeat(booking_id int(11), plane_id char(6))

begin

	
	select seatID from seating
	
	join bookings on bookings.bookingNumber = seating.bookingNumber
	
	where bookings.flightCode = booking_id;

	

	select seatID from seats
	
	where aircraftID = plane_id AND seatID NOT IN (select seatID from seating

							join bookings on bookings.bookingNumber = seating.bookingNumber
							where bookings.flightCode = 1)

	order by rand()

	limit 1;


end $$



delimiter ;



-- -----------------------------------------------------------------------------

-- 4: Skrifið Stored Procedure sem aflýsir flugi séu færri en 5 farþegar 

-- 	  bókaðir.  Ekki er nauðsynlegt að vera með endurgreiðslukerfi, heldur

-- 	  eru upplýsingar um þetta flug einfaldlega teknar úr grunninum.


-- 	  Hérna er spurning um að bæta við einum statusdálki í töfluna Flights

-- 	  sem að hefði:

-- 		0:  Óflogið  (default)

-- 		1:  Flogið

-- 		2:  Aflýst	

-- -----------------------------------------------------------------------------



delimiter $$



drop procedure if exists cancelFlight $$

create procedure cancelFlight(booking_id int(11), plane_id char(6))

begin

	
	declare passengers int;

	

	select count(seatingID) into passengers from seating
	
	join bookings on bookings.bookingNumber = seating.bookingNumber
	
	where bookings.flightCode = booking_id;

	
	IF passengers < 5 THEN
		
		UPDATE `freshair_v2`.`flights`

		SET

		`status` = 2

		WHERE `aircraftID` = plane_id;

	END IF;



end 

$$

delimiter ;