-- =====================================================
-- DATA DUMMY DASHBOARD DIGITAL MONITORING KINERJA GURU
-- SMAN 1 BANTARAN
-- Periode: Agustus 2025 - April 2026
-- =====================================================

-- =====================================================
-- 1. DATA MASTER (Tahun Ajaran, Kelas, Guru, Mapel, Semester)
-- =====================================================

-- 1.1 Tahun Ajaran
INSERT INTO dasb_tahunajaran (tahun_ajaran, aktif) VALUES
('2025/2026', 1);

-- 1.2 Kelas
INSERT INTO dasb_kelas (id_tahunajaran, nama_kelas) VALUES
(1, 'X MIPA 1'),
(1, 'X MIPA 2'),
(1, 'X IPS 1'),
(1, 'XI MIPA 1'),
(1, 'XI MIPA 2'),
(1, 'XI IPS 1'),
(1, 'XII MIPA 1'),
(1, 'XII MIPA 2'),
(1, 'XII IPS 1');

-- 1.3 Mata Pelajaran
INSERT INTO dasb_matapelajaran (kode_mapel, nama_mapel, keterangan) VALUES
('MTK01', 'Matematika', 'Wajib'),
('BIN01', 'Bahasa Indonesia', 'Wajib'),
('BIG01', 'Bahasa Inggris', 'Wajib'),
('FIS01', 'Fisika', 'MIPA'),
('KIM01', 'Kimia', 'MIPA'),
('BIO01', 'Biologi', 'MIPA'),
('SEJ01', 'Sejarah', 'IPS'),
('GEO01', 'Geografi', 'IPS'),
('EKO01', 'Ekonomi', 'IPS'),
('SOS01', 'Sosiologi', 'IPS'),
('INF01', 'Informatika', 'Wajib'),
('SEN01', 'Seni Budaya', 'Wajib'),
('PJK01', 'Penjaskes', 'Wajib'),
('PAI01', 'Pendidikan Agama', 'Wajib');

-- 1.4 Guru (16 guru)
INSERT INTO dasb_guru (id_tahunajaran, nip, nama_guru, keterangan) VALUES
(1, '197501011998021001', 'Dr. Ahmad Hidayat, M.Pd', 'Kepala Sekolah'),
(1, '197502021999031002', 'Drs. Bambang Susanto', 'Waka Kurikulum'),
(1, '197503032000041003', 'Siti Aminah, S.Pd., M.Si', 'Guru Matematika'),
(1, '197504042001051004', 'Dewi Lestari, S.Pd', 'Guru Bahasa Indonesia'),
(1, '197505052002061005', 'Muhammad Rizal, S.Pd., M.Pd', 'Guru Bahasa Inggris'),
(1, '197506062003071006', 'Ratna Sari, S.Si', 'Guru Fisika'),
(1, '197507072004081007', 'Agus Prasetyo, S.Pd', 'Guru Kimia'),
(1, '197508082005091008', 'Lina Marlina, S.Pd', 'Guru Biologi'),
(1, '197509092006101009', 'Hendra Kurniawan, S.Pd', 'Guru Sejarah'),
(1, '197510102007111010', 'Rina Wulandari, S.Pd', 'Guru Geografi'),
(1, '197511112008121011', 'Eko Budianto, S.Pd., M.Pd', 'Guru Ekonomi'),
(1, '197512122009131012', 'Nina Susanti, S.Pd', 'Guru Sosiologi'),
(1, '197601012010141013', 'Joko Prasetyo, S.Kom', 'Guru Informatika'),
(1, '197602022011151014', 'Mega Utami, S.Pd', 'Guru Seni Budaya'),
(1, '197603032012161015', 'Rudi Hartono, S.Pd', 'Guru Penjaskes'),
(1, '197604042013171016', 'Sri Mulyani, S.Ag', 'Guru Pendidikan Agama');

-- 1.5 Semester
INSERT INTO dasb_semester (id_tahunajaran, semester, aktif) VALUES
(1, 'Ganjil', 1),
(1, 'Genap', 0);

-- 1.6 Users (untuk login)
INSERT INTO dasb_users (nama, username, password) VALUES
('Administrator', 'admin', '$2y$10$V8PtluHI1/dXD10hfAfOAudCL3XtRqAkiPyF5QcMRzNXiRCuLNe/a');

