/*
- make ID's unique
- cross check types with lab instructions
- double check NOT NULL
- added year to day
*/

# TO RUN
# source /home/krila597/TDDD12/script.sql;

#drop all the tables

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS destination;
DROP TABLE IF EXISTS year;
DROP TABLE IF EXISTS route;
DROP TABLE IF EXISTS day;
DROP TABLE IF EXISTS weeklyschedule;
DROP TABLE IF EXISTS flight;
DROP TABLE IF EXISTS reservation;
DROP TABLE IF EXISTS creditcard;
DROP TABLE IF EXISTS payment;
DROP TABLE IF EXISTS passenger;
DROP TABLE IF EXISTS contact;
DROP TABLE IF EXISTS ticket;

SET FOREIGN_KEY_CHECKS=1;



# create table 'destination'
CREATE TABLE IF NOT EXISTS destination(
	code VARCHAR(3) NOT NULL,
	name VARCHAR(30) NOT NULL,
	country VARCHAR(30) NOT NULL,
	PRIMARY KEY(code)
);

# create table 'year' that holds profit factor
CREATE TABLE IF NOT EXISTS year(
	year INT NOT NULL,
	profitfactor DOUBLE,
	PRIMARY KEY(year)
);

# create table 'route', arrival and departure are foreign keys from Destination. year from Year entity
CREATE TABLE IF NOT EXISTS route(
	id INT AUTO_INCREMENT,
	departure VARCHAR(3),
	arrival VARCHAR(3),
	route_year INT NOT NULL,
	price DOUBLE,
	PRIMARY KEY(id),
	FOREIGN KEY (departure) REFERENCES destination(code),
	FOREIGN KEY (arrival) REFERENCES destination(code)
);

# create table 'day'
CREATE TABLE IF NOT EXISTS day(
	dayname VARCHAR(10) NOT NULL,
	weekdayfactor DOUBLE NOT NULL,
	year INT,
	PRIMARY KEY(dayname)
);

# create table 'weeklyschedule', with route and day has foreign keys
CREATE TABLE IF NOT EXISTS weeklyschedule(
	id INT auto_increment NOT NULL,
	route INT,
	departure_time TIME,
	wday VARCHAR(10) NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (wday) REFERENCES day(dayname),
	FOREIGN KEY (route) REFERENCES route(id)
);


# create table 'flight', with weekly schedule as foreign key
CREATE TABLE IF NOT EXISTS flight(
	flightnumber INT auto_increment NOT NULL,
	fweek INT,
	weekly_schedule INT,
	available_seats INT,
	PRIMARY KEY (flightnumber),
	FOREIGN KEY (weekly_schedule) REFERENCES weeklyschedule(id)
);

# create table 'reservation'
CREATE TABLE IF NOT EXISTS reservation(
	reservationnumber INT auto_increment NOT NULL,
	flight INT,
	passengers INT,
	PRIMARY KEY (reservationnumber),
	FOREIGN KEY (flight) REFERENCES flight(flightnumber)
);

# create table 'creditcard'
CREATE TABLE IF NOT EXISTS creditcard(
	card_number BIGINT NOT NULL,
	name VARCHAR(30),
	PRIMARY KEY(card_number)
);


# create table 'payment'
CREATE TABLE IF NOT EXISTS payment(
	id INT auto_increment,
	card_number BIGINT,
	reservation INT,
	PRIMARY KEY(id),
	FOREIGN KEY (card_number) REFERENCES creditcard(card_number),
	FOREIGN KEY (reservation) REFERENCES reservation(reservationnumber)
);

#create table 'passenger'
CREATE TABLE IF NOT EXISTS passenger(
	id INT auto_increment,
	passport_number INT,
	name VARCHAR(30),
	reservation INT,
	PRIMARY KEY (id),
	FOREIGN KEY (reservation) REFERENCES reservation(reservationnumber)
);

# create table 'contact', no foreign key right?
CREATE TABLE IF NOT EXISTS contact(
	passport_number INT NOT NULL,
	email VARCHAR(30),
	phone BIGINT,
	PRIMARY KEY (passport_number)/*,
	FOREIGN KEY (passenger_id) REFERENCES passenger(id)*/
);




