<?php
/**
 * Database Migration: Add Idempotency Support for Paystack Payments
 * This fixes the critical vulnerability where callbacks can be replayed infinitely
 * 
 * Run this file ONCE to update the database schema
 */

require_once __DIR__ . '/../../config.php';

header('Content-Type: text/html; charset=utf-8');
echo "<!DOCTYPE html><html><head><title>Paystack Idempotency Fix</title>";
echo "<style>body{font-family:Arial,sans-serif;max-width:800px;margin:50px auto;padding:20px;}";
echo ".success{color:green;background:#e8f5e9;padding:10px;border-radius:4px;margin:10px 0;}";
echo ".error{color:red;background:#ffebee;padding:10px;border-radius:4px;margin:10px 0;}";
echo ".warning{color:orange;background:#fff3e0;padding:10px;border-radius:4px;margin:10px 0;}";
echo "</style></head><body>";

echo "<h1>🔒 Paystack Idempotency Fix - Database Migration</h1>";
echo "<p>This will add idempotency protection to prevent duplicate payment credits.</p>";
echo "<hr>";

$errors = [];
$warnings = [];
$success = [];

// Check if columns already exist
$checkQuery = "SHOW COLUMNS FROM orders_payments LIKE 'payment_reference'";
$result = mysqli_query($conn, $checkQuery);

if (mysqli_num_rows($result) > 0) {
    $warnings[] = "Column 'payment_reference' already exists. Skipping creation.";
} else {
    // Add payment_reference column with UNIQUE constraint
    $sql1 = "ALTER TABLE orders_payments 
             ADD COLUMN payment_reference VARCHAR(100) NULL UNIQUE AFTER payment_status";
    
    if (mysqli_query($conn, $sql1)) {
        $success[] = "✓ Added 'payment_reference' column with UNIQUE constraint";
    } else {
        $errors[] = "Failed to add payment_reference: " . mysqli_error($conn);
    }
}

// Check and add payment_type column
$checkQuery2 = "SHOW COLUMNS FROM orders_payments LIKE 'payment_type'";
$result2 = mysqli_query($conn, $checkQuery2);

if (mysqli_num_rows($result2) > 0) {
    $warnings[] = "Column 'payment_type' already exists. Skipping creation.";
} else {
    $sql2 = "ALTER TABLE orders_payments 
             ADD COLUMN payment_type ENUM('paystack', 'manual', 'bank_transfer') DEFAULT 'manual' AFTER payment_reference";
    
    if (mysqli_query($conn, $sql2)) {
        $success[] = "✓ Added 'payment_type' column";
    } else {
        $errors[] = "Failed to add payment_type: " . mysqli_error($conn);
    }
}

// Check and add external_transaction_id column
$checkQuery3 = "SHOW COLUMNS FROM orders_payments LIKE 'external_transaction_id'";
$result3 = mysqli_query($conn, $checkQuery3);

if (mysqli_num_rows($result3) > 0) {
    $warnings[] = "Column 'external_transaction_id' already exists. Skipping creation.";
} else {
    $sql3 = "ALTER TABLE orders_payments 
             ADD COLUMN external_transaction_id VARCHAR(100) NULL AFTER payment_type";
    
    if (mysqli_query($conn, $sql3)) {
        $success[] = "✓ Added 'external_transaction_id' column";
    } else {
        $errors[] = "Failed to add external_transaction_id: " . mysqli_error($conn);
    }
}

// Check and add raw_response_data column
$checkQuery4 = "SHOW COLUMNS FROM orders_payments LIKE 'raw_response_data'";
$result4 = mysqli_query($conn, $checkQuery4);

if (mysqli_num_rows($result4) > 0) {
    $warnings[] = "Column 'raw_response_data' already exists. Skipping creation.";
} else {
    $sql4 = "ALTER TABLE orders_payments 
             ADD COLUMN raw_response_data TEXT NULL AFTER external_transaction_id";
    
    if (mysqli_query($conn, $sql4)) {
        $success[] = "✓ Added 'raw_response_data' column for audit trail";
    } else {
        $errors[] = "Failed to add raw_response_data: " . mysqli_error($conn);
    }
}

// Check and add payment_date column if not exists
$checkQuery5 = "SHOW COLUMNS FROM orders_payments LIKE 'payment_date'";
$result5 = mysqli_query($conn, $checkQuery5);

if (mysqli_num_rows($result5) > 0) {
    $warnings[] = "Column 'payment_date' already exists. Skipping creation.";
} else {
    $sql5 = "ALTER TABLE orders_payments 
             ADD COLUMN payment_date TIMESTAMP NULL DEFAULT NULL AFTER raw_response_data";
    
    if (mysqli_query($conn, $sql5)) {
        $success[] = "✓ Added 'payment_date' column";
    } else {
        $errors[] = "Failed to add payment_date: " . mysqli_error($conn);
    }
}

// Display results
if (!empty($success)) {
    echo "<div class='success'><h3>✅ Success:</h3><ul>";
    foreach ($success as $msg) {
        echo "<li>$msg</li>";
    }
    echo "</ul></div>";
}

if (!empty($warnings)) {
    echo "<div class='warning'><h3>⚠️ Warnings:</h3><ul>";
    foreach ($warnings as $msg) {
        echo "<li>$msg</li>";
    }
    echo "</ul></div>";
}

if (!empty($errors)) {
    echo "<div class='error'><h3>❌ Errors:</h3><ul>";
    foreach ($errors as $msg) {
        echo "<li>$msg</li>";
    }
    echo "</ul></div>";
} else {
    echo "<div class='success'>";
    echo "<h3>🎉 Migration Completed Successfully!</h3>";
    echo "<p>The database has been updated with idempotency protection.</p>";
    echo "<p><strong>Next Steps:</strong></p>";
    echo "<ol>";
    echo "<li>The paystack_callback.php file has been updated with duplicate prevention</li>";
    echo "<li>Test the payment flow to ensure everything works correctly</li>";
    echo "<li>Delete this migration file after confirming everything works</li>";
    echo "</ol>";
    echo "</div>";
}

// Show current table structure
echo "<hr><h3>📋 Current orders_payments Table Structure:</h3>";
echo "<pre style='background:#f5f5f5;padding:15px;border-radius:4px;overflow:auto;'>";
$showColumns = mysqli_query($conn, "SHOW COLUMNS FROM orders_payments");
while ($col = mysqli_fetch_assoc($showColumns)) {
    echo sprintf("%-30s %-30s %-10s %-10s\n", 
                 $col['Field'], 
                 $col['Type'], 
                 $col['Null'], 
                 $col['Key'] ? "[$col[Key]]" : "");
}
echo "</pre>";

echo "</body></html>";
?>


