-- ============================================
-- WebGIS SPBU - Database Schema
-- ============================================

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

USE webgis_spbu;

CREATE TABLE IF NOT EXISTS spbu (
  id         INT           NOT NULL AUTO_INCREMENT,
  nama       VARCHAR(150)  NOT NULL,
  no_spbu    VARCHAR(50)   NOT NULL,
  status     ENUM('24jam', 'tidak') NOT NULL DEFAULT 'tidak',
  latitude   DECIMAL(10, 8) NOT NULL,
  longitude  DECIMAL(11, 8) NOT NULL,
  created_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Contoh data awal (opsional)
-- ============================================
INSERT INTO spbu (nama, no_spbu, status, latitude, longitude) VALUES
  ('SPBU Sudirman',     '64.751.01', '24jam', -0.0260000, 109.3425000),
  ('SPBU Ahmad Yani',  '64.751.02', 'tidak', -0.0310000, 109.3218000),
  ('SPBU Sutan Syahrir','64.751.03', '24jam', -0.0195000, 109.3612000);

-- ============================================
-- Tabel Jalan (Line)
-- ============================================
CREATE TABLE IF NOT EXISTS jalan (
  id          INT           NOT NULL AUTO_INCREMENT,
  nama_jalan  VARCHAR(150)  NOT NULL,
  status      ENUM('Nasional', 'Provinsi', 'Kabupaten') NOT NULL,
  panjang     DECIMAL(10, 2) NOT NULL, -- dalam meter
  geom        LONGTEXT      NOT NULL,  -- GeoJSON Feature atau Geometry LineString
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Tabel Parsil Tanah (Polygon)
-- ============================================
CREATE TABLE IF NOT EXISTS parsil (
  id            INT           NOT NULL AUTO_INCREMENT,
  nama_pemilik  VARCHAR(150)  NOT NULL,
  status        ENUM('SHM', 'HGB', 'HGU', 'HP') NOT NULL,
  luas          DECIMAL(12, 2) NOT NULL, -- dalam meter persegi
  geom          LONGTEXT      NOT NULL,  -- GeoJSON Feature atau Geometry Polygon
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Tabel Rumah Ibadah (Point)
-- ============================================
CREATE TABLE IF NOT EXISTS rumah_ibadah (
  id INT NOT NULL AUTO_INCREMENT,
  nama VARCHAR(150) NOT NULL,
  alamat TEXT NOT NULL,
  latitude DECIMAL(10, 8) NOT NULL,
  longitude DECIMAL(11, 8) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Tabel Penduduk Miskin (Point)
-- ============================================
CREATE TABLE IF NOT EXISTS penduduk_miskin (
  id INT NOT NULL AUTO_INCREMENT,
  nama_kk VARCHAR(150) NOT NULL,
  keterangan TEXT,
  latitude DECIMAL(10, 8) NOT NULL,
  longitude DECIMAL(11, 8) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Tabel Admin (Users)
-- ============================================
CREATE TABLE IF NOT EXISTS users (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin (Password: admin123)
-- Hash generated by password_hash('admin123', PASSWORD_DEFAULT)
INSERT IGNORE INTO users (username, password) VALUES 
('admin', '$2y$10$WqB3H9H1B3h1B3h1B3h1B.Q1B3h1B3h1B3h1B3h1B3h1B3h1B3h1.');