-- View-backed tables index script
-- Database: attendance_db
-- Purpose: Add indexes only for synced view tables used by active queries.
-- Usage in MySQL Workbench:
--   1) Run Block A to create indexes.
--   2) If rollback is needed, run Block B to drop the same indexes.


/* =========================================================
   Block A: Create indexes (copy-paste and run)
   ========================================================= */

USE `su_attendance_db_prod`;

-- PS_S_PRD_EX_RME_VW: join/filter by EXAM_DT, CATALOG_NBR; joins by EMPLID.
CREATE INDEX `idx_ex_rme_emplid` ON `PS_S_PRD_EX_RME_VW` (`EMPLID`);
CREATE INDEX `idx_ex_rme_examdt_room_catalog_strm` ON `PS_S_PRD_EX_RME_VW` (`EXAM_DT`, `ROOM_NBR`, `CATALOG_NBR`, `STRM`);
CREATE INDEX `idx_ex_rme_examdt_catalog_strm` ON `PS_S_PRD_EX_RME_VW` (`EXAM_DT`, `CATALOG_NBR`, `STRM`);

CREATE INDEX `idx_ex_rme_emplid` ON `PS_S_PRD_EX_RME_VW__stage` (`EMPLID`);
CREATE INDEX `idx_ex_rme_examdt_room_catalog_strm` ON `PS_S_PRD_EX_RME_VW__stage` (`EXAM_DT`, `ROOM_NBR`, `CATALOG_NBR`, `STRM`);
CREATE INDEX `idx_ex_rme_examdt_catalog_strm` ON `PS_S_PRD_EX_RME_VW__stage` (`EXAM_DT`, `CATALOG_NBR`, `STRM`);


-- PS_S_PRD_EX_TME_VW: join/filter by EXAM_DT, CATALOG_NBR; sort by EXAM_START_TIME.
CREATE INDEX `idx_ex_tme_examdt_starttime` ON `PS_S_PRD_EX_TME_VW` (`EXAM_DT`, `EXAM_START_TIME`);
CREATE INDEX `idx_ex_tme_examdt_catalog_strm_start` ON `PS_S_PRD_EX_TME_VW` (`EXAM_DT`, `CATALOG_NBR`, `STRM`, `EXAM_START_TIME`);
CREATE INDEX `idx_ex_tme_catalog_strm_type` ON `PS_S_PRD_EX_TME_VW` (`CATALOG_NBR`, `STRM`, `EXAM_TYPE_CD`);

CREATE INDEX `idx_ex_tme_examdt_starttime` ON `PS_S_PRD_EX_TME_VW__stage` (`EXAM_DT`, `EXAM_START_TIME`);
CREATE INDEX `idx_ex_tme_examdt_catalog_strm_start` ON `PS_S_PRD_EX_TME_VW__stage` (`EXAM_DT`, `CATALOG_NBR`, `STRM`, `EXAM_START_TIME`);
CREATE INDEX `idx_ex_tme_catalog_strm_type` ON `PS_S_PRD_EX_TME_VW__stage` (`CATALOG_NBR`, `STRM`, `EXAM_TYPE_CD`);


-- PS_S_PRD_STDNT_VW: join by EMPLID.
CREATE INDEX `idx_stdnt_emplid` ON `PS_S_PRD_STDNT_VW` (`EMPLID`);

CREATE INDEX `idx_stdnt_emplid` ON `PS_S_PRD_STDNT_VW__stage` (`EMPLID`);


-- PS_S_ADMSBJEXCPRC: lookup by CATALOG_NBR, STRM, S_ADM_TMPL.
CREATE INDEX `idx_admsbj_catalog_strm_tmpl` ON `PS_S_ADMSBJEXCPRC` (`CATALOG_NBR`, `STRM`, `S_ADM_TMPL`);

