CREATE TABLE spbu_markers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_spbu VARCHAR(100) NOT NULL,
    status_operasional ENUM('Buka 24 Jam', 'Tidak 24 Jam') NOT NULL,
    no_telp VARCHAR(20) NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE jalan_polylines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_jalan VARCHAR(100) NOT NULL,
    status_jalan ENUM('Jalan Nasional', 'Jalan Provinsi', 'Jalan Kabupaten') NOT NULL,
    jarak_meter INT NOT NULL,
    koordinat JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE tanah_polygons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_pemilik VARCHAR(100) NOT NULL,
    status_kepemilikan ENUM('SHM', 'HGB', 'HGU', 'HP') NOT NULL,
    luas_meter INT NOT NULL,
    koordinat JSON NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE spbu_markers 
ADD COLUMN alamat TEXT AFTER no_telp;
CREATE TABLE rumah_ibadah (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(150) NOT NULL,
    jenis ENUM('Masjid', 'Gereja', 'Vihara', 'Pura', 'Klenteng') NOT NULL,
    radius_meter INT NOT NULL,
    alamat TEXT,
    latitude DOUBLE NOT NULL,
    longitude DOUBLE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE rumah_penduduk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_kk VARCHAR(100) NOT NULL,
    agama VARCHAR(50),
    jml_tanggungan INT DEFAULT 0,
    pengeluaran INT NOT NULL,
    pmt_lantai INT DEFAULT 0,
    pmt_dinding INT DEFAULT 0,
    pmt_sanitasi INT DEFAULT 0,
    pmt_penerangan INT DEFAULT 0,
    pmt_air INT DEFAULT 0,
    pmt_score INT NOT NULL,
    status_kemiskinan ENUM('Sangat Miskin', 'Miskin', 'Rentan Miskin', 'Tidak Miskin') NOT NULL,
    alamat TEXT,
    latitude DOUBLE NOT NULL,
    longitude DOUBLE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE log_bantuan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_penduduk INT NOT NULL,
    beras_kg INT DEFAULT 0,
    minyak_l INT DEFAULT 0,
    gula_kg INT DEFAULT 0,
    telur_kg INT DEFAULT 0,
    susu_kaleng INT DEFAULT 0,
    tunai_rp INT DEFAULT 0,
    catatan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_penduduk) REFERENCES rumah_penduduk(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS garis_kemiskinan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tahun YEAR NOT NULL,
    nilai_rupiah INT NOT NULL,
    is_active BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO garis_kemiskinan (tahun, nilai_rupiah, is_active) 
VALUES (2026, 700000, TRUE);

CREATE TABLE master_pelatihan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_pelatihan VARCHAR(255) NOT NULL,
    instansi_penyelenggara VARCHAR(255) NOT NULL,
    tanggal_mulai DATE NOT NULL,
    tanggal_selesai DATE NOT NULL,
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE anggota_keluarga (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_penduduk INT NOT NULL,
    nik VARCHAR(16) NOT NULL UNIQUE,
    nama_lengkap VARCHAR(255) NOT NULL,
    tanggal_lahir DATE NOT NULL,
    pendidikan_terakhir ENUM('Tidak Sekolah', 'SD', 'SMP', 'SMA/SMK', 'D3', 'S1', 'S2', 'S3') NOT NULL,
    pekerjaan VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_penduduk) REFERENCES rumah_penduduk(id) ON DELETE CASCADE
);

CREATE TABLE log_pelatihan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_anggota INT NOT NULL,
    id_pelatihan INT NOT NULL,
    tanggal_daftar TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_anggota) REFERENCES anggota_keluarga(id) ON DELETE CASCADE,
    FOREIGN KEY (id_pelatihan) REFERENCES master_pelatihan(id) ON DELETE CASCADE
);
CREATE TABLE laporan_musibah (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_penduduk INT NOT NULL,
    tanggal_lapor DATE NOT NULL,
    jenis_musibah ENUM('Kecelakaan', 'Kematian', 'Sakit Keras', 'Kebakaran', 'Bencana Alam', 'Lainnya') NOT NULL,
    deskripsi TEXT,
    status_penanganan ENUM('Belum Ditangani', 'Sudah Ditangani') DEFAULT 'Belum Ditangani',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_penduduk) REFERENCES rumah_penduduk(id) ON DELETE CASCADE
);
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);