CREATE DATABASE IF NOT EXISTS databasen;
USE databasen;

CREATE TABLE IF NOT EXISTS qmtbl_users(user_id INT NOT NULL AUTO_INCREMENT, username TEXT, password TEXT, registration_date DATETIME, last_seen DATETIME, image_mime TEXT, image_data MEDIUMBLOB, realname TEXT, email TEXT, PRIMARY KEY (user_id));

CREATE TABLE IF NOT EXISTS qmtbl_quizes(quiz_id INT NOT NULL AUTO_INCREMENT, owner_user_id INT, public_quiz BOOL, title TEXT, creation_date DATETIME, last_finished DATETIME, times_finished INT, PRIMARY KEY (quiz_id), FOREIGN KEY (owner_user_id) REFERENCES qmtbl_users (user_id));

CREATE TABLE IF NOT EXISTS qmtbl_images(image_id INT NOT NULL AUTO_INCREMENT, image_md5 TEXT, image_mime TEXT, image_data MEDIUMBLOB, PRIMARY KEY (image_id));

CREATE TABLE IF NOT EXISTS qmtbl_questions(question_id INT NOT NULL AUTO_INCREMENT, quiz_id INT, question TEXT, comment TEXT, image_index INT, question_type ENUM('qt_written_answer', 'qt_multiple_choice'), PRIMARY KEY (question_id), FOREIGN KEY (quiz_id) REFERENCES qmtbl_quizes (quiz_id), FOREIGN KEY (image_index) REFERENCES qmtbl_images (image_index));