-- 1.7 Roles
INSERT INTO dasb_roles (nama_role) VALUES 
('admin'), ('guru'), ('guru-bk'), ('waka'), ('waka kurikulum'),
('waka kesiswaan'), ('waka humas'), ('waka sarpras'), ('wali-kelas'),
('ketua-lab'), ('kepala-perpus'), ('operator');

-- 1.8 User Roles (Admin)
SET @role_admin = (SELECT id_role FROM dasb_roles WHERE nama_role = 'admin');
INSERT INTO dasb_user_roles (id_user, id_role)
SELECT id_user, @role_admin FROM dasb_users WHERE username = 'admin';

-- 1.9 User untuk setiap guru (username = NIP, password = 123)
INSERT INTO dasb_users (nama, username, password)
SELECT nama_guru, nip, '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi'
FROM dasb_guru
WHERE nip NOT IN (SELECT username FROM dasb_users WHERE username IS NOT NULL);

-- 1.10 Assign role guru
SET @role_guru = (SELECT id_role FROM dasb_roles WHERE nama_role = 'guru');
INSERT INTO dasb_user_roles (id_user, id_role)
SELECT u.id_user, @role_guru
FROM dasb_users u
WHERE u.username IN (SELECT nip FROM dasb_guru);

-- =====================================================
-- 2. DATA JADWAL MENGAJAR
-- =====================================================
INSERT INTO dasb_jadwal (id_user, id_semester, id_kelas, id_mapel, hari, jam_mulai, jam_selesai) VALUES
-- Guru Matematika (id_user dari guru dengan nip 197503032000041003)
((SELECT id_user FROM dasb_users WHERE username = '197503032000041003'), 1, 1, 1, 'Senin', '07:30:00', '09:00:00'),
((SELECT id_user FROM dasb_users WHERE username = '197503032000041003'), 1, 2, 1, 'Selasa', '07:30:00', '09:00:00'),
((SELECT id_user FROM dasb_users WHERE username = '197503032000041003'), 1, 4, 1, 'Rabu', '09:15:00', '10:45:00'),

-- Guru Bahasa Indonesia
((SELECT id_user FROM dasb_users WHERE username = '197504042001051004'), 1, 1, 2, 'Senin', '09:15:00', '10:45:00'),
((SELECT id_user FROM dasb_users WHERE username = '197504042001051004'), 1, 2, 2, 'Rabu', '07:30:00', '09:00:00'),
((SELECT id_user FROM dasb_users WHERE username = '197504042001051004'), 1, 4, 2, 'Kamis', '07:30:00', '09:00:00'),

-- Guru Bahasa Inggris
((SELECT id_user FROM dasb_users WHERE username = '197505052002061005'), 1, 1, 3, 'Selasa', '09:15:00', '10:45:00'),
((SELECT id_user FROM dasb_users WHERE username = '197505052002061005'), 1, 2, 3, 'Kamis', '09:15:00', '10:45:00'),
((SELECT id_user FROM dasb_users WHERE username = '197505052002061005'), 1, 4, 3, 'Jumat', '07:30:00', '09:00:00'),

-- Guru Fisika
((SELECT id_user FROM dasb_users WHERE username = '197506062003071006'), 1, 1, 4, 'Rabu', '10:45:00', '12:15:00'),
((SELECT id_user FROM dasb_users WHERE username = '197506062003071006'), 1, 2, 4, 'Kamis', '10:45:00', '12:15:00'),

-- Guru Kimia
((SELECT id_user FROM dasb_users WHERE username = '197507072004081007'), 1, 1, 5, 'Selasa', '10:45:00', '12:15:00'),
((SELECT id_user FROM dasb_users WHERE username = '197507072004081007'), 1, 2, 5, 'Jumat', '09:15:00', '10:45:00'),

-- Guru Biologi
((SELECT id_user FROM dasb_users WHERE username = '197508082005091008'), 1, 1, 6, 'Senin', '10:45:00', '12:15:00'),
((SELECT id_user FROM dasb_users WHERE username = '197508082005091008'), 1, 2, 6, 'Rabu', '10:45:00', '12:15:00'),

-- Guru Informatika
((SELECT id_user FROM dasb_users WHERE username = '197601012010141013'), 1, 1, 11, 'Jumat', '07:30:00', '09:00:00'),
((SELECT id_user FROM dasb_users WHERE username = '197601012010141013'), 1, 2, 11, 'Senin', '07:30:00', '09:00:00'),
((SELECT id_user FROM dasb_users WHERE username = '197601012010141013'), 1, 4, 11, 'Selasa', '07:30:00', '09:00:00'),

