DROP DATABASE IF EXISTS quiz_db;

CREATE DATABASE quiz_db;

USE quiz_db;

CREATE TABLE users(username varchar(20), pw_hash varchar(72), email varchar(100), country varchar(2), profilePic BOOLEAN, PRIMARY KEY(username));

CREATE TABLE user_record(username varchar(20),
	totalQuestions INT, totalPoints INT,
	totalQuizzes INT, totalHosted INT,
	totalCorrect INT, totalIncorrect INT,
	geoCorrect INT, geoIncorrect INT,
	hisCorrect INT, hisIncorrect INT,
	spoCorrect INT, spoIncorrect INT,
	tecCorrect INT, tecIncorrect INT,
	filCorrect INT, filIncorrect INT,
	musCorrect INT, musIncorrect INT,
	sciCorrect INT, sciIncorrect INT,
	genCorrect INT, genIncorrect INT,
	totalWins INT, totalLosses INT,
	oneVOneWins INT, oneVOneLosses INT, oneVOnePoints INT,
	elimWins INT, elimLosses INT, elimPoints INT,
	battleWins INT, battleLosses INT, battlePoints INT,
	towWins INT, towLosses INT, towPoints INT,
	PRIMARY KEY(username)
);

CREATE TABLE quiz(id varchar(8), host varchar(20), public BOOLEAN, type ENUM('elim', 'bat', '1v1', 'tow'), category ENUM('geo', 'his', 'spo', 'tec', 'fil', 'mus', 'sci', 'gen'), num_questions INT, questions TEXT, PRIMARY KEY (id));

CREATE TABLE question(id INT AUTO_INCREMENT, category ENUM('geo', 'his', 'spo', 'tec', 'fil', 'mus', 'sci', 'gen'), difficulty ENUM('e', 'm', 'h'), q TEXT, a1 varchar(100), a2 varchar(100), a3 varchar(100), a4 varchar(100), correctAns varchar(100), PRIMARY KEY(id));

INSERT INTO users(username, pw_hash, email, country, profilePic) VALUES('p1', '$2a$01$i6cRJVKmWm4CeM2C7oi6y.yfVNmKn8SwgqfDWk6AhScmk7j2.pUmK', 'p1@mail.com', 'gb', FALSE),  ('p2', '$2a$01$ntgOlOvFm3BDcmG8astEsOMuzd7hKtO3Wdc1nA0ZUh6Bg8zZ8tTc2', 'p2@mail.com', 'ro',FALSE), ('p3', '$2a$01$oxxeElsilNNLqc2w13Lg6.1r9K0U1bGdIKfLCkqfZaeIP8jFSgffu', 'p3@mail.com', 'br', FALSE), ('p4', '$2a$01$PSxYBp./ek4EE/tzq8Q12ubeIMCEV2p6t/NryoWxwpwGfUhfUo0vS', 'p4@mail.com', 'es', FALSE), ('p5', '$2a$01$oMeGFaXCv1sZt4PT22sr4.8RvetMWymevxb6mQ2RCjfHhzaJ29XfC', 'p5@mail.com', 'jp', FALSE);