# create table 'ticket'
CREATE TABLE IF NOT EXISTS ticket(
	id INT NOT NULL,
	payment INT,
	passenger INT,
	flight INT,
	PRIMARY KEY (id),
	FOREIGN KEY (payment) REFERENCES payment(id),
	FOREIGN KEY (passenger) REFERENCES passenger(id),
	FOREIGN KEY (flight) REFERENCES flight(flightnumber)
);




# PROCEDURES
/*call addYear(2019, 2.5);
call addDay(2019, 'Monday', 10.0);
call addDestination('SKA', 'Skavsta', 'Sweden');
call addDestination('GBG', 'Göteborg', 'Sweden');
call addRoute('SKA', 'GBG', 2019, 100.0);*/


/*====================================================================================================================================

=====================================================================================================================================*/

# addYear
DROP PROCEDURE IF EXISTS addYear;

DELIMITER //
CREATE PROCEDURE addYear(IN vyear INT, IN factor DOUBLE)
BEGIN

IF (SELECT year FROM year WHERE year = vyear) IS NULL THEN
INSERT INTO `year`(`year`, `profitfactor`) VALUES (vyear, factor);
END IF;
END //
DELIMITER ;


/*====================================================================================================================================

=====================================================================================================================================*/
# addDay
DROP PROCEDURE IF EXISTS addDay;

DELIMITER //
CREATE PROCEDURE addDay(IN year INT, IN day VARCHAR(10), IN factor DOUBLE)
BEGIN
INSERT INTO `day`(`year`, `dayname`, `weekdayfactor`) VALUES (year, day, factor);
END //
DELIMITER ;


/*====================================================================================================================================

=====================================================================================================================================*/
# add destination
DROP PROCEDURE IF EXISTS addDestination;

DELIMITER //
CREATE PROCEDURE addDestination(IN airport_code VARCHAR(3), IN name VARCHAR(30), IN country VARCHAR(30))
BEGIN

INSERT INTO `destination`(`code`, `name`, `country`) VALUES (airport_code, name, country);




END //
DELIMITER ;


/*====================================================================================================================================

=====================================================================================================================================*/

# add route
DROP PROCEDURE IF EXISTS addRoute;

DELIMITER //
CREATE PROCEDURE addRoute(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN routeprice DOUBLE)
BEGIN
INSERT INTO `route`(`departure`, `arrival`, `route_year`, `price`) VALUES (departure_airport_code, arrival_airport_code, year, routeprice);
END //
DELIMITER ;


/*====================================================================================================================================

=====================================================================================================================================*/

# add weeklyflight
DROP PROCEDURE IF EXISTS addFlight;

DELIMITER //
CREATE PROCEDURE addFlight(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN day VARCHAR(10), IN time TIME)
BEGIN

DECLARE	route_id INT;
DECLARE route_price DOUBLE;
DECLARE schedule_id INT;
DECLARE i INT;

# find the route id

SELECT id INTO route_id FROM route WHERE departure = departure_airport_code AND arrival = arrival_airport_code AND route_year = year;


SELECT price INTO route_price FROM route WHERE id = route_id;

INSERT INTO `weeklyschedule`(`route`, `departure_time`, `wday`) VALUES (route_id, time, day);

SELECT id INTO schedule_id FROM weeklyschedule WHERE route = route_id AND departure_time = time  and wday = day;

# add 52 flights to Flights, one for each week

IF schedule_id IS NOT NULL THEN
SET i = 1;
	WHILE i <= 52 DO
	INSERT INTO `flight`(`fweek`, `weekly_schedule`, `available_seats`) VALUES (i, schedule_id, 40);
	SET i = i + 1;
	END WHILE;
END IF;

END //
DELIMITER ;




/*====================================================================================================================================

=====================================================================================================================================*/

DROP FUNCTION IF EXISTS calculateFreeSeats;
DELIMITER //

CREATE FUNCTION calculateFreeSeats(flight_number INT)
RETURNS INT
BEGIN

DECLARE seats INT;


SELECT COUNT(*) INTO seats FROM ticket WHERE flight_number = flight;


SET seats = (SELECT available_seats FROM flight WHERE flight_number = flightnumber) - seats;


UPDATE flight SET available_seats = seats WHERE flight_number = flightnumber;

RETURN seats;

END //
DELIMITER ;
/*====================================================================================================================================

=====================================================================================================================================*/
DROP FUNCTION IF EXISTS calculatePrice;
DELIMITER //

CREATE FUNCTION calculatePrice(flight_number INT)
RETURNS DOUBLE
BEGIN

