<?php
include "config/db.php";

// 1. Add column to order_items if it doesn't exist
$checkCol = "SHOW COLUMNS FROM order_items LIKE 'security_deposit'";
$res = mysqli_query($conn, $checkCol);
if (mysqli_num_rows($res) == 0) {
    $sql = "ALTER TABLE order_items ADD COLUMN security_deposit DECIMAL(10,2) DEFAULT NULL AFTER price";
    if (mysqli_query($conn, $sql)) {
        echo "Column security_deposit added to order_items.\n";
    } else {
        die("Error adding column: " . mysqli_error($conn));
    }
} else {
    echo "Column security_deposit already exists.\n";
}

// 2. Backfill existing orders from products table
$backfillSql = "UPDATE order_items oi 
                JOIN products p ON oi.product_id = p.id 
                SET oi.security_deposit = p.security_deposit 
                WHERE oi.security_deposit IS NULL";
if (mysqli_query($conn, $backfillSql)) {
    echo "Backfilled " . mysqli_affected_rows($conn) . " order items from current product catalog.\n";
} else {
    echo "Error backfilling: " . mysqli_error($conn) . "\n";
}
echo "Migration complete.\n";
?>
