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

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('super_admin','dean','lecturer') DEFAULT 'lecturer',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE students (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  student_id VARCHAR(50) NOT NULL UNIQUE,
  full_name VARCHAR(180) NOT NULL,
  programme VARCHAR(180),
  semester VARCHAR(50),
  email VARCHAR(150),
  phone VARCHAR(50),
  face_profile VARCHAR(255),
  status ENUM('active','inactive') DEFAULT 'active',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE subjects (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subject_code VARCHAR(50) NOT NULL UNIQUE,
  subject_name VARCHAR(200) NOT NULL,
  programme VARCHAR(180),
  lecturer_id BIGINT UNSIGNED NULL,
  status ENUM('active','inactive') DEFAULT 'active',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE venues (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  venue_code VARCHAR(50) NOT NULL UNIQUE,
  venue_name VARCHAR(180) NOT NULL,
  venue_type ENUM('classroom','computer_lab') NOT NULL,
  capacity INT DEFAULT 0,
  location VARCHAR(180),
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE class_sessions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subject_id BIGINT UNSIGNED NOT NULL,
  venue_id BIGINT UNSIGNED NOT NULL,
  lecturer_id BIGINT UNSIGNED NULL,
  session_title VARCHAR(200) NOT NULL,
  session_type ENUM('classroom','lab') NOT NULL,
  session_date DATE NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  qr_token VARCHAR(100) NOT NULL UNIQUE,
  status ENUM('open','closed') DEFAULT 'open',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_session_date (session_date),
  FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
  FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE attendances (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  class_session_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  attendance_datetime DATETIME NOT NULL,
  status ENUM('present','late','manual') DEFAULT 'present',
  verification_method ENUM('face','qr_manual') NOT NULL,
  face_image VARCHAR(255),
  qr_token_used VARCHAR(100),
  remarks TEXT,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_session_student (class_session_id, student_id),
  INDEX idx_attendance_datetime (attendance_datetime),
  FOREIGN KEY (class_session_id) REFERENCES class_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (name,email,password,role) VALUES
('Dean FCST','dean@fcuc.edu.my','$2y$10$0lbzOcJ2LgQQ97xlMp9qVuwX8ANmNE6GlRqL9HWmR1aZQysqMlxG6','super_admin'),
('FCST Lecturer','lecturer@fcuc.edu.my','$2y$10$0lbzOcJ2LgQQ97xlMp9qVuwX8ANmNE6GlRqL9HWmR1aZQysqMlxG6','lecturer');
-- Password for demo users: admin123 (change after first login)

INSERT INTO students (student_id, full_name, programme, semester, email, phone) VALUES
('FCST001','Joel Wong','Bachelor of Information Systems (Business Management)','Year 3 Semester 3','joel@example.com','0123456789'),
('FCST002','Lee Zhi Guan','Bachelor of Computer Science','Year 3 Semester 2','zhiguan@example.com','0123456790'),
('FCST003','Chong Kok Siong','Bachelor of Information Systems','Graduate','kok@example.com','0123456791');

INSERT INTO subjects (subject_code,subject_name,programme,lecturer_id) VALUES
('CAPC3010','Parallel and Distributed Computing','Bachelor of Computer Science',2),
('CTCS2010','Database Systems','Diploma in Information Technology',2);

INSERT INTO venues (venue_code,venue_name,venue_type,capacity,location) VALUES
('CR-301','Classroom 301','classroom',45,'FCST Block'),
('LAB-2','Computer Lab 2','computer_lab',35,'FCST Lab Area');

INSERT INTO class_sessions (subject_id,venue_id,lecturer_id,session_title,session_type,session_date,start_time,end_time,qr_token,status) VALUES
(1,1,2,'CAPC3010 Week 1 Lecture','classroom',CURDATE(),'09:00:00','11:00:00','FCST-QR-DEMO-001','open'),
(2,2,2,'Database Lab Practical','lab',CURDATE(),'14:00:00','16:00:00','FCST-QR-DEMO-002','open');