-- Guru PAI
((SELECT id_user FROM dasb_users WHERE username = '197604042013171016'), 1, 1, 14, 'Kamis', '07:30:00', '09:00:00'),
((SELECT id_user FROM dasb_users WHERE username = '197604042013171016'), 1, 2, 14, 'Jumat', '10:45:00', '12:15:00');

-- =====================================================
-- 3. DATA KEHADIRAN GURU (Agustus 2025 - April 2026)
-- =====================================================

-- Fungsi untuk generate tanggal (akan dijalankan per bulan)
-- Data kehadiran per guru (16 guru) selama 9 bulan

-- ==================== AGUSTUS 2025 ====================
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    g.id_guru,
    DATE('2025-08-01') + INTERVAL (a.n - 1) DAY as tanggal,
    CASE 
        WHEN RAND() < 0.85 THEN '07:15:00'
        WHEN RAND() < 0.95 THEN '08:00:00'
        ELSE NULL
    END as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN RAND() < 0.80 THEN 'Hadir'
        WHEN RAND() < 0.85 THEN 'Terlambat'
        WHEN RAND() < 0.90 THEN 'Izin'
        WHEN RAND() < 0.95 THEN 'Sakit'
        ELSE 'Alpa'
    END as status,
    NULL as keterangan
FROM dasb_guru g
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
    SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30
) a
WHERE DATE('2025-08-01') + INTERVAL (a.n - 1) DAY <= '2025-08-31'
AND RAND() > 0.1; -- 90% data terisi

-- ==================== SEPTEMBER 2025 ====================
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    g.id_guru,
    DATE('2025-09-01') + INTERVAL (a.n - 1) DAY as tanggal,
    CASE 
        WHEN RAND() < 0.85 THEN '07:15:00'
        WHEN RAND() < 0.95 THEN '08:00:00'
        ELSE NULL
    END as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN RAND() < 0.85 THEN 'Hadir'
        WHEN RAND() < 0.90 THEN 'Terlambat'
        WHEN RAND() < 0.93 THEN 'Izin'
        WHEN RAND() < 0.96 THEN 'Sakit'
        ELSE 'Dinas Luar'
    END as status,
    NULL as keterangan
FROM dasb_guru g
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
    SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30
) a
WHERE DATE('2025-09-01') + INTERVAL (a.n - 1) DAY <= '2025-09-30'
AND RAND() > 0.08;

-- ==================== OKTOBER 2025 ====================
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    g.id_guru,
    DATE('2025-10-01') + INTERVAL (a.n - 1) DAY as tanggal,
    CASE 
        WHEN RAND() < 0.88 THEN '07:15:00'
        WHEN RAND() < 0.96 THEN '08:00:00'
        ELSE NULL
    END as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN RAND() < 0.88 THEN 'Hadir'
        WHEN RAND() < 0.92 THEN 'Terlambat'
        WHEN RAND() < 0.95 THEN 'Izin'
        WHEN RAND() < 0.98 THEN 'Sakit'
        ELSE 'Dinas Luar'
    END as status,
    NULL as keterangan
FROM dasb_guru g
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
    SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31
) a
WHERE DATE('2025-10-01') + INTERVAL (a.n - 1) DAY <= '2025-10-31'
AND RAND() > 0.05;

-- ==================== NOVEMBER 2025 ====================
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    g.id_guru,
    DATE('2025-11-01') + INTERVAL (a.n - 1) DAY as tanggal,
    CASE 
        WHEN RAND() < 0.90 THEN '07:15:00'
        WHEN RAND() < 0.97 THEN '08:00:00'
        ELSE NULL
    END as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN RAND() < 0.90 THEN 'Hadir'
        WHEN RAND() < 0.94 THEN 'Terlambat'
        WHEN RAND() < 0.96 THEN 'Izin'
        WHEN RAND() < 0.98 THEN 'Sakit'
        ELSE 'Dinas Luar'
    END as status,
    NULL as keterangan
FROM dasb_guru g
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
    SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30
) a
WHERE DATE('2025-11-01') + INTERVAL (a.n - 1) DAY <= '2025-11-30'
AND RAND() > 0.05;

