<?php
// Database migration script to ensure all required columns exist
session_start();
include_once('includes/config.php');

if (strlen($_SESSION["aid"]) == 0) {
    header('location:logout.php');
    exit;
}

$migrations = [];

// 1. Check if totalAmount column exists in orders table
$checkTotalAmount = mysqli_query($con, "SHOW COLUMNS FROM orders LIKE 'totalAmount'");
if (mysqli_num_rows($checkTotalAmount) == 0) {
    // Add totalAmount column
    $addColumn = mysqli_query($con, "ALTER TABLE orders ADD COLUMN totalAmount DECIMAL(10,2) DEFAULT 0 AFTER orderStatus");
    $migrations[] = [
        'name' => 'Add totalAmount to orders table',
        'status' => $addColumn ? 'success' : 'failed',
        'message' => $addColumn ? 'Column added successfully' : 'Failed: ' . mysqli_error($con)
    ];

    // Update existing orders with calculated total
    if ($addColumn) {
        mysqli_query($con, "UPDATE orders o 
            JOIN products p ON o.productId = p.id 
            SET o.totalAmount = (p.productPrice * o.quantity) + p.shippingCharge 
            WHERE o.totalAmount = 0 OR o.totalAmount IS NULL");
        $migrations[] = [
            'name' => 'Calculate totalAmount for existing orders',
            'status' => 'success',
            'message' => 'Existing orders updated with calculated totals'
        ];
    }
} else {
    $migrations[] = [
        'name' => 'totalAmount column check',
        'status' => 'success',
        'message' => 'Column already exists'
    ];
}

// 2. Check if orderNumber column exists
$checkOrderNumber = mysqli_query($con, "SHOW COLUMNS FROM orders LIKE 'orderNumber'");
if (mysqli_num_rows($checkOrderNumber) == 0) {
    $addOrderNum = mysqli_query($con, "ALTER TABLE orders ADD COLUMN orderNumber VARCHAR(50) UNIQUE AFTER id");
    $migrations[] = [
        'name' => 'Add orderNumber to orders table',
        'status' => $addOrderNum ? 'success' : 'failed',
        'message' => $addOrderNum ? 'Column added successfully' : 'Failed: ' . mysqli_error($con)
    ];

    // Generate order numbers for existing orders
    if ($addOrderNum) {
        $orders = mysqli_query($con, "SELECT id FROM orders WHERE orderNumber IS NULL");
        while ($order = mysqli_fetch_assoc($orders)) {
            $orderNum = 'ORD' . str_pad($order['id'], 6, '0', STR_PAD_LEFT);
            mysqli_query($con, "UPDATE orders SET orderNumber = '$orderNum' WHERE id = {$order['id']}");
        }
        $migrations[] = [
            'name' => 'Generate order numbers',
            'status' => 'success',
            'message' => 'Order numbers generated for existing orders'
        ];
    }
} else {
    $migrations[] = [
        'name' => 'orderNumber column check',
        'status' => 'success',
        'message' => 'Column already exists'
    ];
}

// 3. Ensure adminName and adminEmail columns exist in admin table
$checkAdminCols = mysqli_query($con, "SHOW COLUMNS FROM admin");
$cols = [];
while ($col = mysqli_fetch_assoc($checkAdminCols)) {
    $cols[] = $col['Field'];
}

if (!in_array('adminName', $cols)) {
    mysqli_query($con, "ALTER TABLE admin ADD COLUMN adminName VARCHAR(100) AFTER username");
    $migrations[] = [
        'name' => 'Add adminName to admin table',
        'status' => 'success',
        'message' => 'Column added'
    ];
}

if (!in_array('adminEmail', $cols)) {
    mysqli_query($con, "ALTER TABLE admin ADD COLUMN adminEmail VARCHAR(100) AFTER adminName");
    $migrations[] = [
        'name' => 'Add adminEmail to admin table',
        'status' => 'success',
        'message' => 'Column added'
    ];
}

?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="utf-8" />
    <title>Database Migration - Awesome Mall</title>
    <link href="css/styles.css" rel="stylesheet" />
    <link href="css/admin-professional.css" rel="stylesheet" />
    <script src="js/all.min.js"></script>
</head>

<body class="sb-nav-fixed">
    <?php include_once('includes/header.php'); ?>
    <div id="layoutSidenav">
        <?php include_once('includes/sidebar.php'); ?>
        <div id="layoutSidenav_content">
            <main>
                <div class="container-fluid px-4">
                    <div class="page-header mt-4">
                        <div>
                            <h1><i class="fas fa-database me-2"></i> Database Migration</h1>
                            <p>Update database schema for new features</p>
                        </div>
                    </div>

                    <div class="table-card">
                        <div class="card-header">
                            <h5 class="mb-0"><i class="fas fa-tasks me-2"></i>Migration Results</h5>
                        </div>
                        <div class="card-body">
                            <?php if (empty($migrations)) { ?>
                                <div class="alert alert-info">
                                    <i class="fas fa-info-circle me-2"></i>
                                    No migrations needed. Database is up to date!
                                </div>
                            <?php } else { ?>
                                <table class="table">
                                    <thead>
                                        <tr>
                                            <th>Migration</th>
                                            <th>Result</th>
                                            <th>Status</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php foreach ($migrations as $migration) { ?>
                                            <tr>
                                                <td><strong>
                                                        <?php echo $migration['name']; ?>
                                                    </strong></td>
                                                <td>
                                                    <?php echo $migration['message']; ?>
                                                </td>
                                                <td>
                                                    <?php if ($migration['status'] == 'success') { ?>
                                                        <i class="fas fa-check-circle text-success fa-lg"></i>
                                                    <?php } else { ?>
                                                        <i class="fas fa-times-circle text-danger fa-lg"></i>
                                                    <?php } ?>
                                                </td>
                                            </tr>
                                        <?php } ?>
                                    </tbody>
                                </table>

                                <div class="alert alert-success mt-3">
                                    <i class="fas fa-check-circle me-2"></i>
                                    <strong>Migrations completed!</strong> Your database is now ready.
                                </div>
                            <?php } ?>

                            <div class="text-center mt-4">
                                <a href="system-test.php" class="btn btn-primary">
                                    <i class="fas fa-check me-2"></i>Run System Test
                                </a>
                                <a href="dashboard.php" class="btn btn-outline-primary">
                                    <i class="fas fa-home me-2"></i>Back to Dashboard
                                </a>
                            </div>
                        </div>
                    </div>
                </div>
            </main>
            <?php include_once('includes/footer.php'); ?>
        </div>
    </div>
    <script src="js/bootstrap.bundle.min.js"></script>
    <script src="js/scripts.js"></script>
</body>

</html>
