i want to add Additional Products after Product Name and this codes (<?php
// public/orders/view_order_customer.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';

// Helper Function: Generate WhatsApp link
/**
 * 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 the number already includes country code, do not add '20'
    // For this example, just append '20'
    if (substr($digits, 0, 2) !== '20') {
        $digits = '20' . $digits;
    }
    
    return "https://wa.me/" . $digits;
}

// Check if order_id is given
$order_id = isset($_GET['order_id']) ? intval($_GET['order_id']) : 0;

if ($order_id <= 0) {
    header("Location: view_orders.php?error=6"); // Invalid order ID
    exit();
}

// Fetch order details along with customer and primary product information
$order_sql = "
    SELECT 
        orders.id AS order_id,
        orders.status,
        orders.order_date,
        orders.delivery_datetime,
        orders.discount_amount AS primary_discount_amount,
        orders.discount_reason AS primary_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,
        products.price AS primary_product_price
    FROM 
        orders
    JOIN 
        customers ON orders.customer_id = customers.id
    JOIN 
        products ON orders.product_id = products.id
    WHERE 
        orders.id = ?
";
$order_stmt = $conn->prepare($order_sql);
if (!$order_stmt) {
    die("Prepare failed: (" . $conn->errno . ") " . $conn->error);
}
$order_stmt->bind_param("i", $order_id);
$order_stmt->execute();
$order_result = $order_stmt->get_result();
$order = $order_result->fetch_assoc();
$order_stmt->close();

if (!$order) {
    header("Location: view_orders.php?error=7"); // Order not found
    exit();
}

// Fetch additional order items
$order_items_sql = "
    SELECT 
        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 = ?
";
$item_stmt = $conn->prepare($order_items_sql);
if (!$item_stmt) {
    die("Prepare failed: (" . $conn->errno . ") " . $conn->error);
}
$item_stmt->bind_param("i", $order_id);
$item_stmt->execute();
$order_items_result = $item_stmt->get_result();
$order_items = [];
while ($item = $order_items_result->fetch_assoc()) {
    $order_items[] = $item;
}
$item_stmt->close();

// Calculate totals
// Primary product
$primary_product_total = $order['primary_quantity'] * $order['primary_product_price'];
$primary_discount = floatval($order['primary_discount_amount']);
$primary_total_after_discount = max($primary_product_total - $primary_discount, 0);

// Additional products
$additional_total = 0;
$additional_discount = 0;

foreach ($order_items as $item) {
    $item_total = $item['quantity'] * $item['product_price'];
    $additional_total += $item_total;
    $additional_discount += floatval($item['discount_amount']);
}

// Total before discounts
$total_before_discounts = $primary_product_total + $additional_total;

// Total discounts
$total_discounts = $primary_discount + $additional_discount;

// Final total
$final_total = max($total_before_discounts - $total_discounts, 0);

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Order #<?= htmlspecialchars($order['order_id']) ?> Details - 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">
    
    <!-- Custom Styles -->
    <style>
        /* Global Styles */
        body {
            font-family: 'Cairo', sans-serif;
            background: linear-gradient(to bottom, #f5f7fa, #c3cfe2);
            margin: 0;
            padding: 20px;
            box-sizing: border-box;
        }
        .header-section {
            background-color: #4F3F02;
            color: #fff;
            padding: 25px 40px;
            text-align: center;
            border-radius: 10px;
            font-size: 32px;
            margin-bottom: 35px;
            box-shadow: 0 4px 12px rgba(0,0,0,0.3);
        }
        .order-container {
            display: flex;
            flex-direction: column;
            align-items: center;
            gap: 30px;
        }
        .order-details {
            background-color: #ffffff;
            border-radius: 15px;
            padding: 30px 40px;
            box-shadow: 0 8px 16px rgba(0,0,0,0.2);
            width: 100%;
            max-width: 900px;
        }
        .order-details h4 {
            font-size: 24px;
            font-weight: bold;
            color: #4F3F02;
            margin-bottom: 25px;
            border-bottom: 2px solid #D1C287;
            padding-bottom: 15px;
        }
        .info-section {
            display: flex;
            flex-wrap: wrap;
            justify-content: space-between;
            padding: 12px 0;
            border-bottom: 1px solid #eaeaea;
        }
        .info-section:last-child {
            border-bottom: none;
        }
        .info-label {
            font-weight: bold;
            color: #333;
            flex: 1 1 35%;
            margin-bottom: 5px;
        }
        .info-value {
            color: #555;
            flex: 1 1 60%;
            margin-bottom: 5px;
        }
        .discount-section {
            background-color: #fffae6;
            padding: 12px;
            border-radius: 5px;
            margin-top: 15px;
        }
        .packaging {
            background-color: #e0f7fa;
            padding: 6px 12px;
            border-radius: 4px;
            display: inline-block;
            font-size: 0.95em;
        }
        .btn-back {
            background-color: #4F3F02;
            color: white;
            text-align: center;
            padding: 14px 30px;
            border-radius: 6px;
            text-decoration: none;
            font-weight: bold;
            transition: background-color 0.3s;
            margin-top: 25px;
            box-shadow: 0 6px 10px rgba(0,0,0,0.15);
        }
        .btn-back:hover {
            background-color: #D1C287;
            color: #333;
        }
        .orders-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
        }
        .orders-table th, .orders-table td {
            padding: 12px 15px;
            text-align: left;
            border: 1px solid #dee2e6;
        }
        .orders-table th {
            background-color: #4F3F02;
            color: #fff;
            font-size: 1em;
        }
        .orders-table tr:nth-child(even) {
            background-color: #f9f9f9;
        }
        .orders-table tr:hover {
            background-color: #e6f7ff;
        }
        .total-section {
            background-color: #e8f5e9;
            padding: 15px;
            border-radius: 5px;
            margin-top: 20px;
            font-size: 1.1em;
            font-weight: bold;
        }
        /* Responsive Design */
        @media (max-width: 768px) {
            .info-label, .info-value {
                flex: 1 1 100%;
            }
            .packaging {
                display: block;
                margin-top: 5px;
            }
        }
        /* Currency Symbol Alignment */
        .currency-symbol {
            margin-right: 3px;
            font-weight: bold;
        }
    </style>
</head>
<body>
    <!-- Header Section -->
    <div class="header-section">
        Order #<?= htmlspecialchars($order['order_id']) ?> Details
    </div>
    
    <!-- Order Container -->
    <div class="order-container">
        <!-- Order Information -->
        <div class="order-details">
            <h4>Order Information</h4>
            <div class="info-section">
                <span class="info-label">Status:</span>
                <span class="info-value"><?= htmlspecialchars($order['status']) ?></span>
            </div>
            <div class="info-section">
                <span class="info-label">Order Date:</span>
                <span class="info-value"><?= date("d M Y, h:i A", strtotime($order['order_date'])) ?></span>
            </div>
            <div class="info-section">
                <span class="info-label">Delivery Date & Time:</span>
                <span class="info-value"><?= $order['delivery_datetime'] ? date("d M Y, h:i A", strtotime($order['delivery_datetime'])) : 'N/A' ?></span>
            </div>
            <?php if ($primary_discount > 0): ?>
                <div class="discount-section">
                    <div class="info-section">
                        <span class="info-label">Primary Product Discount:</span>
                        <span class="info-value"><span class="currency-symbol">KD</span><?= number_format($primary_discount, 2) ?></span>
                    </div>
                    <div class="info-section">
                        <span class="info-label">Reason:</span>
                        <span class="info-value"><?= htmlspecialchars($order['primary_discount_reason'] ?? 'N/A') ?></span>
                    </div>
                </div>
            <?php endif; ?>
        </div>

        <!-- Customer Information -->
        <div class="order-details">
            <h4>Customer Information</h4>
            <div class="info-section">
                <span class="info-label">Name:</span>
                <span class="info-value"><?= htmlspecialchars($order['customer_name']) ?></span>
            </div>
            <div class="info-section">
                <span class="info-label">Contact:</span>
                <span class="info-value">
                    <a href="<?= generate_whatsapp_link($order['contact']) ?>" target="_blank">
                        <i class="bi bi-whatsapp" style="color: #25D366;"></i> <?= htmlspecialchars($order['contact']) ?>
                    </a>
                </span>
            </div>
            <div class="info-section">
                <span class="info-label">Delivery Address:</span>
                <span class="info-value"><?= nl2br(htmlspecialchars($order['delivery_address'])) ?></span>
            </div>
        </div>

        <!-- Primary Product Information -->
        <div class="order-details">
            <h4>Primary Product Information</h4>
            <div class="info-section">
                <span class="info-label">Product:</span>
                <span class="info-value"><?= htmlspecialchars($order['primary_product_name']) ?> - <?= htmlspecialchars($order['primary_product_size']) ?></span>
            </div>
            <div class="info-section">
                <span class="info-label">Quantity:</span>
                <span class="info-value"><?= htmlspecialchars($order['primary_quantity']) ?></span>
            </div>
            <div class="info-section">
                <span class="info-label">Price per Unit:</span>
                <span class="info-value"><span class="currency-symbol">KD</span><?= number_format($order['primary_product_price'], 2) ?></span>
            </div>
            <div class="info-section">
                <span class="info-label">Packaging Preferences:</span>
                <span class="info-value"><span class="packaging"><?= htmlspecialchars($order['primary_packaging_preferences']) ?></span></span>
            </div>
            <?php if ($primary_discount > 0): ?>
                <div class="discount-section">
                    <div class="info-section">
                        <span class="info-label">Discount Amount:</span>
                        <span class="info-value"><span class="currency-symbol">KD</span><?= number_format($primary_discount, 2) ?></span>
                    </div>
                    <div class="info-section">
                        <span class="info-label">Reason:</span>
                        <span class="info-value"><?= htmlspecialchars($order['primary_discount_reason']) ?></span>
                    </div>
                </div>
            <?php endif; ?>
            <div class="info-section">
                <span class="info-label">Total for Primary Product:</span>
                <span class="info-value"><span class="currency-symbol">KD</span><?= number_format($primary_total_after_discount, 2) ?></span>
            </div>
        </div>

        <!-- Additional Products -->
        <div class="order-details">
            <h4>Additional Products</h4>
            <?php if (!empty($order_items)): ?>
                <table class="orders-table">
                    <thead>
                        <tr>
                            <th>#</th>
                            <th>Product</th>
                            <th>Size</th>
                            <th>Price per Unit</th>
                            <th>Quantity</th>
                            <th>Packaging Preferences</th>
                            <th>Discount Amount</th>
                            <th>Discount Reason</th>
                            <th>Total</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php foreach ($order_items as $index => $item): ?>
                            <?php
                                $item_total = ($item['quantity'] * $item['product_price']) - floatval($item['discount_amount']);
                                $item_total = $item_total < 0 ? 0 : $item_total;
                            ?>
                            <tr>
                                <td><?= $index + 1 ?></td>
                                <td><?= htmlspecialchars($item['product_name']) ?></td>
                                <td><?= htmlspecialchars($item['product_size']) ?></td>
                                <td><span class="currency-symbol">KD</span><?= number_format($item['product_price'], 2) ?></td>
                                <td><?= htmlspecialchars($item['quantity']) ?></td>
                                <td><span class="packaging"><?= htmlspecialchars($item['packaging_preferences']) ?></span></td>
                                <td><?= floatval($item['discount_amount']) > 0 ? '<span class="currency-symbol">KD</span>' . number_format($item['discount_amount'], 2) : 'N/A' ?></td>
                                <td><?= !empty($item['discount_reason']) ? htmlspecialchars($item['discount_reason']) : 'N/A' ?></td>
                                <td><span class="currency-symbol">KD</span><?= number_format($item_total, 2) ?></td>
                            </tr>
                        <?php endforeach; ?>
                    </tbody>
                </table>
            <?php else: ?>
                <p class="info-value">No additional products.</p>
            <?php endif; ?>
        </div>

        <!-- Order Summary -->
        <div class="order-details">
            <h4>Order Summary</h4>
            <div class="total-section">
                <p>Total Before Discounts: <span style="float:right;"><span class="currency-symbol">KD</span><?= number_format($total_before_discounts, 2) ?></span></p>
                <p>Total Discounts: <span style="float:right;"><span class="currency-symbol">KD</span><?= number_format($total_discounts, 2) ?></span></p>
                <hr>
                <p>Final Amount: <span style="float:right;"><span class="currency-symbol">KD</span><?= number_format($final_total, 2) ?></span></p>
            </div>
        </div>

        <!-- Back to Orders Button -->
        <a href="view_orders.php" class="btn-back"><i class="bi bi-arrow-left-circle"></i> Back to Orders</a>
    </div>
</body>
</html>) to help and so change this (<?php
// public/reports/generate_reports.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 '../../includes/auth.php';
require_once '../../includes/db_connect.php';

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

// Initialize filter variables
$filter_date_from = '';
$filter_date_to = '';
$filter_status = '';

// Initialize conditions and parameters for prepared statements
$conditions = [];
$params = [];
$types = '';

// Handle GET request for filtering
if ($_SERVER['REQUEST_METHOD'] == 'GET') {
    if (!empty($_GET['filter_date_from'])) {
        $filter_date_from = trim($_GET['filter_date_from']);
        $conditions[] = "DATE(orders.order_date) >= ?";
        $params[] = $filter_date_from;
        $types .= 's';
    }
    if (!empty($_GET['filter_date_to'])) {
        $filter_date_to = trim($_GET['filter_date_to']);
        $conditions[] = "DATE(orders.order_date) <= ?";
        $params[] = $filter_date_to;
        $types .= 's';
    }
    if (!empty($_GET['filter_status'])) {
        $filter_status = trim($_GET['filter_status']);
        $conditions[] = "orders.status = ?";
        $params[] = $filter_status;
        $types .= 's';
    }
}

$where = '';
if (count($conditions) > 0) {
    $where = "WHERE " . implode(" AND ", $conditions);
}

// **Handle Export to CSV Before Any Output**
if (isset($_GET['export'])) {
    // Prepare the SQL statement with filters for export
    $export_sql = "SELECT 
                    orders.id AS order_id,
                    orders.status,
                    orders.order_date,
                    orders.delivery_datetime,
                    orders.delivery_date,
                    orders.quantity,
                    orders.packaging_preferences,
                    orders.discount_amount,
                    orders.discount_reason,
                    customers.name AS customer_name,
                    customers.contact AS customer_contact,
                    customers.delivery_address,
                    products.name AS product_name,
                    products.size AS product_size,
                    products.category AS product_category,
                    products.price AS product_price
                   FROM orders 
                   JOIN customers ON orders.customer_id = customers.id 
                   JOIN products ON orders.product_id = products.id 
                   $where 
                   ORDER BY orders.order_date DESC";

    $export_stmt = $conn->prepare($export_sql);
    if ($export_stmt) {
        if (count($params) > 0) {
            $export_stmt->bind_param($types, ...$params);
        }

        if ($export_stmt->execute()) {
            $export_result = $export_stmt->get_result();
            if ($export_result) {
                // Set headers for CSV download
                header('Content-Type: text/csv; charset=utf-8');
                header('Content-Disposition: attachment; filename=orders_report.csv');
                $output = fopen('php://output', 'w');

                // Column headings
                fputcsv($output, [
                    'Order ID',
                    'Status',
                    'Order Date',
                    'Delivery Date & Time',
                    'Delivery Date',
                    'Quantity',
                    'Packaging Preferences',
                    'Product Price (KD)',
                    'Discount Amount (KD)',
                    'Discount Reason',
                    'Subtotal (KD)',
                    'Total Amount (KD)',
                    'Customer Name',
                    'Customer Contact',
                    'Delivery Address',
                    'Product Name',
                    'Product Size',
                    'Product Category'
                ]);

                // Data rows
                if ($export_result->num_rows > 0) {
                    while ($row = $export_result->fetch_assoc()) {
                        // Calculate Subtotal and Total Amount
                        $subtotal = $row['quantity'] * $row['product_price'];
                        $total_amount = $subtotal - $row['discount_amount'];

                        fputcsv($output, [
                            $row['order_id'],
                            $row['status'],
                            $row['order_date'],
                            $row['delivery_datetime'] ? $row['delivery_datetime'] : 'N/A',
                            $row['delivery_date'] ? $row['delivery_date'] : 'N/A',
                            $row['quantity'],
                            $row['packaging_preferences'] ? $row['packaging_preferences'] : 'N/A',
                            number_format((float)$row['product_price'], 2),
                            number_format((float)$row['discount_amount'], 2),
                            $row['discount_reason'] ? $row['discount_reason'] : 'N/A',
                            number_format((float)$subtotal, 2),
                            number_format((float)$total_amount, 2),
                            $row['customer_name'],
                            $row['customer_contact'] ? $row['customer_contact'] : 'N/A',
                            $row['delivery_address'] ? $row['delivery_address'] : 'N/A',
                            $row['product_name'],
                            $row['product_size'] ? $row['product_size'] : 'N/A',
                            $row['product_category'] ? $row['product_category'] : 'N/A'
                        ]);
                    }
                }
                fclose($output);
                exit(); // Ensure no further output is sent
            } else {
                $error = "Failed to retrieve export data.";
            }
        } else {
            $error = "Failed to execute export query.";
        }
    } else {
        $error = "Failed to prepare export query.";
    }
}

// Now include the header after handling export to prevent "headers already sent" issues
require_once __DIR__ . '/../../includes/header.php';

// Prepare the SQL statement with filters for the Orders Table
$orders_sql = "SELECT 
                orders.id AS order_id,
                orders.status,
                orders.order_date,
                orders.delivery_datetime,
                orders.delivery_date,
                orders.quantity,
                orders.packaging_preferences,
                orders.discount_amount,
                orders.discount_reason,
                customers.name AS customer_name,
                products.name AS product_name
               FROM orders 
               JOIN customers ON orders.customer_id = customers.id 
               JOIN products ON orders.product_id = products.id 
               $where 
               ORDER BY orders.order_date DESC";

$stmt = $conn->prepare($orders_sql);
if ($stmt) {
    if (count($params) > 0) {
        $stmt->bind_param($types, ...$params);
    }

    if ($stmt->execute()) {
        $orders_result = $stmt->get_result();
        if (!$orders_result) {
            $error = "Failed to retrieve orders.";
        }
    } else {
        $error = "Failed to execute orders query.";
    }
} else {
    $error = "Failed to prepare orders query.";
}

// Fetch data for Charts

// 1. Orders by Status
$status_sql = "SELECT status, COUNT(*) as count FROM orders $where GROUP BY status";
$status_stmt = $conn->prepare($status_sql);
if ($status_stmt) {
    if (count($params) > 0) {
        $status_stmt->bind_param($types, ...$params);
    }

    if ($status_stmt->execute()) {
        $status_result = $status_stmt->get_result();
        $statuses = [];
        $status_counts = [];

        if ($status_result && $status_result->num_rows > 0) {
            while ($row = $status_result->fetch_assoc()) {
                $statuses[] = $row['status'];
                $status_counts[] = (int)$row['count'];
            }
        }
    } else {
        $error .= " Failed to execute orders by status query.";
    }
} else {
    $error .= " Failed to prepare orders by status query.";
}

// 2. Orders Over Time (Monthly)
$orders_over_time_sql = "
    SELECT DATE_FORMAT(order_date, '%Y-%m') as month, COUNT(*) as count 
    FROM orders 
    $where 
    GROUP BY month 
    ORDER BY month ASC
";
$orders_over_time_stmt = $conn->prepare($orders_over_time_sql);
if ($orders_over_time_stmt) {
    if (count($params) > 0) {
        $orders_over_time_stmt->bind_param($types, ...$params);
    }

    if ($orders_over_time_stmt->execute()) {
        $orders_over_time_result = $orders_over_time_stmt->get_result();
        $months = [];
        $orders_counts = [];

        // Fetch all results
        while ($row = $orders_over_time_result->fetch_assoc()) {
            $months[] = date("M Y", strtotime($row['month'] . "-01"));
            $orders_counts[] = (int)$row['count'];
        }

        // Ensure that all months in the last 12 months are represented
        $all_months = [];
        $all_counts = [];

        for ($i = 11; $i >= 0; $i--) {
            $month = date('Y-m', strtotime("-$i months"));
            $formatted_month = date("M Y", strtotime($month . "-01"));
            if (in_array($formatted_month, $months)) {
                $index = array_search($formatted_month, $months);
                $all_months[] = $formatted_month;
                $all_counts[] = $orders_counts[$index];
            } else {
                $all_months[] = $formatted_month;
                $all_counts[] = 0;
            }
        }

        $months = $all_months;
        $orders_counts = $all_counts;
    } else {
        $error .= " Failed to execute orders over time query.";
    }
} else {
    $error .= " Failed to prepare orders over time query.";
}

// 3. Top 5 Products Sales
$top_products_sql = "
    SELECT products.name AS product_name, SUM(orders.quantity * products.price - IFNULL(orders.discount_amount, 0)) as total_sales 
    FROM orders 
    JOIN products ON orders.product_id = products.id 
    $where 
    GROUP BY products.name 
    ORDER BY total_sales DESC 
    LIMIT 5
";
$top_products_stmt = $conn->prepare($top_products_sql);
if ($top_products_stmt) {
    if (count($params) > 0) {
        $top_products_stmt->bind_param($types, ...$params);
    }

    if ($top_products_stmt->execute()) {
        $top_products_result = $top_products_stmt->get_result();
        $top_products = [];
        $top_sales = [];

        if ($top_products_result && $top_products_result->num_rows > 0) {
            while ($row = $top_products_result->fetch_assoc()) {
                $top_products[] = $row['product_name'];
                $top_sales[] = (float)$row['total_sales'];
            }
        }
    } else {
        $error .= " Failed to execute top products sales query.";
    }
} else {
    $error .= " Failed to prepare top products sales query.";
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Generate Reports - Kaak Alia Management System</title>
    
    <!-- Tailwind CSS -->
    <script src="https://cdn.tailwindcss.com"></script>
    
    <!-- Chart.js -->
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    
    <!-- Google Fonts -->
    <link href="https://fonts.googleapis.com/css2?family=Cairo:wght@400;600;700&display=swap" rel="stylesheet">
    
    <!-- Custom Styles -->
    <style>
        body {
            font-family: 'Cairo', sans-serif;
        }
    </style>
</head>
<body class="bg-gradient-to-b from-gray-100 to-blue-50 min-h-screen">
    <main class="container mx-auto p-4">
        <!-- Header Content -->
        <header class="bg-gray-800 text-white py-4 rounded-lg shadow-md mb-6">
            <h1 class="text-2xl font-bold text-center">Kaak Alia Management System</h1>
        </header>

        <!-- Charts Section -->
        <section class="mb-8">
            <div class="bg-white p-6 rounded-lg shadow-md">
                <h2 class="text-xl font-semibold mb-4">Sales and Orders Overview</h2>
                <div class="grid grid-cols-1 lg:grid-cols-3 gap-6">
                    <!-- Orders by Status Pie Chart -->
                    <div class="bg-gray-50 p-4 rounded-lg shadow">
                        <h3 class="text-lg font-medium mb-2">Orders by Status</h3>
                        <canvas id="ordersStatusChart"></canvas>
                    </div>
                    
                    <!-- Orders Over Time Line Chart -->
                    <div class="bg-gray-50 p-4 rounded-lg shadow">
                        <h3 class="text-lg font-medium mb-2">Orders Over Time</h3>
                        <canvas id="ordersOverTimeChart"></canvas>
                    </div>
                    
                    <!-- Top 5 Products Sales Bar Chart -->
                    <div class="bg-gray-50 p-4 rounded-lg shadow">
                        <h3 class="text-lg font-medium mb-2">Top 5 Products by Sales</h3>
                        <canvas id="topProductsChart"></canvas>
                    </div>
                </div>
            </div>
        </section>

        <!-- Filters Section -->
        <section class="mb-8">
            <div class="bg-white p-6 rounded-lg shadow-md">
                <h2 class="text-xl font-semibold mb-4">Filter Reports</h2>
                <form method="GET" action="" class="grid grid-cols-1 md:grid-cols-4 gap-4">
                    <!-- Filter by Date From -->
                    <div>
                        <label for="filter_date_from" class="block text-sm font-medium text-gray-700">Date From</label>
                        <input type="date" id="filter_date_from" name="filter_date_from" class="mt-1 block w-full border border-gray-300 rounded-md shadow-sm p-2 focus:ring-blue-500 focus:border-blue-500" value="<?= htmlspecialchars($filter_date_from) ?>">
                    </div>
                    
                    <!-- Filter by Date To -->
                    <div>
                        <label for="filter_date_to" class="block text-sm font-medium text-gray-700">Date To</label>
                        <input type="date" id="filter_date_to" name="filter_date_to" class="mt-1 block w-full border border-gray-300 rounded-md shadow-sm p-2 focus:ring-blue-500 focus:border-blue-500" value="<?= htmlspecialchars($filter_date_to) ?>">
                    </div>
                    
                    <!-- Filter by Status -->
                    <div>
                        <label for="filter_status" class="block text-sm font-medium text-gray-700">Status</label>
                        <select id="filter_status" name="filter_status" class="mt-1 block w-full border border-gray-300 rounded-md shadow-sm p-2 focus:ring-blue-500 focus:border-blue-500">
                            <option value="">All</option>
                            <option value="Pending" <?= ($filter_status == 'Pending') ? 'selected' : '' ?>>Pending</option>
                            <option value="In Progress" <?= ($filter_status == 'In Progress') ? 'selected' : '' ?>>In Progress</option>
                            <option value="Completed" <?= ($filter_status == 'Completed') ? 'selected' : '' ?>>Completed</option>
                            <option value="Delivered" <?= ($filter_status == 'Delivered') ? 'selected' : '' ?>>Delivered</option>
                        </select>
                    </div>
                    
                    <!-- Action Buttons -->
                    <div class="flex items-end space-x-4">
                        <button type="submit" class="w-full bg-blue-600 text-white px-4 py-2 rounded-md shadow hover:bg-blue-700">Filter</button>
                        <a href="generate_reports.php?export=1<?= !empty($filter_date_from) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= !empty($filter_date_to) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?><?= !empty($filter_status) ? '&filter_status=' . urlencode($filter_status) : '' ?>" class="w-full bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export CSV</a>
                    </div>
                </form>
            </div>
        </section>

        <!-- Display Success or Error Messages -->
        <?php if (!empty($success)): ?>
            <div class="bg-green-100 border border-green-400 text-green-700 px-4 py-3 rounded relative mb-6" role="alert">
                <span class="block sm:inline"><?= htmlspecialchars($success) ?></span>
                <span class="absolute top-0 bottom-0 right-0 px-4 py-3">
                    <svg class="fill-current h-6 w-6 text-green-500" role="button" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20">
                        <title>Close</title>
                        <path d="M14.348 5.652a1 1 0 00-1.414 0L10 8.586 7.066 5.652a1 1 0 10-1.414 1.414L8.586 10l-2.934 2.934a1 1 0 101.414 1.414L10 11.414l2.934 2.934a1 1 0 001.414-1.414L11.414 10l2.934-2.934a1 1 0 000-1.414z"/>
                    </svg>
                </span>
            </div>
        <?php endif; ?>

        <?php if (!empty($error)): ?>
            <div class="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded relative mb-6" role="alert">
                <span class="block sm:inline"><?= htmlspecialchars($error) ?></span>
                <span class="absolute top-0 bottom-0 right-0 px-4 py-3">
                    <svg class="fill-current h-6 w-6 text-red-500" role="button" xmlns="http://www.w3.org/2000/svg" viewBox="0 0 20 20">
                        <title>Close</title>
                        <path d="M14.348 5.652a1 1 0 00-1.414 0L10 8.586 7.066 5.652a1 1 0 10-1.414 1.414L8.586 10l-2.934 2.934a1 1 0 101.414 1.414L10 11.414l2.934 2.934a1 1 0 001.414-1.414L11.414 10l2.934-2.934a1 1 0 000-1.414z"/>
                    </svg>
                </span>
            </div>
        <?php endif; ?>

        <!-- Reports Table -->
        <section>
            <div class="bg-white p-6 rounded-lg shadow-md">
                <h2 class="text-xl font-semibold mb-4">Orders Report</h2>
                <div class="overflow-x-auto">
                    <table class="min-w-full bg-white">
                        <thead class="bg-gray-800 text-white">
                            <tr>
                                <th class="w-1/12 text-left py-3 px-4 uppercase font-semibold text-sm">Order ID</th>
                                <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Customer Name</th>
                                <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Product</th>
                                <th class="w-1/12 text-left py-3 px-4 uppercase font-semibold text-sm">Quantity</th>
                                <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Packaging Preferences</th>
                                <th class="w-2/12 text-left py-3 px-4 uppercase font-semibold text-sm">Status</th>
                                <th class="w-3/12 text-left py-3 px-4 uppercase font-semibold text-sm">Order Date</th>
                            </tr>
                        </thead>
                        <tbody class="text-gray-700">
                            <?php if ($orders_result && $orders_result->num_rows > 0): ?>
                                <?php while($order = $orders_result->fetch_assoc()): ?>
                                    <tr class="border-b">
                                        <td class="py-3 px-4"><?= htmlspecialchars($order['order_id']) ?></td>
                                        <td class="py-3 px-4"><?= htmlspecialchars($order['customer_name']) ?></td>
                                        <td class="py-3 px-4"><?= htmlspecialchars($order['product_name']) ?></td>
                                        <td class="py-3 px-4"><?= htmlspecialchars($order['quantity']) ?></td>
                                        <td class="py-3 px-4"><?= htmlspecialchars($order['packaging_preferences']) ?></td>
                                        <td class="py-3 px-4">
                                            <?php
                                                $status = htmlspecialchars($order['status']);
                                                $badge = '';
                                                switch ($status) {
                                                    case 'Pending':
                                                        $badge = 'bg-yellow-400 text-white';
                                                        break;
                                                    case 'In Progress':
                                                        $badge = 'bg-green-500 text-white';
                                                        break;
                                                    case 'Completed':
                                                        $badge = 'bg-blue-500 text-white';
                                                        break;
                                                    case 'Delivered':
                                                        $badge = 'bg-red-500 text-white';
                                                        break;
                                                    default:
                                                        $badge = 'bg-gray-500 text-white';
                                                }
                                            ?>
                                            <span class="px-2 py-1 <?= $badge ?> rounded-full text-xs font-semibold"><?= $status ?></span>
                                        </td>
                                        <td class="py-3 px-4"><?= date("d M Y, h:i A", strtotime($order['order_date'])) ?></td>
                                    </tr>
                                <?php endwhile; ?>
                            <?php else: ?>
                                <tr>
                                    <td colspan="7" class="text-center py-4">No orders found for the selected criteria.</td>
                                </tr>
                            <?php endif; ?>
                        </tbody>
                    </table>
                </div>
            </div>
        </section>
    </main>
    
    <!-- Footer -->
    <footer class="bg-gray-800 text-white py-4 mt-8 rounded-lg shadow-md">
        <p class="text-center">&copy; 2024 Kaak Alia Bakery. All rights reserved.</p>
    </footer>

    <!-- Chart.js Initialization -->
    <script>
        document.addEventListener('DOMContentLoaded', function () {
            // 1. Orders by Status Pie Chart
            const ordersStatusCtx = document.getElementById('ordersStatusChart').getContext('2d');
            const ordersStatusChart = new Chart(ordersStatusCtx, {
                type: 'pie',
                data: {
                    labels: <?= json_encode($statuses) ?>,
                    datasets: [{
                        data: <?= json_encode($status_counts) ?>,
                        backgroundColor: [
                            '#F59E0B', // Yellow-400
                            '#10B981', // Green-500
                            '#3B82F6', // Blue-500
                            '#EF4444', // Red-500
                            '#6B7280'  // Gray-500 for unknown
                        ],
                        borderColor: '#FFFFFF',
                        borderWidth: 2
                    }]
                },
                options: {
                    responsive: true,
                    plugins: {
                        legend: {
                            position: 'bottom',
                            labels: {
                                color: '#6B7280',
                                font: {
                                    size: 14
                                }
                            }
                        },
                        title: {
                            display: false
                        }
                    }
                }
            });

            // 2. Orders Over Time Line Chart
            const ordersOverTimeCtx = document.getElementById('ordersOverTimeChart').getContext('2d');
            const ordersOverTimeChart = new Chart(ordersOverTimeCtx, {
                type: 'line',
                data: {
                    labels: <?= json_encode($months) ?>,
                    datasets: [{
                        label: 'Number of Orders',
                        data: <?= json_encode($orders_counts) ?>,
                        fill: false,
                        borderColor: '#3B82F6', // Blue-500
                        backgroundColor: '#3B82F6',
                        tension: 0.3,
                        pointBackgroundColor: '#3B82F6',
                        pointBorderColor: '#FFFFFF',
                        pointHoverBackgroundColor: '#FFFFFF',
                        pointHoverBorderColor: '#3B82F6',
                    }]
                },
                options: {
                    responsive: true,
                    plugins: {
                        legend: {
                            display: false
                        },
                        title: {
                            display: false
                        },
                        tooltip: {
                            callbacks: {
                                label: function(context) {
                                    return ` ${context.parsed.y} Orders`;
                                }
                            }
                        }
                    },
                    scales: {
                        y: {
                            beginAtZero: true,
                            ticks: {
                                color: '#6B7280',
                                stepSize: 1
                            },
                            grid: {
                                color: '#E5E7EB'
                            }
                        },
                        x: {
                            ticks: {
                                color: '#6B7280'
                            },
                            grid: {
                                color: '#E5E7EB'
                            }
                        }
                    }
                }
            });

            // 3. Top 5 Products Sales Bar Chart
            const topProductsCtx = document.getElementById('topProductsChart').getContext('2d');
            const topProductsChart = new Chart(topProductsCtx, {
                type: 'bar',
                data: {
                    labels: <?= json_encode($top_products) ?>,
                    datasets: [{
                        label: 'Total Sales (KD)',
                        data: <?= json_encode($top_sales) ?>,
                        backgroundColor: '#10B981', // Green-500
                        borderColor: '#10B981',
                        borderWidth: 1
                    }]
                },
                options: {
                    responsive: true,
                    plugins: {
                        legend: {
                            display: false
                        },
                        title: {
                            display: false
                        },
                        tooltip: {
                            callbacks: {
                                label: function(context) {
                                    return ` KD${context.parsed.y.toLocaleString()}`;
                                }
                            }
                        }
                    },
                    scales: {
                        y: {
                            beginAtZero: true,
                            ticks: {
                                color: '#6B7280',
                                callback: function(value) {
                                    return 'KD ' + value.toLocaleString();
                                }
                            },
                            grid: {
                                color: '#E5E7EB'
                            }
                        },
                        x: {
                            ticks: {
                                color: '#6B7280'
                            },
                            grid: {
                                color: '#E5E7EB'
                            }
                        }
                    }
                }
            });
        });
    </script>
</body>
</html>) of export csv and make info organzie and easy to readable and this info database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 08, 2024 at 03:05 PM
-- Server version: 10.11.9-MariaDB
-- PHP Version: 7.2.34

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


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

--
-- Database: `u406552082_kaakalia`
--

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

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

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

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

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

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

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

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

CREATE TABLE `order_items` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `packaging_preferences` varchar(255) DEFAULT NULL,
  `discount_amount` decimal(10,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;

--
-- Indexes for dumped tables
--

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

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

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

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

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

--
-- Constraints for dumped tables
--

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

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

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