<?php
// update_voter_centers.php

// 1. Database Connection
$host = "localhost";
$db_name = "jessupba_voter";
$username = "jessupba_voter"; // Change to your DB user
$password = "voter@1234";     // Change to your DB pass

try {
    $conn = new PDO("mysql:host=" . $host . ";dbname=" . $db_name, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $conn->exec("SET sql_mode = ''"); 
    $conn->exec("set names utf8mb4");
} catch(PDOException $exception) {
    die("Connection error: " . $exception->getMessage());
}

// CONFIGURATION: Check your table name. 
// In previous steps we called it 'people', here you called it 'voters'.
// Change this variable to match your actual database table name.
$votersTable = 'voters'; 

echo "<h3>Starting Update Process...</h3>";
flush();

try {
    // ---------------------------------------------------------
    // STEP 1: Add Columns if they don't exist
    // ---------------------------------------------------------
    echo "Checking columns...<br>";
    
    $cols = [
        "Ward_No TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL;",
        "Area_Name TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL;",
        "Center_Id VARCHAR(150) DEFAULT NULL"
    ];

    foreach ($cols as $colDef) {
        try {
            // Attempt to add column. If it exists, it throws an error which we catch and ignore.
            $colName = explode(' ', $colDef)[0];
            $sql = "ALTER TABLE $votersTable ADD COLUMN $colDef";
            $conn->exec($sql);
            echo "Added column: $colName<br>";
        } catch (PDOException $e) {
            // Column likely exists, continue
        }
    }

    // ---------------------------------------------------------
    // STEP 2: Add Indexes for Speed (CRUCIAL for large datasets)
    // ---------------------------------------------------------
    echo "Optimizing tables (Adding Indexes)...<br>";
    try {
        $conn->exec("ALTER TABLE $votersTable ADD INDEX idx_pdf_serial (Pdf_Information_Id, Serial_Int)");
        $conn->exec("ALTER TABLE vote_centers ADD INDEX idx_pdf_range (pdf_id, serial_start, serial_end)");
    } catch (PDOException $e) {
        // Indexes likely exist
    }

    // ---------------------------------------------------------
    // STEP 3: Execute The Bulk Update (SQL JOIN)
    // ---------------------------------------------------------
    echo "Updating voter records. This may take a while depending on data size...<br>";
    flush();

    $sql = "
        UPDATE $votersTable v
        JOIN vote_centers c 
            ON v.Pdf_Information_Id = c.pdf_id
            AND v.Serial_Int >= c.serial_start 
            AND v.Serial_Int <= c.serial_end
        SET 
            v.Center_Id = c.id,
            v.Ward_No = c.ward,
            v.Area_Name = c.area_name
        WHERE 
            v.Center_Id IS NULL OR v.Center_Id = 0
    ";

    $stmt = $conn->prepare($sql);
    $stmt->execute();

    $count = $stmt->rowCount();

    echo "<h2 style='color:green'>Success!</h2>";
    echo "<strong>$count</strong> voter records were updated with Ward, Area, and Center info.<br>";

} catch (PDOException $e) {
    echo "<h2 style='color:red'>Error:</h2>";
    echo $e->getMessage();
}
?>