CREATE INDEX `idx_admsbj_catalog_strm_tmpl` ON `PS_S_ADMSBJEXCPRC__stage` (`CATALOG_NBR`, `STRM`, `S_ADM_TMPL`);


-- PS_S_PRD_CLS_VW: lookup by EMPLID + CATALOG_NBR, then STRM check.
CREATE INDEX `idx_cls_emplid_catalog_strm` ON `PS_S_PRD_CLS_VW` (`EMPLID`, `CATALOG_NBR`, `STRM`);
CREATE INDEX `idx_cls_students_practical_class` ON `PS_S_PRD_CLS_VW` (`CATALOG_NBR`, `STRM`, `CLASS_NBR`, `SSR_COMPONENT`);

CREATE INDEX `idx_cls_emplid_catalog_strm` ON `PS_S_PRD_CLS_VW__stage` (`EMPLID`, `CATALOG_NBR`, `STRM`);
CREATE INDEX `idx_cls_students_practical_class` ON `PS_S_PRD_CLS_VW__stage` (`CATALOG_NBR`, `STRM`, `CLASS_NBR`, `SSR_COMPONENT`);


-- PS_S_PRD_CT_ATT_VW: join/filter by EMPLID + STRM and latest row by S_TRANS_DT_TM.
CREATE INDEX `idx_ctatt_emplid_strm` ON `PS_S_PRD_CT_ATT_VW` (`EMPLID`, `STRM`);
CREATE INDEX `idx_ctatt_emplid_catalog_strm_transdt_desc` ON `PS_S_PRD_CT_ATT_VW` (`EMPLID`, `CATALOG_NBR`, `STRM`, `S_TRANS_DT_TM` DESC);

CREATE INDEX `idx_ctatt_emplid_strm` ON `PS_S_PRD_CT_ATT_VW__stage` (`EMPLID`, `STRM`);
CREATE INDEX `idx_ctatt_emplid_catalog_strm_transdt_desc` ON `PS_S_PRD_CT_ATT_VW__stage` (`EMPLID`, `CATALOG_NBR`, `STRM`, `S_TRANS_DT_TM` DESC);


-- PS_S_PRD_TRS_AT_VW: join/filter by EMPLID + STRM, plus exam type filter S_ADM_TMPL.
CREATE INDEX `idx_trsat_emplid_strm_tmpl` ON `PS_S_PRD_TRS_AT_VW` (`EMPLID`, `STRM`, `S_ADM_TMPL`);

CREATE INDEX `idx_trsat_emplid_strm_tmpl` ON `PS_S_PRD_TRS_AT_VW__stage` (`EMPLID`, `STRM`, `S_ADM_TMPL`);


CREATE INDEX `idx_inv_duty_employee_date_shift_room` ON `tbl_invigilator_duty` (`employeeId`, `date`, `shift`, `room`);

CREATE INDEX `idx_report_room_slot` ON `tbl_report_master` (`EXAM_DT`, `ROOM_NBR`, `EXAM_START_TIME`);
CREATE INDEX `idx_report_student_slot` ON `tbl_report_master` (`EMPLID`, `EXAM_DT`, `ROOM_NBR`, `EXAM_START_TIME`);
CREATE INDEX `idx_report_practical_class_latest` ON `tbl_report_master` (`EXAM_TYPE`, `CATALOG_NBR`, `STRM`, `ROOM_NBR`, `EXAM_DT`, `EMPLID`, `PK_Report_Id`);

CREATE INDEX `idx_setup_examdt_starttime` ON `tbl_setup_master` (`EXAM_DT`, `EXAM_START_TIME`);

CREATE INDEX `idx_photo_emplid` ON `tbl_photo_data` (`EMPLID`);

CREATE INDEX `idx_copy_reportid` ON `tbl_copy_master` (`FK_ReportId`);
CREATE INDEX `idx_copy_number` ON `tbl_copy_master` (`copyNumber`);


/* =========================================================
   Block B: Drop indexes (copy-paste and run for rollback)
   ========================================================= */

