


-- **
-- ! 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;

