-- =====================================================================
-- SIMAP — Data contoh SD swasta
--
-- Dipasang setelah 03_game_konten.sql:
--   mysql -u root -p simap_db < database/04_data_contoh.sql
--
-- Berisi 18 siswa dengan data induk lengkap, orang tua, tagihan SPP dan
-- iuran khas sekolah swasta, kartu RFID, serta riwayat pembayaran.
-- Tujuannya agar setiap layar terlihat dengan data yang wajar, bukan kosong.
--
-- HAPUS berkas ini sebelum dipakai sekolah sungguhan.
-- Aman dijalankan berulang: seluruh sisipan memakai INSERT IGNORE.
-- =====================================================================

SET NAMES utf8mb4;
SET @ta := (SELECT id FROM tahun_ajaran WHERE is_aktif = 1);

-- ------------------------------------------------------------- SISWA
INSERT IGNORE INTO siswa
 (nisn,nis,nik,no_kk,no_akta,nama,nama_panggilan,jenis_kelamin,tempat_lahir,tanggal_lahir,
  agama,kewarganegaraan,anak_ke,jumlah_saudara,alamat,rt,rw,desa,kecamatan,kabupaten,provinsi,kode_pos,
  jarak_rumah_km,transportasi,tinggal_bersama,golongan_darah,tinggi_badan,berat_badan,
  riwayat_kesehatan,kebutuhan_khusus,asal_tk,tanggal_masuk,no_kip,penerima_kip,status)
