-- ============================================================
-- WebGIS Database Schema
-- Supports: Points (SPBU, Mosque, Poor Population),
--           Lines (Roads), Polygons (Land Parcels)
-- ============================================================

CREATE DATABASE IF NOT EXISTS webgis_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE webgis_db;

-- ------------------------------------------------------------
-- TABLE: points
-- Stores all point-type features (SPBU, Mosque, Poor Population)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS points (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(255) NOT NULL,
    category            ENUM('spbu','mosque','poor') NOT NULL COMMENT 'spbu=Gas Station, mosque=Mosque, poor=Poor Population',
    subtype             VARCHAR(100)  DEFAULT NULL  COMMENT 'For SPBU: 24hours or not24hours',
    latitude            DECIMAL(10,8) NOT NULL,
    longitude           DECIMAL(11,8) NOT NULL,
    description         TEXT          DEFAULT NULL,
    created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    tanggal_lahir       DATE          DEFAULT NULL,
    pendidikan          VARCHAR(100)  DEFAULT NULL,
    pekerjaan           VARCHAR(100)  DEFAULT NULL,
    jumlah_tanggungan   INT           DEFAULT NULL,
    riwayat_penyakit    TEXT          DEFAULT NULL,
    alamat              TEXT          DEFAULT NULL,
    status_verifikasi   ENUM('Menunggu Verifikasi','Layak','Tidak Layak','Perlu Verifikasi Ulang') DEFAULT 'Menunggu Verifikasi'
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLE: laporan_masyarakat
-- Stores reports submitted by citizens about poor populations
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS laporan_masyarakat (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    nama_pelapor  VARCHAR(100) NOT NULL,
    no_hp         VARCHAR(20) DEFAULT NULL,
    nama_warga    VARCHAR(100) NOT NULL,
    alamat        TEXT DEFAULT NULL,
    latitude      DECIMAL(10,8) DEFAULT NULL,
    longitude     DECIMAL(11,8) DEFAULT NULL,
    keterangan    TEXT DEFAULT NULL,
    status        ENUM('Menunggu Verifikasi','Disetujui','Ditolak') DEFAULT 'Menunggu Verifikasi',
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLE: bantuan
-- Stores aid distribution records for poor populations
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bantuan (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    point_id        INT NOT NULL,
    jenis_bantuan   VARCHAR(100) NOT NULL,
    nominal         DECIMAL(12,2) DEFAULT NULL,
    tanggal_bantuan DATE DEFAULT NULL,
    instansi_pemberi VARCHAR(100) DEFAULT NULL,
    keterangan      TEXT DEFAULT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (point_id) REFERENCES points (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLE: roads
-- Stores road polylines with GeoJSON geometry
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS roads (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    road_type   ENUM('national','provincial','district') NOT NULL,
    length_m    DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'Length in meters, auto-calculated by frontend',
    geojson     LONGTEXT NOT NULL COMMENT 'GeoJSON LineString geometry',
    description TEXT DEFAULT NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- TABLE: parcels
-- Stores land parcel polygons with GeoJSON geometry
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS parcels (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    owner_name      VARCHAR(255) NOT NULL,
    ownership_type  ENUM('SHM','HGB','HGU','HP') NOT NULL,
    area_m2         DECIMAL(15,4) NOT NULL DEFAULT 0 COMMENT 'Area in square meters, auto-calculated by frontend',
    geojson         LONGTEXT NOT NULL COMMENT 'GeoJSON Polygon geometry',
    description     TEXT DEFAULT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Sample seed data (optional)
-- ------------------------------------------------------------
INSERT INTO points (name, category, subtype, latitude, longitude, description) VALUES
('SPBU Jl. Sudirman', 'spbu', '24hours', -6.9175, 107.6191, 'SPBU di pusat kota'),
('SPBU Jl. Asia Afrika', 'spbu', 'not24hours', -6.9214, 107.6062, 'SPBU selatan kota'),
('Masjid Agung', 'mosque', NULL, -6.9200, 107.6100, 'Masjid Agung Bandung'),
('Masjid Al-Ikhlas', 'mosque', NULL, -6.9150, 107.6250, 'Masjid Kelurahan'),
('Lokasi Kemiskinan A', 'poor', NULL, -6.9300, 107.6050, 'Kawasan padat penduduk'),
('Lokasi Kemiskinan B', 'poor', NULL, -6.9350, 107.6150, 'RW 07 Kebon Jeruk');