<?php
$host = "localhost";
$user = "root";
$pass = "";
$db   = "sikompro";

$conn = new mysqli($host, $user, $pass, $db);

if ($conn->connect_error) {
    die("Koneksi gagal: " . $conn->connect_error);
}

if (!function_exists('sikompro_schema_bootstrap')) {
    function sikompro_schema_bootstrap($conn)
    {
        static $done = false;
        if ($done) {
            return;
        }
        $done = true;

        $columns = [];
        $result = mysqli_query($conn, "SHOW COLUMNS FROM komplain");
        while ($result && ($row = mysqli_fetch_assoc($result))) {
            $columns[$row['Field']] = strtolower((string) $row['Type']);
        }

        $requiredColumns = [
            "admin_id INT NULL AFTER id",
            "pelaksana_id INT NULL AFTER admin_id",
            "hasil_pelaksana TEXT NULL AFTER hasil_teknisi",
            "catatan_teknisi TEXT NULL AFTER hasil_pelaksana",
            "catatan_pelaksana TEXT NULL AFTER catatan_teknisi",
            "catatan_admin TEXT NULL AFTER catatan_pelaksana",
            "tanggal_update DATETIME NULL AFTER tanggal",
            "kategori_kerusakan VARCHAR(30) NULL AFTER kerusakan",
            "deskripsi_komplain TEXT NULL AFTER lokasi",
            "wilayah_id INT NULL AFTER teknisi_id",
            "cluster_id INT NULL AFTER wilayah_id",
            "lokasi_detail VARCHAR(150) NULL AFTER cluster_id"
        ];

        foreach ($requiredColumns as $sql) {
            preg_match('/^([a-z_]+)/i', $sql, $matches);
            $columnName = $matches[1];

            if (!isset($columns[$columnName])) {
                mysqli_query($conn, "ALTER TABLE komplain ADD COLUMN {$sql}");
            }
        }

        if (isset($columns['status']) && strpos($columns['status'], 'varchar') !== 0) {
            mysqli_query($conn, "ALTER TABLE komplain MODIFY status VARCHAR(50) NULL");
        }

        if (isset($columns['foto_before']) && strpos($columns['foto_before'], 'text') !== 0) {
            mysqli_query($conn, "ALTER TABLE komplain MODIFY foto_before TEXT NULL");
        }

        if (isset($columns['foto_after']) && strpos($columns['foto_after'], 'text') !== 0) {
            mysqli_query($conn, "ALTER TABLE komplain MODIFY foto_after TEXT NULL");
        }

        $userColumns = [];
        $userResult = mysqli_query($conn, "SHOW COLUMNS FROM users");
        while ($userResult && ($row = mysqli_fetch_assoc($userResult))) {
            $userColumns[$row['Field']] = strtolower((string) $row['Type']);
        }

        if (!isset($userColumns['gaji_harian'])) {
            mysqli_query($conn, "ALTER TABLE users ADD COLUMN gaji_harian DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER role");
            mysqli_query($conn, "UPDATE users SET gaji_harian = CASE role
                WHEN 'admin' THEN 125000
                WHEN 'pelaksana' THEN 100000
                WHEN 'teknisi' THEN 90000
                ELSE 0
            END WHERE gaji_harian = 0");
        }

        if (!isset($userColumns['email'])) {
            mysqli_query($conn, "ALTER TABLE users ADD COLUMN email VARCHAR(150) NULL AFTER username");
        }

        if (!isset($userColumns['no_hp'])) {
            mysqli_query($conn, "ALTER TABLE users ADD COLUMN no_hp VARCHAR(25) NULL AFTER email");
        } else {
            mysqli_query($conn, "ALTER TABLE users MODIFY no_hp VARCHAR(25) NULL DEFAULT NULL");
        }

        if (!isset($userColumns['google_id'])) {
            mysqli_query($conn, "ALTER TABLE users ADD COLUMN google_id VARCHAR(150) NULL AFTER no_hp");
        } else {
            mysqli_query($conn, "ALTER TABLE users MODIFY google_id VARCHAR(150) NULL DEFAULT NULL");
        }

        if (!isset($userColumns['foto_profil'])) {
            mysqli_query($conn, "ALTER TABLE users ADD COLUMN foto_profil VARCHAR(255) NULL AFTER gaji_harian");
        }

        if (!isset($userColumns['gaji_lembur_per_jam'])) {
            mysqli_query($conn, "ALTER TABLE users ADD COLUMN gaji_lembur_per_jam DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER gaji_harian");
            mysqli_query($conn, "UPDATE users SET gaji_lembur_per_jam = ROUND(CASE
                WHEN gaji_harian > 0 THEN gaji_harian / 8
                ELSE 0
            END) WHERE gaji_lembur_per_jam = 0");
        }

        if (!isset($userColumns['gaji_lembur_total'])) {
            mysqli_query($conn, "ALTER TABLE users ADD COLUMN gaji_lembur_total DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER gaji_lembur_per_jam");
        }

        mysqli_query($conn, "CREATE TABLE IF NOT EXISTS absensi (
            id INT AUTO_INCREMENT PRIMARY KEY,
            user_id INT NOT NULL,
            role VARCHAR(20) NULL,
            tanggal DATE NOT NULL,
            jam_masuk DATETIME NULL,
            jam_pulang DATETIME NULL,
            latitude VARCHAR(50) NULL,
            longitude VARCHAR(50) NULL,
            foto VARCHAR(255) NULL,
            status VARCHAR(30) NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_absensi_user_tanggal (user_id, tanggal),
            KEY idx_absensi_tanggal (tanggal),
            KEY idx_absensi_role (role)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        $absensiColumns = [];
        $absensiResult = mysqli_query($conn, "SHOW COLUMNS FROM absensi");
        while ($absensiResult && ($row = mysqli_fetch_assoc($absensiResult))) {
            $absensiColumns[$row['Field']] = strtolower((string) $row['Type']);
        }

        $requiredAbsensiColumns = [
            "role VARCHAR(20) NULL AFTER user_id",
            "jam_masuk DATETIME NULL AFTER tanggal",
            "jam_pulang DATETIME NULL AFTER jam_masuk",
            "latitude VARCHAR(50) NULL AFTER jam_pulang",
            "longitude VARCHAR(50) NULL AFTER latitude",
            "foto VARCHAR(255) NULL AFTER longitude",
            "foto_pulang VARCHAR(255) NULL AFTER foto",
            "jam_lembur_mulai DATETIME NULL AFTER foto_pulang",
            "jam_lembur_selesai DATETIME NULL AFTER jam_lembur_mulai",
            "foto_lembur VARCHAR(255) NULL AFTER jam_lembur_selesai",
            "keterangan_lembur TEXT NULL AFTER foto_lembur",
            "lokasi_nama VARCHAR(255) NULL AFTER longitude",
            "status VARCHAR(30) NULL AFTER foto",
            "created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER status"
        ];

        foreach ($requiredAbsensiColumns as $sql) {
            preg_match('/^([a-z_]+)/i', $sql, $matches);
            $columnName = $matches[1];

            if (!isset($absensiColumns[$columnName])) {
                mysqli_query($conn, "ALTER TABLE absensi ADD COLUMN {$sql}");
            }
        }

        mysqli_query($conn, "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)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        mysqli_query($conn, "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),
            KEY idx_cluster_wilayah (wilayah_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        mysqli_query($conn, "CREATE TABLE IF NOT EXISTS notifikasi (
            id INT AUTO_INCREMENT PRIMARY KEY,
            role_tujuan VARCHAR(20) NOT NULL,
            user_id INT NULL,
            komplain_id INT NULL,
            judul VARCHAR(150) NOT NULL,
            pesan TEXT NOT NULL,
            is_read TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            read_at DATETIME NULL,
            KEY idx_notifikasi_role_read (role_tujuan, is_read),
            KEY idx_notifikasi_created (created_at),
            KEY idx_notifikasi_komplain (komplain_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        foreach (['BSD', 'Bogor', 'Medan', 'Malang'] as $wilayahNama) {
            $wilayahNama = mysqli_real_escape_string($conn, $wilayahNama);
            mysqli_query($conn, "INSERT INTO wilayah (nama)
                SELECT '{$wilayahNama}'
                FROM DUAL
                WHERE NOT EXISTS (
                    SELECT 1 FROM wilayah WHERE nama = '{$wilayahNama}'
                )");
        }

        $clusterSeeds = [
            'BSD' => ['Cluster Vani', 'Cluster Zola', 'Cluster Maple'],
            'Bogor' => ['Cluster Cemara', 'Cluster Anggrek'],
            'Medan' => ['Cluster Melati', 'Cluster Kenanga'],
            'Malang' => ['Cluster Apel', 'Cluster Bromo']
        ];

        foreach ($clusterSeeds as $wilayahNama => $clusters) {
            $wilayahNamaSql = mysqli_real_escape_string($conn, $wilayahNama);
            $wilayahResult = mysqli_query($conn, "SELECT id FROM wilayah WHERE nama = '{$wilayahNamaSql}' LIMIT 1");
            $wilayah = $wilayahResult ? mysqli_fetch_assoc($wilayahResult) : null;

            if (!$wilayah) {
                continue;
            }

            $wilayahId = (int) $wilayah['id'];

            foreach ($clusters as $clusterNama) {
                $clusterNamaSql = mysqli_real_escape_string($conn, $clusterNama);
                mysqli_query($conn, "INSERT INTO cluster (wilayah_id, nama)
                    SELECT '{$wilayahId}', '{$clusterNamaSql}'
                    FROM DUAL
                    WHERE NOT EXISTS (
                        SELECT 1 FROM cluster WHERE wilayah_id = '{$wilayahId}' AND nama = '{$clusterNamaSql}'
                    )");
            }
        }

        mysqli_query($conn, "UPDATE komplain
            SET hasil_teknisi = keterangan
            WHERE (hasil_teknisi IS NULL OR hasil_teknisi = '')
              AND keterangan IS NOT NULL
              AND keterangan <> ''");

        mysqli_query($conn, "UPDATE komplain
            SET hasil_pelaksana = admin_keterangan
            WHERE (hasil_pelaksana IS NULL OR hasil_pelaksana = '')
              AND admin_keterangan IS NOT NULL
              AND admin_keterangan <> ''");

        mysqli_query($conn, "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 = ''");

        mysqli_query($conn, "UPDATE komplain
            SET status = 'selesai'
            WHERE (status IS NULL OR status = '' OR LOWER(status) = 'final_selesai')
              AND (COALESCE(hasil_pelaksana, '') <> '' OR COALESCE(admin_keterangan, '') <> '')");

        mysqli_query($conn, "UPDATE komplain
            SET status = 'selesai_teknisi'
            WHERE (status IS NULL OR status = '')
              AND (COALESCE(hasil_teknisi, '') <> '' OR COALESCE(keterangan, '') <> '' OR COALESCE(foto_after, '') <> '')");

        mysqli_query($conn, "UPDATE komplain
            SET status = 'dikirim_ke_teknisi'
            WHERE (status IS NULL OR status = '')
              AND teknisi_id IS NOT NULL
              AND teknisi_id > 0");

        mysqli_query($conn, "UPDATE komplain
            SET status = 'diproses_pelaksana'
            WHERE LOWER(COALESCE(status, '')) = 'proses'");

        mysqli_query($conn, "UPDATE komplain
            SET status = 'pending_pelaksana'
            WHERE LOWER(COALESCE(status, '')) IN ('pending', 'menunggu')
               OR status IS NULL
               OR status = ''");

        mysqli_query($conn, "UPDATE komplain
            SET tanggal_update = COALESCE(tanggal_update, NOW())");
    }
}

sikompro_schema_bootstrap($conn);
?>
