<?php
/**
 * update_db.php - RUN ONCE on your shared hosting
 * This script adds the missing 'method' columns and standardizes labels.
 */
require_once 'config.php';
require_once 'includes/db.php';

try {
    echo "Starting update...<br>";

    // 1. Add method column to transactions table
    try {
        $pdo->exec("ALTER TABLE transactions ADD COLUMN method VARCHAR(50) DEFAULT 'Mobile Money' AFTER amount");
        echo "✅ Added 'method' column to 'transactions' table.<br>";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
            echo "ℹ️ Column 'method' already exists in 'transactions'.<br>";
        } else { throw $e; }
    }

    // 2. Update existing transactions to 'Mobile Money'
    $pdo->exec("UPDATE transactions SET method = 'Mobile Money' WHERE method IS NULL OR method = ''");
    echo "✅ Updated existing transaction records.<br>";

    // 3. Update withdrawals table defaults, strings, and add withdrawal_id
    $pdo->exec("ALTER TABLE withdrawals MODIFY COLUMN method VARCHAR(50) DEFAULT 'Mobile Money'");
    $pdo->exec("UPDATE withdrawals SET method = 'Mobile Money' WHERE method = 'Mobile Transfer'");
    $pdo->exec("UPDATE withdrawals SET method = 'Bank transfer' WHERE method = 'Bank Transfer'");
    
    try {
        $pdo->exec("ALTER TABLE withdrawals ADD COLUMN withdrawal_id VARCHAR(50) DEFAULT NULL AFTER id");
        echo "✅ Added 'withdrawal_id' column to 'withdrawals'.<br>";
        
        // Populate existing records
        $stmt = $pdo->query("SELECT id FROM withdrawals WHERE withdrawal_id IS NULL");
        $count = 0;
        while ($row = $stmt->fetch()) {
            $wid = 'WDR-' . str_pad($row['id'], 6, '0', STR_PAD_LEFT);
            $pdo->prepare("UPDATE withdrawals SET withdrawal_id = ? WHERE id = ?")->execute([$wid, $row['id']]);
            $count++;
        }
        if ($count > 0) echo "✅ Populated $count existing withdrawal IDs.<br>";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
            echo "ℹ️ Column 'withdrawal_id' already exists.<br>";
        } else { throw $e; }
    }
    
    echo "✅ Standardized 'withdrawals' table methods.<br>";

    echo "<br><b>Database update complete!</b> You can now delete this file.";

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

