CREATE TABLE `attendance_db`.`PS_S_PRD_EX_TME_VW` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `EXAM_TIME_CODE` VARCHAR(8) NULL,
  `STRM` VARCHAR(4) NULL,
  `SESSION_CODE` VARCHAR(3) NULL,
  `EXAM_DT` DATETIME NULL,
  `EXAM_START_TIME` DATETIME(6) NULL,
  `EXAM_END_TIME` DATETIME(6) NULL,
  `CATALOG_NBR` VARCHAR(10) NULL,
  `DESCR100` VARCHAR(100) NULL,
  `EXAM_TYPE_CD` VARCHAR(3) NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `attendance_db`.`PS_S_PRD_EX_RME_VW` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `EMPLID` VARCHAR(11) NULL,
  `NAME` VARCHAR(60) NULL,
  `STRM` VARCHAR(4) NULL,
  `CATALOG_NBR` VARCHAR(10) NULL,
  `EXAM_DT` DATETIME NULL,
  `ROOM_NBR` VARCHAR(30) NULL,
  `PTP_SEQ_CHAR` VARCHAR(6) NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `attendance_db`.`PS_S_PRD_STDNT_VW` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `EMPLID` VARCHAR(11) NULL,
  `ADM_APPL_NBR` VARCHAR(8) NULL,
  `CAMPUS_ID` VARCHAR(16) NULL,
  `NAME_FORMAL` VARCHAR(60) NULL,
  `BUSINESS_UNIT` VARCHAR(5) NULL,
  `ACAD_GROUP` VARCHAR(5) NULL,
  `DESCR` VARCHAR(30) NULL,
  `ACAD_CAREER` VARCHAR(4) NULL,
  `DESCR1` VARCHAR(30) NULL,
  `ACAD_PROG` VARCHAR(5) NULL,
  `DESCR2` VARCHAR(30) NULL,
  `ACAD_PLAN` VARCHAR(10) NULL,
  `DESCR3` VARCHAR(30) NULL,
  `PROG_ACTION` VARCHAR(4) NULL,
  `DESCR5` VARCHAR(30) NULL,
  `PROG_STATUS` VARCHAR(4) NULL,
  `ADMIT_TERM` VARCHAR(4) NULL,
  `STRM` VARCHAR(4) NULL,
  `EFFDT` DATETIME NULL,
  `EMAIL_ADDR2` VARCHAR(70) NULL,
  `EMAIL_SUBJECT80` VARCHAR(70) NULL,
  `PHONE` VARCHAR(24) NULL,
  `SEX` VARCHAR(1) NULL,
  `BIRTHDATE` DATETIME NULL,
  `DESCR254` VARCHAR(309) NULL,
  `COUNTRY` VARCHAR(3) NULL,
  `DESCR6` VARCHAR(30) NULL,
  `STATE` VARCHAR(6) NULL,
  `DESCR7` VARCHAR(30) NULL,
  `CITY` VARCHAR(30) NULL,
  `DESCR8` VARCHAR(30) NULL,
  `ACAD_LEVEL_BOT` VARCHAR(3) NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `attendance_db`.`PS_S_ADMSBJEXCPRC` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `STRM` VARCHAR(4) NULL,
  `S_ADM_TMPL` VARCHAR(3) NULL,
  `CATALOG_NBR` VARCHAR(10) NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `attendance_db`.`PS_S_PRD_CLS_VW` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `EMPLID` VARCHAR(11) NULL,
  `NAME_DISPLAY` VARCHAR(50) NULL,
  `ACAD_CAREER` VARCHAR(4) NULL,
  `STRM` VARCHAR(4) NULL,
  `STDNT_ENRL_STATUS` VARCHAR(2) NULL,
  `CLASS_NBR` DECIMAL(38, 0) NULL,
  `CRSE_ID` VARCHAR(6) NULL,
  `CATALOG_NBR` VARCHAR(10) NULL,
  `SSR_COMPONENT` VARCHAR(3) NULL,
  `DESCR` VARCHAR(30) NULL,
  `ACAD_PROG` VARCHAR(5) NULL,
  `CLASS_SECTION` VARCHAR(4) NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `attendance_db`.`PS_S_PRD_CT_ATT_VW` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `EMPLID` VARCHAR(11) NULL,
  `STRM` VARCHAR(4) NULL,
  `STRM1` VARCHAR(4) NULL,
  `CATALOG_NBR` VARCHAR(10) NULL,
  `SSR_COMPONENT` VARCHAR(3) NULL,
  `PERCENTAGE` DECIMAL(5, 2) NULL,
  `S_NOC_STATUS` VARCHAR(10) NULL,
  `S_TRANS_DT_TM` DATETIME(6) NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `attendance_db`.`PS_S_PRD_TRS_AT_VW` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `EMPLID` VARCHAR(11) NULL,
  `STRM` VARCHAR(4) NULL,
  `S_ADM_TMPL` VARCHAR(3) NULL,
  `PERCENTCHG` DECIMAL(7, 2) NULL,
  `BALANCE_AMT` DECIMAL(16, 2) NULL,
  `BALANCE_REMAIN` DECIMAL(16, 2) NULL,
  `FROMDATE` DATETIME NULL,
  `TO_DATE` DATETIME NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `attendance_db`.`PS_SU_PSFT_COEM_VW` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `EMPLID` VARCHAR(11) NULL,
  `DISPLAY_NAME` VARCHAR(50) NULL,
  `HR_STATUS` VARCHAR(1) NULL,
  `BUSINESS_UNIT` VARCHAR(5) NULL,
  `DESCR` VARCHAR(30) NULL,
  `JOBCODE` VARCHAR(6) NULL,
  `DESCR1` VARCHAR(30) NULL,
  `DEPTID` VARCHAR(10) NULL,
  `DEPT_DESCR` VARCHAR(30) NULL,
  `EMAILID` VARCHAR(70) NULL,
  `EMAIL_ADDR` VARCHAR(70) NULL,
  `PHONE` VARCHAR(24) NULL,
  `PHONE1` VARCHAR(24) NULL,
  `MANAGER_ID` VARCHAR(11) NULL,
  `MANAGER_EMAIL` VARCHAR(70) NULL,
  `ADDRESS1` VARCHAR(55) NULL,
  `ADDRESS2` VARCHAR(55) NULL,
  `ADDRESS3` VARCHAR(55) NULL,
  `COUNTRY` VARCHAR(3) NULL,
  `COUNTRY_DESCR` VARCHAR(30) NULL,
  `STATE` VARCHAR(6) NULL,
  `STATE_DESCR` VARCHAR(30) NULL,
  `CITY` VARCHAR(30) NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `attendance_db`.`tbl_jobs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_name` VARCHAR(100) NOT NULL, -- CAMPUS_VIEWS_DAILY_SYNC / CAMPUS_VIEWS_INITIAL_LOAD / CAMPUS_VIEWS_MANUAL_SYNC
  `user_id` BIGINT UNSIGNED NULL, -- null if automatic, otherwise logged-in user id
  `source` VARCHAR(30) NULL, -- AUTOMATIC / MASTER_LOGIN / API / MANUAL
  `action` VARCHAR(50) NULL, -- UPDATE_ALL_VIEWS / UPDATE_SINGLE_VIEW / INITIAL_LOAD / RETRY_FAILED_VIEWS
  `is_running` TINYINT(1) NOT NULL DEFAULT 0,
  `status` VARCHAR(30) NOT NULL DEFAULT 'IDLE', -- IDLE / INITIATED / IN_PROGRESS / COMPLETED / PARTIAL_SUCCESS / FAILED
  `failure_reason_type` VARCHAR(50) NULL, -- SERVER_ERROR / SERVER_RESTART / LOCKED / UNKNOWN
  `current_view_name` VARCHAR(100) NULL,
  `views_total` INT NULL,
  `views_completed` INT NULL,
  `views_failed` INT NULL,
  `rows_total` BIGINT NULL,
  `rows_completed` BIGINT NULL,
  `next_run_at` DATETIME(3) NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
