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 users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 email VARCHAR(255) NOT NULL UNIQUE,
 password VARCHAR(255) NOT NULL,
 role ENUM('superadmin','dean','lecturer') DEFAULT 'lecturer',
 lecturer_name VARCHAR(255) NULL,
 created_at TIMESTAMP NULL,
 updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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;

INSERT INTO users (name,email,password,role,lecturer_name,created_at,updated_at) VALUES
('Superadmin','admin@fcst.local','$2y$12$rLH/LzHmeWltv10oAyvoOes..B9pzpb3lSyAEee2uziI9pDPf5.3m','superadmin',NULL,NOW(),NOW()),
('Dean','dean@fcst.local','$2y$12$rLH/LzHmeWltv10oAyvoOes..B9pzpb3lSyAEee2uziI9pDPf5.3m','dean',NULL,NOW(),NOW());