-- ============================================================ -- seeder.sql — Data Dummy untuk WebGIS Poverty Mapping -- Lokasi: Kota Pontianak, Kalimantan Barat -- ============================================================ USE `poverty_mapping`; SET FOREIGN_KEY_CHECKS = 0; -- ============================================================ -- 1. USERS (admin + 2 field officers) -- Password untuk semua: password (hash: $2y$10$E7A/QogdGQz9W4DB1YcYl.7KvET/Ru6mZ3k8s1.e700lkMnrnsn4i) -- ============================================================ INSERT INTO `users` (`name`, `email`, `password_hash`, `role`, `is_active`) VALUES ('Administrator', 'admin', '$2y$10$E7A/QogdGQz9W4DB1YcYl.7KvET/Ru6mZ3k8s1.e700lkMnrnsn4i', 'admin', 1), ('Budi Santoso', 'petugas', '$2y$10$E7A/QogdGQz9W4DB1YcYl.7KvET/Ru6mZ3k8s1.e700lkMnrnsn4i', 'field_officer', 1), ('Siti Rahayu', 'petugas2', '$2y$10$E7A/QogdGQz9W4DB1YcYl.7KvET/Ru6mZ3k8s1.e700lkMnrnsn4i', 'field_officer', 1), ('Viewer Demo', 'viewer@povertymapping.id', '$2y$10$E7A/QogdGQz9W4DB1YcYl.7KvET/Ru6mZ3k8s1.e700lkMnrnsn4i', 'viewer', 1); -- ============================================================ -- 2. RELIGIOUS CENTERS (Tempat Ibadah di Pontianak) -- ============================================================ INSERT INTO `religious_centers` (`name`, `worship_type`, `address`, `kelurahan`, `kecamatan`, `latitude`, `longitude`, `radius`, `contact_person`, `contact_phone`, `notes`) VALUES ('Masjid Raya Pontianak', 'masjid', 'Jl. Khatulistiwa No. 1', 'Batu Layang', 'Pontianak Utara', -0.0300000, 109.3250000, 500, 'H. Ahmad Yani', '08112233445', 'Masjid utama Kota Pontianak'), ('Masjid Jami', 'masjid', 'Jl. Pahlawan No. 15', 'Tanjung Hulu', 'Pontianak Timur', -0.0350000, 109.3280000, 400, 'Ustadz Hamid', '08122334456', NULL), ('Gereja Katedral Pontianak', 'gereja', 'Jl. Katedral No. 2', 'Benuamelayu Darat','Pontianak Selatan', -0.0420000, 109.3300000, 350, 'Pdt. Samuel Runtu', '08133445567', 'Pelayanan sosial aktif'), ('Vihara Bodhisattva', 'vihara', 'Jl. Gusti Situt Mahmud No. 8', 'Siantan Hilir', 'Pontianak Utara', -0.0350000, 109.3200000, 300, 'Bhiksu Dharmavira', '08144556678', NULL), ('Klenteng Tri Dharma', 'klenteng', 'Jl. Tanjungpura No. 23', 'Tanjungpura', 'Pontianak Barat', -0.0450000, 109.3350000, 300, 'Ibu Meri', '08155667789', NULL), ('Pura Jagadnatha', 'pura', 'Jl. Perdana No. 5', 'Perdana', 'Pontianak Tenggara',-0.0400000, 109.3280000, 350, 'Mangku Wayan', '08166778890', NULL); -- ============================================================ -- 3. HOUSEHOLDS (15 records, varied poverty levels) -- Koordinat tersebar di sekitar Pontianak -- ============================================================ INSERT INTO `households` (`rt`,`rw`,`kelurahan`,`kecamatan`,`full_address`,`latitude`,`longitude`, `house_condition`,`land_ownership`,`head_name`,`head_nik`,`head_gender`, `head_date_of_birth`,`head_education`,`head_employment_status`, `head_job_name`,`head_monthly_income`,`poverty_score`,`poverty_status`, `aid_status`,`managing_center_id`,`notes`) VALUES -- sangat_miskin (score >= 50) ('001','001','Batu Layang','Pontianak Utara','Jl. Khatulistiwa Gg. 3 No. 5', -0.0315000, 109.3260000, 'tidak_layak','numpang', 'Hasan Basri', '6171010101800001','male', '1980-01-15','sd', 'unemployed', NULL, 350000, 72, 'sangat_miskin','not_yet', 1, 'Tinggal menumpang di tanah orang lain'), ('002','001','Batu Layang','Pontianak Utara','Jl. Khatulistiwa Gg. 5 No. 2', -0.0320000, 109.3270000, 'tidak_layak','numpang', 'Amiruddin', '6171010202790002','male', '1979-02-20','tidak_sekolah','unemployed', NULL, 280000, 85, 'sangat_miskin','not_yet', 1, NULL), ('001','002','Tanjung Hulu','Pontianak Timur','Jl. Pahlawan Dalam No. 9', -0.0360000, 109.3290000, 'tidak_layak','sewa', 'Nurhayati', '6171020303820003','female','1982-03-03','sd', 'unemployed', NULL, 420000, 68, 'sangat_miskin','received', 2, 'Ibu tunggal, 5 anak'), -- miskin (score 30-49) ('003','001','Batu Layang','Pontianak Utara','Jl. Khatulistiwa Gg. 2 No. 11', -0.0305000, 109.3255000, 'tidak_layak','milik', 'Ruslan Daeng', '6171010404780004','male', '1978-04-10','smp', 'daily_labor', 'Buruh harian', 850000, 45, 'miskin', 'not_yet', 1, NULL), ('002','002','Tanjung Hulu','Pontianak Timur','Jl. Pahlawan Raya No. 7', -0.0345000, 109.3275000, 'tidak_layak','sewa', 'Daeng Tawing', '6171020505750005','male', '1975-05-05','sd', 'daily_labor', 'Kuli bangunan',950000, 42, 'miskin', 'received', 2, NULL), ('001','001','Benuamelayu Darat','Pontianak Selatan','Jl. Katedral Gg. 3 No. 4', -0.0415000, 109.3310000, 'tidak_layak','numpang','Marlina Sari', '6171030606850006','female','1985-06-06','smp', 'unemployed', NULL, 600000, 50, 'miskin', 'not_yet', 3, 'Suami meninggal 2023'), ('002','003','Siantan Hilir','Pontianak Utara','Jl. Gusti Situt Mahmud No. 12', -0.0360000, 109.3190000, 'tidak_layak','sewa', 'Wahyu Prasetyo', '6171040707900007','male', '1990-07-07','smp', 'daily_labor', 'Tukang becak', 900000, 38, 'miskin', 'received', 4, NULL), -- rentan_miskin (score 15-29) ('003','002','Siantan Hilir','Pontianak Utara','Jl. Bodhisattva Gg. 2 No. 8', -0.0370000, 109.3210000, 'layak', 'sewa', 'Syamsuddin', '6171030808880008','male', '1988-08-08','sma', 'self_employed','Pedagang kaki lima',1200000, 25,'rentan_miskin','not_yet', 4, NULL), ('002','001','Batu Layang','Pontianak Utara','Jl. Khatulistiwa Gg. 8 No. 6', -0.0310000, 109.3265000, 'layak', 'milik', 'Rahma Wati', '6171010909870009','female','1987-09-09','sma', 'self_employed','Warung kecil', 1500000, 20,'rentan_miskin','not_yet', 1, NULL), ('003','003','Tanjung Hulu','Pontianak Timur','Jl. Hertasning No. 22', -0.0355000, 109.3285000, 'layak', 'sewa', 'Arif Rahman', '6171021010850010','male', '1985-10-10','sma', 'employee', 'Satpam', 1800000, 18,'rentan_miskin','received', 2, NULL), ('001','004','Tanjungpura','Pontianak Barat','Jl. Tanjungpura Gg. 3 No. 9', -0.0440000, 109.3360000, 'layak', 'sewa', 'Dewi Sartika', '6171041111920011','female','1992-11-11','sma', 'employee', 'Kasir', 1600000, 22,'rentan_miskin','not_yet', 5, NULL), -- terdata (score < 15) ('001','001','Benuamelayu Darat','Pontianak Selatan','Jl. Katedral Raya No. 3', -0.0425000, 109.3305000, 'layak', 'milik', 'Eko Setiawan', '6171031212800012','male', '1980-12-12','sma', 'employee', 'Pegawai swasta',2500000, 8, 'terdata', 'not_yet', 3, NULL), ('002','003','Tanjung Hulu','Pontianak Timur','Jl. Pahlawan Baru No. 15', -0.0340000, 109.3270000, 'layak', 'milik', 'Indah Permata', '6171020101930013','female','1993-01-01','diploma', 'employee', 'Perawat', 2800000, 5, 'terdata', 'not_yet', 2, NULL), ('001','002','Siantan Hilir','Pontianak Utara','Jl. Bodhisattva No. 4', -0.0380000, 109.3220000, 'layak', 'milik', 'Firmansyah', '6171030202820014','male', '1982-02-02','sarjana', 'employee', 'Guru SMP', 3200000, 3, 'terdata', 'not_yet', 4, NULL), ('003','001','Tanjungpura','Pontianak Barat','Jl. Tri Dharma No. 20', -0.0460000, 109.3340000, 'layak', 'milik', 'Sari Bulan', '6171040303850015','female','1985-03-03','sarjana', 'employee', 'Bidan', 3500000, 2, 'terdata', 'not_yet', 5, NULL); -- ============================================================ -- 4. HOUSEHOLD MEMBERS (anggota keluarga) -- ============================================================ INSERT INTO `household_members` (`household_id`,`name`,`nik`,`gender`,`date_of_birth`,`education`,`relationship`,`employment_status`,`monthly_income`) VALUES -- Household 1 (Hasan Basri — sangat_miskin) (1, 'Salmah', '6171010101820101', 'female', '1982-03-10', 'sd', 'istri', 'unemployed', 0), (1, 'Muhammad Rizki', '6171010101050102', 'male', '2005-06-15', 'smp', 'anak', 'unemployed', 0), (1, 'Nur Aisyah', '6171010101080103', 'female', '2008-09-20', 'sd', 'anak', 'unemployed', 0), -- Household 2 (Amiruddin — sangat_miskin) (2, 'Rohani', '6171010202810201', 'female', '1981-05-12', 'tidak_sekolah','istri', 'unemployed', 0), (2, 'Akbar', '6171010202030202', 'male', '2003-08-25', 'sd', 'anak', 'daily_labor', 500000), (2, 'Mariam', '6171010202060203', 'female', '2006-11-30', 'sd', 'anak', 'unemployed', 0), (2, 'Sulastri', '6171010202100204', 'female', '2010-02-14', 'sd', 'anak', 'unemployed', 0), (2, 'Hendra', '6171010202130205', 'male', '2013-07-07', 'sd', 'anak', 'unemployed', 0), -- Household 3 (Nurhayati — sangat_miskin, single mother) (3, 'Fitri', '6171020303050301', 'female', '2005-01-10', 'smp', 'anak', 'unemployed', 0), (3, 'Fathur', '6171020303080302', 'male', '2008-04-22', 'sd', 'anak', 'unemployed', 0), (3, 'Zulaikha', '6171020303110303', 'female', '2011-09-05', 'sd', 'anak', 'unemployed', 0), (3, 'Ilham', '6171020303140304', 'male', '2014-12-18', 'sd', 'anak', 'unemployed', 0), (3, 'Rania', '6171020303180305', 'female', '2018-03-28', 'sd', 'anak', 'unemployed', 0), -- Household 4 (Ruslan Daeng — miskin) (4, 'Hasnah', '6171010404810401', 'female', '1981-07-14', 'sd', 'istri', 'unemployed', 0), (4, 'Bagas', '6171010404030402', 'male', '2003-10-25', 'smp', 'anak', 'daily_labor', 600000), (4, 'Nabila', '6171010404060403', 'female', '2006-02-08', 'smp', 'anak', 'unemployed', 0), -- Household 8 (Syamsuddin — rentan_miskin) (8, 'Sri Wahyuni', '6171030808900801', 'female', '1990-04-11', 'sma', 'istri', 'self_employed',800000), (8, 'Alif', '6171030808150802', 'male', '2015-08-19', 'sd', 'anak', 'unemployed', 0), -- Household 12 (Eko Setiawan — terdata) (12,'Lina Karlina', '6171031212820121', 'female', '1982-06-30', 'sma', 'istri', 'employee', 1800000), (12,'Kevin Eko', '6171031212100122', 'male', '2010-11-11', 'sd', 'anak', 'unemployed', 0); -- ============================================================ -- 5. AID HISTORY (bantuan untuk beberapa rumah tangga) -- ============================================================ INSERT INTO `aid_history` (`household_id`,`center_id`,`aid_type`,`aid_date`,`amount`,`description`,`given_by`) VALUES (3, 2, 'sembako', '2025-01-15', NULL, 'Paket sembako Ramadan 2025', 1), (3, 2, 'pendanaan', '2025-03-20', 500000, 'Bantuan biaya sekolah anak', 2), (5, 2, 'sembako', '2025-02-10', NULL, 'Paket sembako bulanan Februari', 2), (7, 4, 'pelatihan', '2025-04-05', NULL, 'Pelatihan keterampilan menjahit', 3), (10, 2, 'sembako_pendanaan', '2025-05-01', 750000, 'Bantuan kombinasi lebaran', 1), (1, 1, 'sembako', '2025-06-01', NULL, 'Paket sembako Juni', 2), (2, 1, 'lengkap', '2025-01-10', 1000000, 'Bantuan lengkap awal tahun', 1), (4, 1, 'pendanaan', '2025-06-05', 300000, 'Subsidi biaya pengobatan', 3); -- ============================================================ -- 6. EMERGENCY REPORTS (laporan darurat) -- ============================================================ INSERT INTO `emergency_reports` (`household_id`,`type`,`severity`,`description`,`status`,`resolved_at`) VALUES (2, 'sakit', 'kritis', 'Kepala keluarga menderita stroke, butuh bantuan biaya pengobatan segera.', 'open', NULL), (3, 'kehilangan_pekerjaan', 'berat', 'Ibu tunggal kehilangan sumber pendapatan akibat warung tutup karena banjir.', 'in_progress', NULL), (1, 'bencana', 'sedang', 'Atap rumah bocor parah akibat hujan deras, 3 anak tinggal di kondisi tidak layak.', 'resolved', '2025-05-20 10:00:00'), (6, 'kematian', 'berat', 'Suami meninggal mendadak, istri tidak memiliki penghasilan.', 'resolved', '2025-03-15 14:30:00'); -- ============================================================ -- 7. PUBLIC REPORTS (laporan dari masyarakat Pontianak) -- ============================================================ INSERT INTO `public_reports` (`reporter_name`,`reporter_phone`,`rt`,`rw`,`head_name`,`address`,`kelurahan`,`kecamatan`, `latitude`,`longitude`,`description`,`severity`,`urgent_need`,`proof_photos`, `status`,`admin_notes`,`reviewed_at`,`converted_household_id`,`ip_address`) VALUES ('Ahmad Fauzi', '081234567890', '005','002', 'Pak Dullah', 'Jl. Khatulistiwa Gg. 3 No. 7', 'Batu Layang', 'Pontianak Utara', -0.0325000, 109.3265000, 'Keluarga ini tinggal di gubuk reyot dengan 6 anak. Tidak ada pekerjaan tetap dan sering kelaparan. Butuh bantuan segera.', 'kritis', 'Sembako dan biaya renovasi rumah', '["reports_1_abc123.jpg"]', 'approved', 'Data sudah diverifikasi dan diinput sebagai rumah tangga baru.', '2025-04-10 09:30:00', NULL, '192.168.1.10'), ('Dewi Lestari', '082345678901', '001','003', 'Ibu Saodah', 'Jl. Tanjungpura Gg. 5 No. 2', 'Tanjungpura', 'Pontianak Barat', -0.0445000, 109.3355000, 'Nenek tua hidup sebatang kara, tidak punya penghasilan. Rumah hampir roboh.', 'berat', 'Bantuan bahan makanan dan perbaikan rumah', '["reports_2_def456.jpg","reports_2_ghi789.jpg"]', 'pending', NULL, NULL, NULL, '192.168.1.20'), ('Rudi Hermawan', '083456789012', '003','001', 'Keluarga Pak Samson', 'Jl. Pahlawan Gg. 2 No. 8', 'Tanjung Hulu', 'Pontianak Timur', -0.0355000, 109.3280000, 'Keluarga dengan 4 anak kecil, penghasilan tidak mencukupi untuk makan sehari-hari.', 'sedang', 'Bantuan sembako rutin', NULL, 'rejected', 'Data tidak dapat diverifikasi, koordinat tidak sesuai lokasi.', '2025-05-01 14:00:00', NULL, '10.0.0.5'); -- ============================================================ -- 8. AUDIT LOGS (sample entries) -- ============================================================ INSERT INTO `audit_logs` (`action`,`table_name`,`record_id`,`old_values`,`new_values`,`ip_address`,`user_agent`) VALUES ('auth.login', 'users', 1, NULL, '{"email":"admin@povertymapping.id"}', '127.0.0.1', 'Mozilla/5.0'), ('Tambah Rumah', 'households', 1, NULL, '{"head_name":"Hasan Basri"}', '127.0.0.1', 'Mozilla/5.0'), ('Tambah Rumah', 'households', 2, NULL, '{"head_name":"Amiruddin"}', '127.0.0.1', 'Mozilla/5.0'), ('Catat Bantuan', 'aid_history', 1, NULL, '{"aid_type":"sembako","amount":null}', '127.0.0.1', 'Mozilla/5.0'), ('auth.login', 'users', 2, NULL, '{"email":"budi@povertymapping.id"}', '192.168.1.1', 'Mozilla/5.0'), ('Tambah Laporan Darurat','emergency_reports',1,NULL,'{"type":"sakit","severity":"kritis"}','192.168.1.1','Mozilla/5.0'); SET FOREIGN_KEY_CHECKS = 1; -- ============================================================ -- VERIFIKASI (Cek jumlah data) -- ============================================================ SELECT 'users' AS `table`, COUNT(*) AS row_count FROM users UNION ALL SELECT 'religious_centers', COUNT(*) FROM religious_centers UNION ALL SELECT 'households', COUNT(*) FROM households UNION ALL SELECT 'household_members', COUNT(*) FROM household_members UNION ALL SELECT 'aid_history', COUNT(*) FROM aid_history UNION ALL SELECT 'emergency_reports', COUNT(*) FROM emergency_reports UNION ALL SELECT 'public_reports', COUNT(*) FROM public_reports UNION ALL SELECT 'audit_logs', COUNT(*) FROM audit_logs;