USE sikompro;

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS email VARCHAR(100) NULL AFTER username;

INSERT INTO users (username, password, role)
SELECT 'pelaksana', MD5('pelaksana'), 'pelaksana'
WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE role = 'pelaksana'
);

ALTER TABLE komplain
    ADD COLUMN IF NOT EXISTS admin_id INT NULL AFTER id,
    ADD COLUMN IF NOT EXISTS pelaksana_id INT NULL AFTER admin_id,
    ADD COLUMN IF NOT EXISTS teknisi_id INT NULL AFTER pelaksana_id,
    ADD COLUMN IF NOT EXISTS wilayah_id INT NULL AFTER teknisi_id,
    ADD COLUMN IF NOT EXISTS cluster_id INT NULL AFTER wilayah_id,
    ADD COLUMN IF NOT EXISTS lokasi_detail VARCHAR(150) NULL AFTER cluster_id,
    ADD COLUMN IF NOT EXISTS deskripsi_komplain TEXT NULL AFTER lokasi,
    ADD COLUMN IF NOT EXISTS kategori_kerusakan VARCHAR(30) NULL AFTER kerusakan,
    ADD COLUMN IF NOT EXISTS pelaksana_keterangan TEXT NULL AFTER foto_after,
    ADD COLUMN IF NOT EXISTS hasil_teknisi TEXT NULL AFTER pelaksana_keterangan,
    ADD COLUMN IF NOT EXISTS hasil_pelaksana TEXT NULL AFTER hasil_teknisi,
    ADD COLUMN IF NOT EXISTS catatan_teknisi TEXT NULL AFTER hasil_pelaksana,
    ADD COLUMN IF NOT EXISTS catatan_pelaksana TEXT NULL AFTER catatan_teknisi,
    ADD COLUMN IF NOT EXISTS catatan_admin TEXT NULL AFTER catatan_pelaksana,
    ADD COLUMN IF NOT EXISTS keterangan TEXT NULL AFTER catatan_admin,
    ADD COLUMN IF NOT EXISTS admin_keterangan TEXT NULL AFTER keterangan,
    ADD COLUMN IF NOT EXISTS tanggal_update DATETIME NULL AFTER tanggal;

CREATE TABLE IF NOT EXISTS wilayah (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_wilayah_nama (nama)
);

CREATE TABLE IF NOT EXISTS cluster (
    id INT AUTO_INCREMENT PRIMARY KEY,
    wilayah_id INT NOT NULL,
    nama VARCHAR(100) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_cluster_wilayah (wilayah_id, nama)
);

UPDATE komplain
SET status = 'pending_pelaksana'
WHERE LOWER(status) IN ('pending');

UPDATE komplain
SET status = 'diproses_pelaksana'
WHERE LOWER(status) IN ('proses');

UPDATE komplain
SET status = 'selesai'
WHERE LOWER(status) IN ('final_selesai');

UPDATE komplain
SET status = 'selesai_teknisi'
WHERE LOWER(status) IN ('menunggu_pelaksana');

UPDATE komplain
SET status = 'dikirim_ke_teknisi'
WHERE LOWER(status) = 'menunggu' AND teknisi_id IS NOT NULL;

UPDATE komplain
SET status = 'pending_pelaksana'
WHERE LOWER(status) = 'menunggu' AND (teknisi_id IS NULL OR teknisi_id = 0);

UPDATE komplain
SET hasil_teknisi = keterangan
WHERE (hasil_teknisi IS NULL OR hasil_teknisi = '')
  AND keterangan IS NOT NULL
  AND keterangan <> '';

UPDATE komplain
SET hasil_pelaksana = admin_keterangan
WHERE (hasil_pelaksana IS NULL OR hasil_pelaksana = '')
  AND admin_keterangan IS NOT NULL
  AND admin_keterangan <> '';

UPDATE komplain
SET kategori_kerusakan = CASE
    WHEN kerusakan BETWEEN 1 AND 33 THEN 'Rusak Ringan'
    WHEN kerusakan BETWEEN 34 AND 66 THEN 'Rusak Sedang'
    WHEN kerusakan BETWEEN 67 AND 100 THEN 'Rusak Parah'
    ELSE COALESCE(kategori_kerusakan, 'Belum Dinilai')
END
WHERE kategori_kerusakan IS NULL OR kategori_kerusakan = '';

UPDATE komplain
SET tanggal_update = NOW()
WHERE tanggal_update IS NULL;

CREATE TABLE IF NOT EXISTS log_aktivitas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    komplain_id INT NOT NULL,
    user_type VARCHAR(20) NOT NULL,
    user_id INT NOT NULL,
    aktivitas VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
