-- ============================================================
-- Migrasi Fase B — Tabel Wilayah (choropleth) + seed sampel
-- ============================================================
-- Polygon kelurahan SAMPEL (disederhanakan) di sekitar data warga.
-- Geometri SRID 0 agar konsisten dengan data spasial lama.
-- jumlah_penduduk = jumlah KK (dummy) untuk hitung persentase miskin.
-- ============================================================

CREATE TABLE IF NOT EXISTS wilayah (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    kode_wilayah    VARCHAR(20)  DEFAULT NULL,
    nama            VARCHAR(100) NOT NULL,
    jenis           ENUM('kelurahan','kecamatan') NOT NULL DEFAULT 'kelurahan',
    parent_id       INT          DEFAULT NULL,
    jumlah_penduduk INT          NOT NULL DEFAULT 0,
    geom            GEOMETRY      NOT NULL,
    created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    SPATIAL INDEX idx_wilayah_geom (geom)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO wilayah (kode_wilayah, nama, jenis, jumlah_penduduk, geom) VALUES
    ('6171010001', 'Kel. Tambelan Sampit', 'kelurahan', 45,
        ST_GeomFromText('POLYGON((109.357 -0.047, 109.368 -0.047, 109.368 -0.058, 109.357 -0.058, 109.357 -0.047))')),
    ('6171010002', 'Kel. Sungai Bangkong', 'kelurahan', 60,
        ST_GeomFromText('POLYGON((109.328 -0.043, 109.340 -0.043, 109.340 -0.055, 109.328 -0.055, 109.328 -0.043))')),
    ('6171010003', 'Kel. Akcaya', 'kelurahan', 80,
        ST_GeomFromText('POLYGON((109.339 -0.035, 109.350 -0.035, 109.350 -0.045, 109.339 -0.045, 109.339 -0.035))')),
    ('6171010004', 'Kel. Benua Melayu Darat', 'kelurahan', 55,
        ST_GeomFromText('POLYGON((109.318 -0.044, 109.329 -0.044, 109.329 -0.056, 109.318 -0.056, 109.318 -0.044))'));