<?php
require_once 'config.php';
$db = getDB();
$results = [];

$queries = [
    "CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(100) NOT NULL UNIQUE,
        nama_lengkap VARCHAR(200) NOT NULL,
        role ENUM('admin','petugas','pengurus','walikota') NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        aktif TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

    "CREATE TABLE IF NOT EXISTS data_kemiskinan (
        id INT AUTO_INCREMENT PRIMARY KEY,
        nama_kk VARCHAR(200) NOT NULL,
        nik VARCHAR(20) NULL,
        jumlah_kk INT NOT NULL DEFAULT 1,
        alamat TEXT NOT NULL,
        latitude DOUBLE NOT NULL,
        longitude DOUBLE NOT NULL,
        status_bantuan ENUM('Belum','Sudah') NOT NULL DEFAULT 'Belum',
        kategori VARCHAR(50) DEFAULT 'Miskin',
        skor_penghasilan INT DEFAULT 0,
        skor_rumah INT DEFAULT 0,
        skor_makan INT DEFAULT 0,
        tanggal_lahir DATE NULL,
        pendidikan VARCHAR(50) NULL,
        riwayat_penyakit VARCHAR(255) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

    "CREATE TABLE IF NOT EXISTS data_masjid (
        id INT AUTO_INCREMENT PRIMARY KEY,
        nama_masjid VARCHAR(200) NOT NULL,
        jenis ENUM('Masjid','Mushola','Gereja','Pura','Vihara','Klenteng') NOT NULL DEFAULT 'Masjid',
        pic_masjid VARCHAR(200) NOT NULL,
        alamat TEXT NOT NULL,
        radius_m INT NOT NULL DEFAULT 500,
        latitude DOUBLE NOT NULL,
        longitude DOUBLE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

    "CREATE TABLE IF NOT EXISTS laporan_masyarakat (
        id INT AUTO_INCREMENT PRIMARY KEY,
        nama_pelapor VARCHAR(200) NOT NULL,
        tanggal_lahir DATE NULL,
        pendidikan VARCHAR(50) NULL,
        riwayat_penyakit VARCHAR(255) NULL,
        skor_penghasilan INT NULL,
        skor_rumah INT NULL,
        skor_makan INT NULL,
        jumlah_kk INT DEFAULT 1,
        nik VARCHAR(20) NULL,
        no_wa VARCHAR(20) NULL,
        deskripsi TEXT NOT NULL,
        latitude DOUBLE NOT NULL,
        longitude DOUBLE NOT NULL,
        status ENUM('Pending','Diproses','Diverifikasi','Ditolak','Selesai') NOT NULL DEFAULT 'Pending',
        alasan_tolak TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

    "CREATE TABLE IF NOT EXISTS riwayat_bantuan (
        id INT AUTO_INCREMENT PRIMARY KEY,
        id_kemiskinan INT NOT NULL,
        pemberi VARCHAR(200) NOT NULL,
        jenis_bantuan VARCHAR(200) NOT NULL,
        tanggal DATE NOT NULL,
        keterangan TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
];

foreach ($queries as $q) {
    $tableName = '';
    preg_match('/TABLE IF NOT EXISTS (\w+)/', $q, $m);
    if ($m) $tableName = $m[1];
    
    if ($db->query($q)) {
        $results[] = "✅ Tabel '$tableName' berhasil dibuat";
    } else {
        $results[] = "❌ Error '$tableName': " . $db->error;
    }
}

// Insert user demo
$hash = '$2y$10$mhMxyKlOSSTQxfmWXEncfe1AF56CJ8fb.v0h0CYZ.ELQQd8iP5LrG';
$users = [
    ['admin', 'Administrator Sistem', 'admin'],
    ['pengurus', 'Pengurus Rumah Ibadah', 'pengurus'],
    ['walikota', 'Walikota Pontianak', 'walikota'],
];
foreach ($users as $u) {
    $stmt = $db->prepare("INSERT IGNORE INTO users (username, nama_lengkap, role, password_hash) VALUES (?, ?, ?, ?)");
    $stmt->bind_param('ssss', $u[0], $u[1], $u[2], $hash);
    $stmt->execute();
    $results[] = "✅ User '{$u[0]}' (password: password)";
}

header('Content-Type: text/plain');
echo implode("\n", $results);
echo "\n\nSELESAI! Sekarang coba login.";
?>