fix this 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);

// Start output buffering to prevent "headers already sent" errors
ob_start();

// Include authentication and database connection **before** any output
require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../includes/db_connect.php';

// Helper Functions
/**
 * Sanitize input to prevent XSS and other injections.
 *
 * @param string $input The input string to sanitize.
 * @return string The sanitized string.
 */
function sanitize_input($input) {
    return htmlspecialchars(trim($input));
}

/**
 * Build a query string while preserving existing parameters.
 *
 * @param array $new_params Associative array of new parameters to add or update.
 * @return string The resulting query string.
 */
function build_query($new_params = []) {
    $current_params = $_GET;
    foreach ($new_params as $key => $value) {
        if ($value !== null) { // Only set if value is not null
            $current_params[$key] = $value;
        } else {
            unset($current_params[$key]); // Remove if value is null
        }
    }
    // Remove parameters that shouldn't persist
    unset($current_params['page']);
    $query = http_build_query($current_params);
    return $query ? '?' . $query : '';
}

/**
 * Get the appropriate sort icon based on current sorting.
 *
 * @param string $column The column to check.
 * @param string $current_sort_by The current column being sorted by.
 * @param string $current_sort_order The current sort order ('ASC' or 'DESC').
 * @return string The HTML for the sort icon.
 */
function get_sort_icon($column, $current_sort_by, $current_sort_order) {
    if ($current_sort_by === $column) {
        if ($current_sort_order === 'ASC') {
            return '<i class="bi bi-arrow-up sort-icon"></i>';
        } else {
            return '<i class="bi bi-arrow-down sort-icon"></i>';
        }
    }
    return '';
}

/**
 * Retrieve the price of a product by its ID.
 *
 * @param mysqli $conn The database connection.
 * @param int $product_id The ID of the product.
 * @return string The price formatted to two decimal places.
 */
function get_product_price($conn, $product_id) {
    $price_sql = "SELECT price FROM products WHERE id = ? LIMIT 1";
    $price_stmt = $conn->prepare($price_sql);
    if (!$price_stmt) {
        return "0.00";
    }
    $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;
}

/**
 * Generate a WhatsApp link from a contact number.
 *
 * @param string $contact The contact number.
 * @return string The WhatsApp URL.
 */
function generate_whatsapp_link($contact) {
    // Remove any non-digit characters
    $digits = preg_replace('/\D+/', '', $contact);
    
    // Assuming Egypt country code (+20). Adjust as needed.
    if (substr($digits, 0, 2) !== '20') {
        $digits = '20' . $digits;
    }
    
    return "https://wa.me/" . $digits;
}

// Handle Actions Before Including Header

if (isset($_GET['action'])) {
    if ($_GET['action'] === 'update_status') {
        $order_id = isset($_GET['order_id']) ? intval($_GET['order_id']) : 0;
        $new_status = isset($_GET['new_status']) ? sanitize_input($_GET['new_status']) : '';

        // Validate new_status (Removed 'In Progress')
        $valid_statuses = ['Pending', 'Completed', 'Delivered'];
        if ($order_id > 0 && in_array($new_status, $valid_statuses)) {
            // 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);
                if ($check_stmt) {
                    $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();
                } else {
                    header("Location: view_orders.php?error=2");
                    exit();
                }
            }

            // 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();
        }
    } elseif ($_GET['action'] === 'export') {
        // Implement export functionality here
        // For brevity, this is left as a placeholder
        // You can generate CSV or PDF based on current filters
        // Redirect back with success message
        header("Location: view_orders.php?export=1");
        exit();
    }
}

// Now include the header after handling actions
require_once __DIR__ . '/../../includes/header.php';

// Constants for pagination
define('ORDERS_PER_PAGE_DEFAULT', 10);

// Handle Filters, Sorting, and Pagination
$filter = isset($_GET['filter']) ? sanitize_input($_GET['filter']) : 'all';
$status_filter = isset($_GET['status_filter']) ? sanitize_input($_GET['status_filter']) : 'all';
$search = isset($_GET['search']) ? sanitize_input($_GET['search']) : '';
$sort_by = isset($_GET['sort_by']) ? sanitize_input($_GET['sort_by']) : 'order_date';
$sort_order = isset($_GET['sort_order']) ? sanitize_input($_GET['sort_order']) : 'DESC';
$page = isset($_GET['page']) ? intval($_GET['page']) : 1;
$orders_per_page = isset($_GET['orders_per_page']) ? intval($_GET['orders_per_page']) : ORDERS_PER_PAGE_DEFAULT;

// Validate orders_per_page
$allowed_orders_per_page = [10, 20, 50];
if (!in_array($orders_per_page, $allowed_orders_per_page)) {
    $orders_per_page = ORDERS_PER_PAGE_DEFAULT;
}

// Validate sort_by and sort_order
$allowed_sort_columns = ['order_id', 'customer_name', 'contact', 'status', 'order_date', 'delivery_datetime'];
if (!in_array($sort_by, $allowed_sort_columns)) {
    $sort_by = 'order_date';
}

$allowed_sort_orders = ['ASC', 'DESC'];
if (!in_array($sort_order, $allowed_sort_orders)) {
    $sort_order = 'DESC';
}

// Initialize where conditions
$where_conditions = [];
$parameters = [];
$param_types = "";

// Status Filter
if ($status_filter !== 'all') {
    $where_conditions[] = "orders.status = ?";
    $parameters[] = $status_filter;
    $param_types .= "s";
}

