CREATE TABLE phd_scholar (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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),

    proj_num VARCHAR(50),
    proj_name VARCHAR(255),
    proj_tenure VARCHAR(100),
    sponsored_agency_name VARCHAR(255),
    date_of_joining_as_per_memo DATE,

    dc_chair VARCHAR(255),
    supervisor VARCHAR(255),
    co_supervisor VARCHAR(255),
    dc_internal_member VARCHAR(255),
    dc_external_member VARCHAR(255),
    dc_additional_member_1 VARCHAR(255),
    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'),
    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'),
    form_submit_flag_compre_attempt_2 BOOLEAN DEFAULT 0,
    compre_exam_attempt_2_result_approval_dean BOOLEAN DEFAULT 0,

    date_of_aps_1 DATE,
    title_of_aps_1 VARCHAR(255),
    dc_comment_in_aps_1 TEXT,
    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 TEXT,
    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 TEXT,
    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 TEXT,
    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 TEXT,
    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 TEXT,
    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 TEXT,
    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,

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

    panel_of_examiners JSON, -- Stores a list of examiners
    form_submit_flag_panel_of_examiners BOOLEAN DEFAULT 0,
    name_of_external_examiner VARCHAR(255),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);