based on this inf databass (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 06, 2024 at 04:20 PM
-- Server version: 10.11.9-MariaDB
-- PHP Version: 7.2.34

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `u406552082_kaakalia`
--

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `contact` varchar(50) DEFAULT NULL,
  `delivery_address` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `status` enum('Pending','In Progress','Completed','Delivered') DEFAULT 'Pending',
  `order_date` datetime DEFAULT current_timestamp(),
  `delivery_datetime` datetime DEFAULT NULL,
  `delivery_date` date DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `packaging_preferences` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `order_items`
--

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `packaging_preferences` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `size` varchar(50) DEFAULT NULL,
  `price` decimal(10,2) NOT NULL,
  `category` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','staff') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `customer_id` (`customer_id`);

--
-- Indexes for table `order_items`
--
ALTER TABLE `order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_id` (`order_id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `orders`
--
ALTER TABLE `orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `order_items`
--
ALTER TABLE `order_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`);

--
-- Constraints for table `order_items`
--
ALTER TABLE `order_items`
  ADD CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `order_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;) I want to add delete record in the codes (<?php
// public/orders/view_orders.php

// Enable error reporting for debugging (disable in production)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Include authentication and database connection
require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../includes/db_connect.php';

// Handle status update
if (isset($_GET['action']) && $_GET['action'] === 'update_status') {
    $order_id = isset($_GET['order_id']) ? intval($_GET['order_id']) : 0;
    $new_status = isset($_GET['new_status']) ? trim($_GET['new_status']) : '';

    // Validate new_status
    $valid_statuses = ['Pending', 'In Progress', 'Completed', 'Delivered'];
    if ($order_id > 0 && in_array($new_status, $valid_statuses)) {
        // Optional: If setting to 'Delivered', ensure delivery_datetime is set
        if ($new_status === 'Delivered') {
            $check_sql = "SELECT delivery_datetime FROM orders WHERE id = ?";
            $check_stmt = $conn->prepare($check_sql);
            $check_stmt->bind_param("i", $order_id);
            $check_stmt->execute();
            $check_result = $check_stmt->get_result();
            if ($check_result->num_rows == 1) {
                $order = $check_result->fetch_assoc();
                if (empty($order['delivery_datetime'])) {
                    header("Location: view_orders.php?error=3"); // Delivery datetime not set
                    exit();
                }
            }
            $check_stmt->close();
        }

        // Update status using prepared statement
        $update_sql = "UPDATE orders SET status = ? WHERE id = ?";
        $stmt = $conn->prepare($update_sql);
        if ($stmt) {
            $stmt->bind_param("si", $new_status, $order_id);
            if ($stmt->execute()) {
                $stmt->close();
                header("Location: view_orders.php?success=1");
                exit();
            } else {
                $stmt->close();
                header("Location: view_orders.php?error=1");
                exit();
            }
        } else {
            header("Location: view_orders.php?error=2");
            exit();
        }
    } else {
        header("Location: view_orders.php?error=2");
        exit();
    }
}

// Fetch orders with customer and product details
$orders_sql = "
    SELECT 
        orders.id AS order_id,
        orders.status,
        orders.order_date,
        orders.delivery_datetime,
        customers.name AS customer_name,
        customers.contact,
        customers.delivery_address,
        orders.product_id AS primary_product_id,
        orders.quantity AS primary_quantity,
        orders.packaging_preferences AS primary_packaging_preferences,
        products.name AS primary_product_name,
        products.size AS primary_product_size
    FROM 
        orders
    JOIN 
        customers ON orders.customer_id = customers.id
    JOIN 
        products ON orders.product_id = products.id
    ORDER BY 
        orders.order_date DESC
";
$orders_result = $conn->query($orders_sql);

// Fetch order items for all orders
$order_items_sql = "
    SELECT 
        order_items.order_id,
        order_items.product_id,
        order_items.quantity,
        order_items.packaging_preferences,
        products.name AS product_name,
        products.size AS product_size
    FROM 
        order_items
    JOIN 
        products ON order_items.product_id = products.id
";
$order_items_result = $conn->query($order_items_sql);

// Organize order items by order_id
$order_items = [];
if ($order_items_result && $order_items_result->num_rows > 0) {
    while ($item = $order_items_result->fetch_assoc()) {
        $order_items[$item['order_id']][] = $item;
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>View Orders - Kaak Alia Management System</title>
    
    <!-- Google Fonts -->
    <link href="https://fonts.googleapis.com/css2?family=Cairo:wght@400;600;700&display=swap" rel="stylesheet">
    
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="/kaak_alia_management_system/assets/bootstrap/css/bootstrap.min.css">
    
    <!-- Bootstrap Icons -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css">
    
    <!-- Embedded Custom Styles -->
    <style>
        /* ---------------------------------------------------
           1. CSS Variables for Consistent Design
        ---------------------------------------------------- */
        :root {
            --dark-gray: #333333; /* Header and Footer Background */
            --soft-brown: #FFD166; /* Add New Order Button */
            --blue: #118AB2; /* View Orders Button */
            --green: #06D6A0; /* Manage Products Button */
            --orange: #F4A261; /* Manage Users Button */
            --red-pink: #EF476F; /* Generate Reports Button */

            --main-text: #333333;
            --secondary-text: #666666;
            --white: #FFFFFF;
            --light-gray: #F9F9F9;
            --divider-color: #FFD166;

            --transition-speed: 0.3s;
            --hover-shadow: rgba(0, 0, 0, 0.2);
        }

        /* ---------------------------------------------------
           2. Global Styles
        ---------------------------------------------------- */
        body {
            font-family: 'Cairo', sans-serif;
            background: linear-gradient(to bottom, #f5f7fa, #c3cfe2);
            margin: 0;
            padding: 30px;
            box-sizing: border-box;
        }

        /* ---------------------------------------------------
           3. Header Styles
        ---------------------------------------------------- */
        .header {
            padding: 20px 30px;
            background-color: var(--dark-gray);
            border-radius: 8px;
            text-align: center;
            margin-bottom: 30px;
            color: var(--white);
            font-size: 24px;
            font-weight: bold;
        }

        /* ---------------------------------------------------
           4. Success and Error Alerts
        ---------------------------------------------------- */
        .alert-success-custom {
            background-color: var(--green);
            color: var(--white);
            border-radius: 8px;
            padding: 15px 20px;
            margin-bottom: 20px;
            text-align: center;
            width: 70%;
            margin: 20px auto;
        }

        .alert-error-custom {
            background-color: var(--red-pink);
            color: var(--white);
            border-radius: 8px;
            padding: 15px 20px;
            margin-bottom: 20px;
            text-align: center;
            width: 70%;
            margin: 20px auto;
        }

        /* ---------------------------------------------------
           5. Recent Orders Table
        ---------------------------------------------------- */
        .recent-orders-section {
            background-color: #ffffff;
            border-radius: 8px;
            padding: 20px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }

        .recent-orders-section h4 {
            font-size: 16px;
            font-weight: bold;
            color: var(--main-text);
            margin-bottom: 15px;
        }

        .recent-orders-section table {
            width: 100%;
            border-collapse: collapse;
        }

        .recent-orders-section th, 
        .recent-orders-section td {
            padding: 12px;
            text-align: left;
            font-size: 16px;
        }

        .recent-orders-section th {
            background-color: var(--dark-gray);
            color: var(--white);
            font-weight: bold;
        }

        .recent-orders-section tr:nth-child(even) {
            background-color: var(--light-gray);
        }

        .recent-orders-section tr:hover {
            background-color: #e6f7ff;
        }

        .recent-orders-section td {
            font-size: 14px;
            color: var(--main-text);
            vertical-align: top;
        }

        .recent-orders-section .view-all {
            display: block;
            text-align: right;
            margin-top: 10px;
            font-size: 14px;
            color: var(--blue);
            font-weight: bold;
            text-decoration: none;
            transition: color 0.2s;
        }

        .recent-orders-section .view-all:hover {
            color: #075985;
        }

        /* Badge Styles */
        .badge-status-pending {
            background-color: #FFD166;
            color: var(--white);
            padding: 5px 10px;
            border-radius: 12px;
            font-size: 12px;
            font-weight: bold;
        }

        .badge-status-in-progress {
            background-color: #118AB2;
            color: var(--white);
            padding: 5px 10px;
            border-radius: 12px;
            font-size: 12px;
            font-weight: bold;
        }

        .badge-status-completed {
            background-color: #06D6A0;
            color: var(--white);
            padding: 5px 10px;
            border-radius: 12px;
            font-size: 12px;
            font-weight: bold;
        }

        .badge-status-delivered {
            background-color: #EF476F;
            color: var(--white);
            padding: 5px 10px;
            border-radius: 12px;
            font-size: 12px;
            font-weight: bold;
        }

        /* ---------------------------------------------------
           6. Footer Styles
        ---------------------------------------------------- */
        .footer {
            background-color: var(--dark-gray);
            color: var(--white);
            padding: 15px;
            text-align: center;
            border-radius: 8px;
            font-size: 14px;
            line-height: 20px;
            margin-top: 40px;
        }

        /* ---------------------------------------------------
           7. Responsive Design Enhancements
        ---------------------------------------------------- */
        @media (max-width: 768px) {
            /* Align form elements for tablets */
            .form-label, .form-control, .submit-button, .alert-success-custom, .alert-error-custom {
                width: 90%;
                margin: 0 auto 15px auto;
            }
        }

        @media (max-width: 480px) {
            /* Align form elements for mobile devices */
            .header {
                padding: 15px 20px;
                font-size: 20px;
            }

            .header h1 {
                font-size: 20px;
            }

            .recent-orders-section th, 
            .recent-orders-section td {
                font-size: 12px;
                padding: 8px;
            }

            .recent-orders-section .view-all {
                font-size: 14px;
            }
        }
    </style>
</head>
<body>

    <!-- Header -->
    <div class="header">
        View Orders
    </div>

    <!-- Display Success or Error Messages -->
    <?php if (isset($_GET['success']) && $_GET['success'] == 1): ?>
        <div class="alert-success-custom">Order status updated successfully!</div>
    <?php endif; ?>
    
    <?php if (isset($_GET['error'])): ?>
        <?php if ($_GET['error'] == 1): ?>
            <div class="alert-error-custom">Failed to update order status.</div>
        <?php elseif ($_GET['error'] == 2): ?>
            <div class="alert-error-custom">Invalid status selected.</div>
        <?php elseif ($_GET['error'] == 3): ?>
            <div class="alert-error-custom">Cannot set status to Delivered without a delivery date and time.</div>
        <?php endif; ?>
    <?php endif; ?>

    <!-- Container -->
    <div class="recent-orders-section">
        <h4>All Orders</h4>
        <table>
            <thead>
                <tr>
                    <th>ID</th>
                    <th>Customer</th>
                    <th>Contact</th>
                    <th>Delivery Address</th>
                    <th>Primary Product</th>
                    <th>Additional Products</th>
                    <th>Status</th>
                    <th>Order Date</th>
                    <th>Delivery DateTime</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php if ($orders_result && $orders_result->num_rows > 0): ?>
                    <?php while($order = $orders_result->fetch_assoc()): ?>
                        <tr>
                            <td><?= htmlspecialchars($order['order_id']) ?></td>
                            <td><?= htmlspecialchars($order['customer_name']) ?></td>
                            <td><?= htmlspecialchars($order['contact']) ?></td>
                            <td><?= nl2br(htmlspecialchars($order['delivery_address'])) ?></td>
                            <td>
                                <?= htmlspecialchars($order['primary_product_name'] . " - " . $order['primary_product_size'] . " (" . number_format(get_product_price($conn, $order['primary_product_id']), 2) . " KD)") ?><br>
                                Quantity: <?= htmlspecialchars($order['primary_quantity']) ?><br>
                                Packaging: <?= htmlspecialchars($order['primary_packaging_preferences']) ?>
                            </td>
                            <td>
                                <?php
                                    if (isset($order_items[$order['order_id']])) {
                                        foreach ($order_items[$order['order_id']] as $item) {
                                            echo htmlspecialchars($item['product_name'] . " - " . $item['product_size'] . " (" . number_format(get_product_price($conn, $item['product_id']), 2) . " KD)") . "<br>";
                                            echo "Quantity: " . htmlspecialchars($item['quantity']) . "<br>";
                                            echo "Packaging: " . htmlspecialchars($item['packaging_preferences']) . "<br><br>";
                                        }
                                    } else {
                                        echo "None";
                                    }
                                ?>
                            </td>
                            <td>
                                <?php
                                    switch ($order['status']) {
                                        case 'Pending':
                                            echo '<span class="badge-status-pending">Pending</span>';
                                            break;
                                        case 'In Progress':
                                            echo '<span class="badge-status-in-progress">In Progress</span>';
                                            break;
                                        case 'Completed':
                                            echo '<span class="badge-status-completed">Completed</span>';
                                            break;
                                        case 'Delivered':
                                            echo '<span class="badge-status-delivered">Delivered</span>';
                                            break;
                                        default:
                                            echo '<span class="badge bg-light text-dark">Unknown</span>';
                                    }
                                ?>
                            </td>
                            <td><?= date("d M Y, h:i A", strtotime($order['order_date'])) ?></td>
                            <td><?= $order['delivery_datetime'] ? date("d M Y, h:i A", strtotime($order['delivery_datetime'])) : 'N/A' ?></td>
                            <td>
                                <!-- Edit Button -->
                                <a href="edit_order.php?id=<?= htmlspecialchars($order['order_id']) ?>" class="btn btn-sm btn-outline-secondary" title="Edit Order">
                                    <i class="bi bi-pencil-fill"></i>
                                </a>
                                <!-- Change Status Dropdown -->
                                <div class="btn-group">
                                    <button type="button" class="btn btn-sm btn-outline-info dropdown-toggle" data-bs-toggle="dropdown" title="Change Status">
                                        <i class="bi bi-arrow-repeat"></i>
                                    </button>
                                    <ul class="dropdown-menu">
                                        <li><a class="dropdown-item" href="?action=update_status&order_id=<?= htmlspecialchars($order['order_id']) ?>&new_status=Pending">Pending</a></li>
                                        <li><a class="dropdown-item" href="?action=update_status&order_id=<?= htmlspecialchars($order['order_id']) ?>&new_status=In Progress">In Progress</a></li>
                                        <li><a class="dropdown-item" href="?action=update_status&order_id=<?= htmlspecialchars($order['order_id']) ?>&new_status=Completed">Completed</a></li>
                                        <li><a class="dropdown-item" href="?action=update_status&order_id=<?= htmlspecialchars($order['order_id']) ?>&new_status=Delivered">Delivered</a></li>
                                    </ul>
                                </div>
                            </td>
                        </tr>
                    <?php endwhile; ?>
                <?php else: ?>
                    <tr>
                        <td colspan="10" class="text-center">No orders found.</td>
                    </tr>
                <?php endif; ?>
            </tbody>
        </table>
        <!-- <a href="#" class="view-all">View All Orders</a> --> <!-- Removed since all orders are already displayed -->
    </div>
    
    <!-- Footer -->
    <div class="footer">
        &copy; 2024 Kaak Alia Bakery. All rights reserved.
    </div>
    
    <!-- Bootstrap JS Bundle -->
    <script src="/kaak_alia_management_system/assets/bootstrap/js/bootstrap.bundle.min.js"></script>

    <!-- Initialize Bootstrap Tooltip (Optional) -->
    <script>
        var tooltipTriggerList = [].slice.call(document.querySelectorAll('[title]'))
        var tooltipList = tooltipTriggerList.map(function (tooltipTriggerEl) {
            return new bootstrap.Tooltip(tooltipTriggerEl)
        })
    </script>
</body>
</html>
<?php
// Helper function to get product price
function get_product_price($conn, $product_id) {
    $price_sql = "SELECT price FROM products WHERE id = ? LIMIT 1";
    $price_stmt = $conn->prepare($price_sql);
    $price_stmt->bind_param("i", $product_id);
    $price_stmt->execute();
    $price_result = $price_stmt->get_result();
    if ($price_result->num_rows == 1) {
        $price = $price_result->fetch_assoc()['price'];
    } else {
        $price = "0.00";
    }
    $price_stmt->close();
    return $price;
}
?>) so create file PHP to execute this order