// Search Filter
if (!empty($search)) {
    $where_conditions[] = "(customers.name LIKE ? OR orders.id LIKE ? OR customers.contact LIKE ? OR products.name LIKE ?)";
    $search_term = "%" . $search . "%";
    $parameters[] = $search_term;
    $parameters[] = $search_term;
    $parameters[] = $search_term;
    $parameters[] = $search_term;
    $param_types .= "ssss";
}

// Combine all where conditions
$where_clause = "";
if (count($where_conditions) > 0) {
    $where_clause = "WHERE " . implode(" AND ", $where_conditions);
}

// Sorting
// To prevent SQL injection in ORDER BY, use a mapping
$sort_column_map = [
    'order_id' => 'orders.id',
    'customer_name' => 'customers.name',
    'contact' => 'customers.contact',
    'status' => 'orders.status',
    'order_date' => 'orders.order_date',
    'delivery_datetime' => 'orders.delivery_datetime'
];
$sort_column = isset($sort_column_map[$sort_by]) ? $sort_column_map[$sort_by] : 'orders.order_date';
$order_clause = "ORDER BY $sort_column $sort_order";

// Pagination Calculations
$offset = ($page - 1) * $orders_per_page;

// Fetch total number of orders for pagination
$count_sql = "
    SELECT COUNT(*) as total 
    FROM orders
    JOIN customers ON orders.customer_id = customers.id
    JOIN products ON orders.product_id = products.id
    $where_clause
";
$count_stmt = $conn->prepare($count_sql);
if ($count_stmt) {
    if (!empty($where_clause)) {
        $count_stmt->bind_param($param_types, ...$parameters);
    }
    $count_stmt->execute();
    $count_result = $count_stmt->get_result();
    $total_orders = $count_result->fetch_assoc()['total'];
    $count_stmt->close();
} else {
    // Handle SQL preparation error
    die("Failed to prepare the count query: " . $conn->error);
}

// Calculate total pages
$total_pages = ceil($total_orders / $orders_per_page);

// Fetch orders with customer and product details based on filters and sorting
$orders_sql = "
    SELECT 
        orders.id AS order_id,
        orders.status,
        orders.order_date,
        orders.delivery_datetime,
        orders.discount_amount AS order_discount_amount,
        orders.discount_reason AS order_discount_reason,
        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
    $where_clause
    $order_clause
    LIMIT ?, ?
";
$orders_stmt = $conn->prepare($orders_sql);
if ($orders_stmt) {
    // Bind parameters
    if (!empty($where_clause)) {
        // 'i' for offset and limit
        $types = $param_types . "ii";
        $values = array_merge($parameters, [$offset, $orders_per_page]);
        $orders_stmt->bind_param($types, ...$values);
    } else {
        $orders_stmt->bind_param("ii", $offset, $orders_per_page);
    }
    $orders_stmt->execute();
    $orders_result = $orders_stmt->get_result();
} else {
    // Handle SQL preparation error
    die("Failed to prepare the orders query: " . $conn->error);
}

// Fetch order items for all orders
$order_ids = [];
if ($orders_result && $orders_result->num_rows > 0) {
    while ($order = $orders_result->fetch_assoc()) {
        $order_ids[] = $order['order_id'];
    }
    // Reset result pointer
    $orders_result->data_seek(0);
}

