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`)
);