-- UNIQUE KEY `uq_tbl_jobs_job_name` (`job_name`)  do this at code level
);

CREATE TABLE `attendance_db`.`tbl_job_logs` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `job_id` BIGINT UNSIGNED NOT NULL,
  `run_id` VARCHAR(150) NOT NULL, -- unique id for one execution
  `job_name` VARCHAR(100) NOT NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `source` VARCHAR(30) NULL, -- AUTOMATIC / MASTER_LOGIN / API / MANUAL
  `action` VARCHAR(50) NULL, -- UPDATE_ALL_VIEWS / UPDATE_SINGLE_VIEW / INITIAL_LOAD / RETRY_FAILED_VIEWS
  `trigger_type` VARCHAR(30) NOT NULL, -- CRON / MANUAL / INITIAL_LOAD / RETRY
  `log_type` VARCHAR(30) NOT NULL, -- RUN / VIEW / SYSTEM
  `view_name` VARCHAR(100) NULL,
  `status` VARCHAR(30) NOT NULL, -- STARTED / IN_PROGRESS / SUCCESS / FAILED / PARTIAL_SUCCESS / SKIPPED
  `started_at` DATETIME(3) NULL,
  `ended_at` DATETIME(3) NULL,
  `duration_ms` BIGINT NULL,
  `fetch_duration_ms` BIGINT NULL,
  `insert_duration_ms` BIGINT NULL,
  `replace_duration_ms` BIGINT NULL,
  `rows_total` BIGINT NULL,
  `rows_fetched` BIGINT NULL,
  `rows_inserted` BIGINT NULL,
  `rows_failed` BIGINT NULL,
  `message` TEXT NULL,
  `error_message` LONGTEXT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS PSFT_CBT_EXAM_SETUP (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  TERM VARCHAR(20) NULL,
  TYPE_OF_EXAM VARCHAR(50) NULL,
  CATALOG_NBR VARCHAR(50) NULL,
  CLASS_NBR VARCHAR(50) NULL,
  COMPUTER_BASED_TEST VARCHAR(10) NULL,
  COMMENTS TEXT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS PSFT_CBT_EXAM_SETUP__stage LIKE PSFT_CBT_EXAM_SETUP;

CREATE TABLE IF NOT EXISTS PSFT_PRACTICAL_EXAM_UNFREEZE (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  TERM VARCHAR(20) NULL,
  CATALOG_NBR VARCHAR(50) NULL,
  CLASS_NBR VARCHAR(50) NULL,
  UNFREEZE_FROM_DATE VARCHAR(20) NULL,
  UNFREEZE_FROM_TIME VARCHAR(40) NULL,
  UNFREEZE_TILL_DATE VARCHAR(20) NULL,
  UNFREEZE_TILL_TIME VARCHAR(40) NULL,
  CATALOG_NBR_DESCR VARCHAR(255) NULL,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS PSFT_PRACTICAL_EXAM_UNFREEZE__stage LIKE PSFT_PRACTICAL_EXAM_UNFREEZE;

CREATE TABLE IF NOT EXISTS PSFT_PI_DETAILS (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  CATALOG_NBR VARCHAR(50) NULL,
  CRSE_ID VARCHAR(50) NULL,
  SUBJECT VARCHAR(50) NULL,
  DESCR VARCHAR(255) NULL,
  CLASS_NBR VARCHAR(50) NULL,
  CLASS_SECTION VARCHAR(50) NULL,
  STRM VARCHAR(20) NULL,
  EMPLID VARCHAR(50) NULL,
  INSTR_ROLE VARCHAR(50) NULL,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS PSFT_PI_DETAILS__stage LIKE PSFT_PI_DETAILS;

CREATE TABLE IF NOT EXISTS tbl_student_eligibility (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  eligibility_key VARCHAR(255) NOT NULL,
  STRM VARCHAR(4) NULL,
  EMPLID VARCHAR(11) NULL,
  CAMPUS_ID VARCHAR(16) NULL,
  NAME VARCHAR(60) NULL,
  CATALOG_NBR VARCHAR(10) NULL,
  EXAM_TYPE VARCHAR(20) NULL,
  EXAM_DT DATETIME NULL,
  EXAM_START_TIME DATETIME(6) NULL,
  ROOM_NBR VARCHAR(30) NULL,
  PTP_SEQ_CHAR VARCHAR(6) NULL,
  Attendece_Status VARCHAR(30) NOT NULL DEFAULT 'Not Defined',
  EXAM_TYPE_CD VARCHAR(3) NULL,
  DESCR VARCHAR(30) NULL,
  DESCR2 VARCHAR(30) NULL,
  DESCR3 VARCHAR(30) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_run_id VARCHAR(150) NULL,
  last_synced_at DATETIME(3) NULL,
  created_by VARCHAR(100) NULL,
  updated_by VARCHAR(100) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uq_student_eligibility_key (eligibility_key),
  KEY idx_student_eligibility_strm (STRM),
  KEY idx_student_eligibility_emplid (EMPLID),
  KEY idx_student_eligibility_catalog (CATALOG_NBR),
  KEY idx_student_eligibility_active (is_active),
  KEY idx_student_eligibility_exam_date (EXAM_DT)
);

CREATE TABLE `attendance_db`.`tbl_user_master` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(500) NOT NULL,
  `password` varchar(255) DEFAULT NULL,
  `name` varchar(500) DEFAULT NULL,
  `contact_number` varchar(15) DEFAULT NULL,
  `email_id` varchar(500) DEFAULT NULL,
  `profile_image_url` longtext,
  `otp` int DEFAULT NULL,
  `refreshToken` varchar(255) DEFAULT NULL,
  `isVerified` tinyint DEFAULT '0',
  `isActive` tinyint NOT NULL DEFAULT '1',
  `firstLoginStatus` tinyint DEFAULT '1',
  `status` varchar(100) DEFAULT 'Internal',
  `createdDate` datetime DEFAULT NULL,
  `updatedDate` datetime DEFAULT NULL,
  `isDeleted` tinyint NOT NULL DEFAULT '0',
  `isSuspended` tinyint DEFAULT '0',
  `createdBy` varchar(200) DEFAULT NULL,
  `updatedBy` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 787 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `attendance_db`.`tbl_role_master` (
  `PK_RoleId` int NOT NULL AUTO_INCREMENT,
  `roleName` varchar(225) DEFAULT NULL,
  `description` longtext,
  `isActive` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(200) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`PK_RoleId`)
) ENGINE = InnoDB AUTO_INCREMENT = 5 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `attendance_db`.`tbl_user_role_permission` (
  `PK_user_role_permissionId` int NOT NULL AUTO_INCREMENT,
  `FK_userId` int DEFAULT NULL,
  `FK_RoleId` int DEFAULT NULL,
  `isActive` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`PK_user_role_permissionId`)
) ENGINE = InnoDB AUTO_INCREMENT = 835 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `attendance_db`.`tbl_module_master` (
  `PK_ModuleId` int NOT NULL AUTO_INCREMENT,
  `moduleName` varchar(225) DEFAULT NULL,
  `description` longtext,
  `isActive` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(200) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`PK_ModuleId`)
) ENGINE = InnoDB AUTO_INCREMENT = 16 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `attendance_db`.`tbl_role_module_permission` (
  `PK_role_module_permissionId` int NOT NULL AUTO_INCREMENT,
  `FK_RoleId` int DEFAULT NULL,
  `FK_ModuleId` int DEFAULT NULL,
  `create` tinyint DEFAULT '0',
  `read` tinyint DEFAULT '0',
  `update` tinyint DEFAULT '0',
  `delete` tinyint DEFAULT '0',
  `special` tinyint DEFAULT '0',
  `isActive` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(200) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`PK_role_module_permissionId`)
) ENGINE = InnoDB AUTO_INCREMENT = 51 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `attendance_db`.`tbl_setup_master` (
  `PK_Setup_Id` int NOT NULL AUTO_INCREMENT,
  `EXAM_DT` varchar(100) DEFAULT NULL,
  `EXAM_START_TIME` varchar(100) DEFAULT NULL,
  `time_duration` int DEFAULT NULL,
  `isActive` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(200) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`PK_Setup_Id`),
  KEY `idx_setup_examdt_starttime` (`EXAM_DT`, `EXAM_START_TIME`)
) ENGINE = InnoDB AUTO_INCREMENT = 169 DEFAULT CHARSET = latin1;