$order_items = [];
if (!empty($order_ids)) {
    $placeholders = implode(',', array_fill(0, count($order_ids), '?'));
    $types = str_repeat('i', count($order_ids));
    $order_items_sql = "
        SELECT 
            order_items.order_id,
            order_items.product_id,
            order_items.quantity,
            order_items.packaging_preferences,
            order_items.discount_amount,
            order_items.discount_reason,
            products.name AS product_name,
            products.size AS product_size,
            products.price AS product_price
        FROM 
            order_items
        JOIN 
            products ON order_items.product_id = products.id
        WHERE
            order_items.order_id IN ($placeholders)
    ";
    $items_stmt = $conn->prepare($order_items_sql);
    if ($items_stmt) {
        $items_stmt->bind_param($types, ...$order_ids);
        $items_stmt->execute();
        $order_items_result = $items_stmt->get_result();
        while ($item = $order_items_result->fetch_assoc()) {
            $order_items[$item['order_id']][] = $item;
        }
        $items_stmt->close();
    }
}
?>
<!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">
    
    <!-- Flatpickr CSS for Date Picker -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">
    
    <!-- Embedded Custom Styles -->
    <style>
        /* ---------------------------------------------------
           1. CSS Variables for Consistent Design
        ---------------------------------------------------- */
        :root {
            --dark-gray: #343a40; /* Header and Footer Background */
            --soft-brown: #FFD166; /* Button Background */
            --blue: #118AB2; /* Primary Button */
            --green: #06D6A0; /* Success Button */
            --orange: #F4A261; /* Warning Button */
            --red-pink: #EF476F; /* Danger 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
        ---------------------------------------------------- */
        .container-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: 28px;
            font-weight: bold;
            box-shadow: 0 4px 6px var(--hover-shadow);
            position: sticky;
            top: 0;
            z-index: 1000;
        }

        /* ---------------------------------------------------
           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;
            box-shadow: 0 4px 6px var(--hover-shadow);
            animation: fadeIn 0.5s ease-in-out;
        }

        .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;
            box-shadow: 0 4px 6px var(--hover-shadow);
            animation: fadeIn 0.5s ease-in-out;
        }

        @keyframes fadeIn {
            from { opacity: 0; }
            to { opacity: 1; }
        }

        /* ---------------------------------------------------
           5. Recent Orders Section
        ---------------------------------------------------- */
        .recent-orders-section {
            background-color: var(--white);
            border-radius: 8px;
            padding: 20px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
            max-width: 1200px;
            margin: 0 auto;
        }

        .recent-orders-section h4 {
            font-size: 24px;
            font-weight: bold;
            color: var(--main-text);
            margin-bottom: 20px;
            border-bottom: 2px solid var(--divider-color);
            padding-bottom: 10px;
        }

        /* ---------------------------------------------------
           6. Filter and Search Section
        ---------------------------------------------------- */
        .filter-search-section {
            margin-bottom: 20px;
            display: flex;
            flex-wrap: wrap;
            justify-content: space-between;
            align-items: center;
        }

        .filter-search-section .search-group,
        .filter-search-section .status-filter-group,
        .filter-search-section .entries-per-page-group {
            display: flex;
            align-items: center;
            margin-bottom: 10px;
        }

        /* Enhanced Styles for Search Input */
        .filter-search-section .search-group input {
            margin-left: 5px;
            width: 250px;
            padding: 8px 12px;
            border-radius: 5px;
            border: 1px solid #ced4da;
            transition: border-color var(--transition-speed), box-shadow var(--transition-speed);
        }

        .filter-search-section .search-group input:focus {
            border-color: var(--blue);
            box-shadow: 0 0 5px rgba(17, 138, 178, 0.5);
            outline: none;
        }

        /* Enhanced Styles for Search Button */
        .filter-search-section .search-group button {
            background-color: var(--blue);
            color: var(--white);
            border: none;
            padding: 8px 16px;
            border-radius: 5px;
            margin-left: 10px;
            cursor: pointer;
            transition: background-color var(--transition-speed), transform var(--transition-speed);
            display: flex;
            align-items: center;
            font-size: 14px;
        }

        .filter-search-section .search-group button:hover {
            background-color: var(--dark-gray);
            transform: scale(1.05);
        }

        /* Status Filter Dropdown */
        .filter-search-section .status-filter-group label {
            margin-left: 5px;
            margin-right: 10px;
            font-weight: 600;
        }

        .filter-search-section .status-filter-group select {
            padding: 8px 12px;
            border-radius: 5px;
            border: 1px solid #ced4da;
            transition: border-color var(--transition-speed), box-shadow var(--transition-speed);
            width: 150px;
        }

        .filter-search-section .status-filter-group select:focus {
            border-color: var(--blue);
            box-shadow: 0 0 5px rgba(17, 138, 178, 0.5);
            outline: none;
        }

        /* Entries Per Page Dropdown */
        .filter-search-section .entries-per-page-group label {
            margin-left: 5px;
            margin-right: 10px;
            font-weight: 600;
        }

        .filter-search-section .entries-per-page-group select {
            padding: 8px 12px;
            border-radius: 5px;
            border: 1px solid #ced4da;
            transition: border-color var(--transition-speed), box-shadow var(--transition-speed);
            width: 100px;
        }

        .filter-search-section .entries-per-page-group select:focus {
            border-color: var(--blue);
            box-shadow: 0 0 5px rgba(17, 138, 178, 0.5);
            outline: none;
        }

        /* ---------------------------------------------------
           7. Orders Table Styles
        ---------------------------------------------------- */
        .orders-table {
            width: 100%;
            border-collapse: collapse;
            border: 2px solid var(--dark-gray);
            font-size: 14px;
        }

        .orders-table th, 
        .orders-table td {
            padding: 12px;
            text-align: left;
            font-size: 14px;
            vertical-align: middle;
            border: 1px solid var(--dark-gray);
        }

        .orders-table th {
            background-color: var(--dark-gray);
            color: var(--white);
            font-weight: bold;
            cursor: pointer;
            position: relative;
            user-select: none;
            transition: background-color var(--transition-speed);
        }

        .orders-table th:hover {
            background-color: var(--blue);
        }

        .orders-table th .sort-icon {
            margin-left: 5px;
            font-size: 12px;
        }

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

        .orders-table tbody tr:hover {
            background-color: #e6f7ff;
        }

        .orders-table td {
            font-size: 14px;
            color: var(--main-text);
            position: relative;
        }

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

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

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

        /* Highlight New Order IDs */
        .new-order {
            color: #EF476F;
            font-weight: bold;
        }

        /* ---------------------------------------------------
           8. 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;
            box-shadow: 0 4px 6px var(--hover-shadow);
            max-width: 1000px;
            margin-left: auto;
            margin-right: auto;
        }

        /* ---------------------------------------------------
           9. Responsive Design Enhancements
        ---------------------------------------------------- */
        @media (max-width: 992px) {
            .filter-search-section {
                flex-direction: column;
                align-items: flex-start;
            }

            .filter-search-section .search-group,
            .filter-search-section .status-filter-group,
            .filter-search-section .entries-per-page-group {
                width: 100%;
                justify-content: space-between;
            }

            .orders-table th, .orders-table td {
                font-size: 14px;
            }
        }

        @media (max-width: 768px) {
            /* Adjust filter and search input widths */
            .filter-search-section .search-group input,
            .filter-search-section .status-filter-group select,
            .filter-search-section .entries-per-page-group select {
                width: 100%;
                margin: 5px 0;
            }

            .filter-search-section .search-group button {
                width: 100%;
                margin-top: 10px;
            }

            .orders-table {
                display: block;
                overflow-x: auto;
                white-space: nowrap;
            }

            .orders-table th, .orders-table td {
                padding: 8px;
            }
        }

        /* ---------------------------------------------------
           10. Action Buttons Styles
        ---------------------------------------------------- */
        .action-buttons {
            display: flex;
            gap: 10px;
            flex-wrap: wrap;
        }

        .action-buttons .btn {
            padding: 8px 16px;
            font-size: 14px;
            border-radius: 5px;
            transition: transform var(--transition-speed);
        }

        .action-buttons .btn-edit {
            background-color: var(--blue);
            color: var(--white);
            border: none;
        }

        .action-buttons .btn-edit:hover {
            background-color: var(--dark-gray);
            color: var(--white);
            transform: scale(1.05);
        }

        .action-buttons .btn-change-status {
            background-color: var(--orange);
            color: var(--white);
            border: none;
        }

        .action-buttons .btn-change-status:hover {
            background-color: #e07a5f;
            color: var(--white);
            transform: scale(1.05);
        }

        .action-buttons .btn-delete {
            background-color: var(--red-pink);
            color: var(--white);
            border: none;
        }

        .action-buttons .btn-delete:hover {
            background-color: #c82333;
            color: var(--white);
            transform: scale(1.05);
        }

        /* ---------------------------------------------------
           11. Pagination Styles
        ---------------------------------------------------- */
        .pagination .page-link {
            border-radius: 50%;
            width: 40px;
            height: 40px;
            line-height: 38px;
            text-align: center;
            font-size: 14px;
            color: var(--blue);
            border: 1px solid var(--blue);
            transition: background-color var(--transition-speed), color var(--transition-speed);
        }

        .pagination .page-item.active .page-link {
            background-color: var(--blue);
            color: var(--white);
            border-color: var(--blue);
        }

        .pagination .page-link:hover {
            background-color: var(--blue);
            color: var(--white);
        }

        .pagination .page-item.disabled .page-link {
            color: #6c757d;
            background-color: transparent;
            border-color: #dee2e6;
            cursor: not-allowed;
        }

        /* ---------------------------------------------------
           12. Action Modal Styles
        ---------------------------------------------------- */
        /* Adjust modal size if needed */
        .modal-dialog {
            max-width: 500px;
        }

        /* ---------------------------------------------------
           13. Change Status Controls Styles
        ---------------------------------------------------- */
        .change-status-controls {
            display: none;
            flex-direction: column;
            gap: 10px;
        }

        .change-status-controls.active {
            display: flex;
        }

        .change-status-controls .form-select {
            width: 100%;
            padding: 8px 12px;
            border-radius: 5px;
            border: 1px solid #ced4da;
            transition: border-color var(--transition-speed), box-shadow var(--transition-speed);
        }

        .change-status-controls .form-select:focus {
            border-color: var(--blue);
            box-shadow: 0 0 5px rgba(17, 138, 178, 0.5);
            outline: none;
        }

        .change-status-controls .action-buttons-inline {
            display: flex;
            gap: 10px;
        }

        .change-status-controls .action-buttons-inline .btn {
            flex: 1;
        }

        /* ---------------------------------------------------
           14. Discount Styles
        ---------------------------------------------------- */
        .discount-info {
            margin-top: 5px;
            font-size: 12px;
            color: var(--red-pink);
        }
    </style>
