CREATE TABLE IF NOT EXISTS `course` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(500) NOT NULL,
	`description` varchar(4000) NOT NULL,
	`price` int NOT NULL,
	`is_progress_limited` tinyint NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `module` (
	`id` int AUTO_INCREMENT NOT NULL,
	`course_id` int NOT NULL,
	`name` varchar(500) NOT NULL,
	`number` int NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `lesson` (
	`id` int AUTO_INCREMENT NOT NULL,
	`module_id` int NOT NULL,
	`name` varchar(500) NOT NULL,
	`number` int NOT NULL,
	`video_url` varchar(500) NOT NULL,
	`lesson_details` varchar(4000) NOT NULL,
	`course_order` int NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `student` (
	`id` int AUTO_INCREMENT NOT NULL,
	`email_address` varchar(350) NOT NULL,
	`password` varchar(500) NOT NULL,
	`phone` varchar(15),
	`class` varchar(255),
	`is_active` bit(1) NOT NULL DEFAULT '1',
	`verified` bit(1) NOT NULL DEFAULT '1',
	`token` varchar(255),
	`created_at` timestamp NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `enrolment` (
	`course_id` int NOT NULL,
	`student_id` int NOT NULL,
	`enrolment_datetime` datetime NOT NULL,
	`completed_datetime` datetime NOT NULL
);

CREATE TABLE IF NOT EXISTS `student_lesson` (
	`student_id` int NOT NULL,
	`lesson_id` int NOT NULL,
	`completed_datetime` datetime NOT NULL
);

CREATE TABLE IF NOT EXISTS `quiz` (
	`id` int AUTO_INCREMENT NOT NULL,
	`course_id` int NOT NULL,
	`name` varchar(500) NOT NULL,
	`number` int NOT NULL,
	`course_order` int NOT NULL,
	`min_pass_score` int NOT NULL,
	`is_pass_required` tinyint NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `quiz_question` (
	`id` int AUTO_INCREMENT NOT NULL,
	`quiz_id` int NOT NULL,
	`question_title` varchar(500) NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `quiz_answer` (
	`id` int AUTO_INCREMENT NOT NULL,
	`question_id` int NOT NULL,
	`answer_text` varchar(500) NOT NULL,
	`is_correct` tinyint NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `student_quiz_attempt` (
	`student_id` int NOT NULL,
	`quiz_id` int NOT NULL,
	`attempt_datetime` datetime NOT NULL,
	`score_achieved` int NOT NULL
);


ALTER TABLE `module` ADD CONSTRAINT `module_fk1` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`);
ALTER TABLE `lesson` ADD CONSTRAINT `lesson_fk1` FOREIGN KEY (`module_id`) REFERENCES `module`(`id`);

ALTER TABLE `enrolment` ADD CONSTRAINT `enrolment_fk0` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`);
ALTER TABLE `student_lesson` ADD CONSTRAINT `student_lesson_fk0` FOREIGN KEY (`student_id`) REFERENCES `student`(`id`);
ALTER TABLE `quiz` ADD CONSTRAINT `quiz_fk1` FOREIGN KEY (`course_id`) REFERENCES `course`(`id`);
ALTER TABLE `quiz_question` ADD CONSTRAINT `quiz_question_fk1` FOREIGN KEY (`quiz_id`) REFERENCES `quiz`(`id`);
ALTER TABLE `quiz_answer` ADD CONSTRAINT `quiz_answer_fk1` FOREIGN KEY (`question_id`) REFERENCES `quiz_question`(`id`);
ALTER TABLE `student_quiz_attempt` ADD CONSTRAINT `student_quiz_attempt_fk0` FOREIGN KEY (`student_id`) REFERENCES `student`(`id`);