-- **
-- ! Database Structure
-- **

-- ***
-- ! Table: users
-- Description:
--   Stores system users (administrators and observers/supervisors).
--   Each user has authentication data and a defined role.
-- **

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','observer') NOT NULL DEFAULT 'observer',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- **
-- ! Table: students
-- Description:
--   Contains all student records including name, class, option,
--   identification numbers, and gender.
-- **


CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  classe VARCHAR(50) NOT NULL,
  `option` VARCHAR(50) NOT NULL,
  mtr VARCHAR(20) UNIQUE,
  nni VARCHAR(20) UNIQUE,
  `téléphone` VARCHAR(20),
  genre ENUM('M','F') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_classe (classe),
  INDEX idx_option (`option`),
  INDEX idx_nom (nom)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- **
-- ! Table: attendance
-- Description:
--   Tracks attendance status of students each day.
--   Linked to both students and observers via foreign keys.
--   Prevents duplicate entries per student per date.
-- **


CREATE TABLE IF NOT EXISTS attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  observer_id INT NOT NULL,
  date DATETIME NOT NULL,
  status ENUM('present','absent') NOT NULL,
  justification TEXT DEFAULT NULL,
  hours_absent FLOAT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_attendance (student_id, date),
  INDEX idx_student_date (student_id, date),
  INDEX idx_date (date),
  INDEX idx_status (status),
  INDEX observer_id (observer_id),
  CONSTRAINT fk_attendance_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_attendance_observer FOREIGN KEY (observer_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- **
-- ! Table: classes
-- Description:
--   Stores available classes (used to group students).
--   Each class has a unique name and timestamps.
-- **


CREATE TABLE IF NOT EXISTS `classes` (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- **
-- ! Default Users
-- Description:
--   Adds initial admin and observer accounts with hashed passwords.
-- **

INSERT INTO users (id, name, email, password, role, created_at, updated_at) VALUES
(1, 'Administrator', 'admin@school.com', '$2a$10$uarM.jWYyXClyssYugDuLunz9xHGmrdC/HrjHaKD14DArylXgxUN.', 'admin', '2025-10-15 20:23:52', '2025-10-15 21:27:34'),
(2, 'Surveillant', 'surveillant@school.com', '$2a$10$uarM.jWYyXClyssYugDuLunz9xHGmrdC/HrjHaKD14DArylXgxUN.', 'observer', '2025-10-15 20:38:16', '2025-10-15 20:38:16');