based on this inof database (-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Nov 14, 2024 at 09:26 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,
  `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,
  `delivery_fee` decimal(10,2) NOT NULL DEFAULT 0.00,
  `free_shipping` tinyint(1) NOT NULL DEFAULT 0,
  `is_paid` tinyint(1) NOT NULL DEFAULT 0
) 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 to change this (<?php
// public/orders/view_orders.php

// Enable error reporting for debugging (disable in production)
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

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

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

// Helper Functions

/**
 * Sanitize input to prevent XSS and other injections.
 *
 * @param string $input The input string to sanitize.
 * @return string The sanitized string.
 */
function sanitize_input($input) {
    return htmlspecialchars(trim($input), ENT_QUOTES, 'UTF-8');
}

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

/**
 * Get the appropriate sort icon based on current sorting.
 *
 * @param string $column The column to check.
 * @param string $current_sort_by The current column being sorted by.
 * @param string $current_sort_order The current sort order ('ASC' or 'DESC').
 * @return string The HTML for the sort icon.
 */
function get_sort_icon($column, $current_sort_by, $current_sort_order) {
    if ($current_sort_by === $column) {
        if ($current_sort_order === 'ASC') {
            return '<svg xmlns="http://www.w3.org/2000/svg" class="w-4 h-4 inline-block" fill="none" viewBox="0 0 24 24" stroke="currentColor">
                        <path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M5 15l7-7 7 7" />
                    </svg>';
        } else {
            return '<svg xmlns="http://www.w3.org/2000/svg" class="w-4 h-4 inline-block" fill="none" viewBox="0 0 24 24" stroke="currentColor">
                        <path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M19 9l-7 7-7-7" />
                    </svg>';
        }
    }
    return '';
}

/**
 * Generate a WhatsApp link from a contact number.
 *
 * @param string $contact The contact number.
 * @return string The WhatsApp URL.
 */
function generate_whatsapp_link($contact) {
    // Remove any non-digit characters except the plus sign
    $digits = preg_replace('/[^\d+]/', '', $contact);
    
    // Ensure the number starts with '+'
    if (strpos($digits, '+') !== 0) {
        $digits = '+' . $digits;
    }
    
    return "https://wa.me/" . ltrim($digits, '+');
}

/**
 * Calculate totals for a given set of order items.
 *
 * @param array $order_items The array of order items.
 * @param float $delivery_fee The delivery fee for the order.
 * @param int $free_shipping Indicates if free shipping is applied (1 for yes, 0 for no).
 * @return array An associative array containing total_before_discounts, total_discounts, delivery_fee, and final_total.
 */
function calculate_order_totals($order_items, $delivery_fee, $free_shipping) {
    $total_before_discounts = 0;
    $total_discounts = 0;
    $final_total = 0;

    foreach ($order_items as &$item) {
        $item_total = $item['quantity'] * $item['product_price'];
        $discount_amount = ($item['discount_percentage'] / 100) * $item_total;
        $item['discount_amount'] = $discount_amount;
        $item['total_after_discount'] = $item_total - $discount_amount;

        $total_before_discounts += $item_total;
        $total_discounts += $discount_amount;
    }
    unset($item); // Break reference
    $final_total = $total_before_discounts - $total_discounts;

    // Add delivery fee if not free shipping
    if (!$free_shipping) {
        $final_total += $delivery_fee;
    }

    return [
        'total_before_discounts' => $total_before_discounts,
        'total_discounts' => $total_discounts,
        'delivery_fee' => $free_shipping ? 0.00 : $delivery_fee,
        'final_total' => $final_total
    ];
}

// Handle Actions Before Including Header

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

        // Validate new_status
        $valid_statuses = ['Pending', 'In Progress', 'Completed', 'Delivered'];
        if ($order_id > 0 && in_array($new_status, $valid_statuses)) {
            // If setting to 'Delivered', ensure delivery_datetime is set
            if ($new_status === 'Delivered') {
                $check_sql = "SELECT delivery_datetime FROM orders WHERE id = ?";
                $check_stmt = $conn->prepare($check_sql);
                if ($check_stmt) {
                    $check_stmt->bind_param("i", $order_id);
                    $check_stmt->execute();
                    $check_result = $check_stmt->get_result();
                    if ($check_result->num_rows == 1) {
                        $order = $check_result->fetch_assoc();
                        if (empty($order['delivery_datetime'])) {
                            $check_stmt->close();
                            header("Location: view_orders.php?error=3"); // Delivery datetime not set
                            exit();
                        }
                    }
                    $check_stmt->close();
                } else {
                    header("Location: view_orders.php?error=2");
                    exit();
                }
            }

            // Update status using prepared statement
            $update_sql = "UPDATE orders SET status = ? WHERE id = ?";
            $stmt = $conn->prepare($update_sql);
            if ($stmt) {
                $stmt->bind_param("si", $new_status, $order_id);
                if ($stmt->execute()) {
                    $stmt->close();
                    header("Location: view_orders.php?success=1");
                    exit();
                } else {
                    $stmt->close();
                    header("Location: view_orders.php?error=1");
                    exit();
                }
            } else {
                header("Location: view_orders.php?error=2");
                exit();
            }
        } else {
            header("Location: view_orders.php?error=2");
            exit();
        }
    } elseif ($_GET['action'] === 'mark_paid') {
        $order_id = isset($_GET['order_id']) ? intval($_GET['order_id']) : 0;

        if ($order_id > 0) {
            // Update is_paid to 1
            $update_sql = "UPDATE orders SET is_paid = 1 WHERE id = ?";
            $stmt = $conn->prepare($update_sql);
            if ($stmt) {
                $stmt->bind_param("i", $order_id);
                if ($stmt->execute()) {
                    $stmt->close();
                    header("Location: view_orders.php?success=3");
                    exit();
                } else {
                    $stmt->close();
                    header("Location: view_orders.php?error=6");
                    exit();
                }
            } else {
                header("Location: view_orders.php?error=2");
                exit();
            }
        } else {
            header("Location: view_orders.php?error=5");
            exit();
        }
    } elseif ($_GET['action'] === 'export') {
        // Implement export functionality here (CSV Example)
        $filename = "orders_" . date("Y-m-d_H-i-s") . ".csv";
        header('Content-Type: text/csv');
        header('Content-Disposition: attachment;filename="' . $filename . '"');

        $output = fopen('php://output', 'w');
        // Header Row
        fputcsv($output, ['Order ID', 'Customer Name', 'Contact', 'Delivery Address', 'Products', 'Delivery Fee', 'Status', 'Priority', 'Order Date', 'Delivery DateTime', 'Is Paid', 'Total (KD)']);

        // Fetch all orders based on current filters
        $filter = isset($_GET['filter']) ? sanitize_input($_GET['filter']) : 'all';
        $status_filter = isset($_GET['status_filter']) ? sanitize_input($_GET['status_filter']) : 'all';
        $priority_filter = isset($_GET['priority_filter']) ? sanitize_input($_GET['priority_filter']) : 'all';
        $search = isset($_GET['search']) ? sanitize_input($_GET['search']) : '';
        $sort_by = isset($_GET['sort_by']) ? sanitize_input($_GET['sort_by']) : 'order_date';
        $sort_order = isset($_GET['sort_order']) ? sanitize_input($_GET['sort_order']) : 'DESC';
        $orders_per_page = isset($_GET['orders_per_page']) ? intval($_GET['orders_per_page']) : ORDERS_PER_PAGE_DEFAULT;

        // Handle Date Range Filters
        $from_date = isset($_GET['from_date']) ? sanitize_input($_GET['from_date']) : '';
        $to_date = isset($_GET['to_date']) ? sanitize_input($_GET['to_date']) : '';

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

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

        // Priority Filter
        if ($priority_filter !== 'all') {
            $where_conditions[] = "o.priority = ?";
            $parameters[] = $priority_filter;
            $param_types .= "s";
        }

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

        // Date Range Filter on order_date
        if (!empty($from_date)) {
            $where_conditions[] = "o.order_date >= ?";
            $parameters[] = $from_date . " 00:00:00";
            $param_types .= "s";
        }

        if (!empty($to_date)) {
            $where_conditions[] = "o.order_date <= ?";
            $parameters[] = $to_date . " 23:59:59";
            $param_types .= "s";
        }

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

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

        // Fetch orders
        $orders_sql = "
            SELECT 
                o.id AS order_id,
                o.status,
                o.priority,
                o.order_date,
                o.delivery_datetime,
                o.delivery_fee,
                o.free_shipping,
                o.is_paid,
                c.name AS customer_name,
                c.contact,
                c.delivery_address
            FROM 
                orders o
            JOIN 
                customers c ON o.customer_id = c.id
            $where_clause
            $order_clause
        ";
        $orders_stmt = $conn->prepare($orders_sql);
        if ($orders_stmt) {
            if (!empty($where_clause)) {
                $orders_stmt->bind_param($param_types, ...$parameters);
            }
            $orders_stmt->execute();
            $orders_result = $orders_stmt->get_result();

            // Fetch order items for all orders
            $order_ids = [];
            while ($order = $orders_result->fetch_assoc()) {
                $order_ids[] = $order['order_id'];
            }

            $order_items = [];
            if (!empty($order_ids)) {
                $placeholders = implode(',', array_fill(0, count($order_ids), '?'));
                $types = str_repeat('i', count($order_ids));
                $order_items_sql = "
                    SELECT 
                        oi.order_id,
                        oi.product_id,
                        oi.quantity,
                        oi.packaging_preferences,
                        oi.discount_percentage,
                        oi.discount_reason,
                        p.name AS product_name,
                        p.size AS product_size,
                        p.price AS product_price
                    FROM 
                        order_items oi
                    JOIN 
                        products p ON oi.product_id = p.id
                    WHERE
                        oi.order_id IN ($placeholders)
                ";
                $items_stmt = $conn->prepare($order_items_sql);
                if ($items_stmt) {
                    $items_stmt->bind_param($types, ...$order_ids);
                    $items_stmt->execute();
                    $order_items_result = $items_stmt->get_result();
                    while ($item = $order_items_result->fetch_assoc()) {
                        $order_items[$item['order_id']][] = $item;
                    }
                    $items_stmt->close();
                }
            }

            // Populate CSV
            $orders_stmt->data_seek(0);
            while ($order = $orders_stmt->fetch_assoc()) {
                $items = isset($order_items[$order['order_id']]) ? $order_items[$order['order_id']] : [];
                $products = '';
                foreach ($items as $item) {
                    $products .= $item['product_name'] . ' (' . $item['quantity'] . ' x ' . number_format($item['product_price'], 2) . ' KD)';
                    if ($item['discount_percentage'] > 0) {
                        $products .= ' - Discount: ' . $item['discount_percentage'] . '% (' . $item['discount_reason'] . ')';
                    }
                    $products .= '; ';
                }
                $totals = calculate_order_totals($items, $order['delivery_fee'], $order['free_shipping']);
                fputcsv($output, [
                    $order['order_id'],
                    $order['customer_name'],
                    $order['contact'],
                    $order['delivery_address'],
                    $products,
                    $order['free_shipping'] ? 'Free' : number_format($order['delivery_fee'], 2) . ' KD',
                    $order['status'],
                    $order['priority'],
                    $order['order_date'],
                    $order['delivery_datetime'],
                    $order['is_paid'] ? 'Paid' : 'Unpaid',
                    number_format($totals['final_total'], 2) . ' KD'
                ]);
            }

            $orders_stmt->close();
        }

        fclose($output);
        exit();
    }
}

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

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

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

// Handle Date Range Filters
$from_date = isset($_GET['from_date']) ? sanitize_input($_GET['from_date']) : '';
$to_date = isset($_GET['to_date']) ? sanitize_input($_GET['to_date']) : '';

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

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

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

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

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

// Priority Filter
if ($priority_filter !== 'all') {
    $where_conditions[] = "o.priority = ?";
    $parameters[] = $priority_filter;
    $param_types .= "s";
}

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

// Date Range Filter on order_date
if (!empty($from_date)) {
    $where_conditions[] = "o.order_date >= ?";
    $parameters[] = $from_date . " 00:00:00";
    $param_types .= "s";
}

if (!empty($to_date)) {
    $where_conditions[] = "o.order_date <= ?";
    $parameters[] = $to_date . " 23:59:59";
    $param_types .= "s";
}

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

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

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

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

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

// Fetch orders with customer details based on filters and sorting
$orders_sql = "
    SELECT 
        o.id AS order_id,
        o.status,
        o.priority,
        o.order_date,
        o.delivery_datetime,
        o.delivery_fee,
        o.free_shipping,
        o.is_paid,
        c.name AS customer_name,
        c.contact,
        c.delivery_address
    FROM 
        orders o
    JOIN 
        customers c ON o.customer_id = c.id
    $where_clause
    $order_clause
    LIMIT ?, ?
";
$orders_stmt = $conn->prepare($orders_sql);
if ($orders_stmt) {
    // Bind parameters
    if (!empty($where_clause)) {
        // 'ii' for offset and limit
        $types = $param_types . "ii";
        $values = array_merge($parameters, [$offset, $orders_per_page]);
        $orders_stmt->bind_param($types, ...$values);
    } else {
        $orders_stmt->bind_param("ii", $offset, $orders_per_page);
    }
    $orders_stmt->execute();
    $orders_result = $orders_stmt->get_result();
} else {
    // Handle SQL preparation error
    die("Failed to prepare the orders query: " . $conn->error);
}

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

$order_items = [];
if (!empty($order_ids)) {
    $placeholders = implode(',', array_fill(0, count($order_ids), '?'));
    $types = str_repeat('i', count($order_ids));
    $order_items_sql = "
        SELECT 
            oi.order_id,
            oi.product_id,
            oi.quantity,
            oi.packaging_preferences,
            oi.discount_percentage,
            oi.discount_reason,
            p.name AS product_name,
            p.size AS product_size,
            p.price AS product_price
        FROM 
            order_items oi
        JOIN 
            products p ON oi.product_id = p.id
        WHERE
            oi.order_id IN ($placeholders)
    ";
    $items_stmt = $conn->prepare($order_items_sql);
    if ($items_stmt) {
        $items_stmt->bind_param($types, ...$order_ids);
        $items_stmt->execute();
        $order_items_result = $items_stmt->get_result();
        while ($item = $order_items_result->fetch_assoc()) {
            $order_items[$item['order_id']][] = $item;
        }
        $items_stmt->close();
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>View Orders - Kaak Alia Management System</title>
    
    <!-- Google Fonts -->
    <link href="https://fonts.googleapis.com/css2?family=Poppins:wght@400;600;700&family=Roboto:wght@400;500;700&family=Inter:wght@400;600;700&display=swap" rel="stylesheet">
    
    <!-- Bootstrap Icons -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css">
    
    <!-- Flatpickr CSS for Date Picker -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">
    
    <!-- UnoCSS CDN -->
    <script type="module">
        import 'https://unpkg.com/@unocss/runtime?module';
    </script>
    
    <!-- Custom Styles Removed -->
    
    <!-- Additional JavaScript for Print Functionality -->
    <script>
        function printOrders() {
            window.print();
        }

        function exportOrders() {
            window.location.href = 'view_orders.php?action=export';
        }
    </script>
</head>
<body class="bg-gray-100 font-roboto">
<main class="max-w-7xl mx-auto p-6 bg-white shadow-lg rounded-lg mt-10">
    <!-- Display Success or Error Messages -->
    <?php if (isset($_GET['success'])): ?>
        <?php if ($_GET['success'] == 1): ?>
            <div class="flex items-center bg-green-500 text-white text-sm font-bold px-4 py-3 rounded mb-4" role="alert">
                <i class="bi bi-check-circle-fill mr-2"></i>
                <span>Order status updated successfully!</span>
            </div>
        <?php elseif ($_GET['success'] == 2): ?>
            <div class="flex items-center bg-green-500 text-white text-sm font-bold px-4 py-3 rounded mb-4" role="alert">
                <i class="bi bi-check-circle-fill mr-2"></i>
                <span>Order deleted successfully!</span>
            </div>
        <?php elseif ($_GET['success'] == 3): ?>
            <div class="flex items-center bg-blue-500 text-white text-sm font-bold px-4 py-3 rounded mb-4" role="alert">
                <i class="bi bi-check-circle-fill mr-2"></i>
                <span>Order marked as paid successfully!</span>
            </div>
        <?php endif; ?>
    <?php endif; ?>

    <?php if (isset($_GET['error'])): ?>
        <?php if ($_GET['error'] == 1): ?>
            <div class="flex items-center bg-red-500 text-white text-sm font-bold px-4 py-3 rounded mb-4" role="alert">
                <i class="bi bi-exclamation-triangle-fill mr-2"></i>
                <span>Failed to update order status.</span>
            </div>
        <?php elseif ($_GET['error'] == 2): ?>
            <div class="flex items-center bg-red-500 text-white text-sm font-bold px-4 py-3 rounded mb-4" role="alert">
                <i class="bi bi-exclamation-triangle-fill mr-2"></i>
                <span>Invalid status selected.</span>
            </div>
        <?php elseif ($_GET['error'] == 3): ?>
            <div class="flex items-center bg-red-500 text-white text-sm font-bold px-4 py-3 rounded mb-4" role="alert">
                <i class="bi bi-exclamation-triangle-fill mr-2"></i>
                <span>Cannot set status to Delivered without a delivery date and time.</span>
            </div>
        <?php elseif ($_GET['error'] == 4): ?>
            <div class="flex items-center bg-red-500 text-white text-sm font-bold px-4 py-3 rounded mb-4" role="alert">
                <i class="bi bi-exclamation-triangle-fill mr-2"></i>
                <span>Failed to delete the order. Please try again.</span>
            </div>
        <?php elseif ($_GET['error'] == 5): ?>
            <div class="flex items-center bg-red-500 text-white text-sm font-bold px-4 py-3 rounded mb-4" role="alert">
                <i class="bi bi-exclamation-triangle-fill mr-2"></i>
                <span>Invalid order ID provided.</span>
            </div>
        <?php elseif ($_GET['error'] == 6): ?>
            <div class="flex items-center bg-red-500 text-white text-sm font-bold px-4 py-3 rounded mb-4" role="alert">
                <i class="bi bi-exclamation-triangle-fill mr-2"></i>
                <span>Failed to mark the order as paid. Please try again.</span>
            </div>
        <?php endif; ?>
    <?php endif; ?>


    <!-- Recent Orders Section -->
    <div class="mb-8">
        <h2 class="text-2xl font-poppins font-bold text-gray-800 mb-4">All Orders</h2>

        <!-- Filter and Search Section -->
        <div class="flex flex-col md:flex-row md:justify-between mb-6 space-y-4 md:space-y-0">
            <!-- Filters -->
            <div class="flex flex-wrap items-center space-x-4">
                <!-- Status Filter -->
                <div class="flex items-center space-x-2">
                    <i class="bi bi-filter-circle-fill text-xl text-gray-600"></i>
                    <label for="statusFilter" class="font-semibold text-gray-700">Status:</label>
                    <select id="statusFilter" onchange="applyFilters()" class="border border-gray-300 rounded px-3 py-2 focus:outline-none focus:ring-2 focus:ring-teal-500">
                        <option value="all" <?php echo $status_filter === 'all' ? 'selected' : ''; ?>>All</option>
                        <option value="Pending" <?php echo $status_filter === 'Pending' ? 'selected' : ''; ?>>Pending</option>
                        <option value="In Progress" <?php echo $status_filter === 'In Progress' ? 'selected' : ''; ?>>In Progress</option>
                        <option value="Completed" <?php echo $status_filter === 'Completed' ? 'selected' : ''; ?>>Completed</option>
                        <option value="Delivered" <?php echo $status_filter === 'Delivered' ? 'selected' : ''; ?>>Delivered</option>
                    </select>
                </div>

                <!-- Priority Filter -->
                <div class="flex items-center space-x-2">
                    <i class="bi bi-filter-circle-fill text-xl text-gray-600"></i>
                    <label for="priorityFilter" class="font-semibold text-gray-700">Priority:</label>
                    <select id="priorityFilter" onchange="applyFilters()" class="border border-gray-300 rounded px-3 py-2 focus:outline-none focus:ring-2 focus:ring-teal-500">
                        <option value="all" <?php echo $priority_filter === 'all' ? 'selected' : ''; ?>>All</option>
                        <option value="Quick" <?php echo $priority_filter === 'Quick' ? 'selected' : ''; ?>>Quick</option>
                        <option value="Important" <?php echo $priority_filter === 'Important' ? 'selected' : ''; ?>>Important</option>
                        <option value="Normal" <?php echo $priority_filter === 'Normal' ? 'selected' : ''; ?>>Normal</option>
                    </select>
                </div>

                <!-- Entries Per Page -->
                <div class="flex items-center space-x-2">
                    <i class="bi bi-layout-split text-xl text-gray-600"></i>
                    <label for="entriesPerPage" class="font-semibold text-gray-700">Show:</label>
                    <select id="entriesPerPage" onchange="applyFilters()" class="border border-gray-300 rounded px-3 py-2 focus:outline-none focus:ring-2 focus:ring-teal-500">
                        <option value="10" <?php echo $orders_per_page == 10 ? 'selected' : ''; ?>>10</option>
                        <option value="20" <?php echo $orders_per_page == 20 ? 'selected' : ''; ?>>20</option>
                        <option value="50" <?php echo $orders_per_page == 50 ? 'selected' : ''; ?>>50</option>
                    </select>
                    <span class="text-gray-700">entries</span>
                </div>

                <!-- Reset Filters Button -->
                <div class="flex items-center space-x-2">
                    <?php
                        // Build the reset URL with default filter values while preserving sort parameters
                        $reset_params = [
                            'filter' => 'all',
                            'status_filter' => 'all',
                            'priority_filter' => 'all',
                            'search' => null,
                            'from_date' => null,
                            'to_date' => null,
                            'sort_by' => $sort_by,
                            'sort_order' => $sort_order,
                            'orders_per_page' => $orders_per_page,
                            'page' => 1
                        ];
                    ?>
                    <a href="view_orders.php<?php echo build_query($reset_params); ?>" class="flex items-center space-x-1 bg-gray-500 text-white px-3 py-2 rounded hover:bg-gray-600 transition duration-300">
                        <i class="bi bi-arrow-counterclockwise"></i>
                        <span>Reset Filters</span>
                    </a>
                </div>
            </div>

            <!-- Search Input Above the Table -->
            <div class="w-full md:w-1/3">
                <form method="GET" action="view_orders.php" class="flex items-center space-x-2">
                    <input type="hidden" name="filter" value="<?php echo sanitize_input($filter); ?>">
                    <input type="hidden" name="status_filter" value="<?php echo sanitize_input($status_filter); ?>">
                    <input type="hidden" name="priority_filter" value="<?php echo sanitize_input($priority_filter); ?>">
                    <input type="hidden" name="from_date" value="<?php echo sanitize_input($from_date); ?>">
                    <input type="hidden" name="to_date" value="<?php echo sanitize_input($to_date); ?>">
                    <input type="hidden" name="sort_by" value="<?php echo sanitize_input($sort_by); ?>">
                    <input type="hidden" name="sort_order" value="<?php echo sanitize_input($sort_order); ?>">
                    <input type="text" name="search" class="flex-1 border border-gray-300 rounded px-4 py-2 focus:outline-none focus:ring-2 focus:ring-teal-500" placeholder="🔍 Search by Customer, ID, or Contact" value="<?php echo sanitize_input($search); ?>">
                    <button type="submit" class="flex items-center space-x-1 bg-teal-500 text-white px-4 py-2 rounded hover:bg-teal-600 transition duration-300">
                        <i class="bi bi-search"></i>
                        <span>Search</span>
                    </button>
                    <?php if (!empty($search)): ?>
                        <a href="view_orders.php<?php echo build_query(['search' => null, 'page' => 1]); ?>" class="flex items-center space-x-1 bg-red-500 text-white px-3 py-2 rounded hover:bg-red-600 transition duration-300">
                            <i class="bi bi-x-circle"></i>
                            <span>Reset</span>
                        </a>
                    <?php endif; ?>
                </form>
            </div>
        </div>

        <!-- Date Range Filter -->
        <div class="flex flex-wrap items-center space-x-4 mb-6">
            <div class="flex items-center space-x-2">
                <i class="bi bi-calendar-check-fill text-xl text-gray-600"></i>
                <label for="fromDate" class="font-semibold text-gray-700">From:</label>
                <input type="text" id="fromDate" name="from_date" class="border border-gray-300 rounded px-3 py-2 focus:outline-none focus:ring-2 focus:ring-teal-500" placeholder="YYYY-MM-DD" value="<?php echo sanitize_input($from_date); ?>">
            </div>
            <div class="flex items-center space-x-2">
                <i class="bi bi-calendar-check-fill text-xl text-gray-600"></i>
                <label for="toDate" class="font-semibold text-gray-700">To:</label>
                <input type="text" id="toDate" name="to_date" class="border border-gray-300 rounded px-3 py-2 focus:outline-none focus:ring-2 focus:ring-teal-500" placeholder="YYYY-MM-DD" value="<?php echo sanitize_input($to_date); ?>">
            </div>
            <button type="button" class="flex items-center space-x-1 bg-gray-700 text-white px-4 py-2 rounded hover:bg-gray-800 transition duration-300" onclick="applyDateFilters()">
                <i class="bi bi-calendar-fill"></i>
                <span>Apply</span>
            </button>
        </div>

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

        <!-- Orders Cards -->
        <div class="grid grid-cols-1 md:grid-cols-2 lg:grid-cols-3 gap-6">
            <?php if ($orders_result && $orders_result->num_rows > 0): ?>
                <?php while($order = $orders_result->fetch_assoc()): ?>
                    <?php
                        // Check if the order is new (created within last 24 hours)
                        $is_new = (strtotime($order['order_date']) >= strtotime('-1 day'));
                        // Generate WhatsApp link
                        $whatsapp_link = generate_whatsapp_link($order['contact']);

                        // Determine badge class based on status
                        switch ($order['status']) {
                            case 'Pending':
                                $badge_class = 'bg-yellow-400';
                                $icon = 'hourglass-split';
                                break;
                            case 'In Progress':
                                $badge_class = 'bg-indigo-500';
                                $icon = 'arrow-repeat';
                                break;
                            case 'Completed':
                                $badge_class = 'bg-green-500';
                                $icon = 'check-circle-fill';
                                break;
                            case 'Delivered':
                                $badge_class = 'bg-teal-500';
                                $icon = 'box-seam';
                                break;
                            default:
                                $badge_class = 'bg-gray-400';
                                $icon = 'info-circle';
                        }

                        // Fetch order items
                        $items = isset($order_items[$order['order_id']]) ? $order_items[$order['order_id']] : [];

                        // Calculate order totals, including delivery fee if applicable
                        $totals = calculate_order_totals($items, $order['delivery_fee'], $order['free_shipping']);
                    ?>
                    <div class="bg-white shadow-lg rounded-lg p-6">
                        <!-- Header -->
                        <div class="flex justify-between items-center mb-4">
                            <div>
                                <h3 class="text-xl font-poppins font-bold text-gray-800">Order #<?= htmlspecialchars($order['order_id']) ?></h3>
                                <?php if ($is_new): ?>
                                    <span class="inline-flex items-center px-2.5 py-0.5 rounded-full text-xs font-medium bg-teal-500 text-white">New</span>
                                <?php endif; ?>
                            </div>
                            <div>
                                <span class="inline-flex items-center px-2 py-0.5 rounded-full text-xs font-semibold <?= $badge_class ?> text-white">
                                    <i class="bi bi-<?= $icon ?> mr-1"></i> <?= htmlspecialchars($order['status']) ?>
                                </span>
                            </div>
                        </div>

                        <!-- Customer Info -->
                        <div class="mb-4">
                            <h4 class="text-lg font-semibold text-gray-700">Customer Details</h4>
                            <p class="text-gray-600">
                                <strong>Name:</strong> <?= htmlspecialchars($order['customer_name']) ?><br>
                                <strong>Contact:</strong> <a href="<?= $whatsapp_link ?>" target="_blank" class="text-teal-500 hover:underline"><i class="bi bi-whatsapp"></i> <?= htmlspecialchars($order['contact']) ?></a><br>
                                <strong>Address:</strong> <?= nl2br(htmlspecialchars($order['delivery_address'])) ?>
                            </p>
                        </div>

                        <!-- Products -->
                        <div class="mb-4">
                            <h4 class="text-lg font-semibold text-gray-700">Products</h4>
                            <?php if (!empty($items)): ?>
                                <ul class="list-disc list-inside text-gray-600">
                                    <?php foreach ($items as $item): ?>
                                        <li>
                                            <strong><?= htmlspecialchars($item['product_name']) ?></strong>
                                            <?php if (!empty($item['product_size'])): ?>
                                                (<?= htmlspecialchars($item['product_size']) ?>)
                                            <?php endif; ?> -
                                            <?= htmlspecialchars($item['quantity']) ?> x <?= number_format($item['product_price'], 2) ?> KD
                                            <?php if (floatval($item['discount_percentage']) > 0): ?>
                                                <span class="text-red-500">-<?= htmlspecialchars($item['discount_percentage']) ?>%</span>
                                                (<?= htmlspecialchars($item['discount_reason']) ?>)
                                            <?php endif; ?>
                                        </li>
                                    <?php endforeach; ?>
                                </ul>
                            <?php else: ?>
                                <p class="text-gray-500">No products found.</p>
                            <?php endif; ?>
                        </div>

                        <!-- Totals -->
                        <div class="mb-4">
                            <h4 class="text-lg font-semibold text-gray-700">Totals</h4>
                            <p class="text-gray-600">
                                <strong>Total Before Discounts:</strong> <?= number_format($totals['total_before_discounts'], 2) ?> KD<br>
                                <strong>Total Discounts:</strong> <?= number_format($totals['total_discounts'], 2) ?> KD<br>
                                <strong>Delivery Fee:</strong> <?= $order['free_shipping'] ? 'Free' : number_format($order['delivery_fee'], 2) . ' KD' ?><br>
                                <strong><span class="text-xl"><?= number_format($totals['final_total'], 2) ?> KD</span></strong>
                                <?php if($order['is_paid']): ?>
                                    <span class="inline-flex items-center px-2 py-0.5 rounded-full text-xs font-medium bg-green-500 text-white ml-2">Paid</span>
                                <?php else: ?>
                                    <span class="inline-flex items-center px-2 py-0.5 rounded-full text-xs font-medium bg-red-500 text-white ml-2">Unpaid</span>
                                <?php endif; ?>
                            </p>
                        </div>

                        <!-- Actions -->
                        <div class="flex flex-wrap space-x-2">
                            <!-- Edit Order -->
                            <button onclick="editOrder(<?= htmlspecialchars($order['order_id']) ?>)" class="flex items-center space-x-1 bg-blue-500 text-white px-3 py-2 rounded hover:bg-blue-600 transition duration-300">
                                <i class="bi bi-pencil-fill"></i>
                                <span>Edit</span>
                            </button>
                            
                            <!-- Change Status -->
                            <button onclick="changeStatus(<?= htmlspecialchars($order['order_id']) ?>)" class="flex items-center space-x-1 bg-indigo-500 text-white px-3 py-2 rounded hover:bg-indigo-600 transition duration-300">
                                <i class="bi bi-arrow-repeat"></i>
                                <span>Status</span>
                            </button>
                            
                            <!-- Mark as Paid -->
                            <?php if(!$order['is_paid']): ?>
                                <button onclick="markPaid(<?= htmlspecialchars($order['order_id']) ?>)" class="flex items-center space-x-1 bg-green-500 text-white px-3 py-2 rounded hover:bg-green-600 transition duration-300">
                                    <i class="bi bi-cash-stack"></i>
                                    <span>Mark Paid</span>
                                </button>
                            <?php endif; ?>
                            
                            <!-- Delete Order -->
                            <button onclick="confirmDelete(<?= htmlspecialchars($order['order_id']) ?>)" class="flex items-center space-x-1 bg-red-500 text-white px-3 py-2 rounded hover:bg-red-600 transition duration-300">
                                <i class="bi bi-trash-fill"></i>
                                <span>Delete</span>
                            </button>
                            
                            <!-- View Customer Order -->
                            <a href="view_order_customer.php?order_id=<?= htmlspecialchars($order['order_id']) ?>" class="flex items-center space-x-1 bg-teal-500 text-white px-3 py-2 rounded hover:bg-teal-600 transition duration-300" target="_blank">
                                <i class="bi bi-eye-fill"></i>
                                <span>View</span>
                            </a>
                        </div>
                    </div>
                <?php endwhile; ?>
            <?php else: ?>
                <div class="col-span-1 md:col-span-2 lg:col-span-3 text-center text-gray-500 py-4">
                    No orders found.
                </div>
            <?php endif; ?>
        </div>

        <!-- Pagination -->
        <?php if ($total_pages > 1): ?>
            <div class="flex justify-center mt-6">
                <nav aria-label="Order pagination">
                    <ul class="inline-flex -space-x-px">
                        <!-- Previous -->
                        <li>
                            <a href="view_orders.php<?php
                                $prev_page = max($page - 1, 1);
                                $reset_params_prev = [
                                    'filter' => $filter,
                                    'status_filter' => $status_filter,
                                    'priority_filter' => $priority_filter,
                                    'search' => $search,
                                    'from_date' => $from_date,
                                    'to_date' => $to_date,
                                    'sort_by' => $sort_by,
                                    'sort_order' => $sort_order,
                                    'orders_per_page' => $orders_per_page,
                                    'page' => $prev_page
                                ];
                                echo build_query($reset_params_prev);
                            ?>" class="py-2 px-3 ml-0 leading-tight text-gray-500 bg-white border border-gray-300 rounded-l-lg hover:bg-gray-100 hover:text-gray-700">Previous</a>
                        </li>

                        <!-- Page Numbers -->
                        <?php
                            // Determine the range of pages to show
                            $max_links = 5;
                            $start = max($page - floor($max_links / 2), 1);
                            $end = min($start + $max_links - 1, $total_pages);
                            if ($end - $start < $max_links - 1) {
                                $start = max($end - $max_links + 1, 1);
                            }
                        ?>
                        <?php for ($i = $start; $i <= $end; $i++): ?>
                            <li>
                                <a href="view_orders.php<?php
                                    $reset_params_page = [
                                        'filter' => $filter,
                                        'status_filter' => $status_filter,
                                        'priority_filter' => $priority_filter,
                                        'search' => $search,
                                        'from_date' => $from_date,
                                        'to_date' => $to_date,
                                        'sort_by' => $sort_by,
                                        'sort_order' => $sort_order,
                                        'orders_per_page' => $orders_per_page,
                                        'page' => $i
                                    ];
                                    echo build_query($reset_params_page);
                                ?>" class="py-2 px-3 leading-tight <?php echo ($page == $i) ? 'text-white bg-teal-500 border-teal-500' : 'text-gray-500 bg-white border-gray-300'; ?> border hover:bg-gray-100 hover:text-gray-700">
                                    <?php echo $i; ?>
                                </a>
                            </li>
                        <?php endfor; ?>

                        <!-- Next -->
                        <li>
                            <a href="view_orders.php<?php
                                $next_page = min($page + 1, $total_pages);
                                $reset_params_next = [
                                    'filter' => $filter,
                                    'status_filter' => $status_filter,
                                    'priority_filter' => $priority_filter,
                                    'search' => $search,
                                    'from_date' => $from_date,
                                    'to_date' => $to_date,
                                    'sort_by' => $sort_by,
                                    'sort_order' => $sort_order,
                                    'orders_per_page' => $orders_per_page,
                                    'page' => $next_page
                                ];
                                echo build_query($reset_params_next);
                            ?>" class="py-2 px-3 leading-tight text-gray-500 bg-white border border-gray-300 rounded-r-lg hover:bg-gray-100 hover:text-gray-700">Next</a>
                        </li>
                    </ul>
                </nav>
            </div>
        <?php endif; ?>

        <!-- Footer -->
        <div class="text-center text-gray-500 mt-8">
            &copy; <?= date("Y") ?> Kaak Alia Bakery. All rights reserved.
        </div>
    </div>

    <!-- Delete Confirmation Modal -->
    <div id="deleteModal" class="hidden fixed inset-0 bg-black bg-opacity-50 flex items-center justify-center">
        <div class="bg-white rounded-lg shadow-lg w-80">
            <div class="p-4 border-b">
                <h3 class="text-lg font-semibold text-gray-800">Confirm Deletion</h3>
            </div>
            <div class="p-4">
                <p class="text-gray-700">Are you sure you want to delete this order? This action cannot be undone.</p>
            </div>
            <div class="flex justify-end p-4 border-t">
                <button onclick="closeDeleteModal()" class="bg-gray-500 text-white px-4 py-2 rounded mr-2 hover:bg-gray-600 transition duration-300">Cancel</button>
                <button id="confirmDeleteBtn" class="bg-red-500 text-white px-4 py-2 rounded hover:bg-red-600 transition duration-300">Delete</button>
            </div>
        </div>
    </div>
</main>
    <!-- Flatpickr JS for Date Picker -->
    <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>

    <!-- Bootstrap Icons JS (optional if interactivity is needed) -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.js"></script>

    <!-- UnoCSS Runtime (ensure it's loaded) -->
    <script type="module">
        import 'https://unpkg.com/@unocss/runtime?module';
    </script>

    <!-- JavaScript for Interactivity -->
    <script>
        // Initialize Flatpickr for date inputs
        flatpickr("#fromDate", {
            dateFormat: "Y-m-d",
            maxDate: "today",
            allowInput: true
        });

        flatpickr("#toDate", {
            dateFormat: "Y-m-d",
            maxDate: "today",
            allowInput: true
        });

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

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

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

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

        // Function to confirm deletion using custom modal
        function confirmDelete(orderId) {
            const deleteModal = document.getElementById('deleteModal');
            const confirmDeleteBtn = document.getElementById('confirmDeleteBtn');

            // Show the modal
            deleteModal.classList.remove('hidden');

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

        // Function to close the delete modal
        function closeDeleteModal() {
            const deleteModal = document.getElementById('deleteModal');
            deleteModal.classList.add('hidden');
        }

        // Function to handle status change
        function changeStatus(orderId) {
            const newStatus = prompt("Enter new status (Pending, In Progress, Completed, Delivered):");
            if (newStatus) {
                const valid_statuses = ['Pending', 'In Progress', 'Completed', 'Delivered'];
                if (valid_statuses.includes(newStatus.trim())) {
                    if (confirm(`Are you sure you want to change the status to "${newStatus}"?`)) {
                        window.location.href = `view_orders.php?action=update_status&order_id=${orderId}&new_status=${encodeURIComponent(newStatus)}`;
                    }
                } else {
                    alert("Invalid status entered.");
                }
            }
        }

        // Function to mark an order as paid
        function markPaid(orderId) {
            if (confirm("Are you sure you want to mark this order as paid?")) {
                window.location.href = `view_orders.php?action=mark_paid&order_id=${orderId}`;
            }
        }

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

        // Function to apply filters (on status change, priority change)
        function applyFilters() {
            const urlParams = new URLSearchParams(window.location.search);
            const statusFilter = document.getElementById('statusFilter').value;
            const priorityFilter = document.getElementById('priorityFilter').value;
            const ordersPerPage = document.getElementById('entriesPerPage').value;
            const fromDate = document.getElementById('fromDate').value;
            const toDate = document.getElementById('toDate').value;

            urlParams.set('status_filter', statusFilter);
            urlParams.set('priority_filter', priorityFilter);
            urlParams.set('orders_per_page', ordersPerPage);
            urlParams.set('from_date', fromDate);
            urlParams.set('to_date', toDate);
            urlParams.set('page', 1); // Reset to first page

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

        // Function to apply date filters separately
        function applyDateFilters() {
            applyFilters();
        }

        // Close delete modal when clicking outside of the modal content
        window.onclick = function(event) {
            const deleteModal = document.getElementById('deleteModal');
            if (event.target == deleteModal) {
                deleteModal.classList.add('hidden');
            }
        }
    </script>
</body>
</html>
<?php
// Flush the output buffer
ob_end_flush();
// No closing PHP tag to prevent unwanted output
?>) and cool, but i want to make in row add two cards, and when i press on chagne stutus, make it as popup to change not useing alert chorome or any broswer plz, and make it cool and respovnes and add more consistent and خلي الكرت مرتب ومنسق وشكله شيك وكمان خلي لونه يختلف علي حسب الحالة and make it clear and add view order customer (view_order_customer.php) and type full codes please, and add more codes and make it great and type full codes