-- ============================================================
--  WebGIS Manajemen Jalan & Parsil Tanah
--  Database Structure
--  Geometri disimpan dalam format GeoJSON (TEXT)
-- ============================================================

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

USE webgis_jalan_parsil;

-- ============================================================
--  Tabel: jalan (Data Line / Polyline)
-- ============================================================
CREATE TABLE IF NOT EXISTS jalan (
    id             INT(11)       NOT NULL AUTO_INCREMENT,
    nama_jalan     VARCHAR(200)  NOT NULL,
    status_jalan   ENUM('Nasional','Provinsi','Kabupaten') NOT NULL,
    panjang_meter  DECIMAL(12,2) NOT NULL DEFAULT 0.00  COMMENT 'Dihitung otomatis oleh LeafletJS (meter)',
    geojson        TEXT          NOT NULL               COMMENT 'Geometri GeoJSON LineString',
    created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Data jalan berupa polyline GeoJSON';

-- ============================================================
--  Tabel: parsil_tanah (Data Polygon)
-- ============================================================
CREATE TABLE IF NOT EXISTS parsil_tanah (
    id              INT(11)       NOT NULL AUTO_INCREMENT,
    nama_pemilik    VARCHAR(200)  NOT NULL,
    no_sertifikat   VARCHAR(100)  NOT NULL,
    status_sertifikat ENUM('SHM','HGB','HGU','HP') NOT NULL,
    luas_meter2     DECIMAL(14,2) NOT NULL DEFAULT 0.00 COMMENT 'Dihitung otomatis oleh LeafletJS (m²)',
    geojson         TEXT          NOT NULL              COMMENT 'Geometri GeoJSON Polygon',
    created_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Data parsil tanah berupa polygon GeoJSON';

-- ============================================================
--  Sample data Jalan (di sekitar Pontianak)
-- ============================================================
INSERT INTO jalan (nama_jalan, status_jalan, panjang_meter, geojson) VALUES
(
  'Jl. Ahmad Yani', 'Nasional', 2450.50,
  '{"type":"LineString","coordinates":[[109.3300,-0.0200],[109.3380,-0.0240],[109.3450,-0.0270],[109.3520,-0.0290]]}'
),
(
  'Jl. Sultan Syarif Abdurrahman', 'Provinsi', 1820.30,
  '{"type":"LineString","coordinates":[[109.3250,-0.0350],[109.3310,-0.0330],[109.3370,-0.0310],[109.3420,-0.0290]]}'
),
(
  'Jl. Parit H. Husin II', 'Kabupaten', 980.75,
  '{"type":"LineString","coordinates":[[109.3480,-0.0420],[109.3510,-0.0400],[109.3540,-0.0380]]}'
);

-- ============================================================
--  Sample data Parsil Tanah (di sekitar Pontianak)
-- ============================================================
INSERT INTO parsil_tanah (nama_pemilik, no_sertifikat, status_sertifikat, luas_meter2, geojson) VALUES
(
  'Budi Santoso', 'SHM-PTK-001234', 'SHM', 520.00,
  '{"type":"Polygon","coordinates":[[[109.3320,-0.0280],[109.3330,-0.0280],[109.3330,-0.0290],[109.3320,-0.0290],[109.3320,-0.0280]]]}'
),
(
  'PT. Maju Jaya', 'HGB-PTK-005678', 'HGB', 1250.00,
  '{"type":"Polygon","coordinates":[[[109.3390,-0.0320],[109.3410,-0.0320],[109.3410,-0.0335],[109.3390,-0.0335],[109.3390,-0.0320]]]}'
),
(
  'Koperasi Tani Makmur', 'HGU-PTK-009012', 'HGU', 3800.00,
  '{"type":"Polygon","coordinates":[[[109.3460,-0.0380],[109.3490,-0.0380],[109.3490,-0.0405],[109.3460,-0.0405],[109.3460,-0.0380]]]}'
);