--User (same from HW/project)

-- Quiz
-- 	id (primary key)
-- 	title

-- Question
-- 	id (primary key)
-- 	quiz_id (foreign key that matches id from Quiz)
-- 	question (the question itself)

-- Answers
-- 	id (primary key)
-- 	question_id (foreign key that matches id from Question)
-- 	answer (the answer itself)
-- 	is_correct (a boolean/something to indicate if it's correct or not)

-- User_Answers
-- 	id (primary key)
-- 	question_id (foreign key that matches the id from question)
-- 	answer_id (foreign key that matches the id from Answers)
-- DELETE FROM Users;
-- DELETE FROM Quiz;
-- DELETE FROM Question;
-- DELETE FROM Answers;

CREATE TABLE IF NOT EXISTS Users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    fname VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);
-- This will hold whatever quizzes are available.
CREATE TABLE IF NOT EXISTS Quiz (
    quiz_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    quiz_name VARCHAR(255) NOT NULL
);

CREATE TABLE IF NOT EXISTS Question (
    question_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    quiz_id INT UNSIGNED NOT NULL,
    question VARCHAR(255) NOT NULL,
    is_active ENUM('0','1'),

    FOREIGN KEY (quiz_id) REFERENCES Quiz(quiz_id)
);

CREATE TABLE IF NOT EXISTS Answers (
    answer_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    question_id INT UNSIGNED NOT NULL,
    answer VARCHAR(255) NOT NULL,
    is_correct ENUM('0', '1'),

    FOREIGN KEY (question_id) REFERENCES Question(question_id)
);

CREATE TABLE IF NOT EXISTS User_Answers (
    user_answer_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    question_id INT UNSIGNED NOT NULL,
    answer_id INT UNSIGNED NOT NULL,

    FOREIGN KEY (question_id) REFERENCES Question(question_id),
    FOREIGN KEY (answer_id) REFERENCES Answers(answer_id)
);

-- An initial test user that can be updated through a form.
-- I already have a user provided in the database and that should not change.
-- INSERT INTO Users(fname, email, username, password) 
-- VALUES ('testfname', 'test@test.com', 'test', 'testpassword');

-- Create a Quiz name first.
INSERT INTO Quiz(quiz_name) VALUES ('The Ultimate Test');

-- Now add questions. Going to start with the first quiz.
INSERT INTO Question(quiz_id, question, is_active) VALUES 
(1, "What was the name of the first web browser created?", '1'),
(1, "How many NBA Championships has Michael Jordan won?", '1'),
(1, "Which of these books are NOT written by author, Toni Morrison?", '1'),
(1, "What is the first album released from The Beatles", '1'),
(1, "What year was the original Xbox game console released?", '1'),
(1, "What Nike shoe is worn by Forrest Gump in the movie, Forrest Gump?", '1'),
(1, "What date is the same as the Ides of March in the Roman Calendar?", '1'),
(1, "What philosopher had written the socratic dialogue, The Republic?", '1'),
(1, "Which year is the release year of the original MacOS?", '1'),
(1, "Who has the most top ten singles on the Billboard 100 chart?", '1');

-- Next Quiz, Going to only do this one 5 questions.

INSERT INTO Quiz(quiz_name) VALUES ('The Test');

-- Adding Questions to the Second Quiz

INSERT INTO Question(quiz_id, question, is_active) VALUES 
(2, "Which of the games were NOT published by video game publisher, Activision?", '1'),
(2, "Which person is the president of Valve Software?", '1'),
(2, "Which of these artist is currently still alive?", '1'),
(2, "In the television medical drame, House M.D, what is the first name of Dr. House?", '1'),
(2, "What waas the first FPS (First Person Shooter) video game created by Id Software?", '1');

-- Last Quiz

INSERT INTO Quiz(quiz_name) VALUES ('The Quiz');

-- Adding last questions.

INSERT INTO Question(quiz_id, question, is_active) VALUES
(3,"Which movie is one of Kanye West's favorite moves of all time?", '1'),
(3, "Which country did the US receive the Statue of Liberty from?", '1'),
(3, "In what city and state was rapper, 21 Savage, born?", '1'),
(3, "Which of these movies does NOT have actor, Christian Bale, as the main role?", '1'),
(3, "Which one of these companies develops dedicated Graphical Processing Units (GPUs) as well as CPUs?", '1');

-- Now I want to update the possible answers for these Questions.

-- At the moment, it is an empty set but it will have a few choices tied to every question.
-- Going to have 4 choices for each question where only one is correct.
INSERT INTO Answers(question_id, answer, is_correct) VALUES 
(1, "WorldWideWeb", '1'),
(1, "Internet Explorer", '0'),
(1, "Mosiac", '0'),
(1, "Line Mode Browser", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(2, "5", '0'),
(2, "3", '0'),
(2, "7", '0'),
(2, "6", '1');

INSERT INTO Answers(question_id,answer,is_correct) VALUES
(3, "Beloved", '0'),
(3, "Sula", '0'),
(3, "Tar Baby", '0'),
(3, "War", '1');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(4, "A Hard Day's Night", '0'),
(4, "Beatles for Sale", '0'),
(4, "Please Please Me", '1'),
(4, "With The Beatles", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(5, "2001", '1'),
(5, '2000', '0'),
(5, '2002', '0'),
(5, '2005', '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(6, "Nike Air Jordan 1", '0'),
(6, "Nike Tailwind", '0'),
(6, "Nike Cortez", '1'),
(6, "Nike Blazer", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES 
(7, "March 1", '0'),
(7, "March 27", '0'),
(7, "March 20", '0'),
(7, "March 14", '1');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(8, "Socrates", '0'),
(8, "Plato", '1'),
(8, "Aristotle", '0'),
(8, "Marcus Aurelius", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(9, "1982", '0'),
(9, "1986", '0'),
(9, "1984", '1'),
(9, "1985", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(10, "Michael Jackson", '0'),
(10, "Madonna", '1'),
(10, "Drake", '0'),
(10, "The Beatles", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(11, "Call of Duty", '0'),
(11, "Sekiro: Shadows Die Twice", '0'),
(11, "Pitfall!", '0'),
(11, "None of the Above", '1');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(12, "Mike Harrington", '0'),
(12, "John Carmack", '0'),
(12, "Gabe Newell", '1'),
(12, "Sid Meier", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(13, "Keith Haring", '0'),
(13, "Salvador Dali", '0'),
(13, "Barbara Kruger", '1'),
(13, "Jean Michel Basquiat", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(14, "Gary", '0'),
(14, "Gregory", '1'),
(14, "Eric", '0'),
(14, "James", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(15, "Doom", '0'),
(15, "Wolfenstein", '1'),
(15, "Half-Life", '0'),
(15, "Quake", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(16, "Akira", '1'),
(16, "Spirited Away", '0'),
(16, "Wreck It Ralph", '0'),
(16, "American Psycho", '1');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(17, "Germany", '0'),
(17, "Spain", '0'),
(17, "France", '1'),
(17, "United Kingdom", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(18, "Atlanta, Georgia", '0'),
(18, "London, England", '1'),
(18, "New York City, New York", '0'),
(18, "Miami, Florida", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(19, "American Psycho", '0'),
(19, "Nightcrawler", '1'),
(19, "Batman Begins", '0'),
(19, "The Machinist", '0');

INSERT INTO Answers(question_id, answer, is_correct) VALUES
(20, "Intel", '0'),
(20, "AMD", '1'),
(20, "IBM", '0'),
(20, "Microsoft", '0');