-- =============================================================================
-- Raw MySQL untuk Sistem Pendataan PJU — Dinas Banyuwangi
-- Jangan jalankan php artisan migrate. Eksekusi script ini manual di MySQL.
-- =============================================================================

-- 1. Tabel jenis_lampu (boleh skip jika sudah ada dari migrasi)
-- CREATE TABLE IF NOT EXISTS jenis_lampu (
--     id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
--     nama VARCHAR(255) NOT NULL,
--     created_at TIMESTAMP NULL,
--     updated_at TIMESTAMP NULL
-- );

-- 2. Tabel jenis_tiang
CREATE TABLE IF NOT EXISTS pju.jenis_tiang (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL
);

-- 3. Kolom tambahan untuk lokasi_pju (titik pangkal/ujung, file ruas)
-- Jalankan satu per satu; lewati jika kolom sudah ada (Duplicate column name).
ALTER TABLE lokasi_pju ADD COLUMN titik_pangkal_lat DECIMAL(10, 7) NULL AFTER longitude;
ALTER TABLE lokasi_pju ADD COLUMN titik_pangkal_lng DECIMAL(10, 7) NULL AFTER titik_pangkal_lat;
ALTER TABLE lokasi_pju ADD COLUMN titik_ujung_lat DECIMAL(10, 7) NULL AFTER titik_pangkal_lng;
ALTER TABLE lokasi_pju ADD COLUMN titik_ujung_lng DECIMAL(10, 7) NULL AFTER titik_ujung_lat;
ALTER TABLE lokasi_pju ADD COLUMN file_ruas_path VARCHAR(500) NULL AFTER geojson;
ALTER TABLE lokasi_pju ADD COLUMN file_ruas_type VARCHAR(20) NULL COMMENT 'geojson|kml|kmz|gambar' AFTER file_ruas_path;

-- 4. Tabel pju (PJU per ruas jalan — beda dengan data_pju yang sudah ada)
CREATE TABLE IF NOT EXISTS pju (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ruas_jalan_id BIGINT UNSIGNED NOT NULL,
    nama_pju VARCHAR(255) NOT NULL,
    kode_pju VARCHAR(255) NOT NULL,
    jenis_lampu_id BIGINT UNSIGNED NULL,
    jenis_tiang_id BIGINT UNSIGNED NULL,
    kondisi_fisik ENUM('baik', 'sedang', 'rusak ringan', 'rusak berat') NOT NULL DEFAULT 'baik',
    status ENUM('menyala', 'tidak menyala') NOT NULL DEFAULT 'menyala',
    tahun_pasang SMALLINT UNSIGNED NULL,
    kepemilikan ENUM('Pemkab', 'Pemprov', 'Swasta') NULL,
    catatan TEXT NULL,
    lat DECIMAL(10, 7) NULL,
    lng DECIMAL(10, 7) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT pju_ruas_jalan_id_foreign FOREIGN KEY (ruas_jalan_id) REFERENCES lokasi_pju(id) ON DELETE CASCADE,
    CONSTRAINT pju_jenis_lampu_id_foreign FOREIGN KEY (jenis_lampu_id) REFERENCES jenis_lampu(id) ON DELETE SET NULL,
    CONSTRAINT pju_jenis_tiang_id_foreign FOREIGN KEY (jenis_tiang_id) REFERENCES jenis_tiang(id) ON DELETE SET NULL
);

-- 5. Tabel riwayat_penanganan
CREATE TABLE IF NOT EXISTS riwayat_penanganan (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pju_id BIGINT UNSIGNED NOT NULL,
    tahun SMALLINT UNSIGNED NOT NULL,
    tanggal DATE NULL,
    jenis_penanganan VARCHAR(255) NOT NULL,
    deskripsi TEXT NULL,
    petugas VARCHAR(255) NULL,
    created_at TIMESTAMP NULL,
    updated_at TIMESTAMP NULL,
    CONSTRAINT riwayat_penanganan_pju_id_foreign FOREIGN KEY (pju_id) REFERENCES pju(id) ON DELETE CASCADE
);