-- ==================== DESEMBER 2025 ====================
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    g.id_guru,
    DATE('2025-12-01') + INTERVAL (a.n - 1) DAY as tanggal,
    CASE 
        WHEN RAND() < 0.87 THEN '07:15:00'
        WHEN RAND() < 0.95 THEN '08:00:00'
        ELSE NULL
    END as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN RAND() < 0.87 THEN 'Hadir'
        WHEN RAND() < 0.91 THEN 'Terlambat'
        WHEN RAND() < 0.94 THEN 'Izin'
        WHEN RAND() < 0.97 THEN 'Sakit'
        ELSE 'Alpa'
    END as status,
    NULL as keterangan
FROM dasb_guru g
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
    SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31
) a
WHERE DATE('2025-12-01') + INTERVAL (a.n - 1) DAY <= '2025-12-31'
AND RAND() > 0.10;

-- ==================== JANUARI 2026 ====================
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    g.id_guru,
    DATE('2026-01-01') + INTERVAL (a.n - 1) DAY as tanggal,
    CASE 
        WHEN RAND() < 0.89 THEN '07:15:00'
        WHEN RAND() < 0.96 THEN '08:00:00'
        ELSE NULL
    END as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN RAND() < 0.89 THEN 'Hadir'
        WHEN RAND() < 0.93 THEN 'Terlambat'
        WHEN RAND() < 0.95 THEN 'Izin'
        WHEN RAND() < 0.98 THEN 'Sakit'
        ELSE 'Dinas Luar'
    END as status,
    NULL as keterangan
FROM dasb_guru g
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
    SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31
) a
WHERE DATE('2026-01-01') + INTERVAL (a.n - 1) DAY <= '2026-01-31'
AND RAND() > 0.05;

-- ==================== FEBRUARI 2026 ====================
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    g.id_guru,
    DATE('2026-02-01') + INTERVAL (a.n - 1) DAY as tanggal,
    CASE 
        WHEN RAND() < 0.92 THEN '07:15:00'
        WHEN RAND() < 0.98 THEN '08:00:00'
        ELSE NULL
    END as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN RAND() < 0.92 THEN 'Hadir'
        WHEN RAND() < 0.95 THEN 'Terlambat'
        WHEN RAND() < 0.97 THEN 'Izin'
        WHEN RAND() < 0.99 THEN 'Sakit'
        ELSE 'Dinas Luar'
    END as status,
    NULL as keterangan
FROM dasb_guru g
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
    SELECT 26 UNION SELECT 27 UNION SELECT 28
) a
WHERE DATE('2026-02-01') + INTERVAL (a.n - 1) DAY <= '2026-02-28'
AND RAND() > 0.03;

-- ==================== MARET 2026 ====================
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    g.id_guru,
    DATE('2026-03-01') + INTERVAL (a.n - 1) DAY as tanggal,
    CASE 
        WHEN RAND() < 0.93 THEN '07:15:00'
        WHEN RAND() < 0.98 THEN '08:00:00'
        ELSE NULL
    END as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN RAND() < 0.93 THEN 'Hadir'
        WHEN RAND() < 0.96 THEN 'Terlambat'
        WHEN RAND() < 0.98 THEN 'Izin'
        WHEN RAND() < 0.99 THEN 'Sakit'
        ELSE 'Dinas Luar'
    END as status,
    NULL as keterangan
FROM dasb_guru g
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
    SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31
) a
WHERE DATE('2026-03-01') + INTERVAL (a.n - 1) DAY <= '2026-03-31'
AND RAND() > 0.03;

-- ==================== APRIL 2026 ====================
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    g.id_guru,
    DATE('2026-04-01') + INTERVAL (a.n - 1) DAY as tanggal,
    CASE 
        WHEN RAND() < 0.94 THEN '07:15:00'
        WHEN RAND() < 0.98 THEN '08:00:00'
        ELSE NULL
    END as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN RAND() < 0.94 THEN 'Hadir'
        WHEN RAND() < 0.97 THEN 'Terlambat'
        WHEN RAND() < 0.98 THEN 'Izin'
        WHEN RAND() < 0.99 THEN 'Sakit'
        ELSE 'Dinas Luar'
    END as status,
    NULL as keterangan
FROM dasb_guru g
CROSS JOIN (
    SELECT 1 as n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
    SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION
    SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION
    SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20 UNION
    SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION
    SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION SELECT 30
) a
WHERE DATE('2026-04-01') + INTERVAL (a.n - 1) DAY <= '2026-04-30'
AND RAND() > 0.02;

