<?php
// Aktifkan Error Reporting untuk Diagnosa (Mencegah Blank White Page)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Polyfill fungsi PHP 8 (str_contains, str_starts_with, str_ends_with) untuk Hosting PHP 7.x
if (!function_exists('str_contains')) {
    function str_contains($haystack, $needle) {
        return $needle === '' || mb_strpos($haystack, $needle) !== false;
    }
}
if (!function_exists('str_starts_with')) {
    function str_starts_with($haystack, $needle) {
        return strncmp($haystack, $needle, strlen($needle)) === 0;
    }
}
if (!function_exists('str_ends_with')) {
    function str_ends_with($haystack, $needle) {
        return $needle === '' || substr_compare($haystack, $needle, -strlen($needle)) === 0;
    }
}

// Configuration Database
// Untuk Hosting (cPanel/DirectAdmin/Hostinger/dll), isi sesuai nama database & user di hosting Anda:
$db_host = 'localhost';
$db_user = 'root';                 // Ganti dengan DB User hosting (contoh: monitord_user)
$db_pass = '';                     // Ganti dengan DB Password hosting
$db_name = 'monitordaerah13';       // Ganti dengan Nama Database hosting (contoh: monitord_db)


try {
    // 1. Coba koneksi langsung ke database yang sudah ditentukan (Standar Hosting)
    try {
        $pdo = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", $db_user, $db_pass, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);
    } catch (PDOException $e) {
        // 2. Jika database belum ada (Lokal XAMPP/Laragon), coba buat database otomatis
        $pdo_root = new PDO("mysql:host=$db_host;charset=utf8mb4", $db_user, $db_pass, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        ]);
        $pdo_root->exec("CREATE DATABASE IF NOT EXISTS `$db_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci");
        $pdo = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", $db_user, $db_pass, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);
    }

    // -------------------------------------------------------------
    // SKEMA TABEL & MIGRASI OTOMATIS
    // -------------------------------------------------------------

    // Tabel Users
    $pdo->exec("CREATE TABLE IF NOT EXISTS `users` (
        `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `username` varchar(100) NOT NULL UNIQUE,
        `password` varchar(255) NOT NULL,
        `role` enum('superadmin','admin_instansi','pimpinan') NOT NULL DEFAULT 'admin_instansi',
        `instansi_id` int(11) DEFAULT NULL,
        `email` varchar(150) DEFAULT NULL UNIQUE,
        `google_id` varchar(100) DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT current_timestamp()
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Tabel Instansi
    $pdo->exec("CREATE TABLE IF NOT EXISTS `instansi` (
        `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `nama_instansi` varchar(100) NOT NULL,
        `logo_path` varchar(255) DEFAULT NULL,
        `keyword_global` text,
        `api_key_ai` text,
        `status` enum('aktif','suspend') DEFAULT 'aktif',
        `created_at` timestamp DEFAULT current_timestamp()
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Tabel Instansi Entities (Weighted Scoring)
    $pdo->exec("CREATE TABLE IF NOT EXISTS `instansi_entities` (
        `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `instansi_id` int(11) NOT NULL,
        `entity` varchar(150) NOT NULL,
        `type` enum('target','conflict') NOT NULL,
        `score` int(11) NOT NULL,
        `created_at` timestamp NOT NULL DEFAULT current_timestamp()
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Tabel Media
    $pdo->exec("CREATE TABLE IF NOT EXISTS `media` (
        `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `instansi_id` int(11) DEFAULT 1,
        `nama` varchar(150) NOT NULL,
        `url` varchar(255) DEFAULT NULL,
        `tier` enum('Tier 1','Tier 2','Tier 3') DEFAULT 'Tier 3',
        `html_thumbnail` text DEFAULT NULL,
        `kategori` varchar(100) DEFAULT 'umum',
        `last_scraped_at` datetime DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT current_timestamp()
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Tabel Berita
    $pdo->exec("CREATE TABLE IF NOT EXISTS `berita` (
        `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `instansi_id` int(11) DEFAULT 1,
        `skpd_id` int(11) DEFAULT NULL,
        `guid` varchar(255) NOT NULL,
        `sumber` varchar(100) NOT NULL,
        `judul` varchar(255) NOT NULL,
        `link` text NOT NULL,
        `tanggal` datetime NOT NULL,
        `cuplikan` text DEFAULT NULL,
        `matched_keywords` text DEFAULT NULL,
        `thumbnail` text DEFAULT NULL,
        `sentimen` enum('positif','negatif','netral') DEFAULT 'netral',
        `urgensi` enum('rendah','sedang','tinggi') DEFAULT 'rendah',
        `is_scanned` tinyint(1) DEFAULT 1,
        `kategori_berita` varchar(100) DEFAULT NULL,
        `ringkasan` text DEFAULT NULL,
        `created_at` timestamp NOT NULL DEFAULT current_timestamp()
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Tabel SKPD
    $pdo->exec("CREATE TABLE IF NOT EXISTS `skpd` (
        `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `instansi_id` int(11) DEFAULT 1,
        `nama_skpd` varchar(150) NOT NULL,
        `singkatan` varchar(50) DEFAULT NULL,
        `keywords` text DEFAULT NULL,
        `kontak_wa` varchar(50) DEFAULT NULL,
        `email` varchar(100) DEFAULT NULL,
        `status` enum('aktif','nonaktif') DEFAULT 'aktif',
        `created_at` timestamp NOT NULL DEFAULT current_timestamp()
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // Tabel Laporan SP4N LAPOR
    $pdo->exec("CREATE TABLE IF NOT EXISTS `lapor` (
        `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `instansi_id` int(11) DEFAULT 1,
        `skpd_id` int(11) DEFAULT NULL,
        `tracking_id` varchar(100) NOT NULL,
        `tanggal_masuk` datetime DEFAULT NULL,
        `nama_pelapor` varchar(150) DEFAULT NULL,
        `klasifikasi` varchar(100) DEFAULT NULL,
        `kategori` varchar(150) DEFAULT NULL,
        `judul` varchar(255) NOT NULL,
        `isi_laporan` text DEFAULT NULL,
        `instansi_terdisposisi` varchar(255) DEFAULT NULL,
        `status_laporan` varchar(100) DEFAULT NULL,
        `tindak_lanjut` text DEFAULT NULL,
        `sumber_laporan` varchar(100) DEFAULT 'SP4N LAPOR',
        `created_at` timestamp NOT NULL DEFAULT current_timestamp()
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

    // -------------------------------------------------------------
    // ALTER TABLE (PEMERIKSAAN KOLOM BARU)
    // -------------------------------------------------------------
    $alter_queries = [
        "ALTER TABLE `instansi` ADD COLUMN `logo_path` varchar(255) DEFAULT NULL",
        "ALTER TABLE `media` ADD COLUMN `html_thumbnail` text DEFAULT NULL",
        "ALTER TABLE `media` ADD COLUMN `kategori` varchar(100) DEFAULT 'umum'",
        "ALTER TABLE `media` ADD COLUMN `last_scraped_at` datetime DEFAULT NULL",
        "ALTER TABLE `berita` ADD COLUMN `kategori_berita` varchar(100) DEFAULT NULL",
        "ALTER TABLE `berita` ADD COLUMN `skpd_id` int(11) DEFAULT NULL",
        "ALTER TABLE `users` ADD COLUMN `email` varchar(150) UNIQUE DEFAULT NULL",
        "ALTER TABLE `users` ADD COLUMN `google_id` varchar(100) DEFAULT NULL",
        "ALTER TABLE `users` MODIFY COLUMN `role` ENUM('superadmin', 'admin_instansi', 'pimpinan') NOT NULL DEFAULT 'admin_instansi'",
        // Indexes untuk Optimasi Query & Mencegah Disk Full (Temporary Tables)
        "ALTER TABLE `berita` ADD INDEX `idx_instansi` (`instansi_id`)",
        "ALTER TABLE `berita` ADD INDEX `idx_tanggal` (`tanggal`)",
        "ALTER TABLE `berita` ADD INDEX `idx_sumber` (`sumber`)",
        "ALTER TABLE `media` ADD INDEX `idx_instansi` (`instansi_id`)",
        "ALTER TABLE `media` ADD INDEX `idx_nama` (`nama`)"
    ];

    foreach ($alter_queries as $aq) {
        try {
            $pdo->exec($aq);
        } catch (PDOException $ex) {
            // Abaikan error jika kolom sudah ada
        }
    }

    // -------------------------------------------------------------
    // SEEDER DATA AWAL
    // -------------------------------------------------------------
    $cek_inst = $pdo->query("SELECT count(*) FROM instansi")->fetchColumn();
    if ($cek_inst == 0) {
        $pdo->exec("INSERT INTO instansi (nama_instansi, keyword_global, api_key_ai) VALUES ('Pemko Banjarbaru', 'Pemko Banjarbaru,Banjarbaru', '')");
        $pdo->exec("INSERT INTO media (instansi_id, nama, url, tier) VALUES (1, 'Antara Kalsel', 'https://kalsel.antaranews.com/rss/terkini.xml', 'Tier 1')");
    }

    $cek_user = $pdo->query("SELECT count(*) FROM users")->fetchColumn();
    if ($cek_user == 0) {
        $pass_hash = password_hash('admin123', PASSWORD_BCRYPT);
        $pdo->exec("INSERT INTO users (username, password, role, instansi_id) VALUES ('admin', '$pass_hash', 'superadmin', 1)");
    }

} catch (PDOException $e) {
    http_response_code(500);
    die("<div style='font-family:sans-serif; padding:30px; max-width:600px; margin:50px auto; background:#fff5f5; border:1px solid #feb2b2; border-radius:12px; box-shadow:0 4px 12px rgba(0,0,0,0.1);'>
        <h3 style='color:#c53030; margin-top:0;'>⚠️ Gagal Terhubung ke Database MySQL</h3>
        <p style='color:#4a5568;'>Aplikasi tidak dapat terhubung ke database. Silakan pastikan pengaturan database di file <code>db.php</code> sudah sesuai dengan hosting Anda.</p>
        <div style='background:#edf2f7; padding:15px; border-radius:8px; font-family:monospace; font-size:13px; color:#2d3748; word-break:break-all;'>
            <strong>Pesan Error:</strong> " . htmlspecialchars($e->getMessage()) . "
        </div>
        <hr style='border:0; border-top:1px solid #e2e8f0; margin:20px 0;'>
        <p style='font-size:13px; color:#718096;'><strong>Langkah Perbaikan di Hosting (cPanel/Hostinger/dll):</strong><br>
        1. Buka File Manager & edit file <code>db.php</code>.<br>
        2. Ubah <code>\$db_user</code>, <code>\$db_pass</code>, dan <code>\$db_name</code> sesuai database yang dibuat di cPanel/Hosting.<br>
        3. Simpan dan reload halaman ini.</p>
    </div>");
}
