-- sql/schema.sql
-- Buat database:
-- CREATE DATABASE tpq_alwashil CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS kelas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS santri (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(120) NOT NULL,
  nis VARCHAR(50),
  kelas_id INT NULL,
  jk ENUM('L','P') DEFAULT 'L',
  tgl_lahir DATE NULL,
  alamat TEXT,
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wali (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(120) NOT NULL,
  hp VARCHAR(30),
  email VARCHAR(120),
  alamat TEXT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS santri_wali (
  id INT AUTO_INCREMENT PRIMARY KEY,
  santri_id INT NOT NULL,
  wali_id INT NOT NULL,
  UNIQUE KEY uniq_santri_wali (santri_id, wali_id),
  FOREIGN KEY (santri_id) REFERENCES santri(id) ON DELETE CASCADE,
  FOREIGN KEY (wali_id) REFERENCES wali(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS absensi (
  id INT AUTO_INCREMENT PRIMARY KEY,
  santri_id INT NOT NULL,
  tanggal DATE NOT NULL,
  status ENUM('H','S','I','A') NOT NULL DEFAULT 'H',
  UNIQUE KEY uniq_absensi (santri_id, tanggal),
  FOREIGN KEY (santri_id) REFERENCES santri(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS hafalan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  santri_id INT NOT NULL,
  tanggal DATE NOT NULL,
  materi VARCHAR(200) NOT NULL,
  catatan VARCHAR(255),
  FOREIGN KEY (santri_id) REFERENCES santri(id) ON DELETE CASCADE,
  INDEX (santri_id, tanggal)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pengumuman (
  id INT AUTO_INCREMENT PRIMARY KEY,
  judul VARCHAR(150) NOT NULL,
  isi TEXT NOT NULL,
  dibuat_oleh INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (dibuat_oleh) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Sample data
INSERT INTO kelas(nama) VALUES ('Awwal'),('Tsanī'),('Tsālits') ON DUPLICATE KEY UPDATE nama=VALUES(nama);