VALUES
('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.5,'Antar jemput','Orang tua','A',132,29,NULL,NULL,'TK Islam Al Barokah','2024-07-15',NULL,0,'AKTIF'),
('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.2,'Jalan kaki','Orang tua','O',135,31,NULL,NULL,'RA Nurul Iman','2024-07-15','6011223344',1,'AKTIF'),
('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.0,'Kendaraan pribadi','Orang tua','B',130,27,'Alergi debu','Perlu duduk di depan','TK Tunas Bangsa','2024-07-15',NULL,0,'AKTIF'),
('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.1,'Sepeda','Orang tua','A',140,34,NULL,NULL,'TK Pertiwi','2023-07-17','6011223355',1,'AKTIF'),
('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.8,'Antar jemput','Orang tua','AB',138,32,NULL,NULL,'TK Islam Al Barokah','2023-07-17',NULL,0,'AKTIF'),
('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.2,'Angkutan umum','Nenek','O',142,36,NULL,NULL,'TK Harapan Bunda','2023-07-17','6011223366',1,'AKTIF'),
('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.2,'Jalan kaki','Orang tua','B',145,38,NULL,NULL,'RA Al Hidayah','2022-07-18',NULL,0,'AKTIF'),
('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.6,'Kendaraan pribadi','Orang tua','A',148,41,NULL,NULL,'TK Kristal','2022-07-18',NULL,0,'AKTIF'),
('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.5,'Antar jemput','Orang tua','O',150,43,NULL,NULL,'TK Aisyiyah','2021-07-19','6011223377',1,'AKTIF'),
('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.5,'Sepeda','Orang tua','B',152,45,'Asma ringan','Hindari aktivitas berat berkepanjangan','TK Islam Al Barokah','2021-07-19',NULL,0,'AKTIF');

-- ------------------------------------------------------------- ORANG TUA
INSERT IGNORE INTO ortu (nik,nama,hubungan,tempat_lahir,tanggal_lahir,pendidikan,pekerjaan,penghasilan,no_hp,alamat,status_hidup) VALUES
('3201010101850001','Hendra Pratama','AYAH','Bogor','1985-01-01','S1','Karyawan Swasta','5-10JT','081311110001','Perum Griya Asri Blok C2 No 14','HIDUP'),
('3201014102880001','Siti Maryam','IBU','Bogor','1988-02-01','SMA','Ibu Rumah Tangga',NULL,'081311110002','Perum Griya Asri Blok C2 No 14','HIDUP'),
('3201010101820002','Slamet Ramadhani','AYAH','Cianjur','1982-05-10','SMA','Wiraswasta','2-5JT','081311110003','Kp. Cipeucang RT 02','HIDUP'),
('3201014102860002','Nurhayati','IBU','Bogor','1986-08-17','SMP','Pedagang','1-2JT','081311110004','Kp. Cipeucang RT 02','HIDUP'),
('3201010101800003','Bambang Sutrisno','AYAH','Jakarta','1980-03-03','S2','Pegawai Negeri Sipil','5-10JT','081311110005','Jl. Raya Narogong KM 12','HIDUP'),
('3201014102840003','Dewi Anggraini','IBU','Jakarta','1984-12-12','S1','Guru','2-5JT','081311110006','Jl. Raya Narogong KM 12','HIDUP'),
('3201010101830004','Ahmad Sulaiman','AYAH','Bogor','1983-06-20','SMA','Buruh Harian','1-2JT','081311110007','Kp. Bojong RT 04','HIDUP'),
('3201014102870004','Yuliana','IBU','Bogor','1987-07-07','SMP','Ibu Rumah Tangga',NULL,'081311110008','Kp. Bojong RT 04','HIDUP'),
('3201010101810005','Rudi Hartono','AYAH','Bandung','1981-09-09','S1','Karyawan Swasta','5-10JT','081311110009','Perum Cileungsi Hijau Blok D5','HIDUP'),
('3201014102850005','Fitri Handayani','IBU','Bogor','1985-04-14','S1','Bidan','2-5JT','081311110010','Perum Cileungsi Hijau Blok D5','HIDUP'),
('3201010101790006','Sugianto','AYAH','Bekasi','1979-02-02','SMA','Sopir','1-2JT','081311110011','Jl. Mekarsari No 8','MENINGGAL'),
('3201014102830006','Sri Wahyuni','IBU','Bekasi','1983-10-30','SMA','Karyawan Pabrik','2-5JT','081311110012','Jl. Mekarsari No 8','HIDUP'),
('3201010101840007','Iwan Setiawan','AYAH','Bogor','1984-08-08','D3','Teknisi','2-5JT','081311110013','Kp. Pasir Angin RT 01','HIDUP'),
('3201014102880007','Ratna Sari','IBU','Bogor','1988-11-11','SMA','Penjahit','TIDAK_TETAP','081311110014','Kp. Pasir Angin RT 01','HIDUP'),
('3201010101780008','Gunawan Wibisono','AYAH','Semarang','1978-01-15','S1','Pengusaha','>10JT','081311110015','Perum Metland Blok B12','HIDUP'),
('3201014102820008','Lestari Ningsih','IBU','Bogor','1982-03-21','S1','Karyawan Swasta','5-10JT','081311110016','Perum Metland Blok B12','HIDUP'),
('3201010101860009','Asep Saepudin','AYAH','Sukabumi','1986-05-05','SMA','Petani','TIDAK_TETAP','081311110017','Kp. Cikuda RT 03','HIDUP'),
('3201014102890009','Euis Komariah','IBU','Sukabumi','1989-09-19','SD','Ibu Rumah Tangga',NULL,'081311110018','Kp. Cikuda RT 03','HIDUP'),
('3201010101830010','Deni Kurniawan','AYAH','Bogor','1983-12-01','D3','Wiraswasta','2-5JT','081311110019','Jl. Anggrek Raya No 22','HIDUP'),
('3201014102870010','Wulan Sari','IBU','Bogor','1987-06-16','SMA','Ibu Rumah Tangga',NULL,'081311110020','Jl. Anggrek Raya No 22','HIDUP');

-- Hubungkan orang tua ke anaknya: dua ortu berurutan untuk tiap siswa baru
INSERT IGNORE INTO siswa_ortu (siswa_id, ortu_id, is_utama)
SELECT s.id, o.id, IF(o.hubungan = 'AYAH', 1, 0)
FROM (SELECT id, nis, ROW_NUMBER() OVER (ORDER BY nis) AS urut FROM siswa WHERE nis LIKE '202%') s
JOIN (SELECT id, hubungan, CEIL(ROW_NUMBER() OVER (ORDER BY id) / 2) AS urut FROM ortu WHERE nik LIKE '32010101%' OR nik LIKE '32010141%') o
  ON o.urut = s.urut
WHERE NOT EXISTS (SELECT 1 FROM siswa_ortu so WHERE so.siswa_id = s.id AND so.ortu_id = o.id);

-- ------------------------------------------------------ TEMPATKAN DI KELAS
INSERT IGNORE INTO kelas_siswa (kelas_id, siswa_id, no_absen, status)
SELECT k.id, s.id,
       ROW_NUMBER() OVER (PARTITION BY k.id ORDER BY s.nama),
       'AKTIF'
FROM siswa s
JOIN kelas k ON k.tahun_ajaran_id = @ta AND k.tingkat = CASE
      WHEN s.nis LIKE '2024%' THEN 1
      WHEN s.nis LIKE '2023%' THEN 2
      WHEN s.nis LIKE '2022%' THEN 3
      ELSE 4 END
WHERE s.nis LIKE '202%'
  AND NOT EXISTS (SELECT 1 FROM kelas_siswa ks WHERE ks.siswa_id = s.id AND ks.status = 'AKTIF');

-- ------------------------------------------------ PENUGASAN MATA PELAJARAN
-- Tanpa baris ini, daftar mapel pada menu Perangkat Ajar kosong dan guru
-- tidak dapat menyusun Prota, ATP, maupun Modul Ajar.
-- Penugasan mengikat guru, mata pelajaran, DAN kelas sekaligus.

-- Wali kelas mengampu seluruh mapel yang berlaku pada kelasnya
INSERT IGNORE INTO guru_mapel (tahun_ajaran_id, guru_id, mapel_id, kelas_id)
SELECT @ta, k.wali_kelas_id, m.id, k.id
FROM kelas k
JOIN mapel m ON m.is_aktif = 1 AND k.tingkat BETWEEN m.tingkat_min AND m.tingkat_max
WHERE k.tahun_ajaran_id = @ta AND k.wali_kelas_id IS NOT NULL;

-- Guru mata pelajaran khusus mengampu mapel agama di seluruh kelas
INSERT IGNORE INTO guru_mapel (tahun_ajaran_id, guru_id, mapel_id, kelas_id)
SELECT @ta, g.id, m.id, k.id
FROM guru g
JOIN mapel m ON m.kode IN ('PAI','BAR','BTQ','QH','AA','FIK','SKI') AND m.is_aktif = 1
JOIN kelas k ON k.tahun_ajaran_id = @ta AND k.tingkat BETWEEN m.tingkat_min AND m.tingkat_max
WHERE g.jenis_ptk = 'GURU_MAPEL' AND g.status = 'AKTIF';

-- -------------------------------------------------- ANGGOTA PERPUSTAKAAN
-- Siswa yang ditambahkan lewat aplikasi otomatis menjadi anggota; siswa
-- contoh disisipkan langsung ke basis data, jadi keanggotaannya dibuat di sini
-- agar sirkulasi peminjaman bisa langsung dipakai.
INSERT IGNORE INTO anggota_perpus (no_anggota, tipe, ref_id, tanggal_daftar, berlaku_sampai, maks_pinjam, lama_pinjam_hari, status)
SELECT CONCAT('AP-', s.nis), 'SISWA', s.id, s.tanggal_masuk,
       DATE_ADD(s.tanggal_masuk, INTERVAL 6 YEAR), 2, 7, 'AKTIF'
FROM siswa s
WHERE s.nis LIKE '202%'
  AND NOT EXISTS (SELECT 1 FROM anggota_perpus a WHERE a.tipe = 'SISWA' AND a.ref_id = s.id);

-- ------------------------------------------------------------- KARTU RFID
-- Nomor kartu mengikuti keluaran RDM6300 / EM4100: 10 digit desimal
INSERT IGNORE INTO rfid_kartu (uid, pemilik_tipe, pemilik_id, no_kartu, tanggal_terbit, status)
SELECT LPAD(6100000000 + s.id, 10, '0'), 'SISWA', s.id, CONCAT('KTM-', s.nis), '2024-07-20', 'AKTIF'
FROM siswa s WHERE s.nis LIKE '202%'
  AND NOT EXISTS (SELECT 1 FROM rfid_kartu k WHERE k.pemilik_tipe = 'SISWA' AND k.pemilik_id = s.id);

UPDATE siswa s JOIN rfid_kartu k ON k.pemilik_tipe = 'SISWA' AND k.pemilik_id = s.id
   SET s.rfid_uid = k.uid WHERE s.rfid_uid IS NULL;

-- ------------------------------------------------------------- TAGIHAN
-- SPP tiga bulan terakhir untuk seluruh siswa aktif
INSERT IGNORE INTO tagihan (no_tagihan, tahun_ajaran_id, siswa_id, jenis_id, bulan, tahun,
                     nominal, diskon, total, jatuh_tempo, status)
SELECT CONCAT('SPP-', DATE_FORMAT(d.bln, '%Y%m'), '-', LPAD(s.id, 4, '0')),
       @ta, s.id, jt.id, MONTH(d.bln), YEAR(d.bln),
       jt.nominal_default,
       -- Anak ketiga ke atas dan penerima KIP mendapat keringanan
       CASE WHEN s.penerima_kip = 1 THEN 50000 WHEN s.anak_ke >= 3 THEN 25000 ELSE 0 END,
       jt.nominal_default - CASE WHEN s.penerima_kip = 1 THEN 50000 WHEN s.anak_ke >= 3 THEN 25000 ELSE 0 END,
       LAST_DAY(d.bln), 'BELUM'
FROM siswa s
CROSS JOIN (SELECT DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL n MONTH) AS bln
            FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2) x) d
JOIN jenis_tagihan jt ON jt.kode = 'SPP'
WHERE s.status = 'AKTIF'
  AND NOT EXISTS (SELECT 1 FROM tagihan t WHERE t.siswa_id = s.id AND t.jenis_id = jt.id
                    AND t.bulan = MONTH(d.bln) AND t.tahun = YEAR(d.bln));

-- Iuran kegiatan semester
INSERT IGNORE INTO tagihan (no_tagihan, tahun_ajaran_id, siswa_id, jenis_id, tahun, nominal, total, jatuh_tempo, status)
SELECT CONCAT('KEG-', YEAR(CURDATE()), '-', LPAD(s.id, 4, '0')), @ta, s.id, jt.id, YEAR(CURDATE()),
       jt.nominal_default, jt.nominal_default, DATE_ADD(CURDATE(), INTERVAL 20 DAY), 'BELUM'
FROM siswa s JOIN jenis_tagihan jt ON jt.kode = 'KEGIATAN'
WHERE s.status = 'AKTIF'
  AND NOT EXISTS (SELECT 1 FROM tagihan t WHERE t.siswa_id = s.id AND t.jenis_id = jt.id AND t.tahun = YEAR(CURDATE()));

-- ---------------------------------------------------------- PEMBAYARAN
-- Sebagian sudah lunas, sebagian menunggak — supaya layar keuangan hidup
INSERT IGNORE INTO pembayaran (tagihan_id, no_kwitansi, tanggal, nominal, metode, petugas_id, catatan)
SELECT t.id, CONCAT('KW-', DATE_FORMAT(t.jatuh_tempo, '%Y%m'), '-', LPAD(t.id, 5, '0')),
       DATE_SUB(t.jatuh_tempo, INTERVAL 5 DAY), t.total, 'TUNAI',
       (SELECT id FROM users WHERE username = 'tu' LIMIT 1), 'Dibayar di kantor TU'
FROM tagihan t
JOIN siswa s ON s.id = t.siswa_id
WHERE t.status = 'BELUM'
  AND t.jatuh_tempo < CURDATE()
  AND s.id % 3 <> 0                    -- dua dari tiga siswa membayar tepat waktu
  AND NOT EXISTS (SELECT 1 FROM pembayaran p WHERE p.tagihan_id = t.id);

UPDATE tagihan t
   SET t.terbayar = (SELECT COALESCE(SUM(p.nominal), 0) FROM pembayaran p WHERE p.tagihan_id = t.id),
       t.status = CASE
         WHEN (SELECT COALESCE(SUM(p.nominal), 0) FROM pembayaran p WHERE p.tagihan_id = t.id) >= t.total THEN 'LUNAS'
         WHEN (SELECT COALESCE(SUM(p.nominal), 0) FROM pembayaran p WHERE p.tagihan_id = t.id) > 0 THEN 'SEBAGIAN'
         ELSE 'BELUM' END;

-- ------------------------------------------------------------- ABSENSI
-- Kehadiran 20 hari kerja terakhir, sebagian besar hadir
INSERT IGNORE INTO absensi_siswa (siswa_id, kelas_id, tanggal, status, jam_masuk, jam_pulang, menit_telat, sumber)
SELECT ks.siswa_id, ks.kelas_id, d.tgl,
       CASE WHEN (ks.siswa_id + DAY(d.tgl)) % 23 = 0 THEN 'SAKIT'
            WHEN (ks.siswa_id + DAY(d.tgl)) % 29 = 0 THEN 'IZIN'
            WHEN (ks.siswa_id + DAY(d.tgl)) % 17 = 0 THEN 'TERLAMBAT'
            ELSE 'HADIR' END,
       CASE WHEN (ks.siswa_id + DAY(d.tgl)) % 23 = 0 OR (ks.siswa_id + DAY(d.tgl)) % 29 = 0 THEN NULL
            WHEN (ks.siswa_id + DAY(d.tgl)) % 17 = 0 THEN '07:15:00' ELSE '06:45:00' END,
       CASE WHEN (ks.siswa_id + DAY(d.tgl)) % 23 = 0 OR (ks.siswa_id + DAY(d.tgl)) % 29 = 0 THEN NULL
            ELSE '12:30:00' END,
       CASE WHEN (ks.siswa_id + DAY(d.tgl)) % 17 = 0 THEN 15 ELSE 0 END,
       'RFID'
FROM kelas_siswa ks
CROSS JOIN (SELECT DATE_SUB(CURDATE(), INTERVAL n DAY) AS tgl
            FROM (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
                  UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
                  UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19) y) d
WHERE ks.status = 'AKTIF' AND DAYOFWEEK(d.tgl) NOT IN (1)
  AND NOT EXISTS (SELECT 1 FROM absensi_siswa a WHERE a.siswa_id = ks.siswa_id AND a.tanggal = d.tgl);

SELECT
 (SELECT COUNT(*) FROM siswa WHERE status='AKTIF') AS siswa,
 (SELECT COUNT(*) FROM ortu) AS ortu,
 (SELECT COUNT(*) FROM rfid_kartu) AS kartu,
 (SELECT COUNT(*) FROM tagihan) AS tagihan,
 (SELECT COUNT(*) FROM pembayaran) AS pembayaran,
 (SELECT COUNT(*) FROM absensi_siswa) AS absensi;