-- =====================================================
-- 4. DATA PERANGKAT PEMBELAJARAN (RPP/Modul Ajar)
-- =====================================================
INSERT INTO dasb_perangkat_pembelajaran (id_user, id_semester, id_mapel, id_kelas, link_file, status_validasi, created_at)
SELECT 
    u.id_user,
    1 as id_semester,
    j.id_mapel,
    j.id_kelas,
    CONCAT('https://drive.google.com/file/d/rpp_', u.id_user, '_', j.id_mapel, '_', j.id_kelas),
    CASE 
        WHEN RAND() < 0.70 THEN 'valid'
        WHEN RAND() < 0.85 THEN 'pending'
        ELSE 'revisi'
    END as status_validasi,
    DATE('2025-08-01') + INTERVAL (RAND() * 60) DAY as created_at
FROM dasb_users u
JOIN dasb_guru g ON u.username = g.nip
JOIN dasb_jadwal j ON u.id_user = j.id_user
WHERE j.id_semester = 1
GROUP BY u.id_user, j.id_mapel, j.id_kelas;

-- =====================================================
-- 5. DATA OBSERVASI KINERJA GURU (PERBAIKAN)
-- =====================================================
INSERT INTO dasb_monitoring_observasi_kinerja (id_guru, id_semester, tanggal_observasi, id_observer, skor_pedagogik, skor_profesional, skor_kepribadian, skor_sosial, skor_total, kategori, catatan_observer, tindak_lanjut)
SELECT
    g.id_guru,
    1 as id_semester,
    DATE('2025-09-01') + INTERVAL (FLOOR(RAND() * 180)) DAY as tanggal_observasi,
    (SELECT id_user FROM dasb_users WHERE username = 'admin' LIMIT 1) as id_observer,
    skor_pedagogik,
    skor_profesional,
    skor_kepribadian,
    skor_sosial,
    ROUND((skor_pedagogik + skor_profesional + skor_kepribadian + skor_sosial) / 4, 2) as skor_total,
    CASE 
        WHEN ROUND((skor_pedagogik + skor_profesional + skor_kepribadian + skor_sosial) / 4, 2) >= 85 THEN 'Baik'
        WHEN ROUND((skor_pedagogik + skor_profesional + skor_kepribadian + skor_sosial) / 4, 2) >= 70 THEN 'Cukup'
        ELSE 'Perlu Perbaikan'
    END as kategori,
    CASE 
        WHEN ROUND((skor_pedagogik + skor_profesional + skor_kepribadian + skor_sosial) / 4, 2) >= 85 THEN 'Kinerja sangat baik, menjadi teladan bagi guru lain'
        WHEN ROUND((skor_pedagogik + skor_profesional + skor_kepribadian + skor_sosial) / 4, 2) >= 70 THEN 'Kinerja cukup, perlu peningkatan pada aspek tertentu'
        ELSE 'Perlu pendampingan intensif dan pelatihan'
    END as catatan_observer,
    CASE 
        WHEN ROUND((skor_pedagogik + skor_profesional + skor_kepribadian + skor_sosial) / 4, 2) >= 85 THEN 'Pertahankan dan tingkatkan inovasi'
        WHEN ROUND((skor_pedagogik + skor_profesional + skor_kepribadian + skor_sosial) / 4, 2) >= 70 THEN 'Ikuti pelatihan pengembangan kompetensi'
        ELSE 'Program pembinaan khusus dari Waka Kurikulum'
    END as tindak_lanjut
FROM (
    SELECT 
        id_guru,
        ROUND(65 + RAND() * 30, 2) as skor_pedagogik,
        ROUND(65 + RAND() * 30, 2) as skor_profesional,
        ROUND(70 + RAND() * 25, 2) as skor_kepribadian,
        ROUND(65 + RAND() * 30, 2) as skor_sosial
    FROM dasb_guru
) g
WHERE RAND() > 0.3
LIMIT 40;

