-- Database schema for WebGIS Pemetaan Kemiskinan Pontianak

CREATE DATABASE IF NOT EXISTS webgis_poverty;
USE webgis_poverty;

-- 1. Tabel rumah_ibadah
DROP TABLE IF EXISTS `log_bantuan`;
DROP TABLE IF EXISTS `rumah_ibadah`;
CREATE TABLE `rumah_ibadah` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) NOT NULL,
  `jenis` varchar(100) NOT NULL,
  `alamat` text,
  `radius` double NOT NULL DEFAULT 0,
  `lat` double NOT NULL,
  `lng` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Tabel penduduk_miskin
DROP TABLE IF EXISTS `penduduk_miskin`;
CREATE TABLE `penduduk_miskin` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) NOT NULL,
  `kategori_bantuan` varchar(255) NOT NULL,
  `jumlah_jiwa` int(11) NOT NULL DEFAULT 1,
  `lat` double NOT NULL,
  `lng` double NOT NULL,
  `foto_rumah` varchar(255) DEFAULT NULL,
  `foto_kk` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Tabel log_bantuan
CREATE TABLE `log_bantuan` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `miskin_id` int(11) NOT NULL,
  `ibadah_id` int(11) NOT NULL,
  `tipe_bantuan` varchar(255) NOT NULL,
  `tanggal` date NOT NULL,
  `keterangan` text,
  PRIMARY KEY (`id`),
  KEY `fk_log_miskin` (`miskin_id`),
  KEY `fk_log_ibadah` (`ibadah_id`),
  CONSTRAINT `fk_log_ibadah` FOREIGN KEY (`ibadah_id`) REFERENCES `rumah_ibadah` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_log_miskin` FOREIGN KEY (`miskin_id`) REFERENCES `penduduk_miskin` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Data Contoh (Sample Data) untuk Pontianak

-- Data Contoh Rumah Ibadah
INSERT INTO `rumah_ibadah` (`nama`, `jenis`, `alamat`, `radius`, `lat`, `lng`) VALUES
('Masjid Raya Mujahidin', 'Masjid', 'Jl. Jenderal Ahmad Yani, Akcaya, Kec. Pontianak Sel.', 500, -0.0435, 109.3440),
('Gereja Katedral Santo Yosef', 'Gereja', 'Jl. Patria Tama, Darat Sekip, Kec. Pontianak Kota', 500, -0.0278, 109.3370),
('Vihara Bodhisatva Karaniya Metta', 'Vihara', 'Jl. Kapten Marsan, Darat Sekip, Kec. Pontianak Kota', 500, -0.0232, 109.3432);

-- Data Contoh Penduduk Miskin
INSERT INTO `penduduk_miskin` (`nama`, `kategori_bantuan`, `jumlah_jiwa`, `lat`, `lng`) VALUES
('Budi Santoso', 'Makan', 4, -0.0420, 109.3415),
('Siti Aminah', 'Pemberdayaan', 3, -0.0285, 109.3395),
('Ahmad Subarjo', 'Makan', 5, -0.0245, 109.3448);

-- Data Contoh Log Bantuan
INSERT INTO `log_bantuan` (`miskin_id`, `ibadah_id`, `tipe_bantuan`, `tanggal`, `keterangan`) VALUES
(1, 1, 'Sembako', '2026-05-15', 'Pembagian paket sembako bulanan'),
(2, 2, 'Pendidikan', '2026-05-20', 'Bantuan biaya sekolah anak'),
(3, 1, 'Uang Tunai', '2026-06-01', 'Santunan tunai darurat');



-- 7. Tabel users
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL UNIQUE,
  `password` varchar(255) NOT NULL,
  `role` enum('admin', 'pengelola') NOT NULL,
  `ibadah_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_users_ibadah` FOREIGN KEY (`ibadah_id`) REFERENCES `rumah_ibadah` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data Contoh Pengguna (Default Users)
-- Password admin: password
-- Password pengelola: password (terhubung ke Masjid Raya Mujahidin, ibadah_id = 1)
INSERT INTO `users` (`username`, `password`, `role`, `ibadah_id`) VALUES
('admin', '$2y$10$qqKxivn4646czFu/ffwa/uZxJrkj3T9xmsrRbyxJbHx6jllpOOfDO', 'admin', NULL),
('pengelola_mujahidin', '$2y$10$qqKxivn4646czFu/ffwa/uZxJrkj3T9xmsrRbyxJbHx6jllpOOfDO', 'pengelola', 1);

CREATE TABLE IF NOT EXISTS spbu (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    nama      VARCHAR(255) NOT NULL,
    no_wa     VARCHAR(20)  NOT NULL DEFAULT '',
    is_24_jam TINYINT(1)   NOT NULL DEFAULT 0,
    lat       DOUBLE       NOT NULL,
    lng       DOUBLE       NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS parsil (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nama   VARCHAR(255) NOT NULL DEFAULT '',
    status ENUM('SHM', 'HGB', 'HGU', 'HP') NOT NULL DEFAULT 'SHM',
    luas   DOUBLE NOT NULL DEFAULT 0,
    geom   POLYGON NOT NULL,
    SPATIAL INDEX (geom)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS jalan (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    nama    VARCHAR(255) NOT NULL,
    status  ENUM('Nasional', 'Provinsi', 'Kabupaten') NOT NULL DEFAULT 'Kabupaten',
    panjang DOUBLE NOT NULL DEFAULT 0,
    geom    LINESTRING NOT NULL,
    SPATIAL INDEX (geom)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;