ALTER TABLE `student_proposals` ADD `proposal_quality` INT(11) NULL DEFAULT NULL COMMENT '0=\'accept, 1=in-review,2-pause,3=rejected,4=intarested' AFTER `proposal_id`, ADD `concept` TINYINT(4) NOT NULL DEFAULT '1' AFTER `proposal_quality`, ADD `proposal_status` INT(11) NULL DEFAULT NULL AFTER `concept`, ADD `accepted_date` DATE NULL DEFAULT NULL AFTER `proposal_status`, ADD `proposal_status_date` DATE NULL DEFAULT NULL AFTER `accepted_date`, ADD `feedback_msg` VARCHAR(255) NULL DEFAULT NULL AFTER `proposal_status_date`;


CREATE TABLE `refer_users` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`refer_by` INT(11) NULL DEFAULT NULL,
	`teacher_id` INT(11) NULL DEFAULT NULL,
	`name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`email` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`signup` TINYINT(4) NULL DEFAULT '0',
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `research_interests` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`teacher_id` INT(11) NULL DEFAULT NULL,
	`research` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `student_proposals` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`student_id` INT(10) UNSIGNED NOT NULL,
	`student_apply_ref` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`teacher_id` INT(10) UNSIGNED NOT NULL,
	`proposal_id` INT(11) NULL DEFAULT NULL,
	`proposal_quality` INT(11) NULL DEFAULT NULL,
	`concept` TINYINT(4) NULL DEFAULT '1',
	`proposal_status` TINYINT(4) NULL DEFAULT NULL,
	`accepted_date` DATE NULL DEFAULT NULL,
	`proposal_status_date` DATE NULL DEFAULT NULL,
	`feedback_msg` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`start_year` INT(10) UNSIGNED NOT NULL,
	`start_month` INT(10) UNSIGNED NOT NULL,
	`created_by` INT(10) UNSIGNED NOT NULL,
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `universities` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`country_id` INT(11) NOT NULL,
	`name` VARCHAR(255) NOT NULL COLLATE 'latin1_swedish_ci',
	`email` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`web_address` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`uni_type` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`short_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
	`status` INT(11) NOT NULL DEFAULT '0',
	`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

CREATE TABLE `market_countries` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`country_id` INT(11) NOT NULL,
	`company_id` INT(11) NULL DEFAULT NULL,
	`status` INT(11) NOT NULL DEFAULT '1',
	`status_by` INT(11) NULL DEFAULT NULL,
	`added_by` INT(11) NULL DEFAULT NULL,
	`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

CREATE TABLE `proposal` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`student_id` INT(11) NOT NULL,
	`proposal_ref` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`title` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`methology` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`department` INT(11) NULL DEFAULT NULL,
	`status` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	`document_file` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`file_type` VARCHAR(8) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

ALTER TABLE `users`
	Add COLUMN `wishlist_teacher_id` INT(11) NULL DEFAULT NULL AFTER `user_type`;

