CREATE TABLE admin_user (
    user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    address VARCHAR(255) DEFAULT NULL,
    contact VARCHAR(50) DEFAULT NULL,
    user_type VARCHAR(50) NOT NULL DEFAULT 'admin',
    status TINYINT NOT NULL DEFAULT '1'
);

CREATE TABLE admin_refresh_tokens (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL references admin_user(user_id),
  refresh_token text DEFAULT NULL,
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

create table question (
question_id int primary key auto_increment,
question_title text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
question_subtitle text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
question_text text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
question_image varchar(255) default null,
question_audio varchar(255) default null,
question_set_id int references question_set(question_set_id),
status tinyint default 1,
created_by int not null,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

ALTER TABLE question MODIFY question_image text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE question MODIFY question_audio text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL;

create table options (
option_id int primary key auto_increment,
option_type varchar(45) not null,
option1 text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
option2 text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
option3 text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
option4 text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
correct_answer varchar(45) not null,
question_id int references question(question_id),
status tinyint default 1,
created_by int not null,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

create table question_set(
 question_set_id int primary key auto_increment,
 name varchar(255) not null,
 price double(50,2) default 0.00,
 expiry_duration varchar(50) not null,
 status tinyint default 1,
 created_by int not null,
 created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

create table score(
score_id int primary key auto_increment,
user_id int references user(user_id),
question_set_id int references question_set(question_set_id),
question_id int not null,
submitted_answer varchar(45) not null,
completion_time varchar(45) not null,
status tinyint default 1,
created_by int not null,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

create table package (
package_id int primary key auto_increment,
package_name varchar(255) not null,
question_set_id text not null,
price double(50,2) default 0.00,
status tinyint default 1,
created_by int not null,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

create table payment(
payment_id int primary key auto_increment,
user_id int references user(user_id),
payment_for varchar(255) not null,
question_set_id int references question_set(question_set_id),
package_id int references package(package_id),
payment_gateway varchar(255) not null,
payment_amount double(50,2) not null,
transaction_date date not null,
transaction_id varchar(255) not null,
payment_status boolean default true,
status tinyint default 1,
created_by int not null,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
)


CREATE TABLE user (
    user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    address VARCHAR(255) DEFAULT NULL,
    contact VARCHAR(50) DEFAULT NULL,
    image varchar(255) DEFAULT NULL,
    user_type VARCHAR(50) NOT NULL DEFAULT 'user',
    status TINYINT NOT NULL DEFAULT '1',
    created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE user_refresh_tokens (
  id int NOT NULL AUTO_INCREMENT,
  user_id int NOT NULL references user(user_id),
  refresh_token text DEFAULT NULL,
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
)

create table otp (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email varchar(255) NOT NULL,
  otp int NOT NULL,
  token varchar(255) NOT NULL,
  created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE book (
    book_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    book_name text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    file_name text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    status TINYINT NOT NULL DEFAULT '1',
    created_by INT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
)

CREATE TABLE inhouse_student_detail (
    stu_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    address VARCHAR(255) DEFAULT NULL,
    contact VARCHAR(50) DEFAULT NULL,
    status TINYINT NOT NULL DEFAULT '1',
    created_by INT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE exam_set_inhouse_student (
    set_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    set_title VARCHAR(255) NOT NULL,
    set_duration varchar(255) NOT NULL,
    status TINYINT NOT NULL DEFAULT '1',
    created_by INT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

create table question_inhouse_student (
question_id int primary key auto_increment,
question_title text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
question_subtitle text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
question_text text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
question_image text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
question_audio text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
set_id int references exam_set_inhouse_student(set_id),
status tinyint default 1,
created_by int not null,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

create table options_inhouse_student (
option_id int primary key auto_increment,
option_type varchar(45) not null,
option1 text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
option2 text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
option3 text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
option4 text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
correct_answer varchar(45) not null,
question_id int references question_inhouse_student(question_id),
status tinyint default 1,
created_by int not null,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

create table score_inhouse_student(
score_id int primary key auto_increment,
stu_id int references inhouse_student_detail(stu_id),
set_id int references exam_set_inhouse_student(set_id),
question_id int not null,
submitted_answer varchar(45) not null,
completion_time varchar(45) not null,
status tinyint default 1,
created_by int not null,
created_at timestamp NULL DEFAULT CURRENT_TIMESTAMP,
updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE live_exam_inhouse_student (
    exam_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    exam_code VARCHAR(255) NOT NULL,
    exam_title VARCHAR(255) NOT NULL,
    exam_date_ad date NOT NULL,
    exam_time time NOT NULL,
    set_id int references exam_set_inhouse_student(set_id),
    status TINYINT NOT NULL DEFAULT '1',
    created_by INT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);



ALTER TABLE `score` 
ADD INDEX `fk_score_1_idx` (`user_id` ASC),
ADD INDEX `fk_score_2_idx` (`question_set_id` ASC);
;
ALTER TABLE `score` 
ADD CONSTRAINT `fk_score_1`
  FOREIGN KEY (`user_id`)
  REFERENCES `user` (`user_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
ADD CONSTRAINT `fk_score_2`
  FOREIGN KEY (`question_set_id`)
  REFERENCES `question_set` (`question_set_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;
  
  ALTER TABLE `score` 
ADD INDEX `fk_score_3_idx` (`question_id` ASC);
;
ALTER TABLE `score` 
ADD CONSTRAINT `fk_score_3`
  FOREIGN KEY (`question_id`)
  REFERENCES `question` (`question_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;
  
  ALTER TABLE `options` 
ADD INDEX `fk_options_1_idx` (`question_id` ASC);
;
ALTER TABLE `options` 
ADD CONSTRAINT `fk_options_1`
  FOREIGN KEY (`question_id`)
  REFERENCES `question` (`question_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;
  
  ALTER TABLE `options` 
ADD INDEX `index3` USING BTREE (`correct_answer`);
;

ALTER TABLE `score` 
ADD INDEX `index5` USING BTREE (`submitted_answer`);
;
ALTER TABLE `score` 
ADD INDEX `index6` USING BTREE (`created_at`);
;

ALTER TABLE `payment` 
ADD INDEX `fk_payment_1_idx` (`user_id` ASC),
ADD INDEX `fk_payment_2_idx` (`question_set_id` ASC);
;
ALTER TABLE `payment` 
ADD CONSTRAINT `fk_payment_1`
  FOREIGN KEY (`user_id`)
  REFERENCES `user` (`user_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
ADD CONSTRAINT `fk_payment_2`
  FOREIGN KEY (`question_set_id`)
  REFERENCES `question` (`question_id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;
  
  ALTER TABLE `payment` 
ADD INDEX `index4` USING BTREE (`payment_status`);
;

ALTER TABLE `payment` 
ADD INDEX `index5` USING BTREE (`created_at`);
;

ALTER TABLE `question_set` 
ADD INDEX `index2` (`status` ASC);
;



CREATE TABLE access_control_info (
    id INT AUTO_INCREMENT PRIMARY KEY,
    access_for VARCHAR(255) NOT NULL,
    package_id INT REFERENCES package (package_id),
    question_set_id INT NOT NULL REFERENCES question_set (question_set_id),
    inhouse_student_id TEXT NOT NULL,
    status TINYINT DEFAULT 1,
    created_by int not null,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
)



ALTER TABLE score_inhouse_student ADD CONSTRAINT fk_score_inhouse_student_stu_id FOREIGN KEY(stu_id) REFERENCES inhouse_student_detail(stu_id);

ALTER TABLE score_inhouse_student ADD CONSTRAINT fk_score_inhouse_student_set_id FOREIGN KEY(set_id) REFERENCES exam_set_inhouse_student(set_id);

ALTER TABLE score_inhouse_student ADD CONSTRAINT fk_score_inhouse_student_question_id FOREIGN KEY(question_id) REFERENCES question_inhouse_student(question_id);

ALTER TABLE score_inhouse_student ADD CONSTRAINT fk_score_inhouse_student_created_by FOREIGN KEY(created_by) REFERENCES inhouse_student_detail(stu_id);




ALTER TABLE options_inhouse_student ADD CONSTRAINT fk_options_inhouse_student_question_id FOREIGN KEY(question_id) REFERENCES question_inhouse_student(question_id);

ALTER TABLE options_inhouse_student ADD CONSTRAINT fk_options_inhouse_student_created_by FOREIGN KEY(created_by) REFERENCES admin_user(user_id);

ALTER TABLE inhouse_student_detail ADD CONSTRAINT fk_inhouse_student_detail_created_by FOREIGN KEY(created_by) REFERENCES admin_user(user_id);


  ALTER TABLE `options_inhouse_student` 
ADD INDEX `correct_answer_index` USING BTREE (`correct_answer`);
;

ALTER TABLE `score_inhouse_student` 
ADD INDEX `submitted_answer_index` USING BTREE (`submitted_answer`);
;


ALTER TABLE book ADD COLUMN book_type varchar(55) NOT NULL DEFAULT 'PDF';

CREATE TABLE fcm_tokens(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token TEXT NOT NULL,
	  status TINYINT DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);
ALTER TABLE fcm_tokens ADD CONSTRAINT fk_fcm_tokens_user FOREIGN KEY (user_id) REFERENCES user(user_id);


CREATE TABLE book_info (
    book_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    book_name TEXT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL,
    status TINYINT DEFAULT 1,
    created_by INT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE chapter_info (
    chapter_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    book_id INT NOT NULL,
    chapter_name TEXT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL,
    status TINYINT DEFAULT 1,
    created_by INT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

ALTER TABLE chapter_info ADD CONSTRAINT fk_chpater_info_book_id FOREIGN KEY (book_id) REFERENCES book_info(book_id);

CREATE TABLE book_file_info (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    chapter_id INT NOT NULL,
    file_name TEXT CHARACTER SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI NOT NULL,
    file_type VARCHAR(55) NOT NULL,
    status TINYINT DEFAULT 1,
    created_by INT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
);

ALTER TABLE book_file_info ADD CONSTRAINT fk_book_file_info_chapter_id FOREIGN KEY (chapter_id) REFERENCES chapter_info(chapter_id);