DELIMITER $$

DROP PROCEDURE IF EXISTS drop_index_if_exists $$

CREATE PROCEDURE drop_index_if_exists(IN p_table_name VARCHAR(128), IN p_index_name VARCHAR(128))
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE() AND table_name = p_table_name AND index_name = p_index_name
    LIMIT 1
  ) THEN
    SET @sql = CONCAT('DROP INDEX ', REPLACE(p_index_name, '', '``'), ' ON ', REPLACE(p_table_name, '', '``'), '');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END $$

DELIMITER ;

CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW', 'idx_ctatt_emplid_catalog_strm_transdt');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW', 'idx_ctatt_emplid_strm');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW', 'idx_ctatt_emplid_catalog_strm_transdt_desc');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW', 'idx_ct_att_main');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW__stage', 'idx_emplid');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW__stage', 'idx_strm');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW__stage', 'idx_catalog_nbr');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW__stage', 'idx_component');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW__stage', 'idx_ctatt_emplid_catalog_strm_transdt');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW__stage', 'idx_ctatt_emplid_strm');
CALL drop_index_if_exists('PS_S_PRD_CT_ATT_VW__stage', 'idx_ctatt_emplid_catalog_strm_transdt_desc');

CALL drop_index_if_exists('PS_S_PRD_EX_TME_VW', 'idx_ex_tme_examdt_catalog');
CALL drop_index_if_exists('PS_S_PRD_EX_TME_VW', 'idx_ex_tme_examdt_starttime');
CALL drop_index_if_exists('PS_S_PRD_EX_TME_VW', 'idx_ex_tme_examdt_catalog_strm_start');
CALL drop_index_if_exists('PS_S_PRD_EX_TME_VW', 'idx_ex_tme_catalog_strm_type');
CALL drop_index_if_exists('PS_S_PRD_EX_TME_VW', 'idx_exam_time');
CALL drop_index_if_exists('PS_S_PRD_EX_TME_VW__stage', 'idx_ex_tme_examdt_starttime');
CALL drop_index_if_exists('PS_S_PRD_EX_TME_VW__stage', 'idx_ex_tme_examdt_catalog_strm_start');
CALL drop_index_if_exists('PS_S_PRD_EX_TME_VW__stage', 'idx_ex_tme_catalog_strm_type');


CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW', 'idx_ex_rme_examdt_room_ptp');
CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW', 'idx_ex_rme_examdt_catalog');
CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW', 'idx_ex_rme_emplid');
CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW', 'idx_ex_rme_examdt_room_catalog_strm');
CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW', 'idx_ex_rme_examdt_catalog_strm');
CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW__stage', 'idx_ex_rme_emplid');
CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW__stage', 'idx_ex_rme_examdt_room_catalog_strm');
CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW__stage', 'idx_ex_rme_examdt_catalog_strm');
CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW__stage', 'idx_ex_rme_examdt_catalog');
CALL drop_index_if_exists('PS_S_PRD_EX_RME_VW__stage', 'idx_ex_rme_examdt_room_ptp');

CALL drop_index_if_exists('PS_S_PRD_STDNT_VW', 'idx_stdnt_emplid');
CALL drop_index_if_exists('PS_S_PRD_STDNT_VW', 'idx_emplid');
CALL drop_index_if_exists('PS_S_PRD_STDNT_VW', 'idx_strm');
CALL drop_index_if_exists('PS_S_PRD_STDNT_VW', 'idx_acad_prog');
CALL drop_index_if_exists('PS_S_PRD_STDNT_VW', 'idx_acad_plan');
CALL drop_index_if_exists('PS_S_PRD_STDNT_VW', 'idx_email');
CALL drop_index_if_exists('PS_S_PRD_STDNT_VW__stage', 'idx_stdnt_emplid');

