<?php
include __DIR__ . '/../src/config/koneksi.php';
header('Content-Type: text/plain; charset=utf-8');

echo "Checking bantuan_terakhir column...\n";
$st = $conn->prepare("SELECT COUNT(*) AS c FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='lokasi' AND COLUMN_NAME='bantuan_terakhir'");
$st->execute();
$res = $st->get_result()->fetch_assoc();
$st->close();
if($res && intval($res['c'])>0){
    echo "Column exists.\n";
} else {
    echo "Column missing. Adding...\n";
    if($conn->query("ALTER TABLE lokasi ADD COLUMN bantuan_terakhir DATETIME NULL")){
        echo "Column added.\n";
    } else {
        echo "Failed to add column: " . $conn->error . "\n";
    }
}

// populate bantuan_terakhir from last_assisted_at where appropriate
echo "Populating bantuan_terakhir from last_assisted_at...\n";
if($conn->query("UPDATE lokasi SET bantuan_terakhir = last_assisted_at WHERE bantuan_terakhir IS NULL AND last_assisted_at IS NOT NULL")){
    echo "Populate step finished. Affected rows: " . $conn->affected_rows . "\n";
} else {
    echo "Populate failed: " . $conn->error . "\n";
}

// ensure index exists
echo "Checking index idx_lokasi_assisted...\n";
$st = $conn->prepare("SELECT COUNT(*) AS c FROM information_schema.STATISTICS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='lokasi' AND INDEX_NAME='idx_lokasi_assisted'");
$st->execute();
$res2 = $st->get_result()->fetch_assoc();
$st->close();
if($res2 && intval($res2['c'])>0){
    echo "Index exists.\n";
} else {
    echo "Creating index...\n";
    if($conn->query('CREATE INDEX idx_lokasi_assisted ON lokasi (assisted)')){
        echo "Index created.\n";
    } else {
        echo "Failed to create index: " . $conn->error . "\n";
    }
}

echo "Done.\n";
?>