-- =====================================================================
-- DATABASE DUMP & PRELOAD SCRIPT: eSantri Web
-- Dialect: MySQL 8.0+ / MariaDB 10.5+
-- Generated Date: 2026-10-23
-- Target File: dump1.sql
-- Description: Skema database relasional lengkap untuk migrasi seluruh
--              business logic dan pengolahan data aplikasi eSantri ke Backend API.
-- =====================================================================

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

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+07:00";

-- =====================================================================
-- 1. MODUL OTENTIKASI, PENGGUNA & AUDIT TRAIL
-- =====================================================================

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NULL,
  `phone` VARCHAR(30) NULL,
  `role` ENUM('admin', 'staff', 'musyrif', 'guru', 'keuangan', 'wali') NOT NULL DEFAULT 'staff',
  `is_default_admin` TINYINT(1) NOT NULL DEFAULT 0,
  `recovery_key_hash` VARCHAR(255) NULL,
  `security_question` VARCHAR(255) NULL,
  `security_answer_hash` VARCHAR(255) NULL,
  `permission_version` INT NOT NULL DEFAULT 1,
  `last_login` DATETIME NULL,
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_users_role` (`role`),
  INDEX `idx_users_deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Data akun pengguna dan operator sistem';

DROP TABLE IF EXISTS `user_permissions`;
CREATE TABLE `user_permissions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `feature_name` VARCHAR(50) NOT NULL,
  `access_level` ENUM('none', 'read', 'write') NOT NULL DEFAULT 'none',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_user_feature` (`user_id`, `feature_name`),
  CONSTRAINT `fk_permissions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Hak akses detail granular per fitur per pengguna';

DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
  `id` VARCHAR(64) PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NULL,
  `username` VARCHAR(100) NOT NULL,
  `table_name` VARCHAR(100) NOT NULL,
  `operation` ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
  `record_id` VARCHAR(100) NOT NULL,
  `old_data` LONGTEXT NULL COMMENT 'JSON snapshot sebelum perubahan',
  `new_data` LONGTEXT NULL COMMENT 'JSON snapshot sesudah perubahan',
  `ip_address` VARCHAR(45) NULL,
  `user_agent` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_audit_table_op` (`table_name`, `operation`),
  INDEX `idx_audit_record` (`record_id`),
  INDEX `idx_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Log audit forensik rekam jejak perubahan data';

DROP TABLE IF EXISTS `digital_assets`;
CREATE TABLE `digital_assets` (
  `id` VARCHAR(64) PRIMARY KEY,
  `file_name` VARCHAR(255) NOT NULL,
  `file_type` VARCHAR(100) NOT NULL,
  `file_size` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `storage_path` VARCHAR(500) NOT NULL,
  `url` VARCHAR(500) NOT NULL,
  `uploaded_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_assets_type` (`file_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Aset berkas digital (foto santri, bukti pembayaran, dokumen)';

-- =====================================================================
-- 2. MODUL PROFIL INSTITUSI & STRUKTUR MASTER PENDIDIKAN
-- =====================================================================

DROP TABLE IF EXISTS `pondok_settings`;
CREATE TABLE `pondok_settings` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama_yayasan` VARCHAR(200) NOT NULL,
  `nama_ponpes` VARCHAR(200) NOT NULL,
  `nspp` VARCHAR(50) NULL,
  `npsn` VARCHAR(50) NULL,
  `alamat` TEXT NOT NULL,
  `telepon` VARCHAR(50) NULL,
  `email` VARCHAR(100) NULL,
  `website` VARCHAR(150) NULL,
  `logo_yayasan_url` VARCHAR(500) NULL,
  `logo_ponpes_url` VARCHAR(500) NULL,
  `multi_user_mode` TINYINT(1) NOT NULL DEFAULT 0,
  `surat_tagihan_pembuka` TEXT NULL,
  `surat_tagihan_penutup` TEXT NULL,
  `surat_tagihan_catatan` TEXT NULL,
  `pesan_wa_tunggakan` TEXT NULL,
  `hijri_adjustment` INT NOT NULL DEFAULT 0,
  `tanggal_rapor_default` VARCHAR(50) NULL,
  `tempat_rapor_default` VARCHAR(100) NULL,
  `format_tanggal_rapor_default` VARCHAR(20) DEFAULT 'masehi',
  `nis_settings` JSON NULL COMMENT 'Konfigurasi auto generate NIS santri',
  `portal_config` JSON NULL COMMENT 'Konfigurasi portal wali santri publik',
  `ai_config` JSON NULL COMMENT 'Konfigurasi provider AI chatbot & poster',
  `perpus_config` JSON NULL COMMENT 'Aturan peminjaman perpustakaan',
  `backup_config` JSON NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Profil profil pesantren dan preferensi sistem global';

DROP TABLE IF EXISTS `academic_years`;
CREATE TABLE `academic_years` (
  `id` VARCHAR(50) PRIMARY KEY,
  `label_masehi` VARCHAR(50) NOT NULL,
  `masehi_start_month` INT NOT NULL DEFAULT 7,
  `masehi_start_year` INT NOT NULL,
  `masehi_end_month` INT NOT NULL DEFAULT 6,
  `masehi_end_year` INT NOT NULL,
  `hijri_enabled` TINYINT(1) NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tahun ajaran akademik aktif dan arsip';

DROP TABLE IF EXISTS `jenjang`;
CREATE TABLE `jenjang` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(100) NOT NULL,
  `kode` VARCHAR(20) NOT NULL UNIQUE,
  `hari_libur` JSON NULL COMMENT 'Array angka hari libur mingguan, e.g. [5]=Jumat',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jenjang pendidikan (SW, SU, SMP, SMA, dll)';

DROP TABLE IF EXISTS `kelas`;
CREATE TABLE `kelas` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `jenjang_id` BIGINT UNSIGNED NOT NULL,
  `nama` VARCHAR(50) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_kelas_jenjang` (`jenjang_id`),
  CONSTRAINT `fk_kelas_jenjang` FOREIGN KEY (`jenjang_id`) REFERENCES `jenjang` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tingkat kelas dalam jenjang (Kelas 1, 2, 3)';

DROP TABLE IF EXISTS `tenaga_pengajar`;
CREATE TABLE `tenaga_pengajar` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(150) NOT NULL,
  `nip` VARCHAR(50) NULL,
  `gender` ENUM('Laki-laki', 'Perempuan') NULL,
  `telepon` VARCHAR(30) NULL,
  `alamat` TEXT NULL,
  `config_gaji` JSON NULL COMMENT 'Konfigurasi gaji pokok, tunjangan, honor per jam',
  `hari_masuk` JSON NULL COMMENT 'Array hari mengajar yang diizinkan [0..6]',
  `kompetensi_mapel_ids` JSON NULL COMMENT 'Array ID mapel yang dikuasai',
  `status_keaktifan` ENUM('Aktif', 'Cuti', 'Nonaktif') NOT NULL DEFAULT 'Aktif',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Data guru pengajar, asatidz, dan staf kependidikan';

DROP TABLE IF EXISTS `riwayat_jabatan_pengajar`;
CREATE TABLE `riwayat_jabatan_pengajar` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `pengajar_id` BIGINT UNSIGNED NOT NULL,
  `jabatan` VARCHAR(100) NOT NULL,
  `tanggal_mulai` DATE NOT NULL,
  `tanggal_selesai` DATE NULL,
  `keterangan` TEXT NULL,
  INDEX `idx_jabatan_pengajar` (`pengajar_id`),
  CONSTRAINT `fk_jabatan_pengajar` FOREIGN KEY (`pengajar_id`) REFERENCES `tenaga_pengajar` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Histori jabatan struktural asatidz';

DROP TABLE IF EXISTS `rombel`;
CREATE TABLE `rombel` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kelas_id` BIGINT UNSIGNED NOT NULL,
  `nama` VARCHAR(100) NOT NULL,
  `wali_kelas_id` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_rombel_kelas` (`kelas_id`),
  INDEX `idx_rombel_wali` (`wali_kelas_id`),
  CONSTRAINT `fk_rombel_kelas` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rombel_wali` FOREIGN KEY (`wali_kelas_id`) REFERENCES `tenaga_pengajar` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rombongan belajar / kelas paralel santri';

DROP TABLE IF EXISTS `mata_pelajaran`;
CREATE TABLE `mata_pelajaran` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `jenjang_id` BIGINT UNSIGNED NOT NULL,
  `nama` VARCHAR(150) NOT NULL,
  `kode` VARCHAR(50) NULL,
  `kkm` DECIMAL(5,2) NOT NULL DEFAULT 70.00,
  `kategori` VARCHAR(50) NOT NULL DEFAULT 'Umum',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_mapel_jenjang` (`jenjang_id`),
  CONSTRAINT `fk_mapel_jenjang` FOREIGN KEY (`jenjang_id`) REFERENCES `jenjang` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Mata pelajaran dan kitab kurikulum';

-- =====================================================================
-- 3. MODUL KEASRAMAAN & KAMAR SANTRI
-- =====================================================================

DROP TABLE IF EXISTS `gedung_asrama`;
CREATE TABLE `gedung_asrama` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(150) NOT NULL,
  `jenis` ENUM('Putra', 'Putri') NOT NULL,
  `lokasi` VARCHAR(200) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Gedung komplek asrama putra dan putri';

DROP TABLE IF EXISTS `kamar`;
CREATE TABLE `kamar` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `gedung_id` BIGINT UNSIGNED NOT NULL,
  `nama` VARCHAR(100) NOT NULL,
  `kapasitas` INT NOT NULL DEFAULT 10,
  `lantai` VARCHAR(50) NULL,
  `musyrif_id` BIGINT UNSIGNED NULL,
  `ketua_kamar_id` BIGINT UNSIGNED NULL,
  `fasilitas` TEXT NULL,
  `kondisi_fasilitas` ENUM('Baik', 'Cukup', 'Kurang') NOT NULL DEFAULT 'Baik',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_kamar_gedung` (`gedung_id`),
  INDEX `idx_kamar_musyrif` (`musyrif_id`),
  CONSTRAINT `fk_kamar_gedung` FOREIGN KEY (`gedung_id`) REFERENCES `gedung_asrama` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_kamar_musyrif` FOREIGN KEY (`musyrif_id`) REFERENCES `tenaga_pengajar` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Data kamar santri asrama';

