based on this (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 12, 2024 at 08:36 AM
-- 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,
  `is_star` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `type` enum('order','post','plan','note','ad') NOT NULL,
  `message` varchar(255) NOT NULL,
  `link` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp()
) 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,
  `email` varchar(100) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','staff') NOT NULL,
  `profile_image` varchar(255) DEFAULT 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,
  `audio_path` varchar(255) 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(),
  `priority` enum('Normal','Important','Critical') NOT NULL DEFAULT 'Normal'
) 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 `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `created_at` (`created_at`);

--
-- 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 `notifications`
--
ALTER TABLE `notifications`
  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 `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`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 you to update report (<?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'])) {
    // Get the table to export
    $export = $_GET['export'];

    // Switch based on export type
    switch ($export) {
        case 'orders':
            // 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',
                            'Total Paid Amount (KD)',
                            '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;
                                    $total_orders++;
                                }

                                // 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),
                                    sanitize_csv(number_format((float)$total_paid, 2)),
                                    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 Paid 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.";
            }
            break;

        case 'plans':
            // Export workspace_plans
            $plans_sql = "SELECT 
                            workspace_plans.id AS plan_id,
                            workspace_plans.user_id,
                            users.username,
                            workspace_plans.title,
                            workspace_plans.description,
                            workspace_plans.due_date,
                            workspace_plans.status,
                            workspace_plans.created_at,
                            workspace_plans.updated_at
                          FROM workspace_plans
                          JOIN users ON workspace_plans.user_id = users.id
                          $where
                          ORDER BY workspace_plans.due_date DESC";

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

                if ($plans_stmt->execute()) {
                    $plans_result = $plans_stmt->get_result();
                    if ($plans_result) {
                        // Set headers for CSV download
                        header('Content-Type: text/csv; charset=UTF-8');
                        header('Content-Disposition: attachment; filename=plans_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
                        fputcsv($output, [
                            'Plan ID',
                            'User ID',
                            'Username',
                            'Title',
                            'Description',
                            'Due Date',
                            'Status',
                            'Created At',
                            'Updated At'
                        ]);

                        // Data rows
                        if ($plans_result->num_rows > 0) {
                            while ($row = $plans_result->fetch_assoc()) {
                                fputcsv($output, [
                                    sanitize_csv($row['plan_id']),
                                    sanitize_csv($row['user_id']),
                                    sanitize_csv($row['username']),
                                    sanitize_csv($row['title']),
                                    sanitize_csv($row['description'] ?? 'N/A'),
                                    sanitize_csv($row['due_date'] ? date("d/m/Y", strtotime($row['due_date'])) : 'N/A'),
                                    sanitize_csv($row['status']),
                                    sanitize_csv(date("d/m/Y H:i", strtotime($row['created_at']))),
                                    sanitize_csv(date("d/m/Y H:i", strtotime($row['updated_at'])))
                                ]);
                            }
                        }

                        fclose($output);
                        exit(); // Ensure no further output is sent
                    } else {
                        $error = "Failed to retrieve plans export data.";
                    }
                } else {
                    $error = "Failed to execute plans export query.";
                }
            } else {
                $error = "Failed to prepare plans export query.";
            }
            break;

        case 'posts':
            // Export workspace_posts
            $posts_sql = "SELECT 
                            workspace_posts.id AS post_id,
                            workspace_posts.user_id,
                            users.username,
                            workspace_posts.platform,
                            workspace_posts.content,
                            workspace_posts.media_url,
                            workspace_posts.scheduled_datetime,
                            workspace_posts.status,
                            workspace_posts.created_at,
                            workspace_posts.updated_at,
                            workspace_posts.type
                          FROM workspace_posts
                          JOIN users ON workspace_posts.user_id = users.id
                          $where
                          ORDER BY workspace_posts.scheduled_datetime DESC";

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

                if ($posts_stmt->execute()) {
                    $posts_result = $posts_stmt->get_result();
                    if ($posts_result) {
                        // Set headers for CSV download
                        header('Content-Type: text/csv; charset=UTF-8');
                        header('Content-Disposition: attachment; filename=posts_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
                        fputcsv($output, [
                            'Post ID',
                            'User ID',
                            'Username',
                            'Platform',
                            'Content',
                            'Media URL',
                            'Scheduled Date & Time',
                            'Status',
                            'Created At',
                            'Updated At',
                            'Type'
                        ]);

                        // Data rows
                        if ($posts_result->num_rows > 0) {
                            while ($row = $posts_result->fetch_assoc()) {
                                fputcsv($output, [
                                    sanitize_csv($row['post_id']),
                                    sanitize_csv($row['user_id']),
                                    sanitize_csv($row['username']),
                                    sanitize_csv($row['platform']),
                                    sanitize_csv($row['content'] ?? 'N/A'),
                                    sanitize_csv($row['media_url'] ?? 'N/A'),
                                    sanitize_csv($row['scheduled_datetime'] ? date("d/m/Y H:i", strtotime($row['scheduled_datetime'])) : 'N/A'),
                                    sanitize_csv($row['status']),
                                    sanitize_csv(date("d/m/Y H:i", strtotime($row['created_at']))),
                                    sanitize_csv(date("d/m/Y H:i", strtotime($row['updated_at']))),
                                    sanitize_csv($row['type'])
                                ]);
                            }
                        }

                        fclose($output);
                        exit(); // Ensure no further output is sent
                    } else {
                        $error = "Failed to retrieve posts export data.";
                    }
                } else {
                    $error = "Failed to execute posts export query.";
                }
            } else {
                $error = "Failed to prepare posts export query.";
            }
            break;

        case 'notes':
            // Export workspace_notes
            $notes_sql = "SELECT 
                            workspace_notes.id AS note_id,
                            workspace_notes.user_id,
                            users.username,
                            workspace_notes.title,
                            workspace_notes.content,
                            workspace_notes.created_at,
                            workspace_notes.updated_at
                          FROM workspace_notes
                          JOIN users ON workspace_notes.user_id = users.id
                          $where
                          ORDER BY workspace_notes.created_at DESC";

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

                if ($notes_stmt->execute()) {
                    $notes_result = $notes_stmt->get_result();
                    if ($notes_result) {
                        // Set headers for CSV download
                        header('Content-Type: text/csv; charset=UTF-8');
                        header('Content-Disposition: attachment; filename=notes_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
                        fputcsv($output, [
                            'Note ID',
                            'User ID',
                            'Username',
                            'Title',
                            'Content',
                            'Created At',
                            'Updated At'
                        ]);

                        // Data rows
                        if ($notes_result->num_rows > 0) {
                            while ($row = $notes_result->fetch_assoc()) {
                                fputcsv($output, [
                                    sanitize_csv($row['note_id']),
                                    sanitize_csv($row['user_id']),
                                    sanitize_csv($row['username']),
                                    sanitize_csv($row['title']),
                                    sanitize_csv($row['content'] ?? 'N/A'),
                                    sanitize_csv(date("d/m/Y H:i", strtotime($row['created_at']))),
                                    sanitize_csv(date("d/m/Y H:i", strtotime($row['updated_at'])))
                                ]);
                            }
                        }

                        fclose($output);
                        exit(); // Ensure no further output is sent
                    } else {
                        $error = "Failed to retrieve notes export data.";
                    }
                } else {
                    $error = "Failed to execute notes export query.";
                }
            } else {
                $error = "Failed to prepare notes export query.";
            }
            break;

        case 'ads':
            // Export ads
            $ads_sql = "SELECT 
                            ads.id AS ad_id,
                            ads.user_id,
                            users.username,
                            ads.name,
                            ads.ad_type,
                            ads.related_id,
                            workspace_posts.content AS related_post_content,
                            ads.budget,
                            ads.total_spent,
                            ads.start_date,
                            ads.end_date,
                            ads.status,
                            ads.created_at,
                            ads.updated_at
                        FROM ads
                        JOIN users ON ads.user_id = users.id
                        LEFT JOIN workspace_posts ON ads.related_id = workspace_posts.id
                        $where
                        ORDER BY ads.created_at DESC";

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

                if ($ads_stmt->execute()) {
                    $ads_result = $ads_stmt->get_result();
                    if ($ads_result) {
                        // Set headers for CSV download
                        header('Content-Type: text/csv; charset=UTF-8');
                        header('Content-Disposition: attachment; filename=ads_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
                        fputcsv($output, [
                            'Ad ID',
                            'User ID',
                            'Username',
                            'Name',
                            'Ad Type',
                            'Related ID',
                            'Related Post Content',
                            'Budget (KD)',
                            'Total Spent (KD)',
                            'Start Date',
                            'End Date',
                            'Status',
                            'Created At',
                            'Updated At'
                        ]);

                        // Data rows
                        if ($ads_result->num_rows > 0) {
                            while ($row = $ads_result->fetch_assoc()) {
                                fputcsv($output, [
                                    sanitize_csv($row['ad_id']),
                                    sanitize_csv($row['user_id']),
                                    sanitize_csv($row['username']),
                                    sanitize_csv($row['name']),
                                    sanitize_csv($row['ad_type']),
                                    sanitize_csv($row['related_id']),
                                    sanitize_csv($row['related_post_content'] ?? 'N/A'),
                                    sanitize_csv(number_format((float)$row['budget'], 2)),
                                    sanitize_csv(number_format((float)$row['total_spent'], 2)),
                                    sanitize_csv($row['start_date'] ? date("d/m/Y", strtotime($row['start_date'])) : 'N/A'),
                                    sanitize_csv($row['end_date'] ? date("d/m/Y", strtotime($row['end_date'])) : 'N/A'),
                                    sanitize_csv($row['status']),
                                    sanitize_csv(date("d/m/Y H:i", strtotime($row['created_at']))),
                                    sanitize_csv(date("d/m/Y H:i", strtotime($row['updated_at'])))
                                ]);
                            }
                        }

                        fclose($output);
                        exit(); // Ensure no further output is sent
                    } else {
                        $error = "Failed to retrieve ads export data.";
                    }
                } else {
                    $error = "Failed to execute ads export query.";
                }
            } else {
                $error = "Failed to prepare ads export query.";
            }
            break;

        default:
            $error = "Invalid export type selected.";
            break;
    }
}

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

// Initialize variables for charts
$statuses = [];
$status_counts = [];
$months = [];
$orders_counts = [];
$top_products = [];
$top_sales = [];

// 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();
        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();
        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();
        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;
        }
                        /* Custom scrollbar for better UX */
        ::-webkit-scrollbar {
            width: 8px;
        }

        ::-webkit-scrollbar-track {
            background: #f1f5f9; /* Tailwind's gray-100 */
        }

        ::-webkit-scrollbar-thumb {
            background: #4b6584; /* Teal-500 */
            border-radius: 4px;
        }

        ::-webkit-scrollbar-thumb:hover {
            background: #0d9488; /* Teal-600 */
        }

    </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 - Reports</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 (Only applicable to Orders) -->
                    <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>
                        <!-- Export Buttons -->
                        <a href="generate_reports.php?export=orders<?= !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 Orders CSV</a>
                    </div>
                </form>
                <!-- Additional Export Buttons for other tables -->
                <div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-3 gap-4 mt-6">
                    <a href="generate_reports.php?export=plans<?= !empty($filter_date_from) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= !empty($filter_date_to) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?>" class="bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export Plans CSV</a>
                    <a href="generate_reports.php?export=posts<?= !empty($filter_date_from) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= !empty($filter_date_to) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?>" class="bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export Posts CSV</a>
                    <a href="generate_reports.php?export=notes<?= !empty($filter_date_from) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= !empty($filter_date_to) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?>" class="bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export Notes CSV</a>
                    <a href="generate_reports.php?export=ads<?= !empty($filter_date_from) ? '&filter_date_from=' . urlencode($filter_date_from) : '' ?><?= !empty($filter_date_to) ? '&filter_date_to=' . urlencode($filter_date_to) : '' ?>" class="bg-green-600 text-white px-4 py-2 rounded-md shadow hover:bg-green-700 text-center">Export Ads CSV</a>
                </div>
            </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>) and update them based on database inof and make exports csv files orgnzine and export all info and make it professioanl and type full codes