-- =====================================================================
-- SIMAP - Sistem Informasi Manajemen Pendidikan (Jenjang SD/MI)
-- Sinang Permata Group
-- Engine  : MySQL 8.0 / MariaDB 10.6+
-- Charset : utf8mb4_unicode_ci
-- =====================================================================
SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;

CREATE DATABASE IF NOT EXISTS `simap_db`
  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `simap_db`;

-- =====================================================================
-- BAGIAN 1 : IDENTITAS SEKOLAH, PERIODE & PENGATURAN
-- =====================================================================

CREATE TABLE sekolah (
  id                TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  npsn              VARCHAR(20) UNIQUE,
  nss               VARCHAR(30) NULL,
  nama              VARCHAR(150) NOT NULL,
  jenjang           ENUM('SD','MI') NOT NULL DEFAULT 'SD',
  naungan           ENUM('KEMENDIKDASMEN','KEMENAG') NOT NULL DEFAULT 'KEMENDIKDASMEN',
  kurikulum_aktif   ENUM('MERDEKA','KBC','K13') NOT NULL DEFAULT 'MERDEKA',
  status_sekolah    ENUM('NEGERI','SWASTA') DEFAULT 'SWASTA',
  akreditasi        VARCHAR(5) NULL,
  alamat            TEXT,
  desa              VARCHAR(80), kecamatan VARCHAR(80),
  kabupaten         VARCHAR(80), provinsi VARCHAR(80), kode_pos VARCHAR(10),
  telepon           VARCHAR(30), email VARCHAR(100), website VARCHAR(100),
  kepala_sekolah    VARCHAR(120), nip_kepala VARCHAR(30),
  logo              VARCHAR(255), kop_surat VARCHAR(255), stempel VARCHAR(255),
  ttd_kepala        VARCHAR(255),
  latitude          DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL,
  radius_absen      SMALLINT DEFAULT 150 COMMENT 'meter, untuk absen GPS guru',
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE tahun_ajaran (
  id            SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode          VARCHAR(12) NOT NULL COMMENT '2025/2026',
  semester      ENUM('1','2') NOT NULL COMMENT '1=Ganjil 2=Genap',
  tanggal_mulai DATE, tanggal_selesai DATE,
  tanggal_rapor DATE NULL,
  is_aktif      TINYINT(1) DEFAULT 0,
  is_locked     TINYINT(1) DEFAULT 0 COMMENT 'kunci setelah rapor terbit',
  UNIQUE KEY uq_ta (kode, semester)
) ENGINE=InnoDB;

CREATE TABLE setting (
  skey    VARCHAR(80) PRIMARY KEY,
  svalue  TEXT,
  grup    VARCHAR(40) DEFAULT 'umum',
  tipe    ENUM('text','number','bool','json','file') DEFAULT 'text',
  label   VARCHAR(150)
) ENGINE=InnoDB;

CREATE TABLE hari_libur (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tanggal   DATE NOT NULL,
  tanggal_selesai DATE NULL,
  keterangan VARCHAR(150),
  jenis     ENUM('NASIONAL','SEKOLAH','CUTI_BERSAMA','LIBUR_SEMESTER') DEFAULT 'SEKOLAH',
  INDEX (tanggal)
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 2 : USER, ROLE, HAK AKSES
-- =====================================================================

CREATE TABLE roles (
  id        TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode      VARCHAR(30) UNIQUE NOT NULL,
  nama      VARCHAR(60) NOT NULL,
  level     TINYINT DEFAULT 5 COMMENT '1=superadmin ... 9=terbatas',
  keterangan VARCHAR(200)
) ENGINE=InnoDB;

CREATE TABLE permissions (
  id      SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode    VARCHAR(80) UNIQUE NOT NULL COMMENT 'contoh: absensi.rekap.view',
  modul   VARCHAR(40),
  nama    VARCHAR(150)
) ENGINE=InnoDB;

CREATE TABLE role_permission (
  role_id       TINYINT UNSIGNED,
  permission_id SMALLINT UNSIGNED,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE users (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  username      VARCHAR(60) UNIQUE NOT NULL,
  email         VARCHAR(120) NULL,
  no_hp         VARCHAR(20) NULL,
  password      VARCHAR(255) NOT NULL,
  role_id       TINYINT UNSIGNED NOT NULL,
  ref_type      ENUM('GURU','SISWA','ORTU','PEGAWAI','NONE') DEFAULT 'NONE',
  ref_id        INT UNSIGNED NULL COMMENT 'id guru/siswa/ortu/pegawai',
  nama_tampil   VARCHAR(120),
  foto          VARCHAR(255),
  status        ENUM('AKTIF','NONAKTIF','SUSPEND') DEFAULT 'AKTIF',
  must_change_pw TINYINT(1) DEFAULT 0,
  last_login    DATETIME NULL,
  last_ip       VARCHAR(45) NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id),
  INDEX idx_ref (ref_type, ref_id)
) ENGINE=InnoDB;

CREATE TABLE user_token (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  token       VARCHAR(128) UNIQUE NOT NULL,
  device_id   VARCHAR(120), device_name VARCHAR(120),
  fcm_token   VARCHAR(255) NULL,
  platform    ENUM('ANDROID','IOS','WEB') DEFAULT 'ANDROID',
  expired_at  DATETIME,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE audit_log (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id     INT UNSIGNED NULL,
  aksi        VARCHAR(60), modul VARCHAR(40),
  ref_tabel   VARCHAR(60), ref_id BIGINT UNSIGNED NULL,
  deskripsi   TEXT, ip VARCHAR(45), user_agent VARCHAR(255),
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id), INDEX (modul), INDEX (created_at)
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 3 : PTK (GURU & TENAGA KEPENDIDIKAN) DAN SISWA
-- =====================================================================

CREATE TABLE guru (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nuptk         VARCHAR(20) NULL, nip VARCHAR(30) NULL,
  nik           VARCHAR(20) NULL,
  kode_guru     VARCHAR(15) UNIQUE,
  nama          VARCHAR(120) NOT NULL,
  gelar_depan   VARCHAR(20), gelar_belakang VARCHAR(30),
  jenis_kelamin ENUM('L','P'), tempat_lahir VARCHAR(60), tanggal_lahir DATE,
  agama         VARCHAR(20),
  alamat        TEXT, no_hp VARCHAR(20), email VARCHAR(100),
  status_kepegawaian ENUM('PNS','PPPK','GTY','GTT','HONORER') DEFAULT 'GTY',
  jenis_ptk     ENUM('GURU_KELAS','GURU_MAPEL','KEPALA_SEKOLAH','WAKIL','TU','PUSTAKAWAN','OPERATOR','PENJAGA') DEFAULT 'GURU_KELAS',
  pendidikan    VARCHAR(20), jurusan VARCHAR(80),
  tmt_tugas     DATE NULL,
  foto          VARCHAR(255), ttd VARCHAR(255),
  rfid_uid      VARCHAR(32) NULL UNIQUE,
  status        ENUM('AKTIF','NONAKTIF','PINDAH','PENSIUN') DEFAULT 'AKTIF',
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (nama)
) ENGINE=InnoDB;

CREATE TABLE siswa (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nisn          VARCHAR(15) UNIQUE, nis VARCHAR(20) UNIQUE,
  nik           VARCHAR(20) NULL, no_kk VARCHAR(20) NULL,
  no_akta       VARCHAR(40) NULL,
  nama          VARCHAR(120) NOT NULL, nama_panggilan VARCHAR(40),
  jenis_kelamin ENUM('L','P') NOT NULL,
  tempat_lahir  VARCHAR(60), tanggal_lahir DATE,
  agama         VARCHAR(20), kewarganegaraan VARCHAR(30) DEFAULT 'WNI',
  anak_ke       TINYINT, jumlah_saudara TINYINT,
  alamat        TEXT, rt VARCHAR(4), rw VARCHAR(4),
  desa VARCHAR(80), kecamatan VARCHAR(80), kabupaten VARCHAR(80),
  provinsi VARCHAR(80), kode_pos VARCHAR(10),
  jarak_rumah_km DECIMAL(5,2) NULL, transportasi VARCHAR(40),
  tinggal_bersama VARCHAR(40) DEFAULT 'Orang Tua',
  golongan_darah VARCHAR(3), tinggi_badan SMALLINT, berat_badan SMALLINT,
  riwayat_kesehatan TEXT NULL COMMENT 'alergi / catatan khusus',
  kebutuhan_khusus VARCHAR(60) NULL,
  asal_tk       VARCHAR(120), tanggal_masuk DATE,
  no_kip VARCHAR(30) NULL, no_pkh VARCHAR(30) NULL,
  penerima_kip TINYINT(1) DEFAULT 0,
  foto          VARCHAR(255),
  rfid_uid      VARCHAR(32) NULL UNIQUE,
  status        ENUM('AKTIF','LULUS','PINDAH','KELUAR','MENGULANG') DEFAULT 'AKTIF',
  tanggal_keluar DATE NULL, alasan_keluar VARCHAR(150),
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (nama), INDEX (status)
) ENGINE=InnoDB;

CREATE TABLE ortu (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nik           VARCHAR(20) NULL,
  nama          VARCHAR(120) NOT NULL,
  hubungan      ENUM('AYAH','IBU','WALI') NOT NULL,
  tempat_lahir  VARCHAR(60), tanggal_lahir DATE,
  pendidikan    VARCHAR(30), pekerjaan VARCHAR(80),
  penghasilan   ENUM('<1JT','1-2JT','2-5JT','5-10JT','>10JT','TIDAK_TETAP') NULL,
  no_hp         VARCHAR(20), email VARCHAR(100), alamat TEXT,
  status_hidup  ENUM('HIDUP','MENINGGAL') DEFAULT 'HIDUP',
  foto          VARCHAR(255),
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE siswa_ortu (
  siswa_id  INT UNSIGNED, ortu_id INT UNSIGNED,
  is_utama  TINYINT(1) DEFAULT 0 COMMENT 'penerima notifikasi utama',
  PRIMARY KEY (siswa_id, ortu_id),
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  FOREIGN KEY (ortu_id) REFERENCES ortu(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 4 : KELAS, MAPEL, KURIKULUM, JADWAL
-- =====================================================================

CREATE TABLE kelas (
  id            SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  tingkat       TINYINT NOT NULL COMMENT '1..6',
  fase          ENUM('A','B','C') NOT NULL COMMENT 'A=1-2, B=3-4, C=5-6',
  rombel        VARCHAR(20) NOT NULL COMMENT 'A / B / Ibnu Sina',
  nama          VARCHAR(40) NOT NULL COMMENT '4A',
  wali_kelas_id INT UNSIGNED NULL,
  pendamping_id INT UNSIGNED NULL,
  ruang         VARCHAR(30), kuota SMALLINT DEFAULT 32,
  FOREIGN KEY (tahun_ajaran_id) REFERENCES tahun_ajaran(id),
  FOREIGN KEY (wali_kelas_id) REFERENCES guru(id) ON DELETE SET NULL,
  UNIQUE KEY uq_kelas (tahun_ajaran_id, tingkat, rombel)
) ENGINE=InnoDB;

CREATE TABLE kelas_siswa (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kelas_id  SMALLINT UNSIGNED NOT NULL,
  siswa_id  INT UNSIGNED NOT NULL,
  no_absen  SMALLINT,
  status    ENUM('AKTIF','PINDAH_KELAS','KELUAR') DEFAULT 'AKTIF',
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE,
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  UNIQUE KEY uq_ks (kelas_id, siswa_id)
) ENGINE=InnoDB;

CREATE TABLE mapel (
  id            SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode          VARCHAR(15) UNIQUE NOT NULL,
  nama          VARCHAR(100) NOT NULL,
  nama_singkat  VARCHAR(30),
  kelompok      ENUM('UMUM','AGAMA','MULOK','EKSKUL','BIMBINGAN') DEFAULT 'UMUM',
  naungan       ENUM('SEMUA','KEMENDIKDASMEN','KEMENAG') DEFAULT 'SEMUA',
  urutan_rapor  SMALLINT DEFAULT 99,
  tingkat_min   TINYINT DEFAULT 1, tingkat_max TINYINT DEFAULT 6,
  jam_per_minggu TINYINT DEFAULT 2,
  warna         VARCHAR(9) DEFAULT '#1F4A3C' COMMENT 'warna kartu e-learning',
  ikon          VARCHAR(40),
  is_aktif      TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE guru_mapel (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  guru_id   INT UNSIGNED NOT NULL,
  mapel_id  SMALLINT UNSIGNED NOT NULL,
  kelas_id  SMALLINT UNSIGNED NOT NULL,
  FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE,
  FOREIGN KEY (mapel_id) REFERENCES mapel(id) ON DELETE CASCADE,
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE,
  UNIQUE KEY uq_gmk (tahun_ajaran_id, guru_id, mapel_id, kelas_id)
) ENGINE=InnoDB;

CREATE TABLE jam_pelajaran (
  id        TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  jam_ke    TINYINT NOT NULL,
  mulai     TIME NOT NULL, selesai TIME NOT NULL,
  is_istirahat TINYINT(1) DEFAULT 0,
  keterangan VARCHAR(40)
) ENGINE=InnoDB;

CREATE TABLE jadwal (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  kelas_id      SMALLINT UNSIGNED NOT NULL,
  mapel_id      SMALLINT UNSIGNED NOT NULL,
  guru_id       INT UNSIGNED NOT NULL,
  hari          TINYINT NOT NULL COMMENT '1=Senin .. 7=Minggu',
  jam_mulai_id  TINYINT UNSIGNED, jam_selesai_id TINYINT UNSIGNED,
  ruang         VARCHAR(30),
  FOREIGN KEY (kelas_id) REFERENCES kelas(id) ON DELETE CASCADE,
  FOREIGN KEY (mapel_id) REFERENCES mapel(id),
  FOREIGN KEY (guru_id) REFERENCES guru(id),
  INDEX (hari)
) ENGINE=InnoDB;

-- ---------- Kerangka kurikulum (CP -> TP -> ATP) ----------
CREATE TABLE cp (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kurikulum     ENUM('MERDEKA','KBC','K13') DEFAULT 'MERDEKA',
  mapel_id      SMALLINT UNSIGNED NOT NULL,
  fase          ENUM('A','B','C') NOT NULL,
  elemen        VARCHAR(120) NOT NULL COMMENT 'Menyimak-Berbicara, Bilangan, dst',
  deskripsi     TEXT NOT NULL,
  sumber        VARCHAR(120) DEFAULT 'BSKAP',
  FOREIGN KEY (mapel_id) REFERENCES mapel(id) ON DELETE CASCADE,
  INDEX (mapel_id, fase)
) ENGINE=InnoDB;

CREATE TABLE tp (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  cp_id         INT UNSIGNED NOT NULL,
  mapel_id      SMALLINT UNSIGNED NOT NULL,
  tingkat       TINYINT NOT NULL,
  semester      ENUM('1','2') NOT NULL,
  kode_tp       VARCHAR(20) COMMENT '4.1.1',
  deskripsi     TEXT NOT NULL,
  alokasi_jp    TINYINT DEFAULT 2,
  urutan        SMALLINT DEFAULT 1,
  dibuat_oleh   INT UNSIGNED NULL,
  sumber_ai     TINYINT(1) DEFAULT 0,
  FOREIGN KEY (cp_id) REFERENCES cp(id) ON DELETE CASCADE,
  INDEX (mapel_id, tingkat, semester)
) ENGINE=InnoDB;

CREATE TABLE dimensi_lulusan (
  id        TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kerangka  ENUM('PROFIL_LULUSAN','PANCA_CINTA') NOT NULL,
  kode      VARCHAR(30) UNIQUE, nama VARCHAR(80) NOT NULL,
  deskripsi TEXT, warna VARCHAR(9), ikon VARCHAR(40), urutan TINYINT
) ENGINE=InnoDB
COMMENT='8 Dimensi Profil Lulusan (Permendikdasmen 10/2025) & Panca Cinta (KMA 1503/2025)';

CREATE TABLE dimensi_indikator (
  id        SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  dimensi_id TINYINT UNSIGNED NOT NULL,
  fase      ENUM('A','B','C') NOT NULL,
  indikator TEXT NOT NULL,
  FOREIGN KEY (dimensi_id) REFERENCES dimensi_lulusan(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 5 : ADMINISTRASI GURU (PROTA, PROMES, SILABUS/ATP, MODUL AJAR)
-- =====================================================================

CREATE TABLE prota (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  guru_id       INT UNSIGNED NOT NULL,
  mapel_id      SMALLINT UNSIGNED NOT NULL,
  tingkat       TINYINT NOT NULL, fase ENUM('A','B','C'),
  kurikulum     ENUM('MERDEKA','KBC','K13') DEFAULT 'MERDEKA',
  total_jp      SMALLINT, minggu_efektif_1 TINYINT, minggu_efektif_2 TINYINT,
  status        ENUM('DRAFT','DIAJUKAN','DISETUJUI','REVISI') DEFAULT 'DRAFT',
  catatan_kepsek TEXT NULL,
  disetujui_oleh INT UNSIGNED NULL, disetujui_at DATETIME NULL,
  sumber_ai     TINYINT(1) DEFAULT 0,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE,
  FOREIGN KEY (mapel_id) REFERENCES mapel(id)
) ENGINE=InnoDB;

CREATE TABLE prota_detail (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  prota_id  INT UNSIGNED NOT NULL,
  semester  ENUM('1','2'), lingkup_materi VARCHAR(200),
  tp_id     INT UNSIGNED NULL, uraian TEXT,
  alokasi_jp SMALLINT, urutan SMALLINT,
  FOREIGN KEY (prota_id) REFERENCES prota(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE promes (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  prota_id      INT UNSIGNED NOT NULL,
  semester      ENUM('1','2') NOT NULL,
  status        ENUM('DRAFT','DIAJUKAN','DISETUJUI','REVISI') DEFAULT 'DRAFT',
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (prota_id) REFERENCES prota(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE promes_detail (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  promes_id INT UNSIGNED NOT NULL,
  tp_id     INT UNSIGNED NULL,
  materi    VARCHAR(250), alokasi_jp SMALLINT,
  bulan     TINYINT COMMENT '1..12', minggu_ke TINYINT COMMENT '1..5',
  keterangan VARCHAR(120),
  FOREIGN KEY (promes_id) REFERENCES promes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE atp (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  guru_id       INT UNSIGNED NOT NULL, mapel_id SMALLINT UNSIGNED NOT NULL,
  tingkat       TINYINT, fase ENUM('A','B','C'), semester ENUM('1','2'),
  judul         VARCHAR(200), rasional TEXT,
  file_pdf      VARCHAR(255) NULL,
  status        ENUM('DRAFT','DIAJUKAN','DISETUJUI','REVISI') DEFAULT 'DRAFT',
  sumber_ai     TINYINT(1) DEFAULT 0,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE atp_detail (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  atp_id    INT UNSIGNED NOT NULL, tp_id INT UNSIGNED NULL,
  urutan    SMALLINT, tujuan TEXT, materi_pokok VARCHAR(250),
  kata_kunci VARCHAR(200), profil_dimensi VARCHAR(200) COMMENT 'csv id dimensi',
  alokasi_jp SMALLINT, asesmen VARCHAR(200), glosarium TEXT,
  FOREIGN KEY (atp_id) REFERENCES atp(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE modul_ajar (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  guru_id       INT UNSIGNED NOT NULL,
  mapel_id      SMALLINT UNSIGNED NOT NULL,
  kelas_id      SMALLINT UNSIGNED NULL,
  tingkat       TINYINT, fase ENUM('A','B','C'), semester ENUM('1','2'),
  kurikulum     ENUM('MERDEKA','KBC','K13') DEFAULT 'MERDEKA',
  judul         VARCHAR(200) NOT NULL,
  alokasi_waktu VARCHAR(120) COMMENT '2 x 35 menit',
  jumlah_pertemuan TINYINT DEFAULT 1,
  -- Komponen inti
  tujuan_pembelajaran TEXT, pemahaman_bermakna TEXT, pertanyaan_pemantik TEXT,
  profil_dimensi VARCHAR(200) COMMENT 'csv id dimensi_lulusan',
  panca_cinta   VARCHAR(120) NULL COMMENT 'csv id dimensi KBC (madrasah)',
  model_pembelajaran VARCHAR(255) DEFAULT 'Problem Based Learning',
  metode        VARCHAR(255),
  pendekatan    VARCHAR(255) DEFAULT 'Pembelajaran Mendalam',
  sarana_prasarana TEXT, target_peserta VARCHAR(255),
  kegiatan_pendahuluan TEXT, kegiatan_inti TEXT, kegiatan_penutup TEXT,
  asesmen_diagnostik TEXT, asesmen_formatif TEXT, asesmen_sumatif TEXT,
  rubrik_penilaian TEXT COMMENT 'JSON',
  pengayaan TEXT, remedial TEXT, refleksi_guru TEXT, refleksi_siswa TEXT,
  lampiran_lkpd TEXT, bahan_bacaan TEXT, glosarium TEXT, daftar_pustaka TEXT,
  file_pdf      VARCHAR(255) NULL, file_docx VARCHAR(255) NULL,
  status        ENUM('DRAFT','DIAJUKAN','DISETUJUI','REVISI') DEFAULT 'DRAFT',
  catatan_kepsek TEXT NULL,
  disetujui_oleh INT UNSIGNED NULL, disetujui_at DATETIME NULL,
  sumber_ai     TINYINT(1) DEFAULT 0, ai_log_id BIGINT UNSIGNED NULL,
  dibagikan     TINYINT(1) DEFAULT 0 COMMENT 'boleh dipakai guru lain',
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE,
  FOREIGN KEY (mapel_id) REFERENCES mapel(id),
  INDEX (status), FULLTEXT KEY ft_modul (judul, tujuan_pembelajaran)
) ENGINE=InnoDB;

CREATE TABLE jurnal_mengajar (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  guru_id       INT UNSIGNED NOT NULL, kelas_id SMALLINT UNSIGNED NOT NULL,
  mapel_id      SMALLINT UNSIGNED NOT NULL, modul_ajar_id INT UNSIGNED NULL,
  tanggal       DATE NOT NULL, jam_ke VARCHAR(20),
  materi        VARCHAR(250), kegiatan TEXT,
  jumlah_hadir  SMALLINT, jumlah_absen SMALLINT,
  kendala       TEXT, tindak_lanjut TEXT,
  foto_kegiatan VARCHAR(255) NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE,
  UNIQUE KEY uq_jurnal (guru_id, kelas_id, mapel_id, tanggal, jam_ke)
) ENGINE=InnoDB;

CREATE TABLE supervisi (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED, guru_id INT UNSIGNED NOT NULL,
  supervisor_id INT UNSIGNED NOT NULL, tanggal DATE,
  jenis ENUM('ADMINISTRASI','KUNJUNGAN_KELAS','KLINIS') DEFAULT 'KUNJUNGAN_KELAS',
  instrumen JSON NULL, skor DECIMAL(5,2), predikat VARCHAR(20),
  temuan TEXT, rekomendasi TEXT, tindak_lanjut TEXT,
  file_lampiran VARCHAR(255),
  FOREIGN KEY (guru_id) REFERENCES guru(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 6 : ABSENSI (RFID SISWA, GURU, PER MAPEL)
-- =====================================================================

CREATE TABLE rfid_device (
  id            SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode_device   VARCHAR(30) UNIQUE NOT NULL,
  nama          VARCHAR(80), lokasi VARCHAR(100),
  api_key       VARCHAR(64) NOT NULL,
  mode          ENUM('SISWA','GURU','PERPUS','KANTIN','UMUM') DEFAULT 'SISWA',
  ip_terakhir   VARCHAR(45), firmware VARCHAR(20),
  last_seen     DATETIME NULL, total_tap INT DEFAULT 0,
  status        ENUM('AKTIF','NONAKTIF','MAINTENANCE') DEFAULT 'AKTIF'
) ENGINE=InnoDB;

CREATE TABLE rfid_kartu (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  uid         VARCHAR(32) UNIQUE NOT NULL,
  pemilik_tipe ENUM('SISWA','GURU','PEGAWAI') NOT NULL,
  pemilik_id  INT UNSIGNED NOT NULL,
  no_kartu    VARCHAR(30), tanggal_terbit DATE,
  status      ENUM('AKTIF','HILANG','RUSAK','DIBLOKIR') DEFAULT 'AKTIF',
  INDEX (pemilik_tipe, pemilik_id)
) ENGINE=InnoDB;

CREATE TABLE rfid_log (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  device_id   SMALLINT UNSIGNED, uid VARCHAR(32),
  waktu       DATETIME NOT NULL,
  hasil       ENUM('OK','UID_TIDAK_DIKENAL','DUPLIKAT','DILUAR_JAM','KARTU_BLOKIR') DEFAULT 'OK',
  ref_absensi_id BIGINT UNSIGNED NULL,
  raw         VARCHAR(255),
  INDEX (waktu), INDEX (uid)
) ENGINE=InnoDB;

CREATE TABLE absensi_siswa (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  siswa_id      INT UNSIGNED NOT NULL, kelas_id SMALLINT UNSIGNED NOT NULL,
  tanggal       DATE NOT NULL,
  jam_masuk     TIME NULL, jam_pulang TIME NULL,
  status        ENUM('HADIR','TERLAMBAT','SAKIT','IZIN','ALPA','DISPEN') DEFAULT 'ALPA',
  menit_telat   SMALLINT DEFAULT 0,
  sumber        ENUM('RFID','MANUAL','IMPOR','ORTU') DEFAULT 'RFID',
  device_id     SMALLINT UNSIGNED NULL,
  keterangan    VARCHAR(200), file_surat VARCHAR(255) NULL,
  diinput_oleh  INT UNSIGNED NULL,
  notif_terkirim TINYINT(1) DEFAULT 0, notif_at DATETIME NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  UNIQUE KEY uq_abs (siswa_id, tanggal),
  INDEX (tanggal), INDEX (kelas_id, tanggal)
) ENGINE=InnoDB;

CREATE TABLE absensi_mapel (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  jadwal_id     INT UNSIGNED NULL, kelas_id SMALLINT UNSIGNED NOT NULL,
  mapel_id      SMALLINT UNSIGNED NOT NULL, guru_id INT UNSIGNED NOT NULL,
  siswa_id      INT UNSIGNED NOT NULL,
  tanggal       DATE NOT NULL, jam_ke VARCHAR(20),
  status        ENUM('HADIR','SAKIT','IZIN','ALPA') DEFAULT 'HADIR',
  catatan       VARCHAR(150),
  UNIQUE KEY uq_absmapel (siswa_id, mapel_id, tanggal, jam_ke),
  INDEX (kelas_id, tanggal)
) ENGINE=InnoDB;

CREATE TABLE absensi_guru (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  guru_id       INT UNSIGNED NOT NULL, tanggal DATE NOT NULL,
  jam_masuk     TIME NULL, jam_pulang TIME NULL,
  status        ENUM('HADIR','TERLAMBAT','SAKIT','IZIN','CUTI','DINAS_LUAR','ALPA') DEFAULT 'ALPA',
  menit_telat   SMALLINT DEFAULT 0,
  sumber        ENUM('RFID','GPS','MANUAL') DEFAULT 'RFID',
  latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL,
  foto_selfie   VARCHAR(255) NULL, keterangan VARCHAR(200),
  UNIQUE KEY uq_absguru (guru_id, tanggal)
) ENGINE=InnoDB;

CREATE TABLE perizinan (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  pemohon_tipe  ENUM('SISWA','GURU') NOT NULL, pemohon_id INT UNSIGNED NOT NULL,
  diajukan_oleh INT UNSIGNED NULL COMMENT 'user id (ortu untuk siswa)',
  jenis         ENUM('SAKIT','IZIN','CUTI','DINAS_LUAR') NOT NULL,
  tanggal_mulai DATE NOT NULL, tanggal_selesai DATE NOT NULL,
  alasan        TEXT, file_bukti VARCHAR(255) NULL,
  status        ENUM('MENUNGGU','DISETUJUI','DITOLAK') DEFAULT 'MENUNGGU',
  diproses_oleh INT UNSIGNED NULL, catatan_admin VARCHAR(200),
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (pemohon_tipe, pemohon_id), INDEX (status)
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 7 : E-LEARNING, ASESMEN, GAME
-- =====================================================================

CREATE TABLE materi (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  guru_id       INT UNSIGNED NOT NULL, mapel_id SMALLINT UNSIGNED NOT NULL,
  kelas_id      SMALLINT UNSIGNED NULL COMMENT 'null = semua kelas tingkat itu',
  tingkat       TINYINT, modul_ajar_id INT UNSIGNED NULL, tp_id INT UNSIGNED NULL,
  judul         VARCHAR(200) NOT NULL, slug VARCHAR(220),
  deskripsi     TEXT, konten LONGTEXT COMMENT 'HTML editor',
  tipe          ENUM('TEKS','VIDEO','PDF','SLIDE','AUDIO','LINK','INTERAKTIF') DEFAULT 'TEKS',
  url_video     VARCHAR(255) NULL COMMENT 'youtube/vimeo/self-host',
  durasi_menit  SMALLINT NULL, thumbnail VARCHAR(255),
  file_lampiran VARCHAR(255) NULL,
  urutan        SMALLINT DEFAULT 1,
  poin          SMALLINT DEFAULT 10 COMMENT 'poin gamifikasi saat selesai',
  tanggal_terbit DATETIME NULL,
  status        ENUM('DRAFT','TERBIT','ARSIP') DEFAULT 'DRAFT',
  jumlah_dilihat INT DEFAULT 0,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (mapel_id) REFERENCES mapel(id),
  INDEX (kelas_id, mapel_id), FULLTEXT KEY ft_materi (judul, deskripsi)
) ENGINE=InnoDB;

CREATE TABLE materi_progres (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  materi_id   INT UNSIGNED NOT NULL, siswa_id INT UNSIGNED NOT NULL,
  persen      TINYINT DEFAULT 0, detik_tonton INT DEFAULT 0,
  selesai     TINYINT(1) DEFAULT 0, selesai_at DATETIME NULL,
  terakhir_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_mp (materi_id, siswa_id)
) ENGINE=InnoDB;

CREATE TABLE tugas (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  guru_id       INT UNSIGNED NOT NULL, mapel_id SMALLINT UNSIGNED NOT NULL,
  kelas_id      SMALLINT UNSIGNED NOT NULL, tp_id INT UNSIGNED NULL,
  judul         VARCHAR(200), instruksi TEXT,
  jenis         ENUM('INDIVIDU','KELOMPOK','PROYEK','PRAKTIK') DEFAULT 'INDIVIDU',
  kategori_nilai ENUM('FORMATIF','SUMATIF') DEFAULT 'FORMATIF',
  file_lampiran VARCHAR(255) NULL,
  bobot         TINYINT DEFAULT 100,
  mulai         DATETIME, deadline DATETIME,
  izin_terlambat TINYINT(1) DEFAULT 1,
  status        ENUM('DRAFT','TERBIT','DITUTUP') DEFAULT 'DRAFT',
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (kelas_id, mapel_id)
) ENGINE=InnoDB;

CREATE TABLE tugas_jawaban (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tugas_id    INT UNSIGNED NOT NULL, siswa_id INT UNSIGNED NOT NULL,
  jawaban     TEXT, file_jawaban VARCHAR(255) NULL,
  dikumpul_at DATETIME NULL, terlambat TINYINT(1) DEFAULT 0,
  nilai       DECIMAL(5,2) NULL, feedback TEXT NULL,
  feedback_ai TEXT NULL,
  dinilai_oleh INT UNSIGNED NULL, dinilai_at DATETIME NULL,
  status      ENUM('BELUM','DIKUMPUL','DINILAI','REVISI') DEFAULT 'BELUM',
  UNIQUE KEY uq_tj (tugas_id, siswa_id)
) ENGINE=InnoDB;

CREATE TABLE bank_soal (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  mapel_id      SMALLINT UNSIGNED NOT NULL, tingkat TINYINT, fase ENUM('A','B','C'),
  tp_id         INT UNSIGNED NULL, guru_id INT UNSIGNED NULL,
  tipe          ENUM('PG','PG_KOMPLEKS','BENAR_SALAH','MENJODOHKAN','ISIAN','URAIAN') DEFAULT 'PG',
  level_kognitif ENUM('C1','C2','C3','C4','C5','C6') DEFAULT 'C2',
  tingkat_kesulitan ENUM('MUDAH','SEDANG','SULIT') DEFAULT 'SEDANG',
  pertanyaan    TEXT NOT NULL, gambar VARCHAR(255) NULL, audio VARCHAR(255) NULL,
  opsi          JSON NULL COMMENT '[{"key":"A","teks":"...","gambar":null}]',
  kunci         VARCHAR(100) NULL, pembahasan TEXT NULL,
  skor          DECIMAL(5,2) DEFAULT 1,
  sumber_ai     TINYINT(1) DEFAULT 0,
  dipakai       INT DEFAULT 0, rata_benar DECIMAL(5,2) NULL,
  status        ENUM('DRAFT','SIAP','ARSIP') DEFAULT 'SIAP',
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (mapel_id) REFERENCES mapel(id) ON DELETE CASCADE,
  INDEX (mapel_id, tingkat)
) ENGINE=InnoDB;

CREATE TABLE ujian (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  kode          VARCHAR(20) UNIQUE, judul VARCHAR(200) NOT NULL,
  jenis         ENUM('ULANGAN_HARIAN','STS','SAS','ASESMEN_FORMATIF','ASESMEN_DIAGNOSTIK','TRYOUT','ASPD') DEFAULT 'ULANGAN_HARIAN',
  kategori_nilai ENUM('FORMATIF','SUMATIF') DEFAULT 'SUMATIF',
  mapel_id      SMALLINT UNSIGNED NOT NULL, guru_id INT UNSIGNED NOT NULL,
  tingkat       TINYINT,
  deskripsi     TEXT,
  mulai         DATETIME, selesai DATETIME, durasi_menit SMALLINT DEFAULT 60,
  acak_soal     TINYINT(1) DEFAULT 1, acak_opsi TINYINT(1) DEFAULT 1,
  layar_penuh   TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Paksa layar penuh saat mengerjakan',
  maks_pelanggaran TINYINT UNSIGNED NOT NULL DEFAULT 3 COMMENT '0 = tidak dibatasi',
  tampil_hasil  ENUM('LANGSUNG','SETELAH_SELESAI','MANUAL') DEFAULT 'SETELAH_SELESAI',
  tampil_pembahasan TINYINT(1) DEFAULT 1,
  token         VARCHAR(10) NULL, maks_percobaan TINYINT DEFAULT 1,
  kkm           DECIMAL(5,2) DEFAULT 70,
  status        ENUM('DRAFT','TERJADWAL','BERLANGSUNG','SELESAI','ARSIP') DEFAULT 'DRAFT',
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE ujian_kelas (
  ujian_id INT UNSIGNED, kelas_id SMALLINT UNSIGNED,
  PRIMARY KEY (ujian_id, kelas_id),
  FOREIGN KEY (ujian_id) REFERENCES ujian(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE ujian_soal (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  ujian_id  INT UNSIGNED NOT NULL, soal_id INT UNSIGNED NOT NULL,
  urutan    SMALLINT, bobot DECIMAL(5,2) DEFAULT 1,
  FOREIGN KEY (ujian_id) REFERENCES ujian(id) ON DELETE CASCADE,
  FOREIGN KEY (soal_id) REFERENCES bank_soal(id) ON DELETE CASCADE,
  UNIQUE KEY uq_us (ujian_id, soal_id)
) ENGINE=InnoDB;

CREATE TABLE ujian_peserta (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  ujian_id    INT UNSIGNED NOT NULL, siswa_id INT UNSIGNED NOT NULL,
  percobaan   TINYINT DEFAULT 1,
  mulai_at    DATETIME NULL, selesai_at DATETIME NULL,
  sisa_detik  INT NULL, urutan_soal TEXT NULL COMMENT 'csv id soal hasil acak',
  jumlah_benar SMALLINT DEFAULT 0, jumlah_salah SMALLINT DEFAULT 0,
  skor        DECIMAL(5,2) NULL, nilai DECIMAL(5,2) NULL,
  tuntas      TINYINT(1) NULL,
  pelanggaran SMALLINT DEFAULT 0 COMMENT 'jumlah pindah tab',
  status      ENUM('BELUM','BERLANGSUNG','SELESAI','DIKOREKSI') DEFAULT 'BELUM',
  ip VARCHAR(45), user_agent VARCHAR(255),
  UNIQUE KEY uq_up (ujian_id, siswa_id, percobaan)
) ENGINE=InnoDB;

CREATE TABLE ujian_jawaban (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  peserta_id  BIGINT UNSIGNED NOT NULL, soal_id INT UNSIGNED NOT NULL,
  jawaban     TEXT, benar TINYINT(1) NULL, skor DECIMAL(5,2) DEFAULT 0,
  ragu        TINYINT(1) DEFAULT 0,
  dikoreksi_ai TINYINT(1) DEFAULT 0, catatan_koreksi TEXT NULL,
  updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (peserta_id) REFERENCES ujian_peserta(id) ON DELETE CASCADE,
  UNIQUE KEY uq_uj (peserta_id, soal_id)
) ENGINE=InnoDB;

-- ---------- Game interaktif ----------
CREATE TABLE game (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode          VARCHAR(30) UNIQUE NOT NULL,
  nama          VARCHAR(120) NOT NULL,
  tipe          ENUM('KUIS_CEPAT','TEKA_SILANG','MENJODOHKAN','SUSUN_KATA','TEBAK_GAMBAR','HITUNG_CEPAT','MEMORI','PUZZLE','LABIRIN_KATA','DRAG_DROP') NOT NULL,
  mapel_id      SMALLINT UNSIGNED NULL COMMENT 'null = lintas mapel',
  tingkat_min   TINYINT DEFAULT 1, tingkat_max TINYINT DEFAULT 6,
  fase          ENUM('A','B','C') NULL,
  deskripsi     TEXT, instruksi TEXT,
  thumbnail     VARCHAR(255), warna VARCHAR(9),
  konfigurasi   JSON NULL COMMENT 'durasi, nyawa, jumlah level dsb',
  poin_maks     SMALLINT DEFAULT 100,
  is_aktif      TINYINT(1) DEFAULT 1,
  dibuat_oleh   INT UNSIGNED NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE game_level (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  game_id   INT UNSIGNED NOT NULL, level SMALLINT DEFAULT 1,
  judul     VARCHAR(120), tingkat TINYINT NULL,
  data      JSON NOT NULL COMMENT 'soal / pasangan / kata sesuai tipe game',
  durasi_detik SMALLINT DEFAULT 120, poin SMALLINT DEFAULT 10,
  sumber_ai TINYINT(1) DEFAULT 0,
  FOREIGN KEY (game_id) REFERENCES game(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE game_skor (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  game_id     INT UNSIGNED NOT NULL, level_id INT UNSIGNED NULL,
  siswa_id    INT UNSIGNED NOT NULL,
  skor        SMALLINT DEFAULT 0, bintang TINYINT DEFAULT 0,
  benar SMALLINT, salah SMALLINT, durasi_detik SMALLINT,
  selesai     TINYINT(1) DEFAULT 0,
  dimainkan_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (siswa_id, game_id)
) ENGINE=InnoDB;

-- ---------- Gamifikasi lintas modul ----------
CREATE TABLE poin_siswa (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  siswa_id    INT UNSIGNED NOT NULL,
  sumber      ENUM('MATERI','TUGAS','UJIAN','GAME','LITERASI','ABSENSI','PERILAKU','MANUAL') NOT NULL,
  ref_id      BIGINT UNSIGNED NULL,
  poin        SMALLINT NOT NULL, keterangan VARCHAR(150),
  tanggal     DATE NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (siswa_id, tanggal)
) ENGINE=InnoDB;

CREATE TABLE badge (
  id        SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode      VARCHAR(40) UNIQUE, nama VARCHAR(80), deskripsi VARCHAR(200),
  ikon      VARCHAR(255), warna VARCHAR(9),
  kriteria  JSON COMMENT '{"sumber":"LITERASI","min":10}',
  poin_bonus SMALLINT DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE badge_siswa (
  siswa_id INT UNSIGNED, badge_id SMALLINT UNSIGNED,
  diraih_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (siswa_id, badge_id)
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 8 : PERPUSTAKAAN, PERPUSTAKAAN DIGITAL, LITERASI
-- =====================================================================

CREATE TABLE buku_kategori (
  id      SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode_ddc VARCHAR(10), nama VARCHAR(80), parent_id SMALLINT UNSIGNED NULL,
  warna VARCHAR(9), ikon VARCHAR(40)
) ENGINE=InnoDB;

CREATE TABLE buku (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode_buku     VARCHAR(30) UNIQUE, isbn VARCHAR(20) NULL,
  judul         VARCHAR(250) NOT NULL, subjudul VARCHAR(250),
  pengarang     VARCHAR(200), penerbit VARCHAR(120), tahun_terbit YEAR,
  edisi VARCHAR(30), jumlah_halaman SMALLINT, bahasa VARCHAR(30) DEFAULT 'Indonesia',
  kategori_id   SMALLINT UNSIGNED NULL, no_ddc VARCHAR(20),
  jenis         ENUM('FISIK','DIGITAL','KEDUANYA') DEFAULT 'FISIK',
  jenjang_baca  ENUM('A','B','C','UMUM') DEFAULT 'UMUM' COMMENT 'level literasi',
  sinopsis      TEXT, sampul VARCHAR(255),
  -- digital
  file_ebook    VARCHAR(255) NULL, format_ebook ENUM('PDF','EPUB','FLIPBOOK','AUDIO') NULL,
  url_baca      VARCHAR(255) NULL, boleh_unduh TINYINT(1) DEFAULT 0,
  -- fisik
  jumlah_eks    SMALLINT DEFAULT 0, jumlah_tersedia SMALLINT DEFAULT 0,
  lokasi_rak    VARCHAR(30),
  sumber_dana   VARCHAR(60), harga DECIMAL(12,2) NULL,
  poin_baca     SMALLINT DEFAULT 20,
  jumlah_dibaca INT DEFAULT 0, rating_avg DECIMAL(3,2) DEFAULT 0,
  status        ENUM('AKTIF','ARSIP') DEFAULT 'AKTIF',
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  FULLTEXT KEY ft_buku (judul, pengarang, sinopsis),
  INDEX (kategori_id)
) ENGINE=InnoDB;

CREATE TABLE buku_eksemplar (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  buku_id     INT UNSIGNED NOT NULL,
  kode_induk  VARCHAR(40) UNIQUE NOT NULL, barcode VARCHAR(40) UNIQUE,
  kondisi     ENUM('BAIK','RUSAK_RINGAN','RUSAK_BERAT','HILANG') DEFAULT 'BAIK',
  status      ENUM('TERSEDIA','DIPINJAM','DIPESAN','PERBAIKAN','HILANG') DEFAULT 'TERSEDIA',
  tanggal_masuk DATE,
  FOREIGN KEY (buku_id) REFERENCES buku(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE anggota_perpus (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  no_anggota  VARCHAR(30) UNIQUE,
  tipe        ENUM('SISWA','GURU','PEGAWAI') NOT NULL, ref_id INT UNSIGNED NOT NULL,
  tanggal_daftar DATE, berlaku_sampai DATE,
  maks_pinjam TINYINT DEFAULT 2, lama_pinjam_hari TINYINT DEFAULT 7,
  status      ENUM('AKTIF','NONAKTIF','DIBLOKIR') DEFAULT 'AKTIF',
  UNIQUE KEY uq_anggota (tipe, ref_id)
) ENGINE=InnoDB;

CREATE TABLE peminjaman (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode          VARCHAR(25) UNIQUE,
  anggota_id    INT UNSIGNED NOT NULL, eksemplar_id INT UNSIGNED NOT NULL,
  tanggal_pinjam DATE NOT NULL, tanggal_jatuh_tempo DATE NOT NULL,
  tanggal_kembali DATE NULL,
  perpanjangan  TINYINT DEFAULT 0,
  status        ENUM('DIPINJAM','KEMBALI','TERLAMBAT','HILANG') DEFAULT 'DIPINJAM',
  hari_telat    SMALLINT DEFAULT 0, denda DECIMAL(10,2) DEFAULT 0,
  denda_lunas   TINYINT(1) DEFAULT 1,
  petugas_pinjam INT UNSIGNED NULL, petugas_kembali INT UNSIGNED NULL,
  catatan       VARCHAR(200),
  FOREIGN KEY (anggota_id) REFERENCES anggota_perpus(id),
  FOREIGN KEY (eksemplar_id) REFERENCES buku_eksemplar(id),
  INDEX (status), INDEX (tanggal_jatuh_tempo)
) ENGINE=InnoDB;

CREATE TABLE literasi_log (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  siswa_id      INT UNSIGNED NOT NULL, buku_id INT UNSIGNED NULL,
  judul_luar    VARCHAR(200) NULL COMMENT 'buku di luar koleksi perpus',
  tanggal       DATE NOT NULL,
  halaman_awal  SMALLINT, halaman_akhir SMALLINT,
  menit_baca    SMALLINT DEFAULT 15,
  ringkasan     TEXT, kesan TEXT,
  emosi         ENUM('SENANG','BIASA','SEDIH','SERU','BOSAN') NULL,
  foto_bukti    VARCHAR(255) NULL,
  status_verifikasi ENUM('MENUNGGU','DIVERIFIKASI','DITOLAK') DEFAULT 'MENUNGGU',
  verifikator_id INT UNSIGNED NULL, catatan_guru TEXT NULL,
  poin          SMALLINT DEFAULT 0,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (siswa_id, tanggal)
) ENGINE=InnoDB;

CREATE TABLE resensi (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  buku_id   INT UNSIGNED NOT NULL, siswa_id INT UNSIGNED NOT NULL,
  rating    TINYINT, isi TEXT, 
  status    ENUM('MENUNGGU','TERBIT','DITOLAK') DEFAULT 'MENUNGGU',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_resensi (buku_id, siswa_id)
) ENGINE=InnoDB;

CREATE TABLE ebook_progres (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  buku_id     INT UNSIGNED NOT NULL, siswa_id INT UNSIGNED NOT NULL,
  halaman_terakhir SMALLINT DEFAULT 1, total_halaman SMALLINT,
  persen      TINYINT DEFAULT 0, menit_total INT DEFAULT 0,
  selesai     TINYINT(1) DEFAULT 0, selesai_at DATETIME NULL,
  bookmark    JSON NULL, terakhir_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_ep (buku_id, siswa_id)
) ENGINE=InnoDB;

CREATE TABLE target_literasi (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED, tingkat TINYINT NULL, kelas_id SMALLINT UNSIGNED NULL,
  periode       ENUM('MINGGUAN','BULANAN','SEMESTER') DEFAULT 'BULANAN',
  target_buku   SMALLINT DEFAULT 2, target_menit SMALLINT DEFAULT 300,
  keterangan    VARCHAR(150)
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 9 : ADMINISTRASI TATA USAHA
-- =====================================================================

CREATE TABLE surat_masuk (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  no_agenda   VARCHAR(30) UNIQUE, no_surat VARCHAR(80),
  tanggal_surat DATE, tanggal_terima DATE,
  pengirim    VARCHAR(150), perihal VARCHAR(250), ringkasan TEXT,
  sifat       ENUM('BIASA','PENTING','SEGERA','RAHASIA') DEFAULT 'BIASA',
  file_scan   VARCHAR(255), 
  status      ENUM('BARU','DIDISPOSISI','SELESAI','ARSIP') DEFAULT 'BARU',
  created_by  INT UNSIGNED, created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE disposisi (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  surat_masuk_id INT UNSIGNED NOT NULL,
  dari_user_id  INT UNSIGNED, ke_user_id INT UNSIGNED,
  instruksi     TEXT, batas_waktu DATE NULL,
  status        ENUM('MENUNGGU','DIBACA','DIKERJAKAN','SELESAI') DEFAULT 'MENUNGGU',
  tanggapan     TEXT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (surat_masuk_id) REFERENCES surat_masuk(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE surat_keluar (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  no_surat    VARCHAR(80) UNIQUE, tanggal DATE,
  tujuan      VARCHAR(200), perihal VARCHAR(250),
  jenis       ENUM('UNDANGAN','PEMBERITAHUAN','KETERANGAN','TUGAS','PERMOHONAN','LAINNYA') DEFAULT 'PEMBERITAHUAN',
  isi         LONGTEXT, template_id SMALLINT UNSIGNED NULL,
  file_pdf    VARCHAR(255),
  status      ENUM('DRAFT','MENUNGGU_TTD','TERKIRIM','ARSIP') DEFAULT 'DRAFT',
  ttd_oleh    INT UNSIGNED NULL, created_by INT UNSIGNED,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE surat_template (
  id        SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nama      VARCHAR(120), jenis VARCHAR(40),
  konten    LONGTEXT COMMENT 'placeholder {{nama_siswa}}, {{nisn}}, dst',
  variabel  JSON NULL
) ENGINE=InnoDB;

CREATE TABLE inventaris_kategori (
  id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode VARCHAR(15) UNIQUE, nama VARCHAR(80)
) ENGINE=InnoDB;

CREATE TABLE inventaris (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode_barang   VARCHAR(40) UNIQUE, nama VARCHAR(150),
  kategori_id   SMALLINT UNSIGNED, merk VARCHAR(80), tipe VARCHAR(80),
  tahun_perolehan YEAR, sumber_dana VARCHAR(60),
  harga_satuan  DECIMAL(14,2), jumlah SMALLINT DEFAULT 1, satuan VARCHAR(20) DEFAULT 'unit',
  kondisi_baik SMALLINT DEFAULT 0, kondisi_rusak_ringan SMALLINT DEFAULT 0, kondisi_rusak_berat SMALLINT DEFAULT 0,
  lokasi        VARCHAR(100), penanggung_jawab INT UNSIGNED NULL,
  foto          VARCHAR(255), qrcode VARCHAR(60),
  keterangan    TEXT,
  status        ENUM('AKTIF','DIHAPUS','DIPINJAM') DEFAULT 'AKTIF'
) ENGINE=InnoDB;

CREATE TABLE inventaris_mutasi (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  inventaris_id INT UNSIGNED NOT NULL,
  jenis     ENUM('MASUK','KELUAR','PINJAM','KEMBALI','PERBAIKAN','PENGHAPUSAN') NOT NULL,
  jumlah    SMALLINT, tanggal DATE, dari VARCHAR(100), ke VARCHAR(100),
  keterangan TEXT, petugas_id INT UNSIGNED,
  FOREIGN KEY (inventaris_id) REFERENCES inventaris(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------- Keuangan sederhana (SPP / iuran) ----------
CREATE TABLE jenis_tagihan (
  id        SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode VARCHAR(20) UNIQUE, nama VARCHAR(100),
  periode ENUM('BULANAN','SEMESTER','TAHUNAN','SEKALI') DEFAULT 'BULANAN',
  nominal_default DECIMAL(12,2) DEFAULT 0, is_aktif TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE tagihan (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  no_tagihan    VARCHAR(30) UNIQUE,
  tahun_ajaran_id SMALLINT UNSIGNED, siswa_id INT UNSIGNED NOT NULL,
  jenis_id      SMALLINT UNSIGNED, bulan TINYINT NULL, tahun YEAR NULL,
  nominal       DECIMAL(12,2), diskon DECIMAL(12,2) DEFAULT 0,
  total         DECIMAL(12,2), terbayar DECIMAL(12,2) DEFAULT 0,
  jatuh_tempo   DATE,
  status        ENUM('BELUM','SEBAGIAN','LUNAS','DIBEBASKAN') DEFAULT 'BELUM',
  keterangan    VARCHAR(200),
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE,
  INDEX (siswa_id, status)
) ENGINE=InnoDB;

CREATE TABLE pembayaran (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  no_kwitansi VARCHAR(30) UNIQUE, tagihan_id INT UNSIGNED NOT NULL,
  tanggal     DATETIME, nominal DECIMAL(12,2),
  metode      ENUM('TUNAI','TRANSFER','QRIS','VA') DEFAULT 'TUNAI',
  bukti       VARCHAR(255) NULL, petugas_id INT UNSIGNED,
  catatan     VARCHAR(200),
  FOREIGN KEY (tagihan_id) REFERENCES tagihan(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE mutasi_siswa (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  siswa_id    INT UNSIGNED NOT NULL,
  jenis       ENUM('MASUK','PINDAH_KELUAR','PINDAH_MASUK','LULUS','KELUAR') NOT NULL,
  tanggal     DATE, sekolah_asal VARCHAR(150), sekolah_tujuan VARCHAR(150),
  alasan      TEXT, no_surat VARCHAR(60), file_surat VARCHAR(255),
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 10 : PENILAIAN & E-RAPOR (SINKRON DAPODIK / EMIS)
-- =====================================================================

CREATE TABLE nilai (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  siswa_id      INT UNSIGNED NOT NULL, kelas_id SMALLINT UNSIGNED NOT NULL,
  mapel_id      SMALLINT UNSIGNED NOT NULL, guru_id INT UNSIGNED NULL,
  tp_id         INT UNSIGNED NULL,
  kategori      ENUM('FORMATIF','SUMATIF_LINGKUP','STS','SAS','PROYEK','PRAKTIK') NOT NULL,
  sumber        ENUM('MANUAL','TUGAS','UJIAN','GAME') DEFAULT 'MANUAL',
  ref_id        BIGINT UNSIGNED NULL,
  judul         VARCHAR(150), nilai DECIMAL(5,2), bobot TINYINT DEFAULT 1,
  tanggal       DATE,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (siswa_id, mapel_id, tahun_ajaran_id), INDEX (kelas_id)
) ENGINE=InnoDB;

CREATE TABLE nilai_akhir (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  siswa_id      INT UNSIGNED NOT NULL, kelas_id SMALLINT UNSIGNED NOT NULL,
  mapel_id      SMALLINT UNSIGNED NOT NULL,
  rata_formatif DECIMAL(5,2) NULL, rata_sumatif DECIMAL(5,2) NULL,
  nilai_sts DECIMAL(5,2) NULL, nilai_sas DECIMAL(5,2) NULL,
  nilai_akhir   DECIMAL(5,2) NULL, predikat CHAR(1) NULL,
  kkm           DECIMAL(5,2) DEFAULT 70, tuntas TINYINT(1) NULL,
  capaian_tertinggi TEXT NULL, capaian_perlu_bimbingan TEXT NULL,
  deskripsi     TEXT NULL, deskripsi_ai TINYINT(1) DEFAULT 0,
  dikunci       TINYINT(1) DEFAULT 0,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_na (tahun_ajaran_id, siswa_id, mapel_id)
) ENGINE=InnoDB;

CREATE TABLE nilai_dimensi (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  siswa_id      INT UNSIGNED NOT NULL, dimensi_id TINYINT UNSIGNED NOT NULL,
  capaian       ENUM('BB','MB','BSH','SB') NULL COMMENT 'Belum/Mulai/Sesuai Harapan/Sangat Berkembang',
  deskripsi     TEXT, catatan_guru TEXT,
  UNIQUE KEY uq_nd (tahun_ajaran_id, siswa_id, dimensi_id)
) ENGINE=InnoDB;

CREATE TABLE kokurikuler (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED, kelas_id SMALLINT UNSIGNED NULL,
  judul         VARCHAR(200), tema VARCHAR(120),
  deskripsi     TEXT, dimensi VARCHAR(120) COMMENT 'csv dimensi_lulusan',
  metode        VARCHAR(60) DEFAULT 'Project Based Learning',
  mulai DATE, selesai DATE, koordinator_id INT UNSIGNED NULL
) ENGINE=InnoDB;

CREATE TABLE kokurikuler_nilai (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kokurikuler_id INT UNSIGNED NOT NULL, siswa_id INT UNSIGNED NOT NULL,
  dimensi_id    TINYINT UNSIGNED, capaian ENUM('BB','MB','BSH','SB'),
  catatan       TEXT,
  UNIQUE KEY uq_kn (kokurikuler_id, siswa_id, dimensi_id)
) ENGINE=InnoDB;

CREATE TABLE ekstrakurikuler (
  id        SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  nama      VARCHAR(100), pembina_id INT UNSIGNED NULL,
  jadwal    VARCHAR(80), kuota SMALLINT, deskripsi TEXT, is_aktif TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE ekskul_siswa (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED, ekskul_id SMALLINT UNSIGNED, siswa_id INT UNSIGNED,
  predikat      ENUM('SANGAT_BAIK','BAIK','CUKUP','KURANG') NULL,
  deskripsi     TEXT,
  UNIQUE KEY uq_es (tahun_ajaran_id, ekskul_id, siswa_id)
) ENGINE=InnoDB;

CREATE TABLE prestasi (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  siswa_id  INT UNSIGNED NOT NULL, tahun_ajaran_id SMALLINT UNSIGNED,
  nama_lomba VARCHAR(200), tingkat ENUM('SEKOLAH','KECAMATAN','KABUPATEN','PROVINSI','NASIONAL','INTERNASIONAL'),
  peringkat VARCHAR(40), tanggal DATE, penyelenggara VARCHAR(150),
  file_sertifikat VARCHAR(255),
  FOREIGN KEY (siswa_id) REFERENCES siswa(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE catatan_perilaku (
  id        BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  siswa_id  INT UNSIGNED NOT NULL, guru_id INT UNSIGNED NOT NULL,
  tanggal   DATE NOT NULL,
  jenis     ENUM('POSITIF','PERLU_PERHATIAN','PELANGGARAN') DEFAULT 'POSITIF',
  kategori  VARCHAR(60), catatan TEXT, poin SMALLINT DEFAULT 0,
  tindak_lanjut TEXT, foto VARCHAR(255) NULL,
  lapor_ortu TINYINT(1) DEFAULT 1, dibaca_ortu TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (siswa_id, tanggal)
) ENGINE=InnoDB;

CREATE TABLE rapor (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tahun_ajaran_id SMALLINT UNSIGNED NOT NULL,
  siswa_id      INT UNSIGNED NOT NULL, kelas_id SMALLINT UNSIGNED NOT NULL,
  jenis         ENUM('TENGAH_SEMESTER','AKHIR_SEMESTER','KENAIKAN','KELULUSAN') DEFAULT 'AKHIR_SEMESTER',
  kurikulum     ENUM('MERDEKA','KBC','K13') DEFAULT 'MERDEKA',
  jumlah_hadir SMALLINT DEFAULT 0, jumlah_sakit SMALLINT DEFAULT 0,
  jumlah_izin SMALLINT DEFAULT 0, jumlah_alpa SMALLINT DEFAULT 0,
  tinggi_badan SMALLINT NULL, berat_badan SMALLINT NULL,
  catatan_wali  TEXT, catatan_ai TINYINT(1) DEFAULT 0,
  keputusan     ENUM('NAIK','TINGGAL','LULUS','-') DEFAULT '-',
  naik_ke_kelas TINYINT NULL,
  peringkat     SMALLINT NULL, rata_rata DECIMAL(5,2) NULL,
  tanggal_terbit DATE NULL,
  file_pdf      VARCHAR(255) NULL,
  status        ENUM('DRAFT','FINAL','TERBIT') DEFAULT 'DRAFT',
  dibuka_ortu   TINYINT(1) DEFAULT 0, dibuka_at DATETIME NULL,
  UNIQUE KEY uq_rapor (tahun_ajaran_id, siswa_id, jenis)
) ENGINE=InnoDB;

CREATE TABLE sync_dapodik (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  jenis       ENUM('TARIK_PTK','TARIK_SISWA','TARIK_ROMBEL','KIRIM_NILAI','KIRIM_KEHADIRAN','KIRIM_RAPOR') NOT NULL,
  sumber      ENUM('DAPODIK','EMIS','MANUAL_EXCEL') DEFAULT 'DAPODIK',
  tahun_ajaran_id SMALLINT UNSIGNED,
  jumlah_data INT DEFAULT 0, jumlah_sukses INT DEFAULT 0, jumlah_gagal INT DEFAULT 0,
  file_sumber VARCHAR(255) NULL, response_log LONGTEXT NULL,
  status      ENUM('ANTRE','PROSES','SUKSES','GAGAL','SEBAGIAN') DEFAULT 'ANTRE',
  dijalankan_oleh INT UNSIGNED, mulai_at DATETIME, selesai_at DATETIME
) ENGINE=InnoDB;

CREATE TABLE dapodik_mapping (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  entitas     ENUM('SISWA','PTK','ROMBEL','MAPEL') NOT NULL,
  local_id    INT UNSIGNED NOT NULL,
  dapodik_id  VARCHAR(64) NOT NULL COMMENT 'GUID peserta_didik_id / ptk_id',
  terakhir_sync DATETIME,
  UNIQUE KEY uq_map (entitas, local_id)
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 11 : KOMUNIKASI, NOTIFIKASI, LAPORAN KE ORANG TUA
-- =====================================================================

CREATE TABLE pengumuman (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  judul     VARCHAR(200), isi LONGTEXT, gambar VARCHAR(255),
  target    SET('SEMUA','GURU','SISWA','ORTU','TU') DEFAULT 'SEMUA',
  kelas_id  SMALLINT UNSIGNED NULL COMMENT 'khusus kelas tertentu',
  prioritas ENUM('BIASA','PENTING','URGENT') DEFAULT 'BIASA',
  mulai_tampil DATETIME, selesai_tampil DATETIME,
  kirim_wa  TINYINT(1) DEFAULT 0, kirim_push TINYINT(1) DEFAULT 1,
  dibuat_oleh INT UNSIGNED, created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (mulai_tampil)
) ENGINE=InnoDB;

CREATE TABLE notifikasi (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  tipe        VARCHAR(40) COMMENT 'ABSENSI, NILAI, TUGAS, TAGIHAN, PERPUS, PENGUMUMAN',
  judul       VARCHAR(150), pesan TEXT,
  ref_tabel   VARCHAR(60), ref_id BIGINT UNSIGNED NULL,
  deeplink    VARCHAR(150) NULL COMMENT 'simap://absensi/123',
  dibaca      TINYINT(1) DEFAULT 0, dibaca_at DATETIME NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id, dibaca), INDEX (created_at)
) ENGINE=InnoDB;

CREATE TABLE wa_outbox (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  tujuan      VARCHAR(25) NOT NULL, nama_tujuan VARCHAR(120),
  pesan       TEXT NOT NULL, media_url VARCHAR(255) NULL,
  tipe        VARCHAR(40), ref_tabel VARCHAR(60), ref_id BIGINT UNSIGNED NULL,
  status      ENUM('ANTRE','TERKIRIM','GAGAL','DIBATALKAN') DEFAULT 'ANTRE',
  percobaan   TINYINT DEFAULT 0, response TEXT NULL,
  jadwal_kirim DATETIME NULL, terkirim_at DATETIME NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (status, jadwal_kirim)
) ENGINE=InnoDB;

CREATE TABLE pesan (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  dari_user_id INT UNSIGNED NOT NULL, ke_user_id INT UNSIGNED NOT NULL,
  siswa_id    INT UNSIGNED NULL COMMENT 'konteks anak yang dibahas',
  isi         TEXT, lampiran VARCHAR(255) NULL,
  dibaca      TINYINT(1) DEFAULT 0, dibaca_at DATETIME NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (dari_user_id, ke_user_id), INDEX (created_at)
) ENGINE=InnoDB;

CREATE TABLE laporan_harian (
  id            BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  siswa_id      INT UNSIGNED NOT NULL, tanggal DATE NOT NULL,
  ringkasan     TEXT COMMENT 'digenerate otomatis tiap sore',
  status_absen  VARCHAR(20), jam_masuk TIME NULL, jam_pulang TIME NULL,
  materi_selesai SMALLINT DEFAULT 0, tugas_selesai SMALLINT DEFAULT 0,
  tugas_pending SMALLINT DEFAULT 0, menit_literasi SMALLINT DEFAULT 0,
  poin_hari_ini SMALLINT DEFAULT 0, nilai_baru SMALLINT DEFAULT 0,
  catatan_guru  TEXT NULL, mood ENUM('SENANG','BIASA','KURANG') NULL,
  dikirim       TINYINT(1) DEFAULT 0, dibaca_ortu TINYINT(1) DEFAULT 0,
  UNIQUE KEY uq_lh (siswa_id, tanggal), INDEX (tanggal)
) ENGINE=InnoDB;

-- =====================================================================
-- BAGIAN 12 : INTEGRASI AI
-- =====================================================================

CREATE TABLE ai_template (
  id        SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  kode      VARCHAR(50) UNIQUE NOT NULL,
  nama      VARCHAR(120), modul VARCHAR(40),
  deskripsi VARCHAR(250),
  system_prompt TEXT, user_prompt TEXT COMMENT 'pakai placeholder {{var}}',
  model     VARCHAR(60) DEFAULT 'claude-sonnet-4-6',
  max_tokens SMALLINT DEFAULT 4000, temperature DECIMAL(3,2) DEFAULT 0.7,
  output_format ENUM('TEKS','JSON','HTML') DEFAULT 'TEKS',
  is_aktif  TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE ai_log (
  id          BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id     INT UNSIGNED NULL, template_kode VARCHAR(50),
  modul       VARCHAR(40), ref_tabel VARCHAR(60), ref_id BIGINT UNSIGNED NULL,
  prompt      LONGTEXT, response LONGTEXT,
  model VARCHAR(60), token_input INT, token_output INT,
  biaya_rp    DECIMAL(12,2) DEFAULT 0,
  durasi_ms   INT, status ENUM('SUKSES','GAGAL','TIMEOUT') DEFAULT 'SUKSES',
  error_msg   TEXT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id), INDEX (created_at)
) ENGINE=InnoDB;

CREATE TABLE ai_kuota (
  id          INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL, periode CHAR(7) COMMENT '2026-08',
  jumlah_request INT DEFAULT 0, token_terpakai INT DEFAULT 0,
  batas_request INT DEFAULT 200,
  UNIQUE KEY uq_kuota (user_id, periode)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;