DROP DATABASE IF EXISTS fcst_timetable_db;
CREATE DATABASE fcst_timetable_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fcst_timetable_db;

CREATE TABLE course_lecturers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 dept VARCHAR(100) NULL,
 lecturer_name VARCHAR(255) NULL,
 adm_position VARCHAR(255) NULL,
 tenure VARCHAR(100) NULL,
 programme VARCHAR(255) NULL,
 module VARCHAR(255) NULL,
 cohort VARCHAR(100) NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 INDEX idx_lecturer (lecturer_name),
 INDEX idx_cohort (cohort)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE criteria (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 criteria_no INT NULL,
 description TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE timetable_entries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 lecturer_name VARCHAR(255) NULL,
 programme VARCHAR(255) NULL,
 cohort VARCHAR(100) NULL,
 module VARCHAR(255) NULL,
 session_type ENUM('Lecture','Lab') DEFAULT 'Lecture',
 day VARCHAR(20) NULL,
 start_time TIME NULL,
 end_time TIME NULL,
 room VARCHAR(100) NULL,
 status VARCHAR(50) DEFAULT 'Generated',
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 INDEX idx_day_time (day,start_time,end_time),
 INDEX idx_room (room),
 INDEX idx_lecturer_time (lecturer_name,day,start_time,end_time),
 INDEX idx_cohort_time (cohort,day,start_time,end_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE attendance_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 timetable_entry_id BIGINT UNSIGNED,
 lecturer_name VARCHAR(255) NULL,
 scan_in_at DATETIME NULL,
 scan_out_at DATETIME NULL,
 status ENUM('IN','OUT') DEFAULT 'IN',
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL,
 FOREIGN KEY (timetable_entry_id) REFERENCES timetable_entries(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
