<?php
$conn = new mysqli('localhost', 'zemaraim', '8wwxk]-X3Kx]CJ]5', 'zemaraim_interior');
if ($conn->connect_error) {
    echo 'Error: ' . $conn->connect_error;
    exit;
}

echo "<h2>Fixing Orders Table</h2>";

// Check if status column exists
$result = $conn->query("SHOW COLUMNS FROM orders LIKE 'status'");

if ($result->num_rows == 0) {
    echo "<p>Status column not found. Adding it...</p>";
    
    $sql = "ALTER TABLE orders ADD COLUMN status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending' AFTER id";
    
    if ($conn->query($sql)) {
        echo "<p style='color: green;'><strong>✓ Status column added successfully!</strong></p>";
    } else {
        echo "<p style='color: red;'><strong>✗ Error adding status column: " . $conn->error . "</strong></p>";
    }
} else {
    echo "<p style='color: green;'><strong>✓ Status column already exists</strong></p>";
}

// Check if total column exists
$result = $conn->query("SHOW COLUMNS FROM orders LIKE 'total'");
if ($result->num_rows == 0) {
    echo "<p>Total column not found. Adding it...</p>";
    
    $sql = "ALTER TABLE orders ADD COLUMN total DECIMAL(10, 2) DEFAULT 0 AFTER status";
    
    if ($conn->query($sql)) {
        echo "<p style='color: green;'><strong>✓ Total column added successfully!</strong></p>";
    } else {
        echo "<p style='color: red;'><strong>✗ Error adding total column: " . $conn->error . "</strong></p>";
    }
} else {
    echo "<p style='color: green;'><strong>✓ Total column already exists</strong></p>";
}

// Show current table structure
echo "<h2>Updated Orders Table Structure</h2>";
$result = $conn->query("DESCRIBE orders");
echo "<table border='1' style='border-collapse: collapse; margin-top: 10px;'>";
echo "<tr style='background: #333; color: white;'><th>Field</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th><th>Extra</th></tr>";
while($row = $result->fetch_assoc()) {
    $highlight = in_array($row['Field'], ['status', 'total']) ? "style='background: #d4edda;'" : "";
    echo "<tr $highlight>";
    echo "<td>" . $row['Field'] . "</td>";
    echo "<td>" . $row['Type'] . "</td>";
    echo "<td>" . $row['Null'] . "</td>";
    echo "<td>" . $row['Key'] . "</td>";
    echo "<td>" . ($row['Default'] ?? 'NULL') . "</td>";
    echo "<td>" . $row['Extra'] . "</td>";
    echo "</tr>";
}
echo "</table>";

echo "<h2>Sample Data</h2>";
$result = $conn->query("SELECT id, status, total FROM orders LIMIT 5");
if($result->num_rows > 0) {
    echo "<table border='1' style='border-collapse: collapse; margin-top: 10px;'>";
    echo "<tr style='background: #333; color: white;'><th>Order ID</th><th>Status</th><th>Total</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td><strong>" . $row['status'] . "</strong></td>";
        echo "<td>₹" . number_format($row['total'], 2) . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} else {
    echo "<p>No orders found in database</p>";
}

echo "<p style='margin-top: 20px;'><a href='admin-dashboard.php' class='btn' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px;'>Go to Admin Dashboard</a></p>";

$conn->close();
?>
