-- Jalankan di phpMyAdmin (tab SQL). Aman dijalankan ulang (IF NOT EXISTS).
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY, nama VARCHAR(100) NOT NULL, username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL, role VARCHAR(20) NOT NULL DEFAULT 'Operator', aktif TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS kelas (
  id INT AUTO_INCREMENT PRIMARY KEY, nama VARCHAR(30) NOT NULL UNIQUE, tingkat VARCHAR(10) DEFAULT '', wali VARCHAR(100) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS siswa (
  id INT AUTO_INCREMENT PRIMARY KEY, nis VARCHAR(30) NOT NULL UNIQUE, nama VARCHAR(120) NOT NULL, kelas_id INT NULL,
  jk ENUM('L','P') NOT NULL DEFAULT 'L', no_hp VARCHAR(20) DEFAULT '', status ENUM('aktif','lulus','nonaktif') NOT NULL DEFAULT 'aktif',
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS jenis_pembayaran (
  id INT AUTO_INCREMENT PRIMARY KEY, nama VARCHAR(100) NOT NULL, nominal BIGINT NOT NULL DEFAULT 0,
  tipe ENUM('bulanan','sekali') NOT NULL DEFAULT 'bulanan', keterangan VARCHAR(255) DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tagihan (
  id INT AUTO_INCREMENT PRIMARY KEY, siswa_id INT NOT NULL, jenis_id INT NOT NULL, periode VARCHAR(50) NOT NULL,
  nominal BIGINT NOT NULL, jatuh_tempo DATE NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tagihan (siswa_id,jenis_id,periode),
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE RESTRICT,
  FOREIGN KEY (jenis_id) REFERENCES jenis_pembayaran(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS transaksi (
  id INT AUTO_INCREMENT PRIMARY KEY, no_kwitansi VARCHAR(40) NOT NULL UNIQUE, tagihan_id INT NOT NULL, tanggal DATE NOT NULL,
  jumlah BIGINT NOT NULL, metode VARCHAR(20) NOT NULL DEFAULT 'Tunai', catatan VARCHAR(255) DEFAULT '', user_id INT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tagihan_id) REFERENCES tagihan(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS pengaturan (kunci VARCHAR(50) PRIMARY KEY, nilai TEXT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO pengaturan(kunci,nilai) VALUES
('nama_sekolah','MTsN 3 Bojonegoro'),('alamat','Bojonegoro, Jawa Timur'),('tahun_ajaran','2026/2027'),('bendahara',''),('kepala_sekolah','');
