INSERT INTO users ( email, password, username, verified, registered ) VALUES ( 'admin@readiness-survey.com', '$2y$10$vlI84qALdCrUwbKJR0z.x.yAE0.G5E6e145Oinj.MoeJ8v0/r.21e', 'admin', '1', '1638743822' );
INSERT INTO users_roles ( user_id, role_id) VALUES ( '1', '1');

CREATE TABLE global ( site_enabled INT DEFAULT 1, cron_enabled INT DEFAULT 0, email_queue INT DEFAULT 0);
INSERT INTO global (site_enabled, cron_enabled, email_queue) VALUES (1,0,0);

CREATE TABLE users_roles ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, role_id INT NOT NULL );

CREATE TABLE roles ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title varchar(1000) NOT NULL, is_admin INT DEFAULT 0 );
INSERT INTO roles (title, is_admin) VALUES ('admin', '1');

alter table users_roles add column status INT DEFAULT 0;

CREATE TABLE courses ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title varchar(600), enabled INT DEFAULT 0 );

CREATE TABLE surveys (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title varchar(600), enabled INT DEFAULT 0, img varchar(1000) DEFAULT NULL, display_content varchar(1000) DEFAULT NULL, randomized INT DEFAULT 0, end_content varchar(100) DEFAULT NULL)

CREATE TABLE questions ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, handle varchar(1000) NOT NULL, question_text varchar(1000) DEFAULT NULL, answer INT NOT NULL, response_text varchar(1000) DEFAULT NULL, result_text varchar(1000) DEFAULT NULL, order_by INT NOT NULL  );
-- question_text
-- response_text
-- results_text
-- -- These will be a url to a txt file

ALTER TABLE questions ADD COLUMN enabled INT DEFAULT 0;

CREATE TABLE courses_surveys ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, course_id INT NOT NULL, survey_id INT NOT NULL );

CREATE TABLE surveys_questions ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, survey_id INT NOT NULL, question_id INT NOT NULL );

CREATE TABLE questions ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, handle varchar(1000) NOT NULL, question_text varchar(1000) DEFAULT NULL, answer INT NOT NULL, response_text varchar(10000) DEFAULT NULL, result_text varchar(10000) DEFAULT NULL, order_by INT NOT NULL  );

CREATE TABLE responses ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, survey_id INT NOT NULL, complete INT DEFAULT 0 );

CREATE TABLE responses_questions ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, response_id INT NOT NULL, question_id INT NOT NULL, confidence INT NOT NULL );

CREATE TABLE questions_answers ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, question_id INT NOT NULL, answer_text varchar(1000) DEFAULT NULL );

ALTER TABLE questions_answers MODIFY COLUMN answer_text text DEFAULT NULL;

ALTER TABLE responses ADD COLUMN user_id int NOT NULL;

ALTER TABLE responses_questions ADD COLUMN answer_id INT NOT NULL;

-- composer require dompdf/dompdf
-- sudo apt install php8.2-dom

ALTER TABLE responses_questions ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

-- +-------------+---------------+------+-----+---------+----------------+
-- | Field       | Type          | Null | Key | Default | Extra          |
-- +-------------+---------------+------+-----+---------+----------------+
-- | id          | int           | NO   | PRI | NULL    | auto_increment |
-- | recipient   | varchar(255)  | NO   |     | NULL    |                |
-- | content     | varchar(9000) | YES  |     | NULL    |                |
-- | sent        | int           | NO   |     | 0       |                |
-- | timestamp   | int           | YES  |     | NULL    |                |
-- | error       | varchar(255)  | YES  |     | NULL    |                |
-- | insert_time | int           | YES  |     | NULL    |                |
-- | importance  | int           | YES  |     | 0       |                |
-- | subject     | varchar(255)  | NO   |     | NULL    |                |
-- | send_at     | int           | NO   |     | 0       |                |
-- +-------------+---------------+------+-----+---------+----------------+


CREATE TABLE email_queue ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, recipient varchar(255) NOT NULL, content varchar(9000) NOT NULL, sent int DEFAULT 0, timestamp int DEFAULT NULL, error varchar(255) DEFAULT NULL, insert_time int DEFAULT NULL, importance INT DEFAULT 0, subject varchar(255) NOT NULL, send_at int NOT NULL );

CREATE TABLE regions ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title varchar(1000) NOT NULL );
CREATE TABLE locations ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, title varchar(1000) NOT NULL );
CREATE TABLE regions_locations ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, region_id INT NOT NULL, location_id INT NOT NULL);
ALTER TABLE users ADD COLUMN region INT DEFAULT NULL;
ALTER TABLE users ADD COLUMN location INT DEFAULT NULL;

ALTER TABLE roles ADD COLUMN can_reports INT DEFAULT 0;
insert into roles ( title, can_reports ) values ( 'view reports', 1);
ALTER TABLE responses_questions ADD COLUMN duration int DEFAULT 0;

# MISSING extra_info table creation
