Mr.Haruna
83 lines
4.7 KiB
SQL
83 lines
4.7 KiB
SQL
-- ============================================================
|
|
-- Migrasi Fase C — Manajemen Data Kemiskinan (KF-12 s/d 18)
|
|
-- ============================================================
|
|
-- Memperkaya warga_miskin + bantuan sosial + foto + fasilitas publik.
|
|
-- Aman dijalankan pada DB existing (kolom & tabel baru saja).
|
|
-- ============================================================
|
|
|
|
-- 1. Perluas kolom warga_miskin (KF-12 / KF-18)
|
|
ALTER TABLE warga_miskin
|
|
ADD COLUMN nik_kk VARCHAR(16) DEFAULT NULL AFTER nama_kk,
|
|
ADD COLUMN alamat VARCHAR(255) DEFAULT NULL AFTER nik_kk,
|
|
ADD COLUMN wilayah_id INT DEFAULT NULL AFTER alamat,
|
|
ADD COLUMN rt VARCHAR(5) DEFAULT NULL AFTER wilayah_id,
|
|
ADD COLUMN rw VARCHAR(5) DEFAULT NULL AFTER rt,
|
|
ADD COLUMN jumlah_jiwa INT DEFAULT NULL AFTER rw,
|
|
ADD COLUMN pekerjaan_kk VARCHAR(100) DEFAULT NULL AFTER jumlah_jiwa,
|
|
ADD COLUMN sumber_air ENUM('SUMUR_BOR','PDAM','SUNGAI','HUJAN','TIDAK_ADA') DEFAULT NULL,
|
|
ADD COLUMN status_listrik ENUM('PLN','NON_PLN','TIDAK_ADA') DEFAULT NULL,
|
|
ADD COLUMN material_dinding ENUM('PERMANEN','SEMI_PERMANEN','TIDAK_PERMANEN') DEFAULT NULL,
|
|
ADD COLUMN status_kepemilikan ENUM('MILIK','SEWA','NUMPANG','LAINNYA') DEFAULT NULL,
|
|
ADD COLUMN status_verifikasi ENUM('belum','terverifikasi','perlu_tinjauan') NOT NULL DEFAULT 'belum',
|
|
ADD COLUMN created_by INT DEFAULT NULL;
|
|
|
|
-- 2. Master jenis bantuan (KF-17) + seed
|
|
CREATE TABLE IF NOT EXISTS jenis_bantuan (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
kode VARCHAR(20) NOT NULL UNIQUE,
|
|
nama VARCHAR(100) NOT NULL,
|
|
deskripsi VARCHAR(255) DEFAULT NULL
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT IGNORE INTO jenis_bantuan (kode, nama, deskripsi) VALUES
|
|
('PKH', 'Program Keluarga Harapan', 'Bantuan tunai bersyarat keluarga miskin'),
|
|
('BPNT', 'Bantuan Pangan Non Tunai', 'Bantuan sembako via KKS'),
|
|
('PBI-JK', 'Penerima Bantuan Iuran JK', 'Subsidi iuran BPJS Kesehatan'),
|
|
('BLT-DD', 'Bantuan Langsung Tunai Dana Desa','Bantuan tunai dari Dana Desa'),
|
|
('RUTILAHU','Rumah Tidak Layak Huni', 'Rehabilitasi rumah tidak layak huni'),
|
|
('KIP', 'Kartu Indonesia Pintar', 'Bantuan biaya pendidikan');
|
|
|
|
-- 3. Riwayat penerimaan bantuan per rumah tangga (KF-17)
|
|
CREATE TABLE IF NOT EXISTS bantuan_sosial (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
warga_id INT NOT NULL,
|
|
jenis_bantuan_id INT NOT NULL,
|
|
tgl_mulai DATE DEFAULT NULL,
|
|
tgl_berakhir DATE DEFAULT NULL,
|
|
is_aktif TINYINT(1) NOT NULL DEFAULT 1,
|
|
keterangan VARCHAR(255) DEFAULT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_bansos_warga (warga_id),
|
|
INDEX idx_bansos_jenis (jenis_bantuan_id),
|
|
CONSTRAINT fk_bansos_warga FOREIGN KEY (warga_id) REFERENCES warga_miskin(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_bansos_jenis FOREIGN KEY (jenis_bantuan_id) REFERENCES jenis_bantuan(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 4. Foto kondisi rumah (KF-13)
|
|
CREATE TABLE IF NOT EXISTS foto_rumah (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
warga_id INT NOT NULL,
|
|
file_path VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_foto_warga (warga_id),
|
|
CONSTRAINT fk_foto_warga FOREIGN KEY (warga_id) REFERENCES warga_miskin(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 5. Fasilitas publik untuk proximity (KF-21) + seed sampel
|
|
CREATE TABLE IF NOT EXISTS fasilitas_publik (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
nama VARCHAR(120) NOT NULL,
|
|
jenis ENUM('sekolah','puskesmas','pasar','kantor_kelurahan','rumah_ibadah','lainnya') NOT NULL DEFAULT 'lainnya',
|
|
wilayah_id INT DEFAULT NULL,
|
|
geom GEOMETRY NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
SPATIAL INDEX idx_fasilitas_geom (geom)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT INTO fasilitas_publik (nama, jenis, geom) VALUES
|
|
('SDN 01 Pontianak Kota', 'sekolah', ST_GeomFromText('POINT(109.3380 -0.0420)')),
|
|
('Puskesmas Alianyang', 'puskesmas', ST_GeomFromText('POINT(109.3330 -0.0480)')),
|
|
('Pasar Flamboyan', 'pasar', ST_GeomFromText('POINT(109.3470 -0.0250)')),
|
|
('Kantor Kel. Akcaya', 'kantor_kelurahan', ST_GeomFromText('POINT(109.3445 -0.0400)')),
|
|
('Puskesmas Kampung Bali','puskesmas', ST_GeomFromText('POINT(109.3360 -0.0440)'));
|