DECLARE weekday_factor DOUBLE;
DECLARE booked_passengers INT;
DECLARE profit_factor DOUBLE;

# get weekly flight schedule to get route

# route price
DECLARE route_price DOUBLE;
SELECT price INTO route_price FROM route WHERE id = (SELECT route FROM weeklyschedule WHERE id = (SELECT weekly_schedule FROM flight where flightnumber = flight_number));

# weekday factor

SELECT weekdayfactor INTO weekday_factor FROM day WHERE dayname = (SELECT wday FROM weeklyschedule WHERE id = (SELECT weekly_schedule FROM flight where flightnumber = flight_number));

# number of booked passengers
SELECT calculateFreeSeats(flight_number) INTO booked_passengers;

# profitmargin
SELECT profitfactor INTO profit_factor FROM year WHERE year = (SELECT route_year FROM route WHERE id = (SELECT route FROM weeklyschedule WHERE id = (SELECT weekly_schedule FROM flight where flightnumber = flight_number)));



RETURN route_price * weekday_factor * (seats + 1)/40 * profit_factor;
END //
DELIMITER ;

/*====================================================================================================================================

=====================================================================================================================================*/


DELIMITER //
# create a trigger for ticket
CREATE TRIGGER ticketNumber AFTER INSERT ON payment
FOR EACH ROW
BEGIN

DECLARE reservation_number INT;
SELECT MAX(id) INTO reservation_number FROM payment;

INSERT INTO `ticket`(`id`, `payment`, `passenger`/*, `flight`*/)
SELECT
rand()*1000,
reservation,
id FROM passenger WHERE reservation = reservation_number;/*
flight FROM reservation WHERE reservationnumber = reservation_number;
*/

UPDATE ticket SET flight = (SELECT flight FROM reservation WHERE reservationnumber = reservation_number);

END //
DELIMITER ;


/*====================================================================================================================================

=====================================================================================================================================*/


DROP PROCEDURE IF EXISTS addReservation;
DELIMITER //

CREATE PROCEDURE addReservation(IN departure_airport_code VARCHAR(3), IN arrival_airport_code VARCHAR(3), IN year INT, IN inweek INT, IN day VARCHAR(10), IN time TIME, IN number_of_passengers INT, OUT output_reservation_nr INT)
BEGIN

DECLARE flight_number INT;
DECLARE route_id INT;
DECLARE schedule_id INT;

SELECT id INTO route_id FROM route WHERE departure = departure_airport_code AND arrival = arrival_airport_code AND route_year = year;

IF route_id IS NULL THEN
SELECT 'route_id is null' AS 'Message';
END IF;


SELECT id INTO schedule_id FROM weeklyschedule WHERE wday = day AND departure_time = time AND route = route_id;

IF schedule_id IS NULL THEN
SELECT 'schedule_id is null' AS 'Message';
END IF;

SELECT flightnumber INTO flight_number FROM flight WHERE weekly_schedule = schedule_id AND fweek = inweek;
/*
SELECT flightnumber INTO flight_number FROM flight WHERE fweek = inweek; AND weekly_schedule =
(SELECT id from weeklyschedule WHERE day = day AND departure_time = time  AND route =
(SELECT id FROM route WHERE departure = departure_airport_code AND arrival = arrival_airport_code AND route_year = year));
*/
/*
SELECT @week AS 'Week';
SELECT @flight_number AS 'Flightnumber';*/

IF flight_number IS NULL THEN
SELECT 'There exist no flight for the given route, date and time' AS 'Message';

ELSE
IF calculateFreeSeats(flight_number) < number_of_passengers THEN
SELECT "There are not enough seats available on the chosen flight" AS 'Message';


ELSE
INSERT INTO `reservation`(`flight`, `passengers`) VALUES(flight_number, number_of_passengers);
SELECT MAX(reservationnumber) INTO output_reservation_nr FROM reservation;
END IF;
END IF;



END //
DELIMITER ;


/*====================================================================================================================================

=====================================================================================================================================*/


DROP PROCEDURE IF EXISTS addPassenger;
DELIMITER //

CREATE PROCEDURE addPassenger(IN reservation_nr INT, IN passport_number INT, IN name VARCHAR(30))
BEGIN

IF (SELECT reservationnumber FROM reservation WHERE reservationnumber = reservation_nr) IS NULL THEN
SELECT 'The given reservation number does not exist' AS 'Message';

