265 lines
13 KiB
SQL
265 lines
13 KiB
SQL
-- ============================================================
|
|
-- GeoSosial Database Schema
|
|
-- Sistem Informasi Sosial Kota Pontianak
|
|
-- ============================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS geososial CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE geososial;
|
|
|
|
-- ============================================================
|
|
-- TABEL: users (autentikasi)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(50) NOT NULL UNIQUE,
|
|
password VARCHAR(255) NOT NULL,
|
|
role ENUM('admin','petugas','walikota','viewer') NOT NULL DEFAULT 'viewer',
|
|
jabatan VARCHAR(100),
|
|
can_edit TINYINT(1) NOT NULL DEFAULT 0,
|
|
is_admin TINYINT(1) NOT NULL DEFAULT 0,
|
|
is_wali TINYINT(1) NOT NULL DEFAULT 0,
|
|
aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ============================================================
|
|
-- TABEL: kelurahan (master data)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS kelurahan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(100) NOT NULL,
|
|
kecamatan VARCHAR(100) NOT NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ============================================================
|
|
-- TABEL: rumah_ibadah
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS rumah_ibadah (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
tipe ENUM('masjid','gereja','pura','vihara','klenteng') NOT NULL,
|
|
ikon VARCHAR(10) DEFAULT '🕌',
|
|
lat DECIMAL(10,7) NOT NULL,
|
|
lng DECIMAL(10,7) NOT NULL,
|
|
kapasitas INT DEFAULT 0,
|
|
kelurahan_id INT,
|
|
alamat TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (kelurahan_id) REFERENCES kelurahan(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ============================================================
|
|
-- TABEL: warga (data kepala keluarga)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS warga (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
nik VARCHAR(16),
|
|
kelurahan_id INT,
|
|
lat DECIMAL(10,7) NOT NULL,
|
|
lng DECIMAL(10,7) NOT NULL,
|
|
kategori ENUM('sangat_miskin','miskin','hampir_miskin') NOT NULL,
|
|
jumlah_anggota INT DEFAULT 1,
|
|
penghasilan BIGINT DEFAULT 0,
|
|
tgl_lahir DATE,
|
|
pendidikan ENUM('tidak_sekolah','sd','smp','sma','d3','s1','s2','') DEFAULT '',
|
|
keterangan TEXT,
|
|
status ENUM('aktif','nonaktif') DEFAULT 'aktif',
|
|
pelapor VARCHAR(150),
|
|
no_laporan VARCHAR(20),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (kelurahan_id) REFERENCES kelurahan(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ============================================================
|
|
-- TABEL: anggota_keluarga
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS anggota_keluarga (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
warga_id INT NOT NULL,
|
|
nama VARCHAR(150) NOT NULL,
|
|
usia INT DEFAULT 0,
|
|
hubungan VARCHAR(50) DEFAULT 'Anak',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (warga_id) REFERENCES warga(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ============================================================
|
|
-- TABEL: riwayat_penyakit (per anggota keluarga)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS riwayat_penyakit (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
anggota_id INT NOT NULL,
|
|
penyakit ENUM('hipertensi','diabetes','tbc','asma','stroke','jantung','odgj','stunting','kanker') NOT NULL,
|
|
FOREIGN KEY (anggota_id) REFERENCES anggota_keluarga(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ============================================================
|
|
-- TABEL: histori_bantuan
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS histori_bantuan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
warga_id INT NOT NULL,
|
|
tanggal DATE NOT NULL,
|
|
jenis VARCHAR(50) NOT NULL,
|
|
nominal BIGINT DEFAULT 0,
|
|
sumber_dana VARCHAR(100),
|
|
keterangan TEXT,
|
|
created_by INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (warga_id) REFERENCES warga(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ============================================================
|
|
-- TABEL: laporan (dari masyarakat)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS laporan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
no_laporan VARCHAR(20) NOT NULL UNIQUE,
|
|
nama_pelapor VARCHAR(100),
|
|
hp_pelapor VARCHAR(20),
|
|
nama_dilaporkan VARCHAR(150) NOT NULL,
|
|
nik VARCHAR(16),
|
|
kelurahan_id INT,
|
|
kategori ENUM('sangat_miskin','miskin','hampir_miskin') NOT NULL,
|
|
jumlah_anggota INT DEFAULT 0,
|
|
penghasilan BIGINT DEFAULT 0,
|
|
keterangan TEXT NOT NULL,
|
|
lat DECIMAL(10,7),
|
|
lng DECIMAL(10,7),
|
|
status ENUM('menunggu','disetujui','ditolak') DEFAULT 'menunggu',
|
|
warga_id INT COMMENT 'warga_id jika laporan disetujui',
|
|
approved_by INT,
|
|
tgl_laporan DATE NOT NULL,
|
|
tgl_proses TIMESTAMP NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (kelurahan_id) REFERENCES kelurahan(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (warga_id) REFERENCES warga(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ============================================================
|
|
-- SEED DATA: users
|
|
-- ============================================================
|
|
INSERT INTO users (username, password, role, jabatan, can_edit, is_admin, is_wali) VALUES
|
|
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'Kadis Sosial', 1, 1, 0),
|
|
('petugas', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'petugas', 'Staf Verifikasi', 1, 0, 0),
|
|
('walikota', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'walikota', 'Walikota Pontianak', 0, 0, 1),
|
|
('viewer', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'viewer', 'Pemangku Kepentingan', 0, 0, 0);
|
|
|
|
-- NOTE: Password hash di atas adalah hash bcrypt dari kata "password"
|
|
-- Ubah password melalui phpMyAdmin atau script update_password.php
|
|
-- Password asli: admin=admin123, petugas=petugas123, walikota=wali2024, viewer=viewer123
|
|
-- (Hash akan diupdate otomatis saat pertama login via generate_passwords.php)
|
|
|
|
-- ============================================================
|
|
-- SEED DATA: kelurahan (6 kecamatan, 24 kelurahan)
|
|
-- ============================================================
|
|
INSERT INTO kelurahan (nama, kecamatan) VALUES
|
|
-- Pontianak Kota
|
|
('Kel. Tengah', 'Pontianak Kota'),
|
|
('Kel. Darat Sekip', 'Pontianak Kota'),
|
|
('Kel. Sungai Bangkong', 'Pontianak Kota'),
|
|
('Kel. Mariana', 'Pontianak Kota'),
|
|
('Kel. Sungai Jawi', 'Pontianak Kota'),
|
|
-- Pontianak Selatan
|
|
('Kel. Parit Tokaya', 'Pontianak Selatan'),
|
|
('Kel. Benua Melayu Darat', 'Pontianak Selatan'),
|
|
('Kel. Benua Melayu Laut', 'Pontianak Selatan'),
|
|
('Kel. Kota Baru', 'Pontianak Selatan'),
|
|
-- Pontianak Barat
|
|
('Kel. Sungai Beliung', 'Pontianak Barat'),
|
|
('Kel. Pal Lima', 'Pontianak Barat'),
|
|
('Kel. Akcaya', 'Pontianak Barat'),
|
|
('Kel. Sungai Jawi Luar', 'Pontianak Barat'),
|
|
-- Pontianak Utara
|
|
('Kel. Siantan Hulu', 'Pontianak Utara'),
|
|
('Kel. Batu Layang', 'Pontianak Utara'),
|
|
('Kel. Saigon', 'Pontianak Utara'),
|
|
('Kel. Siantan Hilir', 'Pontianak Utara'),
|
|
-- Pontianak Timur
|
|
('Kel. Tanjung Hulu', 'Pontianak Timur'),
|
|
('Kel. Banjar Serasan', 'Pontianak Timur'),
|
|
('Kel. Dalam Bugis', 'Pontianak Timur'),
|
|
-- Pontianak Tenggara
|
|
('Kel. Bangka Belitung Darat', 'Pontianak Tenggara'),
|
|
('Kel. Bangka Belitung Laut', 'Pontianak Tenggara'),
|
|
('Kel. Bansir Laut', 'Pontianak Tenggara'),
|
|
('Kel. Bansir Darat', 'Pontianak Tenggara');
|
|
|
|
-- ============================================================
|
|
-- SEED DATA: rumah_ibadah
|
|
-- ============================================================
|
|
INSERT INTO rumah_ibadah (nama, tipe, ikon, lat, lng, kapasitas, kelurahan_id, alamat) VALUES
|
|
('Masjid Al-Ikhlas', 'masjid', '🕌', -0.0180000, 109.3320000, 150, 4, 'Jl. Mariana, Pontianak Kota'),
|
|
('Masjid Raya Mujahidin', 'masjid', '🕌', -0.0428000, 109.3373000, 500, 12, 'Jl. Jend. Ahmad Yani, Pontianak Selatan'),
|
|
('Masjid Jami Sultan', 'masjid', '🕌', -0.0400000, 109.3440000, 300, 20, 'Jl. Tanjungpura, Pontianak Timur'),
|
|
('Masjid Nurul Hidayah', 'masjid', '🕌', -0.0100000, 109.3360000, 100, 14, 'Jl. Siantan Hulu, Pontianak Utara'),
|
|
('Gereja GPIB Immanuel', 'gereja', '⛪', -0.0130000, 109.3480000, 200, 14, 'Jl. Sisingamangaraja, Pontianak Utara'),
|
|
('Gereja Bethel Indonesia','gereja', '⛪', -0.0300000, 109.3370000, 180, 6, 'Jl. Parit Tokaya, Pontianak Selatan'),
|
|
('Gereja Katedral', 'gereja', '⛪', -0.0350000, 109.3620000, 350, 15, 'Jl. Diponegoro, Pontianak Utara'),
|
|
('Pura Girinatha', 'pura', '🛕', -0.0220000, 109.3550000, 80, 3, 'Jl. Sungai Bangkong, Pontianak Kota'),
|
|
('Vihara Bodhisatva', 'vihara', '🏛️', -0.0080000, 109.3400000, 60, 14, 'Jl. Siantan, Pontianak Utara'),
|
|
('Klenteng Tua Pek Kong', 'klenteng', '🏯', -0.0260000, 109.3420000, 70, 1, 'Jl. Diponegoro, Pontianak Kota');
|
|
|
|
-- ============================================================
|
|
-- VIEWS berguna
|
|
-- ============================================================
|
|
CREATE OR REPLACE VIEW v_warga_lengkap AS
|
|
SELECT
|
|
w.id, w.nama, w.nik, w.lat, w.lng, w.kategori,
|
|
w.jumlah_anggota, w.penghasilan, w.tgl_lahir, w.pendidikan,
|
|
w.keterangan, w.status, w.pelapor, w.no_laporan, w.created_at,
|
|
k.nama AS kelurahan, k.kecamatan,
|
|
(SELECT COUNT(*) FROM histori_bantuan hb WHERE hb.warga_id = w.id) AS total_bantuan,
|
|
(SELECT COALESCE(SUM(hb2.nominal),0) FROM histori_bantuan hb2 WHERE hb2.warga_id = w.id) AS total_nominal_bantuan
|
|
FROM warga w
|
|
LEFT JOIN kelurahan k ON w.kelurahan_id = k.id;
|
|
|
|
CREATE OR REPLACE VIEW v_laporan_lengkap AS
|
|
SELECT
|
|
l.id, l.no_laporan, l.nama_pelapor, l.hp_pelapor,
|
|
l.nama_dilaporkan, l.nik, l.kategori, l.jumlah_anggota,
|
|
l.penghasilan, l.keterangan, l.lat, l.lng, l.status,
|
|
l.tgl_laporan, l.created_at,
|
|
k.nama AS kelurahan, k.kecamatan
|
|
FROM laporan l
|
|
LEFT JOIN kelurahan k ON l.kelurahan_id = k.id;
|
|
|
|
-- ============================================================
|
|
-- TABEL: fasilitas_umum (opsional)
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS fasilitas_umum (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(150) NOT NULL,
|
|
kategori VARCHAR(50) NOT NULL,
|
|
lat DECIMAL(10,7) NOT NULL,
|
|
lng DECIMAL(10,7) NOT NULL,
|
|
alamat TEXT,
|
|
keterangan TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB;
|
|
|
|
-- ============================================================
|
|
-- SEED DATA: fasilitas_umum
|
|
-- ============================================================
|
|
INSERT INTO fasilitas_umum (nama, kategori, lat, lng, alamat, keterangan) VALUES
|
|
-- Kesehatan
|
|
('RSUD Dr. Soedarso', 'kesehatan', -0.0461000, 109.3458000, 'Jl. Dr. Soedarso, Pontianak Tenggara', 'Rumah Sakit Umum Daerah Provinsi Kalbar'),
|
|
('Puskesmas Parit Tokaya', 'kesehatan', -0.0396000, 109.3364000, 'Jl. Paris II, Pontianak Selatan', 'Pelayanan Kesehatan Tingkat Pertama'),
|
|
('RS Katolik Antonius', 'kesehatan', -0.0336000, 109.3242000, 'Jl. K.H. Wahid Hasyim, Pontianak Kota', 'Rumah Sakit Swasta Tipe B'),
|
|
-- Pendidikan
|
|
('Universitas Tanjungpura', 'pendidikan', -0.0622000, 109.3468000, 'Jl. Prof. Dr. H. Hadari Nawawi, Pontianak Tenggara', 'Perguruan Tinggi Negeri Utama Kalbar'),
|
|
('SMA Negeri 1 Pontianak', 'pendidikan', -0.0354000, 109.3328000, 'Jl. Anjungan, Pontianak Kota', 'Sekolah Menengah Atas Negeri'),
|
|
('SMP Negeri 1 Pontianak', 'pendidikan', -0.0248000, 109.3344000, 'Jl. Jend. Urip Sumoharjo, Pontianak Kota', 'Sekolah Menengah Pertama Negeri'),
|
|
-- Pasar Tradisional
|
|
('Pasar Flamboyan', 'pasar', -0.0381000, 109.3394000, 'Jl. Pahlawan, Pontianak Selatan', 'Pasar Tradisional Terbesar di Kota Pontianak'),
|
|
('Pasar Mawar', 'pasar', -0.0279000, 109.3387000, 'Jl. Cokroaminoto, Pontianak Kota', 'Pasar Tradisional Sayur & Kebutuhan Pokok'),
|
|
('Pasar Kemuning', 'pasar', -0.0456000, 109.3218000, 'Jl. Prof. M. Yamin, Pontianak Kota', 'Pasar Rakyat Modern Kelurahan Kota Baru'),
|
|
-- Kantor Kelurahan
|
|
('Kantor Lurah Akcaya', 'kelurahan', -0.0352000, 109.3236000, 'Jl. Sutan Syahrir, Pontianak Selatan', 'Pelayanan Publik Kelurahan Akcaya'),
|
|
('Kantor Lurah Parit Tokaya', 'kelurahan', -0.0478000, 109.3415000, 'Jl. Paris I, Pontianak Selatan', 'Pelayanan Publik Kelurahan Parit Tokaya'),
|
|
('Kantor Lurah Benua Melayu Darat', 'kelurahan', -0.0345000, 109.3444000, 'Jl. Gajah Mada, Pontianak Selatan', 'Pelayanan Publik Kelurahan BMD');
|