DROP TABLE IF EXISTS `inspeksi_kamar`;
CREATE TABLE `inspeksi_kamar` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kamar_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `skor_kebersihan` INT NOT NULL DEFAULT 0,
  `skor_kerapian` INT NOT NULL DEFAULT 0,
  `skor_kedisiplinan` INT NOT NULL DEFAULT 0,
  `skor_total` INT NOT NULL DEFAULT 0,
  `predikat` VARCHAR(50) NOT NULL,
  `musyrif_pemeriksa` VARCHAR(150) NOT NULL,
  `catatan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_inspeksi_kamar` (`kamar_id`, `tanggal`),
  CONSTRAINT `fk_inspeksi_kamar` FOREIGN KEY (`kamar_id`) REFERENCES `kamar` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Penilaian berkala kebersihan dan kerapian kamar';

DROP TABLE IF EXISTS `jurnal_asrama`;
CREATE TABLE `jurnal_asrama` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `gedung_id` BIGINT UNSIGNED NOT NULL,
  `kamar_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `waktu` VARCHAR(10) NOT NULL,
  `jenis_kegiatan` VARCHAR(100) NOT NULL,
  `keterangan` TEXT NOT NULL,
  `tindakan` TEXT NULL,
  `nama_musyrif` VARCHAR(150) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_jurnal_asrama` (`kamar_id`, `tanggal`),
  CONSTRAINT `fk_jurnal_asrama_kamar` FOREIGN KEY (`kamar_id`) REFERENCES `kamar` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jurnal harian kejadian dan kegiatan asrama';

-- =====================================================================
-- 4. MODUL HALAQAH & TAHFIZH AL-QURAN
-- =====================================================================

DROP TABLE IF EXISTS `kelompok_halaqah`;
CREATE TABLE `kelompok_halaqah` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(150) NOT NULL,
  `muhaffizh_id` BIGINT UNSIGNED NOT NULL,
  `target_juz` INT NOT NULL DEFAULT 30,
  `lokasi` VARCHAR(150) NULL,
  `keterangan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_halaqah_muhaffizh` (`muhaffizh_id`),
  CONSTRAINT `fk_halaqah_muhaffizh` FOREIGN KEY (`muhaffizh_id`) REFERENCES `tenaga_pengajar` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Kelompok halaqah tahfizh quran santri';

-- =====================================================================
-- 5. MODUL KESISWAAN (SANTRI, PRESTASI, PELANGGARAN)
-- =====================================================================

DROP TABLE IF EXISTS `santri`;
CREATE TABLE `santri` (
  `id` BIGINT UNSIGNED PRIMARY KEY,
  `nis` VARCHAR(50) NOT NULL UNIQUE,
  `nisn` VARCHAR(50) NULL,
  `nik` VARCHAR(50) NULL,
  `nama_lengkap` VARCHAR(150) NOT NULL,
  `nama_hijrah` VARCHAR(100) NULL,
  `jenis_kelamin` ENUM('Laki-laki', 'Perempuan') NOT NULL,
  `tempat_lahir` VARCHAR(100) NOT NULL,
  `tanggal_lahir` DATE NOT NULL,
  `kewarganegaraan` VARCHAR(20) NOT NULL DEFAULT 'WNI',
  `alamat_detail` TEXT NULL,
  `desa_kelurahan` VARCHAR(100) NULL,
  `kecamatan` VARCHAR(100) NULL,
  `kabupaten_kota` VARCHAR(100) NULL,
  `provinsi` VARCHAR(100) NULL,
  `kode_pos` VARCHAR(20) NULL,
  `nama_ayah` VARCHAR(150) NULL,
  `nik_ayah` VARCHAR(50) NULL,
  `pekerjaan_ayah` VARCHAR(100) NULL,
  `pendidikan_ayah` VARCHAR(50) NULL,
  `penghasilan_ayah` VARCHAR(100) NULL,
  `telepon_ayah` VARCHAR(30) NULL,
  `nama_ibu` VARCHAR(150) NULL,
  `nik_ibu` VARCHAR(50) NULL,
  `pekerjaan_ibu` VARCHAR(100) NULL,
  `pendidikan_ibu` VARCHAR(50) NULL,
  `penghasilan_ibu` VARCHAR(100) NULL,
  `telepon_ibu` VARCHAR(30) NULL,
  `nama_wali` VARCHAR(150) NULL,
  `telepon_wali` VARCHAR(30) NULL,
  `hubungan_wali` VARCHAR(50) NULL,
  `jenjang_id` BIGINT UNSIGNED NOT NULL,
  `kelas_id` BIGINT UNSIGNED NOT NULL,
  `rombel_id` BIGINT UNSIGNED NOT NULL,
  `kamar_id` BIGINT UNSIGNED NULL,
  `halaqah_id` BIGINT UNSIGNED NULL,
  `tanggal_masuk` DATE NOT NULL,
  `status` ENUM('Aktif', 'Lulus', 'Keluar/Pindah', 'Hiatus', 'DO') NOT NULL DEFAULT 'Aktif',
  `tanggal_status` DATE NULL,
  `foto_url` VARCHAR(500) NULL,
  `jenis_santri` VARCHAR(50) NOT NULL DEFAULT 'Mondok - Baru',
  `sekolah_asal` VARCHAR(150) NULL,
  `hobi` JSON NULL,
  `cita_cita` VARCHAR(100) NULL,
  `anak_ke` INT NULL,
  `jumlah_saudara` INT NULL,
  `golongan_darah` VARCHAR(10) NULL,
  `riwayat_penyakit` TEXT NULL,
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_santri_rombel` (`rombel_id`),
  INDEX `idx_santri_kamar` (`kamar_id`),
  INDEX `idx_santri_halaqah` (`halaqah_id`),
  INDEX `idx_santri_status` (`status`, `deleted`),
  CONSTRAINT `fk_santri_jenjang` FOREIGN KEY (`jenjang_id`) REFERENCES `jenjang` (`id`),
  CONSTRAINT `fk_santri_kelas` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`),
  CONSTRAINT `fk_santri_rombel` FOREIGN KEY (`rombel_id`) REFERENCES `rombel` (`id`),
  CONSTRAINT `fk_santri_kamar` FOREIGN KEY (`kamar_id`) REFERENCES `kamar` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_santri_halaqah` FOREIGN KEY (`halaqah_id`) REFERENCES `kelompok_halaqah` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Data master biodata dan kesiswaan santri';

DROP TABLE IF EXISTS `tahfizh_records`;
CREATE TABLE `tahfizh_records` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `halaqah_id` BIGINT UNSIGNED NULL,
  `muhaffizh_id` BIGINT UNSIGNED NULL,
  `tanggal` DATE NOT NULL,
  `waktu_setor` VARCHAR(20) NOT NULL DEFAULT 'Subuh',
  `tipe` ENUM('Ziyadah', 'Murajaah', 'Tasmi', 'Ujian') NOT NULL DEFAULT 'Ziyadah',
  `surat_mulai` INT NOT NULL,
  `ayat_mulai` INT NOT NULL,
  `surat_selesai` INT NOT NULL,
  `ayat_selesai` INT NOT NULL,
  `juz` INT NOT NULL,
  `halaman` INT NULL,
  `kelancaran` ENUM('A', 'B', 'C', 'D') NOT NULL DEFAULT 'B',
  `tajwid` ENUM('A', 'B', 'C', 'D') NOT NULL DEFAULT 'B',
  `fashahah` ENUM('A', 'B', 'C', 'D') NOT NULL DEFAULT 'B',
  `nilai_angka` DECIMAL(5,2) NULL,
  `catatan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_tahfizh_santri_tgl` (`santri_id`, `tanggal`),
  CONSTRAINT `fk_tahfizh_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tahfizh_halaqah` FOREIGN KEY (`halaqah_id`) REFERENCES `kelompok_halaqah` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_tahfizh_muhaffizh` FOREIGN KEY (`muhaffizh_id`) REFERENCES `tenaga_pengajar` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Catatan setoran hafalan ziyadah dan murajaah tahfizh';

DROP TABLE IF EXISTS `santri_prestasi`;
CREATE TABLE `santri_prestasi` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `nama` VARCHAR(200) NOT NULL,
  `tingkat` VARCHAR(50) NOT NULL,
  `jenis` VARCHAR(50) NOT NULL,
  `tahun` INT NOT NULL,
  `penyelenggara` VARCHAR(150) NULL,
  `peringkat` VARCHAR(50) NULL,
  `sertifikat_url` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_prestasi_santri` (`santri_id`),
  CONSTRAINT `fk_prestasi_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Daftar prestasi dan kejuaraan santri';

DROP TABLE IF EXISTS `santri_pelanggaran`;
CREATE TABLE `santri_pelanggaran` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `kategori` VARCHAR(50) NOT NULL,
  `deskripsi` TEXT NOT NULL,
  `poin` INT NOT NULL DEFAULT 0,
  `sanksi` TEXT NULL,
  `pj` VARCHAR(150) NULL,
  `status` ENUM('Proses', 'Selesai', 'Banding') NOT NULL DEFAULT 'Selesai',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_pelanggaran_santri` (`santri_id`),
  CONSTRAINT `fk_pelanggaran_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Catatan kedisiplinan dan pelanggaran tata tertib santri';

