-- absensi.sql: struktur database and contoh data
CREATE DATABASE IF NOT EXISTS absensi;
USE absensi;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  password VARCHAR(255)
);
INSERT INTO users (username,password) VALUES ('admin', MD5('admin123'));

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nis VARCHAR(20) UNIQUE NULL,
  name VARCHAR(100) NOT NULL,
  class VARCHAR(50) NOT NULL,
  rfid_code VARCHAR(50) UNIQUE NOT NULL,
  parent_phone VARCHAR(20) NOT NULL
);

INSERT INTO students (name, class, rfid_code, parent_phone) VALUES
('Ahmad Fauzi', '7A', '1234567890', '6281234567890'),
('Budi Santoso', '7A', '0987654321', '6289876543210'),
('Citra Dewi', '7B', '1122334455', '6281122334455');

CREATE TABLE attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  scan_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status ENUM('Hadir','Terlambat','Izin','Sakit','Alfa') NOT NULL DEFAULT 'Hadir',
  FOREIGN KEY (student_id) REFERENCES students(id)
);

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  key_name VARCHAR(100) UNIQUE,
  value TEXT
);

INSERT INTO settings (key_name, value) VALUES
('wa_message_late', '⚠️ Info Absensi: Anak Anda *{nama}* hadir pada {waktu}, status: *Terlambat*.'),
('jam_batas_global', '07:00:00');

CREATE TABLE class_settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  class_name VARCHAR(50) UNIQUE,
  jam_batas TIME NOT NULL DEFAULT '07:00:00'
);

INSERT INTO class_settings (class_name, jam_batas) VALUES
('7A','07:00:00'),('7B','07:05:00');