ELSE


IF (SELECT reservation FROM payment WHERE reservation = reservation_nr) IS NOT NULL THEN
SELECT 'The booking has already been payed and no futher passengers can be added' AS 'Message';


ELSE
INSERT INTO `passenger`(`passport_number`, `name`, `reservation`) VALUES (passport_number, name, reservation_nr);
END IF;
END IF;
END //
DELIMITER ;


/*====================================================================================================================================

=====================================================================================================================================*/


DROP PROCEDURE IF EXISTS addContact;
DELIMITER //

CREATE PROCEDURE addContact(IN reservation_nr INT, IN INpassport_number INT, IN email VARCHAR(45), IN phone INT)
BEGIN


IF (SELECT min(reservationnumber) FROM reservation WHERE reservationnumber = reservation_nr) IS NULL THEN
SELECT 'The given reservation number does not exist' AS 'Message';

ELSE
IF (SELECT MIN(id) FROM passenger WHERE passport_number = INpassport_number AND reservation = reservation_nr) IS NULL THEN
SELECT 'The person is not a passenger of the reservation' AS 'Message';
ELSE
INSERT INTO `contact`(`passport_number`, `email`, `phone`) VALUES (INpassport_number, email, phone);
END IF;
END IF;

END //
DELIMITER ;



/*====================================================================================================================================

=====================================================================================================================================*/

DROP PROCEDURE IF EXISTS addPayment;
DELIMITER //

CREATE PROCEDURE addPayment (IN reservation_nr INT, IN cardholder_name VARCHAR(30), IN credit_card_number BIGINT)
BEGIN


DECLARE seats INT;
DECLARE flight_number INT;
DECLARE tmp INT;

/* Get potential contact */
SELECT passport_number INTO tmp FROM contact WHERE passport_number = (SELECT MIN(passport_number) FROM passenger WHERE reservation = reservation_nr);

/* Get flight number */
SELECT flight INTO flight_number FROM reservation WHERE reservationnumber = reservation_nr;

/* Get number of seats in reservation */
SELECT COUNT(*) INTO seats FROM passenger WHERE reservation = reservation_nr;


IF (SELECT `reservationnumber` FROM reservation WHERE `reservationnumber` = reservation_nr) IS NULL THEN
SELECT 'The given reservation number does not exist' AS 'Message';

ELSE

IF tmp IS NULL THEN
SELECT 'The reservation has no contact yet' as 'Message';

ELSE


IF (seats > calculateFreeSeats(flight_number)) THEN
SELECT 'There are not enough seats available on the flight anymore, deleting reservation' AS 'Message';
SET SQL_SAFE_UPDATES=0;
DELETE FROM passenger WHERE reservation = reservation_nr;
DELETE FROM reservation WHERE reservationnumber = reservation_nr;
SET SQL_SAFE_UPDATES=1;

ELSE


IF (SELECT card_number FROM creditcard WHERE card_number = credit_card_number) IS NULL THEN
INSERT INTO creditcard(card_number, name) VALUES (credit_card_number, cardholder_name);
END IF;


INSERT INTO payment(card_number, reservation) VALUES (credit_card_number, reservation_nr);

END IF;
END IF;
END IF;

END //
DELIMITER ;


/*====================================================================================================================================

=====================================================================================================================================*/


DROP VIEW IF EXISTS allFlights;
CREATE VIEW IF NOT EXISTS allFlights AS

SELECT
C.name AS 'departure_city_name', /* VARCHAR(30) */
C.name AS 'destination_city_name', /* VARCHAR(30) */
B.departure_time AS 'departure_time', /* TIME */
B.wday AS 'departure_day', /* VARCHAR(10) */
D.fweek AS 'departure_week', /* INT? */
A.route_year AS 'departure_year', /* INT */
calculateFreeSeats(D.flightnumber) AS 'nr_of_free_seats', /* INTEGER */
calculatePrice(D.flightnumber) AS 'current_price_per_seat' /* DOUBLE */

FROM route A, weeklyschedule B, destination C, flight D
WHERE A.id = B.route AND D.weekly_schedule = B.id AND A.departure = C.code AND A.arrival = C.code;


/*
A route = departure, arrival, year
B weeklyschedule = route, departure_time, day
C destination = name
D flight = flightnumber, fweek

*/