<?php
/**
 * Migration script to add balance_before column to balance_history table
 * This allows proper tracking of balance before and after each transaction
 */

require_once 'config.php';

echo "Adding balance_before column to balance_history table...\n";

// Check if column already exists
$checkStmt = $conn->query("SHOW COLUMNS FROM balance_history LIKE 'balance_before'");
if ($checkStmt && $checkStmt->num_rows > 0) {
    echo "Column 'balance_before' already exists. Skipping.\n";
    $checkStmt->close();
    exit(0);
}
if ($checkStmt) $checkStmt->close();

// Add balance_before column
$sql = "ALTER TABLE balance_history 
        ADD COLUMN balance_before DECIMAL(10,2) NULL AFTER change_amount";

if ($conn->query($sql)) {
    echo "✓ Successfully added balance_before column.\n";
    
    // Backfill existing records by calculating balance_before from balance_after and change_amount
    echo "Backfilling existing records...\n";
    $backfillSql = "UPDATE balance_history 
                    SET balance_before = (balance_after - change_amount) 
                    WHERE balance_before IS NULL";
    
    if ($conn->query($backfillSql)) {
        $affected = $conn->affected_rows;
        echo "✓ Backfilled $affected records with calculated balance_before values.\n";
    } else {
        echo "✗ Error backfilling records: " . $conn->error . "\n";
    }
} else {
    echo "✗ Error adding column: " . $conn->error . "\n";
    exit(1);
}

echo "\nMigration completed successfully!\n";


