-- =====================================================
-- 6. DATA TUGAS ADMINISTRATIF GURU
-- =====================================================
INSERT INTO dasb_monitoring_tugas_administratif (id_guru, jenis_tugas, deadline, tanggal_selesai, status, keterangan)
SELECT
    g.id_guru,
    CASE (RAND() * 4)
        WHEN 0 THEN 'Laporan Bulanan'
        WHEN 1 THEN 'Input Nilai Rapor'
        WHEN 2 THEN 'Laporan Capaian Pembelajaran'
        WHEN 3 THEN 'Dokumen Kurikulum'
        ELSE 'Laporan Bimbingan'
    END as jenis_tugas,
    DATE('2026-03-01') + INTERVAL (RAND() * 60) DAY as deadline,
    CASE 
        WHEN RAND() < 0.85 THEN DATE('2026-03-01') + INTERVAL (RAND() * 50) DAY
        ELSE NULL
    END as tanggal_selesai,
    CASE 
        WHEN RAND() < 0.85 THEN 'Selesai'
        WHEN RAND() < 0.95 THEN 'Belum Selesai'
        ELSE 'Terlambat'
    END as status,
    CASE 
        WHEN RAND() < 0.85 THEN NULL
        ELSE 'Menunggu kelengkapan data dari wali kelas'
    END as keterangan
FROM dasb_guru g
WHERE RAND() > 0.2
LIMIT 60;

-- =====================================================
-- 7. DATA TARGET KPI
-- =====================================================
INSERT INTO dasb_monitoring_target_kpi (id_semester, indikator, target_persen, bobot_kpi) VALUES
(1, 'Kehadiran Guru', 95.00, 25.00),
(1, 'Kelengkapan RPP', 85.00, 25.00),
(1, 'Skor Observasi Minimal', 80.00, 30.00),
(1, 'Tugas Administratif Tepat Waktu', 90.00, 20.00);

-- =====================================================
-- 8. UPDATE STATUS KEHADIRAN HARI INI (April 2026)
-- =====================================================
-- Pastikan ada data kehadiran untuk hari ini (CURDATE())
INSERT INTO dasb_monitoring_kehadiran_guru (id_guru, tanggal, jam_masuk, jam_keluar, status, keterangan)
SELECT 
    id_guru,
    CURDATE() as tanggal,
    '07:15:00' as jam_masuk,
    '15:30:00' as jam_keluar,
    CASE 
        WHEN id_guru IN (4, 6, 9) THEN 'Izin'
        WHEN id_guru = 8 THEN 'Sakit'
        ELSE 'Hadir'
    END as status,
    CASE 
        WHEN id_guru IN (4, 6, 9) THEN 'Ada keperluan keluarga'
        WHEN id_guru = 8 THEN 'Sakit demam'
        ELSE NULL
    END as keterangan
FROM dasb_guru
WHERE NOT EXISTS (
    SELECT 1 FROM dasb_monitoring_kehadiran_guru 
    WHERE id_guru = dasb_guru.id_guru AND tanggal = CURDATE()
);

-- =====================================================
-- 9. VERIFIKASI DATA
-- =====================================================
SELECT '=== DATA TAHUN AJARAN ===' as info;
SELECT * FROM dasb_tahunajaran;

SELECT '=== DATA GURU ===' as info;
SELECT COUNT(*) as total_guru FROM dasb_guru;

SELECT '=== DATA KEHADIRAN ===' as info;
SELECT 
    DATE_FORMAT(tanggal, '%Y-%m') as bulan,
    COUNT(*) as total_data
FROM dasb_monitoring_kehadiran_guru
GROUP BY DATE_FORMAT(tanggal, '%Y-%m')
ORDER BY bulan;

SELECT '=== DATA PERANGKAT PEMBELAJARAN ===' as info;
SELECT 
    status_validasi,
    COUNT(*) as jumlah
FROM dasb_perangkat_pembelajaran
GROUP BY status_validasi;

SELECT '=== DATA OBSERVASI ===' as info;
SELECT 
    kategori,
    COUNT(*) as jumlah
FROM dasb_monitoring_observasi_kinerja
GROUP BY kategori;

SELECT '=== DATA TUGAS ===' as info;
SELECT 
    status,
    COUNT(*) as jumlah
FROM dasb_monitoring_tugas_administratif
GROUP BY status;

SELECT '=== KEHADIRAN HARI INI ===' as info;
SELECT 
    COUNT(CASE WHEN status IN ('Hadir', 'Dinas Luar') THEN 1 END) as hadir,
    COUNT(*) as total_guru,
    ROUND(COUNT(CASE WHEN status IN ('Hadir', 'Dinas Luar') THEN 1 END) * 100.0 / COUNT(*), 0) as persen
FROM dasb_monitoring_kehadiran_guru
WHERE tanggal = CURDATE();

SELECT 'Data dummy berhasil ditambahkan!' as status;