-- =====================================================================
-- SIMAP — Sistem Informasi Manajemen Pendidikan
-- BERKAS PEMASANGAN LENGKAP (satu berkas, sudah mencakup semua)
--
-- Cara pakai di cPanel:
-- 1. MySQL Databases -> buat basis data dan pengguna, beri hak penuh
-- 2. phpMyAdmin -> pilih basis data itu -> tab Import
-- 3. Unggah berkas ini, klik Go
--
-- Cara pakai lewat terminal:
-- mysql -u pengguna -p nama_basis_data < simap-lengkap.sql
--
-- Berkas ini TIDAK membuat basis data sendiri, sehingga dapat diimpor ke
-- basis data bernama apa pun — termasuk nama berawalan akun cPanel
-- seperti namaakun_simap.
--
-- Isinya setara menjalankan seluruh berkas 01 sampai 13 secara berurutan:
-- 116 tabel beserta data awal, contoh, dan koleksi buku. Bila memakai
-- berkas ini, berkas 01-13 tidak perlu dijalankan lagi.
--
-- Sekolah : SMP Islam Al Barokah (dapat diubah lewat menu Pengaturan)
-- Sandi : semua akun contoh memakai simap123 — SEGERA GANTI
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
/*M!999999\- enable the sandbox mode */
-- MariaDB dump 10.19 Distrib 10.11.14-MariaDB, for debian-linux-gnu (x86_64)
--
-- ------------------------------------------------------
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `absensi_guru`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `absensi_guru` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`guru_id` int(10) unsigned NOT NULL,
`tanggal` date NOT NULL,
`jam_masuk` time DEFAULT NULL,
`jam_pulang` time DEFAULT NULL,
`status` enum('HADIR','TERLAMBAT','SAKIT','IZIN','CUTI','DINAS_LUAR','ALPA') DEFAULT 'ALPA',
`menit_telat` smallint(6) DEFAULT 0,
`sumber` enum('RFID','GPS','MANUAL') DEFAULT 'RFID',
`latitude` decimal(10,7) DEFAULT NULL,
`longitude` decimal(10,7) DEFAULT NULL,
`foto_selfie` varchar(255) DEFAULT NULL,
`keterangan` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_absguru` (`guru_id`,`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `absensi_guru`
--
LOCK TABLES `absensi_guru` WRITE;
/*!40000 ALTER TABLE `absensi_guru` DISABLE KEYS */;
/*!40000 ALTER TABLE `absensi_guru` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `absensi_mapel`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `absensi_mapel` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`jadwal_id` int(10) unsigned DEFAULT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`tanggal` date NOT NULL,
`jam_ke` varchar(20) DEFAULT NULL,
`status` enum('HADIR','SAKIT','IZIN','ALPA') DEFAULT 'HADIR',
`catatan` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_absmapel` (`siswa_id`,`mapel_id`,`tanggal`,`jam_ke`),
KEY `kelas_id` (`kelas_id`,`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `absensi_mapel`
--
LOCK TABLES `absensi_mapel` WRITE;
/*!40000 ALTER TABLE `absensi_mapel` DISABLE KEYS */;
/*!40000 ALTER TABLE `absensi_mapel` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `absensi_siswa`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `absensi_siswa` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
`tanggal` date NOT NULL,
`jam_masuk` time DEFAULT NULL,
`jam_pulang` time DEFAULT NULL,
`status` enum('HADIR','TERLAMBAT','SAKIT','IZIN','ALPA','DISPEN') DEFAULT 'ALPA',
`menit_telat` smallint(6) DEFAULT 0,
`sumber` enum('RFID','MANUAL','IMPOR','ORTU') DEFAULT 'RFID',
`device_id` smallint(5) unsigned DEFAULT NULL,
`keterangan` varchar(200) DEFAULT NULL,
`file_surat` varchar(255) DEFAULT NULL,
`diinput_oleh` int(10) unsigned DEFAULT NULL,
`notif_terkirim` tinyint(1) DEFAULT 0,
`notif_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_abs` (`siswa_id`,`tanggal`),
KEY `tanggal` (`tanggal`),
KEY `kelas_id` (`kelas_id`,`tanggal`),
CONSTRAINT `absensi_siswa_ibfk_1` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=256 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `absensi_siswa`
--
LOCK TABLES `absensi_siswa` WRITE;
/*!40000 ALTER TABLE `absensi_siswa` DISABLE KEYS */;
INSERT INTO `absensi_siswa` VALUES
(1,0,1,4,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(2,0,1,4,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(3,0,1,4,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(4,0,1,4,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(5,0,1,4,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(6,0,1,4,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(7,0,1,4,'2026-08-28',NULL,NULL,'IZIN',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(8,0,1,4,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(9,0,1,4,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(10,0,1,4,'2026-08-22',NULL,NULL,'SAKIT',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(11,0,1,4,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(12,0,1,4,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(13,0,2,4,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(14,0,2,4,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(15,0,2,4,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(16,0,2,4,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(17,0,2,4,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(18,0,2,4,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(19,0,2,4,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(20,0,2,4,'2026-08-27',NULL,NULL,'IZIN',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(21,0,2,4,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(22,0,2,4,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(23,0,2,4,'2026-08-21',NULL,NULL,'SAKIT',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(24,0,2,4,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(25,0,3,4,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(26,0,3,4,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(27,0,3,4,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(28,0,3,4,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(29,0,3,4,'2026-08-31','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(30,0,3,4,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(31,0,3,4,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(32,0,3,4,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(33,0,3,4,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(34,0,3,4,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(35,0,3,4,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(36,0,3,4,'2026-08-20',NULL,NULL,'SAKIT',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(37,0,5,1,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(38,0,5,1,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(39,0,5,1,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(40,0,5,1,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(41,0,5,1,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(42,0,5,1,'2026-08-29','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(43,0,5,1,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(44,0,5,1,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(45,0,5,1,'2026-08-24',NULL,NULL,'IZIN',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(46,0,5,1,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(47,0,5,1,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(48,0,5,1,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(49,0,6,1,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(50,0,6,1,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(51,0,6,1,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(52,0,6,1,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(53,0,6,1,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(54,0,6,1,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(55,0,6,1,'2026-08-28','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(56,0,6,1,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(57,0,6,1,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(58,0,6,1,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(59,0,6,1,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(60,0,6,1,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(61,0,4,1,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(62,0,4,1,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(63,0,4,1,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(64,0,4,1,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(65,0,4,1,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(66,0,4,1,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(67,0,4,1,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(68,0,4,1,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(69,0,4,1,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(70,0,4,1,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(71,0,4,1,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(72,0,4,1,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(73,0,8,2,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(74,0,8,2,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(75,0,8,2,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(76,0,8,2,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(77,0,8,2,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(78,0,8,2,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(79,0,8,2,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(80,0,8,2,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(81,0,8,2,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(82,0,8,2,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(83,0,8,2,'2026-08-21',NULL,NULL,'IZIN',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(84,0,8,2,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(85,0,9,2,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(86,0,9,2,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(87,0,9,2,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(88,0,9,2,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(89,0,9,2,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(90,0,9,2,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(91,0,9,2,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(92,0,9,2,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(93,0,9,2,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(94,0,9,2,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(95,0,9,2,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(96,0,9,2,'2026-08-20',NULL,NULL,'IZIN',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(97,0,7,2,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(98,0,7,2,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(99,0,7,2,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(100,0,7,2,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(101,0,7,2,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(102,0,7,2,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(103,0,7,2,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(104,0,7,2,'2026-08-27','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(105,0,7,2,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(106,0,7,2,'2026-08-22',NULL,NULL,'IZIN',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(107,0,7,2,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(108,0,7,2,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(109,0,11,3,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(110,0,11,3,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(111,0,11,3,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(112,0,11,3,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(113,0,11,3,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(114,0,11,3,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(115,0,11,3,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(116,0,11,3,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(117,0,11,3,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(118,0,11,3,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(119,0,11,3,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(120,0,11,3,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(121,0,10,3,'2026-09-07','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(122,0,10,3,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(123,0,10,3,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(124,0,10,3,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(125,0,10,3,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(126,0,10,3,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(127,0,10,3,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(128,0,10,3,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(129,0,10,3,'2026-08-24','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(130,0,10,3,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(131,0,10,3,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(132,0,10,3,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(133,0,13,4,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(134,0,13,4,'2026-09-05','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(135,0,13,4,'2026-09-04','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(136,0,13,4,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(137,0,13,4,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(138,0,13,4,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(139,0,13,4,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(140,0,13,4,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(141,0,13,4,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(142,0,13,4,'2026-08-22','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(143,0,13,4,'2026-08-21','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(144,0,13,4,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(145,0,12,4,'2026-09-07','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(146,0,12,4,'2026-09-05','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(147,0,12,4,'2026-09-04','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(148,0,12,4,'2026-09-03','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(149,0,12,4,'2026-08-31','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(150,0,12,4,'2026-08-29','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(151,0,12,4,'2026-08-28','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(152,0,12,4,'2026-08-27','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(153,0,12,4,'2026-08-24','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(154,0,12,4,'2026-08-22','07:15:00','12:30:00','TERLAMBAT',15,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(155,0,12,4,'2026-08-21','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21'),
(156,0,12,4,'2026-08-20','06:45:00','12:30:00','HADIR',0,'RFID',NULL,NULL,NULL,NULL,0,NULL,'2026-09-08 23:22:21');
/*!40000 ALTER TABLE `absensi_siswa` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ai_kuota`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ai_kuota` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`periode` char(7) DEFAULT NULL COMMENT '2026-08',
`jumlah_request` int(11) DEFAULT 0,
`token_terpakai` int(11) DEFAULT 0,
`batas_request` int(11) DEFAULT 200,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_kuota` (`user_id`,`periode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ai_kuota`
--
LOCK TABLES `ai_kuota` WRITE;
/*!40000 ALTER TABLE `ai_kuota` DISABLE KEYS */;
/*!40000 ALTER TABLE `ai_kuota` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ai_log`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ai_log` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`template_kode` varchar(50) DEFAULT NULL,
`modul` varchar(40) DEFAULT NULL,
`ref_tabel` varchar(60) DEFAULT NULL,
`ref_id` bigint(20) unsigned DEFAULT NULL,
`prompt` longtext DEFAULT NULL,
`response` longtext DEFAULT NULL,
`model` varchar(60) DEFAULT NULL,
`token_input` int(11) DEFAULT NULL,
`token_output` int(11) DEFAULT NULL,
`biaya_rp` decimal(12,2) DEFAULT 0.00,
`durasi_ms` int(11) DEFAULT NULL,
`status` enum('SUKSES','GAGAL','TIMEOUT') DEFAULT 'SUKSES',
`error_msg` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ai_log`
--
LOCK TABLES `ai_log` WRITE;
/*!40000 ALTER TABLE `ai_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `ai_log` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ai_template`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ai_template` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(50) NOT NULL,
`nama` varchar(120) DEFAULT NULL,
`modul` varchar(40) DEFAULT NULL,
`deskripsi` varchar(250) DEFAULT NULL,
`system_prompt` text DEFAULT NULL,
`user_prompt` text DEFAULT NULL COMMENT 'pakai placeholder {{var}}',
`model` varchar(60) DEFAULT 'claude-sonnet-4-6',
`max_tokens` smallint(6) DEFAULT 4000,
`temperature` decimal(3,2) DEFAULT 0.70,
`output_format` enum('TEKS','JSON','HTML') DEFAULT 'TEKS',
`is_aktif` tinyint(1) DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ai_template`
--
LOCK TABLES `ai_template` WRITE;
/*!40000 ALTER TABLE `ai_template` DISABLE KEYS */;
INSERT INTO `ai_template` VALUES
(1,'MODUL_AJAR','Generate Modul Ajar','admguru','Membuat modul ajar lengkap sesuai kurikulum aktif','Anda adalah guru ahli jenjang Sekolah Dasar di Indonesia yang menguasai {{kurikulum}} dan pendekatan Pembelajaran Mendalam (deep learning). Tulis perangkat ajar yang praktis, membumi, dan siap pakai oleh guru kelas. Bahasa Indonesia yang jelas dan ramah anak. Jangan gunakan istilah asing tanpa penjelasan.','Buatkan MODUL AJAR untuk:\nMata pelajaran: {{mapel}}\nKelas/Fase: {{tingkat}} (Fase {{fase}})\nSemester: {{semester}}\nTopik: {{judul}}\nAlokasi: {{alokasi}}\nDimensi yang disasar: {{dimensi}}\n\nKeluarkan JSON dengan kunci: tujuan_pembelajaran, pemahaman_bermakna, pertanyaan_pemantik, model_pembelajaran, metode, sarana_prasarana, kegiatan_pendahuluan, kegiatan_inti, kegiatan_penutup, asesmen_diagnostik, asesmen_formatif, asesmen_sumatif, rubrik_penilaian, pengayaan, remedial, refleksi_guru, refleksi_siswa, lampiran_lkpd, glosarium, daftar_pustaka. Nilai berupa string HTML sederhana (gunakan ,
, - , ). Balas JSON saja tanpa pengantar dan tanpa pagar kode.','claude-sonnet-4-6',8000,0.70,'JSON',1),
(2,'PROTA','Generate Program Tahunan','admguru','Menyusun prota per mapel per tingkat','Anda perancang kurikulum SD yang menguasai struktur {{kurikulum}} dan alokasi minggu efektif di Indonesia.','Susun PROGRAM TAHUNAN untuk mapel {{mapel}} kelas {{tingkat}} (Fase {{fase}}), total {{total_jp}} JP setahun. Bagi ke Semester 1 dan 2. Balas JSON array: [{\"semester\":\"1\",\"lingkup_materi\":\"...\",\"uraian\":\"...\",\"alokasi_jp\":8}]. JSON saja.','claude-sonnet-4-6',6000,0.60,'JSON',1),
(3,'ATP','Generate ATP','admguru','Alur Tujuan Pembelajaran dari CP','Anda perancang ATP untuk SD yang menurunkan Capaian Pembelajaran menjadi Tujuan Pembelajaran yang terukur dan berurutan.','Dari Capaian Pembelajaran berikut:\n{{cp}}\n\nSusun ATP mapel {{mapel}} kelas {{tingkat}} Fase {{fase}} semester {{semester}}. Balas JSON array: [{\"urutan\":1,\"tujuan\":\"...\",\"materi_pokok\":\"...\",\"kata_kunci\":\"...\",\"alokasi_jp\":4,\"asesmen\":\"...\",\"glosarium\":\"...\"}]. JSON saja.','claude-sonnet-4-6',6000,0.60,'JSON',1),
(4,'BANK_SOAL','Generate Bank Soal','ujian','Membuat butir soal sesuai TP dan level kognitif','Anda penulis soal SD berpengalaman. Soal harus sesuai usia anak SD, kalimat pendek, konteks keseharian Indonesia, dan bebas jebakan yang tidak perlu. Pengecoh harus masuk akal.','Buat {{jumlah}} soal {{tipe}} untuk mapel {{mapel}} kelas {{tingkat}}, topik: {{topik}}. Sebaran level kognitif: {{level}}. Balas JSON array: [{\"tipe\":\"PG\",\"level_kognitif\":\"C2\",\"tingkat_kesulitan\":\"SEDANG\",\"pertanyaan\":\"...\",\"opsi\":[{\"key\":\"A\",\"teks\":\"...\"},{\"key\":\"B\",\"teks\":\"...\"},{\"key\":\"C\",\"teks\":\"...\"},{\"key\":\"D\",\"teks\":\"...\"}],\"kunci\":\"B\",\"pembahasan\":\"...\"}]. JSON saja.','claude-sonnet-4-6',8000,0.80,'JSON',1),
(5,'DESKRIPSI_RAPOR','Deskripsi Capaian Rapor','rapor','Kalimat deskripsi capaian per mapel untuk rapor','Anda wali kelas SD yang menulis deskripsi rapor. Gaya kalimat: positif, spesifik, 2-3 kalimat, menyebut kompetensi yang menonjol lebih dulu lalu yang perlu ditingkatkan. Hindari kata \"kurang\" atau \"gagal\"; gunakan \"perlu bimbingan dalam...\". Sebut nama panggilan siswa.','Siswa: {{nama}} ({{panggilan}}), kelas {{kelas}}, mapel {{mapel}}. Nilai akhir {{nilai}}. TP dengan capaian tertinggi: {{tp_tinggi}}. TP yang masih perlu bimbingan: {{tp_rendah}}. Tulis deskripsi rapor. Balas teks saja tanpa pengantar.','claude-sonnet-4-6',600,0.70,'TEKS',1),
(6,'CATATAN_WALI','Catatan Wali Kelas','rapor','Catatan holistik untuk rapor','Anda wali kelas SD yang mengenal siswanya. Tulis catatan 3-4 kalimat yang hangat, jujur, dan memberi arah perbaikan konkret untuk orang tua.','Data siswa {{nama}} kelas {{kelas}}: rata-rata nilai {{rata}}, kehadiran hadir {{hadir}} sakit {{sakit}} izin {{izin}} alpa {{alpa}} hari. Capaian dimensi: {{dimensi}}. Catatan perilaku: {{perilaku}}. Ekstrakurikuler: {{ekskul}}. Tulis catatan wali kelas. Teks saja.','claude-sonnet-4-6',700,0.70,'TEKS',1),
(7,'GAME_LEVEL','Generate Level Game','game','Membuat isi level game edukasi','Anda perancang game edukasi anak SD. Konten harus singkat, menyenangkan, dan tepat secara akademik.','Buat data level untuk game bertipe {{tipe_game}} mapel {{mapel}} kelas {{tingkat}} topik {{topik}}, jumlah butir {{jumlah}}. Balas JSON sesuai tipe: KUIS_CEPAT -> [{\"tanya\":\"...\",\"opsi\":[\"...\"],\"kunci\":0}]; MENJODOHKAN -> [{\"kiri\":\"...\",\"kanan\":\"...\"}]; SUSUN_KATA -> [{\"kata\":\"...\",\"petunjuk\":\"...\"}]; TEBAK_GAMBAR -> [{\"petunjuk\":\"...\",\"jawaban\":\"...\"}]; HITUNG_CEPAT -> [{\"soal\":\"7 x 8\",\"jawaban\":56}]. JSON saja.','claude-sonnet-4-6',4000,0.90,'JSON',1),
(8,'RINGKASAN_HARIAN','Ringkasan Harian untuk Ortu','ortu','Narasi singkat aktivitas anak hari ini','Anda asisten sekolah yang menulis laporan harian untuk orang tua murid SD. Gaya: hangat, ringkas, maksimal 3 kalimat, tanpa jargon pendidikan.','Data hari ini untuk {{panggilan}} kelas {{kelas}}: absen {{status_absen}} (masuk {{jam_masuk}}), materi selesai {{materi}}, tugas dikumpulkan {{tugas_selesai}}, tugas belum {{tugas_pending}}, literasi {{menit_literasi}} menit, poin {{poin}}. Catatan guru: {{catatan}}. Tulis ringkasan untuk orang tua. Teks saja.','claude-sonnet-4-6',400,0.70,'TEKS',1),
(9,'KOREKSI_URAIAN','Koreksi Soal Uraian','ujian','Menilai jawaban uraian siswa dengan rubrik','Anda korektor jawaban uraian siswa SD. Nilai berdasarkan rubrik, adil, dan beri umpan balik yang membangun dalam 1-2 kalimat.','Soal: {{soal}}\nKunci/rubrik: {{kunci}}\nSkor maksimal: {{skor_maks}}\nJawaban siswa: {{jawaban}}\nBalas JSON: {\"skor\":angka,\"benar\":true/false,\"catatan\":\"...\"}. JSON saja.','claude-sonnet-4-6',500,0.30,'JSON',1),
(10,'SURAT_TU','Draft Surat Sekolah','tu','Menyusun draft surat resmi sekolah','Anda staf tata usaha sekolah yang menulis surat resmi berbahasa Indonesia sesuai kaidah persuratan dinas.','Buat draft surat jenis {{jenis}} dari {{sekolah}} kepada {{tujuan}} perihal {{perihal}}. Poin isi: {{poin}}. Keluarkan HTML badan surat saja (tanpa kop dan tanda tangan).','claude-sonnet-4-6',2000,0.50,'HTML',1);
/*!40000 ALTER TABLE `ai_template` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `anggota_perpus`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `anggota_perpus` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`no_anggota` varchar(30) DEFAULT NULL,
`tipe` enum('SISWA','GURU','PEGAWAI') NOT NULL,
`ref_id` int(10) unsigned NOT NULL,
`tanggal_daftar` date DEFAULT NULL,
`berlaku_sampai` date DEFAULT NULL,
`maks_pinjam` tinyint(4) DEFAULT 2,
`lama_pinjam_hari` tinyint(4) DEFAULT 7,
`status` enum('AKTIF','NONAKTIF','DIBLOKIR') DEFAULT 'AKTIF',
PRIMARY KEY (`id`),
UNIQUE KEY `uq_anggota` (`tipe`,`ref_id`),
UNIQUE KEY `no_anggota` (`no_anggota`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `anggota_perpus`
--
LOCK TABLES `anggota_perpus` WRITE;
/*!40000 ALTER TABLE `anggota_perpus` DISABLE KEYS */;
INSERT INTO `anggota_perpus` VALUES
(1,'AP00001','SISWA',1,'2026-09-08','2032-09-08',2,7,'AKTIF'),
(2,'AP00002','SISWA',2,'2026-09-08','2032-09-08',2,7,'AKTIF'),
(3,'AP00003','SISWA',3,'2026-09-08','2032-09-08',2,7,'AKTIF'),
(4,'AP-2024001','SISWA',4,'2024-07-15','2030-07-15',2,7,'AKTIF'),
(5,'AP-2024002','SISWA',5,'2024-07-15','2030-07-15',2,7,'AKTIF'),
(6,'AP-2024003','SISWA',6,'2024-07-15','2030-07-15',2,7,'AKTIF'),
(7,'AP-2023001','SISWA',7,'2023-07-17','2029-07-17',2,7,'AKTIF'),
(8,'AP-2023002','SISWA',8,'2023-07-17','2029-07-17',2,7,'AKTIF'),
(9,'AP-2023003','SISWA',9,'2023-07-17','2029-07-17',2,7,'AKTIF'),
(10,'AP-2022001','SISWA',10,'2022-07-18','2028-07-18',2,7,'AKTIF'),
(11,'AP-2022002','SISWA',11,'2022-07-18','2028-07-18',2,7,'AKTIF'),
(12,'AP-2021001','SISWA',12,'2021-07-19','2027-07-19',2,7,'AKTIF'),
(13,'AP-2021002','SISWA',13,'2021-07-19','2027-07-19',2,7,'AKTIF');
/*!40000 ALTER TABLE `anggota_perpus` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `atp`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `atp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`tingkat` tinyint(4) DEFAULT NULL,
`fase` enum('A','B','C','D','E','F') DEFAULT NULL,
`semester` enum('1','2') DEFAULT NULL,
`judul` varchar(200) DEFAULT NULL,
`rasional` text DEFAULT NULL,
`file_pdf` varchar(255) DEFAULT NULL,
`status` enum('DRAFT','DIAJUKAN','DISETUJUI','REVISI') DEFAULT 'DRAFT',
`sumber_ai` tinyint(1) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `guru_id` (`guru_id`),
CONSTRAINT `atp_ibfk_1` FOREIGN KEY (`guru_id`) REFERENCES `guru` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `atp`
--
LOCK TABLES `atp` WRITE;
/*!40000 ALTER TABLE `atp` DISABLE KEYS */;
/*!40000 ALTER TABLE `atp` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `atp_detail`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `atp_detail` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`atp_id` int(10) unsigned NOT NULL,
`tp_id` int(10) unsigned DEFAULT NULL,
`urutan` smallint(6) DEFAULT NULL,
`tujuan` text DEFAULT NULL,
`materi_pokok` varchar(250) DEFAULT NULL,
`kata_kunci` varchar(200) DEFAULT NULL,
`profil_dimensi` varchar(200) DEFAULT NULL COMMENT 'csv id dimensi',
`alokasi_jp` smallint(6) DEFAULT NULL,
`asesmen` varchar(200) DEFAULT NULL,
`glosarium` text DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `atp_id` (`atp_id`),
CONSTRAINT `atp_detail_ibfk_1` FOREIGN KEY (`atp_id`) REFERENCES `atp` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `atp_detail`
--
LOCK TABLES `atp_detail` WRITE;
/*!40000 ALTER TABLE `atp_detail` DISABLE KEYS */;
/*!40000 ALTER TABLE `atp_detail` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `audit_log`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `audit_log` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned DEFAULT NULL,
`aksi` varchar(60) DEFAULT NULL,
`modul` varchar(40) DEFAULT NULL,
`ref_tabel` varchar(60) DEFAULT NULL,
`ref_id` bigint(20) unsigned DEFAULT NULL,
`deskripsi` text DEFAULT NULL,
`ip` varchar(45) DEFAULT NULL,
`user_agent` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `modul` (`modul`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `audit_log`
--
LOCK TABLES `audit_log` WRITE;
/*!40000 ALTER TABLE `audit_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `audit_log` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `badge`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `badge` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(40) DEFAULT NULL,
`nama` varchar(80) DEFAULT NULL,
`deskripsi` varchar(200) DEFAULT NULL,
`ikon` varchar(255) DEFAULT NULL,
`warna` varchar(9) DEFAULT NULL,
`kriteria` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '{"sumber":"LITERASI","min":10}' CHECK (json_valid(`kriteria`)),
`poin_bonus` smallint(6) DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `badge`
--
LOCK TABLES `badge` WRITE;
/*!40000 ALTER TABLE `badge` DISABLE KEYS */;
INSERT INTO `badge` VALUES
(1,'KUTU_BUKU','Kutu Buku','Menyelesaikan 10 buku dalam satu semester',NULL,'#B7791F','{\"sumber\":\"LITERASI\",\"min\":10}',100),
(2,'RAJIN_HADIR','Selalu Hadir','Hadir penuh selama 1 bulan tanpa alpa',NULL,'#1F7A5A','{\"sumber\":\"ABSENSI\",\"min\":20}',50),
(3,'JUARA_KUIS','Jagoan Kuis','Nilai 100 pada 5 ujian berbeda',NULL,'#2B4570','{\"sumber\":\"UJIAN\",\"min\":5}',75),
(4,'PENJELAJAH','Penjelajah Materi','Menuntaskan 25 materi pembelajaran',NULL,'#E67E22','{\"sumber\":\"MATERI\",\"min\":25}',60),
(5,'SANG_JUARA','Sang Juara Game','Meraih 3 bintang di 10 level game',NULL,'#8E44AD','{\"sumber\":\"GAME\",\"min\":10}',80),
(6,'TEPAT_WAKTU','Tepat Waktu','Mengumpulkan 15 tugas sebelum deadline',NULL,'#16A085','{\"sumber\":\"TUGAS\",\"min\":15}',50);
/*!40000 ALTER TABLE `badge` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `badge_siswa`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `badge_siswa` (
`siswa_id` int(10) unsigned NOT NULL,
`badge_id` smallint(5) unsigned NOT NULL,
`diraih_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`siswa_id`,`badge_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `badge_siswa`
--
LOCK TABLES `badge_siswa` WRITE;
/*!40000 ALTER TABLE `badge_siswa` DISABLE KEYS */;
/*!40000 ALTER TABLE `badge_siswa` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `bank_soal`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `bank_soal` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`mapel_id` smallint(5) unsigned NOT NULL,
`tingkat` tinyint(4) DEFAULT NULL,
`fase` enum('A','B','C','D','E','F') DEFAULT NULL,
`tp_id` int(10) unsigned DEFAULT NULL,
`guru_id` int(10) unsigned DEFAULT 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) DEFAULT NULL,
`audio` varchar(255) DEFAULT NULL,
`opsi` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '[{"key":"A","teks":"...","gambar":null}]' CHECK (json_valid(`opsi`)),
`kunci` varchar(100) DEFAULT NULL,
`pembahasan` text DEFAULT NULL,
`skor` decimal(5,2) DEFAULT 1.00,
`sumber_ai` tinyint(1) DEFAULT 0,
`dipakai` int(11) DEFAULT 0,
`rata_benar` decimal(5,2) DEFAULT NULL,
`status` enum('DRAFT','SIAP','ARSIP') DEFAULT 'SIAP',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `mapel_id` (`mapel_id`,`tingkat`),
CONSTRAINT `bank_soal_ibfk_1` FOREIGN KEY (`mapel_id`) REFERENCES `mapel` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `bank_soal`
--
LOCK TABLES `bank_soal` WRITE;
/*!40000 ALTER TABLE `bank_soal` DISABLE KEYS */;
INSERT INTO `bank_soal` VALUES
(1,18,7,'D',NULL,1,'PG','C2','SEDANG','Kalimat manakah yang termasuk kalimat deskripsi?',NULL,NULL,'[\"Ayah pergi ke pasar pagi tadi.\",\"Rumah itu bercat biru muda dengan pagar kayu rendah.\",\"Mengapa kamu terlambat?\",\"Tutup pintunya!\"]','B','Kalimat deskripsi menggambarkan ciri suatu objek sehingga pembaca dapat membayangkannya.',1.00,0,0,NULL,'','2026-09-08 23:22:21'),
(2,18,7,'D',NULL,1,'PG','C1','MUDAH','Struktur teks deskripsi yang benar adalah…',NULL,NULL,'[\"Identifikasi - deskripsi bagian - simpulan\",\"Orientasi - komplikasi - resolusi\",\"Tesis - argumen - penegasan\",\"Abstrak - orientasi - krisis\"]','A','Teks deskripsi diawali identifikasi objek, dirinci pada deskripsi bagian, lalu ditutup simpulan.',1.00,0,0,NULL,'','2026-09-08 23:22:21'),
(3,18,7,'D',NULL,1,'PG','C3','SEDANG','Kata yang paling tepat melengkapi kalimat \"Air terjun itu ___ dari tebing setinggi tiga puluh meter.\" adalah…',NULL,NULL,'[\"menetes\",\"tergenang\",\"tercurah\",\"merembes\"]','C','Kata tercurah menggambarkan air yang jatuh deras dari ketinggian.',1.00,0,0,NULL,'','2026-09-08 23:22:21'),
(4,18,7,'D',NULL,1,'URAIAN','C4','SULIT','Tuliskan tiga kalimat deskripsi tentang ruang kelasmu. Gunakan kata sifat yang berbeda pada setiap kalimat.',NULL,NULL,NULL,NULL,'Jawaban benar bila memuat tiga kalimat, menggambarkan objek nyata di kelas, dan memakai kata sifat berbeda.',3.00,0,0,NULL,'','2026-09-08 23:22:21');
/*!40000 ALTER TABLE `bank_soal` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `buku`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `buku` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`kode_buku` varchar(30) DEFAULT NULL,
`isbn` varchar(20) DEFAULT NULL,
`judul` varchar(250) NOT NULL,
`subjudul` varchar(250) DEFAULT NULL,
`pengarang` varchar(200) DEFAULT NULL,
`penerbit` varchar(120) DEFAULT NULL,
`tahun_terbit` year(4) DEFAULT NULL,
`edisi` varchar(30) DEFAULT NULL,
`jumlah_halaman` smallint(6) DEFAULT NULL,
`bahasa` varchar(30) DEFAULT 'Indonesia',
`kategori_id` smallint(5) unsigned DEFAULT NULL,
`no_ddc` varchar(20) DEFAULT NULL,
`jenis` enum('FISIK','DIGITAL','KEDUANYA') DEFAULT 'FISIK',
`jenjang_baca` enum('A','B','C','UMUM') DEFAULT 'UMUM' COMMENT 'level literasi',
`sinopsis` text DEFAULT NULL,
`sampul` varchar(255) DEFAULT NULL,
`file_ebook` varchar(255) DEFAULT NULL,
`format_ebook` enum('PDF','EPUB','FLIPBOOK','AUDIO') DEFAULT NULL,
`url_baca` varchar(255) DEFAULT NULL,
`boleh_unduh` tinyint(1) DEFAULT 0,
`jumlah_eks` smallint(6) DEFAULT 0,
`jumlah_tersedia` smallint(6) DEFAULT 0,
`lokasi_rak` varchar(30) DEFAULT NULL,
`sumber_dana` varchar(60) DEFAULT NULL,
`harga` decimal(12,2) DEFAULT NULL,
`poin_baca` smallint(6) DEFAULT 20,
`jumlah_dibaca` int(11) DEFAULT 0,
`rating_avg` decimal(3,2) DEFAULT 0.00,
`status` enum('AKTIF','ARSIP') DEFAULT 'AKTIF',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `kode_buku` (`kode_buku`),
KEY `kategori_id` (`kategori_id`),
FULLTEXT KEY `ft_buku` (`judul`,`pengarang`,`sinopsis`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `buku`
--
LOCK TABLES `buku` WRITE;
/*!40000 ALTER TABLE `buku` DISABLE KEYS */;
INSERT INTO `buku` VALUES
(1,'BK-001',NULL,'Laskar Pelangi',NULL,'Andrea Hirata','Bentang Pustaka',2005,NULL,12,'Indonesia',9,NULL,'KEDUANYA','UMUM','Kisah sepuluh anak Belitung yang gigih bersekolah di tengah keterbatasan.',NULL,'ebook/buku-contoh-1.pdf','PDF',NULL,0,3,3,'Rak A-1',NULL,NULL,30,0,0.00,'AKTIF','2026-09-08 23:22:21'),
(2,'BK-002',NULL,'Bumi Manusia',NULL,'Pramoedya Ananta Toer','Lentera Dipantara',1980,NULL,535,'Indonesia',9,NULL,'FISIK','UMUM','Perjalanan Minke menghadapi ketidakadilan pada masa kolonial.',NULL,NULL,NULL,NULL,0,2,2,'Rak A-2',NULL,NULL,35,0,0.00,'AKTIF','2026-09-08 23:22:21'),
(3,'BK-003',NULL,'Sang Pemimpi',NULL,'Andrea Hirata','Bentang Pustaka',2006,NULL,292,'Indonesia',9,NULL,'KEDUANYA','UMUM','Lanjutan kisah Ikal dan Arai yang mengejar mimpi hingga ke Eropa.',NULL,NULL,NULL,NULL,0,3,3,'Rak A-1',NULL,NULL,25,0,0.00,'AKTIF','2026-09-08 23:22:21'),
(4,'BK-004',NULL,'Fisika untuk SMP Kelas VIII',NULL,'Tim Penyusun','Erlangga',2022,NULL,12,'Indonesia',6,NULL,'KEDUANYA','UMUM','Materi getaran, gelombang, cahaya, dan tekanan untuk kelas VIII.',NULL,'ebook/buku-contoh-2.pdf','PDF',NULL,0,5,5,'Rak B-1',NULL,NULL,20,0,0.00,'AKTIF','2026-09-08 23:22:21'),
(5,'BK-005',NULL,'Matematika Menyenangkan',NULL,'Ridwan Abdullah','Grasindo',2021,NULL,196,'Indonesia',6,NULL,'KEDUANYA','UMUM','Konsep aljabar dan geometri dijelaskan lewat contoh sehari-hari.',NULL,NULL,NULL,NULL,0,4,4,'Rak B-2',NULL,NULL,20,0,0.00,'AKTIF','2026-09-08 23:22:21'),
(6,'BK-006',NULL,'Atlas Sejarah Indonesia',NULL,'Tim Redaksi','Gramedia',2020,NULL,164,'Indonesia',4,NULL,'FISIK','UMUM','Peta dan lini masa peristiwa penting sejarah Indonesia.',NULL,NULL,NULL,NULL,0,2,2,'Rak C-1',NULL,NULL,15,0,0.00,'AKTIF','2026-09-08 23:22:21'),
(7,'BK-007',NULL,'Kisah 25 Nabi dan Rasul',NULL,'H. Abdul Karim','Pustaka Amani',2019,NULL,212,'Indonesia',3,NULL,'KEDUANYA','UMUM','Riwayat para nabi dan rasul beserta teladan akhlaknya.',NULL,NULL,NULL,NULL,0,4,4,'Rak D-1',NULL,NULL,20,0,0.00,'AKTIF','2026-09-08 23:22:21'),
(8,'BK-008',NULL,'Rahasia Tubuh Manusia',NULL,'dr. Siti Nurhaliza','Elex Media',2021,NULL,12,'Indonesia',7,NULL,'DIGITAL','UMUM','Cara kerja organ tubuh dijelaskan dengan bahasa yang mudah dipahami.',NULL,'ebook/buku-contoh-3.pdf','PDF',NULL,1,0,0,NULL,NULL,NULL,20,0,0.00,'AKTIF','2026-09-08 23:22:21'),
(9,'BK-009',NULL,'Kamus Bahasa Inggris Pelajar',NULL,'Tim Penyusun','Balai Pustaka',2018,NULL,624,'Indonesia',5,NULL,'FISIK','UMUM','Kosakata dan contoh kalimat untuk jenjang SMP dan SMA.',NULL,NULL,NULL,NULL,0,6,6,'Rak E-1',NULL,NULL,10,0,0.00,'AKTIF','2026-09-08 23:22:21'),
(10,'BK-010',NULL,'Antologi Puisi Nusantara',NULL,'Berbagai Penyair','Balai Pustaka',2017,NULL,132,'Indonesia',9,NULL,'KEDUANYA','UMUM','Kumpulan puisi penyair Indonesia dari berbagai zaman.',NULL,NULL,NULL,NULL,0,2,2,'Rak A-3',NULL,NULL,20,0,0.00,'AKTIF','2026-09-08 23:22:21');
/*!40000 ALTER TABLE `buku` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `buku_eksemplar`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `buku_eksemplar` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`buku_id` int(10) unsigned NOT NULL,
`kode_induk` varchar(40) NOT NULL,
`barcode` varchar(40) DEFAULT NULL,
`kondisi` enum('BAIK','RUSAK_RINGAN','RUSAK_BERAT','HILANG') DEFAULT 'BAIK',
`status` enum('TERSEDIA','DIPINJAM','DIPESAN','PERBAIKAN','HILANG') DEFAULT 'TERSEDIA',
`tanggal_masuk` date DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `kode_induk` (`kode_induk`),
UNIQUE KEY `barcode` (`barcode`),
KEY `buku_id` (`buku_id`),
CONSTRAINT `buku_eksemplar_ibfk_1` FOREIGN KEY (`buku_id`) REFERENCES `buku` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `buku_eksemplar`
--
LOCK TABLES `buku_eksemplar` WRITE;
/*!40000 ALTER TABLE `buku_eksemplar` DISABLE KEYS */;
/*!40000 ALTER TABLE `buku_eksemplar` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `buku_kategori`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `buku_kategori` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode_ddc` varchar(10) DEFAULT NULL,
`nama` varchar(80) DEFAULT NULL,
`parent_id` smallint(5) unsigned DEFAULT NULL,
`warna` varchar(9) DEFAULT NULL,
`ikon` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `buku_kategori`
--
LOCK TABLES `buku_kategori` WRITE;
/*!40000 ALTER TABLE `buku_kategori` DISABLE KEYS */;
INSERT INTO `buku_kategori` VALUES
(1,'000','Karya Umum & Ensiklopedia',NULL,'#7F8C8D','library'),
(2,'100','Filsafat & Psikologi',NULL,'#8E44AD','brain'),
(3,'200','Agama',NULL,'#1F7A5A','moon-star'),
(4,'300','Ilmu Sosial',NULL,'#C0392B','users'),
(5,'400','Bahasa',NULL,'#2980B9','languages'),
(6,'500','Sains & Matematika',NULL,'#16A085','flask'),
(7,'600','Teknologi & Kesehatan',NULL,'#D35400','cpu'),
(8,'700','Kesenian & Olahraga',NULL,'#E67E22','palette'),
(9,'800','Sastra & Cerita Anak',NULL,'#B7791F','book-heart'),
(10,'900','Sejarah & Geografi',NULL,'#2B4570','globe'),
(11,'BTX','Buku Teks Pelajaran',NULL,'#1F4A3C','book-open');
/*!40000 ALTER TABLE `buku_kategori` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `catatan_perilaku`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `catatan_perilaku` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`siswa_id` int(10) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`tanggal` date NOT NULL,
`jenis` enum('POSITIF','PERLU_PERHATIAN','PELANGGARAN') DEFAULT 'POSITIF',
`kategori` varchar(60) DEFAULT NULL,
`catatan` text DEFAULT NULL,
`poin` smallint(6) DEFAULT 0,
`tindak_lanjut` text DEFAULT NULL,
`foto` varchar(255) DEFAULT NULL,
`lapor_ortu` tinyint(1) DEFAULT 1,
`dibaca_ortu` tinyint(1) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `siswa_id` (`siswa_id`,`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `catatan_perilaku`
--
LOCK TABLES `catatan_perilaku` WRITE;
/*!40000 ALTER TABLE `catatan_perilaku` DISABLE KEYS */;
/*!40000 ALTER TABLE `catatan_perilaku` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `cp`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `cp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`kurikulum` enum('MERDEKA','KBC','K13') DEFAULT 'MERDEKA',
`mapel_id` smallint(5) unsigned NOT NULL,
`fase` enum('A','B','C','D','E','F') DEFAULT NULL,
`elemen` varchar(120) NOT NULL COMMENT 'Menyimak-Berbicara, Bilangan, dst',
`deskripsi` text NOT NULL,
`sumber` varchar(120) DEFAULT 'BSKAP',
PRIMARY KEY (`id`),
KEY `mapel_id` (`mapel_id`,`fase`),
CONSTRAINT `cp_ibfk_1` FOREIGN KEY (`mapel_id`) REFERENCES `mapel` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `cp`
--
LOCK TABLES `cp` WRITE;
/*!40000 ALTER TABLE `cp` DISABLE KEYS */;
/*!40000 ALTER TABLE `cp` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `dapodik_mapping`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `dapodik_mapping` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`entitas` enum('SISWA','PTK','ROMBEL','MAPEL') NOT NULL,
`local_id` int(10) unsigned NOT NULL,
`dapodik_id` varchar(64) NOT NULL COMMENT 'GUID peserta_didik_id / ptk_id',
`terakhir_sync` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_map` (`entitas`,`local_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `dapodik_mapping`
--
LOCK TABLES `dapodik_mapping` WRITE;
/*!40000 ALTER TABLE `dapodik_mapping` DISABLE KEYS */;
/*!40000 ALTER TABLE `dapodik_mapping` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `dimensi_indikator`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `dimensi_indikator` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`dimensi_id` tinyint(3) unsigned NOT NULL,
`fase` enum('A','B','C','D','E','F') DEFAULT NULL,
`indikator` text NOT NULL,
PRIMARY KEY (`id`),
KEY `dimensi_id` (`dimensi_id`),
CONSTRAINT `dimensi_indikator_ibfk_1` FOREIGN KEY (`dimensi_id`) REFERENCES `dimensi_lulusan` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `dimensi_indikator`
--
LOCK TABLES `dimensi_indikator` WRITE;
/*!40000 ALTER TABLE `dimensi_indikator` DISABLE KEYS */;
INSERT INTO `dimensi_indikator` VALUES
(1,1,'A','Kelas 1-2: mulai mengenal dan meniru perilaku keimanan dan ketakwaan kepada tuhan yme dalam kegiatan sehari-hari di sekolah.'),
(2,1,'B','Kelas 3-4: membiasakan diri menerapkan keimanan dan ketakwaan kepada tuhan yme dalam kegiatan sehari-hari di sekolah.'),
(3,1,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam keimanan dan ketakwaan kepada tuhan yme dalam kegiatan sehari-hari di sekolah.'),
(4,2,'A','Kelas 1-2: mulai mengenal dan meniru perilaku kewargaan dalam kegiatan sehari-hari di sekolah.'),
(5,2,'B','Kelas 3-4: membiasakan diri menerapkan kewargaan dalam kegiatan sehari-hari di sekolah.'),
(6,2,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam kewargaan dalam kegiatan sehari-hari di sekolah.'),
(7,3,'A','Kelas 1-2: mulai mengenal dan meniru perilaku penalaran kritis dalam kegiatan sehari-hari di sekolah.'),
(8,3,'B','Kelas 3-4: membiasakan diri menerapkan penalaran kritis dalam kegiatan sehari-hari di sekolah.'),
(9,3,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam penalaran kritis dalam kegiatan sehari-hari di sekolah.'),
(10,4,'A','Kelas 1-2: mulai mengenal dan meniru perilaku kreativitas dalam kegiatan sehari-hari di sekolah.'),
(11,4,'B','Kelas 3-4: membiasakan diri menerapkan kreativitas dalam kegiatan sehari-hari di sekolah.'),
(12,4,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam kreativitas dalam kegiatan sehari-hari di sekolah.'),
(13,5,'A','Kelas 1-2: mulai mengenal dan meniru perilaku kolaborasi dalam kegiatan sehari-hari di sekolah.'),
(14,5,'B','Kelas 3-4: membiasakan diri menerapkan kolaborasi dalam kegiatan sehari-hari di sekolah.'),
(15,5,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam kolaborasi dalam kegiatan sehari-hari di sekolah.'),
(16,6,'A','Kelas 1-2: mulai mengenal dan meniru perilaku kemandirian dalam kegiatan sehari-hari di sekolah.'),
(17,6,'B','Kelas 3-4: membiasakan diri menerapkan kemandirian dalam kegiatan sehari-hari di sekolah.'),
(18,6,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam kemandirian dalam kegiatan sehari-hari di sekolah.'),
(19,7,'A','Kelas 1-2: mulai mengenal dan meniru perilaku kesehatan dalam kegiatan sehari-hari di sekolah.'),
(20,7,'B','Kelas 3-4: membiasakan diri menerapkan kesehatan dalam kegiatan sehari-hari di sekolah.'),
(21,7,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam kesehatan dalam kegiatan sehari-hari di sekolah.'),
(22,8,'A','Kelas 1-2: mulai mengenal dan meniru perilaku komunikasi dalam kegiatan sehari-hari di sekolah.'),
(23,8,'B','Kelas 3-4: membiasakan diri menerapkan komunikasi dalam kegiatan sehari-hari di sekolah.'),
(24,8,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam komunikasi dalam kegiatan sehari-hari di sekolah.'),
(25,9,'A','Kelas 1-2: mulai mengenal dan meniru perilaku cinta allah dan rasul-nya dalam kegiatan sehari-hari di sekolah.'),
(26,9,'B','Kelas 3-4: membiasakan diri menerapkan cinta allah dan rasul-nya dalam kegiatan sehari-hari di sekolah.'),
(27,9,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam cinta allah dan rasul-nya dalam kegiatan sehari-hari di sekolah.'),
(28,10,'A','Kelas 1-2: mulai mengenal dan meniru perilaku cinta ilmu dalam kegiatan sehari-hari di sekolah.'),
(29,10,'B','Kelas 3-4: membiasakan diri menerapkan cinta ilmu dalam kegiatan sehari-hari di sekolah.'),
(30,10,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam cinta ilmu dalam kegiatan sehari-hari di sekolah.'),
(31,11,'A','Kelas 1-2: mulai mengenal dan meniru perilaku cinta lingkungan dalam kegiatan sehari-hari di sekolah.'),
(32,11,'B','Kelas 3-4: membiasakan diri menerapkan cinta lingkungan dalam kegiatan sehari-hari di sekolah.'),
(33,11,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam cinta lingkungan dalam kegiatan sehari-hari di sekolah.'),
(34,12,'A','Kelas 1-2: mulai mengenal dan meniru perilaku cinta diri dan sesama manusia dalam kegiatan sehari-hari di sekolah.'),
(35,12,'B','Kelas 3-4: membiasakan diri menerapkan cinta diri dan sesama manusia dalam kegiatan sehari-hari di sekolah.'),
(36,12,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam cinta diri dan sesama manusia dalam kegiatan sehari-hari di sekolah.'),
(37,13,'A','Kelas 1-2: mulai mengenal dan meniru perilaku cinta tanah air dalam kegiatan sehari-hari di sekolah.'),
(38,13,'B','Kelas 3-4: membiasakan diri menerapkan cinta tanah air dalam kegiatan sehari-hari di sekolah.'),
(39,13,'C','Kelas 5-6: konsisten menunjukkan dan mengajak teman dalam cinta tanah air dalam kegiatan sehari-hari di sekolah.');
/*!40000 ALTER TABLE `dimensi_indikator` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `dimensi_lulusan`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `dimensi_lulusan` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`kerangka` enum('PROFIL_LULUSAN','PANCA_CINTA') NOT NULL,
`kode` varchar(30) DEFAULT NULL,
`nama` varchar(80) NOT NULL,
`deskripsi` text DEFAULT NULL,
`warna` varchar(9) DEFAULT NULL,
`ikon` varchar(40) DEFAULT NULL,
`urutan` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='8 Dimensi Profil Lulusan (Permendikdasmen 10/2025) & Panca Cinta (KMA 1503/2025)';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `dimensi_lulusan`
--
LOCK TABLES `dimensi_lulusan` WRITE;
/*!40000 ALTER TABLE `dimensi_lulusan` DISABLE KEYS */;
INSERT INTO `dimensi_lulusan` VALUES
(1,'PROFIL_LULUSAN','PL1','Keimanan dan Ketakwaan kepada Tuhan YME','Keyakinan yang teguh serta pengamalan nilai spiritual dan akhlak mulia dalam keseharian.','#1F7A5A','sparkles',1),
(2,'PROFIL_LULUSAN','PL2','Kewargaan','Cinta tanah air, taat norma, dan bertanggung jawab pada lingkungan serta masyarakat.','#C0392B','flag',2),
(3,'PROFIL_LULUSAN','PL3','Penalaran Kritis','Berpikir logis, analitis, dan reflektif dalam menilai informasi dan memecahkan masalah.','#2B4570','brain',3),
(4,'PROFIL_LULUSAN','PL4','Kreativitas','Menghasilkan gagasan orisinal dan solusi baru yang bermanfaat.','#D35400','lightbulb',4),
(5,'PROFIL_LULUSAN','PL5','Kolaborasi','Bekerja sama, beradaptasi, dan bersinergi dalam kelompok.','#E67E22','users',5),
(6,'PROFIL_LULUSAN','PL6','Kemandirian','Bertanggung jawab atas proses belajar sendiri dan berinisiatif.','#8E44AD','compass',6),
(7,'PROFIL_LULUSAN','PL7','Kesehatan','Menjaga keseimbangan kesehatan fisik dan mental.','#16A085','heart-pulse',7),
(8,'PROFIL_LULUSAN','PL8','Komunikasi','Menyampaikan gagasan dan berinteraksi secara efektif dan santun.','#2980B9','message-circle',8),
(9,'PANCA_CINTA','KBC1','Cinta Allah dan Rasul-Nya','Sumber cinta: mengenal, mencintai, dan meneladani Allah serta Rasulullah SAW.','#1F7A5A','moon-star',1),
(10,'PANCA_CINTA','KBC2','Cinta Ilmu','Semangat mencari, mengamalkan, dan memuliakan ilmu sepanjang hayat.','#B7791F','book-open',2),
(11,'PANCA_CINTA','KBC3','Cinta Lingkungan','Memelihara alam sebagai amanah, hemat sumber daya, dan peduli kebersihan.','#27AE60','leaf',3),
(12,'PANCA_CINTA','KBC4','Cinta Diri dan Sesama Manusia','Akhlak pada diri sendiri, empati, toleransi, dan kasih sayang pada sesama.','#E67E22','hand-heart',4),
(13,'PANCA_CINTA','KBC5','Cinta Tanah Air','Bangga dan berkontribusi bagi bangsa, menjaga persatuan dalam keberagaman.','#C0392B','flag',5);
/*!40000 ALTER TABLE `dimensi_lulusan` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `disposisi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `disposisi` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`surat_masuk_id` int(10) unsigned NOT NULL,
`dari_user_id` int(10) unsigned DEFAULT NULL,
`ke_user_id` int(10) unsigned DEFAULT NULL,
`instruksi` text DEFAULT NULL,
`batas_waktu` date DEFAULT NULL,
`status` enum('MENUNGGU','DIBACA','DIKERJAKAN','SELESAI') DEFAULT 'MENUNGGU',
`tanggapan` text DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `surat_masuk_id` (`surat_masuk_id`),
CONSTRAINT `disposisi_ibfk_1` FOREIGN KEY (`surat_masuk_id`) REFERENCES `surat_masuk` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `disposisi`
--
LOCK TABLES `disposisi` WRITE;
/*!40000 ALTER TABLE `disposisi` DISABLE KEYS */;
/*!40000 ALTER TABLE `disposisi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ebook_progres`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ebook_progres` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`buku_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`halaman_terakhir` smallint(6) DEFAULT 1,
`total_halaman` smallint(6) DEFAULT NULL,
`persen` tinyint(4) DEFAULT 0,
`menit_total` int(11) DEFAULT 0,
`selesai` tinyint(1) DEFAULT 0,
`selesai_at` datetime DEFAULT NULL,
`bookmark` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`bookmark`)),
`terakhir_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_ep` (`buku_id`,`siswa_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ebook_progres`
--
LOCK TABLES `ebook_progres` WRITE;
/*!40000 ALTER TABLE `ebook_progres` DISABLE KEYS */;
INSERT INTO `ebook_progres` VALUES
(1,3,1,88,292,30,45,0,NULL,NULL,'2026-09-08 23:22:21'),
(2,3,2,88,292,30,45,0,NULL,NULL,'2026-09-08 23:22:21'),
(3,3,3,88,292,30,45,0,NULL,NULL,'2026-09-08 23:22:21');
/*!40000 ALTER TABLE `ebook_progres` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ekskul_siswa`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ekskul_siswa` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned DEFAULT NULL,
`ekskul_id` smallint(5) unsigned DEFAULT NULL,
`siswa_id` int(10) unsigned DEFAULT NULL,
`predikat` enum('SANGAT_BAIK','BAIK','CUKUP','KURANG') DEFAULT NULL,
`deskripsi` text DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_es` (`tahun_ajaran_id`,`ekskul_id`,`siswa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ekskul_siswa`
--
LOCK TABLES `ekskul_siswa` WRITE;
/*!40000 ALTER TABLE `ekskul_siswa` DISABLE KEYS */;
/*!40000 ALTER TABLE `ekskul_siswa` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ekstrakurikuler`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ekstrakurikuler` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`nama` varchar(100) DEFAULT NULL,
`pembina_id` int(10) unsigned DEFAULT NULL,
`jadwal` varchar(80) DEFAULT NULL,
`kuota` smallint(6) DEFAULT NULL,
`deskripsi` text DEFAULT NULL,
`is_aktif` tinyint(1) DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ekstrakurikuler`
--
LOCK TABLES `ekstrakurikuler` WRITE;
/*!40000 ALTER TABLE `ekstrakurikuler` DISABLE KEYS */;
INSERT INTO `ekstrakurikuler` VALUES
(1,'Pramuka Penggalang',1,'Jumat 14.00-16.00',60,'Kepramukaan tingkat penggalang',1),
(2,'Palang Merah Remaja',1,'Sabtu 08.00-10.00',30,'Pertolongan pertama dan kesehatan remaja',1),
(3,'Karya Ilmiah Remaja',1,'Rabu 14.30-16.00',25,'Penelitian sederhana dan lomba karya ilmiah',1),
(4,'Futsal',1,'Selasa 15.00-17.00',25,'Latihan rutin dan persiapan kompetisi',1),
(5,'Tahfidz',1,'Senin & Kamis 15.00-16.30',40,'Hafalan juz 30 dan juz 29',1),
(6,'English Club',1,'Kamis 14.30-16.00',30,'Percakapan dan lomba pidato bahasa Inggris',1);
/*!40000 ALTER TABLE `ekstrakurikuler` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `game`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `game` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(30) 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(5) unsigned DEFAULT NULL COMMENT 'null = lintas mapel',
`tingkat_min` tinyint(4) DEFAULT 1,
`tingkat_max` tinyint(4) DEFAULT 6,
`fase` enum('A','B','C','D','E','F') DEFAULT NULL,
`deskripsi` text DEFAULT NULL,
`instruksi` text DEFAULT NULL,
`thumbnail` varchar(255) DEFAULT NULL,
`warna` varchar(9) DEFAULT NULL,
`konfigurasi` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'durasi, nyawa, jumlah level dsb' CHECK (json_valid(`konfigurasi`)),
`poin_maks` smallint(6) DEFAULT 100,
`is_aktif` tinyint(1) DEFAULT 1,
`dibuat_oleh` int(10) unsigned DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `game`
--
LOCK TABLES `game` WRITE;
/*!40000 ALTER TABLE `game` DISABLE KEYS */;
INSERT INTO `game` VALUES
(1,'CEPAT_HITUNG','Adu Cepat Hitung','HITUNG_CEPAT',4,1,6,NULL,'Selesaikan operasi hitung sebanyak mungkin sebelum waktu habis.','Ketik jawaban lalu tekan Enter. Jawaban benar menambah waktu 2 detik.',NULL,'#2B4570','{\"durasi\":90,\"nyawa\":3,\"bonus_detik\":2}',100,1,NULL,'2026-09-08 23:22:21'),
(2,'SUSUN_KATA','Susun Kata Ajaib','SUSUN_KATA',3,1,6,NULL,'Susun huruf acak menjadi kata yang benar.','Seret huruf ke kotak jawaban. Gunakan petunjuk bila kesulitan.',NULL,'#E67E22','{\"durasi\":120,\"petunjuk_maks\":3}',100,1,NULL,'2026-09-08 23:22:21'),
(3,'JODOH_KATA','Pasangkan Aku','MENJODOHKAN',NULL,1,6,NULL,'Pasangkan istilah dengan artinya.','Tarik garis dari kiri ke kanan.',NULL,'#16A085','{\"durasi\":150}',100,1,NULL,'2026-09-08 23:22:21'),
(4,'TEBAK_GAMBAR','Tebak Gambarnya','TEBAK_GAMBAR',5,3,6,NULL,'Tebak nama benda, hewan, atau tempat dari gambar.','Ketik jawaban pada kolom yang tersedia.',NULL,'#27AE60','{\"durasi\":120,\"petunjuk_maks\":2}',100,1,NULL,'2026-09-08 23:22:21'),
(5,'KUIS_KILAT','Kuis Kilat','KUIS_CEPAT',NULL,1,6,NULL,'Jawab pertanyaan pilihan ganda secepat mungkin.','Pilih jawaban sebelum bar waktu habis.',NULL,'#8E44AD','{\"detik_per_soal\":15}',100,1,NULL,'2026-09-08 23:22:21'),
(6,'MEMORI_HIJAIYAH','Memori Hijaiyah','MEMORI',10,1,3,NULL,'Cocokkan kartu huruf hijaiyah dengan bunyinya.','Buka dua kartu. Jika cocok, kartu tetap terbuka.',NULL,'#1F4A3C','{\"pasangan\":8,\"durasi\":180}',100,1,NULL,'2026-09-08 23:22:21'),
(7,'PANCASILA_JODOH','Sila dan Lambangnya','MENJODOHKAN',2,1,6,NULL,'Pasangkan bunyi sila Pancasila dengan lambangnya.','Pilih pasangan yang tepat untuk tiap sila.',NULL,'#C0563F','{\"durasi\":150}',100,1,NULL,'2026-09-08 23:22:21'),
(8,'IPAS_TEBAK','Tebak Makhluk dan Benda','TEBAK_GAMBAR',5,3,6,NULL,'Tebak nama makhluk hidup, benda, atau gejala alam dari petunjuknya.','Baca petunjuk lalu ketik jawabanmu.',NULL,'#2E9E6B','{\"durasi\":120}',100,1,NULL,'2026-09-08 23:22:21'),
(9,'BING_MEMORI','Word Match','MEMORI',8,1,6,NULL,'Cocokkan kata bahasa Inggris dengan artinya.','Buka dua kartu. Jika cocok, kartu tetap terbuka.',NULL,'#2B4570','{\"pasangan\":8,\"durasi\":180}',100,1,NULL,'2026-09-08 23:22:21'),
(10,'BIN_SUSUN','Susun Kalimat','PUZZLE',3,2,6,NULL,'Susun potongan kata menjadi kalimat yang runtut.','Ketuk potongan kata sesuai urutan yang benar.',NULL,'#E67E22','{\"durasi\":150}',100,1,NULL,'2026-09-08 23:22:21'),
(11,'SBDP_JODOH','Alat Musik dan Asalnya','MENJODOHKAN',7,3,6,NULL,'Pasangkan alat musik tradisional dengan daerah asalnya.','Pilih daerah asal yang tepat.',NULL,'#8E44AD','{\"durasi\":150}',100,1,NULL,'2026-09-08 23:22:21'),
(12,'PJOK_KUIS','Sehat dan Bugar','KUIS_CEPAT',6,1,6,NULL,'Kuis seputar olahraga, kebugaran, dan hidup sehat.','Pilih jawaban yang benar.',NULL,'#16A085','{\"detik_per_soal\":20}',100,1,NULL,'2026-09-08 23:22:21'),
(13,'BSUN_JODOH','Basa Sunda Sapopoé','MENJODOHKAN',9,1,6,NULL,'Pasangkan kata bahasa Sunda dengan artinya dalam bahasa Indonesia.','Pilih arti yang tepat.',NULL,'#D4A017','{\"durasi\":150}',100,1,NULL,'2026-09-08 23:22:21'),
(14,'PAI_KUIS','Kuis Akhlak Mulia','KUIS_CEPAT',1,1,6,NULL,'Kuis tentang akhlak, ibadah, dan kisah teladan.','Pilih jawaban yang benar.',NULL,'#1F4A3C','{\"detik_per_soal\":20}',100,1,NULL,'2026-09-08 23:22:21'),
(15,'QH_SUSUN','Susun Ayat Pendek','PUZZLE',11,3,6,NULL,'Susun potongan lafal surah pendek sesuai urutannya.','Ketuk potongan sesuai urutan ayat.',NULL,'#16362B','{\"durasi\":180}',100,1,NULL,'2026-09-08 23:22:21'),
(16,'AA_KELOMPOK','Terpuji atau Tercela','DRAG_DROP',12,1,6,NULL,'Kelompokkan perbuatan ke dalam akhlak terpuji atau tercela.','Pilih kelompok yang tepat untuk tiap perbuatan.',NULL,'#1F4A3C','{\"durasi\":150}',100,1,NULL,'2026-09-08 23:22:21'),
(17,'FIK_KUIS','Kuis Fikih Sehari-hari','KUIS_CEPAT',13,3,6,NULL,'Kuis tentang tata cara ibadah sehari-hari.','Pilih jawaban yang benar.',NULL,'#2E9E6B','{\"detik_per_soal\":25}',100,1,NULL,'2026-09-08 23:22:21'),
(18,'SKI_JODOH','Tokoh dan Peristiwa','MENJODOHKAN',14,4,6,NULL,'Pasangkan tokoh dengan peristiwa atau perannya.','Pilih pasangan yang tepat.',NULL,'#8A6212','{\"durasi\":150}',100,1,NULL,'2026-09-08 23:22:21'),
(19,'BAR_MEMORI','Mufradat Harian','MEMORI',15,1,6,NULL,'Cocokkan kosakata bahasa Arab dengan artinya.','Buka dua kartu. Jika cocok, kartu tetap terbuka.',NULL,'#C0563F','{\"pasangan\":8,\"durasi\":180}',100,1,NULL,'2026-09-08 23:22:21');
/*!40000 ALTER TABLE `game` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `game_level`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `game_level` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`game_id` int(10) unsigned NOT NULL,
`level` smallint(6) DEFAULT 1,
`judul` varchar(120) DEFAULT NULL,
`tingkat` tinyint(4) DEFAULT NULL,
`data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'soal / pasangan / kata sesuai tipe game' CHECK (json_valid(`data`)),
`durasi_detik` smallint(6) DEFAULT 120,
`poin` smallint(6) DEFAULT 10,
`sumber_ai` tinyint(1) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `game_id` (`game_id`),
CONSTRAINT `game_level_ibfk_1` FOREIGN KEY (`game_id`) REFERENCES `game` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `game_level`
--
LOCK TABLES `game_level` WRITE;
/*!40000 ALTER TABLE `game_level` DISABLE KEYS */;
INSERT INTO `game_level` VALUES
(1,1,1,'Penjumlahan & Pengurangan',1,'[{\"soal\":\"5 + 3\",\"jawaban\":8},{\"soal\":\"9 - 4\",\"jawaban\":5},{\"soal\":\"7 + 6\",\"jawaban\":13},\n {\"soal\":\"12 - 5\",\"jawaban\":7},{\"soal\":\"8 + 8\",\"jawaban\":16},{\"soal\":\"15 - 9\",\"jawaban\":6},\n {\"soal\":\"14 + 5\",\"jawaban\":19},{\"soal\":\"20 - 12\",\"jawaban\":8}]',90,10,0),
(2,1,2,'Perkalian & Pembagian',4,'[{\"soal\":\"7 x 8\",\"jawaban\":56},{\"soal\":\"9 x 6\",\"jawaban\":54},{\"soal\":\"72 : 8\",\"jawaban\":9},\n {\"soal\":\"12 x 5\",\"jawaban\":60},{\"soal\":\"81 : 9\",\"jawaban\":9},{\"soal\":\"11 x 7\",\"jawaban\":77},\n {\"soal\":\"144 : 12\",\"jawaban\":12},{\"soal\":\"25 x 4\",\"jawaban\":100}]',90,15,0),
(3,2,1,'Kata Benda di Sekitar',2,'[{\"kata\":\"SEKOLAH\",\"petunjuk\":\"Tempat kita belajar setiap hari\"},\n {\"kata\":\"PERPUSTAKAAN\",\"petunjuk\":\"Tempat meminjam buku\"},\n {\"kata\":\"SEPEDA\",\"petunjuk\":\"Kendaraan roda dua tanpa mesin\"},\n {\"kata\":\"PELANGI\",\"petunjuk\":\"Muncul di langit setelah hujan\"},\n {\"kata\":\"GURU\",\"petunjuk\":\"Orang yang mengajar di kelas\"},\n {\"kata\":\"KEBUN\",\"petunjuk\":\"Tempat menanam sayur dan bunga\"}]',120,10,0),
(4,10,1,'Susun Kalimat Sederhana',2,'[{\"petunjuk\":\"Susun menjadi kalimat yang benar\",\"urutan\":[\"Adik\",\"membaca\",\"buku\",\"cerita\"]},\n {\"petunjuk\":\"Susun menjadi kalimat yang benar\",\"urutan\":[\"Ibu\",\"memasak\",\"nasi\",\"goreng\"]},\n {\"petunjuk\":\"Susun menjadi kalimat yang benar\",\"urutan\":[\"Kami\",\"belajar\",\"di\",\"perpustakaan\"]},\n {\"petunjuk\":\"Susun menjadi kalimat yang benar\",\"urutan\":[\"Burung\",\"itu\",\"terbang\",\"tinggi\"]}]',150,15,0),
(5,7,1,'Lambang Sila Pancasila',3,'[{\"kiri\":\"Sila ke-1 Ketuhanan Yang Maha Esa\",\"kanan\":\"Bintang\"},\n {\"kiri\":\"Sila ke-2 Kemanusiaan yang Adil dan Beradab\",\"kanan\":\"Rantai\"},\n {\"kiri\":\"Sila ke-3 Persatuan Indonesia\",\"kanan\":\"Pohon Beringin\"},\n {\"kiri\":\"Sila ke-4 Kerakyatan yang Dipimpin oleh Hikmat Kebijaksanaan\",\"kanan\":\"Kepala Banteng\"},\n {\"kiri\":\"Sila ke-5 Keadilan Sosial bagi Seluruh Rakyat Indonesia\",\"kanan\":\"Padi dan Kapas\"}]',150,15,0),
(6,8,1,'Makhluk Hidup dan Alam',4,'[{\"petunjuk\":\"Hewan berkantung yang melompat, berasal dari Australia\",\"jawaban\":\"KANGURU\"},\n {\"petunjuk\":\"Proses tumbuhan membuat makanan dengan bantuan cahaya matahari\",\"jawaban\":\"FOTOSINTESIS\"},\n {\"petunjuk\":\"Perubahan air menjadi uap karena panas\",\"jawaban\":\"PENGUAPAN\"},\n {\"petunjuk\":\"Bagian tumbuhan yang menyerap air dari tanah\",\"jawaban\":\"AKAR\"},\n {\"petunjuk\":\"Planet tempat kita tinggal\",\"jawaban\":\"BUMI\"},\n {\"petunjuk\":\"Hewan yang mengalami metamorfosis dari ulat\",\"jawaban\":\"KUPU-KUPU\"}]',120,15,0),
(7,9,1,'Everyday Words',3,'[{\"kiri\":\"Book\",\"kanan\":\"Buku\"},{\"kiri\":\"Table\",\"kanan\":\"Meja\"},\n {\"kiri\":\"Window\",\"kanan\":\"Jendela\"},{\"kiri\":\"Teacher\",\"kanan\":\"Guru\"},\n {\"kiri\":\"Bag\",\"kanan\":\"Tas\"},{\"kiri\":\"Chair\",\"kanan\":\"Kursi\"},\n {\"kiri\":\"Water\",\"kanan\":\"Air\"},{\"kiri\":\"Friend\",\"kanan\":\"Teman\"}]',180,15,0),
(8,11,1,'Alat Musik Tradisional',4,'[{\"kiri\":\"Angklung\",\"kanan\":\"Jawa Barat\"},{\"kiri\":\"Gamelan\",\"kanan\":\"Jawa Tengah\"},\n {\"kiri\":\"Sasando\",\"kanan\":\"Nusa Tenggara Timur\"},{\"kiri\":\"Tifa\",\"kanan\":\"Papua\"},\n {\"kiri\":\"Kolintang\",\"kanan\":\"Sulawesi Utara\"},{\"kiri\":\"Saluang\",\"kanan\":\"Sumatera Barat\"}]',150,15,0),
(9,12,1,'Hidup Sehat',3,'[{\"tanya\":\"Berapa lama sebaiknya anak tidur setiap malam?\",\"opsi\":[\"4-5 jam\",\"6-7 jam\",\"9-11 jam\",\"12-14 jam\"],\"kunci\":2},\n {\"tanya\":\"Sebelum berolahraga, kita sebaiknya melakukan?\",\"opsi\":[\"Makan banyak\",\"Pemanasan\",\"Tidur\",\"Mandi\"],\"kunci\":1},\n {\"tanya\":\"Cabang olahraga yang dimainkan dengan raket dan kok adalah?\",\"opsi\":[\"Tenis meja\",\"Bulu tangkis\",\"Voli\",\"Basket\"],\"kunci\":1},\n {\"tanya\":\"Minuman terbaik saat haus setelah berolahraga adalah?\",\"opsi\":[\"Air putih\",\"Kopi\",\"Minuman bersoda\",\"Teh manis kental\"],\"kunci\":0},\n {\"tanya\":\"Cuci tangan sebaiknya menggunakan?\",\"opsi\":[\"Air saja\",\"Air dan sabun\",\"Tisu kering\",\"Lap kain\"],\"kunci\":1}]',100,10,0),
(10,13,1,'Kecap Sapopoé',3,'[{\"kiri\":\"Dahar\",\"kanan\":\"Makan\"},{\"kiri\":\"Nginum\",\"kanan\":\"Minum\"},\n {\"kiri\":\"Sare\",\"kanan\":\"Tidur\"},{\"kiri\":\"Leumpang\",\"kanan\":\"Berjalan\"},\n {\"kiri\":\"Maca\",\"kanan\":\"Membaca\"},{\"kiri\":\"Nulis\",\"kanan\":\"Menulis\"}]',150,15,0),
(11,14,1,'Akhlak dan Ibadah',3,'[{\"tanya\":\"Berapa jumlah rakaat salat Magrib?\",\"opsi\":[\"2 rakaat\",\"3 rakaat\",\"4 rakaat\",\"5 rakaat\"],\"kunci\":1},\n {\"tanya\":\"Bacaan yang diucapkan saat memulai pekerjaan adalah?\",\"opsi\":[\"Hamdalah\",\"Basmalah\",\"Takbir\",\"Salam\"],\"kunci\":1},\n {\"tanya\":\"Sikap yang benar ketika teman sedang berbicara adalah?\",\"opsi\":[\"Memotong pembicaraan\",\"Mendengarkan\",\"Bermain sendiri\",\"Pergi\"],\"kunci\":1},\n {\"tanya\":\"Nabi yang dikenal sabar menghadapi ujian adalah?\",\"opsi\":[\"Nabi Ayyub\",\"Nabi Nuh\",\"Nabi Musa\",\"Nabi Yusuf\"],\"kunci\":0},\n {\"tanya\":\"Berbuat baik kepada orang tua disebut?\",\"opsi\":[\"Birrul walidain\",\"Silaturahmi\",\"Ukhuwah\",\"Tawakal\"],\"kunci\":0}]',100,10,0),
(12,15,1,'Urutan Surah Al-Fatihah',4,'[{\"petunjuk\":\"Susun potongan Surah Al-Fatihah ayat 1-3 sesuai urutan\",\"urutan\":[\"Bismillahirrahmanirrahim\",\"Alhamdulillahi rabbil alamin\",\"Ar-Rahmanir-Rahim\"]},\n {\"petunjuk\":\"Susun rukun Islam sesuai urutannya\",\"urutan\":[\"Syahadat\",\"Salat\",\"Zakat\",\"Puasa\",\"Haji\"]}]',180,15,0),
(13,16,1,'Terpuji atau Tercela',3,'[{\"benda\":\"Jujur saat ujian\",\"kelompok\":\"Akhlak Terpuji\"},\n {\"benda\":\"Menolong teman yang jatuh\",\"kelompok\":\"Akhlak Terpuji\"},\n {\"benda\":\"Berkata kasar kepada teman\",\"kelompok\":\"Akhlak Tercela\"},\n {\"benda\":\"Membuang sampah sembarangan\",\"kelompok\":\"Akhlak Tercela\"},\n {\"benda\":\"Mengucapkan terima kasih\",\"kelompok\":\"Akhlak Terpuji\"},\n {\"benda\":\"Mengambil barang tanpa izin\",\"kelompok\":\"Akhlak Tercela\"}]',150,15,0),
(14,17,1,'Tata Cara Ibadah',4,'[{\"tanya\":\"Bersuci dari hadas kecil dilakukan dengan?\",\"opsi\":[\"Mandi wajib\",\"Wudu\",\"Tayamum saja\",\"Mencuci tangan\"],\"kunci\":1},\n {\"tanya\":\"Anggota tubuh yang pertama dibasuh saat wudu adalah?\",\"opsi\":[\"Wajah\",\"Kedua tangan\",\"Kepala\",\"Kaki\"],\"kunci\":1},\n {\"tanya\":\"Salat yang jumlah rakaatnya paling banyak adalah?\",\"opsi\":[\"Subuh\",\"Zuhur\",\"Magrib\",\"Isya\"],\"kunci\":1},\n {\"tanya\":\"Jika tidak ada air, bersuci dapat dilakukan dengan?\",\"opsi\":[\"Tayamum\",\"Menunda salat\",\"Berkumur\",\"Tidak salat\"],\"kunci\":0}]',120,15,0),
(15,18,1,'Tokoh dan Peristiwa',5,'[{\"kiri\":\"Nabi Muhammad SAW\",\"kanan\":\"Menerima wahyu pertama di Gua Hira\"},\n {\"kiri\":\"Abu Bakar\",\"kanan\":\"Khalifah pertama\"},\n {\"kiri\":\"Umar bin Khattab\",\"kanan\":\"Menetapkan kalender Hijriah\"},\n {\"kiri\":\"Utsman bin Affan\",\"kanan\":\"Membukukan mushaf Al-Qur an\"},\n {\"kiri\":\"Ali bin Abi Thalib\",\"kanan\":\"Khalifah keempat\"}]',150,15,0),
(16,19,1,'Mufradat Sekolah',3,'[{\"kiri\":\"مَدْرَسَة\",\"kanan\":\"Sekolah\"},{\"kiri\":\"كِتَاب\",\"kanan\":\"Buku\"},\n {\"kiri\":\"قَلَم\",\"kanan\":\"Pena\"},{\"kiri\":\"مُعَلِّم\",\"kanan\":\"Guru\"},\n {\"kiri\":\"بَيْت\",\"kanan\":\"Rumah\"},{\"kiri\":\"مَاء\",\"kanan\":\"Air\"},\n {\"kiri\":\"بَاب\",\"kanan\":\"Pintu\"},{\"kiri\":\"صَدِيق\",\"kanan\":\"Teman\"}]',180,15,0),
(17,6,1,'Huruf Hijaiyah',1,'[{\"kiri\":\"ا\",\"kanan\":\"Alif\"},{\"kiri\":\"ب\",\"kanan\":\"Ba\"},\n {\"kiri\":\"ت\",\"kanan\":\"Ta\"},{\"kiri\":\"ث\",\"kanan\":\"Tsa\"},\n {\"kiri\":\"ج\",\"kanan\":\"Jim\"},{\"kiri\":\"ح\",\"kanan\":\"Ha\"},\n {\"kiri\":\"خ\",\"kanan\":\"Kha\"},{\"kiri\":\"د\",\"kanan\":\"Dal\"}]',180,10,0),
(18,5,1,'Pengetahuan Umum',4,'[{\"tanya\":\"Ibu kota Indonesia adalah?\",\"opsi\":[\"Bandung\",\"Jakarta\",\"Surabaya\",\"Medan\"],\"kunci\":1},\n {\"tanya\":\"Alat untuk mengukur suhu badan adalah?\",\"opsi\":[\"Termometer\",\"Barometer\",\"Timbangan\",\"Penggaris\"],\"kunci\":0},\n {\"tanya\":\"Hewan yang bernapas dengan insang adalah?\",\"opsi\":[\"Ayam\",\"Ikan\",\"Kucing\",\"Burung\"],\"kunci\":1},\n {\"tanya\":\"Warna bendera Indonesia adalah?\",\"opsi\":[\"Merah putih\",\"Merah biru\",\"Putih hijau\",\"Merah kuning\"],\"kunci\":0},\n {\"tanya\":\"Satu tahun terdiri atas berapa bulan?\",\"opsi\":[\"10\",\"11\",\"12\",\"13\"],\"kunci\":2},\n {\"tanya\":\"Matahari terbit dari arah?\",\"opsi\":[\"Barat\",\"Timur\",\"Utara\",\"Selatan\"],\"kunci\":1}]',120,10,0),
(19,3,1,'Istilah dan Artinya',4,'[{\"kiri\":\"Herbivora\",\"kanan\":\"Pemakan tumbuhan\"},\n {\"kiri\":\"Karnivora\",\"kanan\":\"Pemakan daging\"},\n {\"kiri\":\"Omnivora\",\"kanan\":\"Pemakan segala\"},\n {\"kiri\":\"Ekosistem\",\"kanan\":\"Hubungan makhluk hidup dan lingkungannya\"},\n {\"kiri\":\"Habitat\",\"kanan\":\"Tempat tinggal makhluk hidup\"}]',150,15,0),
(20,4,1,'Tebak Benda dan Tempat',3,'[{\"petunjuk\":\"Benda bulat yang ditendang saat bermain sepak bola\",\"jawaban\":\"BOLA\"},\n {\"petunjuk\":\"Tempat kita berobat ketika sakit\",\"jawaban\":\"RUMAH SAKIT\"},\n {\"petunjuk\":\"Alat untuk menulis di papan tulis\",\"jawaban\":\"SPIDOL\"},\n {\"petunjuk\":\"Kendaraan yang berjalan di atas rel\",\"jawaban\":\"KERETA\"},\n {\"petunjuk\":\"Tempat menyimpan buku di kelas\",\"jawaban\":\"RAK\"}]',120,10,0);
/*!40000 ALTER TABLE `game_level` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `game_skor`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `game_skor` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`game_id` int(10) unsigned NOT NULL,
`level_id` int(10) unsigned DEFAULT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`skor` smallint(6) DEFAULT 0,
`bintang` tinyint(4) DEFAULT 0,
`benar` smallint(6) DEFAULT NULL,
`salah` smallint(6) DEFAULT NULL,
`durasi_detik` smallint(6) DEFAULT NULL,
`selesai` tinyint(1) DEFAULT 0,
`dimainkan_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `siswa_id` (`siswa_id`,`game_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `game_skor`
--
LOCK TABLES `game_skor` WRITE;
/*!40000 ALTER TABLE `game_skor` DISABLE KEYS */;
/*!40000 ALTER TABLE `game_skor` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `guru`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `guru` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nuptk` varchar(20) DEFAULT NULL,
`nip` varchar(30) DEFAULT NULL,
`nik` varchar(20) DEFAULT NULL,
`kode_guru` varchar(15) DEFAULT NULL,
`nama` varchar(120) NOT NULL,
`gelar_depan` varchar(20) DEFAULT NULL,
`gelar_belakang` varchar(30) DEFAULT NULL,
`jenis_kelamin` enum('L','P') DEFAULT NULL,
`tempat_lahir` varchar(60) DEFAULT NULL,
`tanggal_lahir` date DEFAULT NULL,
`agama` varchar(20) DEFAULT NULL,
`alamat` text DEFAULT NULL,
`no_hp` varchar(20) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`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) DEFAULT NULL,
`jurusan` varchar(80) DEFAULT NULL,
`tmt_tugas` date DEFAULT NULL,
`foto` varchar(255) DEFAULT NULL,
`ttd` varchar(255) DEFAULT NULL,
`rfid_uid` varchar(32) DEFAULT NULL,
`status` enum('AKTIF','NONAKTIF','PINDAH','PENSIUN') DEFAULT 'AKTIF',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `kode_guru` (`kode_guru`),
UNIQUE KEY `rfid_uid` (`rfid_uid`),
KEY `nama` (`nama`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `guru`
--
LOCK TABLES `guru` WRITE;
/*!40000 ALTER TABLE `guru` DISABLE KEYS */;
INSERT INTO `guru` VALUES
(1,'1234567890123456',NULL,NULL,'G001','Ahmad Fauzi',NULL,'S.Pd.','L',NULL,NULL,NULL,NULL,'081200000001',NULL,'GTY','KEPALA_SEKOLAH',NULL,NULL,NULL,NULL,NULL,'A1B2C301','AKTIF','2026-09-08 23:22:21'),
(2,'1234567890123457',NULL,NULL,'G002','Siti Nurhaliza',NULL,'S.Pd.','P',NULL,NULL,NULL,NULL,'081200000002',NULL,'GTY','GURU_KELAS',NULL,NULL,NULL,NULL,NULL,'A1B2C302','AKTIF','2026-09-08 23:22:21'),
(3,'1234567890123458',NULL,NULL,'G003','Budi Santoso',NULL,'S.Pd.I.','L',NULL,NULL,NULL,NULL,'081200000003',NULL,'GTT','GURU_MAPEL',NULL,NULL,NULL,NULL,NULL,'A1B2C303','AKTIF','2026-09-08 23:22:21'),
(4,NULL,NULL,NULL,'G004','Rina Marlina',NULL,'A.Md.','P',NULL,NULL,NULL,NULL,'081200000004',NULL,'GTT','TU',NULL,NULL,NULL,NULL,NULL,'A1B2C304','AKTIF','2026-09-08 23:22:21'),
(5,NULL,NULL,NULL,'G005','Dedi Kurniawan',NULL,'S.IP.','L',NULL,NULL,NULL,NULL,'081200000005',NULL,'GTT','PUSTAKAWAN',NULL,NULL,NULL,NULL,NULL,'A1B2C305','AKTIF','2026-09-08 23:22:21');
/*!40000 ALTER TABLE `guru` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `guru_mapel`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `guru_mapel` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_gmk` (`tahun_ajaran_id`,`guru_id`,`mapel_id`,`kelas_id`),
KEY `guru_id` (`guru_id`),
KEY `mapel_id` (`mapel_id`),
KEY `kelas_id` (`kelas_id`),
CONSTRAINT `guru_mapel_ibfk_1` FOREIGN KEY (`guru_id`) REFERENCES `guru` (`id`) ON DELETE CASCADE,
CONSTRAINT `guru_mapel_ibfk_2` FOREIGN KEY (`mapel_id`) REFERENCES `mapel` (`id`) ON DELETE CASCADE,
CONSTRAINT `guru_mapel_ibfk_3` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=221 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `guru_mapel`
--
LOCK TABLES `guru_mapel` WRITE;
/*!40000 ALTER TABLE `guru_mapel` DISABLE KEYS */;
INSERT INTO `guru_mapel` VALUES
(95,1,2,16,1),
(96,1,2,16,4),
(97,1,2,17,1),
(98,1,2,17,4),
(99,1,2,18,1),
(100,1,2,18,4),
(101,1,2,19,1),
(102,1,2,19,4),
(103,1,2,20,1),
(104,1,2,20,4),
(105,1,2,21,1),
(106,1,2,21,4),
(107,1,2,22,1),
(108,1,2,22,4),
(109,1,2,23,1),
(110,1,2,23,4),
(111,1,2,24,1),
(112,1,2,24,4),
(113,1,2,25,1),
(114,1,2,25,4),
(115,1,2,26,1),
(116,1,2,26,4),
(117,1,2,27,1),
(118,1,2,27,4),
(119,1,2,28,1),
(120,1,2,28,4),
(121,1,2,29,1),
(122,1,2,29,4),
(123,1,2,30,1),
(124,1,2,30,4),
(125,1,2,31,1),
(126,1,2,31,4),
(127,1,2,32,1),
(128,1,2,32,4),
(161,1,3,16,1),
(173,1,3,16,2),
(185,1,3,16,3),
(167,1,3,16,4),
(179,1,3,16,5),
(191,1,3,16,6),
(162,1,3,27,1),
(174,1,3,27,2),
(186,1,3,27,3),
(168,1,3,27,4),
(180,1,3,27,5),
(192,1,3,27,6),
(158,1,3,28,1),
(170,1,3,28,2),
(182,1,3,28,3),
(164,1,3,28,4),
(176,1,3,28,5),
(188,1,3,28,6),
(160,1,3,29,1),
(172,1,3,29,2),
(184,1,3,29,3),
(166,1,3,29,4),
(178,1,3,29,5),
(190,1,3,29,6),
(163,1,3,30,1),
(175,1,3,30,2),
(187,1,3,30,3),
(169,1,3,30,4),
(181,1,3,30,5),
(193,1,3,30,6),
(159,1,3,31,1),
(171,1,3,31,2),
(183,1,3,31,3),
(165,1,3,31,4),
(177,1,3,31,5),
(189,1,3,31,6);
/*!40000 ALTER TABLE `guru_mapel` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `hadiah`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `hadiah` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nama` varchar(120) NOT NULL,
`deskripsi` varchar(255) DEFAULT NULL,
`poin` int(10) unsigned NOT NULL DEFAULT 100,
`stok` int(11) NOT NULL DEFAULT 0 COMMENT 'negatif berarti tidak dibatasi',
`gambar` varchar(255) DEFAULT NULL,
`kategori` varchar(40) DEFAULT NULL,
`is_aktif` tinyint(1) NOT NULL DEFAULT 1,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `hadiah`
--
LOCK TABLES `hadiah` WRITE;
/*!40000 ALTER TABLE `hadiah` DISABLE KEYS */;
INSERT INTO `hadiah` VALUES
(1,'Tambahan Nilai Bahasa Indonesia','Tambahan 5 poin nilai harian, diverifikasi guru mapel.',300,50,NULL,'AKADEMIK',1,'2026-09-08 23:22:21'),
(2,'Buku Bacaan Milik Sendiri','Pilih satu buku dari rak hadiah perpustakaan.',500,10,NULL,'BARANG',1,'2026-09-08 23:22:21'),
(3,'Sertifikat Pembaca Terbaik','Sertifikat cetak bertanda tangan kepala sekolah.',400,25,NULL,'PENGHARGAAN',1,'2026-09-08 23:22:21'),
(4,'Voucher Kantin Rp15.000','Ditukar di kantin sekolah pada jam istirahat.',250,30,NULL,'BARANG',1,'2026-09-08 23:22:21'),
(5,'Bebas Tugas Meringkas','Sekali kesempatan bebas dari tugas meringkas.',350,20,NULL,'AKADEMIK',1,'2026-09-08 23:22:21'),
(6,'Pinjam Buku Dua Kali Lipat','Boleh meminjam empat buku sekaligus selama sebulan.',200,-1,NULL,'FASILITAS',1,'2026-09-08 23:22:21');
/*!40000 ALTER TABLE `hadiah` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `hadiah_klaim`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `hadiah_klaim` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`siswa_id` int(10) unsigned NOT NULL,
`hadiah_id` int(10) unsigned NOT NULL,
`kode` varchar(16) NOT NULL,
`poin_terpakai` int(10) unsigned NOT NULL,
`status` enum('MENUNGGU','DISETUJUI','DITOLAK','DIAMBIL') NOT NULL DEFAULT 'MENUNGGU',
`catatan_guru` varchar(255) DEFAULT NULL,
`diverifikasi_oleh` int(10) unsigned DEFAULT NULL,
`diverifikasi_at` datetime DEFAULT NULL,
`diambil_at` datetime DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_kode_klaim` (`kode`),
KEY `idx_klaim` (`siswa_id`,`status`),
KEY `fk_hk_hadiah` (`hadiah_id`),
CONSTRAINT `fk_hk_hadiah` FOREIGN KEY (`hadiah_id`) REFERENCES `hadiah` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_hk_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `hadiah_klaim`
--
LOCK TABLES `hadiah_klaim` WRITE;
/*!40000 ALTER TABLE `hadiah_klaim` DISABLE KEYS */;
/*!40000 ALTER TABLE `hadiah_klaim` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `hari_libur`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `hari_libur` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tanggal` date NOT NULL,
`tanggal_selesai` date DEFAULT NULL,
`keterangan` varchar(150) DEFAULT NULL,
`jenis` enum('NASIONAL','SEKOLAH','CUTI_BERSAMA','LIBUR_SEMESTER') DEFAULT 'SEKOLAH',
PRIMARY KEY (`id`),
KEY `tanggal` (`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `hari_libur`
--
LOCK TABLES `hari_libur` WRITE;
/*!40000 ALTER TABLE `hari_libur` DISABLE KEYS */;
/*!40000 ALTER TABLE `hari_libur` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `industri`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `industri` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nama` varchar(150) NOT NULL,
`bidang` varchar(120) DEFAULT NULL,
`alamat` varchar(255) DEFAULT NULL,
`kota` varchar(80) DEFAULT NULL,
`telepon` varchar(30) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`narahubung` varchar(120) DEFAULT NULL,
`jabatan_narahubung` varchar(80) DEFAULT NULL,
`kuota` smallint(6) NOT NULL DEFAULT 0,
`no_mou` varchar(60) DEFAULT NULL,
`mou_mulai` date DEFAULT NULL,
`mou_selesai` date DEFAULT NULL,
`catatan` text DEFAULT NULL,
`is_aktif` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `industri`
--
LOCK TABLES `industri` WRITE;
/*!40000 ALTER TABLE `industri` DISABLE KEYS */;
/*!40000 ALTER TABLE `industri` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `inventaris`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `inventaris` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`kode_barang` varchar(40) DEFAULT NULL,
`nama` varchar(150) DEFAULT NULL,
`kategori_id` smallint(5) unsigned DEFAULT NULL,
`merk` varchar(80) DEFAULT NULL,
`tipe` varchar(80) DEFAULT NULL,
`tahun_perolehan` year(4) DEFAULT NULL,
`sumber_dana` varchar(60) DEFAULT NULL,
`harga_satuan` decimal(14,2) DEFAULT NULL,
`jumlah` smallint(6) DEFAULT 1,
`satuan` varchar(20) DEFAULT 'unit',
`kondisi_baik` smallint(6) DEFAULT 0,
`kondisi_rusak_ringan` smallint(6) DEFAULT 0,
`kondisi_rusak_berat` smallint(6) DEFAULT 0,
`lokasi` varchar(100) DEFAULT NULL,
`penanggung_jawab` int(10) unsigned DEFAULT NULL,
`foto` varchar(255) DEFAULT NULL,
`qrcode` varchar(60) DEFAULT NULL,
`keterangan` text DEFAULT NULL,
`status` enum('AKTIF','DIHAPUS','DIPINJAM') DEFAULT 'AKTIF',
PRIMARY KEY (`id`),
UNIQUE KEY `kode_barang` (`kode_barang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `inventaris`
--
LOCK TABLES `inventaris` WRITE;
/*!40000 ALTER TABLE `inventaris` DISABLE KEYS */;
/*!40000 ALTER TABLE `inventaris` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `inventaris_kategori`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `inventaris_kategori` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(15) DEFAULT NULL,
`nama` varchar(80) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `inventaris_kategori`
--
LOCK TABLES `inventaris_kategori` WRITE;
/*!40000 ALTER TABLE `inventaris_kategori` DISABLE KEYS */;
/*!40000 ALTER TABLE `inventaris_kategori` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `inventaris_mutasi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `inventaris_mutasi` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`inventaris_id` int(10) unsigned NOT NULL,
`jenis` enum('MASUK','KELUAR','PINJAM','KEMBALI','PERBAIKAN','PENGHAPUSAN') NOT NULL,
`jumlah` smallint(6) DEFAULT NULL,
`tanggal` date DEFAULT NULL,
`dari` varchar(100) DEFAULT NULL,
`ke` varchar(100) DEFAULT NULL,
`keterangan` text DEFAULT NULL,
`petugas_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `inventaris_id` (`inventaris_id`),
CONSTRAINT `inventaris_mutasi_ibfk_1` FOREIGN KEY (`inventaris_id`) REFERENCES `inventaris` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `inventaris_mutasi`
--
LOCK TABLES `inventaris_mutasi` WRITE;
/*!40000 ALTER TABLE `inventaris_mutasi` DISABLE KEYS */;
/*!40000 ALTER TABLE `inventaris_mutasi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `jadwal`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `jadwal` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`hari` tinyint(4) NOT NULL COMMENT '1=Senin .. 7=Minggu',
`jam_mulai_id` tinyint(3) unsigned DEFAULT NULL,
`jam_selesai_id` tinyint(3) unsigned DEFAULT NULL,
`ruang` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `kelas_id` (`kelas_id`),
KEY `mapel_id` (`mapel_id`),
KEY `guru_id` (`guru_id`),
KEY `hari` (`hari`),
CONSTRAINT `jadwal_ibfk_1` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE,
CONSTRAINT `jadwal_ibfk_2` FOREIGN KEY (`mapel_id`) REFERENCES `mapel` (`id`),
CONSTRAINT `jadwal_ibfk_3` FOREIGN KEY (`guru_id`) REFERENCES `guru` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `jadwal`
--
LOCK TABLES `jadwal` WRITE;
/*!40000 ALTER TABLE `jadwal` DISABLE KEYS */;
/*!40000 ALTER TABLE `jadwal` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `jam_pelajaran`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `jam_pelajaran` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`jam_ke` tinyint(4) NOT NULL,
`mulai` time NOT NULL,
`selesai` time NOT NULL,
`is_istirahat` tinyint(1) DEFAULT 0,
`keterangan` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `jam_pelajaran`
--
LOCK TABLES `jam_pelajaran` WRITE;
/*!40000 ALTER TABLE `jam_pelajaran` DISABLE KEYS */;
INSERT INTO `jam_pelajaran` VALUES
(1,0,'07:00:00','07:15:00',0,'Pembiasaan / Doa'),
(2,1,'07:15:00','07:50:00',0,NULL),
(3,2,'07:50:00','08:25:00',0,NULL),
(4,3,'08:25:00','09:00:00',0,NULL),
(5,4,'09:00:00','09:35:00',0,NULL),
(6,5,'09:35:00','09:55:00',1,'Istirahat 1'),
(7,6,'09:55:00','10:30:00',0,NULL),
(8,7,'10:30:00','11:05:00',0,NULL),
(9,8,'11:05:00','11:40:00',0,NULL),
(10,9,'11:40:00','12:20:00',1,'Istirahat 2 & Salat Duhur'),
(11,10,'12:20:00','12:55:00',0,NULL),
(12,11,'12:55:00','13:30:00',0,NULL);
/*!40000 ALTER TABLE `jam_pelajaran` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `jenis_tagihan`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `jenis_tagihan` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(20) DEFAULT NULL,
`nama` varchar(100) DEFAULT NULL,
`periode` enum('BULANAN','SEMESTER','TAHUNAN','SEKALI') DEFAULT 'BULANAN',
`nominal_default` decimal(12,2) DEFAULT 0.00,
`is_aktif` tinyint(1) DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `jenis_tagihan`
--
LOCK TABLES `jenis_tagihan` WRITE;
/*!40000 ALTER TABLE `jenis_tagihan` DISABLE KEYS */;
INSERT INTO `jenis_tagihan` VALUES
(1,'SPP','SPP Bulanan','BULANAN',250000.00,1),
(2,'DPP','Uang Pangkal / Daftar Ulang','TAHUNAN',3500000.00,1),
(3,'SERAGAM','Seragam & Atribut','SEKALI',950000.00,1),
(4,'KEGIATAN','Iuran Kegiatan Semester','SEMESTER',450000.00,1),
(5,'BUKU','Buku & LKS','SEMESTER',500000.00,1),
(6,'STUDI','Studi Tur dan Karyawisata','SEKALI',750000.00,1),
(7,'UJIAN','Biaya Ujian Akhir','TAHUNAN',400000.00,1);
/*!40000 ALTER TABLE `jenis_tagihan` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `jurnal_mengajar`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `jurnal_mengajar` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`modul_ajar_id` int(10) unsigned DEFAULT NULL,
`tanggal` date NOT NULL,
`jam_ke` varchar(20) DEFAULT NULL,
`materi` varchar(250) DEFAULT NULL,
`kegiatan` text DEFAULT NULL,
`jumlah_hadir` smallint(6) DEFAULT NULL,
`jumlah_absen` smallint(6) DEFAULT NULL,
`kendala` text DEFAULT NULL,
`tindak_lanjut` text DEFAULT NULL,
`foto_kegiatan` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_jurnal` (`guru_id`,`kelas_id`,`mapel_id`,`tanggal`,`jam_ke`),
CONSTRAINT `jurnal_mengajar_ibfk_1` FOREIGN KEY (`guru_id`) REFERENCES `guru` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `jurnal_mengajar`
--
LOCK TABLES `jurnal_mengajar` WRITE;
/*!40000 ALTER TABLE `jurnal_mengajar` DISABLE KEYS */;
/*!40000 ALTER TABLE `jurnal_mengajar` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `jurusan`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `jurusan` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(20) NOT NULL,
`nama` varchar(120) NOT NULL COMMENT 'Kompetensi keahlian',
`bidang` varchar(120) DEFAULT NULL COMMENT 'Bidang keahlian induk',
`kaprodi_id` int(10) unsigned DEFAULT NULL,
`akreditasi` varchar(5) DEFAULT NULL,
`deskripsi` text DEFAULT NULL,
`is_aktif` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`),
KEY `fk_jur_guru` (`kaprodi_id`),
CONSTRAINT `fk_jur_guru` FOREIGN KEY (`kaprodi_id`) REFERENCES `guru` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `jurusan`
--
LOCK TABLES `jurusan` WRITE;
/*!40000 ALTER TABLE `jurusan` DISABLE KEYS */;
/*!40000 ALTER TABLE `jurusan` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `kas_kategori`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `kas_kategori` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(20) NOT NULL,
`nama` varchar(100) NOT NULL,
`arah` enum('MASUK','KELUAR') NOT NULL,
`urutan` smallint(6) NOT NULL DEFAULT 10,
`is_aktif` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `kas_kategori`
--
LOCK TABLES `kas_kategori` WRITE;
/*!40000 ALTER TABLE `kas_kategori` DISABLE KEYS */;
INSERT INTO `kas_kategori` VALUES
(1,'IURAN','Penerimaan iuran siswa','MASUK',1,1),
(2,'BOS','Dana BOS','MASUK',2,1),
(3,'BANTUAN','Bantuan dan donasi','MASUK',3,1),
(4,'LAIN_M','Penerimaan lain-lain','MASUK',9,1),
(5,'GAJI','Gaji dan honor pegawai','KELUAR',1,1),
(6,'OPS','Operasional harian','KELUAR',2,1),
(7,'LISTRIK','Listrik, air, dan internet','KELUAR',3,1),
(8,'SARANA','Sarana dan pemeliharaan','KELUAR',4,1),
(9,'KEGIATAN','Kegiatan siswa','KELUAR',5,1),
(10,'ATK','Alat tulis dan bahan ajar','KELUAR',6,1),
(11,'LAIN_K','Pengeluaran lain-lain','KELUAR',9,1);
/*!40000 ALTER TABLE `kas_kategori` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `kas_transaksi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `kas_transaksi` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`no_bukti` varchar(40) NOT NULL,
`tanggal` date NOT NULL,
`arah` enum('MASUK','KELUAR') NOT NULL,
`kategori_id` smallint(5) unsigned DEFAULT NULL,
`sumber_dana` enum('BOS','BOP','YAYASAN','KOMITE','IURAN','LAINNYA') NOT NULL DEFAULT 'IURAN',
`uraian` varchar(255) NOT NULL,
`nominal` decimal(14,2) NOT NULL,
`penerima` varchar(150) DEFAULT NULL COMMENT 'Pihak yang menerima atau menyetor',
`bukti` varchar(255) DEFAULT NULL,
`pembayaran_id` bigint(20) unsigned DEFAULT NULL,
`dicatat_oleh` int(10) unsigned DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `no_bukti` (`no_bukti`),
KEY `idx_tanggal` (`tanggal`,`arah`),
KEY `idx_kategori` (`kategori_id`),
CONSTRAINT `fk_kas_kategori` FOREIGN KEY (`kategori_id`) REFERENCES `kas_kategori` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `kas_transaksi`
--
LOCK TABLES `kas_transaksi` WRITE;
/*!40000 ALTER TABLE `kas_transaksi` DISABLE KEYS */;
INSERT INTO `kas_transaksi` VALUES
(1,'KAS-2601','2026-07-15','MASUK',2,'BOS','Pencairan dana BOS triwulan III',42000000.00,'Rekening sekolah',NULL,NULL,NULL,'2026-09-08 23:22:21'),
(2,'KAS-2602','2026-07-20','KELUAR',5,'YAYASAN','Honor guru dan tendik bulan lalu',28500000.00,'18 pegawai',NULL,NULL,NULL,'2026-09-08 23:22:21'),
(3,'KAS-2603','2026-07-28','KELUAR',7,'BOS','Listrik, air, dan internet',3150000.00,'PLN, PDAM, ISP',NULL,NULL,NULL,'2026-09-08 23:22:21'),
(4,'KAS-2604','2026-08-04','KELUAR',10,'BOS','Pengadaan buku dan alat tulis semester ganjil',6800000.00,'CV Sumber Ilmu',NULL,NULL,NULL,'2026-09-08 23:22:21'),
(5,'KAS-2605','2026-08-11','KELUAR',8,'YAYASAN','Perbaikan atap ruang kelas 3A',4500000.00,'Tukang bangunan',NULL,NULL,NULL,'2026-09-08 23:22:21'),
(6,'KAS-2606','2026-08-19','MASUK',3,'LAINNYA','Donasi wali murid untuk perpustakaan',2500000.00,'Komite sekolah',NULL,NULL,NULL,'2026-09-08 23:22:21'),
(7,'KAS-2607','2026-08-25','KELUAR',9,'KOMITE','Kegiatan Pramuka dan lomba kecamatan',3200000.00,'Panitia kegiatan',NULL,NULL,NULL,'2026-09-08 23:22:21'),
(8,'KAS-2608','2026-09-01','KELUAR',6,'IURAN','Konsumsi rapat dan operasional kantor',1750000.00,'Kantor TU',NULL,NULL,NULL,'2026-09-08 23:22:21');
/*!40000 ALTER TABLE `kas_transaksi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `kelas`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `kelas` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`tingkat` tinyint(4) NOT NULL COMMENT '1..6',
`jurusan_id` smallint(5) unsigned DEFAULT NULL,
`fase` enum('A','B','C','D','E','F') DEFAULT NULL,
`rombel` varchar(20) NOT NULL COMMENT 'A / B / Ibnu Sina',
`nama` varchar(40) NOT NULL COMMENT '4A',
`wali_kelas_id` int(10) unsigned DEFAULT NULL,
`pendamping_id` int(10) unsigned DEFAULT NULL,
`ruang` varchar(30) DEFAULT NULL,
`kuota` smallint(6) DEFAULT 32,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_kelas` (`tahun_ajaran_id`,`tingkat`,`rombel`),
KEY `wali_kelas_id` (`wali_kelas_id`),
CONSTRAINT `kelas_ibfk_1` FOREIGN KEY (`tahun_ajaran_id`) REFERENCES `tahun_ajaran` (`id`),
CONSTRAINT `kelas_ibfk_2` FOREIGN KEY (`wali_kelas_id`) REFERENCES `guru` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `kelas`
--
LOCK TABLES `kelas` WRITE;
/*!40000 ALTER TABLE `kelas` DISABLE KEYS */;
INSERT INTO `kelas` VALUES
(1,1,7,NULL,'D','A','7A',2,NULL,'R101',28),
(2,1,8,NULL,'D','A','8A',NULL,NULL,'R102',28),
(3,1,9,NULL,'D','A','9A',NULL,NULL,'R103',30),
(4,1,7,NULL,'D','B','7B',2,NULL,'R104',30),
(5,1,8,NULL,'D','B','8B',NULL,NULL,'R105',30),
(6,1,9,NULL,'D','B','9B',NULL,NULL,'R106',30);
/*!40000 ALTER TABLE `kelas` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `kelas_siswa`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `kelas_siswa` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`kelas_id` smallint(5) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`no_absen` smallint(6) DEFAULT NULL,
`status` enum('AKTIF','PINDAH_KELAS','KELUAR') DEFAULT 'AKTIF',
PRIMARY KEY (`id`),
UNIQUE KEY `uq_ks` (`kelas_id`,`siswa_id`),
KEY `siswa_id` (`siswa_id`),
CONSTRAINT `kelas_siswa_ibfk_1` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE,
CONSTRAINT `kelas_siswa_ibfk_2` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `kelas_siswa`
--
LOCK TABLES `kelas_siswa` WRITE;
/*!40000 ALTER TABLE `kelas_siswa` DISABLE KEYS */;
INSERT INTO `kelas_siswa` VALUES
(1,4,1,1,'AKTIF'),
(2,4,2,2,'AKTIF'),
(3,4,3,3,'AKTIF'),
(4,1,5,1,'AKTIF'),
(5,1,6,2,'AKTIF'),
(6,1,4,3,'AKTIF'),
(7,2,8,1,'AKTIF'),
(8,2,9,2,'AKTIF'),
(9,2,7,3,'AKTIF'),
(10,3,11,1,'AKTIF'),
(11,3,10,2,'AKTIF'),
(12,4,13,1,'AKTIF'),
(13,4,12,2,'AKTIF');
/*!40000 ALTER TABLE `kelas_siswa` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `kokurikuler`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `kokurikuler` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned DEFAULT NULL,
`kelas_id` smallint(5) unsigned DEFAULT NULL,
`judul` varchar(200) DEFAULT NULL,
`tema` varchar(120) DEFAULT NULL,
`deskripsi` text DEFAULT NULL,
`dimensi` varchar(120) DEFAULT NULL COMMENT 'csv dimensi_lulusan',
`metode` varchar(60) DEFAULT 'Project Based Learning',
`mulai` date DEFAULT NULL,
`selesai` date DEFAULT NULL,
`koordinator_id` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `kokurikuler`
--
LOCK TABLES `kokurikuler` WRITE;
/*!40000 ALTER TABLE `kokurikuler` DISABLE KEYS */;
/*!40000 ALTER TABLE `kokurikuler` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `kokurikuler_nilai`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `kokurikuler_nilai` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`kokurikuler_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`dimensi_id` tinyint(3) unsigned DEFAULT NULL,
`capaian` enum('BB','MB','BSH','SB') DEFAULT NULL,
`catatan` text DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_kn` (`kokurikuler_id`,`siswa_id`,`dimensi_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `kokurikuler_nilai`
--
LOCK TABLES `kokurikuler_nilai` WRITE;
/*!40000 ALTER TABLE `kokurikuler_nilai` DISABLE KEYS */;
/*!40000 ALTER TABLE `kokurikuler_nilai` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `laporan_harian`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `laporan_harian` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`siswa_id` int(10) unsigned NOT NULL,
`tanggal` date NOT NULL,
`ringkasan` text DEFAULT NULL COMMENT 'digenerate otomatis tiap sore',
`status_absen` varchar(20) DEFAULT NULL,
`jam_masuk` time DEFAULT NULL,
`jam_pulang` time DEFAULT NULL,
`materi_selesai` smallint(6) DEFAULT 0,
`tugas_selesai` smallint(6) DEFAULT 0,
`tugas_pending` smallint(6) DEFAULT 0,
`menit_literasi` smallint(6) DEFAULT 0,
`poin_hari_ini` smallint(6) DEFAULT 0,
`nilai_baru` smallint(6) DEFAULT 0,
`catatan_guru` text DEFAULT NULL,
`mood` enum('SENANG','BIASA','KURANG') DEFAULT NULL,
`dikirim` tinyint(1) DEFAULT 0,
`dibaca_ortu` tinyint(1) DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_lh` (`siswa_id`,`tanggal`),
KEY `tanggal` (`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `laporan_harian`
--
LOCK TABLES `laporan_harian` WRITE;
/*!40000 ALTER TABLE `laporan_harian` DISABLE KEYS */;
/*!40000 ALTER TABLE `laporan_harian` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `literasi_log`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `literasi_log` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`siswa_id` int(10) unsigned NOT NULL,
`buku_id` int(10) unsigned DEFAULT NULL,
`judul_luar` varchar(200) DEFAULT NULL COMMENT 'buku di luar koleksi perpus',
`tanggal` date NOT NULL,
`halaman_awal` smallint(6) DEFAULT NULL,
`halaman_akhir` smallint(6) DEFAULT NULL,
`menit_baca` smallint(6) DEFAULT 15,
`ringkasan` text DEFAULT NULL,
`kesan` text DEFAULT NULL,
`emosi` enum('SENANG','BIASA','SEDIH','SERU','BOSAN') DEFAULT NULL,
`foto_bukti` varchar(255) DEFAULT NULL,
`status_verifikasi` enum('MENUNGGU','DIVERIFIKASI','DITOLAK') DEFAULT 'MENUNGGU',
`verifikator_id` int(10) unsigned DEFAULT NULL,
`catatan_guru` text DEFAULT NULL,
`poin` smallint(6) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `siswa_id` (`siswa_id`,`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `literasi_log`
--
LOCK TABLES `literasi_log` WRITE;
/*!40000 ALTER TABLE `literasi_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `literasi_log` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `mapel`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `mapel` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(15) NOT NULL,
`nama` varchar(100) NOT NULL,
`nama_singkat` varchar(30) DEFAULT NULL,
`kelompok` enum('UMUM','AGAMA','MULOK','EKSKUL','BIMBINGAN') DEFAULT 'UMUM',
`naungan` enum('SEMUA','KEMENDIKDASMEN','KEMENAG') DEFAULT 'SEMUA',
`urutan_rapor` smallint(6) DEFAULT 99,
`tingkat_min` tinyint(4) DEFAULT 1,
`tingkat_max` tinyint(4) DEFAULT 12,
`jam_per_minggu` tinyint(4) DEFAULT 2,
`warna` varchar(9) DEFAULT '#1F4A3C' COMMENT 'warna kartu e-learning',
`ikon` varchar(40) DEFAULT NULL,
`is_aktif` tinyint(1) DEFAULT 1,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `mapel`
--
LOCK TABLES `mapel` WRITE;
/*!40000 ALTER TABLE `mapel` DISABLE KEYS */;
INSERT INTO `mapel` VALUES
(1,'PAI','Pendidikan Agama Islam dan Budi Pekerti','PAI','AGAMA','KEMENDIKDASMEN',1,1,6,4,'#1F7A5A','mosque',0),
(2,'PP','Pendidikan Pancasila','P.Pancasila','UMUM','KEMENDIKDASMEN',2,1,6,4,'#C0392B','flag',0),
(3,'BIN','Bahasa Indonesia','B. Indonesia','UMUM','KEMENDIKDASMEN',3,1,6,6,'#E67E22','book-open',0),
(4,'MTK','Matematika','Matematika','UMUM','KEMENDIKDASMEN',4,1,6,5,'#2B4570','calculator',0),
(5,'IPAS','Ilmu Pengetahuan Alam dan Sosial','IPAS','UMUM','KEMENDIKDASMEN',5,3,6,5,'#16A085','globe',0),
(6,'PJOK','Pendidikan Jasmani, Olahraga, dan Kesehatan','PJOK','UMUM','SEMUA',6,1,6,3,'#8E44AD','activity',0),
(7,'SBDP','Seni Budaya dan Prakarya','Seni Budaya','UMUM','SEMUA',7,1,6,3,'#D35400','palette',0),
(8,'BING','Bahasa Inggris','B. Inggris','MULOK','SEMUA',8,1,6,2,'#2980B9','languages',0),
(9,'BSUN','Bahasa Sunda','B. Sunda','MULOK','SEMUA',9,1,6,2,'#7F8C8D','mountain',0),
(10,'BTQ','Baca Tulis Al-Quran','BTQ','MULOK','SEMUA',10,1,6,2,'#1F4A3C','book',0),
(11,'QH','Al-Qur\'an Hadis','Quran Hadis','AGAMA','KEMENAG',1,1,6,2,'#1F7A5A','book',0),
(12,'AA','Akidah Akhlak','Akidah Akhlak','AGAMA','KEMENAG',2,1,6,2,'#27AE60','heart',0),
(13,'FIK','Fikih','Fikih','AGAMA','KEMENAG',3,1,6,2,'#16A085','scale',0),
(14,'SKI','Sejarah Kebudayaan Islam','SKI','AGAMA','KEMENAG',4,3,6,2,'#B7791F','landmark',0),
(15,'BAR','Bahasa Arab','B. Arab','AGAMA','KEMENAG',5,1,6,2,'#8E6C3B','languages',0),
(16,'PAI-SMP','Pendidikan Agama Islam dan Budi Pekerti',NULL,'UMUM','SEMUA',1,7,9,3,'#1F4A3C',NULL,1),
(17,'PP-SMP','Pendidikan Pancasila',NULL,'UMUM','SEMUA',2,7,9,3,'#C0563F',NULL,1),
(18,'BIN-SMP','Bahasa Indonesia',NULL,'UMUM','SEMUA',3,7,9,6,'#E67E22',NULL,1),
(19,'MTK-SMP','Matematika',NULL,'UMUM','SEMUA',4,7,9,5,'#2B4570',NULL,1),
(20,'IPA-SMP','Ilmu Pengetahuan Alam',NULL,'UMUM','SEMUA',5,7,9,5,'#2E9E6B',NULL,1),
(21,'IPS-SMP','Ilmu Pengetahuan Sosial',NULL,'UMUM','SEMUA',6,7,9,4,'#8A6212',NULL,1),
(22,'BING-SMP','Bahasa Inggris',NULL,'UMUM','SEMUA',7,7,9,4,'#534AB7',NULL,1),
(23,'PJOK-SMP','Pendidikan Jasmani, Olahraga, dan Kesehatan',NULL,'UMUM','SEMUA',8,7,9,3,'#16A085',NULL,1),
(24,'INF-SMP','Informatika',NULL,'UMUM','SEMUA',9,7,9,2,'#4A5BC4',NULL,1),
(25,'SBUD-SMP','Seni Budaya',NULL,'UMUM','SEMUA',10,7,9,3,'#8E44AD',NULL,1),
(26,'PRAK-SMP','Prakarya dan Kewirausahaan',NULL,'UMUM','SEMUA',11,7,9,2,'#D35400',NULL,1),
(27,'QH-SMP','Al-Qur\'an Hadis',NULL,'MULOK','SEMUA',12,7,9,2,'#16362B',NULL,1),
(28,'AA-SMP','Akidah Akhlak',NULL,'MULOK','SEMUA',13,7,9,2,'#1F4A3C',NULL,1),
(29,'FIK-SMP','Fikih',NULL,'MULOK','SEMUA',14,7,9,2,'#2E9E6B',NULL,1),
(30,'SKI-SMP','Sejarah Kebudayaan Islam',NULL,'MULOK','SEMUA',15,7,9,2,'#8A6212',NULL,1),
(31,'BAR-SMP','Bahasa Arab',NULL,'MULOK','SEMUA',16,7,9,2,'#C0563F',NULL,1),
(32,'BSUN-SMP','Bahasa Sunda',NULL,'MULOK','SEMUA',17,7,9,2,'#D4A017',NULL,1);
/*!40000 ALTER TABLE `mapel` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `materi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `materi` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned DEFAULT NULL COMMENT 'null = semua kelas tingkat itu',
`tingkat` tinyint(4) DEFAULT NULL,
`modul_ajar_id` int(10) unsigned DEFAULT NULL,
`tp_id` int(10) unsigned DEFAULT NULL,
`judul` varchar(200) NOT NULL,
`slug` varchar(220) DEFAULT NULL,
`deskripsi` text DEFAULT NULL,
`konten` longtext DEFAULT NULL COMMENT 'HTML editor',
`tipe` enum('TEKS','VIDEO','PDF','SLIDE','AUDIO','LINK','INTERAKTIF') DEFAULT 'TEKS',
`url_video` varchar(255) DEFAULT NULL COMMENT 'youtube/vimeo/self-host',
`durasi_menit` smallint(6) DEFAULT NULL,
`thumbnail` varchar(255) DEFAULT NULL,
`file_lampiran` varchar(255) DEFAULT NULL,
`urutan` smallint(6) DEFAULT 1,
`poin` smallint(6) DEFAULT 10 COMMENT 'poin gamifikasi saat selesai',
`tanggal_terbit` datetime DEFAULT NULL,
`status` enum('DRAFT','TERBIT','ARSIP') DEFAULT 'DRAFT',
`jumlah_dilihat` int(11) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `mapel_id` (`mapel_id`),
KEY `kelas_id` (`kelas_id`,`mapel_id`),
FULLTEXT KEY `ft_materi` (`judul`,`deskripsi`),
CONSTRAINT `materi_ibfk_1` FOREIGN KEY (`mapel_id`) REFERENCES `mapel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `materi`
--
LOCK TABLES `materi` WRITE;
/*!40000 ALTER TABLE `materi` DISABLE KEYS */;
/*!40000 ALTER TABLE `materi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `materi_baca`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `materi_baca` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`materi_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`dibaca_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_materi_baca` (`materi_id`,`siswa_id`),
KEY `fk_mb_siswa` (`siswa_id`),
CONSTRAINT `fk_mb_materi` FOREIGN KEY (`materi_id`) REFERENCES `materi` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_mb_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `materi_baca`
--
LOCK TABLES `materi_baca` WRITE;
/*!40000 ALTER TABLE `materi_baca` DISABLE KEYS */;
/*!40000 ALTER TABLE `materi_baca` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `materi_progres`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `materi_progres` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`materi_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`persen` tinyint(4) DEFAULT 0,
`detik_tonton` int(11) DEFAULT 0,
`selesai` tinyint(1) DEFAULT 0,
`selesai_at` datetime DEFAULT NULL,
`terakhir_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_mp` (`materi_id`,`siswa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `materi_progres`
--
LOCK TABLES `materi_progres` WRITE;
/*!40000 ALTER TABLE `materi_progres` DISABLE KEYS */;
/*!40000 ALTER TABLE `materi_progres` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `modul_ajar`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `modul_ajar` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned DEFAULT NULL,
`tingkat` tinyint(4) DEFAULT NULL,
`fase` enum('A','B','C','D','E','F') DEFAULT NULL,
`semester` enum('1','2') DEFAULT NULL,
`kurikulum` enum('MERDEKA','KBC','K13') DEFAULT 'MERDEKA',
`judul` varchar(200) NOT NULL,
`alokasi_waktu` varchar(120) DEFAULT NULL COMMENT '2 x 35 menit',
`jumlah_pertemuan` tinyint(4) DEFAULT 1,
`tujuan_pembelajaran` text DEFAULT NULL,
`pemahaman_bermakna` text DEFAULT NULL,
`pertanyaan_pemantik` text DEFAULT NULL,
`profil_dimensi` varchar(200) DEFAULT NULL COMMENT 'csv id dimensi_lulusan',
`panca_cinta` varchar(120) DEFAULT NULL COMMENT 'csv id dimensi KBC (madrasah)',
`model_pembelajaran` varchar(255) DEFAULT 'Problem Based Learning',
`metode` varchar(255) DEFAULT NULL,
`pendekatan` varchar(255) DEFAULT 'Pembelajaran Mendalam',
`sarana_prasarana` text DEFAULT NULL,
`target_peserta` varchar(255) DEFAULT NULL,
`kegiatan_pendahuluan` text DEFAULT NULL,
`kegiatan_inti` text DEFAULT NULL,
`kegiatan_penutup` text DEFAULT NULL,
`asesmen_diagnostik` text DEFAULT NULL,
`asesmen_formatif` text DEFAULT NULL,
`asesmen_sumatif` text DEFAULT NULL,
`rubrik_penilaian` text DEFAULT NULL COMMENT 'JSON',
`pengayaan` text DEFAULT NULL,
`remedial` text DEFAULT NULL,
`refleksi_guru` text DEFAULT NULL,
`refleksi_siswa` text DEFAULT NULL,
`lampiran_lkpd` text DEFAULT NULL,
`bahan_bacaan` text DEFAULT NULL,
`glosarium` text DEFAULT NULL,
`daftar_pustaka` text DEFAULT NULL,
`file_pdf` varchar(255) DEFAULT NULL,
`file_docx` varchar(255) DEFAULT NULL,
`status` enum('DRAFT','DIAJUKAN','DISETUJUI','REVISI') DEFAULT 'DRAFT',
`catatan_kepsek` text DEFAULT NULL,
`disetujui_oleh` int(10) unsigned DEFAULT NULL,
`disetujui_at` datetime DEFAULT NULL,
`sumber_ai` tinyint(1) DEFAULT 0,
`ai_log_id` bigint(20) unsigned DEFAULT 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(),
PRIMARY KEY (`id`),
KEY `guru_id` (`guru_id`),
KEY `mapel_id` (`mapel_id`),
KEY `status` (`status`),
FULLTEXT KEY `ft_modul` (`judul`,`tujuan_pembelajaran`),
CONSTRAINT `modul_ajar_ibfk_1` FOREIGN KEY (`guru_id`) REFERENCES `guru` (`id`) ON DELETE CASCADE,
CONSTRAINT `modul_ajar_ibfk_2` FOREIGN KEY (`mapel_id`) REFERENCES `mapel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `modul_ajar`
--
LOCK TABLES `modul_ajar` WRITE;
/*!40000 ALTER TABLE `modul_ajar` DISABLE KEYS */;
/*!40000 ALTER TABLE `modul_ajar` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `mutasi_siswa`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `mutasi_siswa` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`siswa_id` int(10) unsigned NOT NULL,
`jenis` enum('MASUK','PINDAH_KELUAR','PINDAH_MASUK','LULUS','KELUAR') NOT NULL,
`tanggal` date DEFAULT NULL,
`sekolah_asal` varchar(150) DEFAULT NULL,
`sekolah_tujuan` varchar(150) DEFAULT NULL,
`alasan` text DEFAULT NULL,
`no_surat` varchar(60) DEFAULT NULL,
`file_surat` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `siswa_id` (`siswa_id`),
CONSTRAINT `mutasi_siswa_ibfk_1` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `mutasi_siswa`
--
LOCK TABLES `mutasi_siswa` WRITE;
/*!40000 ALTER TABLE `mutasi_siswa` DISABLE KEYS */;
/*!40000 ALTER TABLE `mutasi_siswa` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `nilai`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `nilai` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned DEFAULT NULL,
`tp_id` int(10) unsigned DEFAULT NULL,
`kategori` enum('FORMATIF','SUMATIF_LINGKUP','STS','SAS','PROYEK','PRAKTIK') NOT NULL,
`sumber` enum('MANUAL','TUGAS','UJIAN','GAME') DEFAULT 'MANUAL',
`ref_id` bigint(20) unsigned DEFAULT NULL,
`judul` varchar(150) DEFAULT NULL,
`nilai` decimal(5,2) DEFAULT NULL,
`bobot` tinyint(4) DEFAULT 1,
`tanggal` date DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `siswa_id` (`siswa_id`,`mapel_id`,`tahun_ajaran_id`),
KEY `kelas_id` (`kelas_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `nilai`
--
LOCK TABLES `nilai` WRITE;
/*!40000 ALTER TABLE `nilai` DISABLE KEYS */;
/*!40000 ALTER TABLE `nilai` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `nilai_akhir`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `nilai_akhir` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`rata_formatif` decimal(5,2) DEFAULT NULL,
`rata_sumatif` decimal(5,2) DEFAULT NULL,
`nilai_sts` decimal(5,2) DEFAULT NULL,
`nilai_sas` decimal(5,2) DEFAULT NULL,
`nilai_akhir` decimal(5,2) DEFAULT NULL,
`predikat` char(1) DEFAULT NULL,
`kkm` decimal(5,2) DEFAULT 70.00,
`tuntas` tinyint(1) DEFAULT NULL,
`capaian_tertinggi` text DEFAULT NULL,
`capaian_perlu_bimbingan` text DEFAULT NULL,
`deskripsi` text DEFAULT NULL,
`deskripsi_ai` tinyint(1) DEFAULT 0,
`dikunci` tinyint(1) DEFAULT 0,
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_na` (`tahun_ajaran_id`,`siswa_id`,`mapel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `nilai_akhir`
--
LOCK TABLES `nilai_akhir` WRITE;
/*!40000 ALTER TABLE `nilai_akhir` DISABLE KEYS */;
/*!40000 ALTER TABLE `nilai_akhir` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `nilai_dimensi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `nilai_dimensi` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`dimensi_id` tinyint(3) unsigned NOT NULL,
`capaian` enum('BB','MB','BSH','SB') DEFAULT NULL COMMENT 'Belum/Mulai/Sesuai Harapan/Sangat Berkembang',
`deskripsi` text DEFAULT NULL,
`catatan_guru` text DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_nd` (`tahun_ajaran_id`,`siswa_id`,`dimensi_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `nilai_dimensi`
--
LOCK TABLES `nilai_dimensi` WRITE;
/*!40000 ALTER TABLE `nilai_dimensi` DISABLE KEYS */;
/*!40000 ALTER TABLE `nilai_dimensi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `notifikasi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `notifikasi` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`tipe` varchar(40) DEFAULT NULL COMMENT 'ABSENSI, NILAI, TUGAS, TAGIHAN, PERPUS, PENGUMUMAN',
`judul` varchar(150) DEFAULT NULL,
`pesan` text DEFAULT NULL,
`ref_tabel` varchar(60) DEFAULT NULL,
`ref_id` bigint(20) unsigned DEFAULT NULL,
`deeplink` varchar(150) DEFAULT NULL COMMENT 'simap://absensi/123',
`dibaca` tinyint(1) DEFAULT 0,
`dibaca_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`,`dibaca`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `notifikasi`
--
LOCK TABLES `notifikasi` WRITE;
/*!40000 ALTER TABLE `notifikasi` DISABLE KEYS */;
/*!40000 ALTER TABLE `notifikasi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ortu`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ortu` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nik` varchar(20) DEFAULT NULL,
`nama` varchar(120) NOT NULL,
`hubungan` enum('AYAH','IBU','WALI') NOT NULL,
`tempat_lahir` varchar(60) DEFAULT NULL,
`tanggal_lahir` date DEFAULT NULL,
`pendidikan` varchar(30) DEFAULT NULL,
`pekerjaan` varchar(80) DEFAULT NULL,
`penghasilan` enum('<1JT','1-2JT','2-5JT','5-10JT','>10JT','TIDAK_TETAP') DEFAULT NULL,
`no_hp` varchar(20) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`alamat` text DEFAULT NULL,
`status_hidup` enum('HIDUP','MENINGGAL') DEFAULT 'HIDUP',
`foto` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ortu`
--
LOCK TABLES `ortu` WRITE;
/*!40000 ALTER TABLE `ortu` DISABLE KEYS */;
INSERT INTO `ortu` VALUES
(1,NULL,'Hendra Pratama','AYAH',NULL,NULL,NULL,'Wiraswasta',NULL,'081311110001',NULL,NULL,'HIDUP',NULL,'2026-09-08 23:22:21'),
(2,NULL,'Dewi Lestari','IBU',NULL,NULL,NULL,'Ibu Rumah Tangga',NULL,'081311110002',NULL,NULL,'HIDUP',NULL,'2026-09-08 23:22:21'),
(3,NULL,'Slamet Ramadhani','AYAH',NULL,NULL,NULL,'Karyawan Swasta',NULL,'081311110003',NULL,NULL,'HIDUP',NULL,'2026-09-08 23:22:21'),
(4,'3201010101850001','Hendra Pratama','AYAH','Bogor','1985-01-01','S1','Karyawan Swasta','5-10JT','081311110001',NULL,'Perum Griya Asri Blok C2 No 14','HIDUP',NULL,'2026-09-08 23:22:21'),
(5,'3201014102880001','Siti Maryam','IBU','Bogor','1988-02-01','SMA','Ibu Rumah Tangga',NULL,'081311110002',NULL,'Perum Griya Asri Blok C2 No 14','HIDUP',NULL,'2026-09-08 23:22:21'),
(6,'3201010101820002','Slamet Ramadhani','AYAH','Cianjur','1982-05-10','SMA','Wiraswasta','2-5JT','081311110003',NULL,'Kp. Cipeucang RT 02','HIDUP',NULL,'2026-09-08 23:22:21'),
(7,'3201014102860002','Nurhayati','IBU','Bogor','1986-08-17','SMP','Pedagang','1-2JT','081311110004',NULL,'Kp. Cipeucang RT 02','HIDUP',NULL,'2026-09-08 23:22:21'),
(8,'3201010101800003','Bambang Sutrisno','AYAH','Jakarta','1980-03-03','S2','Pegawai Negeri Sipil','5-10JT','081311110005',NULL,'Jl. Raya Narogong KM 12','HIDUP',NULL,'2026-09-08 23:22:21'),
(9,'3201014102840003','Dewi Anggraini','IBU','Jakarta','1984-12-12','S1','Guru','2-5JT','081311110006',NULL,'Jl. Raya Narogong KM 12','HIDUP',NULL,'2026-09-08 23:22:21'),
(10,'3201010101830004','Ahmad Sulaiman','AYAH','Bogor','1983-06-20','SMA','Buruh Harian','1-2JT','081311110007',NULL,'Kp. Bojong RT 04','HIDUP',NULL,'2026-09-08 23:22:21'),
(11,'3201014102870004','Yuliana','IBU','Bogor','1987-07-07','SMP','Ibu Rumah Tangga',NULL,'081311110008',NULL,'Kp. Bojong RT 04','HIDUP',NULL,'2026-09-08 23:22:21'),
(12,'3201010101810005','Rudi Hartono','AYAH','Bandung','1981-09-09','S1','Karyawan Swasta','5-10JT','081311110009',NULL,'Perum Cileungsi Hijau Blok D5','HIDUP',NULL,'2026-09-08 23:22:21'),
(13,'3201014102850005','Fitri Handayani','IBU','Bogor','1985-04-14','S1','Bidan','2-5JT','081311110010',NULL,'Perum Cileungsi Hijau Blok D5','HIDUP',NULL,'2026-09-08 23:22:21'),
(14,'3201010101790006','Sugianto','AYAH','Bekasi','1979-02-02','SMA','Sopir','1-2JT','081311110011',NULL,'Jl. Mekarsari No 8','MENINGGAL',NULL,'2026-09-08 23:22:21'),
(15,'3201014102830006','Sri Wahyuni','IBU','Bekasi','1983-10-30','SMA','Karyawan Pabrik','2-5JT','081311110012',NULL,'Jl. Mekarsari No 8','HIDUP',NULL,'2026-09-08 23:22:21'),
(16,'3201010101840007','Iwan Setiawan','AYAH','Bogor','1984-08-08','D3','Teknisi','2-5JT','081311110013',NULL,'Kp. Pasir Angin RT 01','HIDUP',NULL,'2026-09-08 23:22:21'),
(17,'3201014102880007','Ratna Sari','IBU','Bogor','1988-11-11','SMA','Penjahit','TIDAK_TETAP','081311110014',NULL,'Kp. Pasir Angin RT 01','HIDUP',NULL,'2026-09-08 23:22:21'),
(18,'3201010101780008','Gunawan Wibisono','AYAH','Semarang','1978-01-15','S1','Pengusaha','>10JT','081311110015',NULL,'Perum Metland Blok B12','HIDUP',NULL,'2026-09-08 23:22:21'),
(19,'3201014102820008','Lestari Ningsih','IBU','Bogor','1982-03-21','S1','Karyawan Swasta','5-10JT','081311110016',NULL,'Perum Metland Blok B12','HIDUP',NULL,'2026-09-08 23:22:21'),
(20,'3201010101860009','Asep Saepudin','AYAH','Sukabumi','1986-05-05','SMA','Petani','TIDAK_TETAP','081311110017',NULL,'Kp. Cikuda RT 03','HIDUP',NULL,'2026-09-08 23:22:21'),
(21,'3201014102890009','Euis Komariah','IBU','Sukabumi','1989-09-19','SD','Ibu Rumah Tangga',NULL,'081311110018',NULL,'Kp. Cikuda RT 03','HIDUP',NULL,'2026-09-08 23:22:21'),
(22,'3201010101830010','Deni Kurniawan','AYAH','Bogor','1983-12-01','D3','Wiraswasta','2-5JT','081311110019',NULL,'Jl. Anggrek Raya No 22','HIDUP',NULL,'2026-09-08 23:22:21'),
(23,'3201014102870010','Wulan Sari','IBU','Bogor','1987-06-16','SMA','Ibu Rumah Tangga',NULL,'081311110020',NULL,'Jl. Anggrek Raya No 22','HIDUP',NULL,'2026-09-08 23:22:21');
/*!40000 ALTER TABLE `ortu` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `pembayaran`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `pembayaran` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`no_kwitansi` varchar(30) DEFAULT NULL,
`tagihan_id` int(10) unsigned NOT NULL,
`tanggal` datetime DEFAULT NULL,
`nominal` decimal(12,2) DEFAULT NULL,
`metode` enum('TUNAI','TRANSFER','QRIS','VA') DEFAULT 'TUNAI',
`bukti` varchar(255) DEFAULT NULL,
`petugas_id` int(10) unsigned DEFAULT NULL,
`catatan` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `no_kwitansi` (`no_kwitansi`),
KEY `tagihan_id` (`tagihan_id`),
CONSTRAINT `pembayaran_ibfk_1` FOREIGN KEY (`tagihan_id`) REFERENCES `tagihan` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pembayaran`
--
LOCK TABLES `pembayaran` WRITE;
/*!40000 ALTER TABLE `pembayaran` DISABLE KEYS */;
INSERT INTO `pembayaran` VALUES
(1,'KW-202608-00002',2,'2026-08-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(2,'KW-202607-00003',3,'2026-07-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(3,'KW-202608-00005',5,'2026-08-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(4,'KW-202607-00006',6,'2026-07-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(5,'KW-202608-00011',11,'2026-08-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(6,'KW-202607-00012',12,'2026-07-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(7,'KW-202608-00014',14,'2026-08-26 00:00:00',100000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(8,'KW-202607-00015',15,'2026-07-26 00:00:00',100000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(9,'KW-202608-00020',20,'2026-08-26 00:00:00',100000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(10,'KW-202607-00021',21,'2026-07-26 00:00:00',100000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(11,'KW-202608-00023',23,'2026-08-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(12,'KW-202607-00024',24,'2026-07-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(13,'KW-202608-00029',29,'2026-08-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(14,'KW-202607-00030',30,'2026-07-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(15,'KW-202608-00032',32,'2026-08-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(16,'KW-202607-00033',33,'2026-07-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(17,'KW-202608-00038',38,'2026-08-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU'),
(18,'KW-202607-00039',39,'2026-07-26 00:00:00',150000.00,'TUNAI',NULL,5,'Dibayar di kantor TU');
/*!40000 ALTER TABLE `pembayaran` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `pembayaran_online`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `pembayaran_online` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`kode_bayar` varchar(30) NOT NULL COMMENT 'Kode unik yang dilihat orang tua',
`siswa_id` int(10) unsigned NOT NULL,
`diajukan_oleh` int(10) unsigned DEFAULT NULL COMMENT 'user ortu',
`total` decimal(12,2) NOT NULL,
`metode` enum('TRANSFER','QRIS','VA','TUNAI') NOT NULL DEFAULT 'TRANSFER',
`bank` varchar(40) DEFAULT NULL,
`no_va` varchar(40) DEFAULT NULL COMMENT 'Nomor virtual account bila dipakai',
`status` enum('MENUNGGU','DIBAYAR','DIKONFIRMASI','DITOLAK','KEDALUWARSA') NOT NULL DEFAULT 'MENUNGGU',
`bukti` varchar(255) DEFAULT NULL COMMENT 'Foto bukti transfer dari orang tua',
`catatan_ortu` varchar(255) DEFAULT NULL,
`catatan_admin` varchar(255) DEFAULT NULL,
`gateway` varchar(30) DEFAULT NULL COMMENT 'Nama penyedia bila memakai payment gateway',
`gateway_ref` varchar(100) DEFAULT NULL COMMENT 'Nomor rujukan dari penyedia',
`kedaluwarsa_at` datetime DEFAULT NULL,
`dibayar_at` datetime DEFAULT NULL,
`dikonfirmasi_oleh` int(10) unsigned DEFAULT NULL,
`dikonfirmasi_at` datetime DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `kode_bayar` (`kode_bayar`),
KEY `idx_siswa` (`siswa_id`,`status`),
KEY `idx_status` (`status`,`created_at`),
CONSTRAINT `fk_po_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pembayaran_online`
--
LOCK TABLES `pembayaran_online` WRITE;
/*!40000 ALTER TABLE `pembayaran_online` DISABLE KEYS */;
/*!40000 ALTER TABLE `pembayaran_online` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `pembayaran_online_item`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `pembayaran_online_item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`bayar_id` bigint(20) unsigned NOT NULL,
`tagihan_id` int(10) unsigned NOT NULL,
`nominal` decimal(12,2) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_item` (`bayar_id`,`tagihan_id`),
KEY `fk_poi_tagihan` (`tagihan_id`),
CONSTRAINT `fk_poi_bayar` FOREIGN KEY (`bayar_id`) REFERENCES `pembayaran_online` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_poi_tagihan` FOREIGN KEY (`tagihan_id`) REFERENCES `tagihan` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pembayaran_online_item`
--
LOCK TABLES `pembayaran_online_item` WRITE;
/*!40000 ALTER TABLE `pembayaran_online_item` DISABLE KEYS */;
/*!40000 ALTER TABLE `pembayaran_online_item` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `peminjaman`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `peminjaman` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(25) DEFAULT NULL,
`anggota_id` int(10) unsigned NOT NULL,
`eksemplar_id` int(10) unsigned NOT NULL,
`tanggal_pinjam` date NOT NULL,
`tanggal_jatuh_tempo` date NOT NULL,
`tanggal_kembali` date DEFAULT NULL,
`perpanjangan` tinyint(4) DEFAULT 0,
`status` enum('DIPINJAM','KEMBALI','TERLAMBAT','HILANG') DEFAULT 'DIPINJAM',
`hari_telat` smallint(6) DEFAULT 0,
`denda` decimal(10,2) DEFAULT 0.00,
`denda_lunas` tinyint(1) DEFAULT 1,
`petugas_pinjam` int(10) unsigned DEFAULT NULL,
`petugas_kembali` int(10) unsigned DEFAULT NULL,
`catatan` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`),
KEY `anggota_id` (`anggota_id`),
KEY `eksemplar_id` (`eksemplar_id`),
KEY `status` (`status`),
KEY `tanggal_jatuh_tempo` (`tanggal_jatuh_tempo`),
CONSTRAINT `peminjaman_ibfk_1` FOREIGN KEY (`anggota_id`) REFERENCES `anggota_perpus` (`id`),
CONSTRAINT `peminjaman_ibfk_2` FOREIGN KEY (`eksemplar_id`) REFERENCES `buku_eksemplar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `peminjaman`
--
LOCK TABLES `peminjaman` WRITE;
/*!40000 ALTER TABLE `peminjaman` DISABLE KEYS */;
/*!40000 ALTER TABLE `peminjaman` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `penanda_baca`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `penanda_baca` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`siswa_id` int(10) unsigned NOT NULL,
`buku_id` int(10) unsigned NOT NULL,
`halaman` int(10) unsigned NOT NULL,
`catatan` varchar(255) DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_penanda` (`siswa_id`,`buku_id`),
KEY `fk_pb_buku` (`buku_id`),
CONSTRAINT `fk_pb_buku` FOREIGN KEY (`buku_id`) REFERENCES `buku` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_pb_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `penanda_baca`
--
LOCK TABLES `penanda_baca` WRITE;
/*!40000 ALTER TABLE `penanda_baca` DISABLE KEYS */;
/*!40000 ALTER TABLE `penanda_baca` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `pengumuman`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `pengumuman` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`judul` varchar(200) DEFAULT NULL,
`isi` longtext DEFAULT NULL,
`gambar` varchar(255) DEFAULT NULL,
`target` set('SEMUA','GURU','SISWA','ORTU','TU') DEFAULT 'SEMUA',
`kelas_id` smallint(5) unsigned DEFAULT NULL COMMENT 'khusus kelas tertentu',
`prioritas` enum('BIASA','PENTING','URGENT') DEFAULT 'BIASA',
`mulai_tampil` datetime DEFAULT NULL,
`selesai_tampil` datetime DEFAULT NULL,
`kirim_wa` tinyint(1) DEFAULT 0,
`kirim_push` tinyint(1) DEFAULT 1,
`dibuat_oleh` int(10) unsigned DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `mulai_tampil` (`mulai_tampil`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pengumuman`
--
LOCK TABLES `pengumuman` WRITE;
/*!40000 ALTER TABLE `pengumuman` DISABLE KEYS */;
/*!40000 ALTER TABLE `pengumuman` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `perizinan`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `perizinan` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pemohon_tipe` enum('SISWA','GURU') NOT NULL,
`pemohon_id` int(10) unsigned NOT NULL,
`diajukan_oleh` int(10) unsigned DEFAULT 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 DEFAULT NULL,
`file_bukti` varchar(255) DEFAULT NULL,
`status` enum('MENUNGGU','DISETUJUI','DITOLAK') DEFAULT 'MENUNGGU',
`diproses_oleh` int(10) unsigned DEFAULT NULL,
`catatan_admin` varchar(200) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `pemohon_tipe` (`pemohon_tipe`,`pemohon_id`),
KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `perizinan`
--
LOCK TABLES `perizinan` WRITE;
/*!40000 ALTER TABLE `perizinan` DISABLE KEYS */;
/*!40000 ALTER TABLE `perizinan` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `permissions`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `permissions` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(80) NOT NULL COMMENT 'contoh: absensi.rekap.view',
`modul` varchar(40) DEFAULT NULL,
`nama` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `permissions`
--
LOCK TABLES `permissions` WRITE;
/*!40000 ALTER TABLE `permissions` DISABLE KEYS */;
INSERT INTO `permissions` VALUES
(1,'master.siswa.view','master','Lihat data siswa'),
(2,'master.siswa.manage','master','Kelola data siswa'),
(3,'master.guru.manage','master','Kelola data guru'),
(4,'master.kelas.manage','master','Kelola kelas & rombel'),
(5,'master.mapel.manage','master','Kelola mata pelajaran'),
(6,'master.jadwal.manage','master','Mengatur jadwal pelajaran'),
(7,'absensi.view','absensi','Lihat absensi'),
(8,'absensi.input','absensi','Input/koreksi absensi'),
(9,'absensi.device','absensi','Kelola mesin RFID'),
(10,'absensi.rekap','absensi','Rekap & ekspor absensi'),
(11,'admguru.own','admguru','Kelola administrasi sendiri'),
(12,'admguru.approve','admguru','Setujui perangkat ajar'),
(13,'admguru.all','admguru','Lihat semua administrasi guru'),
(14,'elearning.manage','elearning','Kelola materi & tugas'),
(15,'elearning.learn','elearning','Ikut pembelajaran'),
(16,'ujian.manage','ujian','Kelola ujian & bank soal'),
(17,'ujian.ikut','ujian','Mengerjakan ujian'),
(18,'game.manage','game','Kelola game edukasi'),
(19,'game.main','game','Memainkan game'),
(20,'perpus.manage','perpus','Kelola koleksi & sirkulasi'),
(21,'perpus.pinjam','perpus','Meminjam buku'),
(22,'literasi.input','literasi','Isi jurnal literasi'),
(23,'literasi.verifikasi','literasi','Verifikasi jurnal literasi'),
(24,'tu.surat','tu','Kelola persuratan'),
(25,'tu.inventaris','tu','Kelola inventaris'),
(26,'tu.keuangan','tu','Kelola tagihan & pembayaran'),
(27,'nilai.input','nilai','Input nilai'),
(28,'rapor.kelola','rapor','Kelola e-rapor'),
(29,'rapor.cetak','rapor','Cetak/unduh rapor'),
(30,'rapor.lihat','rapor','Lihat rapor anak/sendiri'),
(31,'dapodik.sync','dapodik','Sinkronisasi Dapodik/EMIS'),
(32,'ai.gunakan','ai','Menggunakan asisten AI'),
(33,'laporan.sekolah','laporan','Dashboard & laporan sekolah'),
(34,'setting.manage','setting','Pengaturan sistem'),
(35,'yayasan.monitor','yayasan','Memantau kinerja sekolah'),
(36,'yayasan.catatan','yayasan','Membuat catatan pemantauan'),
(37,'keuangan.verifikasi','keuangan','Memverifikasi pembayaran daring'),
(38,'kas.view','keuangan','Melihat kas sekolah'),
(39,'kas.manage','keuangan','Mencatat kas sekolah'),
(41,'smk.jurusan','smk','Kelola jurusan dan industri mitra'),
(42,'pkl.kelola','smk','Kelola penempatan PKL'),
(43,'pkl.nilai','smk','Menilai dan memverifikasi PKL'),
(44,'ukk.kelola','smk','Kelola uji kompetensi keahlian'),
(45,'ukk.nilai','smk','Menilai uji kompetensi keahlian'),
(46,'literasi.hadiah','perpus','Kelola hadiah literasi'),
(47,'literasi.klaim','perpus','Verifikasi penukaran poin'),
(48,'perpus.buku','perpus','Menambah dan menyunting koleksi buku');
/*!40000 ALTER TABLE `permissions` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `pesan`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `pesan` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`dari_user_id` int(10) unsigned NOT NULL,
`ke_user_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned DEFAULT NULL COMMENT 'konteks anak yang dibahas',
`isi` text DEFAULT NULL,
`lampiran` varchar(255) DEFAULT NULL,
`dibaca` tinyint(1) DEFAULT 0,
`dibaca_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `dari_user_id` (`dari_user_id`,`ke_user_id`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pesan`
--
LOCK TABLES `pesan` WRITE;
/*!40000 ALTER TABLE `pesan` DISABLE KEYS */;
/*!40000 ALTER TABLE `pesan` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `pkl`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `pkl` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`industri_id` int(10) unsigned NOT NULL,
`pembimbing_sekolah_id` int(10) unsigned DEFAULT NULL,
`pembimbing_industri` varchar(120) DEFAULT NULL,
`jabatan_pembimbing` varchar(80) DEFAULT NULL,
`bagian` varchar(120) DEFAULT NULL COMMENT 'Unit atau divisi penempatan',
`mulai` date NOT NULL,
`selesai` date NOT NULL,
`no_surat` varchar(60) DEFAULT NULL,
`status` enum('DIAJUKAN','BERJALAN','SELESAI','DIBATALKAN') NOT NULL DEFAULT 'DIAJUKAN',
`nilai_industri` decimal(5,2) DEFAULT NULL,
`nilai_sekolah` decimal(5,2) DEFAULT NULL,
`nilai_akhir` decimal(5,2) DEFAULT NULL,
`predikat` varchar(20) DEFAULT NULL,
`catatan_industri` text DEFAULT NULL,
`catatan_sekolah` text DEFAULT NULL,
`sertifikat` varchar(255) DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_pkl` (`tahun_ajaran_id`,`siswa_id`,`industri_id`),
KEY `idx_status` (`status`,`mulai`),
KEY `fk_pkl_siswa` (`siswa_id`),
KEY `fk_pkl_industri` (`industri_id`),
KEY `fk_pkl_guru` (`pembimbing_sekolah_id`),
CONSTRAINT `fk_pkl_guru` FOREIGN KEY (`pembimbing_sekolah_id`) REFERENCES `guru` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_pkl_industri` FOREIGN KEY (`industri_id`) REFERENCES `industri` (`id`),
CONSTRAINT `fk_pkl_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pkl`
--
LOCK TABLES `pkl` WRITE;
/*!40000 ALTER TABLE `pkl` DISABLE KEYS */;
/*!40000 ALTER TABLE `pkl` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `pkl_absensi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `pkl_absensi` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`pkl_id` int(10) unsigned NOT NULL,
`tanggal` date NOT NULL,
`status` enum('HADIR','SAKIT','IZIN','ALPA') NOT NULL DEFAULT 'HADIR',
`keterangan` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_absen_pkl` (`pkl_id`,`tanggal`),
CONSTRAINT `fk_pa_pkl` FOREIGN KEY (`pkl_id`) REFERENCES `pkl` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pkl_absensi`
--
LOCK TABLES `pkl_absensi` WRITE;
/*!40000 ALTER TABLE `pkl_absensi` DISABLE KEYS */;
/*!40000 ALTER TABLE `pkl_absensi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `pkl_jurnal`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `pkl_jurnal` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`pkl_id` int(10) unsigned NOT NULL,
`tanggal` date NOT NULL,
`jam_masuk` time DEFAULT NULL,
`jam_pulang` time DEFAULT NULL,
`kegiatan` text NOT NULL,
`kendala` text DEFAULT NULL,
`kompetensi` varchar(255) DEFAULT NULL COMMENT 'Kompetensi yang dilatih hari itu',
`foto` varchar(255) DEFAULT NULL,
`status` enum('MENUNGGU','DISETUJUI','DITOLAK') NOT NULL DEFAULT 'MENUNGGU',
`catatan_pembimbing` text DEFAULT NULL,
`diverifikasi_oleh` int(10) unsigned DEFAULT NULL,
`diverifikasi_at` datetime DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_jurnal_pkl` (`pkl_id`,`tanggal`),
CONSTRAINT `fk_pj_pkl` FOREIGN KEY (`pkl_id`) REFERENCES `pkl` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `pkl_jurnal`
--
LOCK TABLES `pkl_jurnal` WRITE;
/*!40000 ALTER TABLE `pkl_jurnal` DISABLE KEYS */;
/*!40000 ALTER TABLE `pkl_jurnal` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `poin_siswa`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `poin_siswa` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`siswa_id` int(10) unsigned NOT NULL,
`sumber` enum('MATERI','TUGAS','UJIAN','GAME','LITERASI','ABSENSI','PERILAKU','MANUAL') NOT NULL,
`ref_id` bigint(20) unsigned DEFAULT NULL,
`poin` smallint(6) NOT NULL,
`keterangan` varchar(150) DEFAULT NULL,
`tanggal` date NOT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `siswa_id` (`siswa_id`,`tanggal`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `poin_siswa`
--
LOCK TABLES `poin_siswa` WRITE;
/*!40000 ALTER TABLE `poin_siswa` DISABLE KEYS */;
/*!40000 ALTER TABLE `poin_siswa` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `prestasi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `prestasi` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`siswa_id` int(10) unsigned NOT NULL,
`tahun_ajaran_id` smallint(5) unsigned DEFAULT NULL,
`nama_lomba` varchar(200) DEFAULT NULL,
`tingkat` enum('SEKOLAH','KECAMATAN','KABUPATEN','PROVINSI','NASIONAL','INTERNASIONAL') DEFAULT NULL,
`peringkat` varchar(40) DEFAULT NULL,
`tanggal` date DEFAULT NULL,
`penyelenggara` varchar(150) DEFAULT NULL,
`file_sertifikat` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `siswa_id` (`siswa_id`),
CONSTRAINT `prestasi_ibfk_1` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `prestasi`
--
LOCK TABLES `prestasi` WRITE;
/*!40000 ALTER TABLE `prestasi` DISABLE KEYS */;
/*!40000 ALTER TABLE `prestasi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `promes`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `promes` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`prota_id` int(10) unsigned NOT NULL,
`semester` enum('1','2') NOT NULL,
`status` enum('DRAFT','DIAJUKAN','DISETUJUI','REVISI') DEFAULT 'DRAFT',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `prota_id` (`prota_id`),
CONSTRAINT `promes_ibfk_1` FOREIGN KEY (`prota_id`) REFERENCES `prota` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `promes`
--
LOCK TABLES `promes` WRITE;
/*!40000 ALTER TABLE `promes` DISABLE KEYS */;
/*!40000 ALTER TABLE `promes` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `promes_detail`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `promes_detail` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`promes_id` int(10) unsigned NOT NULL,
`tp_id` int(10) unsigned DEFAULT NULL,
`materi` varchar(250) DEFAULT NULL,
`alokasi_jp` smallint(6) DEFAULT NULL,
`bulan` tinyint(4) DEFAULT NULL COMMENT '1..12',
`minggu_ke` tinyint(4) DEFAULT NULL COMMENT '1..5',
`keterangan` varchar(120) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `promes_id` (`promes_id`),
CONSTRAINT `promes_detail_ibfk_1` FOREIGN KEY (`promes_id`) REFERENCES `promes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `promes_detail`
--
LOCK TABLES `promes_detail` WRITE;
/*!40000 ALTER TABLE `promes_detail` DISABLE KEYS */;
/*!40000 ALTER TABLE `promes_detail` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `prota`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `prota` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`tingkat` tinyint(4) NOT NULL,
`fase` enum('A','B','C','D','E','F') DEFAULT NULL,
`kurikulum` enum('MERDEKA','KBC','K13') DEFAULT 'MERDEKA',
`total_jp` smallint(6) DEFAULT NULL,
`minggu_efektif_1` tinyint(4) DEFAULT NULL,
`minggu_efektif_2` tinyint(4) DEFAULT NULL,
`status` enum('DRAFT','DIAJUKAN','DISETUJUI','REVISI') DEFAULT 'DRAFT',
`catatan_kepsek` text DEFAULT NULL,
`disetujui_oleh` int(10) unsigned DEFAULT NULL,
`disetujui_at` datetime DEFAULT NULL,
`sumber_ai` tinyint(1) DEFAULT 0,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `guru_id` (`guru_id`),
KEY `mapel_id` (`mapel_id`),
CONSTRAINT `prota_ibfk_1` FOREIGN KEY (`guru_id`) REFERENCES `guru` (`id`) ON DELETE CASCADE,
CONSTRAINT `prota_ibfk_2` FOREIGN KEY (`mapel_id`) REFERENCES `mapel` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `prota`
--
LOCK TABLES `prota` WRITE;
/*!40000 ALTER TABLE `prota` DISABLE KEYS */;
/*!40000 ALTER TABLE `prota` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `prota_detail`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `prota_detail` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`prota_id` int(10) unsigned NOT NULL,
`semester` enum('1','2') DEFAULT NULL,
`lingkup_materi` varchar(200) DEFAULT NULL,
`tp_id` int(10) unsigned DEFAULT NULL,
`uraian` text DEFAULT NULL,
`alokasi_jp` smallint(6) DEFAULT NULL,
`urutan` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `prota_id` (`prota_id`),
CONSTRAINT `prota_detail_ibfk_1` FOREIGN KEY (`prota_id`) REFERENCES `prota` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `prota_detail`
--
LOCK TABLES `prota_detail` WRITE;
/*!40000 ALTER TABLE `prota_detail` DISABLE KEYS */;
/*!40000 ALTER TABLE `prota_detail` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `rapor`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `rapor` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
`jenis` enum('TENGAH_SEMESTER','AKHIR_SEMESTER','KENAIKAN','KELULUSAN') DEFAULT 'AKHIR_SEMESTER',
`kurikulum` enum('MERDEKA','KBC','K13') DEFAULT 'MERDEKA',
`jumlah_hadir` smallint(6) DEFAULT 0,
`jumlah_sakit` smallint(6) DEFAULT 0,
`jumlah_izin` smallint(6) DEFAULT 0,
`jumlah_alpa` smallint(6) DEFAULT 0,
`tinggi_badan` smallint(6) DEFAULT NULL,
`berat_badan` smallint(6) DEFAULT NULL,
`catatan_wali` text DEFAULT NULL,
`catatan_ai` tinyint(1) DEFAULT 0,
`keputusan` enum('NAIK','TINGGAL','LULUS','-') DEFAULT '-',
`naik_ke_kelas` tinyint(4) DEFAULT NULL,
`peringkat` smallint(6) DEFAULT NULL,
`rata_rata` decimal(5,2) DEFAULT NULL,
`tanggal_terbit` date DEFAULT NULL,
`file_pdf` varchar(255) DEFAULT NULL,
`status` enum('DRAFT','FINAL','TERBIT') DEFAULT 'DRAFT',
`dibuka_ortu` tinyint(1) DEFAULT 0,
`dibuka_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_rapor` (`tahun_ajaran_id`,`siswa_id`,`jenis`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `rapor`
--
LOCK TABLES `rapor` WRITE;
/*!40000 ALTER TABLE `rapor` DISABLE KEYS */;
/*!40000 ALTER TABLE `rapor` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `rekening_sekolah`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `rekening_sekolah` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`bank` varchar(40) NOT NULL,
`no_rekening` varchar(40) NOT NULL,
`atas_nama` varchar(120) NOT NULL,
`jenis` enum('TRANSFER','QRIS','VA') NOT NULL DEFAULT 'TRANSFER',
`gambar_qris` varchar(255) DEFAULT NULL,
`urutan` tinyint(4) NOT NULL DEFAULT 1,
`is_aktif` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `rekening_sekolah`
--
LOCK TABLES `rekening_sekolah` WRITE;
/*!40000 ALTER TABLE `rekening_sekolah` DISABLE KEYS */;
INSERT INTO `rekening_sekolah` VALUES
(1,'Bank Syariah Indonesia','7123456789','Yayasan Al Barokah','TRANSFER',NULL,1,1),
(2,'Bank Jabar Banten','0012345678','SD Islam Al Barokah','TRANSFER',NULL,2,1),
(3,'QRIS','-','SD Islam Al Barokah','QRIS',NULL,3,1);
/*!40000 ALTER TABLE `rekening_sekolah` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `resensi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `resensi` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`buku_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`rating` tinyint(4) DEFAULT NULL,
`isi` text DEFAULT NULL,
`status` enum('MENUNGGU','TERBIT','DITOLAK') DEFAULT 'MENUNGGU',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_resensi` (`buku_id`,`siswa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `resensi`
--
LOCK TABLES `resensi` WRITE;
/*!40000 ALTER TABLE `resensi` DISABLE KEYS */;
/*!40000 ALTER TABLE `resensi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `rfid_device`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `rfid_device` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode_device` varchar(30) NOT NULL,
`nama` varchar(80) DEFAULT NULL,
`lokasi` varchar(100) DEFAULT NULL,
`api_key` varchar(64) NOT NULL,
`mode` enum('SISWA','GURU','PERPUS','KANTIN','UMUM') DEFAULT 'SISWA',
`ip_terakhir` varchar(45) DEFAULT NULL,
`firmware` varchar(20) DEFAULT NULL,
`last_seen` datetime DEFAULT NULL,
`total_tap` int(11) DEFAULT 0,
`status` enum('AKTIF','NONAKTIF','MAINTENANCE') DEFAULT 'AKTIF',
PRIMARY KEY (`id`),
UNIQUE KEY `kode_device` (`kode_device`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `rfid_device`
--
LOCK TABLES `rfid_device` WRITE;
/*!40000 ALTER TABLE `rfid_device` DISABLE KEYS */;
INSERT INTO `rfid_device` VALUES
(1,'RFID-GERBANG-01','Mesin Gerbang Utama','Gerbang depan','dev-key-ganti-ini-1234567890','SISWA',NULL,NULL,NULL,0,'AKTIF'),
(2,'RFID-KANTOR-01','Mesin Ruang Guru','Ruang guru','dev-key-ganti-ini-0987654321','GURU',NULL,NULL,NULL,0,'AKTIF');
/*!40000 ALTER TABLE `rfid_device` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `rfid_kartu`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `rfid_kartu` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` varchar(32) NOT NULL,
`pemilik_tipe` enum('SISWA','GURU','PEGAWAI') NOT NULL,
`pemilik_id` int(10) unsigned NOT NULL,
`no_kartu` varchar(30) DEFAULT NULL,
`tanggal_terbit` date DEFAULT NULL,
`status` enum('AKTIF','HILANG','RUSAK','DIBLOKIR') DEFAULT 'AKTIF',
PRIMARY KEY (`id`),
UNIQUE KEY `uid` (`uid`),
KEY `pemilik_tipe` (`pemilik_tipe`,`pemilik_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `rfid_kartu`
--
LOCK TABLES `rfid_kartu` WRITE;
/*!40000 ALTER TABLE `rfid_kartu` DISABLE KEYS */;
INSERT INTO `rfid_kartu` VALUES
(1,'6100000012','SISWA',12,'KTM-2021001','2024-07-20','AKTIF'),
(2,'6100000013','SISWA',13,'KTM-2021002','2024-07-20','AKTIF'),
(3,'6100000010','SISWA',10,'KTM-2022001','2024-07-20','AKTIF'),
(4,'6100000011','SISWA',11,'KTM-2022002','2024-07-20','AKTIF'),
(5,'6100000007','SISWA',7,'KTM-2023001','2024-07-20','AKTIF'),
(6,'6100000008','SISWA',8,'KTM-2023002','2024-07-20','AKTIF'),
(7,'6100000009','SISWA',9,'KTM-2023003','2024-07-20','AKTIF'),
(8,'6100000004','SISWA',4,'KTM-2024001','2024-07-20','AKTIF'),
(9,'6100000005','SISWA',5,'KTM-2024002','2024-07-20','AKTIF'),
(10,'6100000006','SISWA',6,'KTM-2024003','2024-07-20','AKTIF');
/*!40000 ALTER TABLE `rfid_kartu` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `rfid_log`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `rfid_log` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`device_id` smallint(5) unsigned DEFAULT NULL,
`uid` varchar(32) DEFAULT NULL,
`waktu` datetime NOT NULL,
`hasil` enum('OK','UID_TIDAK_DIKENAL','DUPLIKAT','DILUAR_JAM','KARTU_BLOKIR') DEFAULT 'OK',
`ref_absensi_id` bigint(20) unsigned DEFAULT NULL,
`raw` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `waktu` (`waktu`),
KEY `uid` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `rfid_log`
--
LOCK TABLES `rfid_log` WRITE;
/*!40000 ALTER TABLE `rfid_log` DISABLE KEYS */;
/*!40000 ALTER TABLE `rfid_log` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `role_permission`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `role_permission` (
`role_id` tinyint(3) unsigned NOT NULL,
`permission_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`role_id`,`permission_id`),
KEY `permission_id` (`permission_id`),
CONSTRAINT `role_permission_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
CONSTRAINT `role_permission_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `role_permission`
--
LOCK TABLES `role_permission` WRITE;
/*!40000 ALTER TABLE `role_permission` DISABLE KEYS */;
INSERT INTO `role_permission` VALUES
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,7),
(1,8),
(1,9),
(1,10),
(1,11),
(1,12),
(1,13),
(1,14),
(1,15),
(1,16),
(1,17),
(1,18),
(1,19),
(1,20),
(1,21),
(1,22),
(1,23),
(1,24),
(1,25),
(1,26),
(1,27),
(1,28),
(1,29),
(1,30),
(1,31),
(1,32),
(1,33),
(1,34),
(1,37),
(1,38),
(1,39),
(1,41),
(1,42),
(1,43),
(1,44),
(1,45),
(1,46),
(1,47),
(1,48),
(2,1),
(2,2),
(2,3),
(2,4),
(2,5),
(2,6),
(2,7),
(2,8),
(2,10),
(2,11),
(2,12),
(2,13),
(2,14),
(2,16),
(2,18),
(2,20),
(2,23),
(2,24),
(2,27),
(2,28),
(2,29),
(2,31),
(2,32),
(2,33),
(2,37),
(2,38),
(2,41),
(2,42),
(2,43),
(2,44),
(2,45),
(2,46),
(2,47),
(2,48),
(3,1),
(3,7),
(3,8),
(3,11),
(3,14),
(3,16),
(3,18),
(3,23),
(3,27),
(3,32),
(3,43),
(3,45),
(3,46),
(3,47),
(3,48),
(4,1),
(4,7),
(4,8),
(4,10),
(4,11),
(4,14),
(4,16),
(4,18),
(4,23),
(4,27),
(4,28),
(4,29),
(4,32),
(4,43),
(4,45),
(4,46),
(4,47),
(4,48),
(5,1),
(5,2),
(5,3),
(5,4),
(5,6),
(5,7),
(5,9),
(5,10),
(5,24),
(5,25),
(5,26),
(5,29),
(5,37),
(5,38),
(5,39),
(5,42),
(5,44),
(6,1),
(6,20),
(6,21),
(6,23),
(6,46),
(6,47),
(6,48),
(7,15),
(7,17),
(7,19),
(7,21),
(7,22),
(7,30),
(8,7),
(8,30),
(9,2),
(9,3),
(9,4),
(9,6),
(9,29),
(9,31),
(9,42),
(9,44),
(10,1),
(10,7),
(10,10),
(10,29),
(10,33),
(10,35),
(10,36),
(10,38);
/*!40000 ALTER TABLE `role_permission` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `roles`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `roles` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(30) NOT NULL,
`nama` varchar(60) NOT NULL,
`level` tinyint(4) DEFAULT 5 COMMENT '1=superadmin ... 9=terbatas',
`keterangan` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `roles`
--
LOCK TABLES `roles` WRITE;
/*!40000 ALTER TABLE `roles` DISABLE KEYS */;
INSERT INTO `roles` VALUES
(1,'SUPERADMIN','Super Admin',1,'Akses penuh sistem'),
(2,'KEPALA_SEKOLAH','Kepala Sekolah',2,'Monitoring, approval, laporan'),
(3,'GURU','Guru',4,'Mengajar, adm guru, nilai'),
(4,'WALI_KELAS','Wali Kelas',3,'Guru + kelola rapor kelasnya'),
(5,'TU','Tata Usaha',3,'Administrasi, keuangan, surat, inventaris'),
(6,'PUSTAKAWAN','Pustakawan',4,'Kelola perpustakaan & literasi'),
(7,'SISWA','Siswa',7,'E-learning, ujian, game, perpus digital'),
(8,'ORTU','Orang Tua',7,'Monitoring anak via aplikasi mobile'),
(9,'OPERATOR','Operator Dapodik',3,'Sinkronisasi data & e-rapor'),
(10,'YAYASAN','Pengurus Yayasan',2,'Pemantauan kinerja sekolah, tanpa akses operasional harian');
/*!40000 ALTER TABLE `roles` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `sekolah`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `sekolah` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`npsn` varchar(20) DEFAULT NULL,
`nss` varchar(30) DEFAULT NULL,
`nama` varchar(150) NOT NULL,
`jenjang` enum('SD','MI','SMP','MTS','SMA','MA','SMK','MAK') 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) DEFAULT NULL,
`alamat` text DEFAULT NULL,
`desa` varchar(80) DEFAULT NULL,
`kecamatan` varchar(80) DEFAULT NULL,
`kabupaten` varchar(80) DEFAULT NULL,
`provinsi` varchar(80) DEFAULT NULL,
`kode_pos` varchar(10) DEFAULT NULL,
`telepon` varchar(30) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`website` varchar(100) DEFAULT NULL,
`kepala_sekolah` varchar(120) DEFAULT NULL,
`nip_kepala` varchar(30) DEFAULT NULL,
`logo` varchar(255) DEFAULT NULL,
`kop_surat` varchar(255) DEFAULT NULL,
`stempel` varchar(255) DEFAULT NULL,
`ttd_kepala` varchar(255) DEFAULT NULL,
`latitude` decimal(10,7) DEFAULT NULL,
`longitude` decimal(10,7) DEFAULT NULL,
`radius_absen` smallint(6) DEFAULT 150 COMMENT 'meter, untuk absen GPS guru',
`created_at` datetime DEFAULT current_timestamp(),
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `npsn` (`npsn`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `sekolah`
--
LOCK TABLES `sekolah` WRITE;
/*!40000 ALTER TABLE `sekolah` DISABLE KEYS */;
INSERT INTO `sekolah` VALUES
(1,'20200002',NULL,'SMP Islam Al Barokah','SMP','KEMENDIKDASMEN','MERDEKA','SWASTA',NULL,'Jl. Raya Cileungsi No. 1, Cileungsi','Cileungsi Kidul','Cileungsi','Bogor','Jawa Barat','16820','021-8230001','info@smpislamalbarokah.sch.id','https://smpislamalbarokah.sch.id','Drs. H. Muhammad Yusuf, M.Pd.','196805121994031005',NULL,NULL,NULL,NULL,NULL,NULL,150,'2026-09-08 23:22:21','2026-09-08 23:22:21');
/*!40000 ALTER TABLE `sekolah` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `setting`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `setting` (
`skey` varchar(80) NOT NULL,
`svalue` text DEFAULT NULL,
`grup` varchar(40) DEFAULT 'umum',
`tipe` enum('text','number','bool','json','file') DEFAULT 'text',
`label` varchar(150) DEFAULT NULL,
PRIMARY KEY (`skey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `setting`
--
LOCK TABLES `setting` WRITE;
/*!40000 ALTER TABLE `setting` DISABLE KEYS */;
INSERT INTO `setting` VALUES
('ai_aktif','1','ai','bool','Aktifkan asisten AI'),
('ai_kuota_guru','200','ai','number','Batas request AI per guru per bulan'),
('ai_model','claude-sonnet-4-6','ai','text','Model default'),
('app_nama','SIMAP','umum','text','Nama aplikasi'),
('app_tagline','Sistem Informasi Manajemen Pendidikan','umum','text','Tagline'),
('batas_terlambat','07:15','absensi','text','Batas dianggap terlambat'),
('bayar_batas_jam','24','keuangan','number','Batas waktu pembayaran (jam)'),
('bayar_instruksi','Transfer sesuai nominal, lalu unggah bukti pembayaran pada aplikasi. Konfirmasi diproses maksimal 1 hari kerja.','keuangan','','Instruksi pembayaran'),
('bayar_online_aktif','1','keuangan','bool','Aktifkan pembayaran lewat aplikasi'),
('bobot_formatif','40','nilai','number','Bobot rata formatif (%)'),
('bobot_sas','15','nilai','number','Bobot SAS (%)'),
('bobot_sts','15','nilai','number','Bobot STS (%)'),
('bobot_sumatif','30','nilai','number','Bobot rata sumatif lingkup (%)'),
('denda_perpus_per_hari','500','perpus','number','Denda keterlambatan per hari (Rp)'),
('gateway_aktif','0','keuangan','bool','Gunakan payment gateway'),
('gateway_key','','keuangan','text','Kunci server payment gateway'),
('gateway_nama','','keuangan','text','Nama payment gateway'),
('jam_masuk','07:00','absensi','text','Jam masuk sekolah'),
('jam_pulang','12:55','absensi','text','Jam pulang'),
('jenjang','SMP','sekolah','text','Jenjang sekolah: SD, SMP, atau SMA'),
('kkm_default','70','nilai','number','KKM/KKTP default'),
('lama_pinjam_hari','7','perpus','number','Lama peminjaman (hari)'),
('maks_pinjam_siswa','2','perpus','number','Maksimal buku dipinjam siswa'),
('notif_wa_absen','1','notifikasi','bool','Kirim WA saat siswa masuk/tidak hadir'),
('notif_wa_jam','07:30','notifikasi','text','Jam kirim rekap ketidakhadiran'),
('pkl_durasi_bulan','6','sekolah','number','Lama PKL dalam bulan'),
('pkl_min_jurnal','20','sekolah','number','Jurnal minimal agar PKL dinyatakan selesai'),
('poin_hadir','5','gamifikasi','number','Poin hadir tepat waktu'),
('poin_literasi','20','gamifikasi','number','Poin jurnal literasi diverifikasi'),
('poin_materi','10','gamifikasi','number','Poin menuntaskan materi'),
('template_wa_alpa','Assalamualaikum Bpk/Ibu {ortu}. Hingga pukul {jam}, ananda *{siswa}* ({kelas}) belum tercatat hadir di sekolah. Mohon konfirmasinya. - {sekolah}','notifikasi','text','Template WA ketidakhadiran'),
('template_wa_hadir','Assalamualaikum Bpk/Ibu {ortu}. Ananda *{siswa}* ({kelas}) telah tiba di sekolah pukul {jam}. Terima kasih. - {sekolah}','notifikasi','text','Template WA kehadiran'),
('tingkat_max','9','sekolah','number','Kelas tertinggi'),
('tingkat_min','7','sekolah','number','Kelas terendah'),
('toleransi_tap_menit','3','absensi','number','Jeda minimal antar tap kartu (menit)'),
('ukk_bobot_industri','60','sekolah','number','Bobot nilai penguji industri (persen)'),
('wa_gateway_key','ubah-kunci-ini','notifikasi','text','API key gateway WhatsApp'),
('wa_gateway_url','http://localhost:3005/send','notifikasi','text','Endpoint gateway WhatsApp (Baileys)');
/*!40000 ALTER TABLE `setting` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `siswa`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `siswa` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nisn` varchar(15) DEFAULT NULL,
`nis` varchar(20) DEFAULT NULL,
`nik` varchar(20) DEFAULT NULL,
`no_kk` varchar(20) DEFAULT NULL,
`no_akta` varchar(40) DEFAULT NULL,
`nama` varchar(120) NOT NULL,
`nama_panggilan` varchar(40) DEFAULT NULL,
`jenis_kelamin` enum('L','P') NOT NULL,
`tempat_lahir` varchar(60) DEFAULT NULL,
`tanggal_lahir` date DEFAULT NULL,
`agama` varchar(20) DEFAULT NULL,
`kewarganegaraan` varchar(30) DEFAULT 'WNI',
`anak_ke` tinyint(4) DEFAULT NULL,
`jumlah_saudara` tinyint(4) DEFAULT NULL,
`alamat` text DEFAULT NULL,
`rt` varchar(4) DEFAULT NULL,
`rw` varchar(4) DEFAULT NULL,
`desa` varchar(80) DEFAULT NULL,
`kecamatan` varchar(80) DEFAULT NULL,
`kabupaten` varchar(80) DEFAULT NULL,
`provinsi` varchar(80) DEFAULT NULL,
`kode_pos` varchar(10) DEFAULT NULL,
`jarak_rumah_km` decimal(5,2) DEFAULT NULL,
`transportasi` varchar(40) DEFAULT NULL,
`tinggal_bersama` varchar(40) DEFAULT 'Orang Tua',
`golongan_darah` varchar(3) DEFAULT NULL,
`tinggi_badan` smallint(6) DEFAULT NULL,
`berat_badan` smallint(6) DEFAULT NULL,
`riwayat_kesehatan` text DEFAULT NULL COMMENT 'alergi / catatan khusus',
`kebutuhan_khusus` varchar(60) DEFAULT NULL,
`asal_tk` varchar(120) DEFAULT NULL,
`tanggal_masuk` date DEFAULT NULL,
`no_kip` varchar(30) DEFAULT NULL,
`no_pkh` varchar(30) DEFAULT NULL,
`penerima_kip` tinyint(1) DEFAULT 0,
`foto` varchar(255) DEFAULT NULL,
`rfid_uid` varchar(32) DEFAULT NULL,
`status` enum('AKTIF','LULUS','PINDAH','KELUAR','MENGULANG') DEFAULT 'AKTIF',
`jurusan_id` smallint(5) unsigned DEFAULT NULL,
`tanggal_keluar` date DEFAULT NULL,
`alasan_keluar` varchar(150) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `nisn` (`nisn`),
UNIQUE KEY `nis` (`nis`),
UNIQUE KEY `rfid_uid` (`rfid_uid`),
KEY `nama` (`nama`),
KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `siswa`
--
LOCK TABLES `siswa` WRITE;
/*!40000 ALTER TABLE `siswa` DISABLE KEYS */;
INSERT INTO `siswa` VALUES
(1,'0123456781','2601001',NULL,NULL,NULL,'Muhammad Rafi Pratama','Rafi','L','Bogor','2016-03-11','Islam','WNI',NULL,NULL,'Kp. Cileungsi RT 02/05',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Orang Tua',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,'5A6B7C01','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(2,'0123456782','2601002',NULL,NULL,NULL,'Aisyah Putri Ramadhani','Aisyah','P','Bogor','2016-06-21','Islam','WNI',NULL,NULL,'Perum Griya Asri Blok C',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Orang Tua',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,'5A6B7C02','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(3,'0123456783','2601003',NULL,NULL,NULL,'Bagas Dwi Saputra','Bagas','L','Bekasi','2016-01-09','Islam','WNI',NULL,NULL,'Jl. Mawar No. 12',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'Orang Tua',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,'5A6B7C03','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(4,'0141223301','2024001','3201014203150001','3201010101200001','AL/2015/001','Zahra Aulia Rahmadani','Zahra','P','Bogor','2015-03-02','Islam','Indonesia',1,2,'Perum Griya Asri Blok C2 No 14','03','07','Cileungsi Kidul','Cileungsi','Bogor','Jawa Barat','16820',2.50,'Antar jemput','Orang tua','A',132,29,NULL,NULL,'TK Islam Al Barokah','2024-07-15',NULL,NULL,0,NULL,'6100000004','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(5,'0141223302','2024002','3201011505150002','3201010101200002','AL/2015/002','Muhammad Faiz Alfarizi','Faiz','L','Bogor','2015-05-15','Islam','Indonesia',2,3,'Kp. Cipeucang RT 02','02','05','Cileungsi','Cileungsi','Bogor','Jawa Barat','16820',1.20,'Jalan kaki','Orang tua','O',135,31,NULL,NULL,'RA Nurul Iman','2024-07-15','6011223344',NULL,1,NULL,'6100000005','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(6,'0141223303','2024003','3201012208150003','3201010101200003','AL/2015/003','Nayla Syakira Putri','Nayla','P','Jakarta','2015-08-22','Islam','Indonesia',1,1,'Jl. Raya Narogong KM 12','01','03','Limusnunggal','Cileungsi','Bogor','Jawa Barat','16820',4.00,'Kendaraan pribadi','Orang tua','B',130,27,'Alergi debu','Perlu duduk di depan','TK Tunas Bangsa','2024-07-15',NULL,NULL,0,NULL,'6100000006','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(7,'0131223304','2023001','3201010911140004','3201010101200004','AL/2014/004','Rafi Ahmad Maulana','Rafi','L','Bogor','2014-11-09','Islam','Indonesia',3,4,'Kp. Bojong RT 04','04','02','Gandoang','Cileungsi','Bogor','Jawa Barat','16820',3.10,'Sepeda','Orang tua','A',140,34,NULL,NULL,'TK Pertiwi','2023-07-17','6011223355',NULL,1,NULL,'6100000007','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(8,'0131223305','2023002','3201010102140005','3201010101200005','AL/2014/005','Aisyah Nur Fadhilah','Aisyah','P','Bogor','2014-02-01','Islam','Indonesia',1,2,'Perum Cileungsi Hijau Blok D5','05','08','Cileungsi Kidul','Cileungsi','Bogor','Jawa Barat','16820',1.80,'Antar jemput','Orang tua','AB',138,32,NULL,NULL,'TK Islam Al Barokah','2023-07-17',NULL,NULL,0,NULL,'6100000008','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(9,'0131223306','2023003','3201011807140006','3201010101200006','AL/2014/006','Bagas Dwi Saputra','Bagas','L','Bekasi','2014-07-18','Islam','Indonesia',2,2,'Jl. Mekarsari No 8','02','04','Mekarsari','Cileungsi','Bogor','Jawa Barat','16820',5.20,'Angkutan umum','Nenek','O',142,36,NULL,NULL,'TK Harapan Bunda','2023-07-17','6011223366',NULL,1,NULL,'6100000009','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(10,'0121223307','2022001','3201012503130007','3201010101200007','AL/2013/007','Keisha Ramadhani','Keisha','P','Bogor','2013-03-25','Islam','Indonesia',1,3,'Kp. Pasir Angin RT 01','01','06','Pasir Angin','Cileungsi','Bogor','Jawa Barat','16820',2.20,'Jalan kaki','Orang tua','B',145,38,NULL,NULL,'RA Al Hidayah','2022-07-18',NULL,NULL,0,NULL,'6100000010','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(11,'0121223308','2022002','3201011209130008','3201010101200008','AL/2013/008','Arka Pratama Wijaya','Arka','L','Bogor','2013-09-12','Islam','Indonesia',1,1,'Perum Metland Blok B12','03','09','Cileungsi Kidul','Cileungsi','Bogor','Jawa Barat','16820',3.60,'Kendaraan pribadi','Orang tua','A',148,41,NULL,NULL,'TK Kristal','2022-07-18',NULL,NULL,0,NULL,'6100000011','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(12,'0111223309','2021001','3201010506120009','3201010101200009','AL/2012/009','Salma Khairunnisa','Salma','P','Bogor','2012-06-05','Islam','Indonesia',2,3,'Kp. Cikuda RT 03','03','01','Wanaherang','Gunung Putri','Bogor','Jawa Barat','16965',6.50,'Antar jemput','Orang tua','O',150,43,NULL,NULL,'TK Aisyiyah','2021-07-19','6011223377',NULL,1,NULL,'6100000012','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21'),
(13,'0111223310','2021002','3201012811120010','3201010101200010','AL/2012/010','Daffa Kurniawan','Daffa','L','Bogor','2012-11-28','Islam','Indonesia',1,2,'Jl. Anggrek Raya No 22','04','07','Cileungsi','Cileungsi','Bogor','Jawa Barat','16820',1.50,'Sepeda','Orang tua','B',152,45,'Asma ringan','Hindari aktivitas berat berkepanjangan','TK Islam Al Barokah','2021-07-19',NULL,NULL,0,NULL,'6100000013','AKTIF',NULL,NULL,NULL,'2026-09-08 23:22:21','2026-09-08 23:22:21');
/*!40000 ALTER TABLE `siswa` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `siswa_ortu`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `siswa_ortu` (
`siswa_id` int(10) unsigned NOT NULL,
`ortu_id` int(10) unsigned NOT NULL,
`is_utama` tinyint(1) DEFAULT 0 COMMENT 'penerima notifikasi utama',
PRIMARY KEY (`siswa_id`,`ortu_id`),
KEY `ortu_id` (`ortu_id`),
CONSTRAINT `siswa_ortu_ibfk_1` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE,
CONSTRAINT `siswa_ortu_ibfk_2` FOREIGN KEY (`ortu_id`) REFERENCES `ortu` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `siswa_ortu`
--
LOCK TABLES `siswa_ortu` WRITE;
/*!40000 ALTER TABLE `siswa_ortu` DISABLE KEYS */;
INSERT INTO `siswa_ortu` VALUES
(1,1,1),
(1,2,0),
(2,3,1),
(4,18,1),
(4,19,0),
(5,20,1),
(5,21,0),
(6,22,1),
(6,23,0),
(7,12,1),
(7,13,0),
(8,14,1),
(8,15,0),
(9,16,1),
(9,17,0),
(10,8,1),
(10,9,0),
(11,10,1),
(11,11,0),
(12,4,1),
(12,5,0),
(13,6,1),
(13,7,0);
/*!40000 ALTER TABLE `siswa_ortu` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `supervisi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `supervisi` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned DEFAULT NULL,
`guru_id` int(10) unsigned NOT NULL,
`supervisor_id` int(10) unsigned NOT NULL,
`tanggal` date DEFAULT NULL,
`jenis` enum('ADMINISTRASI','KUNJUNGAN_KELAS','KLINIS') DEFAULT 'KUNJUNGAN_KELAS',
`instrumen` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`instrumen`)),
`skor` decimal(5,2) DEFAULT NULL,
`predikat` varchar(20) DEFAULT NULL,
`temuan` text DEFAULT NULL,
`rekomendasi` text DEFAULT NULL,
`tindak_lanjut` text DEFAULT NULL,
`file_lampiran` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `guru_id` (`guru_id`),
CONSTRAINT `supervisi_ibfk_1` FOREIGN KEY (`guru_id`) REFERENCES `guru` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `supervisi`
--
LOCK TABLES `supervisi` WRITE;
/*!40000 ALTER TABLE `supervisi` DISABLE KEYS */;
/*!40000 ALTER TABLE `supervisi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `surat_keluar`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `surat_keluar` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`no_surat` varchar(80) DEFAULT NULL,
`tanggal` date DEFAULT NULL,
`tujuan` varchar(200) DEFAULT NULL,
`perihal` varchar(250) DEFAULT NULL,
`jenis` enum('UNDANGAN','PEMBERITAHUAN','KETERANGAN','TUGAS','PERMOHONAN','LAINNYA') DEFAULT 'PEMBERITAHUAN',
`isi` longtext DEFAULT NULL,
`template_id` smallint(5) unsigned DEFAULT NULL,
`file_pdf` varchar(255) DEFAULT NULL,
`status` enum('DRAFT','MENUNGGU_TTD','TERKIRIM','ARSIP') DEFAULT 'DRAFT',
`ttd_oleh` int(10) unsigned DEFAULT NULL,
`created_by` int(10) unsigned DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `no_surat` (`no_surat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `surat_keluar`
--
LOCK TABLES `surat_keluar` WRITE;
/*!40000 ALTER TABLE `surat_keluar` DISABLE KEYS */;
/*!40000 ALTER TABLE `surat_keluar` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `surat_masuk`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `surat_masuk` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`no_agenda` varchar(30) DEFAULT NULL,
`no_surat` varchar(80) DEFAULT NULL,
`tanggal_surat` date DEFAULT NULL,
`tanggal_terima` date DEFAULT NULL,
`pengirim` varchar(150) DEFAULT NULL,
`perihal` varchar(250) DEFAULT NULL,
`ringkasan` text DEFAULT NULL,
`sifat` enum('BIASA','PENTING','SEGERA','RAHASIA') DEFAULT 'BIASA',
`file_scan` varchar(255) DEFAULT NULL,
`status` enum('BARU','DIDISPOSISI','SELESAI','ARSIP') DEFAULT 'BARU',
`created_by` int(10) unsigned DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `no_agenda` (`no_agenda`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `surat_masuk`
--
LOCK TABLES `surat_masuk` WRITE;
/*!40000 ALTER TABLE `surat_masuk` DISABLE KEYS */;
/*!40000 ALTER TABLE `surat_masuk` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `surat_template`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `surat_template` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`nama` varchar(120) DEFAULT NULL,
`jenis` varchar(40) DEFAULT NULL,
`konten` longtext DEFAULT NULL COMMENT 'placeholder {{nama_siswa}}, {{nisn}}, dst',
`variabel` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`variabel`)),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `surat_template`
--
LOCK TABLES `surat_template` WRITE;
/*!40000 ALTER TABLE `surat_template` DISABLE KEYS */;
/*!40000 ALTER TABLE `surat_template` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `sync_dapodik`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `sync_dapodik` (
`id` int(10) unsigned NOT NULL 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(5) unsigned DEFAULT NULL,
`jumlah_data` int(11) DEFAULT 0,
`jumlah_sukses` int(11) DEFAULT 0,
`jumlah_gagal` int(11) DEFAULT 0,
`file_sumber` varchar(255) DEFAULT NULL,
`response_log` longtext DEFAULT NULL,
`status` enum('ANTRE','PROSES','SUKSES','GAGAL','SEBAGIAN') DEFAULT 'ANTRE',
`dijalankan_oleh` int(10) unsigned DEFAULT NULL,
`mulai_at` datetime DEFAULT NULL,
`selesai_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `sync_dapodik`
--
LOCK TABLES `sync_dapodik` WRITE;
/*!40000 ALTER TABLE `sync_dapodik` DISABLE KEYS */;
/*!40000 ALTER TABLE `sync_dapodik` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tagihan`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `tagihan` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`no_tagihan` varchar(30) DEFAULT NULL,
`tahun_ajaran_id` smallint(5) unsigned DEFAULT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`jenis_id` smallint(5) unsigned DEFAULT NULL,
`bulan` tinyint(4) DEFAULT NULL,
`tahun` year(4) DEFAULT NULL,
`nominal` decimal(12,2) DEFAULT NULL,
`diskon` decimal(12,2) DEFAULT 0.00,
`total` decimal(12,2) DEFAULT NULL,
`terbayar` decimal(12,2) DEFAULT 0.00,
`jatuh_tempo` date DEFAULT NULL,
`status` enum('BELUM','SEBAGIAN','LUNAS','DIBEBASKAN') DEFAULT 'BELUM',
`keterangan` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `no_tagihan` (`no_tagihan`),
KEY `siswa_id` (`siswa_id`,`status`),
CONSTRAINT `tagihan_ibfk_1` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tagihan`
--
LOCK TABLES `tagihan` WRITE;
/*!40000 ALTER TABLE `tagihan` DISABLE KEYS */;
INSERT INTO `tagihan` VALUES
(1,'SPP-202609-0001',1,1,1,9,2026,150000.00,0.00,150000.00,0.00,'2026-09-30','BELUM',NULL),
(2,'SPP-202608-0001',1,1,1,8,2026,150000.00,0.00,150000.00,150000.00,'2026-08-31','LUNAS',NULL),
(3,'SPP-202607-0001',1,1,1,7,2026,150000.00,0.00,150000.00,150000.00,'2026-07-31','LUNAS',NULL),
(4,'SPP-202609-0002',1,2,1,9,2026,150000.00,0.00,150000.00,0.00,'2026-09-30','BELUM',NULL),
(5,'SPP-202608-0002',1,2,1,8,2026,150000.00,0.00,150000.00,150000.00,'2026-08-31','LUNAS',NULL),
(6,'SPP-202607-0002',1,2,1,7,2026,150000.00,0.00,150000.00,150000.00,'2026-07-31','LUNAS',NULL),
(7,'SPP-202609-0003',1,3,1,9,2026,150000.00,0.00,150000.00,0.00,'2026-09-30','BELUM',NULL),
(8,'SPP-202608-0003',1,3,1,8,2026,150000.00,0.00,150000.00,0.00,'2026-08-31','BELUM',NULL),
(9,'SPP-202607-0003',1,3,1,7,2026,150000.00,0.00,150000.00,0.00,'2026-07-31','BELUM',NULL),
(10,'SPP-202609-0004',1,4,1,9,2026,150000.00,0.00,150000.00,0.00,'2026-09-30','BELUM',NULL),
(11,'SPP-202608-0004',1,4,1,8,2026,150000.00,0.00,150000.00,150000.00,'2026-08-31','LUNAS',NULL),
(12,'SPP-202607-0004',1,4,1,7,2026,150000.00,0.00,150000.00,150000.00,'2026-07-31','LUNAS',NULL),
(13,'SPP-202609-0005',1,5,1,9,2026,150000.00,50000.00,100000.00,0.00,'2026-09-30','BELUM',NULL),
(14,'SPP-202608-0005',1,5,1,8,2026,150000.00,50000.00,100000.00,100000.00,'2026-08-31','LUNAS',NULL),
(15,'SPP-202607-0005',1,5,1,7,2026,150000.00,50000.00,100000.00,100000.00,'2026-07-31','LUNAS',NULL),
(16,'SPP-202609-0006',1,6,1,9,2026,150000.00,0.00,150000.00,0.00,'2026-09-30','BELUM',NULL),
(17,'SPP-202608-0006',1,6,1,8,2026,150000.00,0.00,150000.00,0.00,'2026-08-31','BELUM',NULL),
(18,'SPP-202607-0006',1,6,1,7,2026,150000.00,0.00,150000.00,0.00,'2026-07-31','BELUM',NULL),
(19,'SPP-202609-0007',1,7,1,9,2026,150000.00,50000.00,100000.00,0.00,'2026-09-30','BELUM',NULL),
(20,'SPP-202608-0007',1,7,1,8,2026,150000.00,50000.00,100000.00,100000.00,'2026-08-31','LUNAS',NULL),
(21,'SPP-202607-0007',1,7,1,7,2026,150000.00,50000.00,100000.00,100000.00,'2026-07-31','LUNAS',NULL),
(22,'SPP-202609-0008',1,8,1,9,2026,150000.00,0.00,150000.00,0.00,'2026-09-30','BELUM',NULL),
(23,'SPP-202608-0008',1,8,1,8,2026,150000.00,0.00,150000.00,150000.00,'2026-08-31','LUNAS',NULL),
(24,'SPP-202607-0008',1,8,1,7,2026,150000.00,0.00,150000.00,150000.00,'2026-07-31','LUNAS',NULL),
(25,'SPP-202609-0009',1,9,1,9,2026,150000.00,50000.00,100000.00,0.00,'2026-09-30','BELUM',NULL),
(26,'SPP-202608-0009',1,9,1,8,2026,150000.00,50000.00,100000.00,0.00,'2026-08-31','BELUM',NULL),
(27,'SPP-202607-0009',1,9,1,7,2026,150000.00,50000.00,100000.00,0.00,'2026-07-31','BELUM',NULL),
(28,'SPP-202609-0010',1,10,1,9,2026,150000.00,0.00,150000.00,0.00,'2026-09-30','BELUM',NULL),
(29,'SPP-202608-0010',1,10,1,8,2026,150000.00,0.00,150000.00,150000.00,'2026-08-31','LUNAS',NULL),
(30,'SPP-202607-0010',1,10,1,7,2026,150000.00,0.00,150000.00,150000.00,'2026-07-31','LUNAS',NULL),
(31,'SPP-202609-0011',1,11,1,9,2026,150000.00,0.00,150000.00,0.00,'2026-09-30','BELUM',NULL),
(32,'SPP-202608-0011',1,11,1,8,2026,150000.00,0.00,150000.00,150000.00,'2026-08-31','LUNAS',NULL),
(33,'SPP-202607-0011',1,11,1,7,2026,150000.00,0.00,150000.00,150000.00,'2026-07-31','LUNAS',NULL),
(34,'SPP-202609-0012',1,12,1,9,2026,150000.00,50000.00,100000.00,0.00,'2026-09-30','BELUM',NULL),
(35,'SPP-202608-0012',1,12,1,8,2026,150000.00,50000.00,100000.00,0.00,'2026-08-31','BELUM',NULL),
(36,'SPP-202607-0012',1,12,1,7,2026,150000.00,50000.00,100000.00,0.00,'2026-07-31','BELUM',NULL),
(37,'SPP-202609-0013',1,13,1,9,2026,150000.00,0.00,150000.00,0.00,'2026-09-30','BELUM',NULL),
(38,'SPP-202608-0013',1,13,1,8,2026,150000.00,0.00,150000.00,150000.00,'2026-08-31','LUNAS',NULL),
(39,'SPP-202607-0013',1,13,1,7,2026,150000.00,0.00,150000.00,150000.00,'2026-07-31','LUNAS',NULL),
(64,'KEG-2026-0001',1,1,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(65,'KEG-2026-0002',1,2,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(66,'KEG-2026-0003',1,3,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(67,'KEG-2026-0004',1,4,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(68,'KEG-2026-0005',1,5,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(69,'KEG-2026-0006',1,6,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(70,'KEG-2026-0007',1,7,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(71,'KEG-2026-0008',1,8,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(72,'KEG-2026-0009',1,9,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(73,'KEG-2026-0010',1,10,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(74,'KEG-2026-0011',1,11,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(75,'KEG-2026-0012',1,12,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL),
(76,'KEG-2026-0013',1,13,4,NULL,2026,250000.00,0.00,250000.00,0.00,'2026-09-28','BELUM',NULL);
/*!40000 ALTER TABLE `tagihan` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tahun_ajaran`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `tahun_ajaran` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`kode` varchar(12) NOT NULL COMMENT '2025/2026',
`semester` enum('1','2') NOT NULL COMMENT '1=Ganjil 2=Genap',
`tanggal_mulai` date DEFAULT NULL,
`tanggal_selesai` date DEFAULT NULL,
`tanggal_rapor` date DEFAULT NULL,
`is_aktif` tinyint(1) DEFAULT 0,
`is_locked` tinyint(1) DEFAULT 0 COMMENT 'kunci setelah rapor terbit',
PRIMARY KEY (`id`),
UNIQUE KEY `uq_ta` (`kode`,`semester`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tahun_ajaran`
--
LOCK TABLES `tahun_ajaran` WRITE;
/*!40000 ALTER TABLE `tahun_ajaran` DISABLE KEYS */;
INSERT INTO `tahun_ajaran` VALUES
(1,'2026/2027','1','2026-07-13','2026-12-19',NULL,1,0),
(2,'2026/2027','2','2027-01-05','2027-06-20',NULL,0,0);
/*!40000 ALTER TABLE `tahun_ajaran` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `target_literasi`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `target_literasi` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned DEFAULT NULL,
`tingkat` tinyint(4) DEFAULT NULL,
`kelas_id` smallint(5) unsigned DEFAULT NULL,
`periode` enum('MINGGUAN','BULANAN','SEMESTER') DEFAULT 'BULANAN',
`target_buku` smallint(6) DEFAULT 2,
`target_menit` smallint(6) DEFAULT 300,
`keterangan` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `target_literasi`
--
LOCK TABLES `target_literasi` WRITE;
/*!40000 ALTER TABLE `target_literasi` DISABLE KEYS */;
/*!40000 ALTER TABLE `target_literasi` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tp`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `tp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cp_id` int(10) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`tingkat` tinyint(4) NOT NULL,
`semester` enum('1','2') NOT NULL,
`kode_tp` varchar(20) DEFAULT NULL COMMENT '4.1.1',
`deskripsi` text NOT NULL,
`alokasi_jp` tinyint(4) DEFAULT 2,
`urutan` smallint(6) DEFAULT 1,
`dibuat_oleh` int(10) unsigned DEFAULT NULL,
`sumber_ai` tinyint(1) DEFAULT 0,
PRIMARY KEY (`id`),
KEY `cp_id` (`cp_id`),
KEY `mapel_id` (`mapel_id`,`tingkat`,`semester`),
CONSTRAINT `tp_ibfk_1` FOREIGN KEY (`cp_id`) REFERENCES `cp` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tp`
--
LOCK TABLES `tp` WRITE;
/*!40000 ALTER TABLE `tp` DISABLE KEYS */;
/*!40000 ALTER TABLE `tp` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tugas`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `tugas` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`mapel_id` smallint(5) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
`tp_id` int(10) unsigned DEFAULT NULL,
`judul` varchar(200) DEFAULT NULL,
`instruksi` text DEFAULT NULL,
`jenis` enum('INDIVIDU','KELOMPOK','PROYEK','PRAKTIK') DEFAULT 'INDIVIDU',
`kategori_nilai` enum('FORMATIF','SUMATIF') DEFAULT 'FORMATIF',
`file_lampiran` varchar(255) DEFAULT NULL,
`bobot` tinyint(4) DEFAULT 100,
`mulai` datetime DEFAULT NULL,
`deadline` datetime DEFAULT NULL,
`izin_terlambat` tinyint(1) DEFAULT 1,
`status` enum('DRAFT','TERBIT','DITUTUP') DEFAULT 'DRAFT',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `kelas_id` (`kelas_id`,`mapel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tugas`
--
LOCK TABLES `tugas` WRITE;
/*!40000 ALTER TABLE `tugas` DISABLE KEYS */;
/*!40000 ALTER TABLE `tugas` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `tugas_jawaban`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `tugas_jawaban` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tugas_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`jawaban` text DEFAULT NULL,
`file_jawaban` varchar(255) DEFAULT NULL,
`dikumpul_at` datetime DEFAULT NULL,
`terlambat` tinyint(1) DEFAULT 0,
`nilai` decimal(5,2) DEFAULT NULL,
`feedback` text DEFAULT NULL,
`feedback_ai` text DEFAULT NULL,
`dinilai_oleh` int(10) unsigned DEFAULT NULL,
`dinilai_at` datetime DEFAULT NULL,
`status` enum('BELUM','DIKUMPUL','DINILAI','REVISI') DEFAULT 'BELUM',
PRIMARY KEY (`id`),
UNIQUE KEY `uq_tj` (`tugas_id`,`siswa_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tugas_jawaban`
--
LOCK TABLES `tugas_jawaban` WRITE;
/*!40000 ALTER TABLE `tugas_jawaban` DISABLE KEYS */;
/*!40000 ALTER TABLE `tugas_jawaban` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ujian`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ujian` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`kode` varchar(20) DEFAULT NULL,
`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(5) unsigned NOT NULL,
`guru_id` int(10) unsigned NOT NULL,
`tingkat` tinyint(4) DEFAULT NULL,
`deskripsi` text DEFAULT NULL,
`mulai` datetime DEFAULT NULL,
`selesai` datetime DEFAULT NULL,
`durasi_menit` smallint(6) 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(3) 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) DEFAULT NULL,
`maks_percobaan` tinyint(4) DEFAULT 1,
`kkm` decimal(5,2) DEFAULT 70.00,
`status` enum('DRAFT','TERJADWAL','BERLANGSUNG','SELESAI','ARSIP') DEFAULT 'DRAFT',
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `kode` (`kode`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ujian`
--
LOCK TABLES `ujian` WRITE;
/*!40000 ALTER TABLE `ujian` DISABLE KEYS */;
INSERT INTO `ujian` VALUES
(1,1,'UH-BIN-01','Ulangan Harian: Teks Deskripsi','ULANGAN_HARIAN','FORMATIF',18,1,7,'Empat soal tentang ciri dan struktur teks deskripsi.','2026-09-08 23:22:21','2026-10-08 23:22:21',45,1,1,1,3,'LANGSUNG',1,NULL,1,75.00,'BERLANGSUNG','2026-09-08 23:22:21');
/*!40000 ALTER TABLE `ujian` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ujian_jawaban`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ujian_jawaban` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`peserta_id` bigint(20) unsigned NOT NULL,
`soal_id` int(10) unsigned NOT NULL,
`jawaban` text DEFAULT NULL,
`benar` tinyint(1) DEFAULT NULL,
`skor` decimal(5,2) DEFAULT 0.00,
`ragu` tinyint(1) DEFAULT 0,
`dikoreksi_ai` tinyint(1) DEFAULT 0,
`catatan_koreksi` text DEFAULT NULL,
`updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `uq_uj` (`peserta_id`,`soal_id`),
CONSTRAINT `ujian_jawaban_ibfk_1` FOREIGN KEY (`peserta_id`) REFERENCES `ujian_peserta` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ujian_jawaban`
--
LOCK TABLES `ujian_jawaban` WRITE;
/*!40000 ALTER TABLE `ujian_jawaban` DISABLE KEYS */;
/*!40000 ALTER TABLE `ujian_jawaban` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ujian_kelas`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ujian_kelas` (
`ujian_id` int(10) unsigned NOT NULL,
`kelas_id` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`ujian_id`,`kelas_id`),
CONSTRAINT `ujian_kelas_ibfk_1` FOREIGN KEY (`ujian_id`) REFERENCES `ujian` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ujian_kelas`
--
LOCK TABLES `ujian_kelas` WRITE;
/*!40000 ALTER TABLE `ujian_kelas` DISABLE KEYS */;
INSERT INTO `ujian_kelas` VALUES
(1,1),
(1,4);
/*!40000 ALTER TABLE `ujian_kelas` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ujian_peserta`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ujian_peserta` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`ujian_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`percobaan` tinyint(4) DEFAULT 1,
`mulai_at` datetime DEFAULT NULL,
`selesai_at` datetime DEFAULT NULL,
`sisa_detik` int(11) DEFAULT NULL,
`urutan_soal` text DEFAULT NULL COMMENT 'csv id soal hasil acak',
`jumlah_benar` smallint(6) DEFAULT 0,
`jumlah_salah` smallint(6) DEFAULT 0,
`skor` decimal(5,2) DEFAULT NULL,
`nilai` decimal(5,2) DEFAULT NULL,
`tuntas` tinyint(1) DEFAULT NULL,
`pelanggaran` smallint(6) DEFAULT 0 COMMENT 'jumlah pindah tab',
`status` enum('BELUM','BERLANGSUNG','SELESAI','DIKOREKSI') DEFAULT 'BELUM',
`ip` varchar(45) DEFAULT NULL,
`user_agent` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_up` (`ujian_id`,`siswa_id`,`percobaan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ujian_peserta`
--
LOCK TABLES `ujian_peserta` WRITE;
/*!40000 ALTER TABLE `ujian_peserta` DISABLE KEYS */;
/*!40000 ALTER TABLE `ujian_peserta` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ujian_soal`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ujian_soal` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ujian_id` int(10) unsigned NOT NULL,
`soal_id` int(10) unsigned NOT NULL,
`urutan` smallint(6) DEFAULT NULL,
`bobot` decimal(5,2) DEFAULT 1.00,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_us` (`ujian_id`,`soal_id`),
KEY `soal_id` (`soal_id`),
CONSTRAINT `ujian_soal_ibfk_1` FOREIGN KEY (`ujian_id`) REFERENCES `ujian` (`id`) ON DELETE CASCADE,
CONSTRAINT `ujian_soal_ibfk_2` FOREIGN KEY (`soal_id`) REFERENCES `bank_soal` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ujian_soal`
--
LOCK TABLES `ujian_soal` WRITE;
/*!40000 ALTER TABLE `ujian_soal` DISABLE KEYS */;
INSERT INTO `ujian_soal` VALUES
(1,1,1,1,1.00),
(2,1,2,2,1.00),
(3,1,3,3,1.00),
(4,1,4,4,3.00);
/*!40000 ALTER TABLE `ujian_soal` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ukk`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ukk` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`tahun_ajaran_id` smallint(5) unsigned NOT NULL,
`jurusan_id` smallint(5) unsigned NOT NULL,
`nama` varchar(150) NOT NULL,
`skema` varchar(120) DEFAULT NULL COMMENT 'Skema sertifikasi atau paket soal',
`paket_soal` varchar(60) DEFAULT NULL,
`penyelenggara` enum('MANDIRI','LSP_P1','LSP_P2','LSP_P3','INDUSTRI') NOT NULL DEFAULT 'MANDIRI',
`industri_id` int(10) unsigned DEFAULT NULL COMMENT 'Mitra penguji bila melibatkan industri',
`penguji_internal_id` int(10) unsigned DEFAULT NULL,
`penguji_eksternal` varchar(120) DEFAULT NULL,
`asal_penguji_eksternal` varchar(150) DEFAULT NULL,
`tanggal_mulai` date DEFAULT NULL,
`tanggal_selesai` date DEFAULT NULL,
`tempat` varchar(150) DEFAULT NULL,
`kkm` decimal(5,2) NOT NULL DEFAULT 70.00,
`status` enum('DRAFT','TERJADWAL','BERLANGSUNG','SELESAI') NOT NULL DEFAULT 'DRAFT',
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_ukk` (`tahun_ajaran_id`,`jurusan_id`),
KEY `fk_ukk_jurusan` (`jurusan_id`),
KEY `fk_ukk_industri` (`industri_id`),
KEY `fk_ukk_guru` (`penguji_internal_id`),
CONSTRAINT `fk_ukk_guru` FOREIGN KEY (`penguji_internal_id`) REFERENCES `guru` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_ukk_industri` FOREIGN KEY (`industri_id`) REFERENCES `industri` (`id`) ON DELETE SET NULL,
CONSTRAINT `fk_ukk_jurusan` FOREIGN KEY (`jurusan_id`) REFERENCES `jurusan` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ukk`
--
LOCK TABLES `ukk` WRITE;
/*!40000 ALTER TABLE `ukk` DISABLE KEYS */;
/*!40000 ALTER TABLE `ukk` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ukk_nilai`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ukk_nilai` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`peserta_id` int(10) unsigned NOT NULL,
`unit_id` int(10) unsigned NOT NULL,
`skor` decimal(5,2) DEFAULT NULL,
`kompeten` tinyint(1) DEFAULT NULL,
`catatan` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_ukk_nilai` (`peserta_id`,`unit_id`),
KEY `fk_un_unit` (`unit_id`),
CONSTRAINT `fk_un_peserta` FOREIGN KEY (`peserta_id`) REFERENCES `ukk_peserta` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_un_unit` FOREIGN KEY (`unit_id`) REFERENCES `ukk_unit` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ukk_nilai`
--
LOCK TABLES `ukk_nilai` WRITE;
/*!40000 ALTER TABLE `ukk_nilai` DISABLE KEYS */;
/*!40000 ALTER TABLE `ukk_nilai` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ukk_peserta`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ukk_peserta` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ukk_id` int(10) unsigned NOT NULL,
`siswa_id` int(10) unsigned NOT NULL,
`no_peserta` varchar(40) DEFAULT NULL,
`nilai` decimal(5,2) DEFAULT NULL,
`predikat` varchar(20) DEFAULT NULL,
`keputusan` enum('KOMPETEN','BELUM_KOMPETEN','MENGULANG') DEFAULT NULL,
`no_sertifikat` varchar(60) DEFAULT NULL,
`tanggal_sertifikat` date DEFAULT NULL,
`sertifikat` varchar(255) DEFAULT NULL,
`catatan` text DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_ukk_peserta` (`ukk_id`,`siswa_id`),
KEY `fk_up_siswa` (`siswa_id`),
CONSTRAINT `fk_up_siswa` FOREIGN KEY (`siswa_id`) REFERENCES `siswa` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_up_ukk` FOREIGN KEY (`ukk_id`) REFERENCES `ukk` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ukk_peserta`
--
LOCK TABLES `ukk_peserta` WRITE;
/*!40000 ALTER TABLE `ukk_peserta` DISABLE KEYS */;
/*!40000 ALTER TABLE `ukk_peserta` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `ukk_unit`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `ukk_unit` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ukk_id` int(10) unsigned NOT NULL,
`kode` varchar(40) DEFAULT NULL COMMENT 'Kode unit kompetensi SKKNI bila ada',
`nama` varchar(200) NOT NULL,
`bobot` decimal(5,2) NOT NULL DEFAULT 1.00,
`urutan` smallint(6) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
KEY `fk_uu_ukk` (`ukk_id`),
CONSTRAINT `fk_uu_ukk` FOREIGN KEY (`ukk_id`) REFERENCES `ukk` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `ukk_unit`
--
LOCK TABLES `ukk_unit` WRITE;
/*!40000 ALTER TABLE `ukk_unit` DISABLE KEYS */;
/*!40000 ALTER TABLE `ukk_unit` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `user_token`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `user_token` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`token` varchar(128) NOT NULL,
`device_id` varchar(120) DEFAULT NULL,
`device_name` varchar(120) DEFAULT NULL,
`fcm_token` varchar(255) DEFAULT NULL,
`platform` enum('ANDROID','IOS','WEB') DEFAULT 'ANDROID',
`expired_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `token` (`token`),
KEY `user_id` (`user_id`),
CONSTRAINT `user_token_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `user_token`
--
LOCK TABLES `user_token` WRITE;
/*!40000 ALTER TABLE `user_token` DISABLE KEYS */;
/*!40000 ALTER TABLE `user_token` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `users`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(60) NOT NULL,
`email` varchar(120) DEFAULT NULL,
`no_hp` varchar(20) DEFAULT NULL,
`password` varchar(255) NOT NULL,
`role_id` tinyint(3) unsigned NOT NULL,
`ref_type` enum('GURU','SISWA','ORTU','PEGAWAI','NONE') DEFAULT 'NONE',
`ref_id` int(10) unsigned DEFAULT NULL COMMENT 'id guru/siswa/ortu/pegawai',
`nama_tampil` varchar(120) DEFAULT NULL,
`foto` varchar(255) DEFAULT NULL,
`status` enum('AKTIF','NONAKTIF','SUSPEND') DEFAULT 'AKTIF',
`must_change_pw` tinyint(1) DEFAULT 0,
`last_login` datetime DEFAULT NULL,
`last_ip` varchar(45) DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `role_id` (`role_id`),
KEY `idx_ref` (`ref_type`,`ref_id`),
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `users`
--
LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES
(1,'admin',NULL,'081200000000','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',1,'NONE',NULL,'Administrator',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(2,'kepsek',NULL,'081200000001','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',2,'GURU',1,'Ahmad Fauzi, S.Pd.',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(3,'guru',NULL,'081200000002','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',4,'GURU',2,'Siti Nurhaliza, S.Pd.',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(4,'gurumapel',NULL,'081200000003','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',3,'GURU',3,'Budi Santoso, S.Pd.I.',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(5,'tu',NULL,'081200000004','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',5,'GURU',4,'Rina Marlina, A.Md.',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(6,'perpus',NULL,'081200000005','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',6,'GURU',5,'Dedi Kurniawan, S.IP.',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(7,'rafi',NULL,'-','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',7,'SISWA',1,'Muhammad Rafi Pratama',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(8,'aisyah',NULL,'-','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',7,'SISWA',2,'Aisyah Putri Ramadhani',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(9,'081311110001',NULL,'081311110001','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',8,'ORTU',1,'Hendra Pratama',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(10,'081311110003',NULL,'081311110003','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',8,'ORTU',3,'Slamet Ramadhani',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21'),
(11,'yayasan','ketua@albarokah.sch.id','081311110099','$2y$10$AG0.fBfZaNYkGU01pXdCsuS7PB98FiTE79LsLb6uGRc2x78Gx95Re',10,'NONE',NULL,'H. Abdul Rahman, S.E.',NULL,'AKTIF',0,NULL,NULL,'2026-09-08 23:22:21');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `wa_outbox`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `wa_outbox` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`tujuan` varchar(25) NOT NULL,
`nama_tujuan` varchar(120) DEFAULT NULL,
`pesan` text NOT NULL,
`media_url` varchar(255) DEFAULT NULL,
`tipe` varchar(40) DEFAULT NULL,
`ref_tabel` varchar(60) DEFAULT NULL,
`ref_id` bigint(20) unsigned DEFAULT NULL,
`status` enum('ANTRE','TERKIRIM','GAGAL','DIBATALKAN') DEFAULT 'ANTRE',
`percobaan` tinyint(4) DEFAULT 0,
`response` text DEFAULT NULL,
`jadwal_kirim` datetime DEFAULT NULL,
`terkirim_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `status` (`status`,`jadwal_kirim`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `wa_outbox`
--
LOCK TABLES `wa_outbox` WRITE;
/*!40000 ALTER TABLE `wa_outbox` DISABLE KEYS */;
/*!40000 ALTER TABLE `wa_outbox` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `yayasan`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `yayasan` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nama` varchar(150) NOT NULL,
`akta` varchar(80) DEFAULT NULL,
`alamat` varchar(255) DEFAULT NULL,
`telepon` varchar(30) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
`ketua` varchar(120) DEFAULT NULL,
`logo` varchar(255) DEFAULT NULL,
`is_aktif` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `yayasan`
--
LOCK TABLES `yayasan` WRITE;
/*!40000 ALTER TABLE `yayasan` DISABLE KEYS */;
INSERT INTO `yayasan` VALUES
(1,'Yayasan Pendidikan Al Barokah','AHU-0012345.AH.01.04','Jl. Raya Cileungsi No. 1, Bogor','021-8230000','sekretariat@albarokah.sch.id','H. Abdul Rahman, S.E.',NULL,1);
/*!40000 ALTER TABLE `yayasan` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `yayasan_catatan`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8mb4 */;
CREATE TABLE `yayasan_catatan` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`yayasan_id` int(10) unsigned NOT NULL,
`periode` varchar(20) NOT NULL COMMENT 'mis. 2026-08 atau 2026/2027-1',
`bidang` enum('AKADEMIK','KEUANGAN','KEPEGAWAIAN','SARANA','KESISWAAN','UMUM') NOT NULL DEFAULT 'UMUM',
`temuan` text DEFAULT NULL,
`rekomendasi` text DEFAULT NULL,
`tindak_lanjut` text DEFAULT NULL,
`status` enum('BARU','DIPROSES','SELESAI') NOT NULL DEFAULT 'BARU',
`dibuat_oleh` int(10) unsigned DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
KEY `idx_periode` (`yayasan_id`,`periode`),
CONSTRAINT `fk_yc_yayasan` FOREIGN KEY (`yayasan_id`) REFERENCES `yayasan` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `yayasan_catatan`
--
LOCK TABLES `yayasan_catatan` WRITE;
/*!40000 ALTER TABLE `yayasan_catatan` DISABLE KEYS */;
/*!40000 ALTER TABLE `yayasan_catatan` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
SET FOREIGN_KEY_CHECKS = 1;
SELECT CONCAT('SIMAP terpasang: ', COUNT(*), ' tabel') AS hasil
FROM information_schema.tables WHERE table_schema = DATABASE();