CALL drop_index_if_exists('PS_S_ADMSBJEXCPRC', 'idx_admsbj_catalog_strm_tmpl');
CALL drop_index_if_exists('PS_S_ADMSBJEXCPRC__stage', 'idx_admsbj_catalog_strm_tmpl');

CALL drop_index_if_exists('PS_S_PRD_CLS_VW', 'idx_cls_emplid_catalog_strm');
CALL drop_index_if_exists('PS_S_PRD_CLS_VW__stage', 'idx_cls_emplid_catalog_strm');
CALL drop_index_if_exists('PS_S_PRD_CLS_VW', 'idx_cls_students_practical_class');
CALL drop_index_if_exists('PS_S_PRD_CLS_VW__stage', 'idx_cls_students_practical_class');

CALL drop_index_if_exists('PS_S_PRD_TRS_AT_VW', 'idx_trsat_emplid_strm_tmpl');
CALL drop_index_if_exists('PS_S_PRD_TRS_AT_VW', 'idx_trs_att_main');
CALL drop_index_if_exists('PS_S_PRD_TRS_AT_VW__stage', 'idx_emplid');
CALL drop_index_if_exists('PS_S_PRD_TRS_AT_VW__stage', 'idx_strm');
CALL drop_index_if_exists('PS_S_PRD_TRS_AT_VW__stage', 'idx_from_to_date');
CALL drop_index_if_exists('PS_S_PRD_TRS_AT_VW__stage', 'idx_trsat_emplid_strm_tmpl');

CALL drop_index_if_exists('PS_SU_PSFT_COEM_VW__stage', 'idx_emplid');
CALL drop_index_if_exists('PS_SU_PSFT_COEM_VW__stage', 'idx_deptid');
CALL drop_index_if_exists('PS_SU_PSFT_COEM_VW__stage', 'idx_email');

CALL drop_index_if_exists('tbl_invigilator_duty', 'idx_inv_duty_employee_date_shift_room');

CALL drop_index_if_exists('tbl_report_master', 'idx_report_room_slot');
CALL drop_index_if_exists('tbl_report_master', 'idx_report_student_slot');
CALL drop_index_if_exists('tbl_report_master', 'idx_report_practical_class_latest');
CALL drop_index_if_exists('tbl_report_master', 'idx_exam_dt');
CALL drop_index_if_exists('tbl_report_master', 'idx_pk_report_id');
CALL drop_index_if_exists('tbl_report_master', 'idx_report_exam');

CALL drop_index_if_exists('tbl_setup_master', 'idx_setup_examdt_starttime');

CALL drop_index_if_exists('tbl_photo_data', 'idx_photo_emplid');

CALL drop_index_if_exists('tbl_copy_master', 'idx_copy_reportid');
CALL drop_index_if_exists('tbl_copy_master', 'idx_copy_number');
CALL drop_index_if_exists('tbl_copy_master', 'idx_fk_report_id');
CALL drop_index_if_exists('tbl_copy_master', 'idx_copy_report');
CALL drop_index_if_exists('tbl_copy_master', 'idx_ct_att_main');
CALL drop_index_if_exists('tbl_copy_master', 'idx_ct_att_main');

CALL drop_index_if_exists('tbl_jobs', 'idx_job_name');
CALL drop_index_if_exists('tbl_jobs', 'idx_status');
CALL drop_index_if_exists('tbl_jobs', 'idx_is_running');
CALL drop_index_if_exists('tbl_jobs', 'idx_next_run');

CALL drop_index_if_exists('tbl_job_logs', 'idx_job_id');
CALL drop_index_if_exists('tbl_job_logs', 'idx_run_id');
CALL drop_index_if_exists('tbl_job_logs', 'idx_status');
CALL drop_index_if_exists('tbl_job_logs', 'idx_view_name');
CALL drop_index_if_exists('tbl_job_logs', 'idx_started_at');
DROP PROCEDURE IF EXISTS drop_index_if_exists;