DROP TABLE IF EXISTS `santri_riwayat_status`;
CREATE TABLE `santri_riwayat_status` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `tanggal` DATE NOT NULL,
  `keterangan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_riwayat_status_santri` (`santri_id`),
  CONSTRAINT `fk_status_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Histori mutasi status santri (Masuk, Lulus, Hiatus, Pindah)';

-- =====================================================================
-- 6. MODUL PENERIMAAN SANTRI BARU (PSB)
-- =====================================================================

DROP TABLE IF EXISTS `psb_pendaftar`;
CREATE TABLE `psb_pendaftar` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nomor_pendaftaran` VARCHAR(50) NOT NULL UNIQUE,
  `nama_lengkap` VARCHAR(150) NOT NULL,
  `nisn` VARCHAR(50) NULL,
  `nik` VARCHAR(50) NULL,
  `jenis_kelamin` ENUM('Laki-laki', 'Perempuan') NOT NULL,
  `tempat_lahir` VARCHAR(100) NOT NULL,
  `tanggal_lahir` DATE NOT NULL,
  `alamat_detail` TEXT NULL,
  `desa_kelurahan` VARCHAR(100) NULL,
  `kecamatan` VARCHAR(100) NULL,
  `kabupaten_kota` VARCHAR(100) NULL,
  `provinsi` VARCHAR(100) NULL,
  `nama_wali` VARCHAR(150) NOT NULL,
  `nomor_hp_wali` VARCHAR(30) NOT NULL,
  `asal_sekolah` VARCHAR(150) NULL,
  `nama_ayah` VARCHAR(150) NULL,
  `nama_ibu` VARCHAR(150) NULL,
  `jenjang_id` BIGINT UNSIGNED NOT NULL,
  `gelombang` INT NOT NULL DEFAULT 1,
  `tahun_ajaran` VARCHAR(50) NOT NULL,
  `tanggal_daftar` DATE NOT NULL,
  `status` ENUM('Menunggu', 'Terverifikasi', 'Lulus', 'Tidak Lulus', 'Cadangan', 'Dibatalkan') NOT NULL DEFAULT 'Menunggu',
  `bukti_bayar_url` VARCHAR(500) NULL,
  `status_pembayaran` ENUM('Belum', 'Lunas', 'Diverifikasi') NOT NULL DEFAULT 'Belum',
  `nilai_ujian` JSON NULL COMMENT 'Nilai tes baca quran, wawancara, pengetahuan',
  `berkas_fisik` JSON NULL COMMENT 'Ceklis berkas fisik pendaftaran',
  `custom_fields_data` JSON NULL,
  `catatan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_psb_status` (`status`),
  INDEX `idx_psb_tahun_gel` (`tahun_ajaran`, `gelombang`),
  CONSTRAINT `fk_psb_jenjang` FOREIGN KEY (`jenjang_id`) REFERENCES `jenjang` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Data pendaftar calon santri baru';

