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