-- ============================================================
-- DATABASE LENGKAP: webgis_p3
-- WebGIS Poverty Mapping + Sistem Login (3 Role)
-- Import file ini ke Laragon phpMyAdmin
-- ============================================================

CREATE DATABASE IF NOT EXISTS webgis_p3
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE webgis_p3;

-- ── Tabel Data Jalan (Polyline) ────────────────────────────
CREATE TABLE IF NOT EXISTS data_jalan (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nama_jalan  VARCHAR(200) NOT NULL,
    status      ENUM('Nasional','Provinsi','Kabupaten') NOT NULL DEFAULT 'Kabupaten',
    panjang_m   DOUBLE NOT NULL DEFAULT 0,
    geojson     LONGTEXT NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Tabel Data Parsil Tanah (Polygon) ─────────────────────
CREATE TABLE IF NOT EXISTS data_parsil (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    no_parsil   VARCHAR(100) NOT NULL,
    pemilik     VARCHAR(200) NOT NULL,
    status      ENUM('SHM','HGB','HGU','HP') NOT NULL DEFAULT 'SHM',
    luas_m2     DOUBLE NOT NULL DEFAULT 0,
    geojson     LONGTEXT NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Tabel Data SPBU (Point/Marker) ────────────────────────
CREATE TABLE IF NOT EXISTS data_spbu (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nama_spbu   VARCHAR(200) NOT NULL,
    no_wa       VARCHAR(20)  NOT NULL,
    buka_24_jam ENUM('Ya','Tidak') NOT NULL DEFAULT 'Tidak',
    latitude    DOUBLE NOT NULL,
    longitude   DOUBLE NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Data Contoh SPBU ──────────────────────────────────────
INSERT INTO data_spbu (nama_spbu, no_wa, buka_24_jam, latitude, longitude) VALUES
('SPBU Jl. Ahmad Yani',       '0812-1111-0001', 'Ya',    -0.0400, 109.3200),
('SPBU Jl. Gajah Mada',       '0812-1111-0002', 'Ya',    -0.0520, 109.3450),
('SPBU Jl. Diponegoro',       '0812-1111-0003', 'Tidak', -0.0610, 109.3600),
('SPBU Jl. Sutan Syahrir',    '0812-1111-0004', 'Tidak', -0.0480, 109.3700),
('SPBU Jl. Kom. Yos Sudarso', '0812-1111-0005', 'Ya',    -0.0350, 109.3550);

-- ── Tabel Data Kemiskinan (Point/Marker) ──────────────────
CREATE TABLE IF NOT EXISTS data_kemiskinan (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nama_kk     VARCHAR(200) NOT NULL,
    jumlah_kk   INT          NOT NULL DEFAULT 1,
    alamat      TEXT         NOT NULL,
    latitude    DOUBLE       NOT NULL,
    longitude   DOUBLE       NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Tabel Data Rumah Ibadah (Point/Marker + Radius Buffer)
CREATE TABLE IF NOT EXISTS data_masjid (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nama_masjid VARCHAR(200) NOT NULL,
    jenis       ENUM('Masjid','Mushola','Gereja','Pura','Vihara','Klenteng') NOT NULL DEFAULT 'Masjid',
    pic_masjid  VARCHAR(200) NOT NULL,
    alamat      TEXT         NOT NULL,
    radius_m    INT          NOT NULL DEFAULT 500,
    latitude    DOUBLE       NOT NULL,
    longitude   DOUBLE       NOT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Tabel Users (Sistem Login 3 Role) ─────────────────────
CREATE TABLE IF NOT EXISTS users (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    username       VARCHAR(100) NOT NULL UNIQUE,
    nama_lengkap   VARCHAR(200) NOT NULL,
    role           ENUM('admin','petugas','pengurus','walikota') NOT NULL,
    password_hash  VARCHAR(255) NOT NULL,
    aktif          TINYINT(1) NOT NULL DEFAULT 1,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role  (role),
    INDEX idx_aktif (aktif)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Akun Demo ─────────────────────────────────────────────
-- Password semua akun: "password" (bcrypt via PHP PASSWORD_DEFAULT)
INSERT INTO users (username, nama_lengkap, role, password_hash) VALUES
(
    'admin',
    'Administrator Sistem',
    'admin',
    '$2y$10$mhMxyKlOSSTQxfmWXEncfe1AF56CJ8fb.v0h0CYZ.ELQQd8iP5LrG'
),
(
    'petugas',
    'Petugas Lapangan',
    'petugas',
    '$2y$10$mhMxyKlOSSTQxfmWXEncfe1AF56CJ8fb.v0h0CYZ.ELQQd8iP5LrG'
),
(
    'pengurus',
    'Pengurus Rumah Ibadah',
    'pengurus',
    '$2y$10$mhMxyKlOSSTQxfmWXEncfe1AF56CJ8fb.v0h0CYZ.ELQQd8iP5LrG'
),
(
    'walikota',
    'Walikota Pontianak',
    'walikota',
    '$2y$10$mhMxyKlOSSTQxfmWXEncfe1AF56CJ8fb.v0h0CYZ.ELQQd8iP5LrG'
);