-- ================================================================
-- WebGIS Sebaran Penduduk Miskin
-- Database: poverty_map
-- ================================================================

CREATE DATABASE IF NOT EXISTS poverty_map CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE poverty_map;

-- ----------------------------------------------------------------
-- Tabel: users
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id          INT           NOT NULL AUTO_INCREMENT,
  nama        VARCHAR(100)  NOT NULL,
  email       VARCHAR(100)  NOT NULL UNIQUE,
  password    VARCHAR(255)  NOT NULL,
  role        ENUM('admin','operator') NOT NULL DEFAULT 'operator',
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Tabel: rumah_ibadah
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS rumah_ibadah (
  id          INT           NOT NULL AUTO_INCREMENT,
  nama        VARCHAR(150)  NOT NULL,
  jenis       ENUM('Masjid','Musholla','Gereja','Katolik','Pura','Vihara','Klenteng','Lainnya') NOT NULL DEFAULT 'Masjid',
  kontak      VARCHAR(100)  DEFAULT NULL,
  alamat      TEXT          DEFAULT NULL,
  kelurahan   VARCHAR(100)  DEFAULT NULL,
  kecamatan   VARCHAR(100)  DEFAULT NULL,
  lat         DECIMAL(10,7) NOT NULL,
  lng         DECIMAL(10,7) NOT NULL,
  radius      INT           NOT NULL DEFAULT 300,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Tabel: penduduk_miskin
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS penduduk_miskin (
  id                INT           NOT NULL AUTO_INCREMENT,
  kk_nama           VARCHAR(150)  NOT NULL,
  nik               VARCHAR(20)   DEFAULT NULL,
  jumlah_anggota    INT           NOT NULL DEFAULT 1,
  alamat            TEXT          DEFAULT NULL,
  kelurahan         VARCHAR(100)  DEFAULT NULL,
  kecamatan         VARCHAR(100)  DEFAULT NULL,
  lat               DECIMAL(10,7) NOT NULL,
  lng               DECIMAL(10,7) NOT NULL,
  rumah_ibadah_id   INT           DEFAULT NULL,
  jarak_ke_ibadah   DECIMAL(10,2) DEFAULT NULL,
  status_bantuan    ENUM('belum','sudah','menunggu','darurat') NOT NULL DEFAULT 'belum',
  jenis_bantuan     VARCHAR(200)  DEFAULT NULL,
  tanggal_bantuan   DATE          DEFAULT NULL,
  nominal_bantuan   DECIMAL(15,2) DEFAULT NULL COMMENT 'Nominal bantuan dalam Rupiah',
  bukti_file        VARCHAR(255)  DEFAULT NULL,
  created_at        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (rumah_ibadah_id) REFERENCES rumah_ibadah(id) ON DELETE SET NULL ON UPDATE CASCADE,
  INDEX idx_status (status_bantuan),
  INDEX idx_ibadah (rumah_ibadah_id),
  INDEX idx_kecamatan (kecamatan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Tabel: anggota_keluarga
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS anggota_keluarga (
  id            INT           NOT NULL AUTO_INCREMENT,
  penduduk_id   INT           NOT NULL,
  nama          VARCHAR(150)  NOT NULL,
  hubungan      VARCHAR(50)   DEFAULT NULL,
  umur          INT           DEFAULT NULL,
  pekerjaan     VARCHAR(100)  DEFAULT NULL,
  keterangan    TEXT          DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (penduduk_id) REFERENCES penduduk_miskin(id) ON DELETE CASCADE ON UPDATE CASCADE,
  INDEX idx_penduduk (penduduk_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Tabel: bantuan (riwayat bantuan)
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bantuan (
  id              INT           NOT NULL AUTO_INCREMENT,
  penduduk_id     INT           NOT NULL,
  jenis_bantuan   VARCHAR(200)  NOT NULL,
  tanggal_bantuan DATE          NOT NULL,
  keterangan      TEXT          DEFAULT NULL,
  bukti_file      VARCHAR(255)  DEFAULT NULL,
  created_by      INT           DEFAULT NULL,
  created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (penduduk_id) REFERENCES penduduk_miskin(id) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (created_by)  REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------
-- Default admin user: admin@povertymap.id / admin123
-- ----------------------------------------------------------------
INSERT IGNORE INTO users (nama, email, password, role) VALUES
('Administrator', 'admin@povertymap.id', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin'),
('Operator', 'operator@povertymap.id', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'operator');

-- ----------------------------------------------------------------
-- Sample Data Rumah Ibadah (Pontianak)
-- ----------------------------------------------------------------
INSERT IGNORE INTO rumah_ibadah (nama, jenis, kontak, alamat, kelurahan, kecamatan, lat, lng, radius) VALUES
('Masjid Raya Mujahidin', 'Masjid', '0561-123456', 'Jl. Jend. Ahmad Yani, Pontianak', 'Akcaya', 'Pontianak Selatan', -0.0412, 109.3325, 500),
('Masjid Sultan Syarif Abdurrahman', 'Masjid', '0561-123457', 'Jl. Tanjungpura, Pontianak', 'Dalam Bugis', 'Pontianak Kota', -0.0263, 109.3425, 400),
('Gereja Katedral Santo Yosef', 'Gereja', '0561-123458', 'Jl. Pattimura, Pontianak', 'Mariana', 'Pontianak Kota', -0.0290, 109.3380, 350),
('Vihara Bodhisattva Karaniya', 'Vihara', '0561-123459', 'Jl. Diponegoro, Pontianak', 'Sungai Bangkong', 'Pontianak Kota', -0.0320, 109.3350, 300);

-- ----------------------------------------------------------------
-- Sample Data Penduduk Miskin
-- ----------------------------------------------------------------
INSERT IGNORE INTO penduduk_miskin (kk_nama, nik, jumlah_anggota, alamat, kelurahan, kecamatan, lat, lng, rumah_ibadah_id, jarak_ke_ibadah, status_bantuan, jenis_bantuan, tanggal_bantuan) VALUES
('Ahmad Sulaiman', '6171012345678901', 4, 'Jl. Veteran No. 12, Pontianak', 'Akcaya', 'Pontianak Selatan', -0.0430, 109.3340, 1, 210.5, 'sudah', 'Sembako + Uang Tunai', '2024-01-15'),
('Budi Santoso', '6171023456789012', 3, 'Jl. Imam Bonjol No. 5, Pontianak', 'Dalam Bugis', 'Pontianak Kota', -0.0270, 109.3440, 2, 180.2, 'belum', NULL, NULL),
('Siti Rahayu', '6171034567890123', 5, 'Jl. Gajahmada No. 8, Pontianak', 'Mariana', 'Pontianak Kota', -0.0300, 109.3400, 3, 320.8, 'menunggu', NULL, NULL),
('Wati Susanti', '6171045678901234', 2, 'Jl. Diponegoro No. 20, Pontianak', 'Sungai Bangkong', 'Pontianak Kota', -0.0350, 109.3370, 4, 180.0, 'belum', NULL, NULL);

-- Sample anggota keluarga
INSERT IGNORE INTO anggota_keluarga (penduduk_id, nama, hubungan, umur, pekerjaan, keterangan) VALUES
(1, 'Fatimah', 'Istri', 35, 'Ibu Rumah Tangga', NULL),
(1, 'Ali', 'Anak', 12, 'Pelajar', 'Kelas 6 SD'),
(1, 'Sari', 'Anak', 8, 'Pelajar', 'Kelas 2 SD'),
(2, 'Dewi Santoso', 'Istri', 30, 'Pedagang Kecil', NULL),
(2, 'Rizki', 'Anak', 10, 'Pelajar', NULL);
