based on this database info  (-- 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 */;) i want to add more features in thie reports (<?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 = '';
$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'])) {
        $filter_date = trim($_GET['filter_date']);
        $conditions[] = "DATE(orders.order_date) = ?";
        $params[] = $filter_date;
        $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);
}

// Prepare the SQL statement with filters
$orders_sql = "SELECT orders.*, 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 && count($params) > 0) {
    $stmt->bind_param($types, ...$params);
}
$orders_result = $stmt ? $stmt->execute() : false;
$orders_result = $stmt ? $stmt->get_result() : false;

// Handle Export to CSV
if (isset($_GET['export'])) {
    // Re-execute the query without pagination for export
    $export_sql = "SELECT orders.*, 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";
    $export_stmt = $conn->prepare($export_sql);
    if ($export_stmt && count($params) > 0) {
        $export_stmt->bind_param($types, ...$params);
    }
    $export_result = $export_stmt ? $export_stmt->execute() : false;
    $export_result = $export_stmt ? $export_stmt->get_result() : false;

    // 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', 'Customer Name', 'Product', 'Quantity', 'Packaging Preferences', 'Status', 'Order Date']);
    // Data rows
    if ($export_result && $export_result->num_rows > 0) {
        while ($row = $export_result->fetch_assoc()) {
            fputcsv($output, [
                $row['id'],
                $row['customer_name'],
                $row['product_name'],
                $row['quantity'],
                $row['packaging_preferences'],
                $row['status'],
                $row['order_date']
            ]);
        }
    }
    fclose($output);
    exit();
}
// Now include the header after handling actions
require_once __DIR__ . '/../../includes/header.php';

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Generate Reports - 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="../../public/bootstrap/css/bootstrap.min.css">
    
    <!-- Bootstrap Icons -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css">
    
    <!-- Embedded Custom Styles -->
    <style>
        /* ---------------------------------------------------
           1. CSS Variables for Consistent Design
        ---------------------------------------------------- */
        :root {
            --dark-gray: #333333; /* Header and Footer Background */
            --primary-color: #b78d67; /* Buttons and Highlights */
            --secondary-color: #515151; /* Text and Icons */
            --success-color: #06D6A0; /* Success Messages */
            --error-color: #EF476F; /* Error Messages */
            --white: #FFFFFF;
            --background-gradient: linear-gradient(to bottom, #f5f7fa, #c3cfe2);
            --divider-color: #FFD166;
        }

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

        h1, h2, h3, h4, h5, p {
            margin: 0;
        }

        /* ---------------------------------------------------
           3. Header Styles
        ---------------------------------------------------- */
        .header {
            padding: 20px 30px;
            background-color: var(--dark-gray);
            border-radius: 8px;
            display: flex;
            align-items: center;
            justify-content: center;
            margin-bottom: 20px;
        }

        .header h1 {
            color: var(--white);
            font-size: 24px;
            font-weight: bold;
        }

        /* ---------------------------------------------------
           4. Welcome Section (Optional)
        ---------------------------------------------------- */
        .welcome-section {
            background-color: var(--white);
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
            border-radius: 8px;
            padding: 20px;
            text-align: center;
            margin-bottom: 20px;
        }

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

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

        /* ---------------------------------------------------
           5. Reports Filter Form Styles
        ---------------------------------------------------- */
        .filter-form-card {
            background-color: var(--white);
            border: 1px solid #ccc;
            border-radius: 8px;
            padding: 20px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
            margin-bottom: 30px;
        }

        .filter-form-card .card-header {
            background-color: var(--primary-color);
            color: var(--white);
            font-weight: bold;
            border-top-left-radius: 8px;
            border-top-right-radius: 8px;
        }

        .filter-form-card .form-label {
            font-weight: 600;
            color: var(--secondary-color);
        }

        .filter-form-card .btn-primary {
            background-color: var(--primary-color);
            border: none;
            transition: background-color 0.3s, transform 0.3s;
        }

        .filter-form-card .btn-primary:hover {
            background-color: #a77c55;
            transform: scale(1.05);
        }

        .filter-form-card .btn-success {
            background-color: #06D6A0;
            border: none;
            transition: background-color 0.3s, transform 0.3s;
        }

        .filter-form-card .btn-success:hover {
            background-color: #05c08f;
            transform: scale(1.05);
        }

        /* ---------------------------------------------------
           6. Reports Table Styles
        ---------------------------------------------------- */
        .reports-table-section {
            background-color: var(--white);
            border: 1px solid #ccc;
            border-radius: 8px;
            padding: 20px;
            box-shadow: 0 4px 6px rgba(0,0,0,0.1);
        }

        .reports-table-section h4 {
            font-size: 18px;
            font-weight: bold;
            color: var(--secondary-color);
            margin-bottom: 15px;
        }

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

        .reports-table-section th, 
        .reports-table-section td {
            padding: 12px;
            text-align: left;
            border: 1px solid #ccc;
            font-size: 14px;
        }

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

        .reports-table-section tr:nth-child(even) {
            background-color: #F9F9F9;
        }

        .reports-table-section tr:hover {
            background-color: #e6f7ff;
        }

        .reports-table-section td {
            color: var(--secondary-color);
        }

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

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

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

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

        /* ---------------------------------------------------
           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;
        }

        /* ---------------------------------------------------
           9. Responsive Design Enhancements
        ---------------------------------------------------- */
        @media (max-width: 768px) {
            .filter-form-card, .reports-table-section {
                padding: 15px;
            }

            .filter-form-card .row .col-md-4,
            .filter-form-card .row .col-md-6 {
                width: 100%;
            }

            .reports-table-section table, 
            .reports-table-section th, 
            .reports-table-section td {
                font-size: 12px;
                padding: 10px;
            }

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

            .footer {
                position: static;
            }
        }

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

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

            .filter-form-card .btn {
                width: 100%;
                margin-top: 10px;
            }

            .reports-table-section table, 
            .reports-table-section th, 
            .reports-table-section td {
                font-size: 10px;
                padding: 8px;
            }
        }
    </style>
