-- MySQL dump 10.13  Distrib 9.5.0, for macos15 (x86_64)
--
-- Host: localhost    Database: webgis_donations
-- ------------------------------------------------------
-- Server version	9.5.0

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `webgis_donations`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `webgis_donations` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `webgis_donations`;

--
-- Table structure for table `distribution_history`
--

DROP TABLE IF EXISTS `distribution_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `distribution_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `house_id` int NOT NULL,
  `place_id` int DEFAULT NULL,
  `distributed_by` int DEFAULT NULL,
  `aid_category` varchar(100) NOT NULL,
  `amount_value` decimal(15,2) NOT NULL,
  `notes` text,
  `verification_status` enum('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  `verified_by` int DEFAULT NULL,
  `verified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `house_id` (`house_id`),
  KEY `place_id` (`place_id`),
  KEY `distributed_by` (`distributed_by`),
  KEY `verified_by` (`verified_by`),
  CONSTRAINT `distribution_history_ibfk_1` FOREIGN KEY (`house_id`) REFERENCES `households` (`id`) ON DELETE CASCADE,
  CONSTRAINT `distribution_history_ibfk_2` FOREIGN KEY (`place_id`) REFERENCES `religious_places` (`id`) ON DELETE SET NULL,
  CONSTRAINT `distribution_history_ibfk_3` FOREIGN KEY (`distributed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `distribution_history_ibfk_4` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `distribution_history`
--

LOCK TABLES `distribution_history` WRITE;
/*!40000 ALTER TABLE `distribution_history` DISABLE KEYS */;
INSERT INTO `distribution_history` VALUES (1,9,4,1,'sembako',1.00,'Diberikan sembako 10kg berupa bahan pangan','verified',1,'2026-06-11 23:59:05'),(2,11,2,1,'sembako',1.00,'Diberikan sembako berupa PC Gaming','pending',NULL,NULL);
/*!40000 ALTER TABLE `distribution_history` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `households`
--

DROP TABLE IF EXISTS `households`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `households` (
  `id` int NOT NULL AUTO_INCREMENT,
  `kk_number` varchar(16) NOT NULL,
  `head_nik` varchar(16) NOT NULL,
  `head_name` varchar(255) NOT NULL,
  `resident_count` int NOT NULL,
  `lat` decimal(10,8) NOT NULL,
  `lng` decimal(11,8) NOT NULL,
  `linked_place_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `linked_place_id` (`linked_place_id`),
  CONSTRAINT `households_ibfk_1` FOREIGN KEY (`linked_place_id`) REFERENCES `religious_places` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `households`
--

LOCK TABLES `households` WRITE;
/*!40000 ALTER TABLE `households` DISABLE KEYS */;
INSERT INTO `households` VALUES (6,'6171050505670005','6171052511790005','Hendra Kurniawan',6,-0.02563119,109.33623791,3),(7,'6171020202340002','6171021508820002','Saputra Wijaya',5,-0.02630174,109.33527231,3),(8,'6171010101230001','6171011203840001','Budi Santoso',4,-0.02426863,109.33210194,4),(9,'6171030303450003','6171030912850003','Syahputra Ramadhan',6,-0.02335668,109.33048725,4),(10,'6171040404560004','6171042104900004','Dian Lestari',2,-0.02706885,109.33138311,NULL),(11,'6171098765432','6171234567890','Darin',6,-0.04112363,109.34434891,2),(12,'617193843984394','617123829734828','Bagas',4,-0.04228234,109.33483243,1),(13,'61712387293847','61713423423478','Dimas',3,-0.04366636,109.33228970,NULL);
/*!40000 ALTER TABLE `households` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `poverty_assessments`
--

DROP TABLE IF EXISTS `poverty_assessments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `poverty_assessments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `household_id` int NOT NULL,
  `surveyor_id` int DEFAULT NULL,
  `electricity_va` varchar(50) NOT NULL,
  `floor_material` varchar(100) NOT NULL,
  `wall_material` varchar(100) NOT NULL,
  `drinking_water_source` varchar(100) NOT NULL,
  `photo_evidence_url` varchar(255) DEFAULT NULL,
  `verification_status` enum('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  `verified_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `household_id` (`household_id`),
  KEY `surveyor_id` (`surveyor_id`),
  KEY `verified_by` (`verified_by`),
  CONSTRAINT `poverty_assessments_ibfk_1` FOREIGN KEY (`household_id`) REFERENCES `households` (`id`) ON DELETE CASCADE,
  CONSTRAINT `poverty_assessments_ibfk_2` FOREIGN KEY (`surveyor_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `poverty_assessments_ibfk_3` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `poverty_assessments`
--

LOCK TABLES `poverty_assessments` WRITE;
/*!40000 ALTER TABLE `poverty_assessments` DISABLE KEYS */;
INSERT INTO `poverty_assessments` VALUES (6,6,3,'450','cement','wood','well',NULL,'verified',1),(7,7,3,'450','cement','brick','pdam',NULL,'verified',1),(8,8,4,'450','wood','wood','rainwater',NULL,'verified',1),(9,9,4,'900','ceramic','brick','pdam',NULL,'verified',1),(10,10,4,'450','cement','brick','bottled',NULL,'verified',1),(11,11,6,'1300','ceramic','brick','pdam',NULL,'verified',1),(12,12,5,'900','dirt','bamboo','river',NULL,'pending',NULL),(13,13,5,'450','cement','brick','rainwater',NULL,'pending',NULL);
/*!40000 ALTER TABLE `poverty_assessments` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `religious_places`
--

DROP TABLE IF EXISTS `religious_places`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `religious_places` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `lat` decimal(10,8) NOT NULL,
  `lng` decimal(11,8) NOT NULL,
  `radius` int NOT NULL DEFAULT '500',
  `place_type` enum('mosque','church_catholic','church_protestant','pura','vihara','kelenteng') NOT NULL DEFAULT 'mosque',
  `added_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `added_by` (`added_by`),
  CONSTRAINT `religious_places_ibfk_1` FOREIGN KEY (`added_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `religious_places`
--

LOCK TABLES `religious_places` WRITE;
/*!40000 ALTER TABLE `religious_places` DISABLE KEYS */;
INSERT INTO `religious_places` VALUES (1,'Mujahideen Mosque',-0.04149913,109.33635592,500,'mosque',1),(2,'Majid Amanatul Hasanah',-0.04104852,109.34329748,500,'mosque',2),(3,'GPIB Siloam',-0.02507329,109.33727860,500,'church_protestant',3),(4,'Masjid Al-Manar',-0.02383947,109.33471441,500,'mosque',4);
/*!40000 ALTER TABLE `religious_places` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('admin','surveyor') NOT NULL DEFAULT 'surveyor',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'admin','admin@webgis.com','$2b$10$wOFuhFBZzEWdnNIt7hxNBeDW.sivkV5Y0jCHQz0k0Ew7eC7T3xJQe','admin','2026-06-11 16:28:22'),(2,'Surveyor1','surveyor1@webgis.com','$2b$10$C6TlOgTG6FQhfq6USToWHen3gRouR3z0tnkaVbWCQlcsd7m/aluM2','surveyor','2026-06-11 16:28:22'),(3,'GPIB Siloam','gpibsiloam@webgis.com','$2b$10$nEUOivMsFlPwvBpbaGCIMO.ciqqllOTRZisPaLJXot3OsXsOsOiUW','surveyor','2026-06-11 16:43:02'),(4,'Masjid Al-Manar','masjidalmanar@webgis.com','$2b$10$QMaZwRx0PsoxXFyOEVKghey.yUUK76ER2hW7gmlD4lGq3GM00uP.6','surveyor','2026-06-11 16:45:16'),(5,'Masjid Mujahideen','mujahideen@webgis.com','$2b$10$KYGptHaDBQLB93/eCNf/t.n.nEosWx4KELb8qiEG2rMG3THPUzKdC','surveyor','2026-06-11 16:53:55'),(6,'Masjid Amanatul Hasanah','amanatulhasanah@webgis.com','$2b$10$BqozVZ6M8NrFBnUuWt/BvekEmd8T1mmv0cBb9NZdZ2z.eTlhxBHZu','surveyor','2026-06-11 16:54:29');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-06-12  0:03:34