DROP TABLE IF EXISTS `psb_custom_fields`;
CREATE TABLE `psb_custom_fields` (
  `id` VARCHAR(50) PRIMARY KEY,
  `type` VARCHAR(50) NOT NULL,
  `label` VARCHAR(255) NOT NULL,
  `required` TINYINT(1) NOT NULL DEFAULT 0,
  `options` JSON NULL,
  `sort_order` INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Form builder kustomisasi formulir online PSB';

DROP TABLE IF EXISTS `psb_archive_records`;
CREATE TABLE `psb_archive_records` (
  `id` VARCHAR(64) PRIMARY KEY,
  `tahun_ajaran` VARCHAR(50) NOT NULL,
  `gelombang` INT NOT NULL,
  `nama_arsip` VARCHAR(200) NOT NULL,
  `data_arsip` LONGTEXT NOT NULL COMMENT 'JSON snapshot pendaftar terarsip',
  `archived_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Arsip riwayat seleksi PSB tahun lampau';

-- =====================================================================
-- 7. MODUL KBM, JADWAL, ABSENSI, JURNAL & RAPOR
-- =====================================================================

DROP TABLE IF EXISTS `jadwal_pelajaran`;
CREATE TABLE `jadwal_pelajaran` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `rombel_id` BIGINT UNSIGNED NOT NULL,
  `mapel_id` BIGINT UNSIGNED NULL,
  `guru_id` BIGINT UNSIGNED NULL,
  `hari` INT NOT NULL COMMENT '0=Ahad, 1=Senin, ..., 6=Sabtu',
  `jam_ke` INT NOT NULL,
  `jam_mulai` VARCHAR(10) NULL,
  `jam_selesai` VARCHAR(10) NULL,
  `keterangan` VARCHAR(150) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_jadwal_rombel_hari` (`rombel_id`, `hari`, `jam_ke`),
  CONSTRAINT `fk_jadwal_rombel` FOREIGN KEY (`rombel_id`) REFERENCES `rombel` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_jadwal_mapel` FOREIGN KEY (`mapel_id`) REFERENCES `mata_pelajaran` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_jadwal_guru` FOREIGN KEY (`guru_id`) REFERENCES `tenaga_pengajar` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jadwal pelajaran mingguan per rombel';

DROP TABLE IF EXISTS `arsip_jadwal`;
CREATE TABLE `arsip_jadwal` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tahun_ajaran` VARCHAR(50) NOT NULL,
  `semester` ENUM('Ganjil', 'Genap') NOT NULL,
  `jenjang_id` BIGINT UNSIGNED NOT NULL,
  `data_jadwal` LONGTEXT NOT NULL COMMENT 'JSON snapshot master jadwal',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Arsip jadwal KBM semester lampau';

DROP TABLE IF EXISTS `jadwal_ujian`;
CREATE TABLE `jadwal_ujian` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `jenjang_id` BIGINT UNSIGNED NOT NULL,
  `rombel_id` BIGINT UNSIGNED NULL,
  `mapel_id` BIGINT UNSIGNED NOT NULL,
  `jenis_ujian` VARCHAR(50) NOT NULL,
  `tanggal` DATE NOT NULL,
  `hari` VARCHAR(20) NOT NULL,
  `sesi_ke` INT NOT NULL,
  `jam_mulai` VARCHAR(10) NOT NULL,
  `jam_selesai` VARCHAR(10) NOT NULL,
  `ruangan` VARCHAR(50) NULL,
  `pengawas_id` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_ujian_tgl` (`tanggal`, `jenjang_id`),
  CONSTRAINT `fk_ujian_jenjang` FOREIGN KEY (`jenjang_id`) REFERENCES `jenjang` (`id`),
  CONSTRAINT `fk_ujian_mapel` FOREIGN KEY (`mapel_id`) REFERENCES `mata_pelajaran` (`id`),
  CONSTRAINT `fk_ujian_pengawas` FOREIGN KEY (`pengawas_id`) REFERENCES `tenaga_pengajar` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jadwal pelaksanaan ujian semester dan tes';

DROP TABLE IF EXISTS `absensi_records`;
CREATE TABLE `absensi_records` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `rombel_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `sesi` VARCHAR(50) NOT NULL DEFAULT 'Harian',
  `status` ENUM('Hadir', 'Sakit', 'Izin', 'Alpa', 'Dispensasi') NOT NULL DEFAULT 'Hadir',
  `keterangan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_santri_sesi_tgl` (`santri_id`, `tanggal`, `sesi`),
  INDEX `idx_absensi_rombel_tgl` (`rombel_id`, `tanggal`),
  CONSTRAINT `fk_absensi_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_absensi_rombel` FOREIGN KEY (`rombel_id`) REFERENCES `rombel` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Presensi kehadiran santri (KBM, sholat, asrama)';

DROP TABLE IF EXISTS `jurnal_mengajar`;
CREATE TABLE `jurnal_mengajar` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `rombel_id` BIGINT UNSIGNED NOT NULL,
  `mapel_id` BIGINT UNSIGNED NOT NULL,
  `guru_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `jam_ke` VARCHAR(20) NOT NULL,
  `materi` TEXT NOT NULL,
  `capaian_kbm` TEXT NULL,
  `santri_absen_ids` JSON NULL,
  `catatan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_jurnal_guru_tgl` (`guru_id`, `tanggal`),
  INDEX `idx_jurnal_rombel_tgl` (`rombel_id`, `tanggal`),
  CONSTRAINT `fk_jurnal_rombel` FOREIGN KEY (`rombel_id`) REFERENCES `rombel` (`id`),
  CONSTRAINT `fk_jurnal_mapel` FOREIGN KEY (`mapel_id`) REFERENCES `mata_pelajaran` (`id`),
  CONSTRAINT `fk_jurnal_guru` FOREIGN KEY (`guru_id`) REFERENCES `tenaga_pengajar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Agenda jurnal mengajar harian guru di kelas';

DROP TABLE IF EXISTS `rapor_records`;
CREATE TABLE `rapor_records` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `tahun_ajaran` VARCHAR(50) NOT NULL,
  `semester` ENUM('Ganjil', 'Genap') NOT NULL,
  `rombel_id` BIGINT UNSIGNED NOT NULL,
  `peringkat` INT NULL,
  `catatan_wali_kelas` TEXT NULL,
  `sikap_spiritual` VARCHAR(50) NULL,
  `sikap_sosial` VARCHAR(50) NULL,
  `izin` INT NOT NULL DEFAULT 0,
  `sakit` INT NOT NULL DEFAULT 0,
  `alpa` INT NOT NULL DEFAULT 0,
  `tanggal_cetak` DATE NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uk_rapor_santri_periode` (`santri_id`, `tahun_ajaran`, `semester`),
  INDEX `idx_rapor_rombel` (`rombel_id`, `tahun_ajaran`, `semester`),
  CONSTRAINT `fk_rapor_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rapor_rombel` FOREIGN KEY (`rombel_id`) REFERENCES `rombel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rapor semester santri';

DROP TABLE IF EXISTS `rapor_nilai_mapel`;
CREATE TABLE `rapor_nilai_mapel` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `rapor_id` BIGINT UNSIGNED NOT NULL,
  `mapel_id` BIGINT UNSIGNED NOT NULL,
  `nilai_tugas` DECIMAL(5,2) NULL,
  `nilai_uts` DECIMAL(5,2) NULL,
  `nilai_uas` DECIMAL(5,2) NULL,
  `nilai_akhir` DECIMAL(5,2) NOT NULL,
  `predikat` VARCHAR(5) NULL,
  `deskripsi_kemajuan` TEXT NULL,
  INDEX `idx_nilai_rapor` (`rapor_id`),
  CONSTRAINT `fk_nilai_rapor` FOREIGN KEY (`rapor_id`) REFERENCES `rapor_records` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_nilai_mapel` FOREIGN KEY (`mapel_id`) REFERENCES `mata_pelajaran` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Nilai rapor per mata pelajaran';

-- =====================================================================
-- 8. MODUL KEUANGAN, BILLING, TABUNGAN & KAS
-- =====================================================================

DROP TABLE IF EXISTS `biaya`;
CREATE TABLE `biaya` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(150) NOT NULL,
  `jenis` ENUM('Bulanan', 'Cicilan', 'Sekali Bayar', 'Bebas') NOT NULL,
  `nominal` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `jenjang_id` BIGINT UNSIGNED NULL,
  `jumlah_cicilan` INT NULL DEFAULT 1,
  `nominal_cicilan` DECIMAL(15,2) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_biaya_jenjang` (`jenjang_id`),
  CONSTRAINT `fk_biaya_jenjang` FOREIGN KEY (`jenjang_id`) REFERENCES `jenjang` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Komponen tarif master biaya pendidikan';

DROP TABLE IF EXISTS `tagihan`;
CREATE TABLE `tagihan` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `biaya_id` BIGINT UNSIGNED NOT NULL,
  `tahun` INT NOT NULL,
  `bulan` INT NOT NULL DEFAULT 0 COMMENT '1..12, 0 jika sekali bayar/bebas',
  `nominal` DECIMAL(15,2) NOT NULL,
  `terbayar` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `sisa` DECIMAL(15,2) NOT NULL,
  `status` ENUM('Belum Lunas', 'Sebagian', 'Lunas') NOT NULL DEFAULT 'Belum Lunas',
  `jatuh_tempo` DATE NULL,
  `keterangan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_tagihan_santri` (`santri_id`, `status`),
  INDEX `idx_tagihan_biaya_periode` (`biaya_id`, `tahun`, `bulan`),
  CONSTRAINT `fk_tagihan_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_tagihan_biaya` FOREIGN KEY (`biaya_id`) REFERENCES `biaya` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Invoice tagihan pendidikan santri per periode';

DROP TABLE IF EXISTS `pembayaran`;
CREATE TABLE `pembayaran` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nomor_kwitansi` VARCHAR(50) NOT NULL UNIQUE,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `total_bayar` DECIMAL(15,2) NOT NULL,
  `metode_bayar` ENUM('Tunai', 'Transfer', 'Potong Saldo') NOT NULL DEFAULT 'Tunai',
  `rekening_tujuan` VARCHAR(100) NULL,
  `disetor_ke_kas` TINYINT(1) NOT NULL DEFAULT 0,
  `kas_transaksi_id` BIGINT UNSIGNED NULL,
  `petugas` VARCHAR(150) NOT NULL,
  `catatan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_bayar_santri` (`santri_id`),
  INDEX `idx_bayar_tgl` (`tanggal`),
  CONSTRAINT `fk_bayar_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Kwitansi pembayaran tagihan santri';

DROP TABLE IF EXISTS `pembayaran_item`;
CREATE TABLE `pembayaran_item` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `pembayaran_id` BIGINT UNSIGNED NOT NULL,
  `tagihan_id` BIGINT UNSIGNED NOT NULL,
  `jumlah_bayar` DECIMAL(15,2) NOT NULL,
  INDEX `idx_item_pembayaran` (`pembayaran_id`),
  INDEX `idx_item_tagihan` (`tagihan_id`),
  CONSTRAINT `fk_item_pembayaran` FOREIGN KEY (`pembayaran_id`) REFERENCES `pembayaran` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_item_tagihan` FOREIGN KEY (`tagihan_id`) REFERENCES `tagihan` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rincian alokasi kwitansi per invoice tagihan';

DROP TABLE IF EXISTS `saldo_santri`;
CREATE TABLE `saldo_santri` (
  `santri_id` BIGINT UNSIGNED PRIMARY KEY,
  `saldo` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `limit_harian` DECIMAL(15,2) NOT NULL DEFAULT 0.00 COMMENT 'Batas belanja harian santri di kantin/koperasi',
  `last_updated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_saldo_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Buku rekening tabungan/uang saku santri';

DROP TABLE IF EXISTS `transaksi_saldo`;
CREATE TABLE `transaksi_saldo` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `tipe` ENUM('Topup', 'Tarik', 'Belanja', 'Bayar Tagihan', 'Koreksi') NOT NULL,
  `jumlah` DECIMAL(15,2) NOT NULL,
  `saldo_sebelum` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `saldo_setelah` DECIMAL(15,2) NOT NULL,
  `deskripsi` VARCHAR(255) NOT NULL,
  `petugas` VARCHAR(150) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_trans_saldo_santri` (`santri_id`, `tanggal`),
  CONSTRAINT `fk_trans_saldo_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Mutasi debet kredit rekening tabungan santri';

DROP TABLE IF EXISTS `chart_of_accounts`;
CREATE TABLE `chart_of_accounts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kode` VARCHAR(50) NOT NULL UNIQUE,
  `nama` VARCHAR(150) NOT NULL,
  `kategori` ENUM('Pendapatan', 'Beban', 'Aset', 'Kewajiban', 'Modal') NOT NULL,
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_coa_kategori` (`kategori`, `deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Bagan akun standar akuntansi pesantren (COA)';

DROP TABLE IF EXISTS `transaksi_kas`;
CREATE TABLE `transaksi_kas` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tanggal` DATE NOT NULL,
  `coa_id` BIGINT UNSIGNED NULL,
  `jenis` ENUM('Masuk', 'Keluar', 'Mutasi') NOT NULL,
  `kategori` VARCHAR(100) NOT NULL,
  `jumlah` DECIMAL(15,2) NOT NULL,
  `saldo_setelah` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `rekening_asal` VARCHAR(100) NULL,
  `rekening_tujuan` VARCHAR(100) NULL,
  `pj` VARCHAR(150) NOT NULL,
  `keterangan` TEXT NOT NULL,
  `bukti_transaksi_url` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_kas_tgl_jenis` (`tanggal`, `jenis`),
  INDEX `idx_kas_coa` (`coa_id`),
  CONSTRAINT `fk_kas_coa` FOREIGN KEY (`coa_id`) REFERENCES `chart_of_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Buku kas umum arus kas masuk dan keluar pondok';

DROP TABLE IF EXISTS `payroll_records`;
CREATE TABLE `payroll_records` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `guru_id` BIGINT UNSIGNED NOT NULL,
  `bulan` INT NOT NULL,
  `tahun` INT NOT NULL,
  `gaji_pokok` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `tunjangan_jabatan` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `honor_jam` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `jumlah_jam` INT NOT NULL DEFAULT 0,
  `tunjangan_lain` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `potongan_lain` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `total_gaji` DECIMAL(15,2) NOT NULL,
  `tanggal_bayar` DATE NOT NULL,
  `status_bayar` ENUM('Draft', 'Dibayar') NOT NULL DEFAULT 'Dibayar',
  `catatan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_payroll_guru_periode` (`guru_id`, `tahun`, `bulan`),
  CONSTRAINT `fk_payroll_guru` FOREIGN KEY (`guru_id`) REFERENCES `tenaga_pengajar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Slip dan histori penggajian asatidz / staf';

-- =====================================================================
-- 9. MODUL KOPERASI, POINT OF SALE & INVENTARIS TOKO
-- =====================================================================

DROP TABLE IF EXISTS `warehouses`;
CREATE TABLE `warehouses` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(100) NOT NULL,
  `kode` VARCHAR(50) NOT NULL UNIQUE,
  `lokasi` VARCHAR(200) NULL,
  `is_default` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Gudang dan cabang toko koperasi pondok';

DROP TABLE IF EXISTS `suppliers`;
CREATE TABLE `suppliers` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(150) NOT NULL,
  `kontak` VARCHAR(100) NULL,
  `telepon` VARCHAR(30) NULL,
  `alamat` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pemasok barang dagangan koperasi';

DROP TABLE IF EXISTS `produk_koperasi`;
CREATE TABLE `produk_koperasi` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `barcode` VARCHAR(100) NULL UNIQUE,
  `nama` VARCHAR(200) NOT NULL,
  `kategori` VARCHAR(100) NOT NULL,
  `harga_beli` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `harga_jual` DECIMAL(15,2) NOT NULL,
  `stok` INT NOT NULL DEFAULT 0,
  `min_stok` INT NOT NULL DEFAULT 5,
  `satuan` VARCHAR(50) NOT NULL DEFAULT 'Pcs',
  `supplier_id` BIGINT UNSIGNED NULL,
  `warehouse_id` BIGINT UNSIGNED NULL,
  `foto_url` VARCHAR(500) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_produk_kategori` (`kategori`),
  CONSTRAINT `fk_produk_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_produk_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Katalog barang dagangan toko koperasi/kantin';

DROP TABLE IF EXISTS `transaksi_koperasi`;
CREATE TABLE `transaksi_koperasi` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nomor_transaksi` VARCHAR(50) NOT NULL UNIQUE,
  `tanggal` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `tipe_pembeli` ENUM('Santri', 'Guru/Staf', 'Umum') NOT NULL DEFAULT 'Umum',
  `pembeli_id` BIGINT UNSIGNED NULL,
  `nama_pembeli` VARCHAR(150) NULL,
  `subtotal` DECIMAL(15,2) NOT NULL,
  `diskon` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `total` DECIMAL(15,2) NOT NULL,
  `bayar` DECIMAL(15,2) NOT NULL,
  `kembalian` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `metode_bayar` ENUM('Tunai', 'Potong Saldo', 'Hutang', 'Transfer', 'QRIS') NOT NULL DEFAULT 'Tunai',
  `status_transaksi` ENUM('Selesai', 'Hutang', 'Batal') NOT NULL DEFAULT 'Selesai',
  `kasir` VARCHAR(150) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_trans_koperasi_tgl` (`tanggal`),
  INDEX `idx_trans_koperasi_status` (`status_transaksi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Transaksi kasir penjualan koperasi POS';

DROP TABLE IF EXISTS `transaksi_koperasi_items`;
CREATE TABLE `transaksi_koperasi_items` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaksi_id` BIGINT UNSIGNED NOT NULL,
  `produk_id` BIGINT UNSIGNED NOT NULL,
  `nama_produk` VARCHAR(200) NOT NULL,
  `harga_satuan` DECIMAL(15,2) NOT NULL,
  `qty` INT NOT NULL,
  `diskon_item` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `subtotal` DECIMAL(15,2) NOT NULL,
  INDEX `idx_item_transaksi` (`transaksi_id`),
  CONSTRAINT `fk_item_transaksi` FOREIGN KEY (`transaksi_id`) REFERENCES `transaksi_koperasi` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_item_produk` FOREIGN KEY (`produk_id`) REFERENCES `produk_koperasi` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Item barang terjual per transaksi kasir';

DROP TABLE IF EXISTS `riwayat_stok`;
CREATE TABLE `riwayat_stok` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `produk_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `tipe` ENUM('Masuk', 'Keluar', 'Penyesuaian', 'Transfer') NOT NULL,
  `jumlah` INT NOT NULL,
  `stok_sebelum` INT NOT NULL,
  `stok_setelah` INT NOT NULL,
  `keterangan` TEXT NULL,
  `petugas` VARCHAR(150) NOT NULL,
  INDEX `idx_stok_produk_tgl` (`produk_id`, `tanggal`),
  CONSTRAINT `fk_stok_produk` FOREIGN KEY (`produk_id`) REFERENCES `produk_koperasi` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Log kartu stok inventaris produk koperasi';

DROP TABLE IF EXISTS `pembayaran_hutang_koperasi`;
CREATE TABLE `pembayaran_hutang_koperasi` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaksi_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `jumlah_bayar` DECIMAL(15,2) NOT NULL,
  `sisa_hutang` DECIMAL(15,2) NOT NULL,
  `petugas` VARCHAR(150) NOT NULL,
  `catatan` TEXT NULL,
  INDEX `idx_hutang_transaksi` (`transaksi_id`),
  CONSTRAINT `fk_hutang_transaksi` FOREIGN KEY (`transaksi_id`) REFERENCES `transaksi_koperasi` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Pelunasan piutang belanja santri/staf';

-- =====================================================================
-- 10. MODUL KESEHATAN (POSKESTREN) & BIMBINGAN KONSELING (BK)
-- =====================================================================

DROP TABLE IF EXISTS `obat`;
CREATE TABLE `obat` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(150) NOT NULL,
  `jenis` VARCHAR(100) NOT NULL,
  `satuan` VARCHAR(50) NOT NULL DEFAULT 'Strip',
  `stok` INT NOT NULL DEFAULT 0,
  `min_stok` INT NOT NULL DEFAULT 5,
  `indikasi` TEXT NULL,
  `kadaluarsa` DATE NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stok obat-obatan dan inventaris medis poskestren';

DROP TABLE IF EXISTS `kesehatan_records`;
CREATE TABLE `kesehatan_records` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `keluhan` TEXT NOT NULL,
  `diagnosa` TEXT NOT NULL,
  `suhu_tubuh` DECIMAL(4,1) NULL,
  `tensi` VARCHAR(20) NULL,
  `status` ENUM('Rawat Jalan', 'Istirahat di Kamar', 'Rawat di Poskestren', 'Rujuk RS') NOT NULL DEFAULT 'Rawat Jalan',
  `tindakan` TEXT NULL,
  `petugas` VARCHAR(150) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_kesehatan_santri_tgl` (`santri_id`, `tanggal`),
  CONSTRAINT `fk_kesehatan_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rekam medis dan pemeriksaan santri sakit';

DROP TABLE IF EXISTS `resep_obat_item`;
CREATE TABLE `resep_obat_item` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kesehatan_id` BIGINT UNSIGNED NOT NULL,
  `obat_id` BIGINT UNSIGNED NOT NULL,
  `jumlah` INT NOT NULL,
  `aturan_pakai` VARCHAR(100) NOT NULL,
  INDEX `idx_resep_kesehatan` (`kesehatan_id`),
  CONSTRAINT `fk_resep_kesehatan` FOREIGN KEY (`kesehatan_id`) REFERENCES `kesehatan_records` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_resep_obat` FOREIGN KEY (`obat_id`) REFERENCES `obat` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Rincian obat yang diberikan pada rekam medis';

DROP TABLE IF EXISTS `bk_sessions`;
CREATE TABLE `bk_sessions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `tanggal` DATE NOT NULL,
  `kategori` ENUM('Pribadi', 'Sosial', 'Belajar', 'Keluarga', 'Kedisiplinan') NOT NULL DEFAULT 'Pribadi',
  `masalah` TEXT NOT NULL,
  `penyelesaian` TEXT NOT NULL,
  `komitmen_santri` TEXT NULL,
  `konselor` VARCHAR(150) NOT NULL,
  `status` ENUM('Baru', 'Dalam Proses', 'Selesai', 'Rujukan') NOT NULL DEFAULT 'Baru',
  `privasi` ENUM('Biasa', 'Rahasia') NOT NULL DEFAULT 'Rahasia',
  `tanggal_berikutnya` DATE NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_bk_santri_tgl` (`santri_id`, `tanggal`),
  CONSTRAINT `fk_bk_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Sesi konseling bimbingan dan penyuluhan santri';

-- =====================================================================
-- 11. MODUL PERPUSTAKAAN & INVENTARIS ASET (SARPRAS)
-- =====================================================================

DROP TABLE IF EXISTS `buku`;
CREATE TABLE `buku` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kode_buku` VARCHAR(100) NOT NULL UNIQUE,
  `isbn` VARCHAR(50) NULL,
  `judul` VARCHAR(255) NOT NULL,
  `pengarang` VARCHAR(150) NOT NULL,
  `penerbit` VARCHAR(150) NULL,
  `tahun_terbit` INT NULL,
  `kategori` VARCHAR(100) NOT NULL,
  `lokasi_rak` VARCHAR(100) NULL,
  `jumlah_total` INT NOT NULL DEFAULT 1,
  `jumlah_tersedia` INT NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_buku_kategori` (`kategori`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Katalog buku perpustakaan pesantren';

DROP TABLE IF EXISTS `sirkulasi_perpustakaan`;
CREATE TABLE `sirkulasi_perpustakaan` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `buku_id` BIGINT UNSIGNED NOT NULL,
  `santri_id` BIGINT UNSIGNED NOT NULL,
  `tanggal_pinjam` DATE NOT NULL,
  `tanggal_jatuh_tempo` DATE NOT NULL,
  `tanggal_kembali` DATE NULL,
  `denda` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `status` ENUM('Dipinjam', 'Kembali', 'Hilang', 'Terlambat') NOT NULL DEFAULT 'Dipinjam',
  `petugas` VARCHAR(150) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_sirkulasi_status` (`status`),
  INDEX `idx_sirkulasi_santri` (`santri_id`),
  CONSTRAINT `fk_sirkulasi_buku` FOREIGN KEY (`buku_id`) REFERENCES `buku` (`id`),
  CONSTRAINT `fk_sirkulasi_santri` FOREIGN KEY (`santri_id`) REFERENCES `santri` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Sirkulasi peminjaman dan pengembalian buku';

DROP TABLE IF EXISTS `inventaris_sarpras`;
CREATE TABLE `inventaris_sarpras` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kode` VARCHAR(100) NOT NULL UNIQUE,
  `nama` VARCHAR(200) NOT NULL,
  `jenis` ENUM('Barang Bergerak', 'Tanah', 'Bangunan', 'Kendaraan', 'Elektronik') NOT NULL,
  `kategori` VARCHAR(100) NOT NULL,
  `lokasi` VARCHAR(150) NOT NULL,
  `jumlah` INT NOT NULL DEFAULT 1,
  `satuan` VARCHAR(50) NOT NULL DEFAULT 'Unit',
  `kondisi` ENUM('Baik', 'Rusak Ringan', 'Rusak Berat') NOT NULL DEFAULT 'Baik',
  `asal_perolehan` ENUM('Beli', 'Wakaf', 'Hibah', 'Bantuan') NOT NULL DEFAULT 'Beli',
  `nilai_perolehan` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  `tanggal_perolehan` DATE NULL,
  `penanggung_jawab` VARCHAR(150) NULL,
  `catatan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_sarpras_jenis` (`jenis`),
  INDEX `idx_sarpras_lokasi` (`lokasi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Aset fisik inventaris sarana prasarana pondok';

-- =====================================================================
-- 12. MODUL PERSURATAN, BUKU TAMU, KALENDER & PIKET
-- =====================================================================

DROP TABLE IF EXISTS `surat_templates`;
CREATE TABLE `surat_templates` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama` VARCHAR(150) NOT NULL,
  `kategori` VARCHAR(50) NOT NULL DEFAULT 'Resmi',
  `format_nomor` VARCHAR(150) NOT NULL,
  `judul_surat` VARCHAR(200) NOT NULL,
  `isi_surat` LONGTEXT NOT NULL,
  `mengetahui_config` JSON NULL,
  `tempat_tanggal_config` JSON NULL,
  `margin_config` JSON NULL,
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Template baku cetak persuratan resmi pondok';

DROP TABLE IF EXISTS `arsip_surat`;
CREATE TABLE `arsip_surat` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nomor_surat` VARCHAR(150) NOT NULL UNIQUE,
  `tujuan` VARCHAR(200) NOT NULL,
  `perihal` VARCHAR(200) NOT NULL,
  `kategori` VARCHAR(50) NOT NULL,
  `tanggal_surat` DATE NOT NULL,
  `template_id` BIGINT UNSIGNED NULL,
  `isi_surat` LONGTEXT NOT NULL,
  `file_url` VARCHAR(500) NULL,
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_arsip_surat_tgl` (`tanggal_surat`),
  CONSTRAINT `fk_arsip_surat_template` FOREIGN KEY (`template_id`) REFERENCES `surat_templates` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Arsip surat keluar yang telah diterbitkan';

DROP TABLE IF EXISTS `buku_tamu`;
CREATE TABLE `buku_tamu` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tanggal` DATE NOT NULL,
  `waktu_masuk` VARCHAR(10) NOT NULL,
  `waktu_keluar` VARCHAR(10) NULL,
  `nama_tamu` VARCHAR(150) NOT NULL,
  `instansi` VARCHAR(150) NULL,
  `telepon` VARCHAR(30) NULL,
  `keperluan` TEXT NOT NULL,
  `tujuan_bertemu` VARCHAR(150) NOT NULL,
  `santri_dikunjungi_id` BIGINT UNSIGNED NULL,
  `status` ENUM('Berkunjung', 'Selesai') NOT NULL DEFAULT 'Berkunjung',
  `penerima_tamu` VARCHAR(150) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_tamu_tanggal` (`tanggal`),
  CONSTRAINT `fk_tamu_santri` FOREIGN KEY (`santri_dikunjungi_id`) REFERENCES `santri` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Buku registrasi tamu dan kunjungan wali santri';

DROP TABLE IF EXISTS `calendar_events`;
CREATE TABLE `calendar_events` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200) NOT NULL,
  `description` TEXT NULL,
  `start_date` DATETIME NOT NULL,
  `end_date` DATETIME NOT NULL,
  `all_day` TINYINT(1) NOT NULL DEFAULT 1,
  `category` VARCHAR(50) NOT NULL DEFAULT 'Akademik',
  `color` VARCHAR(30) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_calendar_dates` (`start_date`, `end_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Agenda kalender pendidikan dan kegiatan pondok';

DROP TABLE IF EXISTS `piket_schedules`;
CREATE TABLE `piket_schedules` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `tanggal` DATE NOT NULL,
  `sholat` VARCHAR(50) NOT NULL,
  `imam_id` BIGINT UNSIGNED NULL,
  `muadzin_santri_id` BIGINT UNSIGNED NULL,
  `piket_santri_ids` JSON NULL,
  `catatan` TEXT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_piket_tgl` (`tanggal`),
  CONSTRAINT `fk_piket_imam` FOREIGN KEY (`imam_id`) REFERENCES `tenaga_pengajar` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_piket_muadzin` FOREIGN KEY (`muadzin_santri_id`) REFERENCES `santri` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Jadwal piket ibadah, imam, dan muadzin santri';

-- =====================================================================
-- PRELOAD & INITIAL SEED DATA
-- =====================================================================

-- 1. Pengguna Utama (Admin)
INSERT INTO `users` (`id`, `username`, `password_hash`, `full_name`, `email`, `phone`, `role`, `is_default_admin`, `permission_version`)
VALUES (1, 'admin', '$2b$10$EpRnTzVlqHNP0.fUbXUwSOyvrQQx5FhV5jA5Qd5o8p2F00u8gV1yG', 'Administrator Inti', 'admin@ppalikhlas.com', '08123456789', 'admin', 1, 1);

-- 2. Hak Akses Admin (Full Access untuk seluruh modul)
INSERT INTO `user_permissions` (`user_id`, `feature_name`, `access_level`) VALUES
(1, 'santri', 'write'),
(1, 'psb', 'write'),
(1, 'whatsapp', 'write'),
(1, 'akademik', 'write'),
(1, 'absensi', 'write'),
(1, 'tahfizh', 'write'),
(1, 'keuangan', 'write'),
(1, 'bukukas', 'write'),
(1, 'surat', 'write'),
(1, 'laporan', 'write'),
(1, 'auditlog', 'write'),
(1, 'pengaturan', 'write'),
(1, 'keasramaan', 'write'),
(1, 'kesehatan', 'write'),
(1, 'bk', 'write'),
(1, 'bukutamu', 'write'),
(1, 'kalender', 'write'),
(1, 'perpustakaan', 'write'),
(1, 'datamaster', 'write'),
(1, 'koperasi', 'write'),
(1, 'sarpras', 'write'),
(1, 'syncAdmin', 'write');

-- 3. Profil Pesantren (Pondok Settings)
INSERT INTO `pondok_settings` (
  `id`, `nama_yayasan`, `nama_ponpes`, `nspp`, `npsn`, `alamat`, `telepon`, `email`, `website`,
  `logo_yayasan_url`, `logo_ponpes_url`, `multi_user_mode`, `surat_tagihan_pembuka`, `surat_tagihan_penutup`,
  `surat_tagihan_catatan`, `pesan_wa_tunggakan`, `hijri_adjustment`
) VALUES (
  1,
  'Yayasan Pondok Pesantren Al-Ikhlas',
  'Pondok Pesantren Al-Ikhlas',
  '510033020001',
  '69900001',
  'Jl. Pesantren No. 1, Desa Sukamaju, Kec. Karangjaya, Kab. Berkah',
  '08123456789',
  'info@ppalikhlas.com',
  'https://ppalikhlas.com',
  NULL,
  NULL,
  0,
  'Assalamu''alaikum Wr. Wb.\n\nDengan ini kami sampaikan tagihan biaya pendidikan santri sebagai berikut:',
  'Demikian pemberitahuan ini kami sampaikan. Atas perhatian dan kerjasamanya kami ucapkan terima kasih.\n\nWassalamu''alaikum Wr. Wb.',
  'Mohon dibayarkan sebelum tanggal 10 bulan berjalan.',
  'Assalamu''alaikum Wr. Wb.\n\nYth. Wali Santri {NAMA_SANTRI},\n\nKami informasikan bahwa terdapat tunggakan administrasi sebesar *{JUMLAH_TUNGGAKAN}*.\nMohon segera melakukan pembayaran.\n\nTerima kasih.\n{NAMA_PONPES}',
  0
);

-- 4. Tahun Ajaran
INSERT INTO `academic_years` (`id`, `label_masehi`, `masehi_start_month`, `masehi_start_year`, `masehi_end_month`, `masehi_end_year`, `hijri_enabled`, `is_active`) VALUES
('ta-2024-2025', '2024/2025', 7, 2024, 6, 2025, 0, 0),
('ta-2025-2026', '2025/2026', 7, 2025, 6, 2026, 0, 0),
('ta-2026-2027', '2026/2027', 7, 2026, 6, 2027, 0, 1);

-- 5. Jenjang Pendidikan
INSERT INTO `jenjang` (`id`, `nama`, `kode`, `hari_libur`) VALUES
(1, 'Salafiyah Wustho', 'SW', '[5]'),
(2, 'Salafiyah Ulya', 'SU', '[5]');

-- 6. Kelas
INSERT INTO `kelas` (`id`, `jenjang_id`, `nama`) VALUES
(1, 1, 'Kelas 1'),
(2, 1, 'Kelas 2'),
(3, 1, 'Kelas 3'),
(4, 2, 'Kelas 1'),
(5, 2, 'Kelas 2'),
(6, 2, 'Kelas 3');

-- 7. Tenaga Pengajar & Asatidz
INSERT INTO `tenaga_pengajar` (`id`, `nama`, `nip`, `gender`, `config_gaji`, `hari_masuk`, `kompetensi_mapel_ids`, `status_keaktifan`) VALUES
(1, 'Kyai Abdullah', '19700101001', 'Laki-laki', '{"gajiPokok": 2000000, "tunjanganJabatan": 500000, "honorPerJam": 0, "tunjanganLain": 0, "potonganLain": 0}', '[0, 1, 2, 3, 4, 5, 6]', '[3]', 'Aktif'),
(2, 'Ust. Hasan', '19850215002', 'Laki-laki', '{"gajiPokok": 1500000, "tunjanganJabatan": 300000, "honorPerJam": 20000, "tunjanganLain": 0, "potonganLain": 0}', '[1, 2, 3, 4, 6]', '[1, 2]', 'Aktif'),
(3, 'Ustdz. Fatimah', '19900320003', 'Perempuan', '{"gajiPokok": 1200000, "tunjanganJabatan": 0, "honorPerJam": 25000, "tunjanganLain": 100000, "potonganLain": 0}', '[1, 2, 3, 4, 6]', '[1, 3]', 'Aktif'),
(4, 'Ust. Zulkifli', '19920510004', 'Laki-laki', '{"gajiPokok": 1300000, "tunjanganJabatan": 100000, "honorPerJam": 20000, "tunjanganLain": 0, "potonganLain": 0}', '[0, 1, 2, 3, 4, 6]', '[2]', 'Aktif');

-- 8. Riwayat Jabatan
INSERT INTO `riwayat_jabatan_pengajar` (`id`, `pengajar_id`, `jabatan`, `tanggal_mulai`) VALUES
(1, 1, 'Pengasuh', '2010-01-01'),
(2, 2, 'Kepala Sekolah SW', '2020-01-01'),
(3, 3, 'Guru Pengajar', '2021-06-15'),
(4, 4, 'Guru & Musyrif', '2022-01-01');

-- 9. Rombel
INSERT INTO `rombel` (`id`, `kelas_id`, `nama`, `wali_kelas_id`) VALUES
(1, 1, '1A Putra', 2),
(2, 1, '1B Putri', 3),
(3, 2, '2A Putra', 4),
(4, 4, '1A Ulya', 1);

-- 10. Mata Pelajaran
INSERT INTO `mata_pelajaran` (`id`, `jenjang_id`, `nama`, `kode`, `kkm`, `kategori`) VALUES
(1, 1, 'Nahwu', 'NHW-1', 70.00, 'Diniyah'),
(2, 1, 'Shorof', 'SRF-1', 70.00, 'Diniyah'),
(3, 1, 'Fiqh', 'FQH-1', 75.00, 'Diniyah'),
(4, 1, 'Tauhid', 'THD-1', 75.00, 'Diniyah'),
(5, 1, 'Bahasa Arab', 'ARB-1', 70.00, 'Bahasa'),
(6, 2, 'Tafsir', 'TFS-2', 75.00, 'Diniyah'),
(7, 2, 'Hadits', 'HDT-2', 75.00, 'Diniyah'),
(8, 2, 'Ushul Fiqh', 'USF-2', 70.00, 'Diniyah');

-- 11. Master Komponen Biaya
INSERT INTO `biaya` (`id`, `nama`, `jenis`, `nominal`, `jenjang_id`, `jumlah_cicilan`, `nominal_cicilan`) VALUES
(1, 'SPP Bulanan', 'Bulanan', 150000.00, 1, 1, NULL),
(2, 'SPP Bulanan Ulya', 'Bulanan', 200000.00, 2, 1, NULL),
(3, 'Uang Makan', 'Bulanan', 450000.00, NULL, 1, NULL),
(4, 'Uang Gedung', 'Cicilan', 3000000.00, NULL, 6, 500000.00),
(5, 'Seragam', 'Sekali Bayar', 600000.00, NULL, 1, NULL),
(6, 'Kitab Awal Tahun', 'Sekali Bayar', 350000.00, NULL, 1, NULL);

-- 12. Gedung Asrama
INSERT INTO `gedung_asrama` (`id`, `nama`, `jenis`, `lokasi`) VALUES
(1, 'Asrama Putra Al-Fatih', 'Putra', 'Sayap Barat Komplek'),
(2, 'Asrama Putri Khadijah', 'Putri', 'Sayap Timur Komplek');

-- 13. Kamar Asrama
INSERT INTO `kamar` (`id`, `gedung_id`, `nama`, `kapasitas`, `lantai`, `musyrif_id`, `ketua_kamar_id`, `fasilitas`, `kondisi_fasilitas`) VALUES
(1, 1, 'Kamar 101 (Abu Bakar)', 10, 'Lantai 1', 4, 1001, '5 Ranjang Susun, 10 Lemari, 2 Kipas Angin', 'Baik'),
(2, 1, 'Kamar 102 (Umar bin Khattab)', 10, 'Lantai 1', 4, 1005, '5 Ranjang Susun, 10 Lemari, 2 Kipas Angin', 'Baik'),
(3, 2, 'Kamar 201 (Aisyah)', 12, 'Lantai 2', 3, 1003, '6 Ranjang Susun, 12 Lemari, 3 Kipas Angin', 'Baik'),
(4, 2, 'Kamar 202 (Fathimah)', 12, 'Lantai 2', 3, NULL, '6 Ranjang Susun, 12 Lemari, 2 Kipas Angin, 1 AC', 'Cukup');

-- 14. Inspeksi Kamar
INSERT INTO `inspeksi_kamar` (`id`, `kamar_id`, `tanggal`, `skor_kebersihan`, `skor_kerapian`, `skor_kedisiplinan`, `skor_total`, `predikat`, `musyrif_pemeriksa`, `catatan`) VALUES
(1, 1, '2026-09-01', 95, 90, 92, 92, 'Mumtaz', 'Ust. Zulkifli, S.Pd.I', 'Sangat rapi, kasur tertata kencang dan lantai harum.'),
(2, 2, '2026-09-01', 85, 82, 88, 85, 'Jayyid Jiddan', 'Ust. Zulkifli, S.Pd.I', 'Handuk dijemur di luar rapi, perlu pembersihan rak sepatu.'),
(3, 3, '2026-09-01', 98, 95, 96, 96, 'Mumtaz', 'Ustdz. Fatimah, S.Pd', 'Juara kerapian pekanan, loker santri bersih dan terkunci tertib.'),
(4, 4, '2026-09-01', 78, 75, 80, 78, 'Jayyid', 'Ustdz. Fatimah, S.Pd', 'Peralatan makan belum dicuci tuntas di wastafel kamar.');

-- 15. Jurnal Asrama
INSERT INTO `jurnal_asrama` (`id`, `gedung_id`, `kamar_id`, `tanggal`, `waktu`, `jenis_kegiatan`, `keterangan`, `tindakan`, `nama_musyrif`) VALUES
(1, 1, 1, '2026-09-03', '05:30', 'Kedisiplinan', 'Semua santri kamar 101 bangun tepat waktu dan sholat subuh di shaf pertama.', 'Diberikan apresiasi kebersihan dan disiplin oleh musyrif.', 'Ust. Zulkifli, S.Pd.I'),
(2, 1, 2, '2026-09-02', '16:45', 'Kebersihan', 'Kerja bakti pembersihan kipas angin dan ventilasi kamar bersama seluruh penghuni.', 'Pengecekan fasilitas selesai dengan baik.', 'Ust. Zulkifli, S.Pd.I'),
(3, 2, 3, '2026-09-02', '20:15', 'Pembinaan', 'Evaluasi adab tidur, doa bersama, dan pembagian piket kebersihan kamar oleh ketua kamar.', 'Musyrifah memberikan arahan 15 menit.', 'Ustdz. Fatimah, S.Pd');

-- 16. Kelompok Halaqah Tahfizh
INSERT INTO `kelompok_halaqah` (`id`, `nama`, `muhaffizh_id`, `target_juz`, `lokasi`, `keterangan`) VALUES
(1, 'Halaqah Ula (Ust. Hasan)', 2, 30, 'Masjid Utama', 'Fokus Juz 30 & Tahsin'),
(2, 'Halaqah Wustha (Ust. Zulkifli)', 4, 29, 'Serambi Timur', 'Fokus Juz 29 & 28'),
(3, 'Halaqah Banat (Ustdz. Fatimah)', 3, 30, 'Musholla Putri', 'Fokus Juz 30 & Ziyadah');

-- 17. Chart of Accounts (COA Akuntansi Pesantren)
INSERT INTO `chart_of_accounts` (`id`, `kode`, `nama`, `kategori`, `deleted`) VALUES
(100401, '401', 'Syahriah / SPP Santri', 'Pendapatan', 0),
(100402, '402', 'Infaq & Donasi Muhsinin', 'Pendapatan', 0),
(100403, '403', 'Wakaf & Hibah Pembangunan', 'Pendapatan', 0),
(100404, '404', 'Hasil Usaha Koperasi & Kantin', 'Pendapatan', 0),
(100405, '405', 'Bantuan Pemerintah / BOS', 'Pendapatan', 0),
(100501, '501', 'Konsumsi & Dapur Santri', 'Beban', 0),
(100502, '502', 'Listrik, Air & Internet', 'Beban', 0),
(100503, '503', 'Gaji & Bisyarah Asatidz', 'Beban', 0),
(100504, '504', 'Pemeliharaan Gedung & Asrama', 'Beban', 0),
(100505, '505', 'ATK & Kesekretariatan', 'Beban', 0),
(100506, '506', 'Kesehatan Santri (Poskestren)', 'Beban', 0),
(100507, '507', 'Kegiatan & Ekstrakurikuler Santri', 'Beban', 0);

-- 18. Data Santri
INSERT INTO `santri` (
  `id`, `nis`, `nisn`, `nik`, `nama_lengkap`, `nama_hijrah`, `jenis_kelamin`, `tempat_lahir`, `tanggal_lahir`,
  `kewarganegaraan`, `alamat_detail`, `desa_kelurahan`, `kecamatan`, `kabupaten_kota`, `provinsi`, `kode_pos`,
  `nama_ayah`, `nik_ayah`, `pekerjaan_ayah`, `pendidikan_ayah`, `penghasilan_ayah`, `telepon_ayah`,
  `nama_ibu`, `nik_ibu`, `pekerjaan_ibu`, `pendidikan_ibu`, `penghasilan_ibu`, `telepon_ibu`,
  `jenjang_id`, `kelas_id`, `rombel_id`, `kamar_id`, `halaqah_id`, `tanggal_masuk`, `status`, `tanggal_status`,
  `foto_url`, `jenis_santri`, `sekolah_asal`, `hobi`
) VALUES
(
  1001, '202401001', '0012345678', '3301010101010001', 'Ahmad Fauzan', 'Fauzan', 'Laki-laki', 'Banyumas', '2010-05-15',
  'WNI', 'Jl. Pemuda No. 10, RT 01 RW 02', 'Karangjaya', 'Karangjaya', 'Banyumas', 'Jawa Tengah', '53123',
  'Budi Santoso', '3301010101010002', 'Wiraswasta', 'SLTA/Sederajat', 'Rp. 2.000.000 - Rp. 5.000.000', '081234567001',
  'Siti Aminah', '3301010101010003', 'Mengurus Rumah Tangga', 'SLTP/Sederajat', 'Tidak Berpenghasilan', '081234567002',
  1, 1, 1, 1, 1, '2024-07-15', 'Aktif', NULL,
  'https://placehold.co/150x200/e2e8f0/334155?text=Foto', 'Mondok - Baru', NULL, '["Sepak Bola", "Membaca"]'
),
(
  1002, '202401002', '0012345679', '3301010101010004', 'Zainul Arifin', NULL, 'Laki-laki', 'Cilacap', '2010-08-20',
  'WNI', 'Dusun Manis RT 03 RW 01', 'Adipala', 'Adipala', 'Cilacap', 'Jawa Tengah', NULL,
  'Slamet Riyadi', NULL, 'Petani/Pekebun', NULL, NULL, '081234567003',
  'Khotimah', NULL, 'Pedagang', NULL, NULL, NULL,
  1, 1, 1, 1, 1, '2024-07-15', 'Aktif', NULL,
  'https://placehold.co/150x200/e2e8f0/334155?text=Foto', 'Mondok - Baru', NULL, NULL
),
(
  1003, '202401003', '0012345680', NULL, 'Aisyah Humaira', NULL, 'Perempuan', 'Kebumen', '2010-11-10',
  'WNI', 'Jl. Laut No. 45', 'Ayah', 'Ayah', 'Kebumen', 'Jawa Tengah', NULL,
  'H. Rahmat', NULL, 'PNS', NULL, NULL, '081234567004',
  'Hj. Zulaikha', NULL, 'Guru', NULL, NULL, NULL,
  1, 1, 2, 3, 3, '2024-07-15', 'Aktif', NULL,
  'https://placehold.co/150x200/e2e8f0/334155?text=Foto', 'Mondok - Baru', NULL, NULL
),
(
  1004, '202401004', '0012345681', NULL, 'Fatimatuz Zahra', NULL, 'Perempuan', 'Purbalingga', '2010-02-28',
  'WNI', 'Gang Delima No. 5', 'Kalimanah', 'Kalimanah', 'Purbalingga', 'Jawa Tengah', NULL,
  'Supriyanto', NULL, 'Karyawan Swasta', NULL, NULL, NULL,
  'Sri Wahyuni', NULL, NULL, NULL, NULL, NULL,
  1, 1, 2, 3, 3, '2024-07-15', 'Aktif', NULL,
  'https://placehold.co/150x200/e2e8f0/334155?text=Foto', 'Mondok - Baru', NULL, NULL
),
(
  1005, '202302001', NULL, NULL, 'Muhammad Yusuf', NULL, 'Laki-laki', 'Jakarta', '2008-01-12',
  'WNI', 'Komp. Pondok Indah Blok A', 'Pondok Pinang', 'Kebayoran Lama', 'Jakarta Selatan', 'DKI Jakarta', NULL,
  'Ir. Hendra', NULL, 'Wiraswasta', NULL, NULL, '081299998888',
  'Dr. Sarah', NULL, NULL, NULL, NULL, NULL,
  2, 4, 4, 2, 2, '2023-07-10', 'Aktif', NULL,
  'https://placehold.co/150x200/e2e8f0/334155?text=Foto', 'Mondok - Pindahan', 'SMPIT Darul Quran', NULL
),
(
  1006, '202401006', NULL, NULL, 'Budi Darmawan', NULL, 'Laki-laki', 'Banyumas', '2010-06-10',
  'WNI', 'Desa Karangmangu', 'Karangmangu', NULL, 'Banyumas', 'Jawa Tengah', NULL,
  'Darmawan', NULL, NULL, NULL, NULL, NULL,
  'Suminah', NULL, NULL, NULL, NULL, NULL,
  1, 1, 1, 1, 1, '2024-07-15', 'Hiatus', '2024-10-01',
  'https://placehold.co/150x200/e2e8f0/334155?text=Foto', 'Mondok - Baru', NULL, NULL
),
(
  1007, '202401007', NULL, NULL, 'Siti Nurhaliza', NULL, 'Perempuan', 'Banyumas', '2010-09-09',
  'WNI', 'Desa Rempoah', 'Rempoah', NULL, 'Banyumas', 'Jawa Tengah', NULL,
  'Nurhadi', NULL, NULL, NULL, NULL, NULL,
  'Halimah', NULL, NULL, NULL, NULL, NULL,
  1, 1, 2, NULL, 3, '2024-07-15', 'Keluar/Pindah', '2024-09-15',
  'https://placehold.co/150x200/e2e8f0/334155?text=Foto', 'Mondok - Baru', NULL, NULL
);

-- 19. Prestasi Santri
INSERT INTO `santri_prestasi` (`id`, `santri_id`, `nama`, `tingkat`, `jenis`, `tahun`, `penyelenggara`) VALUES
(1, 1003, 'Juara 1 MTQ Kecamatan', 'Kecamatan', 'Keagamaan', 2024, 'Kemenag');

-- 20. Riwayat Status Santri
INSERT INTO `santri_riwayat_status` (`santri_id`, `status`, `tanggal`, `keterangan`) VALUES
(1001, 'Masuk', '2024-07-15', 'Santri Baru'),
(1002, 'Masuk', '2024-07-15', 'Santri Baru'),
(1003, 'Masuk', '2024-07-15', 'Santri Baru'),
(1004, 'Masuk', '2024-07-15', 'Santri Baru'),
(1005, 'Masuk', '2023-07-10', 'Pindahan'),
(1006, 'Masuk', '2024-07-15', 'Santri Baru'),
(1006, 'Hiatus', '2024-10-01', 'Sakit Jangka Panjang'),
(1007, 'Masuk', '2024-07-15', 'Santri Baru'),
(1007, 'Keluar/Pindah', '2024-09-15', 'Pindah ikut orang tua');

-- 21. Saldo Rekening Santri Awal
INSERT INTO `saldo_santri` (`santri_id`, `saldo`, `limit_harian`) VALUES
(1001, 250000.00, 25000.00),
(1002, 100000.00, 20000.00),
(1003, 350000.00, 30000.00),
(1004, 150000.00, 20000.00),
(1005, 500000.00, 50000.00);

-- 22. Gudang & Supplier Koperasi
INSERT INTO `warehouses` (`id`, `nama`, `kode`, `lokasi`, `is_default`) VALUES
(1, 'Toko Utama Pondok', 'WH-01', 'Gedung Serbaguna Lt 1', 1);

INSERT INTO `suppliers` (`id`, `nama`, `kontak`, `telepon`, `alamat`) VALUES
(1, 'CV Berkah Bersama Distributor', 'Bpk. Hendra', '081234567888', 'Jl. Raya Industri No. 12, Purwokerto');

-- 23. Produk Koperasi Contoh
INSERT INTO `produk_koperasi` (`id`, `barcode`, `nama`, `kategori`, `harga_beli`, `harga_jual`, `stok`, `min_stok`, `satuan`, `supplier_id`, `warehouse_id`) VALUES
(1, '899123456001', 'Kitab Matan Al-Jurumiyyah', 'Kitab', 8000.00, 12000.00, 50, 10, 'Eks', 1, 1),
(2, '899123456002', 'Buku Tulis Santri 58 Lembar', 'ATK', 3000.00, 4500.00, 120, 20, 'Pcs', 1, 1),
(3, '899123456003', 'Peci Hitam Nasional No. 7', 'Seragam', 35000.00, 50000.00, 25, 5, 'Pcs', 1, 1),
(4, '899123456004', 'Air Mineral 600ml', 'Minuman', 2200.00, 3500.00, 96, 24, 'Botol', 1, 1),
(5, '899123456005', 'Sabun Mandi Santri', 'Kebutuhan Harian', 3500.00, 5000.00, 60, 15, 'Pcs', 1, 1);

-- 24. Stok Obat Poskestren
INSERT INTO `obat` (`id`, `nama`, `jenis`, `satuan`, `stok`, `min_stok`, `indikasi`, `kadaluarsa`) VALUES
(1, 'Paracetamol 500mg', 'Tablet', 'Strip', 40, 10, 'Pereda demam dan sakit kepala santri', '2028-12-31'),
(2, 'Antasida Doen', 'Tablet Kunyah', 'Strip', 30, 10, 'Mengurangi gejala maag dan asam lambung', '2028-10-31'),
(3, 'Oralit Sachet', 'Serbuk', 'Sachet', 50, 20, 'Rehidrasi diare dan dehidrasi', '2029-06-30'),
(4, 'Betadine Antiseptik 30ml', 'Cair', 'Botol', 15, 5, 'Obat luka luar lecet dan gores', '2028-05-31'),
(5, 'Minyak Kayu Putih 60ml', 'Cair', 'Botol', 20, 5, 'Meredakan masuk angin dan gatal serangga', '2029-01-31');

-- 25. Katalog Buku Perpustakaan
INSERT INTO `buku` (`id`, `kode_buku`,`isbn`, `judul`, `pengarang`, `penerbit`, `tahun_terbit`, `kategori`, `lokasi_rak`, `jumlah_total`, `jumlah_tersedia`) VALUES
(1, 'BK-001', '978-602-001-1', 'Fathul Qorib Al-Mujib', 'Ibnu Qosim Al-Ghazi', 'Darul Kutub Islamiyah', 2020, 'Kitab Kuning', 'Rak A1 - Fiqh', 10, 10),
(2, 'BK-002', '978-602-001-2', 'Riyadhus Shalihin', 'Imam An-Nawawi', 'Darul Ihya', 2019, 'Hadits & Akhlak', 'Rak B1 - Hadits', 8, 8),
(3, 'BK-003', '978-602-001-3', 'Kamus Al-Munawwir Arab-Indonesia', 'Ahmad Warson Munawwir', 'Pustaka Progressif', 2021, 'Referensi', 'Rak C1 - Kamus', 5, 5),
(4, 'BK-004', '978-602-001-4', 'Tafsir Jalalain', 'Jalaluddin Al-Mahalli & As-Suyuthi', 'Darul Fikr', 2018, 'Tafsir & Hadits', 'Rak A2 - Tafsir', 6, 6);

-- 26. Template Surat Baku
INSERT INTO `surat_templates` (`id`, `nama`, `kategori`, `format_nomor`, `judul_surat`, `isi_surat`, `deleted`) VALUES
(
  1,
  'Surat Keterangan Santri Aktif',
  'Resmi',
  '{NO}/PP-AI/KET/{BLN_ROMAWI}/{THN}',
  'SURAT KETERANGAN SANTRI AKTIF',
  'Yang bertanda tangan di bawah ini Pengasuh Pondok Pesantren Al-Ikhlas menerangkan bahwa:\n\nNama: {NAMA_SANTRI}\nNIS: {NIS}\nKelas/Jenjang: {KELAS} - {JENJANG}\nAlamat: {ALAMAT_SANTRI}\n\nAdalah benar-benar santri aktif yang sedang menempuh pendidikan di Pondok Pesantren Al-Ikhlas pada tahun ajaran berjalan dengan berkelakuan baik.\n\nDemikian surat keterangan ini dibuat untuk dipergunakan sebagaimana mestinya.',
  0
),
(
  2,
  'Surat Izin Pulang Santri',
  'Perizinan',
  '{NO}/PP-AI/IZIN/{BLN_ROMAWI}/{THN}',
  'SURAT IZIN PULANG / PESIAR',
  'Diberikan izin kepada santri:\n\nNama: {NAMA_SANTRI}\nNIS: {NIS}\nKamar: {KAMAR}\n\nUntuk pulang/pesiar mulai tanggal {TGL_MULAI} sampai dengan {TGL_KEMBALI} dengan keperluan: {KEPERLUAN}.\n\nSantri wajib kembali tepat waktu ke pondok dengan mengenakan busana muslim rapi.',
  0
),
(
  3,
  'Surat Undangan Pertemuan Wali Santri',
  'Resmi',
  '{NO}/PP-AI/UND/{BLN_ROMAWI}/{THN}',
  'UNDANGAN PERTEMUAN WALI SANTRI',
  'Kepada Yth.\nBapak/Ibu Wali Santri dari {NAMA_SANTRI}\ndi Tempat\n\nAssalamu''alaikum Wr. Wb.\n\nDengan hormat, mengharap kehadiran Bapak/Ibu pada acara Pertemuan Wali Santri dan Pembagian Rapor Semester yang insya Allah akan dilaksanakan pada:\n\nHari/Tanggal: {HARI_TANGGAL}\nWaktu: 08:30 WIB - Selesai\nTempat: Aula Utama Pondok Pesantren Al-Ikhlas\n\nDemikian undangan ini kami sampaikan, atas kehadiran dan kerjasamanya kami haturkan terima kasih.\n\nWassalamu''alaikum Wr. Wb.',
  0
);

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- SELESAI: DUMP SQL ESANTRI BERHASIL DIBUAT DENGAN STRUKTUR LENGKAP
-- =====================================================================