USE SPRING;
CREATE TABLE IF NOT EXISTS USER( 
	USER_ID MEDIUMINT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS EVENT( 
	EVENT_ID MEDIUMINT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(255) NOT NULL, 
	DATE DATETIME NOT NULL
);

CREATE TABLE IF NOT EXISTS RANK( 
	RANK_ID MEDIUMINT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(255), 
	PRICE MEDIUMINT NOT NULL,
	EVENT_ID MEDIUMINT NOT NULL,
	FOREIGN KEY (EVENT_ID) REFERENCES EVENT (EVENT_ID)
);

CREATE TABLE IF NOT EXISTS TICKET( 
	TICKET_ID MEDIUMINT PRIMARY KEY AUTO_INCREMENT, 
	EVENT_ID MEDIUMINT NOT NULL, 
	RANK_ID MEDIUMINT NOT NULL, 
	FOREIGN KEY (EVENT_ID) REFERENCES EVENT (EVENT_ID), 
	FOREIGN KEY (RANK_ID) REFERENCES RANK (RANK_ID) 
);

CREATE TABLE IF NOT EXISTS RESERVATION( 
	RESERVATION_ID MEDIUMINT PRIMARY KEY, 
	USER_ID MEDIUMINT NOT NULL, 
	RESERVED_TS TIMESTAMP NOT NULL, 
	FOREIGN KEY (RESERVATION_ID) REFERENCES TICKET (TICKET_ID), 
	FOREIGN KEY (USER_ID) REFERENCES USER (USER_ID) 
);

DELETE FROM RESERVATION;

DELETE FROM TICKET;

DELETE FROM RANK;

DELETE FROM EVENT;

DELETE FROM USER;

LOAD DATA LOCAL INFILE 'user.csv' 
INTO TABLE USER 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n';

LOAD DATA LOCAL INFILE 'event.csv' 
INTO TABLE EVENT 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n';

LOAD DATA LOCAL INFILE 'rank.csv' 
INTO TABLE RANK 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n';

LOAD DATA LOCAL INFILE 'ticket.csv' 
INTO TABLE TICKET 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n';

LOAD DATA LOCAL INFILE 'reservation.csv' 
INTO TABLE RESERVATION 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n';

QUIT