CREATE TABLE `attendance_db`.`tbl_invigilator_duty` (
  `PK_InvigilatorDutyId` int NOT NULL AUTO_INCREMENT,
  `employeeId` varchar(500) DEFAULT NULL,
  `invigilatorName` varchar(500) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `shift` varchar(100) DEFAULT NULL,
  `room` varchar(100) DEFAULT NULL,
  `duty_status` varchar(100) DEFAULT NULL,
  `isActive` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(200) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`PK_InvigilatorDutyId`)
) ENGINE = InnoDB AUTO_INCREMENT = 12647 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `attendance_db`.`tbl_photo_data` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `EMPLID` varchar(45) DEFAULT NULL,
  `studentPic` varchar(225) DEFAULT NULL,
  `signPic` varchar(225) DEFAULT NULL,
  `isActive` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE = InnoDB AUTO_INCREMENT = 22496 DEFAULT CHARSET = latin1;

CREATE TABLE `attendance_db`.`tbl_report_master` (
  `PK_Report_Id` int NOT NULL AUTO_INCREMENT,
  `EMPLID` int DEFAULT NULL,
  `EXAM_DT` varchar(100) DEFAULT NULL,
  `EXAM_TYPE` varchar(45) DEFAULT NULL,
  `ROOM_NBR` varchar(100) DEFAULT NULL,
  `EXAM_START_TIME` varchar(100) DEFAULT NULL,
  `STRM` int DEFAULT NULL,
  `CATALOG_NBR` varchar(100) DEFAULT NULL,
  `PTP_SEQ_CHAR` int DEFAULT NULL,
  `NAME_FORMAL` varchar(500) DEFAULT NULL,
  `ADM_APPL_NBR` varchar(50) DEFAULT NULL,
  `DESCR` longtext,
  `DESCR2` longtext,
  `DESCR3` longtext,
  `SU_PAPER_ID` varchar(100) DEFAULT NULL,
  `DESCR100` longtext,
  `Attendece_Status` varchar(100) DEFAULT NULL,
  `Status` varchar(100) DEFAULT NULL,
  `EXAM_TYPE_CD` varchar(45) DEFAULT NULL,
  `studentPic` varchar(200) DEFAULT NULL,
  `signPic` varchar(200) DEFAULT NULL,
  `ufm_image_url` longtext,
  `isActive` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(200) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`PK_Report_Id`),
  KEY `idx_exam_dt` (`EXAM_DT`),
  KEY `idx_pk_report_id` (`PK_Report_Id`)
) ENGINE = InnoDB AUTO_INCREMENT = 213521 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `attendance_db`.`tbl_copy_master` (
  `PK_CopyId` int NOT NULL AUTO_INCREMENT,
  `FK_ReportId` int DEFAULT NULL,
  `EMPLID` int DEFAULT NULL,
  `copyNumber` varchar(100) DEFAULT NULL,
  `alternateCopyNumber1` varchar(100) DEFAULT NULL,
  `alternateCopyNumber2` varchar(100) DEFAULT NULL,
  `alternateCopyNumber3` varchar(100) DEFAULT NULL,
  `alternateCopyNumber4` varchar(100) DEFAULT NULL,
  `alternateCopyNumber5` varchar(100) DEFAULT NULL,
  `alternateCopyNumber6` varchar(100) DEFAULT NULL,
  `isActive` tinyint DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` varchar(200) DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`PK_CopyId`),
  KEY `idx_fk_report_id` (`FK_ReportId`),
  KEY `idx_copy_number` (`copyNumber`)
) ENGINE = InnoDB AUTO_INCREMENT = 175843 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

CREATE TABLE `attendance_db`.`cron_runs` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `cron_name` varchar(50) DEFAULT NULL,
  `status` enum('RUNNING','SUCCESS','FAILED') DEFAULT 'RUNNING',
  `is_running` tinyint(1) NOT NULL DEFAULT '0',
  `started_at` datetime DEFAULT NULL,
  `finished_at` datetime DEFAULT NULL,
  `params` json DEFAULT NULL,
  `triggered_by` varchar(30) DEFAULT NULL,
  `user_id` bigint DEFAULT NULL,
  `source` varchar(30) DEFAULT 'WEB',
  `total` int DEFAULT '0',
  `success` int DEFAULT '0',
  `skipped` int DEFAULT '0',
  `failed` int DEFAULT '0',
  `error_message` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_cron_running` (`is_running`,`status`),
  KEY `idx_cron_name_started` (`cron_name`,`started_at`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;

CREATE TABLE `attendance_db`.`cron_logs` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `cron_run_id` bigint DEFAULT NULL,
  `level` enum('INFO','WARN','ERROR') DEFAULT NULL,
  `message` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `cron_run_id` (`cron_run_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1523 DEFAULT CHARSET=latin1;

ALTER TABLE `attendance_db`.`cron_runs`
ADD COLUMN is_running TINYINT(1) NOT NULL DEFAULT 0 AFTER status,
ADD COLUMN user_id BIGINT NULL AFTER triggered_by,
ADD COLUMN source VARCHAR(30) DEFAULT 'WEB' AFTER user_id,
ADD COLUMN error_message TEXT NULL AFTER failed,
ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP AFTER error_message,
ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at,
ADD INDEX idx_cron_running (is_running, status),
ADD INDEX idx_cron_name_started (cron_name, started_at);

ALTER TABLE `attendance_db`.`cron_logs`
ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at;