-- ============================================================
--  WebGIS Portal — Combined Database Schema
--  Covers: Project 01 (SPBU Map), 02 (WebGIS Jalan & Tanah),
--          03 (Kepadatan Penduduk — reference), 04 (SPBU + Layer)
--
--  Usage:
--    mysql -u root -p < webgis-portal.sql
-- ============================================================

-- ============================================================
--  1. spbu_map  —  used by Project 01 and Project 04
-- ============================================================

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

USE spbu_map;

DROP TABLE IF EXISTS spbu;

CREATE TABLE spbu (
  id          INT UNSIGNED     AUTO_INCREMENT PRIMARY KEY,
  nama_spbu   VARCHAR(200)     NOT NULL,
  nomor_wa    VARCHAR(20)      NOT NULL DEFAULT '',
  buka_24_jam TINYINT(1)       NOT NULL DEFAULT 0
              COMMENT '1 = buka 24 jam, 0 = tidak buka 24 jam',
  latitude    DECIMAL(10, 7)   NOT NULL,
  longitude   DECIMAL(10, 7)   NOT NULL,
  created_at  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_buka   (buka_24_jam),
  INDEX idx_coords (latitude, longitude)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- Sample data — SPBU di area Pontianak
INSERT INTO spbu (nama_spbu, nomor_wa, buka_24_jam, latitude, longitude) VALUES
  ('Pertamina Ahmad Yani',        '6281234567890', 1, -0.0500000, 109.3450000),
  ('Pertamina Sungai Raya',       '6281234567891', 0, -0.0870000, 109.3620000),
  ('Shell Pontianak Tengah',      '6281234567892', 1, -0.0320000, 109.3300000),
  ('Pertamina Tanjung Raya',      '6281234567893', 0, -0.0130000, 109.3550000),
  ('Vivo Khatulistiwa',           '6281234567894', 1,  0.0020000, 109.3280000),
  ('Pertamina Khatulistiwa',      '6281234567895', 0, -0.0650000, 109.3400000),
  ('SPBU Gajah Mada',             '6281234567896', 1, -0.0210000, 109.3350000);


-- ============================================================
--  2. webgis_db  —  used by Project 02 (Jalan & Tanah)
-- ============================================================

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

USE webgis_db;

-- ── 2a. Jalan (polylines) ──────────────────────────────────

DROP TABLE IF EXISTS jalan;

CREATE TABLE jalan (
  id            INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
  nama_jalan    VARCHAR(200)   NOT NULL,
  status_jalan  ENUM('Nasional','Provinsi','Kabupaten')
                               NOT NULL DEFAULT 'Kabupaten',
  panjang_meter DECIMAL(12, 2) NOT NULL DEFAULT 0.00
                COMMENT 'Panjang jalan dalam meter, dihitung otomatis',
  geojson       MEDIUMTEXT     NOT NULL
                COMMENT 'GeoJSON LineString geometry (WGS84)',
  created_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_status (status_jalan)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- Sample jalan data
INSERT INTO jalan (nama_jalan, status_jalan, panjang_meter, geojson) VALUES
(
  'Jl. Ahmad Yani',
  'Nasional',
  4200.00,
  '{"type":"LineString","coordinates":[[109.325,-0.045],[109.330,-0.042],[109.335,-0.038],[109.340,-0.034],[109.345,-0.030]]}'
),
(
  'Jl. Gajah Mada',
  'Provinsi',
  2800.00,
  '{"type":"LineString","coordinates":[[109.330,-0.020],[109.333,-0.018],[109.336,-0.016],[109.340,-0.014]]}'
),
(
  'Jl. Teuku Umar',
  'Kabupaten',
  1900.00,
  '{"type":"LineString","coordinates":[[109.315,-0.055],[109.318,-0.052],[109.322,-0.050],[109.326,-0.048]]}'
),
(
  'Jl. Sultan Abdurrahman',
  'Provinsi',
  3300.00,
  '{"type":"LineString","coordinates":[[109.320,-0.028],[109.323,-0.025],[109.327,-0.022],[109.331,-0.019]]}'
),
(
  'Jl. Tanjungpura',
  'Nasional',
  5100.00,
  '{"type":"LineString","coordinates":[[109.310,-0.035],[109.315,-0.033],[109.320,-0.031],[109.326,-0.029],[109.333,-0.027]]}'
);

-- ── 2b. Parsil Tanah (polygons) ────────────────────────────

DROP TABLE IF EXISTS parsil_tanah;

CREATE TABLE parsil_tanah (
  id                  INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
  nama_parsil         VARCHAR(200)   NOT NULL,
  status_kepemilikan  ENUM('SHM','HGB','HGU','HP')
                                     NOT NULL DEFAULT 'SHM',
  luas_m2             DECIMAL(14, 2) NOT NULL DEFAULT 0.00
                      COMMENT 'Luas parsil dalam meter persegi, dihitung otomatis',
  geojson             MEDIUMTEXT     NOT NULL
                      COMMENT 'GeoJSON Polygon geometry (WGS84)',
  created_at          TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP
                      ON UPDATE CURRENT_TIMESTAMP,

  INDEX idx_status (status_kepemilikan)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- Sample parsil data
INSERT INTO parsil_tanah (nama_parsil, status_kepemilikan, luas_m2, geojson) VALUES
(
  'Kavling A-01',
  'SHM',
  2400.00,
  '{"type":"Polygon","coordinates":[[[109.328,-0.038],[109.330,-0.038],[109.330,-0.040],[109.328,-0.040],[109.328,-0.038]]]}'
),
(
  'Kavling B-03',
  'HGB',
  3100.00,
  '{"type":"Polygon","coordinates":[[[109.340,-0.025],[109.343,-0.025],[109.343,-0.028],[109.340,-0.028],[109.340,-0.025]]]}'
),
(
  'Kavling C-07',
  'HGU',
  4800.00,
  '{"type":"Polygon","coordinates":[[[109.318,-0.050],[109.322,-0.050],[109.322,-0.054],[109.318,-0.054],[109.318,-0.050]]]}'
),
(
  'Kavling D-02',
  'HP',
  1750.00,
  '{"type":"Polygon","coordinates":[[[109.335,-0.016],[109.337,-0.016],[109.337,-0.018],[109.335,-0.018],[109.335,-0.016]]]}'
);


-- ============================================================
--  3. kepadatan_db  —  reference for Project 03
--     (geometry is embedded statically in the front-end via
--      UTM reprojection; this table stores the attribute data)
-- ============================================================

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

USE kepadatan_db;

DROP TABLE IF EXISTS kecamatan;

CREATE TABLE kecamatan (
  id            INT UNSIGNED   AUTO_INCREMENT PRIMARY KEY,
  nama          VARCHAR(100)   NOT NULL,
  density       DECIMAL(6, 3)  NOT NULL
                COMMENT 'Kepadatan penduduk: ribu jiwa per km²',
  color_class   ENUM('rendah','sedang','tinggi')
                               NOT NULL
                COMMENT 'rendah ≤5, sedang 5-7, tinggi >7',
  geojson       MEDIUMTEXT     NOT NULL DEFAULT ''
                COMMENT 'GeoJSON Polygon geometry (WGS84); populasi via front-end UTM reprojection',
  created_at    TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_class (color_class)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

-- Data kepadatan dari BPS Kota Pontianak
INSERT INTO kecamatan (nama, density, color_class) VALUES
  ('Pontianak Selatan',   5.673, 'sedang'),
  ('Pontianak Utara',     3.642, 'rendah'),
  ('Pontianak Timur',     9.196, 'tinggi'),
  ('Pontianak Tenggara',  3.103, 'rendah'),
  ('Pontianak Kota',      8.102, 'tinggi'),
  ('Pontianak Barat',     9.331, 'tinggi');


-- ============================================================
--  End of schema
-- ============================================================