

-- TABLE: desa
CREATE TABLE `desa` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nama_desa` varchar(100) NOT NULL,
  `kecamatan` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

-- TABLE: dusun
CREATE TABLE `dusun` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `desa_id` bigint unsigned NOT NULL,
  `nama_dusun` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`desa_id`) REFERENCES desa(`id`) ON DELETE CASCADE
);

-- TABLE: laporan
CREATE TABLE `laporan` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint unsigned NOT NULL,
  `dusun_id` bigint unsigned NOT NULL,
  `level_risiko` enum('hijau', 'kuning', 'merah') DEFAULT 'hijau',
  `keterangan` text,
  `foto` varchar(255) DEFAULT NULL,
  `status` enum('diajukan', 'diproses', 'selesai') DEFAULT 'diajukan',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES users(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`dusun_id`) REFERENCES dusun(`id`) ON DELETE CASCADE
);

-- TABLE: bmkg_predictions
CREATE TABLE `bmkg_predictions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `dusun_id` bigint unsigned NOT NULL,
  `tanggal` date NOT NULL,
  `risk_score` float DEFAULT 0,
  `category` enum('hijau', 'kuning', 'merah') DEFAULT 'hijau',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`dusun_id`) REFERENCES dusun(`id`) ON DELETE CASCADE
);

-- SEED DATA - CONTOH GUNUNGKIDUL
INSERT INTO `desa` (`nama_desa`, `kecamatan`) VALUES
('Semanu', 'Semanu'),
('Girisubo', 'Girisubo'),
('Tepus', 'Tepus'),
('Rongkop', 'Rongkop');

INSERT INTO `dusun` (`desa_id`, `nama_dusun`) VALUES
(1, 'Dadapayu'),
(1, 'Pacarejo'),
(2, 'Nglindur'),
(2, 'Balong'),
(3, 'Tepus Barat'),
(3, 'Tepus Timur'),
(4, 'Sambirejo'),
(4, 'Pringombo');
-- Password: admin123
