-- ============================================================
-- DATABASE: webgis_sig
-- Sistem Manajemen Data Jalan, Parsil Tanah & SPBU
-- ============================================================
-- ============================================================
-- TABEL: data_jalan (Line / Polyline)
-- Menyimpan data ruas jalan berdasarkan status jalan
-- Geometri disimpan dalam format GeoJSON (LineString)
-- ============================================================
CREATE TABLE IF NOT EXISTS data_jalan (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nama_jalan VARCHAR(150) NOT NULL COMMENT 'Nama ruas jalan',
    status_jalan ENUM (
        'Jalan Nasional',
        'Jalan Provinsi',
        'Jalan Kabupaten'
    ) NOT NULL COMMENT 'Status / klasifikasi jalan',
    panjang_m DECIMAL(12, 2) DEFAULT 0.00 COMMENT 'Panjang jalan dalam meter (dihitung otomatis oleh LeafletJS)',
    geojson LONGTEXT NOT NULL COMMENT 'Geometri LineString dalam format GeoJSON',
    keterangan TEXT DEFAULT NULL COMMENT 'Keterangan tambahan',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- TABEL: data_parsil (Polygon)
-- Menyimpan data parsil tanah / kavling berdasarkan status kepemilikan
-- Geometri disimpan dalam format GeoJSON (Polygon)
-- ============================================================
CREATE TABLE IF NOT EXISTS data_parsil (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nama_parsil VARCHAR(150) NOT NULL COMMENT 'Nama / nomor parsil',
    status_hak ENUM (
        'Sertifikat Hak Milik',
        'Sertifikat Hak Guna Bangunan',
        'Sertifikat Hak Guna Usaha',
        'Sertifikat Hak Pakai'
    ) NOT NULL COMMENT 'Status kepemilikan / hak atas tanah',
    luas_m2 DECIMAL(15, 2) DEFAULT 0.00 COMMENT 'Luas tanah dalam meter persegi (dihitung otomatis oleh LeafletJS)',
    geojson LONGTEXT NOT NULL COMMENT 'Geometri Polygon dalam format GeoJSON',
    pemilik VARCHAR(150) DEFAULT NULL COMMENT 'Nama pemilik (opsional)',
    keterangan TEXT DEFAULT NULL COMMENT 'Keterangan tambahan',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- DATA CONTOH – Jalan
-- ============================================================
INSERT INTO
    data_jalan (
        nama_jalan,
        status_jalan,
        panjang_m,
        geojson,
        keterangan
    )
VALUES
    (
        'Jl. Sudirman',
        'Jalan Nasional',
        1250.50,
        '{"type":"LineString","coordinates":[[106.8272,-6.2088],[106.8290,-6.2075],[106.8310,-6.2060],[106.8330,-6.2045]]}',
        'Jalan protokol pusat kota'
    ),
    (
        'Jl. Gatot Subroto',
        'Jalan Provinsi',
        980.00,
        '{"type":"LineString","coordinates":[[106.8200,-6.2100],[106.8220,-6.2095],[106.8240,-6.2090],[106.8260,-6.2085]]}',
        'Penghubung antar kecamatan'
    ),
    (
        'Jl. Pramuka',
        'Jalan Kabupaten',
        620.75,
        '{"type":"LineString","coordinates":[[106.8150,-6.2180],[106.8160,-6.2170],[106.8175,-6.2160],[106.8185,-6.2150]]}',
        'Jalan lingkungan kabupaten'
    );

-- ============================================================
-- DATA CONTOH – Parsil Tanah
-- ============================================================
INSERT INTO
    data_parsil (
        nama_parsil,
        status_hak,
        luas_m2,
        geojson,
        pemilik,
        keterangan
    )
VALUES
    (
        'Parsil A-001',
        'Sertifikat Hak Milik',
        1200.00,
        '{"type":"Polygon","coordinates":[[[106.8200,-6.2000],[106.8210,-6.2000],[106.8210,-6.2010],[106.8200,-6.2010],[106.8200,-6.2000]]]}',
        'Budi Santoso',
        'Tanah kavling perumahan'
    ),
    (
        'Parsil B-002',
        'Sertifikat Hak Guna Bangunan',
        850.50,
        '{"type":"Polygon","coordinates":[[[106.8220,-6.2000],[106.8230,-6.2000],[106.8230,-6.2008],[106.8220,-6.2008],[106.8220,-6.2000]]]}',
        'PT. Maju Bersama',
        'Kavling komersial'
    ),
    (
        'Parsil C-003',
        'Sertifikat Hak Guna Usaha',
        2500.00,
        '{"type":"Polygon","coordinates":[[[106.8240,-6.1990],[106.8260,-6.1990],[106.8260,-6.2010],[106.8240,-6.2010],[106.8240,-6.1990]]]}',
        'CV. Tani Makmur',
        'Lahan pertanian/perkebunan'
    ),
    (
        'Parsil D-004',
        'Sertifikat Hak Pakai',
        4000.00,
        '{"type":"Polygon","coordinates":[[[106.8270,-6.1985],[106.8295,-6.1985],[106.8295,-6.2005],[106.8270,-6.2005],[106.8270,-6.1985]]]}',
        'Pemerintah Kota',
        'Lahan fasilitas umum'
    );

-- ============================================================
-- TABEL: data_spbu (Point)
-- Menyimpan data SPBU (Stasiun Pengisian Bahan Bakar Umum)
-- Geometri disimpan dalam format GeoJSON (Point)
-- ============================================================
CREATE TABLE IF NOT EXISTS data_spbu (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    nama_spbu VARCHAR(150) NOT NULL COMMENT 'Nama SPBU / nama cabang',
    telepon VARCHAR(20) DEFAULT NULL COMMENT 'Nomor telepon SPBU',
    buka_24jam TINYINT (1) NOT NULL DEFAULT 0 COMMENT '1 = buka 24 jam (marker hijau), 0 = tidak (marker merah)',
    lat DECIMAL(10, 7) NOT NULL COMMENT 'Latitude titik SPBU',
    lng DECIMAL(10, 7) NOT NULL COMMENT 'Longitude titik SPBU',
    geojson LONGTEXT NOT NULL COMMENT 'Geometri Point dalam format GeoJSON',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- ============================================================
-- DATA CONTOH – SPBU di Pontianak
-- ============================================================
INSERT INTO
    data_spbu (nama_spbu, telepon, buka_24jam, lat, lng, geojson)
VALUES
    (
        'SPBU Jl. Ahmad Yani',
        '0561-123456',
        1,
        -0.0200,
        109.3300,
        '{"type":"Point","coordinates":[109.3300,-0.0200]}'
    ),
    (
        'SPBU Jl. Gajah Mada',
        '0561-654321',
        0,
        -0.0350,
        109.3200,
        '{"type":"Point","coordinates":[109.3200,-0.0350]}'
    ),
    (
        'SPBU Jl. Tanjungpura',
        '0561-789012',
        1,
        -0.0100,
        109.3450,
        '{"type":"Point","coordinates":[109.3450,-0.0100]}'
    ),
    (
        'SPBU Jl. Sutan Syahrir',
        '0561-345678',
        0,
        -0.0480,
        109.3380,
        '{"type":"Point","coordinates":[109.3380,-0.0480]}'
    );