in this codes (<?php
// public/orders/edit_order.php

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

// Include necessary files
require_once __DIR__ . '/../../includes/auth.php';
require_once __DIR__ . '/../../includes/db_connect.php';

// Function to sanitize input
function sanitize_input($input) {
    return htmlspecialchars(trim($input), ENT_QUOTES, 'UTF-8');
}

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

// Retrieve and sanitize the order ID from GET parameters
$order_id = isset($_GET['id']) ? intval($_GET['id']) : 0;

// Redirect if no valid order ID is provided
if ($order_id <= 0) {
    header("Location: view_orders.php?error=Invalid Order ID");
    exit();
}

// Fetch order details along with customer information and is_paid status
$order_sql = "SELECT o.*, c.name AS customer_name, c.contact AS customer_contact, c.delivery_address 
              FROM orders o 
              JOIN customers c ON o.customer_id = c.id 
              WHERE o.id = ? LIMIT 1";
$order_stmt = $conn->prepare($order_sql);
if (!$order_stmt) {
    die("Prepare statement failed: " . $conn->error);
}
$order_stmt->bind_param("i", $order_id);
$order_stmt->execute();
$order_result = $order_stmt->get_result();

if ($order_result->num_rows != 1) {
    header("Location: view_orders.php?error=Order Not Found");
    exit();
}

$order = $order_result->fetch_assoc();
$order_stmt->close();

// Fetch existing order items
$order_items_sql = "SELECT oi.*, p.name AS product_name, p.size, p.price 
                   FROM order_items oi 
                   JOIN products p ON oi.product_id = p.id 
                   WHERE oi.order_id = ?";
$order_items_stmt = $conn->prepare($order_items_sql);
if (!$order_items_stmt) {
    die("Prepare statement failed: " . $conn->error);
}
$order_items_stmt->bind_param("i", $order_id);
$order_items_stmt->execute();
$order_items_result = $order_items_stmt->get_result();
$order_items = [];
while ($item = $order_items_result->fetch_assoc()) {
    $order_items[] = $item;
}
$order_items_stmt->close();

// Fetch products for selection
$products_sql = "SELECT * FROM products ORDER BY name ASC";
$products_result = $conn->query($products_sql);

// Handle form submission for updating the order
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    // Retrieve and sanitize form inputs
    $customer_name = sanitize_input($_POST['customer_name']);
    $contact = sanitize_input($_POST['contact']);
    $delivery_address = sanitize_input($_POST['delivery_address']);
    $status = sanitize_input($_POST['status']);
    $priority = sanitize_input($_POST['priority']);
    $delivery_datetime = isset($_POST['delivery_datetime']) && !empty($_POST['delivery_datetime']) ? sanitize_input($_POST['delivery_datetime']) : null;
    $delivery_date = isset($_POST['delivery_date']) && !empty($_POST['delivery_date']) ? sanitize_input($_POST['delivery_date']) : null;

    // New fields based on database schema
    $delivery_fee = isset($_POST['delivery_fee']) && is_numeric($_POST['delivery_fee']) ? floatval($_POST['delivery_fee']) : 0.00;
    $free_shipping = isset($_POST['free_shipping']) ? 1 : 0;
    $is_paid = isset($_POST['is_paid']) ? 1 : 0; // New field

    // If free shipping is selected, set delivery_fee to 0.00
    if ($free_shipping) {
        $delivery_fee = 0.00;
    }

    // Handle existing order items
    $existing_item_ids = isset($_POST['existing_item_id']) ? $_POST['existing_item_id'] : [];
    $product_ids = isset($_POST['product_id']) ? $_POST['product_id'] : [];
    $quantities = isset($_POST['quantity']) ? $_POST['quantity'] : [];
    $packaging_preferences = isset($_POST['packaging_preferences']) ? $_POST['packaging_preferences'] : [];
    $discount_percentages = isset($_POST['discount_percentage']) ? $_POST['discount_percentage'] : [];
    $discount_reasons = isset($_POST['discount_reason']) ? $_POST['discount_reason'] : [];

    // Handle new order items
    $new_product_ids = isset($_POST['new_product_id']) ? $_POST['new_product_id'] : [];
    $new_quantities = isset($_POST['new_quantity']) ? $_POST['new_quantity'] : [];
    $new_packaging_preferences = isset($_POST['new_packaging_preferences']) ? $_POST['new_packaging_preferences'] : [];
    $new_discount_percentages = isset($_POST['new_discount_percentage']) ? $_POST['new_discount_percentage'] : [];
    $new_discount_reasons = isset($_POST['new_discount_reason']) ? $_POST['new_discount_reason'] : [];

    // Basic validation
    if (empty($customer_name)) {
        $error = "Customer name is required.";
    } elseif (empty($status)) {
        $error = "Order status is required.";
    } elseif (empty($priority)) {
        $error = "Order priority is required.";
    } elseif ((empty($existing_item_ids) && empty($new_product_ids))) {
        $error = "At least one product must be selected.";
    } else {
        // Validate each existing product entry
        foreach ($existing_item_ids as $index => $item_id) {
            if (empty($product_ids[$index])) {
                $error = "Product selection is required for all existing items.";
                break;
            }
            if (intval($quantities[$index]) <= 0) {
                $error = "Quantity must be at least 1 for all existing items.";
                break;
            }
            if ($discount_percentages[$index] !== '' && (floatval($discount_percentages[$index]) < 0 || floatval($discount_percentages[$index]) > 100)) {
                $error = "Discount percentage must be between 0 and 100.";
                break;
            }
        }

        // Validate new product entries
        foreach ($new_product_ids as $index => $pid) {
            if (!empty($pid)) {
                if (intval($new_quantities[$index]) <= 0) {
                    $error = "Quantity must be at least 1 for all new items.";
                    break;
                }
                if ($new_discount_percentages[$index] !== '' && (floatval($new_discount_percentages[$index]) < 0 || floatval($new_discount_percentages[$index]) > 100)) {
                    $error = "Discount percentage must be between 0 and 100 for all new items.";
                    break;
                }
            }
        }

        if ($delivery_datetime && strtotime(str_replace('T', ' ', $delivery_datetime)) < strtotime(date('Y-m-d H:i'))) {
            $error = "Delivery date and time cannot be in the past.";
        }

        if ($delivery_date && strtotime($delivery_date) < strtotime(date('Y-m-d'))) {
            $error = "Delivery date cannot be in the past.";
        }
    }

    if (empty($error)) {
        // Start transaction
        $conn->begin_transaction();

        try {
            // Update customer details
            $stmt = $conn->prepare("UPDATE customers SET name = ?, contact = ?, delivery_address = ? WHERE id = ?");
            if (!$stmt) {
                throw new Exception("Prepare statement failed: " . $conn->error);
            }
            $stmt->bind_param("sssi", $customer_name, $contact, $delivery_address, $order['customer_id']);
            if (!$stmt->execute()) {
                throw new Exception("Execute failed: " . $stmt->error);
            }
            $stmt->close();

            // Update orders table with new fields including is_paid
            $stmt = $conn->prepare("UPDATE orders SET status = ?, priority = ?, delivery_datetime = ?, delivery_date = ?, delivery_fee = ?, free_shipping = ?, is_paid = ? WHERE id = ?");
            if (!$stmt) {
                throw new Exception("Prepare statement failed: " . $conn->error);
            }
            $stmt->bind_param("ssssdiii", $status, $priority, $delivery_datetime, $delivery_date, $delivery_fee, $free_shipping, $is_paid, $order_id);
            if (!$stmt->execute()) {
                throw new Exception("Execute failed: " . $stmt->error);
            }
            $stmt->close();

            // Update existing order items
            foreach ($existing_item_ids as $index => $item_id) {
                $item_id = intval($item_id);
                $pid = intval($product_ids[$index]);
                $qty = intval($quantities[$index]);
                $pack_pref = sanitize_input($packaging_preferences[$index]);
                $discount_pct = $discount_percentages[$index] !== '' ? floatval($discount_percentages[$index]) : 0.00;
                $discount_rsn = sanitize_input($discount_reasons[$index]);

                $stmt = $conn->prepare("UPDATE order_items SET product_id = ?, quantity = ?, packaging_preferences = ?, discount_percentage = ?, discount_reason = ? WHERE id = ?");
                if (!$stmt) {
                    throw new Exception("Prepare statement failed: " . $conn->error);
                }
                $stmt->bind_param("iisdsi", $pid, $qty, $pack_pref, $discount_pct, $discount_rsn, $item_id);
                if (!$stmt->execute()) {
                    throw new Exception("Execute failed: " . $stmt->error);
                }
                $stmt->close();
            }

            // Insert new order items
            $stmt = $conn->prepare("INSERT INTO order_items (order_id, product_id, quantity, packaging_preferences, discount_percentage, discount_reason) VALUES (?, ?, ?, ?, ?, ?)");
            if (!$stmt) {
                throw new Exception("Prepare statement failed: " . $conn->error);
            }

            for ($i = 0; $i < count($new_product_ids); $i++) {
                $new_pid = intval($new_product_ids[$i]);
                $new_qty = intval($new_quantities[$i]);
                $new_pack_pref = sanitize_input($new_packaging_preferences[$i]);
                $new_discount_pct = $new_discount_percentages[$i] !== '' ? floatval($new_discount_percentages[$i]) : 0.00;
                $new_discount_rsn = sanitize_input($new_discount_reasons[$i]);

                if (!empty($new_pid)) {
                    $stmt->bind_param("iiidsi", $order_id, $new_pid, $new_qty, $new_pack_pref, $new_discount_pct, $new_discount_rsn);
                    if (!$stmt->execute()) {
                        throw new Exception("Execute failed: " . $stmt->error);
                    }
                }
            }
            $stmt->close();

            // Commit transaction
            $conn->commit();

            $success = "Order updated successfully!";

            // Refresh order data
            // Fetch updated order items
            $order_items_sql = "SELECT oi.*, p.name AS product_name, p.size, p.price 
                               FROM order_items oi 
                               JOIN products p ON oi.product_id = p.id 
                               WHERE oi.order_id = ?";
            $order_items_stmt = $conn->prepare($order_items_sql);
            if (!$order_items_stmt) {
                throw new Exception("Prepare statement failed: " . $conn->error);
            }
            $order_items_stmt->bind_param("i", $order_id);
            $order_items_stmt->execute();
            $order_items_result = $order_items_stmt->get_result();
            $order_items = [];
            while ($item = $order_items_result->fetch_assoc()) {
                $order_items[] = $item;
            }
            $order_items_stmt->close();
        } catch (Exception $e) {
            // Rollback transaction
            $conn->rollback();
            $error = "Error updating order: " . sanitize_input($e->getMessage());
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Edit Order #<?= htmlspecialchars($order_id) ?> - Kaak Alia Management System</title>
    
    <!-- Google Fonts -->
    <link href="https://fonts.googleapis.com/css2?family=Inter:wght@400;600;700&display=swap" rel="stylesheet">
    
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="/kaak_alia_management_system/assets/bootstrap/css/bootstrap.min.css">
    
    <!-- Bootstrap Icons -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap-icons@1.10.5/font/bootstrap-icons.css">
    
    <!-- Flatpickr CSS for Date Picker -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">
    
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <!-- Custom Styles -->
    <style>
        /* (Same CSS as provided by the user or adjust as needed) */
        :root {
            /* Primary Colors */
            --background-color: #FFFFFF; /* Crisp White */
            --light-grey: #F5F5F5;
            --medium-grey: #CCCCCC;
            --dark-grey: #333333;
            --charcoal: #374151; /* Updated to requested color */

            /* Accent Colors */
            --accent-blue: #4A90E2;
            --accent-green: #50C878;
            --accent-red: #FF6B6B;
            --accent-teal: #20B2AA;
            --accent-soft-blue: #A3D2CA;
            --accent-purple: #8A65FF;
            --accent-orange: #FFA500;

            /* Text Colors */
            --primary-text: var(--dark-grey);
            --secondary-text: #666666;

            /* UI Elements */
            --input-border: var(--medium-grey);
            --input-focus-border: var(--accent-teal);
            --box-shadow: 0 2px 8px rgba(0, 0, 0, 0.05);
            --box-border-radius: 12px;
            --hover-shadow: rgba(0, 0, 0, 0.2);

            /* Button Colors */
            --button-add-order: #6C63FF;
            --button-view-orders: #FFA500;
            --button-manage-products: #20B2AA;
            --button-manage-users: #4B0082;
            --button-generate-reports: #FF6B6B;

            /* Status Badge Colors */
            --badge-pending: #FFD166;
            --badge-in-progress: #6A4C93;
            --badge-completed: #4CAF50;
            --badge-delivered: #20B2AA;
            --badge-paid: #50C878; /* Green for Paid */
            --badge-not-paid: #FF6B6B; /* Red for Not Paid */
        }

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

        /* Global Styles */
        body, html {
            font-family: 'Inter', sans-serif;
            background-color: var(--background-color);
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }

        .container-body {
            padding: 20px;
            max-width: 1200px;
            margin: 0 auto;
        }

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

        /* Header Styles */
        .header {
            padding: 20px 30px;
            background-color: var(--charcoal);
            border-radius: 12px;
            display: flex;
            align-items: center;
            justify-content: center;
            margin-bottom: 30px;
            box-shadow: var(--box-shadow);
        }

        .header h1 {
            color: var(--background-color);
            font-size: 28px;
            font-weight: 700;
        }

        /* Container Styles */
        .container-custom {
            max-width: 1000px;
            margin: auto;
            background-color: var(--background-color);
            padding: 40px;
            border: 1px solid var(--medium-grey);
            border-radius: var(--box-border-radius);
            box-shadow: var(--box-shadow);
        }

        /* Success and Error Alerts */
        .alert-custom {
            border-radius: var(--box-border-radius);
            padding: 20px 25px;
            margin-bottom: 30px;
            text-align: center;
            font-size: 18px;
            font-weight: 600;
        }

        .alert-success-custom {
            background-color: var(--accent-green);
            color: var(--background-color);
        }

        .alert-danger-custom {
            background-color: var(--accent-red);
            color: var(--background-color);
        }

        /* Form Styles */
        .form-label {
            font-weight: 600;
            color: var(--primary-text);
            font-size: 16px;
        }

        .form-control {
            background-color: #F9F9F9;
            border: 1px solid var(--input-border);
            border-radius: 8px;
            padding: 12px 16px;
            transition: border-color 0.3s, box-shadow 0.3s;
            font-size: 16px;
        }

        .form-control:focus {
            border-color: var(--input-focus-border);
            box-shadow: 0 0 5px rgba(32, 178, 170, 0.5);
            background-color: var(--background-color);
        }

        .btn-primary-custom {
            background-color: var(--button-add-order);
            border: none;
            color: var(--background-color);
            padding: 12px 24px;
            font-size: 16px;
            font-weight: 600;
            border-radius: 8px;
            transition: background-color 0.3s, transform 0.3s;
            cursor: pointer;
        }

        .btn-primary-custom:hover {
            background-color: #5750d3;
            transform: scale(1.05);
        }

        .btn-secondary-custom {
            background-color: var(--accent-soft-blue);
            border: none;
            color: var(--primary-text);
            padding: 12px 24px;
            font-size: 16px;
            font-weight: 600;
            border-radius: 8px;
            transition: background-color 0.3s, transform 0.3s;
            cursor: pointer;
            text-decoration: none;
            display: inline-block;
        }

        .btn-secondary-custom:hover {
            background-color: #8AB6D6;
            transform: scale(1.05);
        }

        .remove-product-button {
            background-color: var(--accent-red);
            color: var(--background-color);
            border: none;
            border-radius: 50%;
            width: 35px;
            height: 35px;
            cursor: pointer;
            transition: background-color 0.3s, transform 0.3s;
            font-size: 18px;
            font-weight: bold;
            display: flex;
            align-items: center;
            justify-content: center;
        }

        .remove-product-button:hover {
            background-color: #e63946;
            transform: scale(1.1);
        }

        /* Footer Styles */
        .footer {
            background-color: var(--charcoal);
            color: var(--background-color);
            padding: 20px;
            text-align: center;
            border-radius: 12px;
            font-size: 16px;
            line-height: 1.5;
            margin-top: 40px;
            box-shadow: var(--box-shadow);
        }

        /* Responsive Design Enhancements */
        @media (max-width: 768px) {
            .container-custom {
                padding: 30px;
            }

            .btn {
                width: 100%;
                margin-bottom: 10px;
            }

            .remove-product-button {
                width: 30px;
                height: 30px;
            }
        }

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

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

            .form-label {
                font-size: 14px;
            }

            .form-control {
                padding: 10px 12px;
                font-size: 14px;
            }

            .btn-primary-custom, .btn-secondary-custom {
                font-size: 14px;
                padding: 10px 18px;
            }

            .footer {
                font-size: 14px;
            }
        }
    </style>

    <!-- Additional JavaScript for dynamic product addition, form enhancements, and payment functionality -->
    <script>
        function addOrderItemRow() {
            const orderItemsContainer = document.getElementById('order-items-container');
            const itemCount = orderItemsContainer.children.length;

            const orderItemRow = document.createElement('div');
            orderItemRow.className = 'order-item-row border p-4 mb-4 shadow-sm';
            orderItemRow.style.borderRadius = '12px';
            orderItemRow.innerHTML = `
                <div class="row g-3">
                    <div class="col-md-4">
                        <label class="form-label" for="new_product_id_${itemCount}">Product <span class="text-danger">*</span></label>
                        <select name="new_product_id[]" id="new_product_id_${itemCount}" class="form-control" required>
                            <option value="">Select Product</option>
                            <?php
                            // Reset the products_result pointer
                            $products_result->data_seek(0);
                            while($product = $products_result->fetch_assoc()): ?>
                                <option value="<?= htmlspecialchars($product['id']) ?>">
                                    <?= htmlspecialchars($product['name'] . " - " . $product['size'] . " (" . number_format($product['price'], 2) . " KD)") ?>
                                </option>
                            <?php endwhile; ?>
                        </select>
                    </div>
                    <div class="col-md-2">
                        <label class="form-label" for="new_quantity_${itemCount}">Quantity <span class="text-danger">*</span></label>
                        <input type="number" name="new_quantity[]" id="new_quantity_${itemCount}" class="form-control" min="1" value="1" required>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label" for="new_packaging_preferences_${itemCount}">Packaging Preferences</label>
                        <input type="text" name="new_packaging_preferences[]" id="new_packaging_preferences_${itemCount}" class="form-control" placeholder="e.g., Gift Wrap">
                    </div>
                    <div class="col-md-2">
                        <label class="form-label" for="new_discount_percentage_${itemCount}">Discount (%)</label>
                        <input type="number" name="new_discount_percentage[]" id="new_discount_percentage_${itemCount}" class="form-control" min="0" max="100" step="0.01" value="0.00">
                    </div>
                    <div class="col-md-1 d-flex align-items-end">
                        <button type="button" class="remove-product-button" onclick="removeOrderItemRow(this)" title="Remove Product">✕</button>
                    </div>
                </div>
                <div class="row mt-3">
                    <div class="col-md-12">
                        <label class="form-label" for="new_discount_reason_${itemCount}">Discount Reason</label>
                        <input type="text" name="new_discount_reason[]" id="new_discount_reason_${itemCount}" class="form-control" placeholder="e.g., Seasonal Sale">
                    </div>
                </div>
            `;
            orderItemsContainer.appendChild(orderItemRow);
        }

        function removeOrderItemRow(button) {
            const orderItemRow = button.closest('.order-item-row');
            orderItemRow.remove();
        }

        function removeExistingOrderItemRow(button) {
            const orderItemRow = button.closest('.order-item-row');
            // Optionally, implement functionality to handle backend deletion if necessary
            // For simplicity, we'll remove it directly
            orderItemRow.remove();
            // To handle deletion in backend, you'd need to track removed IDs and delete them from database
            // This requires additional implementation
        }

        // Function to toggle delivery fee input based on free shipping checkbox
        function toggleDeliveryFee() {
            const freeShippingCheckbox = document.getElementById('free_shipping');
            const deliveryFeeInput = document.getElementById('delivery_fee');
            if (freeShippingCheckbox.checked) {
                deliveryFeeInput.value = '0.00';
                deliveryFeeInput.disabled = true;
            } else {
                deliveryFeeInput.disabled = false;
            }
        }

        // Initialize on page load
        document.addEventListener('DOMContentLoaded', function() {
            const freeShippingCheckbox = document.getElementById('free_shipping');
            if (freeShippingCheckbox) {
                freeShippingCheckbox.addEventListener('change', toggleDeliveryFee);
                // Initial toggle based on current state
                toggleDeliveryFee();
            }

            // Initialize Flatpickr for date and datetime fields if needed
            flatpickr("#delivery_datetime", {
                enableTime: true,
                dateFormat: "Y-m-d\\TH:i",
            });

            flatpickr("#delivery_date", {
                dateFormat: "Y-m-d",
            });
        });

        // Function to print the order details
        function printOrder() {
            window.print();
        }

        // Function to copy order details to clipboard
        function copyOrderDetails() {
            // Gather the required information
            const orderId = "<?= htmlspecialchars($order['id']) ?>";
            const status = "<?= htmlspecialchars($order['status']) ?>";
            const priority = "<?= htmlspecialchars($order['priority']) ?>";
            const orderDate = "<?= date("l, d M Y, h:i A", strtotime($order['order_date'])) ?>";
            const deliveryDateTime = "<?= $order['delivery_datetime'] ? date("l, d M Y, h:i A", strtotime($order['delivery_datetime'])) : 'N/A' ?>";
            const customerName = "<?= htmlspecialchars($order['customer_name']) ?>";
            const contactNumber = "<?= htmlspecialchars($order['customer_contact']) ?>";
            const deliveryAddress = "<?= nl2br(htmlspecialchars($order['delivery_address'])) ?>";
            const deliveryFee = <?= htmlspecialchars($order['free_shipping']) == 1 ? '0.00' : htmlspecialchars($order['delivery_fee']) ?>;
            const freeShippingStatus = <?= htmlspecialchars($order['free_shipping']) == 1 ? '"Yes"' : '"No"' ?>;
            const isPaidStatus = <?= htmlspecialchars($order['is_paid']) == 1 ? '"Paid"' : '"Not Paid"' ?>;

            let productSummary = '';
            document.querySelectorAll('.order-item-row').forEach(function(item) {
                const productName = item.querySelector('.product-name').innerText;
                const productSize = item.querySelector('.product-size').innerText.replace('Size: ', '');
                const productQuantityText = item.querySelector('.product-quantity').innerText.replace('Quantity: ', '');
                const productQuantity = parseInt(productQuantityText);
                const packagingPreferences = item.querySelector('.packaging_preferences') ? item.querySelector('.packaging_preferences').innerText.replace('Packaging: ', '') : 'N/A';
                const discountPercentage = item.querySelector('.discount_percentage') ? item.querySelector('.discount_percentage').innerText.replace('%', '') : '0.00';

                if (!isNaN(productQuantity) && productQuantity > 1) {
                    productSummary += `• Item: ${productQuantity} Boxes\n  Size: ${productSize}\n  Product: ${productName}\n  Packaging: ${packagingPreferences}\n  Discount: ${discountPercentage}%\n\n`;
                } else if (!isNaN(productQuantity) && productQuantity === 1) {
                    productSummary += `• Item: ${productQuantity} Box\n  Size: ${productSize}\n  Product: ${productName}\n  Packaging: ${packagingPreferences}\n  Discount: ${discountPercentage}%\n\n`;
                } else {
                    productSummary += `• Product: ${productName}\n  Size: ${productSize}\n  Packaging: ${packagingPreferences}\n  Discount: ${discountPercentage}%\n\n`;
                }
            });

            // Construct the formatted text
            const formattedText = `🔴 Order Details 🔴
• Order ID: ${orderId}
• Status: ${status}
• Priority: ${priority}
• Order Date: ${orderDate}
• Delivery Date & Time: ${deliveryDateTime}
• Customer Name: ${customerName}

📦 Product Summary:
${productSummary}📍 Delivery Address:
${deliveryAddress}

💰 Delivery Fee: KWD ${deliveryFee} (${freeShippingStatus} Shipping)
💵 Payment Status: ${isPaidStatus}
📞 Contact Number: ${contactNumber}`;

            // Copy to clipboard using Clipboard API
            navigator.clipboard.writeText(formattedText).then(() => {
                alert('Order details copied to clipboard!');
            }).catch(err => {
                alert('Failed to copy: ' + err);
            });
        }
    </script>
</head>
<body>

    <!-- Header -->
    <div class="header">
        <h1>Kaak Alia Management System</h1>
    </div>

    <!-- Container -->
    <div class="container-custom">
        <h3 class="mb-5">Edit Order #<?= htmlspecialchars($order_id) ?></h3>
        
        <!-- Display Success or Error Messages -->
        <?php if (!empty($success)): ?>
            <div class="alert alert-custom alert-success-custom"><?= htmlspecialchars($success) ?></div>
        <?php endif; ?>

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

        <!-- Edit Order Form -->
        <form method="POST" action="">
            <!-- Customer Details -->
            <div class="mb-5">
                <h5 class="mb-4">Customer Details</h5>
                <div class="row g-4">
                    <div class="col-md-6">
                        <label class="form-label" for="customer_name">Customer Name <span class="text-danger">*</span></label>
                        <input type="text" id="customer_name" name="customer_name" class="form-control" 
                               value="<?= htmlspecialchars(isset($_POST['customer_name']) ? $_POST['customer_name'] : $order['customer_name']) ?>" 
                               required>
                    </div>
                    <div class="col-md-6">
                        <label class="form-label" for="contact">Contact</label>
                        <input type="text" id="contact" name="contact" class="form-control" 
                               value="<?= htmlspecialchars(isset($_POST['contact']) ? $_POST['contact'] : $order['customer_contact']) ?>">
                    </div>
                </div>
                <div class="mt-4">
                    <label class="form-label" for="delivery_address">Delivery Address</label>
                    <textarea id="delivery_address" name="delivery_address" class="form-control" rows="3"><?= htmlspecialchars(isset($_POST['delivery_address']) ? $_POST['delivery_address'] : $order['delivery_address']) ?></textarea>
                </div>
            </div>
            <hr>
            <!-- Order Details -->
            <div class="mb-5">
                <h5 class="mb-4">Order Details</h5>
                <div class="row g-4">
                    <div class="col-md-3">
                        <label class="form-label" for="status">Status <span class="text-danger">*</span></label>
                        <select name="status" id="status" class="form-control" required>
                            <option value="">Select Status</option>
                            <option value="Pending" <?= (isset($_POST['status']) ? $_POST['status'] : $order['status']) == 'Pending' ? 'selected' : '' ?>>Pending</option>
                            <option value="In Progress" <?= (isset($_POST['status']) ? $_POST['status'] : $order['status']) == 'In Progress' ? 'selected' : '' ?>>In Progress</option>
                            <option value="Completed" <?= (isset($_POST['status']) ? $_POST['status'] : $order['status']) == 'Completed' ? 'selected' : '' ?>>Completed</option>
                            <option value="Delivered" <?= (isset($_POST['status']) ? $_POST['status'] : $order['status']) == 'Delivered' ? 'selected' : '' ?>>Delivered</option>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label" for="priority">Priority <span class="text-danger">*</span></label>
                        <select name="priority" id="priority" class="form-control" required>
                            <option value="">Select Priority</option>
                            <option value="Normal" <?= (isset($_POST['priority']) ? $_POST['priority'] : $order['priority']) == 'Normal' ? 'selected' : '' ?>>Normal</option>
                            <option value="Important" <?= (isset($_POST['priority']) ? $_POST['priority'] : $order['priority']) == 'Important' ? 'selected' : '' ?>>Important</option>
                            <option value="Quick" <?= (isset($_POST['priority']) ? $_POST['priority'] : $order['priority']) == 'Quick' ? 'selected' : '' ?>>Quick</option>
                        </select>
                    </div>
                    <div class="col-md-3">
                        <label class="form-label" for="delivery_datetime">Delivery Date and Time</label>
                        <input type="datetime-local" id="delivery_datetime" name="delivery_datetime" class="form-control" 
                               value="<?= isset($_POST['delivery_datetime']) ? htmlspecialchars($_POST['delivery_datetime']) : ($order['delivery_datetime'] ? date('Y-m-d\TH:i', strtotime($order['delivery_datetime'])) : '') ?>">
                    </div>
                    <div class="col-md-3">
                        <label class="form-label" for="delivery_date">Delivery Date</label>
                        <input type="date" id="delivery_date" name="delivery_date" class="form-control" 
                               value="<?= isset($_POST['delivery_date']) ? htmlspecialchars($_POST['delivery_date']) : ($order['delivery_date'] ? date('Y-m-d', strtotime($order['delivery_date'])) : '') ?>">
                    </div>
                </div>
                <div class="row g-4 mt-3">
                    <div class="col-md-4">
                        <label class="form-label" for="delivery_fee">Delivery Fee (KD) <span class="text-danger" id="delivery_fee_required">*</span></label>
                        <input type="number" step="0.01" id="delivery_fee" name="delivery_fee" class="form-control" min="0" 
                               value="<?= isset($_POST['delivery_fee']) ? htmlspecialchars($_POST['delivery_fee']) : number_format($order['delivery_fee'], 2, '.', '') ?>" 
                               <?= isset($_POST['free_shipping']) || $order['free_shipping'] ? 'disabled' : 'required' ?>>
                    </div>
                    <div class="col-md-4 d-flex align-items-end">
                        <div class="form-check">
                            <input class="form-check-input" type="checkbox" value="1" id="free_shipping" name="free_shipping" <?= (isset($_POST['free_shipping']) ? $_POST['free_shipping'] : $order['free_shipping']) ? 'checked' : '' ?>>
                            <label class="form-check-label" for="free_shipping">
                                Free Shipping
                            </label>
                        </div>
                    </div>
                    <div class="col-md-4 d-flex align-items-end">
                        <div class="form-check form-switch">
                            <input class="form-check-input" type="checkbox" id="is_paid" name="is_paid" <?= (isset($_POST['is_paid']) ? $_POST['is_paid'] : $order['is_paid']) ? 'checked' : '' ?>>
                            <label class="form-check-label" for="is_paid">
                                <?= (isset($_POST['is_paid']) ? $_POST['is_paid'] : $order['is_paid']) ? 'Mark as Paid' : 'Mark as Not Paid' ?>
                            </label>
                        </div>
                    </div>
                </div>
            </div>
            <hr>
            <!-- Existing Order Items -->
            <div class="mb-5">
                <h5 class="mb-4">Existing Order Items</h5>
                <div id="existing-order-items-container">
                    <?php foreach ($order_items as $item): ?>
                        <div class="order-item-row border p-4 mb-4 shadow-sm" style="border-radius: 12px;">
                            <input type="hidden" name="existing_item_id[]" value="<?= htmlspecialchars($item['id']) ?>">
                            <div class="row g-3">
                                <div class="col-md-4">
                                    <label class="form-label" for="product_id_<?= htmlspecialchars($item['id']) ?>">Product <span class="text-danger">*</span></label>
                                    <select name="product_id[]" id="product_id_<?= htmlspecialchars($item['id']) ?>" class="form-control" required>
                                        <option value="">Select Product</option>
                                        <?php
                                        // Reset the products_result pointer
                                        $products_result->data_seek(0);
                                        while($product = $products_result->fetch_assoc()): ?>
                                            <option value="<?= htmlspecialchars($product['id']) ?>" <?= ($product['id'] == $item['product_id']) ? 'selected' : '' ?>>
                                                <?= htmlspecialchars($product['name'] . " - " . $product['size'] . " (" . number_format($product['price'], 2) . " KD)") ?>
                                            </option>
                                        <?php endwhile; ?>
                                    </select>
                                </div>
                                <div class="col-md-2">
                                    <label class="form-label" for="quantity_<?= htmlspecialchars($item['id']) ?>">Quantity <span class="text-danger">*</span></label>
                                    <input type="number" name="quantity[]" id="quantity_<?= htmlspecialchars($item['id']) ?>" class="form-control" min="1" value="<?= htmlspecialchars($item['quantity']) ?>" required>
                                </div>
                                <div class="col-md-3">
                                    <label class="form-label" for="packaging_preferences_<?= htmlspecialchars($item['id']) ?>">Packaging Preferences</label>
                                    <input type="text" name="packaging_preferences[]" id="packaging_preferences_<?= htmlspecialchars($item['id']) ?>" class="form-control" placeholder="e.g., Gift Wrap" value="<?= htmlspecialchars($item['packaging_preferences']) ?>">
                                </div>
                                <div class="col-md-2">
                                    <label class="form-label" for="discount_percentage_<?= htmlspecialchars($item['id']) ?>">Discount (%)</label>
                                    <input type="number" name="discount_percentage[]" id="discount_percentage_<?= htmlspecialchars($item['id']) ?>" class="form-control" min="0" max="100" step="0.01" value="<?= htmlspecialchars($item['discount_percentage']) ?>">
                                </div>
                                <div class="col-md-1 d-flex align-items-end">
                                    <button type="button" class="remove-product-button" onclick="removeExistingOrderItemRow(this)" title="Remove Product">✕</button>
                                </div>
                            </div>
                            <div class="row mt-3">
                                <div class="col-md-12">
                                    <label class="form-label" for="discount_reason_<?= htmlspecialchars($item['id']) ?>">Discount Reason</label>
                                    <input type="text" name="discount_reason[]" id="discount_reason_<?= htmlspecialchars($item['id']) ?>" class="form-control" placeholder="e.g., Seasonal Sale" value="<?= htmlspecialchars($item['discount_reason']) ?>">
                                </div>
                            </div>
                        </div>
                    <?php endforeach; ?>
                </div>
            </div>
            <hr>
            <!-- Add New Order Items -->
            <div class="mb-5">
                <h5 class="mb-4">Add New Order Items</h5>
                <div id="order-items-container">
                    <!-- New Order Item Rows will be added here -->
                </div>
                <button type="button" class="btn btn-secondary-custom mt-3" onclick="addOrderItemRow()">
                    <i class="bi bi-plus-circle-fill"></i> Add Another Product
                </button>
            </div>
            <!-- Submit Buttons -->
            <div class="d-flex justify-content-between mt-4">
                <button type="submit" class="btn-primary-custom">Update Order</button>
                <a href="view_orders.php" class="btn-secondary-custom">Cancel</a>
            </div>
        </form>
    </div>

    <!-- Footer -->
    <div class="footer">
        &copy; <?= date("Y") ?> Kaak Alia Bakery. All rights reserved.
    </div>

    <!-- Bootstrap JS Bundle -->
    <script src="/kaak_alia_management_system/assets/bootstrap/js/bootstrap.bundle.min.js"></script>
    
    <!-- Flatpickr JS for Date Picker -->
    <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>

    <!-- Bootstrap Tooltip Initialization (if using tooltips in future) -->
    <script>
        var tooltipTriggerList = [].slice.call(document.querySelectorAll('[data-bs-toggle="tooltip"]'))
        var tooltipList = tooltipTriggerList.map(function (tooltipTriggerEl) {
            return new bootstrap.Tooltip(tooltipTriggerEl)
        })
    </script>

    <!-- Custom JS for Print, Copy, and Delivery Fee Toggling -->
    <script>
        function printOrder() {
            window.print();
        }

        function copyOrderDetails() {
            // Gather the required information
            const orderId = "<?= htmlspecialchars($order['id']) ?>";
            const status = "<?= htmlspecialchars($order['status']) ?>";
            const priority = "<?= htmlspecialchars($order['priority']) ?>";
            const orderDate = "<?= date("l, d M Y, h:i A", strtotime($order['order_date'])) ?>";
            const deliveryDateTime = "<?= $order['delivery_datetime'] ? date("l, d M Y, h:i A", strtotime($order['delivery_datetime'])) : 'N/A' ?>";
            const customerName = "<?= htmlspecialchars($order['customer_name']) ?>";
            const contactNumber = "<?= htmlspecialchars($order['customer_contact']) ?>";
            const deliveryAddress = "<?= nl2br(htmlspecialchars($order['delivery_address'])) ?>";
            const deliveryFee = <?= htmlspecialchars($order['free_shipping']) == 1 ? '0.00' : htmlspecialchars($order['delivery_fee']) ?>;
            const freeShippingStatus = <?= htmlspecialchars($order['free_shipping']) == 1 ? '"Yes"' : '"No"' ?>;
            const isPaidStatus = <?= htmlspecialchars($order['is_paid']) == 1 ? '"Paid"' : '"Not Paid"' ?>;

            let productSummary = '';
            document.querySelectorAll('.order-item-row').forEach(function(item) {
                const productName = item.querySelector('.product-name').innerText;
                const productSize = item.querySelector('.product-size').innerText.replace('Size: ', '');
                const productQuantityText = item.querySelector('.product-quantity').innerText.replace('Quantity: ', '');
                const productQuantity = parseInt(productQuantityText);
                const packagingPreferences = item.querySelector('.packaging_preferences') ? item.querySelector('.packaging_preferences').innerText.replace('Packaging: ', '') : 'N/A';
                const discountPercentage = item.querySelector('.discount_percentage') ? item.querySelector('.discount_percentage').innerText.replace('%', '') : '0.00';

                if (!isNaN(productQuantity) && productQuantity > 1) {
                    productSummary += `• Item: ${productQuantity} Boxes\n  Size: ${productSize}\n  Product: ${productName}\n  Packaging: ${packagingPreferences}\n  Discount: ${discountPercentage}%\n\n`;
                } else if (!isNaN(productQuantity) && productQuantity === 1) {
                    productSummary += `• Item: ${productQuantity} Box\n  Size: ${productSize}\n  Product: ${productName}\n  Packaging: ${packagingPreferences}\n  Discount: ${discountPercentage}%\n\n`;
                } else {
                    productSummary += `• Product: ${productName}\n  Size: ${productSize}\n  Packaging: ${packagingPreferences}\n  Discount: ${discountPercentage}%\n\n`;
                }
            });

            // Construct the formatted text
            const formattedText = `🔴 Order Details 🔴
• Order ID: ${orderId}
• Status: ${status}
• Priority: ${priority}
• Order Date: ${orderDate}
• Delivery Date & Time: ${deliveryDateTime}
• Customer Name: ${customerName}

📦 Product Summary:
${productSummary}📍 Delivery Address:
${deliveryAddress}

💰 Delivery Fee: KWD ${deliveryFee} (${freeShippingStatus} Shipping)
💵 Payment Status: ${isPaidStatus}
📞 Contact Number: ${contactNumber}`;

            // Copy to clipboard using Clipboard API
            navigator.clipboard.writeText(formattedText).then(() => {
                alert('Order details copied to clipboard!');
            }).catch(err => {
                alert('Failed to copy: ' + err);
            });
        }
    </script>
</body>
</html>
<?php
// Flush the output buffer
ob_end_flush();
// No closing PHP tag to prevent unwanted output
?>) make it cool and Page Styling and Design Description

1. Overall Aesthetic and Theme

Modern and Clean Design: The page is designed with a minimalistic aesthetic focused on simplicity and clarity, allowing users to engage with form elements without distractions. This promotes easy interaction and efficient data entry.
Color Palette:
Primary Colors: A soothing teal hue (#20B2AA) highlights key interactive elements like buttons and icons, establishing a sense of trust and professionalism.
Neutral Backgrounds: Soft gray tones (e.g., #f1f5f9 for scrollbar tracks) provide a balanced backdrop that enhances readability without overpowering content.
Accent Colors: Red tones draw attention to error messages, while green hues are used for success indicators, offering clear, visual feedback for various form states.
2. Layout and Structure

Container-Based Layout: The content is housed in a responsive container (container mx-auto p-6), maintaining alignment and spacing across screen sizes. Generous padding (p-6) prevents clutter, creating a comfortable visual structure.
Sectioning and Hierarchy: The form is divided into sections (e.g., Customer Details, Order Details), each within distinct, bordered blocks (bg-white rounded-lg shadow-lg p-6 mb-8), helping users navigate and logically organize information.
Responsive Grid System: Using Tailwind CSS's grid utilities, form fields adapt to different screen sizes, appearing side-by-side on large screens (md:flex-row md:space-x-6) and stacking vertically on smaller devices for better accessibility.
3. Typography and Text Styling

Font Choices: A clean, sans-serif font is used for readability. Tailwind CSS's default font settings ensure consistency.
Text Hierarchy:
Headings: Prominent headings (text-3xl font-bold text-teal-600) guide users through sections.
Labels and Instructions: Labels use medium font weights (font-medium text-gray-700) for clarity, with additional descriptions styled smaller and gray for non-disruptive guidance.
4. Form Elements and Inputs

Input Fields:
Consistent Styling: Uniform styling across inputs (rounded-md border-gray-300 with focus states like focus:ring-teal-500 focus:border-teal-500) creates a cohesive user experience.
Padding and Sizing: Sufficient padding (p-2) enhances readability and interaction.
Buttons:
Primary Actions: Bold-colored buttons (bg-teal-500 rounded-lg) with hover effects (hover:bg-teal-600) provide visual feedback.
Icon Integration: Using icons (via Bootstrap Icons) next to button labels aids in clarity, making functions intuitive.
Checkboxes and Radio Buttons:
Customized Appearance: Consistent sizing and color accents (text-teal-600) make these elements noticeable without overwhelming the design.
5. Interactive Elements and Enhancements

Dynamic Form Rows: Animated transitions for adding/removing product rows (shadow-md) keep changes smooth and visually coherent.
Tooltips: Small icons (bi-question-circle) with tooltips provide guidance on hover, helping users without adding clutter. Tooltips have dark backgrounds and smooth animations.
6. Notifications and Alerts

Feedback Messages:
Error Messages: Styled with red tones (bg-red-100, border-red-400, text-red-700), they are prominent and actionable.
Success Messages: Green styling (bg-green-100, border-green-400, text-green-700) reinforces positive actions.
Iconography: Icons (bi-exclamation-triangle-fill for errors, bi-check-circle-fill for successes) enhance message clarity.
7. Scrollbar Customization

Aesthetic Scrollbars: Styled with narrow widths (8px) and rounded edges, ensuring they are functional but not intrusive. Colors align with the main scheme, and the thumb color changes on hover for feedback.
8. Buttons and Interactive Controls

Add and Remove Buttons: Both buttons are consistently designed with background colors, text colors, padding, and hover effects, making the interface predictable.
Icon Usage: Icons (e.g., bi-plus-circle-fill for adding, bi-trash-fill for removing) clarify actions.
9. Date and Time Picker Integration

Enhanced User Input: Integrate the Flatpickr library for date and time selection (#delivery_datetime), offering users an intuitive interface that matches other form styles.
Keyboard Accessibility: Designed to be keyboard-friendly, accommodating all users.
10. Footer Design

Subtle Footer: The footer uses soft gray tones (text-gray-600), ensuring it provides information without detracting from the main content.
11. Responsive Design and Mobile Optimization

Adaptive Layouts: Tailwind CSS responsive utilities ensure layout flexibility across devices.
Touch-Friendly Elements: Adequate sizing and spacing facilitate easy touchscreen interaction.
12. Accessibility Features

Semantic HTML: Elements like <label>, <input>, and <button> are used correctly to support assistive technologies.
Focus States: Interactive elements have clear focus states (focus:ring-teal-500 focus:border-teal-500) for keyboard navigation.
13. Consistent Spacing and Sizing

Whitespace Utilization: Generous margins (mb-6, mt-4) and padding (p-4, p-6) create visual balance.
Element Sizing: Uniform element sizing promotes a well-organized, professional appearance.
14. Visual Hierarchy and Emphasis

Clear Hierarchy: Larger, bolder fonts in headings guide users through sections.
Emphasized Labels: Important fields are marked with an asterisk (<span class="text-red-500">*</span>), drawing attention without overwhelming.
15. Interactive Feedback and Animations

Hover and Focus Effects: Color changes on hover (hover:bg-teal-600, hover:bg-red-600) provide feedback.
Smooth Transitions: Transition effects (transition duration-300) enhance user experience.
16. Iconography and Visual Cues

Bootstrap Icons Integration: Icons are sized and colored consistently (text-gray-400 text-sm for tooltips), ensuring they enhance without disrupting design.
17. Enhanced User Guidance

Tooltip Descriptions: Tooltips provide context-sensitive help, available only when needed to maintain a clean interface.
and this info 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 */;)