<?php
include "db_config.php";

// Step 1: Update ENUM to include walikota
$sql1 = "ALTER TABLE users MODIFY COLUMN role ENUM('admin', 'operator', 'walikota') NOT NULL DEFAULT 'operator'";
$r1 = $conn->query($sql1);
echo "1. Update ENUM: " . ($r1 ? "OK" : "GAGAL - " . $conn->error) . "\n";

// Step 2: Add kemiskinan columns (ignore if already exists)
$sql2 = "ALTER TABLE kemiskinan 
  ADD COLUMN IF NOT EXISTS tanggal_lahir DATE NULL AFTER nama_kk,
  ADD COLUMN IF NOT EXISTS pendidikan VARCHAR(100) NULL AFTER tanggal_lahir,
  ADD COLUMN IF NOT EXISTS riwayat_penyakit TEXT NULL AFTER pendidikan";
$r2 = $conn->query($sql2);
echo "2. Kolom kemiskinan: " . ($r2 ? "OK" : "GAGAL - " . $conn->error) . "\n";

// Step 3: Create laporan_masyarakat table
$sql3 = "CREATE TABLE IF NOT EXISTS laporan_masyarakat (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama_pelapor VARCHAR(150) NOT NULL,
  kontak VARCHAR(50) NULL,
  deskripsi_laporan TEXT NOT NULL,
  lat DECIMAL(10,7) NOT NULL DEFAULT 0,
  lng DECIMAL(10,7) NOT NULL DEFAULT 0,
  foto VARCHAR(255) NULL,
  status ENUM('menunggu', 'diproses', 'selesai') NOT NULL DEFAULT 'menunggu',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
$r3 = $conn->query($sql3);
echo "3. Tabel laporan_masyarakat: " . ($r3 ? "OK" : "GAGAL - " . $conn->error) . "\n";

// Step 4: Create histori_bantuan table
$sql4 = "CREATE TABLE IF NOT EXISTS histori_bantuan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  kemiskinan_id INT NOT NULL,
  jenis_bantuan VARCHAR(150) NOT NULL,
  tanggal DATE NOT NULL,
  keterangan TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (kemiskinan_id) REFERENCES kemiskinan(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
$r4 = $conn->query($sql4);
echo "4. Tabel histori_bantuan: " . ($r4 ? "OK" : "GAGAL - " . $conn->error) . "\n";

// Step 5: Create walikota account
$pass = password_hash("walikota123", PASSWORD_BCRYPT);
$sql5 = "INSERT IGNORE INTO users (username, password, nama_lengkap, role) VALUES ('walikota', '$pass', 'Walikota Pontianak', 'walikota')";
$r5 = $conn->query($sql5);
echo "5. Akun walikota: " . ($r5 ? "OK (affected: " . $conn->affected_rows . ")" : "GAGAL - " . $conn->error) . "\n";

echo "\nSelesai! Silakan login dengan username: walikota | password: walikota123\n";
?>