</head>
<body>
<main class='container-body'>
    <!-- Header Already Included via header.php -->

    <!-- Display Success or Error Messages -->
    <?php if (isset($_GET['success']) && $_GET['success'] == 1): ?>
        <div class="alert alert-success alert-success-custom" role="alert">
            Order status updated successfully!
        </div>
    <?php endif; ?>

    <?php if (isset($_GET['error'])): ?>
        <?php if ($_GET['error'] == 1): ?>
            <div class="alert alert-danger alert-error-custom" role="alert">
                Failed to update order status.
            </div>
        <?php elseif ($_GET['error'] == 2): ?>
            <div class="alert alert-danger alert-error-custom" role="alert">
                Invalid status selected.
            </div>
        <?php elseif ($_GET['error'] == 3): ?>
            <div class="alert alert-danger alert-error-custom" role="alert">
                Cannot set status to Delivered without a delivery date and time.
            </div>
        <?php endif; ?>
    <?php endif; ?>

    <?php if (isset($_GET['export']) && $_GET['export'] == 1): ?>
        <div class="alert alert-success alert-success-custom" role="alert">
            Orders exported successfully!
        </div>
    <?php endif; ?>

    <!-- Container -->
    <div class="recent-orders-section">
        <h4>All Orders</h4>

        <!-- Filter and Search Section -->
        <div class="filter-search-section">
            <!-- Status Filter Dropdown -->
            <div class="status-filter-group mb-3">
                <label for="statusFilter"><i class="bi bi-filter-circle"></i> Status:</label>
                <select id="statusFilter" onchange="applyFilters()">
                    <option value="all" <?php echo $status_filter === 'all' ? 'selected' : ''; ?>>All</option>
                    <option value="Pending" <?php echo $status_filter === 'Pending' ? 'selected' : ''; ?>>Pending</option>
                    <option value="Completed" <?php echo $status_filter === 'Completed' ? 'selected' : ''; ?>>Completed</option>
                    <option value="Delivered" <?php echo $status_filter === 'Delivered' ? 'selected' : ''; ?>>Delivered</option>
                </select>
            </div>

            <!-- Search Group -->
            <div class="search-group mb-3">
                <form method="GET" action="view_orders.php" class="d-flex align-items-center flex-wrap">
                    <input type="hidden" name="filter" value="<?php echo htmlspecialchars($filter); ?>">
                    <input type="hidden" name="status_filter" value="<?php echo htmlspecialchars($status_filter); ?>">
                    <input type="hidden" name="from_date" value="<?php echo htmlspecialchars($from_date ?? ''); ?>">
                    <input type="hidden" name="to_date" value="<?php echo htmlspecialchars($to_date ?? ''); ?>">
                    <input type="hidden" name="sort_by" value="<?php echo htmlspecialchars($sort_by); ?>">
                    <input type="hidden" name="sort_order" value="<?php echo htmlspecialchars($sort_order); ?>">
                    <input type="text" name="search" class="form-control me-2 mb-2" placeholder="🔍 Search by Customer, ID, or Contact" value="<?php echo htmlspecialchars($search); ?>">
                    <button type="submit" class="btn btn-primary mb-2"><i class="bi bi-search"></i> Search</button>
                    <?php if (!empty($search)): ?>
                        <a href="view_orders.php<?php echo build_query(['search' => null, 'page' => 1]); ?>" class="btn btn-outline-secondary ms-2 mb-2 btn-reset"><i class="bi bi-x-circle"></i> Reset</a>
                    <?php endif; ?>
                </form>
            </div>

            <!-- Entries Per Page Dropdown -->
            <div class="entries-per-page-group mb-3">
                <label for="entriesPerPage"><i class="bi bi-layout-split"></i> Show:</label>
                <select id="entriesPerPage" onchange="applyFilters()">
                    <option value="10" <?php echo $orders_per_page == 10 ? 'selected' : ''; ?>>10</option>
                    <option value="20" <?php echo $orders_per_page == 20 ? 'selected' : ''; ?>>20</option>
                    <option value="50" <?php echo $orders_per_page == 50 ? 'selected' : ''; ?>>50</option>
                </select>
                <span class="ms-2">entries</span>
            </div>
        </div>

        <!-- Pagination Information -->
        <div class="pagination-info">
            <?php
                if ($total_orders == 0) {
                    $start_order = 0;
                    $end_order = 0;
                } else {
                    $start_order = ($page - 1) * $orders_per_page + 1;
                    $end_order = min($page * $orders_per_page, $total_orders);
                }
                echo "Showing <strong>$start_order</strong> to <strong>$end_order</strong> of <strong>$total_orders</strong> orders";
            ?>
        </div>

        <!-- Orders Table -->
        <div class="table-responsive">
            <table class="table table-hover orders-table">
                <thead>
                    <tr>
                        <th onclick="sortTable('order_id')">
                            ID
                            <?php echo get_sort_icon('order_id', $sort_by, $sort_order); ?>
                        </th>
                        <th onclick="sortTable('customer_name')">
                            Customer
                            <?php echo get_sort_icon('customer_name', $sort_by, $sort_order); ?>
                        </th>
                        <th>
                            Contact
                        </th>
                        <th>Delivery Address</th>
                        <th>Primary Product</th>
                        <th>Additional Products</th>
                        <th onclick="sortTable('status')">
                            Status
                            <?php echo get_sort_icon('status', $sort_by, $sort_order); ?>
                        </th>
                        <th onclick="sortTable('order_date')">
                            Order Date
                            <?php echo get_sort_icon('order_date', $sort_by, $sort_order); ?>
                        </th>
                        <th onclick="sortTable('delivery_datetime')">
                            Delivery DateTime
                            <?php echo get_sort_icon('delivery_datetime', $sort_by, $sort_order); ?>
                        </th>
                    </tr>
                </thead>
                <tbody>
                    <?php if ($orders_result && $orders_result->num_rows > 0): ?>
                        <?php while($order = $orders_result->fetch_assoc()): ?>
                            <?php
                                // Check if the order is new (created within last 24 hours)
                                $is_new = (strtotime($order['order_date']) >= strtotime('-1 day'));
                                // Generate WhatsApp link
                                $whatsapp_link = generate_whatsapp_link($order['contact']);
                                // Determine badge class based on status
                                switch ($order['status']) {
                                    case 'Pending':
                                        $badge_class = 'badge-status-pending';
                                        break;
                                    case 'Completed':
                                        $badge_class = 'badge-status-completed';
                                        break;
                                    case 'Delivered':
                                        $badge_class = 'badge-status-delivered';
                                        break;
                                    default:
                                        $badge_class = 'badge bg-light text-dark';
                                }

                                // Calculate total for primary product
                                $primary_price = floatval(get_product_price($conn, $order['primary_product_id']));
                                $primary_quantity = intval($order['primary_quantity']);
                                $primary_total = ($primary_price * $primary_quantity) - floatval($order['order_discount_amount']);
                                $primary_total = $primary_total < 0 ? 0 : $primary_total;
                            ?>
                            <!-- Main Row -->
                            <tr onclick="toggleActions(<?= htmlspecialchars($order['order_id']) ?>)" data-bs-toggle="collapse" data-bs-target="#actionRow<?= htmlspecialchars($order['order_id']) ?>" aria-expanded="false" aria-controls="actionRow<?= htmlspecialchars($order['order_id']) ?>" style="cursor: pointer;">
                                <td class="<?php echo $is_new ? 'new-order' : ''; ?>">
                                    <?= htmlspecialchars($order['order_id']) ?>
                                    <?php if ($is_new): ?>
                                        <span class="badge bg-warning text-dark ms-1"><i class="bi bi-star-fill"></i> New</span>
                                    <?php endif; ?>
                                </td>
                                <td>
                                    <?= htmlspecialchars($order['customer_name']) ?>
                                </td>
                                <td>
                                    <a href="<?= $whatsapp_link ?>" target="_blank" class="text-decoration-none">
                                        <i class="bi bi-whatsapp" style="color: #25D366;"></i> <?= htmlspecialchars($order['contact']) ?>
                                    </a>
                                </td>
                                <td><?= nl2br(htmlspecialchars($order['delivery_address'])) ?></td>
                                <td>
                                    <strong><?= htmlspecialchars($order['primary_product_name']) ?></strong><br>
                                    Size: <?= htmlspecialchars($order['primary_product_size']) ?><br>
                                    Price: <?= number_format($primary_price, 2) ?> KD<br>
                                    Qty: <?= htmlspecialchars($order['primary_quantity']) ?><br>
                                    Packaging: <?= htmlspecialchars($order['primary_packaging_preferences']) ?><br>
                                    <?php if (floatval($order['order_discount_amount']) > 0): ?>
                                        <div class="discount-info">
                                            <i class="bi bi-tag"></i> Discount: <?= number_format(floatval($order['order_discount_amount']), 2) ?> KD<br>
                                            <small>Reason: <?= htmlspecialchars($order['order_discount_reason']) ?></small>
                                        </div>
                                    <?php endif; ?>
                                    <strong>Total: <?= number_format($primary_total, 2) ?> KD</strong>
                                </td>
                                <td>
                                    <?php
                                        if (isset($order_items[$order['order_id']])) {
                                            $additional_html = "";
                                            foreach ($order_items[$order['order_id']] as $item) {
                                                $item_price = floatval($item['product_price']);
                                                $item_quantity = intval($item['quantity']);
                                                $item_total = ($item_price * $item_quantity) - floatval($item['discount_amount']);
                                                $item_total = $item_total < 0 ? 0 : $item_total;

                                                $additional_html .= "<strong>" . htmlspecialchars($item['product_name']) . "</strong><br>";
                                                $additional_html .= "Size: " . htmlspecialchars($item['product_size']) . "<br>";
                                                $additional_html .= "Price: " . number_format($item_price, 2) . " KD<br>";
                                                $additional_html .= "Qty: " . htmlspecialchars($item_quantity) . "<br>";
                                                $additional_html .= "Packaging: " . htmlspecialchars($item['packaging_preferences']) . "<br>";
                                                if (floatval($item['discount_amount']) > 0) {
                                                    $additional_html .= "<div class='discount-info'>";
                                                    $additional_html .= "<i class='bi bi-tag'></i> Discount: " . number_format(floatval($item['discount_amount']), 2) . " KD<br>";
                                                    $additional_html .= "<small>Reason: " . htmlspecialchars($item['discount_reason']) . "</small>";
                                                    $additional_html .= "</div>";
                                                }
                                                $additional_html .= "<strong>Total: " . number_format($item_total, 2) . " KD</strong><br><br>";
                                            }
                                        } else {
                                            $additional_html = "<span class='text-muted'>None</span>";
                                        }
                                        echo $additional_html;
                                    ?>
                                </td>
                                <td>
                                    <span class="<?= $badge_class ?>"><i class="bi-<?php 
                                        switch ($order['status']) {
                                            case 'Pending':
                                                echo 'hourglass-split';
                                                break;
                                            case 'Completed':
                                                echo 'check-circle-fill';
                                                break;
                                            case 'Delivered':
                                                echo 'box-seam';
                                                break;
                                            default:
                                                echo 'info-circle';
                                        }
                                    ?>"></i> <?= htmlspecialchars($order['status']) ?></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>
                            </tr>
                            <!-- Action Buttons Row (Hidden by Default) -->
                            <tr id="actionRow<?= htmlspecialchars($order['order_id']) ?>" class="collapse">
                                <td colspan="9">
                                    <div class="action-buttons">
                                        <!-- Edit Order -->
                                        <button class="btn btn-edit" onclick="event.stopPropagation(); editOrder(<?= htmlspecialchars($order['order_id']) ?>)" title="Edit Order" data-bs-toggle="tooltip" data-bs-placement="top" data-bs-original-title="Edit Order">
                                            <i class="bi bi-pencil-fill"></i> Edit
                                        </button>
                                        
                                        <!-- Change Status -->
                                        <button class="btn btn-change-status" onclick="event.stopPropagation(); toggleChangeStatus(<?= htmlspecialchars($order['order_id']) ?>)" title="Change Status" data-bs-toggle="tooltip" data-bs-placement="top" data-bs-original-title="Change Status">
                                            <i class="bi bi-arrow-repeat"></i> Change Status
                                        </button>
                                        
                                        <!-- Delete Order -->
                                        <button class="btn btn-delete" onclick="event.stopPropagation(); confirmDelete(<?= htmlspecialchars($order['order_id']) ?>)" title="Delete Order" data-bs-toggle="tooltip" data-bs-placement="top" data-bs-original-title="Delete Order">
                                            <i class="bi bi-trash-fill"></i> Delete
                                        </button>
                                            <!-- View Customer Order -->
                                            <a href="view_order_customer.php?order_id=<?= htmlspecialchars($order['order_id']) ?>" class="btn btn-info" title="View Customer Order" data-bs-toggle="tooltip" data-bs-placement="top" data-bs-original-title="View Customer Order">
                                                <i class="bi bi-eye-fill"></i> View Customer Order
                                            </a>

                                    </div>
                                    
                                    <!-- Change Status Controls -->
                                    <div class="change-status-controls" id="changeStatusControls<?= htmlspecialchars($order['order_id']) ?>">
                                        <label for="newStatus<?= htmlspecialchars($order['order_id']) ?>" class="form-label"><i class="bi bi-arrow-repeat"></i> Change Order Status</label>
                                        <select class="form-select" id="newStatus<?= htmlspecialchars($order['order_id']) ?>" onchange="validateChangeStatus(<?= htmlspecialchars($order['order_id']) ?>)">
                                            <option value="" selected disabled>Choose status...</option>
                                            <option value="Pending">Pending</option>
                                            <option value="Completed">Completed</option>
                                            <option value="Delivered">Delivered</option>
                                        </select>
                                        <div class="action-buttons-inline">
                                            <button class="btn btn-primary" id="confirmChangeStatusBtn<?= htmlspecialchars($order['order_id']) ?>" onclick="confirmChangeStatus(<?= htmlspecialchars($order['order_id']) ?>)" disabled>Change Status</button>
                                            <button class="btn btn-secondary" onclick="cancelChangeStatus(<?= htmlspecialchars($order['order_id']) ?>)">Cancel</button>
                                        </div>
                                    </div>
                                </td>
                            </tr>
                        <?php endwhile; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="9" class="text-center">No orders found.</td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>

        <!-- Pagination -->
        <?php if ($total_pages > 1): ?>
            <nav aria-label="Order pagination">
                <ul class="pagination justify-content-center">
                    <!-- Previous -->
                    <li class="page-item <?php echo ($page <= 1) ? 'disabled' : ''; ?>">
                        <a class="page-link" href="view_orders.php<?php
                            $prev_page = max($page - 1, 1);
                            $query_params = array_merge($_GET, ['page' => $prev_page]);
                            echo http_build_query($query_params) ? '?' . http_build_query($query_params) : '';
                        ?>" tabindex="-1" aria-disabled="<?php echo ($page <= 1) ? 'true' : 'false'; ?>">Previous</a>
                    </li>

                    <!-- Page Numbers -->
                    <?php
                        // Determine the range of pages to show
                        $max_links = 5;
                        $start = max($page - floor($max_links / 2), 1);
                        $end = min($start + $max_links - 1, $total_pages);
                        if ($end - $start < $max_links - 1) {
                            $start = max($end - $max_links + 1, 1);
                        }
                    ?>
                    <?php for ($i = $start; $i <= $end; $i++): ?>
                        <li class="page-item <?php echo ($page == $i) ? 'active' : ''; ?>">
                            <a class="page-link" href="view_orders.php<?php
                                $query_params = array_merge($_GET, ['page' => $i]);
                                echo http_build_query($query_params) ? '?' . http_build_query($query_params) : '';
                            ?>"><?php echo $i; ?></a>
                        </li>
                    <?php endfor; ?>

                    <!-- Next -->
                    <li class="page-item <?php echo ($page >= $total_pages) ? 'disabled' : ''; ?>">
                        <a class="page-link" href="view_orders.php<?php
                            $next_page = min($page + 1, $total_pages);
                            $query_params = array_merge($_GET, ['page' => $next_page]);
                            echo http_build_query($query_params) ? '?' . http_build_query($query_params) : '';
                        ?>">Next</a>
                    </li>
                </ul>
            </nav>
        <?php endif; ?>

    </div>

    <!-- Footer -->
    <div class="footer">
        &copy; 2024 Kaak Alia Bakery. All rights reserved.
    </div>
