

-- ============================================
-- Table: desa
-- ============================================
CREATE TABLE `desa` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_desa` varchar(255) NOT NULL,
  `kecamatan` varchar(255) NOT NULL,
  `kabupaten` varchar(255) NOT NULL DEFAULT 'Gunungkidul',
  `alamat` text DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `jumlah_penduduk` int(11) NOT NULL DEFAULT 0,
  `kebutuhan_air_harian` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: dusun
-- ============================================
CREATE TABLE `dusun` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `desa_id` bigint(20) UNSIGNED NOT NULL,
  `nama_dusun` varchar(255) NOT NULL,
  `jumlah_rt` int(11) NOT NULL DEFAULT 0,
  `jumlah_rw` int(11) NOT NULL DEFAULT 0,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `dusun_desa_id_foreign` (`desa_id`),
  CONSTRAINT `dusun_desa_id_foreign` FOREIGN KEY (`desa_id`) REFERENCES `desa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: prediksi
-- ============================================
CREATE TABLE `prediksi` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `desa_id` bigint(20) UNSIGNED NOT NULL,
  `dusun_id` bigint(20) UNSIGNED DEFAULT NULL,
  `tanggal_prediksi` date NOT NULL,
  `curah_hujan` decimal(8,2) NOT NULL DEFAULT 0.00,
  `kelembapan_tanah` decimal(5,2) NOT NULL DEFAULT 0.00,
  `tingkat_risiko` enum('aman','waspada','kritis') NOT NULL DEFAULT 'aman',
  `risiko_score` int(11) NOT NULL DEFAULT 0,
  `rekomendasi` text DEFAULT NULL,
  `data_bmkg` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `prediksi_desa_id_foreign` (`desa_id`),
  KEY `prediksi_dusun_id_foreign` (`dusun_id`),
  CONSTRAINT `prediksi_desa_id_foreign` FOREIGN KEY (`desa_id`) REFERENCES `desa` (`id`) ON DELETE CASCADE,
  CONSTRAINT `prediksi_dusun_id_foreign` FOREIGN KEY (`dusun_id`) REFERENCES `dusun` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: laporan