</head>
<body>
<main class='container-body'>
    <!-- Header -->
    <div class="header">
        <h1>Kaak Alia Management System</h1>
    </div>

    <!-- Container -->
    <div class="container">
        <!-- Display Success or Error Messages -->
        <?php if (!empty($success)): ?>
            <div class="alert alert-success mb-4"><?= htmlspecialchars($success) ?></div>
        <?php endif; ?>

        <?php if (!empty($error)): ?>
            <div class="alert alert-danger mb-4"><?= htmlspecialchars($error) ?></div>
        <?php endif; ?>

        <!-- Reports Filter Form -->
        <div class="filter-form-card">
            <div class="card-header">
                Filter Reports
            </div>
            <div class="card-body">
                <form method="GET" action="" class="row g-3">
                    <!-- Filter by Date -->
                    <div class="col-md-4">
                        <label for="filter_date" class="form-label">Date</label>
                        <input type="date" id="filter_date" name="filter_date" class="form-control" value="<?= htmlspecialchars($filter_date) ?>">
                    </div>
                    <!-- Filter by Status -->
                    <div class="col-md-4">
                        <label for="filter_status" class="form-label">Status</label>
                        <select id="filter_status" name="filter_status" class="form-control">
                            <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="col-md-4 d-flex align-items-end">
                        <button type="submit" class="btn btn-primary me-2">Filter</button>
                        <a href="generate_reports.php?export=1&filter_date=<?= urlencode($filter_date) ?>&filter_status=<?= urlencode($filter_status) ?>" class="btn btn-success">Export CSV</a>
                    </div>
                </form>
            </div>
        </div>

        <!-- Reports Table -->
        <div class="reports-table-section">
            <h4>Orders Report</h4>
            <table>
                <thead>
                    <tr>
                        <th>Order ID</th>
                        <th>Customer Name</th>
                        <th>Product</th>
                        <th>Quantity</th>
                        <th>Packaging Preferences</th>
                        <th>Status</th>
                        <th>Order Date</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if ($orders_result && $orders_result->num_rows > 0): ?>
                        <?php while($order = $orders_result->fetch_assoc()): ?>
                            <tr>
                                <td><?= htmlspecialchars($order['id']) ?></td>
                                <td><?= htmlspecialchars($order['customer_name']) ?></td>
                                <td><?= htmlspecialchars($order['product_name']) ?></td>
                                <td><?= htmlspecialchars($order['quantity']) ?></td>
                                <td><?= htmlspecialchars($order['packaging_preferences']) ?></td>
                                <td>
                                    <?php
                                        $status = htmlspecialchars($order['status']);
                                        switch ($status) {
                                            case 'Pending':
                                                echo '<span class="badge-status-pending">Pending</span>';
                                                break;
                                            case 'In Progress':
                                                echo '<span class="badge-status-in-progress">In Progress</span>';
                                                break;
                                            case 'Completed':
                                                echo '<span class="badge-status-completed">Completed</span>';
                                                break;
                                            case 'Delivered':
                                                echo '<span class="badge-status-delivered">Delivered</span>';
                                                break;
                                            default:
                                                echo '<span class="badge bg-light text-dark">Unknown</span>';
                                        }
                                    ?>
                                </td>
                                <td><?= date("d M Y, h:i A", strtotime($order['order_date'])) ?></td>
                            </tr>
                        <?php endwhile; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="7" class="text-center">No orders found for the selected criteria.</td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>

    <!-- Footer -->
    <div class="footer">
        &copy; 2024 Kaak Alia Bakery. All rights reserved.
    </div>
</main>
    <!-- Bootstrap JS Bundle -->
    <script src="../../public/bootstrap/js/bootstrap.bundle.min.js"></script>

</body>
</html>) add more features and add charts in top and make it cool and don't chaneg colors stlyes and make it cool design and type full codes and make itrepsoivne desing for all devices