in this database  (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 10, 2024 at 02:45 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 `ads`
--

CREATE TABLE `ads` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `ad_type` enum('Post','Story') NOT NULL,
  `related_id` int(11) NOT NULL,
  `budget` decimal(10,2) NOT NULL,
  `total_spent` decimal(10,2) DEFAULT 0.00,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `status` enum('Active','Paused','Completed') DEFAULT 'Active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `ad_spend_logs`
--

CREATE TABLE `ad_spend_logs` (
  `id` int(11) NOT NULL,
  `ad_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `spend_date` datetime DEFAULT current_timestamp(),
  `description` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- 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 `drivers`
--

CREATE TABLE `drivers` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `number` varchar(15) NOT 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',
  `priority` enum('Normal','Important','Quick') DEFAULT 'Normal',
  `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;

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

--
-- Table structure for table `workspace_notes`
--

CREATE TABLE `workspace_notes` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `workspace_plans`
--

CREATE TABLE `workspace_plans` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `due_date` datetime DEFAULT NULL,
  `status` enum('Not Started','In Progress','Completed') DEFAULT 'Not Started',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `workspace_posts`
--

CREATE TABLE `workspace_posts` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `platform` enum('Instagram','Facebook','Twitter','Other') NOT NULL,
  `content` text NOT NULL,
  `media_url` varchar(255) DEFAULT NULL,
  `scheduled_datetime` datetime DEFAULT NULL,
  `status` enum('Scheduled','Posted','Failed') DEFAULT 'Scheduled',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `type` enum('Post','Story') DEFAULT 'Post'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `ads`
--
ALTER TABLE `ads`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `related_id` (`related_id`);

--
-- Indexes for table `ad_spend_logs`
--
ALTER TABLE `ad_spend_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `ad_id` (`ad_id`);

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

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

--
-- Indexes for table `orders`
--
ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `customer_id` (`customer_id`),
  ADD KEY `idx_orders_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`),
  ADD KEY `idx_order_items_order_id` (`order_id`),
  ADD KEY `idx_order_items_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`);

--
-- Indexes for table `workspace_notes`
--
ALTER TABLE `workspace_notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `workspace_plans`
--
ALTER TABLE `workspace_plans`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `workspace_posts`
--
ALTER TABLE `workspace_posts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

--
-- AUTO_INCREMENT for table `drivers`
--
ALTER TABLE `drivers`
  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 for table `workspace_notes`
--
ALTER TABLE `workspace_notes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `ads`
--
ALTER TABLE `ads`
  ADD CONSTRAINT `fk_ads_related_id` FOREIGN KEY (`related_id`) REFERENCES `workspace_posts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ads_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `ad_spend_logs`
--
ALTER TABLE `ad_spend_logs`
  ADD CONSTRAINT `fk_ad_spend_logs_ad_id` FOREIGN KEY (`ad_id`) REFERENCES `ads` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `orders`
--
ALTER TABLE `orders`
  ADD CONSTRAINT `fk_orders_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  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 `fk_order_items_order_id` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_order_items_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  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;

--
-- Constraints for table `workspace_notes`
--
ALTER TABLE `workspace_notes`
  ADD CONSTRAINT `fk_workspace_notes_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `workspace_plans`
--
ALTER TABLE `workspace_plans`
  ADD CONSTRAINT `fk_workspace_plans_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `workspace_posts`
--
ALTER TABLE `workspace_posts`
  ADD CONSTRAINT `fk_workspace_posts_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`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 update this codes (<?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);
}

// Function to sanitize CSV data
function sanitize_csv($data) {
    return mb_convert_encoding($data, 'UTF-8', 'UTF-8');
}

// **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,
                    order_items.quantity,
                    order_items.packaging_preferences,
                    order_items.discount_percentage,
                    order_items.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 order_items ON orders.id = order_items.order_id
                   JOIN products ON order_items.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 with UTF-8 BOM for Arabic support
                header('Content-Type: text/csv; charset=UTF-8');
                header('Content-Disposition: attachment; filename=orders_report.csv');

                // Open the output stream
                $output = fopen('php://output', 'w');

                // Write UTF-8 BOM
                fprintf($output, chr(0xEF) . chr(0xBB) . chr(0xBF));

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

                // Initialize summary variables
                $total_orders = 0;
                $total_paid_amount = 0.00;

                // 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'];
                        $discount_amount = ($subtotal * $row['discount_percentage']) / 100;
                        $total_amount = $subtotal - $discount_amount;

                        // Determine Payment Status
                        $payment_status = ($row['status'] === 'Delivered') ? 'Paid' : 'Unpaid';

                        // Calculate Total Paid Amount (only if Paid)
                        $total_paid = ($payment_status === 'Paid') ? $total_amount : '0.00';

                        // Update summary if paid
                        if ($payment_status === 'Paid') {
                            $total_paid_amount += $total_amount;
                        }

                        // Format dates for better readability
                        $order_date = date("d/m/Y H:i", strtotime($row['order_date']));
                        $delivery_datetime = $row['delivery_datetime'] ? date("d/m/Y H:i", strtotime($row['delivery_datetime'])) : 'N/A';
                        $delivery_date = $row['delivery_date'] ? date("d/m/Y", strtotime($row['delivery_date'])) : 'N/A';

                        fputcsv($output, [
                            sanitize_csv($row['order_id']),
                            sanitize_csv($row['status']),
                            sanitize_csv($order_date),
                            sanitize_csv($delivery_datetime),
                            sanitize_csv($delivery_date),
                            sanitize_csv($row['quantity']),
                            sanitize_csv($row['packaging_preferences'] ?? 'N/A'),
                            sanitize_csv(number_format((float)$row['discount_percentage'], 2)),
                            sanitize_csv(number_format((float)$discount_amount, 2)),
                            sanitize_csv($row['discount_reason'] ?? 'N/A'),
                            sanitize_csv(number_format((float)$subtotal, 2)),
                            sanitize_csv(number_format((float)$total_amount, 2)),
                            sanitize_csv($payment_status),          // New Column
                            sanitize_csv(number_format((float)$total_paid, 2)),  // New Column
                            sanitize_csv($row['customer_name']),
                            sanitize_csv($row['customer_contact'] ?? 'N/A'),
                            sanitize_csv($row['delivery_address'] ?? 'N/A'),
                            sanitize_csv($row['product_name']),
                            sanitize_csv($row['product_size'] ?? 'N/A'),
                            sanitize_csv($row['product_category'] ?? 'N/A'),
                            sanitize_csv(number_format((float)$row['product_price'], 2))
                        ]);
                    }

                    // Add a blank row before summary
                    fputcsv($output, []);

                    // Summary section
                    fputcsv($output, ['Total Orders', $total_orders]);
                    fputcsv($output, ['Total Paid Amount (KD)', number_format($total_paid_amount, 2)]);
                }

                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,
                order_items.quantity,
                order_items.packaging_preferences,
                order_items.discount_percentage,
                order_items.discount_reason,
                customers.name AS customer_name,
                products.name AS product_name,
                products.price AS product_price
               FROM orders 
               JOIN customers ON orders.customer_id = customers.id 
               JOIN order_items ON orders.id = order_items.order_id
               JOIN products ON order_items.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(orders.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(order_items.quantity * products.price - (order_items.quantity * products.price * IFNULL(order_items.discount_percentage, 0)/100)) as total_sales 
    FROM orders 
    JOIN order_items ON orders.id = order_items.order_id
    JOIN products ON order_items.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 flex-col space-y-4 justify-end">
                        <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", 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>) i want to export all info of database plz and make export for all info order like exist in the codes header, and create new export about plans and posts and notes and ad all of them in csv and type full codes plz