</main>
    <!-- Bootstrap JS Bundle -->
    <script src="/kaak_alia_management_system/assets/bootstrap/js/bootstrap.bundle.min.js"></script>
    
    <!-- Flatpickr JS for Date Picker -->
    <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>

    <!-- Bootstrap Tooltip Initialization -->
    <script>
        var tooltipTriggerList = [].slice.call(document.querySelectorAll('[data-bs-toggle="tooltip"]'))
        var tooltipList = tooltipTriggerList.map(function (tooltipTriggerEl) {
            return new bootstrap.Tooltip(tooltipTriggerEl)
        })
    </script>

    <!-- Custom JS for Sorting, Deletion, and Filtering -->
    <script>
        // Initialize Flatpickr for date inputs if present
        if (document.getElementById('from_date')) {
            flatpickr("#from_date", {
                dateFormat: "Y-m-d",
                maxDate: "today"
            });
        }

        if (document.getElementById('to_date')) {
            flatpickr("#to_date", {
                dateFormat: "Y-m-d",
                maxDate: "today"
            });
        }

        // Function to handle sorting
        function sortTable(column) {
            const urlParams = new URLSearchParams(window.location.search);
            let currentSortBy = urlParams.get('sort_by') || 'order_date';
            let currentSortOrder = urlParams.get('sort_order') || 'DESC';

            if (currentSortBy === column) {
                // Toggle sort order
                currentSortOrder = currentSortOrder === 'ASC' ? 'DESC' : 'ASC';
            } else {
                currentSortBy = column;
                currentSortOrder = 'ASC';
            }

            urlParams.set('sort_by', currentSortBy);
            urlParams.set('sort_order', currentSortOrder);
            // Reset to first page if pagination is implemented
            urlParams.set('page', 1);

            window.location.search = urlParams.toString();
        }

        // Function to confirm deletion using Bootstrap Modal
        function confirmDelete(orderId) {
            // Create modal if it doesn't exist
            if (!document.getElementById('deleteModal')) {
                const modalHTML = `
                <div class="modal fade" id="deleteModal" tabindex="-1" aria-labelledby="deleteModalLabel" aria-hidden="true">
                  <div class="modal-dialog modal-dialog-centered">
                    <div class="modal-content">
                      <div class="modal-header">
                        <h5 class="modal-title" id="deleteModalLabel"><i class="bi bi-exclamation-triangle-fill text-danger"></i> Confirm Deletion</h5>
                        <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                      </div>
                      <div class="modal-body">
                        Are you sure you want to delete this order? This action cannot be undone.
                      </div>
                      <div class="modal-footer">
                        <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                        <button type="button" class="btn btn-danger" id="confirmDeleteBtn">Delete</button>
                      </div>
                    </div>
                  </div>
                </div>
                `;
                document.body.insertAdjacentHTML('beforeend', modalHTML);
            }

            // Set up the delete action
            document.getElementById('confirmDeleteBtn').onclick = function() {
                window.location.href = 'delete_order.php?order_id=' + orderId;
            };

            // Show the modal
            var deleteModal = new bootstrap.Modal(document.getElementById('deleteModal'));
            deleteModal.show();
        }

        // Function to toggle Change Status Controls
        function toggleChangeStatus(orderId) {
            const controls = document.getElementById('changeStatusControls' + orderId);
            const originalButtons = controls.previousElementSibling; // action-buttons div

            if (controls.classList.contains('active')) {
                controls.classList.remove('active');
                originalButtons.style.display = 'flex';
            } else {
                // Hide other change status controls
                document.querySelectorAll('.change-status-controls.active').forEach(function(el) {
                    el.classList.remove('active');
                    const siblings = el.previousElementSibling;
                    if (siblings) {
                        siblings.style.display = 'flex';
                    }
                });
                controls.classList.add('active');
                originalButtons.style.display = 'none';
            }
        }

        // Function to confirm status change
        function confirmChangeStatus(orderId) {
            const newStatus = document.getElementById('newStatus' + orderId).value;
            if (newStatus) {
                window.location.href = `view_orders.php?action=update_status&order_id=${orderId}&new_status=${newStatus}`;
            }
        }

        // Function to cancel status change
        function cancelChangeStatus(orderId) {
            const controls = document.getElementById('changeStatusControls' + orderId);
            const originalButtons = controls.previousElementSibling; // action-buttons div

            controls.classList.remove('active');
            originalButtons.style.display = 'flex';
        }

        // Function to validate Change Status button
        function validateChangeStatus(orderId) {
            const newStatus = document.getElementById('newStatus' + orderId).value;
            const confirmBtn = document.getElementById('confirmChangeStatusBtn' + orderId);
            if (newStatus) {
                confirmBtn.disabled = false;
            } else {
                confirmBtn.disabled = true;
            }
        }

        // Function to edit order
        function editOrder(orderId) {
            window.location.href = `edit_order.php?id=${orderId}`;
        }

        // Function to apply filters (on status change and records per page)
        function applyFilters() {
            const urlParams = new URLSearchParams(window.location.search);
            const statusFilter = document.getElementById('statusFilter').value;
            const ordersPerPage = document.getElementById('entriesPerPage').value;

            urlParams.set('status_filter', statusFilter);
            urlParams.set('orders_per_page', ordersPerPage);
            urlParams.set('page', 1); // Reset to first page

            window.location.search = urlParams.toString();
        }

        // Close action rows when clicking outside the table
        document.addEventListener('click', function(event) {
            const isClickInside = event.target.closest('.orders-table');
            if (!isClickInside) {
                document.querySelectorAll('.change-status-controls.active').forEach(function(el) {
                    el.classList.remove('active');
                    const siblings = el.previousElementSibling;
                    if (siblings) {
                        siblings.style.display = 'flex';
                    }
                });
            }
        });
    </script>
