UAS_WEBGIS_AlifaAisyahRahmani_D1041231042/Web GIS Rumah Ibadah/database.sql

-- ============================================================
-- DATABASE: webgis_rumah_ibadah
-- WebGIS Pontianak — Pengentasan Kemiskinan Berbasis Rumah Ibadah
-- ============================================================
-- Hapus tabel lama (urutan sesuai dependensi FK)
DROP TABLE IF EXISTS histori_bantuan;

DROP TABLE IF EXISTS laporan_masyarakat;

DROP TABLE IF EXISTS keluarga_miskin;

DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS rumah_ibadah;

DROP TABLE IF EXISTS rate_limit;

-- ============================================================
-- TABEL: rumah_ibadah
-- Menyimpan data titik lokasi rumah ibadah beserta radius cakupan
-- ============================================================
CREATE TABLE rumah_ibadah (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(200) NOT NULL COMMENT 'Nama rumah ibadah',
    jenis ENUM (
        'Masjid',
        'Gereja',
        'Pura',
        'Vihara',
        'Klenteng',
        'Lainnya'
    ) NOT NULL COMMENT 'Jenis rumah ibadah',
    alamat TEXT NOT NULL COMMENT 'Alamat lengkap',
    lat DECIMAL(10, 7) NOT NULL COMMENT 'Latitude',
    lng DECIMAL(10, 7) NOT NULL COMMENT 'Longitude',
    radius_km FLOAT NOT NULL DEFAULT 2.0 COMMENT 'Radius cakupan (km)',
    kode_pengurus VARCHAR(20) NULL UNIQUE COMMENT 'Kode unik login pengurus, format RI-XXX-9999',
    password_pengurus VARCHAR(255) NULL COMMENT 'Password pengurus (hashed)',
    kecamatan ENUM (
        'Pontianak Utara',
        'Pontianak Selatan',
        'Pontianak Timur',
        'Pontianak Barat',
        'Pontianak Tenggara',
        'Pontianak Kota'
    ) NOT NULL COMMENT 'Kecamatan lokasi',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- TABEL: users
-- Manajemen pengguna dengan 3 role: admin, pengurus, walikota
-- ============================================================
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(150) NOT NULL COMMENT 'Nama lengkap pengguna',
    username VARCHAR(100) NOT NULL UNIQUE COMMENT 'Username login',
    password VARCHAR(255) NOT NULL COMMENT 'Password ter-hash (bcrypt)',
    role ENUM ('admin', 'pengurus', 'walikota') NOT NULL DEFAULT 'pengurus' COMMENT 'Role pengguna',
    rumah_ibadah_id INT DEFAULT NULL COMMENT 'FK ke rumah_ibadah (hanya untuk pengurus)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (rumah_ibadah_id) REFERENCES rumah_ibadah (id) ON DELETE SET NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- TABEL: keluarga_miskin
-- Data keluarga miskin dengan auto-assign ke rumah ibadah terdekat
-- ============================================================
CREATE TABLE keluarga_miskin (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nik VARCHAR(20) NOT NULL UNIQUE COMMENT 'NIK Kepala Keluarga',
    nama_kepala_keluarga VARCHAR(200) NOT NULL COMMENT 'Nama Kepala Keluarga',
    tanggal_lahir DATE NULL COMMENT 'Tanggal lahir KK',
    pendidikan ENUM (
        'Tidak Sekolah',
        'SD',
        'SMP',
        'SMA',
        'D3',
        'S1',
        'S2/S3'
    ) NOT NULL DEFAULT 'Tidak Sekolah',
    jumlah_anggota INT NOT NULL DEFAULT 1 COMMENT 'Jumlah anggota keluarga',
    alamat TEXT NOT NULL COMMENT 'Alamat lengkap',
    kecamatan ENUM (
        'Pontianak Utara',
        'Pontianak Selatan',
        'Pontianak Timur',
        'Pontianak Barat',
        'Pontianak Tenggara',
        'Pontianak Kota'
    ) NOT NULL,
    lat DECIMAL(10, 7) NOT NULL,
    lng DECIMAL(10, 7) NOT NULL,
    tingkat_kemiskinan ENUM ('Sangat Miskin', 'Miskin', 'Hampir Miskin') NOT NULL DEFAULT 'Miskin',
    status_dtks ENUM (
        'Terdaftar DTKS',
        'Belum DTKS',
        'Sedang Diusulkan'
    ) NOT NULL DEFAULT 'Belum DTKS',
    riwayat_penyakit JSON NULL COMMENT 'Array penyakit: Stunting, TBC, Hipertensi, dll',
    rumah_ibadah_id INT DEFAULT NULL COMMENT 'Auto-assign ke RI terdekat',
    jarak_ke_ibadah FLOAT DEFAULT NULL COMMENT 'Jarak ke RI terdekat (km)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (rumah_ibadah_id) REFERENCES rumah_ibadah (id) ON DELETE SET NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- TABEL: laporan_masyarakat
-- Laporan warga tentang kemiskinan (bisa anonim, tanpa login)
-- ============================================================
CREATE TABLE laporan_masyarakat (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nama_terlapor VARCHAR(150) NULL COMMENT 'Nama warga yang dilaporkan (boleh null)',
    nik_terlapor VARCHAR(16) NULL COMMENT 'NIK warga yang dilaporkan (boleh null)',
    nama_pelapor VARCHAR(150) NULL COMMENT 'Boleh anonim (null)',
    no_hp VARCHAR(20) NULL,
    alamat_terlapor TEXT NOT NULL COMMENT 'Alamat yang dilaporkan',
    lat DECIMAL(10, 7) NOT NULL,
    lng DECIMAL(10, 7) NOT NULL,
    deskripsi TEXT NOT NULL COMMENT 'Deskripsi laporan',
    foto VARCHAR(255) NULL COMMENT 'Path file foto',
    status ENUM ('Baru', 'Diverifikasi', 'Ditolak') NOT NULL DEFAULT 'Baru',
    alasan_tolak TEXT NULL COMMENT 'Alasan jika ditolak',
    ditangani_oleh INT NULL COMMENT 'FK ke users.id',
    converted_to_kk_id INT NULL COMMENT 'FK ke keluarga_miskin.id jika dikonversi',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ditangani_oleh) REFERENCES users (id) ON DELETE SET NULL,
    FOREIGN KEY (converted_to_kk_id) REFERENCES keluarga_miskin (id) ON DELETE SET NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- TABEL: histori_bantuan
-- Riwayat bantuan yang diterima keluarga miskin
-- ============================================================
CREATE TABLE histori_bantuan (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    kk_id INT NOT NULL COMMENT 'FK ke keluarga_miskin',
    jenis_bantuan ENUM (
        'PKH',
        'BPNT/Sembako',
        'Beras Bansos',
        'KIS-PBI',
        'Bansos Tunai Pemkot',
        'Rutilahu',
        'PMT Stunting',
        'CSR Swasta',
        'Lainnya'
    ) NOT NULL,
    nominal BIGINT NOT NULL DEFAULT 0 COMMENT 'Nominal dalam rupiah (0 jika non-uang)',
    keterangan TEXT NULL COMMENT 'Catatan bebas: 10 kg beras, 3 karung, dll',
    tanggal_penyaluran DATE NOT NULL,
    sumber_dana ENUM ('APBN', 'APBD', 'CSR', 'Lainnya') NOT NULL DEFAULT 'APBD',
    penanggung_jawab INT NOT NULL COMMENT 'FK ke users.id',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (kk_id) REFERENCES keluarga_miskin (id) ON DELETE CASCADE,
    FOREIGN KEY (penanggung_jawab) REFERENCES users (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- TABEL: rate_limit
-- Pembatasan submit laporan masyarakat per IP (maks 5/jam)
-- ============================================================
CREATE TABLE rate_limit (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_time (ip_address, created_at)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- DATA SEED: Rumah Ibadah (2 data)
-- ============================================================
INSERT INTO
    rumah_ibadah (
        nama,
        jenis,
        alamat,
        lat,
        lng,
        radius_km,
        kode_pengurus,
        password_pengurus,
        kecamatan
    )
VALUES
    (
        'Masjid Raya Mujahidin',
        'Masjid',
        'Jl. Ahmad Yani No. 1, Pontianak Tenggara',
        -0.0263330,
        109.3425200,
        2.5,
        'RI-MSJ-4821',
        -- password: pengurus123
        '$2y$10$EQKGMwtGqPsn3kxOGkUf3eh3cnXOCEYqVNfCKDVk.Q7WYTCKvi5iG',
        'Pontianak Tenggara'
    ),
    (
        'Gereja Katedral Santo Yosef',
        'Gereja',
        'Jl. Diponegoro No. 120, Pontianak Kota',
        -0.0380000,
        109.3422000,
        2.0,
        'RI-GRJ-7153',
        -- password: pengurus123
        '$2y$10$EQKGMwtGqPsn3kxOGkUf3eh3cnXOCEYqVNfCKDVk.Q7WYTCKvi5iG',
        'Pontianak Kota'
    );

-- ============================================================
-- DATA SEED: Users (1 admin, 1 walikota)
-- Password semua: admin123
-- ============================================================
INSERT INTO
    users (nama, username, password, role, rumah_ibadah_id)
VALUES
    (
        'Administrator Sistem',
        'admin',
        '$2y$10$AIO3pPEflVaouN/G.rvgWeZmSjr0Nnjsd2PTna4lSHEoDTWr9KQpi',
        'admin',
        NULL
    ),
    (
        'Walikota Pontianak',
        'walikota',
        '$2y$10$AIO3pPEflVaouN/G.rvgWeZmSjr0Nnjsd2PTna4lSHEoDTWr9KQpi',
        'walikota',
        NULL
    );

-- ============================================================
-- DATA SEED: Keluarga Miskin (5 data)
-- Auto-assign rumah_ibadah_id dilakukan oleh aplikasi saat disimpan
-- ============================================================
INSERT INTO
    keluarga_miskin (
        nik,
        nama_kepala_keluarga,
        tanggal_lahir,
        pendidikan,
        jumlah_anggota,
        alamat,
        kecamatan,
        lat,
        lng,
        tingkat_kemiskinan,
        status_dtks,
        riwayat_penyakit,
        rumah_ibadah_id,
        jarak_ke_ibadah
    )
VALUES
    (
        '6171010101010001',
        'Budi Santoso',
        '1978-03-15',
        'SD',
        4,
        'Gg. Mawar No. 3, Benua Melayu Darat',
        'Pontianak Tenggara',
        -0.0283000,
        109.3440000,
        'Sangat Miskin',
        'Terdaftar DTKS',
        '["Stunting","Gizi Buruk"]',
        1,
        0.2500
    ),
    (
        '6171010101010002',
        'Siti Rahayu',
        '1985-07-22',
        'SMP',
        6,
        'Jl. Merdeka Gg. 5 No. 7, Mariana',
        'Pontianak Kota',
        -0.0370000,
        109.3430000,
        'Miskin',
        'Terdaftar DTKS',
        '["TBC","Hipertensi"]',
        2,
        0.1200
    ),
    (
        '6171010101010003',
        'Wayan Dharma',
        '1972-11-08',
        'Tidak Sekolah',
        3,
        'Gg. Bali No. 2, Bansir Laut',
        'Pontianak Tenggara',
        -0.0290000,
        109.3410000,
        'Sangat Miskin',
        'Belum DTKS',
        '["Diabetes","Hipertensi","ODGJ"]',
        1,
        0.3400
    ),
    (
        '6171010101010004',
        'Ahmad Fauzan',
        '1990-01-30',
        'SMA',
        5,
        'Jl. Tanjungpura Gg. 12, Parit Tokaya',
        'Pontianak Selatan',
        -0.0310000,
        109.3350000,
        'Miskin',
        'Sedang Diusulkan',
        '["Stunting"]',
        NULL,
        NULL
    ),
    (
        '6171010101010005',
        'Ruslan Effendi',
        '1968-09-05',
        'SD',
        7,
        'Jl. Sutan Syahrir Gg. Melati, Batu Layang',
        'Pontianak Utara',
        -0.0200000,
        109.3460000,
        'Hampir Miskin',
        'Terdaftar DTKS',
        '["Jantung","DBD"]',
        1,
        0.7800
    );

-- ============================================================
-- DATA SEED: Laporan Masyarakat (1 contoh)
-- ============================================================
INSERT INTO
    laporan_masyarakat (
        nama_pelapor,
        no_hp,
        alamat_terlapor,
        lat,
        lng,
        deskripsi,
        status
    )
VALUES
    (
        'Pak RT 03',
        '081234567890',
        'Gg. Flamboyan No. 5, Pontianak Tenggara',
        -0.0275000,
        109.3435000,
        'Terdapat keluarga dengan 5 anak yang sangat membutuhkan bantuan. Rumah tidak layak huni dan anak-anak putus sekolah.',
        'Baru'
    );

-- ============================================================
-- DATA SEED: Histori Bantuan (2 contoh)
-- ============================================================
INSERT INTO
    histori_bantuan (
        kk_id,
        jenis_bantuan,
        nominal,
        keterangan,
        tanggal_penyaluran,
        sumber_dana,
        penanggung_jawab
    )
VALUES
    (
        1,
        'PKH',
        3000000,
        'Bantuan tahap I tahun 2026',
        '2026-01-15',
        'APBN',
        1
    ),
    (
        2,
        'BPNT/Sembako',
        0,
        '10 kg beras + minyak goreng',
        '2026-02-20',
        'APBN',
        1
    );