DROP TABLE EMAWD_DB.Friend;
DROP TABLE EMAWD_DB.HighScore;
DROP TABLE EMAWD_DB.UserAnswer;
DROP TABLE EMAWD_DB.User_Interest_Linker;
DROP TABLE EMAWD_DB.QuestionAnswer;
DROP TABLE EMAWD_DB.QuizQuestion;
DROP TABLE EMAWD_DB.User;
DROP TABLE EMAWD_DB.Game;
DROP TABLE EMAWD_DB.Quiz;
DROP TABLE EMAWD_DB.Interest;

CREATE TABLE EMAWD_DB.Interest (
       interestID INT NOT NULL AUTO_INCREMENT
     , interest VARCHAR(40) NOT NULL
     , PRIMARY KEY (interestID)
)TYPE=InnoDB;

CREATE TABLE EMAWD_DB.Quiz (
       quizID INT NOT NULL AUTO_INCREMENT
     , quizName VARCHAR(255) NOT NULL
     , description TEXT
     , PRIMARY KEY (quizID)
)TYPE=InnoDB;

CREATE TABLE EMAWD_DB.Game (
       gameID INT NOT NULL AUTO_INCREMENT
     , name VARCHAR(255) NOT NULL
     , PRIMARY KEY (gameID)
)TYPE=InnoDB;

CREATE TABLE EMAWD_DB.User (
       userID INT NOT NULL AUTO_INCREMENT
     , userName VARCHAR(50) NOT NULL
     , firstName VARCHAR(50)
     , lastName VARCHAR(50)
     , PRIMARY KEY (userID)
)TYPE=InnoDB;

CREATE TABLE EMAWD_DB.QuizQuestion (
       quizQuestionID INT NOT NULL AUTO_INCREMENT
     , question TEXT NOT NULL
     , questionNumber SMALLINT NOT NULL
     , quizID INT NOT NULL
     , PRIMARY KEY (quizQuestionID)
     , INDEX (quizID)
     , CONSTRAINT FK_QuizQuestion_1 FOREIGN KEY (quizID)
                  REFERENCES EMAWD_DB.Quiz (quizID)
)TYPE=InnoDB;

CREATE TABLE EMAWD_DB.QuestionAnswer (
       questionAnswerID INT NOT NULL AUTO_INCREMENT
     , answer VARCHAR(255) NOT NULL
     , answerLetter CHAR(1)
     , quizQuestionID INT NOT NULL
     , PRIMARY KEY (questionAnswerID)
     , INDEX (quizQuestionID)
     , CONSTRAINT FK_QuestionAnswer_1 FOREIGN KEY (quizQuestionID)
                  REFERENCES EMAWD_DB.QuizQuestion (quizQuestionID)
)TYPE=InnoDB;

CREATE TABLE EMAWD_DB.User_Interest_Linker (
       userID INT NOT NULL
     , interestID INT NOT NULL
     , PRIMARY KEY (userID, interestID)
     , INDEX (userID)
     , CONSTRAINT FK_User_Interest_Linker_1 FOREIGN KEY (userID)
                  REFERENCES EMAWD_DB.User (userID)
     , INDEX (interestID)
     , CONSTRAINT FK_User_Interest_Linker_2 FOREIGN KEY (interestID)
                  REFERENCES EMAWD_DB.Interest (interestID)
)TYPE=InnoDB;

CREATE TABLE EMAWD_DB.UserAnswer (
       userID INT NOT NULL
     , questionAnswerID INT NOT NULL
     , PRIMARY KEY (userID, questionAnswerID)
     , INDEX (userID)
     , CONSTRAINT FK_UserAnswer_1 FOREIGN KEY (userID)
                  REFERENCES EMAWD_DB.User (userID)
     , INDEX (questionAnswerID)
     , CONSTRAINT FK_UserAnswer_2 FOREIGN KEY (questionAnswerID)
                  REFERENCES EMAWD_DB.QuestionAnswer (questionAnswerID)
)TYPE=InnoDB;

CREATE TABLE EMAWD_DB.HighScore (
       userID INT NOT NULL
     , gameID INT NOT NULL
     , highScore INT NOT NULL
     , PRIMARY KEY (userID, gameID)
     , INDEX (gameID)
     , CONSTRAINT FK_User_Game_Linker_1 FOREIGN KEY (gameID)
                  REFERENCES EMAWD_DB.Game (gameID)
     , INDEX (userID)
     , CONSTRAINT FK_User_Game_Linker_2 FOREIGN KEY (userID)
                  REFERENCES EMAWD_DB.User (userID)
)TYPE=InnoDB;

CREATE TABLE EMAWD_DB.Friend (
       userID1 INT NOT NULL
     , userID2 INT NOT NULL
     , PRIMARY KEY (userID1, userID2)
     , INDEX (userID1)
     , CONSTRAINT FK_Friend_1 FOREIGN KEY (userID1)
                  REFERENCES EMAWD_DB.User (userID)
     , INDEX (userID2)
     , CONSTRAINT FK_Friend_2 FOREIGN KEY (userID2)
                  REFERENCES EMAWD_DB.User (userID)
)TYPE=InnoDB;