</body>
</html>
<?php
// Flush the output buffer
ob_end_flush();
// No closing PHP tag to prevent unwanted output
?>) based on info database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 09, 2024 at 12:13 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,
  `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
) 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 DEFAULT 1,
  `packaging_preferences` varchar(255) DEFAULT NULL,
  `discount_percentage` decimal(5,2) DEFAULT 0.00,
  `discount_reason` 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,
  `thumbnail` varchar(255) 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;

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

INSERT INTO `users` (`id`, `username`, `password`, `role`) VALUES
(1, 'kaakalia', '$2y$10$tEdSPRwL8WTXljuAxNh.m.af/8sHd/ouDB21Bgs1bC7GkbRq/h0D6', '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 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, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE;

--
-- 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 */;) and this header (<?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 o.id 
    FROM orders o
    WHERE o.status = 'Pending' 
    ORDER BY o.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 Bundle with Popper (Loaded Defer for Performance) -->
    <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">

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

    <!-- 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/img/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 with their items
                                $notifications_sql = "
                                    SELECT o.id, o.order_date, c.name AS customer_name, oi.quantity, p.name AS product_name
                                    FROM orders o
                                    JOIN customers c ON o.customer_id = c.id
                                    JOIN order_items oi ON oi.order_id = o.id
                                    JOIN products p ON oi.product_id = p.id
                                    WHERE o.status = 'Pending'
                                    ORDER BY o.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'], ENT_QUOTES, 'UTF-8'); ?> ordered <?= htmlspecialchars($notification['product_name'], ENT_QUOTES, 'UTF-8'); ?> x<?= htmlspecialchars($notification['quantity'], ENT_QUOTES, 'UTF-8'); ?>
                                        </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'], ENT_QUOTES, 'UTF-8') : '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>
        </header>

    <!-- 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');
                                const existingBadge = bellIcon.parentNode.querySelector('.notification-badge');
                                if (!existingBadge) {
                                    const badge = document.createElement('span');
                                    badge.className = 'notification-badge 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>

    <!-- Remove Redundant Bootstrap JS Inclusion -->
    <!-- <script src="/assets/bootstrap/js/bootstrap.bundle.min.js"></script> -->
</body>
</html>) and so type full codes of view order and make hte next and provious looks amainzing styles and tpye full codes