phd

CREATE TABLE phd_scholar (
id INT AUTO_INCREMENT PRIMARY KEY,

/*Auto Fetch based on Trigger Values*/
name_of_scholar VARCHAR(255) NOT NULL,
dept VARCHAR(255) NOT NULL,
roll VARCHAR(50) UNIQUE NOT NULL,
broad_research_area VARCHAR(255),
admission_month ENUM('January', 'June') NOT NULL,
gender ENUM('Male', 'Female', 'Other') NOT NULL,
category VARCHAR(100),
scholar_phd_category VARCHAR(100),
mobile VARCHAR(100),
/*Auto Fetch based on Trigger Values*/

/*Text Boxes Start*/
proj_num VARCHAR(50),
proj_name VARCHAR(255),
proj_tenure VARCHAR(100),
sponsored_agency_name VARCHAR(255),
/*Text Boxes End*/

date_of_joining_as_per_memo DATE,

dc_chair VARCHAR(255),
supervisor VARCHAR(255),
supervisor_emp_id INT,
co_supervisor VARCHAR(255),
co_supervisor_emp_id INT,
dc_internal_member_emp_id INT,
dc_internal_member VARCHAR(255),
dc_external_member_emp_id INT,
dc_external_member VARCHAR(255),
dc_additional_member_1_emp_id INT,
dc_additional_member_1 VARCHAR(255),
dc_additional_member_2_emp_id INT,
dc_additional_member_2 VARCHAR(255),
date_of_dc_formation DATE,
form_dc_submit_flag BOOLEAN DEFAULT 0,
dc_approval_dean BOOLEAN DEFAULT 0,

dc_course_work_fac_1 VARCHAR(255),
dc_course_work_fac_2 VARCHAR(255),
dc_course_work_fac_3 VARCHAR(255),
dc_course_work_fac_4 VARCHAR(255),
dc_course_work_fac_5 VARCHAR(255),
dc_course_work_fac_6 VARCHAR(255),
dc_course_work_fac_7 VARCHAR(255),
dc_course_work_fac_8 VARCHAR(255),
dc_course_work_fac_9 VARCHAR(255),
dc_course_work_fac_10 VARCHAR(255),

date_of_compre_attempt_1 DATE,
result_of_compre_attempt_1 ENUM('Pass', 'Fail'),
mode_of_compre_attempt_1 ENUM('Both', 'Oral', 'Written'), /*Take as input dropdown*/
form_submit_flag_compre_attempt_1 BOOLEAN DEFAULT 0,
compre_exam_attempt_1_result_approval_dean BOOLEAN DEFAULT 0,

date_of_compre_attempt_2 DATE,
result_of_compre_attempt_2 ENUM('Pass', 'Fail'),
mode_of_compre_attempt_2 ENUM('Both', 'Oral', 'Written'),  /*Take as input dropdown*/
form_submit_flag_compre_attempt_2 BOOLEAN DEFAULT 0,
compre_exam_attempt_2_result_approval_dean BOOLEAN DEFAULT 0,

date_of_reg_1 DATE,
title_of_reg_1 VARCHAR(255),
dc_comment_in_reg_1 VARCHAR(255),
result_of_reg_1 ENUM('Pass', 'Fail'),
form_submit_flag_reg_1 BOOLEAN DEFAULT 0,
reg_1_result_approval_dean BOOLEAN DEFAULT 0,

date_of_reg_2 DATE,
title_of_reg_2 VARCHAR(255),
dc_comment_in_reg_2 VARCHAR(255),
result_of_reg_2 ENUM('Pass', 'Fail'),
form_submit_flag_reg_2 BOOLEAN DEFAULT 0,
reg_2_result_approval_dean BOOLEAN DEFAULT 0,

date_of_enhancement DATE,
dc_comment_in_enhancement VARCHAR(255),
current_stipend_amount INT,
enhanced_stipend_amount INT,

date_of_aps_1 DATE,
title_of_aps_1 VARCHAR(255),
dc_comment_in_aps_1 VARCHAR(255),
result_of_aps_1 ENUM('Pass', 'Fail'),
form_submit_flag_aps_1 BOOLEAN DEFAULT 0,
aps_1_result_approval_dean BOOLEAN DEFAULT 0,

date_of_aps_2 DATE,
title_of_aps_2 VARCHAR(255),
dc_comment_in_aps_2 VARCHAR(255),
result_of_aps_2 ENUM('Pass', 'Fail'),
form_submit_flag_aps_2 BOOLEAN DEFAULT 0,
aps_2_result_approval_dean BOOLEAN DEFAULT 0,

date_of_aps_3 DATE,
title_of_aps_3 VARCHAR(255),
dc_comment_in_aps_3 VARCHAR(255),
result_of_aps_3 ENUM('Pass', 'Fail'),
form_submit_flag_aps_3 BOOLEAN DEFAULT 0,
aps_3_result_approval_dean BOOLEAN DEFAULT 0,

date_of_aps_4 DATE,
title_of_aps_4 VARCHAR(255),
dc_comment_in_aps_4 VARCHAR(255),
result_of_aps_4 ENUM('Pass', 'Fail'),
form_submit_flag_aps_4 BOOLEAN DEFAULT 0,
aps_4_result_approval_dean BOOLEAN DEFAULT 0,

date_of_aps_5 DATE,
title_of_aps_5 VARCHAR(255),
dc_comment_in_aps_5 VARCHAR(255),
result_of_aps_5 ENUM('Pass', 'Fail'),
form_submit_flag_aps_5 BOOLEAN DEFAULT 0,
aps_5_result_approval_dean BOOLEAN DEFAULT 0,

date_of_synopsis_attempt_1 DATE,
title_of_synopsis_attempt_1 VARCHAR(255),
dc_comment_in_synopsis_attempt_1 VARCHAR(255),
result_of_synopsis_attempt_1 ENUM('Pass', 'Fail'),
form_submit_flag_synopsis_attempt_1 BOOLEAN DEFAULT 0,
synopsis_attempt_1_result_approval_dean BOOLEAN DEFAULT 0,

date_of_synopsis_attempt_2 DATE,
title_of_synopsis_attempt_2 VARCHAR(255),
dc_comment_in_synopsis_attempt_2 VARCHAR(255),
result_of_synopsis_attempt_2 ENUM('Pass', 'Fail'),
form_submit_flag_synopsis_attempt_2 BOOLEAN DEFAULT 0,
synopsis_attempt_2_result_approval_dean BOOLEAN DEFAULT 0,

panel_of_examiners JSON, -- Stores a list of examiners
form_submit_flag_panel_of_examiners BOOLEAN DEFAULT 0,

date_of_thesis_submission DATE,
thesis_title VARCHAR(255),
form_submit_flag_thesis BOOLEAN DEFAULT 0,

name_of_external_examiner VARCHAR(255),
date_of_viva_voce DATE,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);