-- ============================================
CREATE TABLE `laporan` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `desa_id` bigint(20) UNSIGNED NOT NULL,
  `dusun_id` bigint(20) UNSIGNED DEFAULT NULL,
  `jenis_laporan` enum('sumur_kering','mata_air_kering','kebutuhan_air','lainnya') NOT NULL,
  `deskripsi` text NOT NULL,
  `foto` varchar(255) DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `status` enum('pending','ditinjau','selesai') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `laporan_user_id_foreign` (`user_id`),
  KEY `laporan_desa_id_foreign` (`desa_id`),
  KEY `laporan_dusun_id_foreign` (`dusun_id`),
  CONSTRAINT `laporan_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `laporan_desa_id_foreign` FOREIGN KEY (`desa_id`) REFERENCES `desa` (`id`) ON DELETE CASCADE,
  CONSTRAINT `laporan_dusun_id_foreign` FOREIGN KEY (`dusun_id`) REFERENCES `dusun` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: tangki
-- ============================================
CREATE TABLE `tangki` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `desa_id` bigint(20) UNSIGNED NOT NULL,
  `dusun_id` bigint(20) UNSIGNED DEFAULT NULL,
  `petugas_id` bigint(20) UNSIGNED DEFAULT NULL,
  `tanggal_pengiriman` date NOT NULL,
  `waktu_pengiriman` time DEFAULT NULL,
  `volume_air` int(11) NOT NULL DEFAULT 0,
  `prioritas` int(11) NOT NULL DEFAULT 0,
  `status` enum('dijadwalkan','dalam_perjalanan','selesai','dibatalkan') NOT NULL DEFAULT 'dijadwalkan',
  `catatan` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tangki_desa_id_foreign` (`desa_id`),
  KEY `tangki_dusun_id_foreign` (`dusun_id`),
  KEY `tangki_petugas_id_foreign` (`petugas_id`),
  CONSTRAINT `tangki_desa_id_foreign` FOREIGN KEY (`desa_id`) REFERENCES `desa` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tangki_dusun_id_foreign` FOREIGN KEY (`dusun_id`) REFERENCES `dusun` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tangki_petugas_id_foreign` FOREIGN KEY (`petugas_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: notifikasi
-- ============================================
CREATE TABLE `notifikasi` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `desa_id` bigint(20) UNSIGNED DEFAULT NULL,
  `judul` varchar(255) NOT NULL,
  `pesan` text NOT NULL,
  `tipe` enum('peringatan','info','penting') NOT NULL DEFAULT 'info',
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `notifikasi_user_id_foreign` (`user_id`),
  KEY `notifikasi_desa_id_foreign` (`desa_id`),
  CONSTRAINT `notifikasi_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `notifikasi_desa_id_foreign` FOREIGN KEY (`desa_id`) REFERENCES `desa` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table: menu_icons
-- ============================================
CREATE TABLE `menu_icons` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_menu` varchar(255) NOT NULL,
  `icon_class` varchar(255) NOT NULL DEFAULT 'fa-solid fa-circle',
  `route_name` varchar(255) NOT NULL,
  `role_access` enum('all','admin','warga','pemdes','petugas_tangki') NOT NULL DEFAULT 'all',
  `urutan` int(11) NOT NULL DEFAULT 0,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- INSERT DATA: desa (Gunungkidul)
-- ============================================
INSERT INTO `desa` (`id`, `nama_desa`, `kecamatan`, `kabupaten`, `alamat`, `latitude`, `longitude`, `jumlah_penduduk`, `kebutuhan_air_harian`, `created_at`, `updated_at`) VALUES
(1, 'Semanu', 'Semanu', 'Gunungkidul', 'Jl. Raya Semanu, Gunungkidul', -7.9845123, 110.6234567, 4500, 225000, NOW(), NOW()),
(2, 'Girisubo', 'Girisubo', 'Gunungkidul', 'Jl. Pantai Girisubo, Gunungkidul', -8.1234567, 110.7345678, 3800, 190000, NOW(), NOW()),
(3, 'Tepus', 'Tepus', 'Gunungkidul', 'Jl. Pantai Tepus, Gunungkidul', -8.0987654, 110.6543210, 5200, 260000, NOW(), NOW()),
(4, 'Rongkop', 'Rongkop', 'Gunungkidul', 'Jl. Raya Rongkop, Gunungkidul', -8.0456789, 110.5678901, 4100, 205000, NOW(), NOW()),
(5, 'Dadapayu', 'Semanu', 'Gunungkidul', 'Dusun Dadapayu, Semanu', -7.9923456, 110.6345678, 2300, 115000, NOW(), NOW()),
(6, 'Nglindur', 'Semanu', 'Gunungkidul', 'Dusun Nglindur, Semanu', -7.9834567, 110.6456789, 1900, 95000, NOW(), NOW());

-- ============================================
-- INSERT DATA: dusun
-- ============================================
INSERT INTO `dusun` (`id`, `desa_id`, `nama_dusun`, `jumlah_rt`, `jumlah_rw`, `latitude`, `longitude`, `created_at`, `updated_at`) VALUES
(1, 1, 'Dusun Krajan', 8, 3, -7.9845123, 110.6234567, NOW(), NOW()),
(2, 1, 'Dusun Dadapayu', 6, 2, -7.9923456, 110.6345678, NOW(), NOW()),
(3, 1, 'Dusun Nglindur', 5, 2, -7.9834567, 110.6456789, NOW(), NOW()),
(4, 2, 'Dusun Jetis', 7, 3, -8.1234567, 110.7345678, NOW(), NOW()),
(5, 2, 'Dusun Jepitu', 6, 2, -8.1345678, 110.7456789, NOW(), NOW()),
(6, 3, 'Dusun Giripurwo', 9, 4, -8.0987654, 110.6543210, NOW(), NOW()),
(7, 3, 'Dusun Sidoharjo', 7, 3, -8.1098765, 110.6654321, NOW(), NOW()),
(8, 4, 'Dusun Bohol', 8, 3, -8.0456789, 110.5678901, NOW(), NOW()),
(9, 4, 'Dusun Karangwetan', 6, 2, -8.0567890, 110.5789012, NOW(), NOW());

-- ============================================
-- INSERT DATA: menu_icons
-- ============================================
INSERT INTO `menu_icons` (`id`, `nama_menu`, `icon_class`, `route_name`, `role_access`, `urutan`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Prediksi AI', 'fas fa-brain', 'prediksi.index', 'all', 1, 1, NOW(), NOW()),
(2, 'Peta Desa', 'fas fa-map-marked-alt', 'peta', 'all', 2, 1, NOW(), NOW()),
(3, 'Laporan', 'fas fa-file-alt', 'laporan.index', 'all', 3, 1, NOW(), NOW()),
(4, 'Notifikasi', 'fas fa-bell', 'notifikasi.index', 'all', 4, 1, NOW(), NOW()),
(5, 'Peringatan', 'fas fa-exclamation-circle', 'peringatan.index', 'all', 5, 1, NOW(), NOW()),
(6, 'Statistik', 'fas fa-chart-line', 'statistik.index', 'all', 6, 1, NOW(), NOW()),
(7, 'Admin Panel', 'fas fa-cog', 'admin.dashboard', 'admin', 7, 1, NOW(), NOW()),
(8, 'Profile', 'fas fa-user-circle', 'profile', 'all', 8, 1, NOW(), NOW()),
(9, 'Tangki Air', 'fas fa-truck', 'tangki.index', 'admin', 9, 1, NOW(), NOW()),
(10, 'Manajemen Desa', 'fas fa-home', 'admin.desa.index', 'admin', 10, 1, NOW(), NOW());

-- ============================================
-- INSERT DATA: prediksi (Sample 7 hari)
-- ============================================
INSERT INTO `prediksi` (`desa_id`, `dusun_id`, `tanggal_prediksi`, `curah_hujan`, `kelembapan_tanah`, `tingkat_risiko`, `risiko_score`, `rekomendasi`, `data_bmkg`, `created_at`, `updated_at`) VALUES
-- Desa Semanu
(1, NULL, '2025-12-03', 12.50, 45.30, 'waspada', 55, 'Tingkat risiko sedang. Mulai hemat penggunaan air. Hindari pemborosan air untuk mencuci kendaraan atau menyiram tanaman non-esensial.', '{"suhu": 29, "angin": "5 km/h", "cuaca": "Berawan"}', NOW(), NOW()),
(1, NULL, '2025-12-04', 8.20, 38.50, 'waspada', 62, 'Tingkat risiko sedang. Mulai hemat penggunaan air. Hindari pemborosan air untuk mencuci kendaraan atau menyiram tanaman non-esensial.', '{"suhu": 30, "angin": "6 km/h", "cuaca": "Cerah Berawan"}', NOW(), NOW()),
(1, NULL, '2025-12-05', 5.00, 32.10, 'kritis', 75, 'PERINGATAN KRITIS! Segera lakukan penghematan air maksimal. Batasi penggunaan air untuk kebutuhan vital saja. Koordinasi dengan pemdes untuk distribusi air bersih.', '{"suhu": 31, "angin": "4 km/h", "cuaca": "Cerah"}', NOW(), NOW()),
(1, NULL, '2025-12-06', 3.20, 28.40, 'kritis', 82, 'PERINGATAN KRITIS! Segera lakukan penghematan air maksimal. Batasi penggunaan air untuk kebutuhan vital saja. Koordinasi dengan pemdes untuk distribusi air bersih.', '{"suhu": 32, "angin": "3 km/h", "cuaca": "Cerah"}', NOW(), NOW()),
(1, NULL, '2025-12-07', 15.80, 52.30, 'waspada', 48, 'Tingkat risiko sedang. Mulai hemat penggunaan air. Hindari pemborosan air untuk mencuci kendaraan atau menyiram tanaman non-esensial.', '{"suhu": 28, "angin": "7 km/h", "cuaca": "Hujan Ringan"}', NOW(), NOW()),
(1, NULL, '2025-12-08', 45.60, 68.50, 'aman', 25, 'Kondisi air masih aman. Tetap gunakan air secara bijak dan pantau perkembangan cuaca.', '{"suhu": 27, "angin": "8 km/h", "cuaca": "Hujan"}', NOW(), NOW()),
(1, NULL, '2025-12-09', 38.20, 65.20, 'aman', 28, 'Kondisi air masih aman. Tetap gunakan air secara bijak dan pantau perkembangan cuaca.', '{"suhu": 27, "angin": "7 km/h", "cuaca": "Hujan Ringan"}', NOW(), NOW()),

-- Desa Girisubo
(2, NULL, '2025-12-03', 6.30, 35.20, 'kritis', 70, 'PERINGATAN KRITIS! Segera lakukan penghematan air maksimal. Batasi penggunaan air untuk kebutuhan vital saja. Koordinasi dengan pemdes untuk distribusi air bersih.', '{"suhu": 30, "angin": "5 km/h", "cuaca": "Cerah"}', NOW(), NOW()),
(2, NULL, '2025-12-04', 4.50, 30.80, 'kritis', 78, 'PERINGATAN KRITIS! Segera lakukan penghematan air maksimal. Batasi penggunaan air untuk kebutuhan vital saja. Koordinasi dengan pemdes untuk distribusi air bersih.', '{"suhu": 31, "angin": "4 km/h", "cuaca": "Cerah"}', NOW(), NOW()),
(2, NULL, '2025-12-05', 2.10, 25.40, 'kritis', 88, 'PERINGATAN KRITIS! Segera lakukan penghematan air maksimal. Batasi penggunaan air untuk kebutuhan vital saja. Koordinasi dengan pemdes untuk distribusi air bersih.', '{"suhu": 32, "angin": "3 km/h", "cuaca": "Cerah"}', NOW(), NOW()),

-- Desa Tepus
(3, NULL, '2025-12-03', 18.40, 55.60, 'waspada', 42, 'Tingkat risiko sedang. Mulai hemat penggunaan air. Hindari pemborosan air untuk mencuci kendaraan atau menyiram tanaman non-esensial.', '{"suhu": 28, "angin": "8 km/h", "cuaca": "Berawan"}', NOW(), NOW()),
(3, NULL, '2025-12-04', 22.30, 60.20, 'aman', 35, 'Kondisi air masih aman. Tetap gunakan air secara bijak dan pantau perkembangan cuaca.', '{"suhu": 27, "angin": "9 km/h", "cuaca": "Hujan Ringan"}', NOW(), NOW()),

-- Desa Rongkop
(4, NULL, '2025-12-03', 10.20, 42.30, 'waspada', 58, 'Tingkat risiko sedang. Mulai hemat penggunaan air. Hindari pemborosan air untuk mencuci kendaraan atau menyiram tanaman non-esensial.', '{"suhu": 29, "angin": "6 km/h", "cuaca": "Cerah Berawan"}', NOW(), NOW()),
(4, NULL, '2025-12-04', 7.50, 38.70, 'waspada', 64, 'Tingkat risiko sedang. Mulai hemat penggunaan air. Hindari pemborosan air untuk mencuci kendaraan atau menyiram tanaman non-esensial.', '{"suhu": 30, "angin": "5 km/h", "cuaca": "Cerah"}', NOW(), NOW());

-- ============================================
-- INSERT DATA: laporan (Sample reports)
-- ============================================
INSERT INTO `laporan` (`user_id`, `desa_id`, `dusun_id`, `jenis_laporan`, `deskripsi`, `foto`, `latitude`, `longitude`, `status`, `created_at`, `updated_at`) VALUES
(2, 1, 1, 'sumur_kering', 'Sumur warga di RT 01 mulai mengering. Kedalaman air menurun drastis sejak 3 hari lalu.', NULL, -7.9845123, 110.6234567, 'pending', NOW(), NOW()),
(3, 2, 4, 'mata_air_kering', 'Mata air utama di Dusun Jetis sudah kering total. Warga kesulitan mendapatkan air bersih.', NULL, -8.1234567, 110.7345678, 'pending', NOW(), NOW()),
(6, 4, 8, 'kebutuhan_air', 'Mohon segera dikirim tangki air. Persediaan air di dusun sudah menipis.', NULL, -8.0456789, 110.5678901, 'ditinjau', NOW(), NOW()),
(2, 1, 2, 'sumur_kering', 'Sumur pompa di Dusun Dadapayu tidak bisa mengeluarkan air lagi.', NULL, -7.9923456, 110.6345678, 'pending', NOW(), NOW());

-- ============================================
-- INSERT DATA: tangki (Sample schedules)
-- ============================================
INSERT INTO `tangki` (`desa_id`, `dusun_id`, `petugas_id`, `tanggal_pengiriman`, `waktu_pengiriman`, `volume_air`, `prioritas`, `status`, `catatan`, `created_at`, `updated_at`) VALUES
(2, 4, 5, '2025-12-03', '08:00:00', 5000, 10, 'dijadwalkan', 'Prioritas tinggi - Mata air sudah kering total', NOW(), NOW()),
(1, 1, 5, '2025-12-03', '12:00:00', 3000, 8, 'dijadwalkan', 'Sumur mulai mengering', NOW(), NOW()),
(4, 8, 5, '2025-12-04', '09:00:00', 4000, 7, 'dijadwalkan', 'Permintaan dari warga', NOW(), NOW()),
(1, 2, 5, '2025-12-04', '14:00:00', 3500, 6, 'dijadwalkan', 'Pompa tidak berfungsi', NOW(), NOW());

-- ============================================
-- INSERT DATA: notifikasi (Sample notifications)
-- ============================================
INSERT INTO `notifikasi` (`user_id`, `desa_id`, `judul`, `pesan`, `tipe`, `is_read`, `created_at`, `updated_at`) VALUES
(2, 1, 'Peringatan Kekeringan', 'Desa Semanu akan mengalami kekeringan dalam 2 hari. Harap hemat penggunaan air!', 'peringatan', 0, NOW(), NOW()),
(3, 2, 'Status Kritis!', 'Tingkat risiko kekeringan KRITIS untuk Desa Girisubo. Segera lakukan penghematan maksimal!', 'penting', 0, NOW(), NOW()),
(4, 3, 'Prediksi Hujan', 'Prediksi hujan untuk Desa Tepus dalam 24 jam ke depan. Kondisi akan membaik.', 'info', 0, NOW(), NOW()),
(6, 4, 'Tangki Air Dijadwalkan', 'Tangki air akan dikirim ke Dusun Bohol pada tanggal 4 Desember 2025 pukul 09:00.', 'info', 0, NOW(), NOW()),
(NULL, 1, 'Update Sistem', 'SumberAI telah diperbarui dengan fitur prediksi AI yang lebih akurat.', 'info', 0, NOW(), NOW());
ALTER TABLE `desa` AUTO_INCREMENT = 7;
ALTER TABLE `dusun` AUTO_INCREMENT = 10;
ALTER TABLE `prediksi` AUTO_INCREMENT = 1;
ALTER TABLE `laporan` AUTO_INCREMENT = 5;
ALTER TABLE `tangki` AUTO_INCREMENT = 5;
ALTER TABLE `notifikasi` AUTO_INCREMENT = 6;
ALTER TABLE `menu_icons` AUTO_INCREMENT = 11;

-- ============================================
-- CREATE INDEXES FOR PERFORMANCE
-- ============================================
CREATE INDEX idx_prediksi_tanggal ON prediksi(tanggal_prediksi);
CREATE INDEX idx_prediksi_risiko ON prediksi(tingkat_risiko);
CREATE INDEX idx_laporan_status ON laporan(status);
CREATE INDEX idx_tangki_tanggal ON tangki(tanggal_pengiriman);
CREATE INDEX idx_tangki_status ON tangki(status);
CREATE INDEX idx_notifikasi_read ON notifikasi(is_read);

-- ============================================
-- END OF SQL DUMP
-- ============================================

-- ============================================
-- INFORMASI LOGIN DEFAULT
-- ============================================
-- Username: admin
-- Password: password123
-- Role: Admin
--
-- Username: budi_semanu
-- Password: password123
-- Role: Warga
--
-- Username: kades_tepus
-- Password: password123
-- Role: Pemdes
--
-- Username: petugas_tangki1
-- Password: password123
-- Role: Petugas Tangki
-- ============================================
