
CREATE TABLE departments (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) UNIQUE NOT NULL
);

CREATE TABLE labs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) UNIQUE NOT NULL,
  capacity INT NOT NULL,
  status ENUM('available','maintenance') DEFAULT 'available',
  open_time TIME NOT NULL DEFAULT '08:00:00',
  close_time TIME NOT NULL DEFAULT '17:00:00'
);

CREATE TABLE courses (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(32) NOT NULL,
  name VARCHAR(160) NOT NULL,
  department_id BIGINT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_course_code_dept (code, department_id),
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE course_tutors (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  course_id BIGINT NOT NULL,
  tutor_id BIGINT NOT NULL,
  UNIQUE KEY uq_course_tutor (course_id, tutor_id),
  FOREIGN KEY (course_id) REFERENCES courses(id),
  FOREIGN KEY (tutor_id) REFERENCES users(id)
);

CREATE TABLE batches (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL,
  program VARCHAR(80) NULL,
  year SMALLINT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_batch_name (name)
);

CREATE TABLE batch_courses (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  batch_id BIGINT NOT NULL,
  course_id BIGINT NOT NULL,
  UNIQUE KEY uq_batch_course (batch_id, course_id),
  FOREIGN KEY (batch_id) REFERENCES batches(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

CREATE TABLE bookings (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  tutor_id BIGINT NOT NULL,
  lab_id BIGINT NOT NULL,
  course_id BIGINT NOT NULL,
  batch_id BIGINT NULL,
  purpose ENUM('quiz','practical','exam','demo') NOT NULL DEFAULT 'quiz',
  expected_students INT NULL,
  sections INT NOT NULL DEFAULT 1,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  status ENUM('pending','approved','rejected','completed','cancelled') DEFAULT 'pending',
  approval_by BIGINT NULL,
  notes VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tutor_id) REFERENCES users(id),
  FOREIGN KEY (lab_id) REFERENCES labs(id),
  FOREIGN KEY (course_id) REFERENCES courses(id),
  FOREIGN KEY (batch_id) REFERENCES batches(id),
  FOREIGN KEY (approval_by) REFERENCES users(id),
  INDEX idx_lab_time (lab_id, start_time, end_time)
);

CREATE TABLE booking_sections (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  booking_id BIGINT NOT NULL,
  section_no INT NOT NULL,
  start_time DATETIME NOT NULL,
  end_time DATETIME NOT NULL,
  expected_students INT NULL,
  FOREIGN KEY (booking_id) REFERENCES bookings(id),
  UNIQUE KEY uq_booking_section (booking_id, section_no)
);

-- Seed data
INSERT INTO departments(name) VALUES ('ICT'), ('Nursing'), ('Midwifery');

INSERT INTO labs(name, capacity) VALUES ('Lab 1', 60), ('Lab 2', 45);

INSERT INTO courses(code, name, department_id) VALUES
('ANAT101','Anatomy I', 2),
('PHYS201','Physiology II', 2),
('ICT100','Intro to Computing', 1);

INSERT INTO batches(name, program, year) VALUES
('Diploma 2025 A','Diploma',2025),
('Certificate 2025 B','Certificate',2025);