ALTER TABLE `users`
	CHANGE COLUMN `last_name` `last_name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci' AFTER `first_name`;


ALTER TABLE `users`
	CHANGE COLUMN `password` `password` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci' AFTER `dob`;

ALTER TABLE `refer_users`
	ADD COLUMN `q_form_status` TINYINT(2) NULL DEFAULT '0' AFTER `email`;


ALTER TABLE `users`
	Add COLUMN `is_available` INT(11) NULL DEFAULT '0' AFTER `user_type`;

ALTER TABLE `users`
	ADD COLUMN `admin_invite_id` INT(11) NULL DEFAULT NULL AFTER `wishlist_teacher_id`;



ALTER TABLE `users`
	ADD COLUMN `registered_at` DATETIME NULL DEFAULT NULL AFTER `is_test_score`;


CREATE TABLE `requirements` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`std_proposal_id` INT(11) NULL DEFAULT NULL,
	`requirement_title` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`need_upload` TINYINT(4) NULL DEFAULT NULL,
	`created_by` INT(11) NULL DEFAULT NULL,
	`updated_by` INT(11) NULL DEFAULT NULL,
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

ALTER TABLE `users`
	ADD COLUMN `landing_register` TINYINT(4) NULL DEFAULT '0' AFTER `admin_invite_id`;

ALTER TABLE `users`
	ADD COLUMN `invitation_date` DATETIME NULL DEFAULT NULL AFTER `landing_register`,
	ADD COLUMN `registered_date` DATETIME NULL DEFAULT NULL AFTER `invitation_date`;


CREATE TABLE `publications` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`student_id` INT(11) NULL DEFAULT NULL,
	`publication` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`status` INT(10) NULL DEFAULT NULL,
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

ALTER TABLE `users`
	ADD COLUMN `is_attended_conference` INT(11) NULL DEFAULT NULL AFTER `admin_invite_id`;

ALTER TABLE `users`
	ADD COLUMN `is_publication` TINYINT(1) NULL DEFAULT NULL COMMENT '1=yes, 2=no' AFTER `is_test_score`;

ALTER TABLE `users`
	ADD COLUMN `is_education` TINYINT(1) NULL DEFAULT NULL COMMENT '1=yes, 2=no' AFTER `is_publication`;

ALTER TABLE `users`
	ADD COLUMN `freez_profile` TINYINT(4) NULL DEFAULT 0 AFTER `id`;

ALTER TABLE `users`
	CHANGE COLUMN `user_type` `user_type` TINYINT(4) NULL DEFAULT NULL COMMENT '1=student, 2=teacher' AFTER `save_as_complete`,
	CHANGE COLUMN `is_work_experience` `is_work_experience` TINYINT(4) NULL DEFAULT NULL COMMENT '1=yes, 2=no' AFTER `registered_date`,
	CHANGE COLUMN `is_test_score` `is_test_score` TINYINT(4) NULL DEFAULT NULL COMMENT '1=yes, 2=no' AFTER `is_work_experience`,
	CHANGE COLUMN `is_publication` `is_publication` TINYINT(4) NULL DEFAULT NULL COMMENT '1=yes, 2=no' AFTER `is_test_score`,
	CHANGE COLUMN `is_education` `is_education` TINYINT(4) NULL DEFAULT NULL COMMENT '1=yes, 2=no' AFTER `is_publication`,
	ADD COLUMN `sign_up` TINYINT(4) NULL DEFAULT '0' COMMENT '1=sign_up' AFTER `is_education`;

ALTER TABLE `users`
	ADD COLUMN `apply_status` TINYINT(4) NULL DEFAULT '0' COMMENT '1=active, 2=in-active, 3=suspend' AFTER `registered_date`;

ALTER TABLE `users`
	ADD COLUMN `register_type` TINYINT(4) NULL DEFAULT '0' COMMENT '1=invitation, 2=wishlist/landing, 3=q_form' AFTER `sign_up`;


ALTER TABLE `users`
	ADD COLUMN `university_id` INT(11) NULL DEFAULT NULL AFTER `university_name`;

ALTER TABLE `users`
	CHANGE COLUMN `department` `department` INT(11) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci' AFTER `university_id`;

ALTER TABLE `student_proposals`
	CHANGE COLUMN `start_year` `start_year` INT(10) UNSIGNED NULL DEFAULT NULL AFTER `proposal_id`,
	CHANGE COLUMN `start_month` `start_month` INT(10) UNSIGNED NULL DEFAULT NULL AFTER `start_year`,
	CHANGE COLUMN `created_by` `created_by` INT(10) UNSIGNED NULL DEFAULT NULL AFTER `start_month`


ALTER TABLE `student_proposals`
	ADD COLUMN `proposal_submit_status` TINYINT(4) NULL DEFAULT '0' AFTER `start_month`;

ALTER TABLE `student_proposals`
	ADD COLUMN `submitted_date` DATETIME NULL DEFAULT NULL AFTER `proposal_submit_status`;


ALTER TABLE `users`
	CHANGE COLUMN `register_type` `register_type` TINYINT(4) NULL DEFAULT '0' COMMENT '1=teacher_invitation, 2=teacher_wishlist/landing, 3=q-form,4=student_wishlist; 5= normal_register_student; 6= student_refer_friend' AFTER `sign_up`;

ALTER TABLE `users`
	CHANGE COLUMN `apply_status` `apply_status` TINYINT(4) NULL DEFAULT '0' COMMENT '1=approved, 2=declined, 3=suspend' AFTER `registered_date`;


ALTER TABLE `student_proposals`
	CHANGE COLUMN `concept` `concept` TINYINT(4) NULL DEFAULT '1' COMMENT '1=Good; 2=Averagre; 3=Poor' AFTER `proposal_quality`;
ALTER TABLE `student_proposals`
	CHANGE COLUMN `proposal_status` `proposal_status` TINYINT(4) NULL DEFAULT '1' COMMENT '1=New,2=Reviewing,3=Pause, 4=Hold, , 5=Interested ,6=Rejected' AFTER `concept`;

ALTER TABLE `student_proposals`
	ADD COLUMN `draft_status` TINYINT(4) NULL DEFAULT '0' COMMENT '1=drafy_q_from' AFTER `submitted_date`;

ALTER TABLE `users`
	ADD COLUMN `last_logged_timezone` VARCHAR(255) NULL DEFAULT NULL AFTER `register_type`;

ALTER TABLE `users`
	ADD COLUMN `last_logged_at` DATETIME NULL DEFAULT NULL AFTER `last_logged_timezone`;

ALTER TABLE `admins`
	ADD COLUMN `last_logged_at` DATETIME NULL DEFAULT NULL AFTER `last_logged_timezone`;

ALTER TABLE `users`
	CHANGE COLUMN `is_work_experience` `is_work_experience` TINYINT(4) NULL DEFAULT '0' COMMENT '1=yes, 2=no' AFTER `apply_status`,
	CHANGE COLUMN `is_test_score` `is_test_score` TINYINT(4) NULL DEFAULT '0' COMMENT '1=yes, 2=no' AFTER `is_work_experience`,
	CHANGE COLUMN `is_publication` `is_publication` TINYINT(4) NULL DEFAULT '0' COMMENT '1=yes, 2=no' AFTER `is_test_score`,
	CHANGE COLUMN `is_education` `is_education` TINYINT(4) NULL DEFAULT '0' COMMENT '1=yes, 2=no' AFTER `is_publication`;


ALTER TABLE `student_proposals`
	ADD COLUMN `preferred_start_date` DATE NULL DEFAULT NULL AFTER `proposal_id`;


ALTER TABLE `student_proposals`
	ADD COLUMN `feedback_reason` VARCHAR(255) NULL DEFAULT NULL AFTER `feedback_msg`;

CREATE TABLE `feedback_reasons` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`reasons` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	`status` TINYINT(4) NULL DEFAULT '1',
	`created_at` TIMESTAMP NULL DEFAULT NULL,
	`updated_at` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB 
;