based on this info database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 07, 2024 at 04:17 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;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `name`, `contact`, `delivery_address`) VALUES
(1, 'order_94513', '5162154 84561', 'Qena, 332 host'),
(2, 'order_94513', '8888 5461', 'Qina, Egpyt'),
(3, 'order_94513', '5162154 84561', 'Qena, Egypt'),
(4, 'order_8496', '15546843', 'Qena,Egpyt');

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

--
-- 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;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`id`, `customer_id`, `product_id`, `status`, `order_date`, `delivery_datetime`, `delivery_date`, `quantity`, `packaging_preferences`) VALUES
(3, 3, 8, 'Completed', '2024-11-06 16:29:21', '2024-11-08 06:31:00', NULL, 1, 'This Order is so Important and type card, (Mahmoud Tawfik)'),
(4, 4, 10, 'Completed', '2024-11-06 18:00:08', '2024-11-07 10:01:00', NULL, 2, 'This Order is so Important and type card, (Mahmoud Tawfik)');

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

--
-- 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;

--
-- Dumping data for table `order_items`
--

INSERT INTO `order_items` (`id`, `order_id`, `product_id`, `quantity`, `packaging_preferences`) VALUES
(1, 3, 6, 1, '');

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

--
-- 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;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`, `size`, `price`, `category`) VALUES
(1, 'Savory Cake', 'Large', 12.00, 'Savory Cake'),
(2, 'Mini Savory Cake', 'Mini', 6.00, 'Savory Cake'),
(3, 'Mini Savory Cake', 'Mini x24', 9.00, 'Savory Cake'),
(4, 'Mini Savory Cake', 'Mini x36', 12.00, 'Savory Cake'),
(5, 'Special Occasion Box', '35 pieces', 14.50, 'Savory Cake'),
(6, 'Crunchy Pudding', '12 pieces', 12.00, 'Crunchy Pudding'),
(7, 'Kaak - Regular Box Half-kilo', 'Half-kilo', 6.00, 'Kaak'),
(8, 'Kaak - Regular Box One-kilo', 'One-kilo', 8.00, 'Kaak'),
(9, 'Visitor’s Basket - Small', 'Small Basket', 8.00, 'Kaak'),
(10, 'Visitor’s Basket - Large', 'Large Basket', 10.00, 'Kaak'),
(11, 'Special Occasion Box - Kaak', '33 pieces', 11.00, 'Kaak');

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

--
-- 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;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `password`, `role`) VALUES
(1, 'admin', '$2y$10$e0NR4EJkO3jUuL1KqJx3E.E0Iojb95czjJGb2a7v0GfN0V8pQBv8W', 'admin'),
(2, 'kaakalia', '$2y$10$xHUvBhNLyy4eN8WVtCrdzuX0j8wYOVCH6m.czPtqfiWImUCC9w9aO', 'admin');

--
-- 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=5;

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

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

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

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

--
-- 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 */;), in header.php (<?php
// includes/header.php

// Start the session
if (session_status() == PHP_SESSION_NONE) {
    session_start();
}

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

// Fetch the last pending order ID to initialize polling
$last_order_id = 0;
$initial_pending_sql = "
    SELECT id 
    FROM orders 
    WHERE status = 'Pending' 
    ORDER BY id DESC 
    LIMIT 1";
$initial_result = $conn->query($initial_pending_sql);
if ($initial_result && $initial_result->num_rows > 0) {
    $row = $initial_result->fetch_assoc();
    $last_order_id = intval($row['id']);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <!-- Meta Tags for Responsiveness and Accessibility -->
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Admin Dashboard</title>
    
    <!-- Tailwind CSS -->
    <link href="https://cdn.jsdelivr.net/npm/tailwindcss@2.2.19/dist/tailwind.min.css" rel="stylesheet">
    <!-- Bootstrap Icons -->
    <link href="https://cdn.jsdelivr.net/npm/bootstrap-icons/font/bootstrap-icons.css" rel="stylesheet">
    <!-- Bootstrap JS -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/js/bootstrap.bundle.min.js" defer></script>
    <!-- Alpine.js for Interactivity -->
    <script src="https://cdn.jsdelivr.net/npm/alpinejs@3.10.3/dist/cdn.min.js" defer></script>
</head>
<body class="bg-background-gradient">

    <!-- Header -->
    <header class="admin-header fixed-top bg-gray-800 text-white py-4 border-b-4 border-yellow-400 shadow-lg z-50">
        <div class="container mx-auto px-4">
            <div class="flex justify-between items-center">
                <!-- Logo -->
                <div class="logo flex items-center">
                    <a href="/public/dashboard.php" class="flex items-center">
                        <img src="/assets/images/logo.png" alt="Kaak Alia Logo" class="h-12 w-auto transition-transform duration-300 hover:scale-105">
                        <span class="ml-2 text-xl font-bold">Admin Panel</span>
                    </a>
                </div>

                <!-- Navigation Links (Desktop) -->
                <nav class="hidden md:flex space-x-4">
                    <a href="/public/orders/add_order.php" class="flex items-center bg-yellow-400 text-gray-800 px-4 py-2 rounded-lg hover:bg-yellow-300 shadow transition-all duration-300">
                        <i class="bi bi-plus-circle-fill mr-2"></i> Add Order
                    </a>
                    <a href="/public/orders/view_orders.php" class="flex items-center bg-blue-500 text-white px-4 py-2 rounded-lg hover:bg-blue-400 shadow transition-all duration-300">
                        <i class="bi bi-eye-fill mr-2"></i> View Orders
                    </a>
                    <a href="/public/products/manage_products.php" class="flex items-center bg-green-400 text-white px-4 py-2 rounded-lg hover:bg-green-300 shadow transition-all duration-300">
                        <i class="bi bi-box-seam-fill mr-2"></i> Manage Products
                    </a>
                    <a href="/public/users/manage_users.php" class="flex items-center bg-orange-500 text-white px-4 py-2 rounded-lg hover:bg-orange-400 shadow transition-all duration-300">
                        <i class="bi bi-people-fill mr-2"></i> Manage Users
                    </a>
                    <a href="/public/reports/generate_reports.php" class="flex items-center bg-pink-500 text-white px-4 py-2 rounded-lg hover:bg-pink-400 shadow transition-all duration-300">
                        <i class="bi bi-bar-chart-fill mr-2"></i> Reports
                    </a>
                </nav>

                <!-- Right Side Icons -->
                <div class="flex items-center space-x-4">
                    <!-- Notifications Dropdown -->
                    <div class="relative" x-data="{ open: false }">
                        <button @click="open = !open" class="relative text-white hover:text-yellow-400 focus:outline-none" aria-haspopup="true" aria-expanded="false" aria-controls="notification-menu" aria-label="Notifications">
                            <i class="bi bi-bell-fill text-2xl"></i>
                            <?php if ($last_order_id > 0): ?>
                                <!-- Notification Badge -->
                                <span class="absolute top-0 right-0 inline-block w-3 h-3 bg-red-500 rounded-full"></span>
                            <?php endif; ?>
                        </button>
                        <!-- Dropdown Menu -->
                        <div x-show="open" @click.away="open = false" id="notification-menu" class="absolute right-0 mt-2 w-80 bg-gray-800 text-white rounded-lg shadow-lg z-50">
                            <div class="py-2 px-4 border-b border-yellow-400">
                                <h3 class="text-lg font-semibold">Notifications</h3>
                            </div>
                            <ul class="max-h-64 overflow-y-auto" id="notificationsList">
                                <?php
                                // Fetch the latest 5 pending orders
                                $notifications_sql = "
                                    SELECT orders.id, orders.order_date, customers.name AS customer_name, products.name AS product_name, orders.quantity 
                                    FROM orders 
                                    JOIN customers ON orders.customer_id = customers.id 
                                    JOIN products ON orders.product_id = products.id 
                                    WHERE orders.status = 'Pending' 
                                    ORDER BY orders.id DESC 
                                    LIMIT 5";
                                $notifications_result = $conn->query($notifications_sql);
                                if ($notifications_result && $notifications_result->num_rows > 0):
                                    while($notification = $notifications_result->fetch_assoc()):
                                ?>
                                        <li class="px-4 py-2 hover:bg-gray-700 cursor-pointer">
                                            <span class="inline-block w-2 h-2 bg-red-500 rounded-full mr-2"></span>
                                            New Order: <?= htmlspecialchars($notification['customer_name']); ?> ordered <?= htmlspecialchars($notification['product_name']); ?> x<?= htmlspecialchars($notification['quantity']); ?>
                                        </li>
                                <?php
                                    endwhile;
                                else:
                                ?>
                                    <li class="px-4 py-2 text-gray-400">No new notifications</li>
                                <?php endif; ?>
                            </ul>
                            <div class="py-2 px-4 border-t border-yellow-400 text-center">
                                <a href="/public/notifications.php" class="text-yellow-400 hover:underline">View All</a>
                            </div>
                        </div>
                    </div>

                    <!-- User Profile Dropdown -->
                    <div class="relative" x-data="{ open: false }">
                        <button @click="open = !open" class="flex items-center focus:outline-none" aria-haspopup="true" aria-expanded="false" aria-controls="profile-menu">
                            <img src="/assets/images/user-placeholder.png" alt="User Avatar" class="h-10 w-10 rounded-full border-2 border-white hover:border-yellow-400 transition-colors duration-300">
                            <span class="ml-2 hidden lg:inline-block">
                                <?php
                                    // Display the logged-in user's username
                                    echo isset($_SESSION['username']) ? htmlspecialchars($_SESSION['username']) : 'User';
                                ?>
                            </span>
                            <i class="bi bi-caret-down-fill ml-1 hidden lg:inline-block"></i>
                        </button>
                        <!-- Dropdown Menu -->
                        <div x-show="open" @click.away="open = false" id="profile-menu" class="absolute right-0 mt-2 w-48 bg-gray-800 text-white rounded-lg shadow-lg z-50">
                            <a href="/public/users/settings.php" class="block px-4 py-2 hover:bg-yellow-400 hover:text-gray-800 flex items-center">
                                <i class="bi bi-gear-fill mr-2"></i> Settings
                            </a>
                            <a href="/public/logout.php" class="block px-4 py-2 hover:bg-yellow-400 hover:text-gray-800 flex items-center">
                                <i class="bi bi-box-arrow-right mr-2"></i> Logout
                            </a>
                        </div>
                    </div>

                    <!-- Mobile Menu Button -->
                    <div class="md:hidden">
                        <button id="mobile-menu-button" class="text-white focus:outline-none" aria-label="Toggle Mobile Menu">
                            <i class="bi bi-list text-2xl"></i>
                        </button>
                    </div>
                </div>
            </div>
            
            <!-- Mobile Navigation Menu -->
            <nav id="mobile-menu" class="hidden md:hidden mt-4">
                <ul class="flex flex-col space-y-2">
                    <li>
                        <a href="/public/orders/add_order.php" class="flex items-center bg-yellow-400 text-gray-800 px-4 py-2 rounded-lg hover:bg-yellow-300 transition-all duration-300">
                            <i class="bi bi-plus-circle-fill mr-2"></i> Add Order
                        </a>
                    </li>
                    <li>
                        <a href="/public/orders/view_orders.php" class="flex items-center bg-blue-500 text-white px-4 py-2 rounded-lg hover:bg-blue-400 transition-all duration-300">
                            <i class="bi bi-eye-fill mr-2"></i> View Orders
                        </a>
                    </li>
                    <li>
                        <a href="/public/products/manage_products.php" class="flex items-center bg-green-400 text-white px-4 py-2 rounded-lg hover:bg-green-300 transition-all duration-300">
                            <i class="bi bi-box-seam-fill mr-2"></i> Manage Products
                        </a>
                    </li>
                    <li>
                        <a href="/public/users/manage_users.php" class="flex items-center bg-orange-500 text-white px-4 py-2 rounded-lg hover:bg-orange-400 transition-all duration-300">
                            <i class="bi bi-people-fill mr-2"></i> Manage Users
                        </a>
                    </li>
                    <li>
                        <a href="/public/reports/generate_reports.php" class="flex items-center bg-pink-500 text-white px-4 py-2 rounded-lg hover:bg-pink-400 transition-all duration-300">
                            <i class="bi bi-bar-chart-fill mr-2"></i> Reports
                        </a>
                    </li>
                    <!-- Additional Mobile Links -->
                    <li>
                        <a href="/public/notifications.php" class="flex items-center bg-gray-700 text-white px-4 py-2 rounded-lg hover:bg-gray-600 transition-all duration-300">
                            <i class="bi bi-bell-fill mr-2"></i> Notifications
                        </a>
                    </li>
                </ul>
            </nav>
        </div>
    </header>

    <!-- Audio Element for New Order Alert -->
    <audio id="newOrderAudio" src="you_have_a_new_order.mp3" preload="auto"></audio>

    <!-- JavaScript for Real-Time Notifications and Audio Alert -->
    <script>
        document.addEventListener('DOMContentLoaded', function() {
            
                const mobileMenuButton = document.getElementById('mobile-menu-button');
    const mobileMenu = document.getElementById('mobile-menu');

    // Toggle menu on button click
    mobileMenuButton.addEventListener('click', function() {
        mobileMenu.classList.toggle('hidden');
    });

            let lastOrderId = <?= $last_order_id ?>;
            const audio = document.getElementById('newOrderAudio');

            // Function to fetch new orders
            function fetchNewOrders() {
                fetch(`/includes/get_new_orders.php?last_order_id=${lastOrderId}`)
                    .then(response => response.json())
                    .then(data => {
                        if (data.orders && data.orders.length > 0) {
                            data.orders.forEach(order => {
                                // Update lastOrderId
                                if (order.id > lastOrderId) {
                                    lastOrderId = order.id;
                                }

                                // Append to the notifications list
                                const notificationsList = document.getElementById('notificationsList');
                                const li = document.createElement('li');
                                li.className = 'px-4 py-2 hover:bg-gray-700 cursor-pointer';
                                li.innerHTML = `
                                    <span class="inline-block w-2 h-2 bg-red-500 rounded-full mr-2"></span>
                                    New Order: ${sanitizeHTML(order.customer_name)} ordered ${sanitizeHTML(order.product_name)} x${sanitizeHTML(order.quantity)}
                                `;
                                // Insert the new notification at the top
                                notificationsList.insertBefore(li, notificationsList.firstChild);

                                // Play the audio alert
                                if (audio) {
                                    audio.play().catch(function(error) {
                                        console.log('Audio playback failed:', error);
                                    });
                                }

                                // Optionally, update the notification badge
                                const bellIcon = document.querySelector('header .bi-bell-fill');
                                if (bellIcon && !bellIcon.nextElementSibling) {
                                    const badge = document.createElement('span');
                                    badge.className = 'absolute top-0 right-0 inline-block w-3 h-3 bg-red-500 rounded-full';
                                    bellIcon.parentNode.appendChild(badge);
                                }
                            });
                        }
                    })
                    .catch(error => {
                        console.error('Error fetching new orders:', error);
                    });
            }

            // Function to sanitize HTML to prevent XSS
            function sanitizeHTML(str) {
                var temp = document.createElement('div');
                temp.textContent = str;
                return temp.innerHTML;
            }

            // Poll every 10 seconds
            setInterval(fetchNewOrders, 10000); // 10000 ms = 10 seconds

            // Optional: Fetch immediately on page load
            fetchNewOrders();
        });
        
        
        
    </script>

    <!-- Bootstrap JS Bundle -->
    <script src="/kaak_alia_management_system/assets/bootstrap/js/bootstrap.bundle.min.js"></script>
</body>
</html>) fix the audio and the audio file exist in includes folder, (you_have_a_new_order.mp3) and this (<?php
// includes/get_new_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 'auth.php';
require_once 'db_connect.php';

// Get the last known order ID from the client
$last_order_id = isset($_GET['last_order_id']) ? intval($_GET['last_order_id']) : 0;

// Prepare the SQL to fetch new pending orders
$sql = "SELECT orders.id, orders.order_date, customers.name AS customer_name, products.name AS product_name, orders.quantity
        FROM orders 
        JOIN customers ON orders.customer_id = customers.id 
        JOIN products ON orders.product_id = products.id 
        WHERE orders.status = 'Pending' AND orders.id > ?
        ORDER BY orders.id ASC";

$stmt = $conn->prepare($sql);
if (!$stmt) {
    http_response_code(500);
    echo json_encode(['error' => 'Database error: ' . $conn->error]);
    exit();
}

$stmt->bind_param("i", $last_order_id);
$stmt->execute();
$result = $stmt->get_result();

$new_orders = [];
while ($row = $result->fetch_assoc()) {
    $new_orders[] = [
        'id' => $row['id'],
        'order_date' => $row['order_date'],
        'customer_name' => htmlspecialchars($row['customer_name'], ENT_QUOTES, 'UTF-8'),
        'product_name' => htmlspecialchars($row['product_name'], ENT_QUOTES, 'UTF-8'),
        'quantity' => intval($row['quantity'])
    ];
}

$stmt->close();

// Return the new orders as JSON
header('Content-Type: application/json');
echo json_encode(['orders' => $new_orders]);
?>) so fix teh audio and when i add order, it the audio play as notifciaotn for any one register on dasbaord or any page, so fix the audio and this file of add order (<?php
// public/orders/add_order.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 **before** including the header
require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../includes/db_connect.php';

// Now include the header
require_once '../../includes/header.php';

// Initialize variables
$success = '';
$error = '';

// Fetch products for selection
$products_sql = "SELECT * FROM products ORDER BY name ASC";
$products_result = $conn->query($products_sql);

if (!$products_result) {
    die("Error fetching products: " . $conn->error);
}

// Handle form submission
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Sanitize and validate input
    $customer_name = trim($_POST['customer_name']);
    $contact = trim($_POST['contact']);
    $delivery_address = trim($_POST['delivery_address']);
    $delivery_datetime = isset($_POST['delivery_datetime']) && !empty($_POST['delivery_datetime']) ? trim($_POST['delivery_datetime']) : null;

    // Handle multiple products
    $product_ids = isset($_POST['product_id']) ? $_POST['product_id'] : [];
    $quantities = isset($_POST['quantity']) ? $_POST['quantity'] : [];
    $packaging_preferences = isset($_POST['packaging_preferences']) ? $_POST['packaging_preferences'] : [];

    // Basic validation
    if (empty($customer_name)) {
        $error = "Customer name is required.";
    } elseif (empty($product_ids) || !is_array($product_ids)) {
        $error = "At least one product must be selected.";
    } else {
        // Validate each product entry
        foreach ($product_ids as $index => $pid) {
            if (empty($pid)) {
                $error = "Product selection is required for all items.";
                break;
            }
            if (intval($quantities[$index]) <= 0) {
                $error = "Quantity must be at least 1 for all items.";
                break;
            }
        }

        if ($delivery_datetime && !preg_match("/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}$/", $delivery_datetime)) {
            $error = "Invalid delivery date and time format.";
        } elseif ($delivery_datetime && strtotime(str_replace('T', ' ', $delivery_datetime)) < strtotime(date('Y-m-d H:i'))) {
            $error = "Delivery date and time cannot be in the past.";
        }
    }

    if (empty($error)) {
        // Start transaction
        $conn->begin_transaction();

        try {
            // Insert into customers table using prepared statement
            $stmt = $conn->prepare("INSERT INTO customers (name, contact, delivery_address) VALUES (?, ?, ?)");
            if (!$stmt) {
                throw new Exception("Prepare statement failed: " . $conn->error);
            }
            $stmt->bind_param("sss", $customer_name, $contact, $delivery_address);
            if (!$stmt->execute()) {
                throw new Exception("Execute failed: " . $stmt->error);
            }
            $customer_id = $stmt->insert_id;
            $stmt->close();

            // Insert into orders table using prepared statement
            // Retain the first product in the orders table's product_id
            $first_product_id = intval($product_ids[0]);
            $first_quantity = intval($quantities[0]);
            $first_packaging_preferences = trim($packaging_preferences[0]);

            $stmt = $conn->prepare("INSERT INTO orders (customer_id, product_id, quantity, packaging_preferences, status, order_date, delivery_datetime) VALUES (?, ?, ?, ?, 'Pending', NOW(), ?)");
            if (!$stmt) {
                throw new Exception("Prepare statement failed: " . $conn->error);
            }
            $stmt->bind_param("iiiss", $customer_id, $first_product_id, $first_quantity, $first_packaging_preferences, $delivery_datetime);
            if (!$stmt->execute()) {
                throw new Exception("Execute failed: " . $stmt->error);
            }
            $order_id = $stmt->insert_id;
            $stmt->close();

            // Insert remaining products into order_items table
            if (count($product_ids) > 1) {
                $stmt = $conn->prepare("INSERT INTO order_items (order_id, product_id, quantity, packaging_preferences) VALUES (?, ?, ?, ?)");
                if (!$stmt) {
                    throw new Exception("Prepare statement failed: " . $conn->error);
                }

                // Loop through remaining products (starting from index 1)
                for ($i = 1; $i < count($product_ids); $i++) {
                    $pid = intval($product_ids[$i]);
                    $qty = intval($quantities[$i]);
                    $pack_pref = trim($packaging_preferences[$i]);

                    $stmt->bind_param("iiis", $order_id, $pid, $qty, $pack_pref);
                    if (!$stmt->execute()) {
                        throw new Exception("Execute failed: " . $stmt->error);
                    }
                }
                $stmt->close();
            }

            // Commit transaction
            $conn->commit();

            $success = "Order added successfully!";

            // Reset form fields
            $_POST = [];
        } catch (Exception $e) {
            // Rollback transaction
            $conn->rollback();
            $error = "Error adding order: " . $e->getMessage();
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Add New Order - 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: #515151; /* Primary text and key elements */
            --soft-brown: #b78d67; /* Secondary color for accents and buttons */
            --deep-brown: #8e6a51; /* Accent color for interactive elements */
            --white: #ffffff; /* White background and button text */
            --light-gray: #f9f9f9; /* Light gray for input backgrounds and alternate table rows */
            --divider-color: #FFD166; /* Yellow for dividers */
            --error-color: #EF476F; /* Red-pink for error messages */
            --success-color: #06D6A0; /* Green for success messages */
            --hover-shadow: rgba(0, 0, 0, 0.2);
            --transition-speed: 0.3s;
        }

        /* ---------------------------------------------------
           2. Global Styles
        ---------------------------------------------------- */
        body {
            font-family: 'Cairo', sans-serif;
            background-color: var(--white);
            margin: 0;
            padding: 30px;
            box-sizing: border-box;
        }

        h3, h5, label {
            color: var(--dark-gray);
            font-weight: 600;
        }

        .form-section {
            margin-bottom: 20px;
        }

        /* ---------------------------------------------------
           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. Welcome Section
        ---------------------------------------------------- */
        .welcome-section {
            background-color: #ffffff;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
            border-radius: 8px;
            padding: 20px;
            text-align: center;
            margin-bottom: 30px;
        }

        .welcome-section h3 {
            font-size: 20px;
            color: var(--dark-gray);
            margin-bottom: 10px;
        }

        .welcome-section hr {
            width: 50px;
            height: 3px;
            background-color: var(--divider-color);
            border: none;
            margin: 10px auto 0;
        }

        /* ---------------------------------------------------
           5. Form Container Styles
        ---------------------------------------------------- */
        .form-container {
            background-color: #ffffff;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
            border-radius: 8px;
            padding: 30px;
            max-width: 900px;
            margin: auto;
        }

        .form-container h3 {
            text-align: center;
            margin-bottom: 20px;
            color: var(--soft-brown);
        }

        /* ---------------------------------------------------
           6. Form Styles
        ---------------------------------------------------- */
        .form-control {
            border-radius: 8px;
            border: 1px solid #ccc;
            background-color: var(--light-gray);
            padding: 12px 15px;
            transition: border-color var(--transition-speed), box-shadow var(--transition-speed);
            width: 100%;
            margin: 0 auto 15px auto;
            display: block;
        }

        .form-control:focus {
            border-color: var(--deep-brown);
            box-shadow: 0 0 5px rgba(142, 106, 81, 0.3);
            background-color: #ffffff;
        }

        .form-label {
            margin-bottom: 5px;
            font-size: 14px;
            display: block;
            text-align: left;
            width: 100%;
            margin: 0 auto 5px auto;
        }

        .add-product-button {
            background-color: var(--soft-brown);
            color: var(--white);
            border: none;
            padding: 8px 12px;
            border-radius: 5px;
            cursor: pointer;
            margin-top: 10px;
            transition: background-color var(--transition-speed);
        }

        .add-product-button:hover {
            background-color: var(--deep-brown);
        }

        .remove-product-button {
            background-color: #dc3545;
            color: var(--white);
            border: none;
            padding: 6px 10px;
            border-radius: 5px;
            cursor: pointer;
            transition: background-color var(--transition-speed);
        }

        .remove-product-button:hover {
            background-color: #c82333;
        }

        /* ---------------------------------------------------
           7. Buttons Section
        ---------------------------------------------------- */
        .submit-button {
            width: 100%;
            height: 50px;
            font-size: 18px;
            font-weight: bold;
            border-radius: 8px;
            border: none;
            cursor: pointer;
            transition: transform var(--transition-speed), box-shadow var(--transition-speed);
            padding: 10px 20px;
            display: block;
            margin: 20px auto 0 auto;
            text-align: center;
            background-color: #FFD166;
            color: var(--white);
        }

        .submit-button:hover {
            background-color: #FFC043;
            transform: scale(1.02);
            box-shadow: 0 4px 6px var(--hover-shadow);
        }

        /* ---------------------------------------------------
           8. Alert Styles
        ---------------------------------------------------- */
        .alert-success {
            background-color: var(--success-color);
            color: var(--white);
            border-radius: 8px;
            padding: 15px 20px;
            margin-bottom: 20px;
            text-align: center;
            width: 100%;
            margin: 20px auto;
        }

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

        /* ---------------------------------------------------
           9. Responsive Design Enhancements
        ---------------------------------------------------- */
        @media (max-width: 768px) {
            .form-control, .form-label, .submit-button, .alert-success, .alert-danger {
                width: 100%;
            }
        }

        @media (max-width: 480px) {
            .header {
                padding: 15px 20px;
                font-size: 20px;
            }

            .welcome-section h3 {
                font-size: 18px;
            }

            .form-control, .form-label, .submit-button, .alert-success, .alert-danger {
                width: 100%;
            }

            .submit-button {
                height: 50px;
                font-size: 16px;
            }
        }
    </style>

    <!-- Additional JavaScript for dynamic product addition -->
    <script>
        function addProductRow() {
            const productContainer = document.getElementById('products-container');
            const productCount = productContainer.children.length;

            const productRow = document.createElement('div');
            productRow.className = 'product-row border p-3 mb-3';
            productRow.innerHTML = `
                <div class="row">
                    <div class="col-md-5">
                        <label class="form-label" for="product_id_${productCount}">Product <span class="text-danger">*</span></label>
                        <select name="product_id[]" id="product_id_${productCount}" class="form-control" required>
                            <option value="">Select Product</option>
                            <?php
                            // Reset the products_result pointer
                            $products_result->data_seek(0);
                            while($product = $products_result->fetch_assoc()): ?>
                                <option value="<?= htmlspecialchars($product['id']) ?>">
                                    <?= htmlspecialchars($product['name'] . " - " . $product['size'] . " (" . number_format($product['price'], 2) . " KD)") ?>
                                </option>
                            <?php endwhile; ?>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label" for="quantity_${productCount}">Quantity <span class="text-danger">*</span></label>
                        <input type="number" name="quantity[]" id="quantity_${productCount}" class="form-control" min="1" value="1" required>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label" for="packaging_preferences_${productCount}">Packaging Preferences</label>
                        <input type="text" name="packaging_preferences[]" id="packaging_preferences_${productCount}" class="form-control" placeholder="e.g., Gift Wrap">
                    </div>
                    <div class="col-md-1 d-flex align-items-end">
                        <button type="button" class="remove-product-button" onclick="removeProductRow(this)">X</button>
                    </div>
                </div>
            `;
            productContainer.appendChild(productRow);
        }

        function removeProductRow(button) {
            const productRow = button.closest('.product-row');
            productRow.remove();
        }

        // Add initial product row on page load
        document.addEventListener('DOMContentLoaded', function() {
            if (document.getElementById('products-container').children.length === 0) {
                addProductRow();
            }
        });
    </script>

</head>
<body>

    <!-- Header is already included via header.php -->

    <!-- Welcome Section -->
    <div class="welcome-section mt-20">
        <h3>Welcome to the Kaak Alia Management System!</h3>
        <hr>
    </div>

    <!-- Form Container -->
    <div class="form-container">
        <h3>Add New Order</h3>
        <?php if ($success): ?>
            <div class="alert alert-success"><?= htmlspecialchars($success) ?></div>
            <!-- Audio Alert -->
            <audio id="newOrderAudio" src="/includes/you_have_a_new_order.mp3"></audio>
            <script>
                document.addEventListener('DOMContentLoaded', function() {
                    var audio = document.getElementById('newOrderAudio');
                    if (audio) {
                        audio.play().catch(function(error) {
                            console.log('Audio playback failed:', error);
                        });
                    }
                });
            </script>
        <?php endif; ?>
        <?php if ($error): ?>
            <div class="alert alert-danger"><?= htmlspecialchars($error) ?></div>
        <?php endif; ?>
        <form method="POST" action="">
            <!-- Customer Details -->
            <div class="form-section">
                <h5>Customer Details</h5>
                <label class="form-label" for="customer_name">Name <span class="text-danger">*</span></label>
                <input type="text" id="customer_name" name="customer_name" class="form-control" 
                       value="<?= isset($_POST['customer_name']) ? htmlspecialchars($_POST['customer_name']) : '' ?>" 
                       required>
                
                <label class="form-label" for="contact">Contact</label>
                <input type="text" id="contact" name="contact" class="form-control" 
                       value="<?= isset($_POST['contact']) ? htmlspecialchars($_POST['contact']) : '' ?>">
                
                <label class="form-label" for="delivery_address">Delivery Address</label>
                <textarea id="delivery_address" name="delivery_address" class="form-control"><?= isset($_POST['delivery_address']) ? htmlspecialchars($_POST['delivery_address']) : '' ?></textarea>
            </div>
            <hr>
            <!-- Order Details -->
            <div class="form-section">
                <h5>Order Details</h5>
                <div id="products-container">
                    <!-- Product Rows will be added here -->
                </div>
                <button type="button" class="add-product-button" onclick="addProductRow()">Add Another Product</button>
                
                <!-- Delivery DateTime Field -->
                <label class="form-label mt-3" for="delivery_datetime">Delivery Date and Time</label>
                <input type="datetime-local" id="delivery_datetime" name="delivery_datetime" class="form-control" 
                       value="<?= isset($_POST['delivery_datetime']) ? htmlspecialchars($_POST['delivery_datetime']) : '' ?>">
            </div>
            <!-- Submit Button -->
            <button type="submit" class="submit-button">Add Order</button>
        </form>
    </div>

    <!-- Footer -->
    <div class="footer text-center mt-10">
        &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>
</body>
</html>)