DROP DATABASE quizsystem;
CREATE DATABASE quizsystem;
USE quizsystem;

/* Courses */
CREATE TABLE Course(
    name VARCHAR(200),
	description TEXT,
	courseData TEXT,
    PRIMARY KEY(name)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

CREATE TABLE Quiz(
    nr INTEGER,
    courseName VARCHAR(200),
	opening TIMESTAMP,
	closing TIMESTAMP,
    quizData TEXT,
	autoCorrected BOOLEAN,
	allowMultipleReplies BOOLEAN,
	quizURI VARCHAR(255),
    PRIMARY KEY(nr, courseName),
    FOREIGN KEY(courseName) REFERENCES Course(name) ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

CREATE TABLE QuizVariant(
    qVarNr INTEGER,
    quizNr INTEGER,
    quizCourseName VARCHAR(200),
    correctAnswer VARCHAR(255),
    quizObjectIDs TEXT,
    PRIMARY KEY(qVarNr, quizNr, quizCourseName),
    FOREIGN KEY(quizNr, quizCourseName) REFERENCES Quiz(nr, courseName) ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

CREATE TABLE QuizVariantObject(
    id VARCHAR(255),
    quizNr INTEGER,
    qVarNr INTEGER,
    quizCourseName VARCHAR(200),
    objectData TEXT,
    PRIMARY KEY(id, quizNr, qVarNr, quizCourseName),
    FOREIGN KEY(qVarNr, quizNr, quizCourseName) REFERENCES QuizVariant(qVarNr, quizNr, quizCourseName) ON UPDATE CASCADE
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

/* Logging of all student login attempts */
CREATE TABLE userLoginsLog(
    id INTEGER AUTO_INCREMENT,
    loginName VARCHAR(16),
    userAgent VARCHAR(1024), /*$_SERVER['HTTP_USER_AGENT']*/
    userIP VARCHAR(20), /*$_SERVER['REMOTE_ADDR']*/
    DateTime TIMESTAMP,
	success VARCHAR(25),
	courseName VARCHAR(100),
	courseOccasion VARCHAR(25),
    quizNr INTEGER,
    PRIMARY KEY(id)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

/* Logging of failed attempt to login to the backend */
CREATE TABLE logFailedUserLogins(
    id INTEGER AUTO_INCREMENT,
    userName VARCHAR(16),
    user	Type VARCHAR(10),
    userAgent VARCHAR(1024), /*$_SERVER['HTTP_USER_AGENT']*/
    userIP VARCHAR(20), /*$_SERVER['REMOTE_ADDR']*/
    DateTime TIMESTAMP,
	success VARCHAR(25),
    PRIMARY KEY(id)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;



/* Logging of successful logins to the backend */
CREATE TABLE userLoginsLog(
    id INTEGER AUTO_INCREMENT,
    loginName VARCHAR(16),
    userAgent VARCHAR(1024), /*$_SERVER['HTTP_USER_AGENT']*/
    userIP VARCHAR(20), /*$_SERVER['REMOTE_ADDR']*/
    DateTime TIMESTAMP,
	success VARCHAR(25),
    PRIMARY KEY(id)
) ENGINE=INNODB CHARACTER SET utf8 COLLATE utf8_swedish_ci;

INSERT INTO Course(name) VALUES('DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)');

INSERT INTO Quiz(nr, courseName, opening, closing, autoCorrected, allowMultipleReplies, quizURI, quizData) VALUES(1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','2012-08-19 00:00:00','2012-08-28 00:00:00',1,0,'','<div id="dugga"><h1>Dugga ett för en kurs</h1><p>Duggan ditten och duggan datten</p></div>');
INSERT INTO QuizVariant(quizNr, qVarNr, quizCourseName, correctAnswer, quizObjectIDs) VALUES(1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','1,2,3,4,5,6,7,8','TEST1 TEST2 TEST3 TEST4');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST1',1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST1"><p>Du ska göra bla bla...</p></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST2',1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST2"><strong>1 2 3 4 5</strong></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST3',1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("TEST3");</script>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST4',1,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("&lt;TEST4");</script>');

INSERT INTO QuizVariant(quizNr, qVarNr, quizCourseName, correctAnswer, quizObjectIDs) VALUES(1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','8,7,6,5,4,3,2,1','TEST1 TEST2 TEST3 TEST4');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST1',1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST1"><p>Variant 2 på dugga 1</p></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST2',1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST2"><strong>1111 2222 33333</strong></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST3',1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("TEST3 Var2");</script>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST4',1,2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("&lt;TEST4 Var2");</script>');

INSERT INTO Quiz(nr, courseName, opening, closing, autoCorrected, quizData) VALUES(2,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','2012-08-19 00:00:00','2012-08-28 00:00:00',0,'<div id="dugga"><h1>Dugga 2 för en kurs</h1><p>Duggan ditten och duggan datten</p></div>');
INSERT INTO QuizVariant(quizNr, qVarNr, quizCourseName, correctAnswer, quizObjectIDs) VALUES(2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','1,2,3,4,5,6,7,8','TEST1 TEST2 TEST3 TEST4');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST1',2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST1"><p>I dugga 2 variant 1 ska bla bla...</p></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST2',2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<div id="TEST2"><strong>--!!--!!--!!--</strong></div>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST3',2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("TEST3 d2 var1");</script>');
INSERT INTO QuizVariantObject(id, quizNr, qVarNr, quizCourseName, objectData) VALUES('TEST4',2,1,'DA133G Webbutveckling - datorgrafik G1N, 7,5hp (IKI)','<script lang="Javascript">alert("&lt;TEST4 d2 var1");</script>');


DELIMITER //
DROP PROCEDURE IF EXISTS CHECKLOGIN;
END;

;
	allowMultipleReplies BOOLEAN,
	quizURI VARCHAR(255),