CREATE TABLE tl_lamc11_content (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , content_id BIGINT(20) NOT NULL
     , title TEXT
     , instructions TEXT
     , creation_date DATETIME
     , update_date DATETIME
     , questions_sequenced TINYINT(1) NOT NULL DEFAULT 0
     , username_visible TINYINT(1) NOT NULL DEFAULT 0
     , created_by BIGINT(20) NOT NULL DEFAULT 0
     , monitoring_report_title TEXT NOT NULL
     , report_title TEXT NOT NULL
     , run_offline TINYINT(1) NOT NULL DEFAULT 0
     , define_later TINYINT(1) NOT NULL DEFAULT 0
     , synch_in_monitor TINYINT(1) NOT NULL DEFAULT 0
     , offline_instructions TEXT
     , online_instructions TEXT
     , end_learning_message TEXT NOT NULL
     , content_in_use TINYINT(1) NOT NULL DEFAULT 0
     , retries TINYINT(1) NOT NULL DEFAULT 0
     , show_feedback TINYINT(1) NOT NULL DEFAULT 0
     , pass_mark INTEGER
     , show_report TINYINT(1) NOT NULL DEFAULT 0
     , UNIQUE UQ_tl_lamc11_content_1 (content_id)
     , PRIMARY KEY (uid)
)TYPE=InnoDB;

CREATE TABLE tl_lamc11_que_content (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , question VARCHAR(255)
     , weight INT(5) NOT NULL DEFAULT 0
     , disabled TINYINT(1) NOT NULL DEFAULT 1
     , feedbackCorrect TEXT
     , feedbackIncorrect TEXT
     , display_order INT(5)
     , mc_content_id BIGINT(20) NOT NULL
     , PRIMARY KEY (uid)
     , INDEX (mc_content_id)
     , CONSTRAINT FK_tl_lamc11_que_content_1 FOREIGN KEY (mc_content_id)
                  REFERENCES tl_lamc11_content (uid)
)TYPE=InnoDB;

CREATE TABLE tl_lamc11_options_content (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , correct_option TINYINT(1) NOT NULL DEFAULT 0
     , mc_que_content_id BIGINT(20) NOT NULL
     , mc_que_option_text VARCHAR(250)
     , PRIMARY KEY (uid)
     , INDEX (mc_que_content_id)
     , CONSTRAINT FK_tl_lamc11_options_content_1 FOREIGN KEY (mc_que_content_id)
                  REFERENCES tl_lamc11_que_content (uid)
)TYPE=InnoDB;

CREATE TABLE tl_lamc11_session (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , mc_session_id BIGINT(20) NOT NULL
     , session_start_date DATETIME
     , session_end_date DATETIME
     , session_name VARCHAR(100)
     , session_status VARCHAR(100)
     , mc_content_id BIGINT(20) NOT NULL
     , UNIQUE UQ_tl_lamc11_session_1 (mc_session_id)
     , PRIMARY KEY (uid)
     , INDEX (mc_content_id)
     , CONSTRAINT FK_tl_lamc_session_1 FOREIGN KEY (mc_content_id)
                  REFERENCES tl_lamc11_content (uid)
)TYPE=InnoDB;

CREATE TABLE tl_lamc11_que_usr (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , que_usr_id BIGINT(20) NOT NULL
     , mc_session_id BIGINT(20) NOT NULL
     , username VARCHAR(100)
     , fullname VARCHAR(100)
     , PRIMARY KEY (uid)
     , INDEX (mc_session_id)
     , CONSTRAINT FK_tl_lamc11_que_usr_1 FOREIGN KEY (mc_session_id)
                  REFERENCES tl_lamc11_session (uid)
)TYPE=InnoDB;

CREATE TABLE tl_lamc11_usr_attempt (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , que_usr_id BIGINT(20) NOT NULL
     , mc_que_content_id BIGINT(20) NOT NULL
     , mc_que_option_id BIGINT(20) NOT NULL
     , attempt_time DATETIME
     , time_zone VARCHAR(255)
     , isAttemptCorrect TINYINT(1) NOT NULL DEFAULT 0
     , mark VARCHAR(255)
     , passed TINYINT(1) NOT NULL DEFAULT 0
     , attemptOrder INTEGER NOT NULL DEFAULT 1
     , finished TINYINT(1) DEFAULT 0
     , PRIMARY KEY (uid)
     , INDEX (mc_que_content_id)
     , CONSTRAINT FK_tl_lamc11_usr_attempt_2 FOREIGN KEY (mc_que_content_id)
                  REFERENCES tl_lamc11_que_content (uid)
     , INDEX (mc_que_option_id)
     , CONSTRAINT FK_tl_lamc11_usr_attempt_3 FOREIGN KEY (mc_que_option_id)
                  REFERENCES tl_lamc11_options_content (uid)
     , INDEX (que_usr_id)
     , CONSTRAINT FK_tl_lamc11_usr_attempt_4 FOREIGN KEY (que_usr_id)
                  REFERENCES tl_lamc11_que_usr (uid)
)TYPE=InnoDB;

CREATE TABLE tl_lamc11_uploadedfile (
       uid BIGINT(20) NOT NULL AUTO_INCREMENT
     , uuid VARCHAR(255) NOT NULL
     , mc_content_id BIGINT(20) NOT NULL
     , isOnline_File TINYINT(1) NOT NULL
     , filename VARCHAR(255) NOT NULL
     , PRIMARY KEY (uid)
     , INDEX (mc_content_id)
     , CONSTRAINT FK_tl_lamc11_uploadedFile FOREIGN KEY (mc_content_id)
                  REFERENCES tl_lamc11_content (uid)
)TYPE=InnoDB;


INSERT INTO tl_lamc11_content(uid, content_id , title, instructions, creation_date , questions_sequenced , username_visible , created_by , monitoring_report_title , report_title , run_offline , define_later, synch_in_monitor, offline_instructions, online_instructions, end_learning_message, content_in_use, retries, show_feedback, show_report, pass_mark) VALUES (1, ${default_content_id} , 'MCQ Title', 'MCQ Instructions', NOW(), 0, 0,1,'Monitoring Report','Report', 0, 0, 0,'offline instructions','online instructions','Finished Activity...', 0, 0, 0, 0, 50);

INSERT INTO tl_lamc11_que_content  (uid,question, weight, disabled, display_order,  mc_content_id, feedbackIncorrect, feedbackCorrect) VALUES (1, 'A Sample question?', 100, 0, 1,1, 'Your answer is incorrect', 'Correct');
	
INSERT INTO tl_lamc11_options_content (uid,  correct_option,  mc_que_content_id,  mc_que_option_text) VALUES (1, 0, 1,'Candidate Answer 1');
INSERT INTO tl_lamc11_options_content (uid,  correct_option,  mc_que_content_id,  mc_que_option_text) VALUES (2, 1, 1,'Candidate Answer 2');