-- Full setup.sql - creates schema and seed data
CREATE DATABASE IF NOT EXISTS school_management CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE school_management;

DROP TABLE IF EXISTS attendance;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','teacher','student') NOT NULL DEFAULT 'student',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE classes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  phone VARCHAR(50) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  class_id INT NOT NULL,
  student_id INT NOT NULL,
  status ENUM('present','absent') NOT NULL,
  taken_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE,
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (taken_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Seed users (passwords hashed using PHP password_hash)
INSERT INTO users (name, phone, password_hash, role) VALUES
('System Admin','admin', '$2b$12$sk9SbBQhKzaWbHybnjsoKOrCr3pkJRZnNzMLZrDubtOGgYKp2g7.m', 'admin'),
('Teacher One','teacher', '$2b$12$YTSkmxj3O638ZIloGw6CcONqaxweDm.FQC7maHCXYhZtPq8iVZ9me', 'teacher'),
('Student One','student', '$2b$12$BNGSus8ilw5kuFxvdsZuXOtZZsWxl.BrZ8uTWaLhaFd9pnGN7RDzK', 'student');

-- Optionally seed a class and a student record
INSERT INTO classes (title,description) VALUES ('Mathematics 101','Basic mathematics');
INSERT INTO students (name,phone) VALUES ('Student One','student');