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
;