/* Drop old DB and create a new one */
DROP SCHEMA IF EXISTS `ARrive`;
CREATE DATABASE `ARrive`;
USE `ARrive`;

/* Create tables */
CREATE TABLE Location (
	ID INT NOT NULL AUTO_INCREMENT,
	Latitude DOUBLE NOT NULL,
	Longitude DOUBLE NOT NULL,
	Address VARCHAR(64) NOT NULL,

	PRIMARY KEY (ID)
);

CREATE TABLE Image (
	ID INT NOT NULL,
	Data BLOB NOT NULL,
	Location VARCHAR(30) NOT NULL,
	Description VARCHAR(99) NOT NULL,

	PRIMARY KEY (ID)
);

CREATE TABLE Purchased_Cities (
	ID INT NOT NULL AUTO_INCREMENT,
	Customer_ID INT NOT NULL,
	City_ID INT NOT NULL,

	PRIMARY KEY (ID)
);

CREATE TABLE Customer (
	ID INT NOT NULL AUTO_INCREMENT,
	First_Name VARCHAR(32) NOT NULL,
	Last_Name VARCHAR(32) NOT NULL,
	Email_Address VARCHAR(66) NOT NULL,

	PRIMARY KEY (ID)
);

CREATE TABLE Customer_Locations_Join (
	ID INT NOT NULL AUTO_INCREMENT,
	Customer_ID INT,
	Location_ID INT,
	Card INT,

	PRIMARY KEY (`ID`)
);

CREATE TABLE Card (
	ID INT NOT NULL AUTO_INCREMENT,
	Number VARCHAR(16) NOT NULL,
	Name_On_Card VARCHAR(15) NOT NULL,

	PRIMARY KEY (ID)
);

/* Set storage engine for relational view (Important) */
ALTER TABLE `Location` ENGINE = InnoDB;
ALTER TABLE `Image` ENGINE = InnoDB;
ALTER TABLE `Purchased_Cities` ENGINE = InnoDB;
ALTER TABLE `Customer` ENGINE = InnoDB;
ALTER TABLE `Customer_Locations_Join` ENGINE = InnoDB;
ALTER TABLE `Card` ENGINE = InnoDB;

/* Assign foreign keys to tables */
ALTER TABLE `Customer_Locations_Join` ADD CONSTRAINT `Customer_ID_FK` FOREIGN KEY (`Customer_ID`)
REFERENCES `Customer`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `Customer_Locations_Join` ADD CONSTRAINT `Location_ID_FK` FOREIGN KEY (`Location_ID`)
REFERENCES `Location`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `Customer_Locations_Join` ADD CONSTRAINT `Card_FK` FOREIGN KEY (`Card`)
REFERENCES `Card`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `Purchased_Cities` ADD CONSTRAINT `Customer_ID_FoK` FOREIGN KEY (`Customer_ID`)
REFERENCES `Customer`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `Purchased_Cities` ADD CONSTRAINT `Location_ID_FoK` FOREIGN KEY (`City_ID`)
REFERENCES `Location`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;

ALTER TABLE `Image` ADD CONSTRAINT `ID_FK` FOREIGN KEY (`ID`)
REFERENCES `Location`(`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT;

/* Populate tables */
INSERT INTO `Customer` (`ID`, `First_Name`, `Last_Name`, `Email_Address`) VALUES ('1', 'McCauley', 'Hallam', 'mccauleyhallam@hotmail.com');
INSERT INTO `Customer` (`ID`, `First_Name`, `Last_Name`, `Email_Address`) VALUES ('2', 'Alexis', 'Turner', 'alexisturner@gmail.com');
INSERT INTO `Customer` (`ID`, `First_Name`, `Last_Name`, `Email_Address`) VALUES ('3', 'Noah', 'Jones', 'noahjones@gmail.com');

INSERT INTO `Card` (`ID`, `Number`, `Name_On_Card`) VALUES ('1', '4871099563721273', 'McCauley Hallam');
INSERT INTO `Card` (`ID`, `Number`, `Name_On_Card`) VALUES ('2', '4850340092677726', 'Alexis Turner');
INSERT INTO `Card` (`ID`, `Number`, `Name_On_Card`) VALUES ('3', '4255805492194796', 'Noah Jones');

INSERT INTO `Location` (`ID`, `Latitude`, `Longitude`, `Address`) VALUES ('1', '50.7234737', '-3.5319809999999734', 'The Guildhall, High Street, Exeter, UK');
INSERT INTO `Location` (`ID`, `Latitude`, `Longitude`, `Address`) VALUES ('2', '50.7218301', '-3.5348527999999533', 'St Nicholas Priory, Fore Street, Exeter, UK');
INSERT INTO `Location` (`ID`, `Latitude`, `Longitude`, `Address`) VALUES ('3', '50.722478', '-3.5373180000000275', 'St Bartholomew Cemetery, Exe Street, Exeter, UK');

/* Populate table with foreign keys */
INSERT INTO `Customer_Locations_Join` (`ID`, `Customer_ID`, `Location_ID`, `Card`) VALUES ('1', '2', '1', '2');
INSERT INTO `Customer_Locations_Join` (`ID`, `Customer_ID`, `Location_ID`, `Card`) VALUES ('2', '1', '3', '1');
INSERT INTO `Customer_Locations_Join` (`ID`, `Customer_ID`, `Location_ID`, `Card`) VALUES ('3', '3', '2', '3');

INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('1', '1', '2');
INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('2', '1', '1');
INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('3', '1', '3');
INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('4', '3', '1');
INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('5', '3', '2');
INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('6', '2', '3');
INSERT INTO `Purchased_Cities` (`ID`, `Customer_ID`, `City_ID`) VALUES ('7', '2', '1');