 
 -- reparer
 	
-CREATE TABLE USER
+CREATE TABLE user
 (
 	user_id INTEGER AUTO_INCREMENT NOT NULL,
     user_firstname TEXT NOT NULL,

VALUES (2, 1), (2, 7), (2, 16),
	(3, 1),(3, 5), (3, 7), (3, 10),(3,16);



-- PB with next functions
-- SELECT GET_RESULT_STUDENT_QUESTIONNAIRE(2, 1);
-- SELECT GET_RESULT_STUDENT_QUESTIONNAIRE(3, 1);
-- SELECT QUESTIONNAIRE_WEIGHTING(1);
    
	question_hint TEXT,
    question_weight enum('1', '2', '3', '4', '5') NOT NULL DEFAULT 1,
    PRIMARY KEY (question_id),
    FOREIGN KEY (question_questionnaire_id) REFERENCES questionnaire(questionnaire_id) ON DELETE CASCADE
);

CREATE TABLE choice
(
	choice_id INTEGER AUTO_INCREMENT NOT NULL,
    choice_question_id INTEGER NOT NULL,
    choice_content varchar(255) NULL,
    choice_status BOOLEAN NOT NULL,
    PRIMARY KEY (choice_id),
    FOREIGN KEY (choice_question_id) REFERENCES question(question_id) ON DELETE CASCADE
);

CREATE TABLE answer
(
	answer_id INTEGER AUTO_INCREMENT NOT NULL,
    answer_student_user_id INTEGER NOT NULL,
    answer_choice_id INTEGER NOT NULL,
    PRIMARY KEY (answer_id),
    FOREIGN KEY (answer_student_user_id) REFERENCES user(user_id) ON DELETE CASCADE, 
    FOREIGN KEY (answer_choice_id) REFERENCES choice(choice_id) ON DELETE CASCADE
);


-- INDEX


CREATE INDEX user_EMAIL_INDEX ON user(user_email);
CREATE INDEX user_PASSWORD_INDEX ON user(user_password);



INSERT INTO user (user_firstname, user_lastname, user_email, user_photo_path, user_schoolname, user_password)
