-- Run this in your `spbu_db` database.
-- If your MySQL version does not support JSON columns, change `koordinat JSON` to `koordinat TEXT`.

CREATE TABLE IF NOT EXISTS tb_jalan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_jalan VARCHAR(255) NOT NULL,
    status_jalan ENUM('Jalan Nasional','Jalan Provinsi','Jalan Kabupaten') NOT NULL,
    panjang_meter DOUBLE NOT NULL DEFAULT 0,
    koordinat JSON NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tb_parsil (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_pemilik VARCHAR(255) NOT NULL,
    status_kepemilikan ENUM('SHM','HGB','HGU','HP') NOT NULL,
    luas_m2 DOUBLE NOT NULL DEFAULT 0,
    koordinat JSON NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
