-- 5. STUDENTS & ENROLLMENT
-- ---------------------------------------------------------------------

CREATE TABLE student_clubs (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_houses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE students (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED DEFAULT NULL,
  reg_number VARCHAR(30) NOT NULL,
  firstname VARCHAR(50) NOT NULL,
  lastname VARCHAR(50) NOT NULL,
  othername VARCHAR(50) DEFAULT NULL,
  dob DATE DEFAULT NULL,
  gender ENUM('male','female') NOT NULL,
  religion ENUM('christianity','islam','other') DEFAULT NULL,
  state_id INT UNSIGNED DEFAULT NULL,
  lga_id INT UNSIGNED DEFAULT NULL,
  nationality VARCHAR(50) NOT NULL DEFAULT 'Nigerian',
  address TEXT,
  health_conditions VARCHAR(255) DEFAULT NULL,
  passport_url VARCHAR(255) DEFAULT NULL,
  club_id INT UNSIGNED DEFAULT NULL,
  house_id INT UNSIGNED DEFAULT NULL,
  waec_exam_no VARCHAR(30) DEFAULT NULL,   -- SSS3 external exam registration
  neco_exam_no VARCHAR(30) DEFAULT NULL,
  status ENUM('active','withdrawn','graduated','suspended') NOT NULL DEFAULT 'active',
  admitted_date DATE NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  UNIQUE KEY uq_reg_number (reg_number),
  CONSTRAINT fk_student_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_student_state FOREIGN KEY (state_id) REFERENCES states(id),
  CONSTRAINT fk_student_lga FOREIGN KEY (lga_id) REFERENCES lgas(id),
  CONSTRAINT fk_student_club FOREIGN KEY (club_id) REFERENCES student_clubs(id) ON DELETE SET NULL,
  CONSTRAINT fk_student_house FOREIGN KEY (house_id) REFERENCES student_houses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_guardians (
  student_id BIGINT UNSIGNED NOT NULL,
  guardian_id BIGINT UNSIGNED NOT NULL,
  relationship VARCHAR(30) DEFAULT NULL,   -- father, mother, uncle...
  is_primary_contact TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (student_id, guardian_id),
  CONSTRAINT fk_sg_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_sg_guardian FOREIGN KEY (guardian_id) REFERENCES guardians(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- History-preserving enrollment: replaces the single class_id-on-students problem
CREATE TABLE student_enrollments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  status ENUM('active','promoted','repeated','withdrawn','transferred') NOT NULL DEFAULT 'active',
  enrolled_at DATE NOT NULL,
  UNIQUE KEY uq_student_session (student_id, session_id),
  CONSTRAINT fk_enroll_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_enroll_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_enroll_session FOREIGN KEY (session_id) REFERENCES sessions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE student_attendance (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  student_id BIGINT UNSIGNED NOT NULL,
  class_id INT UNSIGNED NOT NULL,
  term_id INT UNSIGNED NOT NULL,
  session_id INT UNSIGNED NOT NULL,
  attendance_date DATE NOT NULL,
  status ENUM('present','absent','late','excused') NOT NULL,
  recorded_by BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY uq_student_date (student_id, attendance_date),
  CONSTRAINT fk_att_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_class FOREIGN KEY (class_id) REFERENCES classes(id),
  CONSTRAINT fk_att_term FOREIGN KEY (term_id) REFERENCES terms(id),
  CONSTRAINT fk_att_session FOREIGN KEY (session_id) REFERENCES sessions(id),
  CONSTRAINT fk_att_recorder FOREIGN KEY (recorded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;