-- ============================================================
-- 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)'));