<?php

declare(strict_types=1);

require_once __DIR__ . '/../products/stock_helpers.php';

if (!function_exists('e')) {
    function e(?string $value): string
    {
        return htmlspecialchars((string) $value, ENT_QUOTES, 'UTF-8');
    }
}

if (!function_exists('base_admin_url')) {
    function base_admin_url(string $path = ''): string
    {
        return '/app/admin/' . ltrim($path, '/');
    }
}

if (!function_exists('redirect_to')) {
    function redirect_to(string $path): void
    {
        header('Location: ' . $path);
        exit;
    }
}

function order_status_options(): array
{
    return [
        'pending' => 'قيد الانتظار',
        'ready' => 'جاهز',
        'delivered' => 'تم التسليم',
        'cancelled' => 'ملغي',
        'postponed' => 'مؤجل',
    ];
}

function order_legacy_status_map(): array
{
    return [
        'pending' => 'pending',
        'confirmed' => 'pending',
        'processing' => 'pending',
        'out_for_delivery' => 'ready',
        'shipped' => 'ready',
        'ready' => 'ready',
        'completed' => 'delivered',
        'delivered' => 'delivered',
        'returned' => 'cancelled',
        'cancelled' => 'cancelled',
        'postponed' => 'postponed',
    ];
}

function order_normalize_status(?string $status): string
{
    $status = trim((string)$status);
    $map = order_legacy_status_map();
    if ($status === '') {
        return 'pending';
    }
    return $map[$status] ?? (array_key_exists($status, order_status_options()) ? $status : 'pending');
}

function order_status_label(?string $status): string
{
    $status = order_normalize_status($status);
    return order_status_options()[$status] ?? $status;
}

function order_status_badge_variant(?string $status): string
{
    return match (order_normalize_status($status)) {
        'delivered' => 'success',
        'cancelled' => 'warn',
        'postponed' => 'warn',
        default => 'info',
    };
}

function order_display_serial(array|int|string $orderOrId): string
{
    $id = 0;
    if (is_array($orderOrId)) {
        $id = (int)($orderOrId['id'] ?? 0);
    } else {
        $id = (int)$orderOrId;
    }
    $id = max(1, $id);
    return 'N-B ' . str_pad((string)$id, 3, '0', STR_PAD_LEFT);
}

function order_status_sql_clause(string $column): string
{
    return "CASE
" .
        " WHEN {$column} IN ('cancelled','returned') THEN 'cancelled'
" .
        " WHEN {$column} IN ('delivered','completed') THEN 'delivered'
" .
        " WHEN {$column} IN ('ready','out_for_delivery','shipped') THEN 'ready'
" .
        " WHEN {$column} = 'postponed' THEN 'postponed'
" .
        " ELSE 'pending' END";
}

function order_setting_value(mysqli $conn, string $key, string $default = ''): string
{
    static $cache = null;
    if (!is_array($cache)) {
        $cache = [];
        if ($res = $conn->query("SELECT setting_key, setting_value FROM settings")) {
            while ($row = $res->fetch_assoc()) {
                $cache[(string)$row['setting_key']] = (string)($row['setting_value'] ?? '');
            }
            $res->close();
        }
    }
    return array_key_exists($key, $cache) ? (string)$cache[$key] : $default;
}

function order_invoice_field_definitions(): array
{
    return [
        'show_store_name' => 'اسم المتجر',
        'show_order_number' => 'رقم الطلب',
        'show_created_at' => 'تاريخ الطلب',
        'show_customer_name' => 'اسم الزبون',
        'show_customer_phone' => 'هاتف الزبون',
        'show_address' => 'العنوان',
        'show_order_type' => 'نوع الطلب',
        'show_order_status' => 'حالة الطلب',
        'show_payment_method' => 'طريقة الدفع',
        'show_payment_status' => 'حالة الدفع',
        'show_product_image' => 'صورة المنتج داخل العناصر',
        'show_items_sku' => 'SKU داخل العناصر',
        'show_notes' => 'ملاحظات الزبون',
        'show_admin_notes' => 'ملاحظات الإدارة',
        'show_totals' => 'الملخص المالي',
    ];
}

function order_invoice_settings(mysqli $conn, string $mode): array
{
    $mode = $mode === 'mobile' ? 'mobile' : 'print';
    $defaults = [
        'show_store_name' => '1',
        'show_order_number' => '1',
        'show_created_at' => '1',
        'show_customer_name' => '1',
        'show_customer_phone' => '1',
        'show_address' => '1',
        'show_order_type' => '1',
        'show_order_status' => '1',
        'show_payment_method' => '1',
        'show_payment_status' => '1',
        'show_product_image' => '1',
        'show_items_sku' => $mode === 'print' ? '1' : '0',
        'show_notes' => '1',
        'show_admin_notes' => $mode === 'print' ? '1' : '0',
        'show_totals' => '1',
        'footer_text' => '',
    ];
    $settings = []
    ;
    foreach ($defaults as $key => $default) {
        $settings[$key] = order_setting_value($conn, 'invoice_' . $mode . '_' . $key, $default);
    }
    return $settings;
}

function order_invoice_enabled(array $settings, string $key): bool
{
    return (($settings[$key] ?? '0') === '1');
}



function order_invoice_local_path_exists(string $webPath): bool
{
    $webPath = trim($webPath);
    if ($webPath === '' || preg_match('~^https?://~i', $webPath)) {
        return false;
    }

    $docRoot = rtrim((string)($_SERVER['DOCUMENT_ROOT'] ?? ''), '/');
    if ($docRoot === '') {
        return false;
    }

    $full = $docRoot . '/' . ltrim($webPath, '/');
    return is_file($full);
}

function order_invoice_pick_existing_image(array $candidates): string
{
    foreach ($candidates as $candidate) {
        $candidate = trim((string)$candidate);
        if ($candidate === '') {
            continue;
        }
        if (preg_match('~^https?://~i', $candidate)) {
            return $candidate;
        }
        if (order_invoice_local_path_exists($candidate)) {
            return $candidate;
        }
    }

    foreach ($candidates as $candidate) {
        $candidate = trim((string)$candidate);
        if ($candidate !== '') {
            return $candidate;
        }
    }

    return '';
}

function order_invoice_image_src(?string $image): string
{
    $image = trim((string)$image);
    if ($image === '') {
        return '';
    }
    if (preg_match('~^https?://~i', $image)) {
        return $image;
    }

    $normalized = str_replace('\\', '/', ltrim($image, '/'));
    $normalized = preg_replace('~^(?:\.\./)+~', '', $normalized);

    $candidates = [];
    if (preg_match('~^app/admin/uploads/~i', $normalized)) {
        $candidates[] = '/' . $normalized;
    } elseif (preg_match('~^uploads/~i', $normalized)) {
        $candidates[] = '/app/admin/' . $normalized;
        $candidates[] = '/' . $normalized;
    } elseif (preg_match('~^products/~i', $normalized)) {
        $candidates[] = '/app/admin/uploads/' . $normalized;
        $candidates[] = '/uploads/' . $normalized;
    } else {
        $trimmed = ltrim($normalized, '/');
        $candidates[] = '/app/admin/uploads/products/' . $trimmed;
        $candidates[] = '/app/admin/uploads/' . $trimmed;
        $candidates[] = '/uploads/products/' . $trimmed;
        $candidates[] = '/uploads/' . $trimmed;
    }

    return order_invoice_pick_existing_image(array_unique($candidates));
}


function order_fill_items_missing_images(mysqli $conn, array $items): array
{
    $productCache = [];
    $variantCache = [];

    foreach ($items as &$item) {
        $rawImage = trim((string)($item['product_image'] ?? ''));
        $resolved = order_invoice_image_src($rawImage);
        if ($resolved !== '' && preg_match('~^https?://~i', $resolved)) {
            $item['product_image'] = $resolved;
            continue;
        }
        if ($resolved !== '' && order_invoice_local_path_exists($resolved)) {
            $item['product_image'] = $resolved;
            continue;
        }

        $fallback = '';
        $variantId = (int)($item['variant_id'] ?? 0);
        $productId = (int)($item['product_id'] ?? 0);

        if ($variantId > 0) {
            if (!array_key_exists($variantId, $variantCache)) {
                $variantCache[$variantId] = '';
                $stmt = $conn->prepare("SELECT variant_image FROM product_variants WHERE id = ? LIMIT 1");
                if ($stmt) {
                    $stmt->bind_param('i', $variantId);
                    $stmt->execute();
                    $row = $stmt->get_result()->fetch_assoc() ?: null;
                    $stmt->close();
                    $variantCache[$variantId] = trim((string)($row['variant_image'] ?? ''));
                }
            }
            $fallback = (string)$variantCache[$variantId];
        }

        if ($fallback === '' && $productId > 0) {
            if (!array_key_exists($productId, $productCache)) {
                $productCache[$productId] = '';
                $stmt = $conn->prepare("SELECT main_image FROM products WHERE id = ? LIMIT 1");
                if ($stmt) {
                    $stmt->bind_param('i', $productId);
                    $stmt->execute();
                    $row = $stmt->get_result()->fetch_assoc() ?: null;
                    $stmt->close();
                    $productCache[$productId] = trim((string)($row['main_image'] ?? ''));
                }
            }
            $fallback = (string)$productCache[$productId];
        }

        $fallbackSrc = order_invoice_image_src($fallback);
        if ($fallbackSrc !== '') {
            $item['product_image'] = $fallbackSrc;
        } else {
            $item['product_image'] = $resolved;
        }
    }
    unset($item);

    return $items;
}

function order_type_options(): array
{
    return [
        'delivery' => 'توصيل',
        'pickup' => 'استلام',
        'preorder' => 'حجز مسبق',
    ];
}

function payment_status_options(): array
{
    return [
        'unpaid' => 'غير مدفوع',
        'pending_confirmation' => 'بانتظار التأكيد',
        'paid' => 'مدفوع',
        'failed' => 'فشل الدفع',
        'refunded' => 'مسترجع',
    ];
}

function payment_methods_active(mysqli $conn): array
{
    $rows = [];
    $sql = "SELECT id, name, code, description, instructions, requires_manual_confirmation
            FROM payment_methods
            WHERE status = 1
            ORDER BY sort_order ASC, id ASC";

    if ($res = $conn->query($sql)) {
        while ($row = $res->fetch_assoc()) {
            $rows[] = $row;
        }
        $res->close();
    }

    return $rows;
}

function order_payment_methods_for_filter(mysqli $conn): array
{
    $rows = [];
    if (!order_table_exists($conn, 'payment_methods')) {
        return $rows;
    }
    $statusWhere = order_table_has_column($conn, 'payment_methods', 'status') ? 'WHERE status = 1' : '';
    $orderBy = order_table_has_column($conn, 'payment_methods', 'sort_order') ? 'sort_order ASC, id ASC' : 'id ASC';
    if ($res = $conn->query("SELECT id, name FROM payment_methods {$statusWhere} ORDER BY {$orderBy}")) {
        while ($row = $res->fetch_assoc()) {
            $rows[] = $row;
        }
        $res->close();
    }
    return $rows;
}

function order_provinces_for_filter(mysqli $conn): array
{
    $items = [];
    if (order_table_has_column($conn, 'orders', 'province')) {
        if ($res = $conn->query("SELECT DISTINCT province AS name FROM orders WHERE COALESCE(province,'') <> '' ORDER BY province ASC LIMIT 100")) {
            while ($row = $res->fetch_assoc()) {
                $name = trim((string)($row['name'] ?? ''));
                if ($name !== '') {
                    $items[$name] = $name;
                }
            }
            $res->close();
        }
    }
    if (order_table_exists($conn, 'provinces')) {
        $nameColumn = order_table_has_column($conn, 'provinces', 'name') ? 'name' : (order_table_has_column($conn, 'provinces', 'title') ? 'title' : '');
        if ($nameColumn !== '' && ($res = $conn->query("SELECT {$nameColumn} AS name FROM provinces ORDER BY {$nameColumn} ASC LIMIT 100"))) {
            while ($row = $res->fetch_assoc()) {
                $name = trim((string)($row['name'] ?? ''));
                if ($name !== '') {
                    $items[$name] = $name;
                }
            }
            $res->close();
        }
    }
    return array_values($items);
}

function order_pickup_branches_for_filter(mysqli $conn): array
{
    $items = [];
    if (order_table_has_column($conn, 'orders', 'pickup_branch')) {
        if ($res = $conn->query("SELECT DISTINCT pickup_branch AS name FROM orders WHERE COALESCE(pickup_branch,'') <> '' ORDER BY pickup_branch ASC LIMIT 100")) {
            while ($row = $res->fetch_assoc()) {
                $name = trim((string)($row['name'] ?? ''));
                if ($name !== '') {
                    $items[$name] = $name;
                }
            }
            $res->close();
        }
    }
    if (order_table_exists($conn, 'branches')) {
        $nameColumn = order_table_has_column($conn, 'branches', 'name') ? 'name' : (order_table_has_column($conn, 'branches', 'title') ? 'title' : '');
        if ($nameColumn !== '' && ($res = $conn->query("SELECT {$nameColumn} AS name FROM branches ORDER BY {$nameColumn} ASC LIMIT 100"))) {
            while ($row = $res->fetch_assoc()) {
                $name = trim((string)($row['name'] ?? ''));
                if ($name !== '') {
                    $items[$name] = $name;
                }
            }
            $res->close();
        }
    }
    return array_values($items);
}

function order_quick_filter_options(): array
{
    return [
        'needs_payment' => 'بحاجة لتأكيد الدفع',
        'needs_processing' => 'بحاجة للتجهيز',
        'postponed' => 'مؤجلة',
        'ready' => 'جاهزة للتسليم/الاستلام',
        'preorder' => 'حجوزات مسبقة',
        'today' => 'طلبات اليوم',
    ];
}

function order_csv_cell(mixed $value): string
{
    $text = str_replace(["
", "
", "	"], ' ', (string)$value);
    return trim($text);
}


function order_payment_method_is_transfer_like(?string $code, ?string $name = null): bool
{
    $haystack = mb_strtolower(trim((string)$code . ' ' . (string)$name), 'UTF-8');
    if ($haystack === '') {
        return false;
    }

    foreach (['transfer', 'bank', 'manual', 'wire', 'تحويل', 'حوال', 'ماستر', 'master', 'بطاق'] as $needle) {
        if (mb_strpos($haystack, $needle, 0, 'UTF-8') !== false) {
            return true;
        }
    }

    return false;
}

function order_payment_requires_confirmation(array $order): bool
{
    if ((int)($order['requires_manual_confirmation'] ?? 0) === 1) {
        return true;
    }

    return order_payment_method_is_transfer_like(
        (string)($order['payment_method_code'] ?? ''),
        (string)($order['payment_method_name'] ?? '')
    );
}

function order_can_confirm_payment(array $order): bool
{
    return order_payment_requires_confirmation($order)
        && in_array((string)($order['payment_status'] ?? ''), ['pending_confirmation', 'unpaid'], true)
        && !order_status_is_cancel_like((string)($order['order_status'] ?? ''));
}

function order_round_iqd(float $amount): int
{
    return (int) (round($amount / 1000) * 1000);
}

function order_generate_number(mysqli $conn): string
{
    do {
        $number = 'NAY-' . date('Ymd') . '-' . strtoupper(substr(bin2hex(random_bytes(4)), 0, 6));
        $stmt = $conn->prepare('SELECT id FROM orders WHERE order_number = ? LIMIT 1');
        $stmt->bind_param('s', $number);
        $stmt->execute();
        $exists = $stmt->get_result()->fetch_assoc();
        $stmt->close();
    } while ($exists);

    return $number;
}

function order_find_customer_addresses(mysqli $conn, int $customerId): array
{
    $rows = [];
    $stmt = $conn->prepare("SELECT id, title, province, city, area, street, building_no, floor_no, apartment_no, landmark, full_address, delivery_fee, is_default
                            FROM customer_addresses
                            WHERE customer_id = ? AND status = 1
                            ORDER BY is_default DESC, id DESC");
    $stmt->bind_param('i', $customerId);
    $stmt->execute();
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function order_compose_full_address(array $address): string
{
    $parts = [];
    foreach (['province', 'city', 'area', 'landmark'] as $key) {
        $value = trim((string)($address[$key] ?? ''));
        if ($value !== '') {
            $parts[] = $value;
        }
    }

    if (!$parts) {
        foreach (['street', 'building_no', 'floor_no', 'apartment_no'] as $key) {
            $value = trim((string)($address[$key] ?? ''));
            if ($value !== '') {
                $parts[] = $value;
            }
        }
    }

    if (!$parts) {
        $fallback = trim((string)($address['full_address'] ?? ''));
        return $fallback;
    }

    return implode(' - ', $parts);
}

function order_normalize_customer_payload(mysqli $conn, array $payload, array &$errors): array
{
    $customerId = (int)($payload['customer_id'] ?? 0);
    if ($customerId <= 0) {
        $errors[] = 'اختيار الزبون مطلوب.';
        return $payload;
    }

    $stmt = $conn->prepare("SELECT id, full_name, phone, email FROM customers WHERE id = ? AND status = 1 LIMIT 1");
    $stmt->bind_param('i', $customerId);
    $stmt->execute();
    $customer = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if (!$customer) {
        $errors[] = 'الزبون المختار غير موجود أو غير فعال.';
        return $payload;
    }

    $payload['customer_name'] = $customer['full_name'];
    $payload['customer_phone'] = $customer['phone'];
    $payload['customer_email'] = $customer['email'] ?? '';

    $orderType = trim((string)($payload['order_type'] ?? 'delivery'));
    if ($orderType !== 'delivery') {
        return $payload;
    }

    $addresses = order_find_customer_addresses($conn, $customerId);
    if (!$addresses) {
        $errors[] = 'هذا الزبون ما عنده عنوان توصيل محفوظ.';
        return $payload;
    }

    $selectedAddressId = (int)($payload['customer_address_id'] ?? 0);
    $selected = null;
    foreach ($addresses as $address) {
        if ($selectedAddressId > 0 && (int)$address['id'] === $selectedAddressId) {
            $selected = $address;
            break;
        }
    }
    if (!$selected) {
        $selected = $addresses[0];
    }

    $payload['customer_address_id'] = (int)$selected['id'];
    $payload['province'] = trim((string)($selected['province'] ?? ''));
    $payload['city'] = trim((string)($selected['city'] ?? ''));
    $payload['area'] = trim((string)($selected['area'] ?? ''));
    $payload['full_address'] = order_compose_full_address($selected);
    $payload['delivery_fee'] = (float)($selected['delivery_fee'] ?? 0);

    if ($payload['full_address'] === '') {
        $errors[] = 'عنوان التوصيل المختار غير مكتمل.';
    }

    return $payload;
}

function order_table_has_column(mysqli $conn, string $table, string $column): bool
{
    static $cache = [];
    $key = $table . '.' . $column;
    if (isset($cache[$key])) {
        return $cache[$key];
    }
    $table = $conn->real_escape_string($table);
    $column = $conn->real_escape_string($column);
    $res = $conn->query("SHOW COLUMNS FROM `{$table}` LIKE '{$column}'");
    $cache[$key] = $res && $res->num_rows > 0;
    return $cache[$key];
}

function order_fetch_offer_context(mysqli $conn): array
{
    $ctx = [
        'individual' => [],
        'quantity' => [],
        'company' => [],
        'bundles' => [],
    ];

    if ($res = $conn->query("SELECT * FROM individual_offers WHERE status = 1 AND (start_date IS NULL OR start_date <= NOW()) AND (end_date IS NULL OR end_date >= NOW())")) {
        while ($row = $res->fetch_assoc()) {
            $pid = (int)$row['product_id'];
            if (!isset($ctx['individual'][$pid]) || (float)$row['new_price'] < (float)$ctx['individual'][$pid]['new_price']) {
                $ctx['individual'][$pid] = $row;
            }
        }
        $res->close();
    }

    if ($res = $conn->query("SELECT * FROM quantity_offers WHERE status = 1 AND (start_date IS NULL OR start_date <= NOW()) AND (end_date IS NULL OR end_date >= NOW()) ORDER BY required_qty DESC")) {
        while ($row = $res->fetch_assoc()) {
            $ctx['quantity'][(int)$row['product_id']][] = $row;
        }
        $res->close();
    }

    if ($res = $conn->query("SELECT co.*, b.name AS brand_name FROM company_offers co LEFT JOIN brands b ON b.id = co.company_id WHERE co.status = 1 AND (co.start_date IS NULL OR co.start_date <= NOW()) AND (co.end_date IS NULL OR co.end_date >= NOW())")) {
        while ($row = $res->fetch_assoc()) {
            $ctx['company'][] = $row;
        }
        $res->close();
    }

    if ($res = $conn->query("SELECT * FROM bundle_offers WHERE status = 1 AND (start_date IS NULL OR start_date <= NOW()) AND (end_date IS NULL OR end_date >= NOW()) ORDER BY id DESC")) {
        while ($row = $res->fetch_assoc()) {
            $row['items'] = [];
            $stmt = $conn->prepare("SELECT * FROM bundle_offer_items WHERE bundle_offer_id = ? ORDER BY id ASC");
            $stmt->bind_param('i', $row['id']);
            $stmt->execute();
            $itemsRes = $stmt->get_result();
            while ($it = $itemsRes->fetch_assoc()) {
                $row['items'][] = $it;
            }
            $stmt->close();
            if ($row['items']) {
                $ctx['bundles'][] = $row;
            }
        }
        $res->close();
    }

    return $ctx;
}

function order_company_offer_for_brand(array $companyOffers, string $brandPart): ?array
{
    $brandPart = trim(mb_strtolower($brandPart, 'UTF-8'));
    if ($brandPart === '') {
        return null;
    }
    $best = null;
    foreach ($companyOffers as $offer) {
        $brandName = trim(mb_strtolower((string)($offer['brand_name'] ?? ''), 'UTF-8'));
        if ($brandName === '' || $brandName !== $brandPart) {
            continue;
        }
        if (!$best) {
            $best = $offer;
            continue;
        }
        $bestVal = (float)($best['discount_value'] ?? 0);
        $curVal = (float)($offer['discount_value'] ?? 0);
        if ($curVal > $bestVal) {
            $best = $offer;
        }
    }
    return $best;
}

function order_apply_offers_to_items(mysqli $conn, array $items): array
{
    $ctx = order_fetch_offer_context($conn);
    foreach ($items as &$item) {
        $item['original_unit_price'] = (float)$item['unit_price'];
        $item['original_line_total'] = (float)$item['line_total'];
        $item['line_discount_value'] = 0.0;
        $item['discounted_unit_price'] = (float)$item['unit_price'];
        $item['applied_offer_type'] = '';
        $item['applied_offer_title'] = '';
        $item['remaining_qty_for_offer'] = (int)$item['quantity'];
    }
    unset($item);

    foreach ($ctx['bundles'] as $bundle) {
        $requiredSets = null;
        foreach ($bundle['items'] as $bundleItem) {
            $neededProduct = (int)$bundleItem['product_id'];
            $neededQty = (int)$bundleItem['qty'];
            if ($neededQty <= 0) continue;
            $available = 0;
            foreach ($items as $item) {
                if ((int)$item['product_id'] === $neededProduct) {
                    $available += (int)$item['remaining_qty_for_offer'];
                }
            }
            $sets = intdiv($available, $neededQty);
            $requiredSets = $requiredSets === null ? $sets : min($requiredSets, $sets);
        }
        $requiredSets = (int)($requiredSets ?? 0);
        if ($requiredSets <= 0) continue;

        $oldTotal = 0.0;
        foreach ($bundle['items'] as $bundleItem) {
            $oldTotal += (float)$bundleItem['line_total'];
        }
        $newTotal = (float)($bundle['new_price_total'] ?? 0);
        if ($oldTotal <= 0 || $newTotal >= $oldTotal) continue;

        $bundleDiscountTotal = ($oldTotal - $newTotal) * $requiredSets;
        foreach ($bundle['items'] as $bundleItem) {
            $productId = (int)$bundleItem['product_id'];
            $requiredQty = (int)$bundleItem['qty'] * $requiredSets;
            if ($requiredQty <= 0) continue;
            $shareBase = (float)$bundleItem['line_total'];
            $productDiscountShare = $oldTotal > 0 ? ($shareBase / $oldTotal) * $bundleDiscountTotal : 0;
            $remainingForThisProduct = $requiredQty;
            foreach ($items as &$item) {
                if ((int)$item['product_id'] !== $productId || (int)$item['remaining_qty_for_offer'] <= 0 || $remainingForThisProduct <= 0) {
                    continue;
                }
                $take = min((int)$item['remaining_qty_for_offer'], $remainingForThisProduct);
                $ratio = $requiredQty > 0 ? ($take / $requiredQty) : 0;
                $item['line_discount_value'] += $productDiscountShare * $ratio;
                $item['remaining_qty_for_offer'] -= $take;
                $remainingForThisProduct -= $take;
                $item['applied_offer_type'] = 'bundle';
                $item['applied_offer_title'] = $bundle['title'];
            }
            unset($item);
        }
    }

    foreach ($items as &$item) {
        $remainingQty = (int)$item['remaining_qty_for_offer'];
        $baseUnitPrice = (float)$item['original_unit_price'];
        $brandPart = (string)($item['brand_part'] ?? '');
        $bestUnitDiscount = 0.0;
        $bestUnitOfferType = '';
        $bestUnitOfferTitle = '';

        if (isset($ctx['individual'][(int)$item['product_id']])) {
            $offer = $ctx['individual'][(int)$item['product_id']];
            $disc = max(0.0, $baseUnitPrice - (float)$offer['new_price']);
            if ($disc > $bestUnitDiscount) {
                $bestUnitDiscount = $disc;
                $bestUnitOfferType = 'individual';
                $bestUnitOfferTitle = (string)$offer['title'];
            }
        }

        $companyOffer = order_company_offer_for_brand($ctx['company'], $brandPart);
        if ($companyOffer) {
            $disc = 0.0;
            if (($companyOffer['discount_type'] ?? '') === 'percent') {
                $disc = $baseUnitPrice * ((float)$companyOffer['discount_value'] / 100);
            } else {
                $disc = (float)$companyOffer['discount_value'];
            }
            $disc = min($disc, $baseUnitPrice);
            if ($disc > $bestUnitDiscount) {
                $bestUnitDiscount = $disc;
                $bestUnitOfferType = 'company';
                $bestUnitOfferTitle = (string)$companyOffer['title'];
            }
        }

        $bestTotalDiscount = $bestUnitDiscount * $remainingQty;
        $appliedType = $bestUnitOfferType;
        $appliedTitle = $bestUnitOfferTitle;

        foreach (($ctx['quantity'][(int)$item['product_id']] ?? []) as $qtyOffer) {
            $requiredQty = (int)$qtyOffer['required_qty'];
            if ($requiredQty <= 1 || $remainingQty < $requiredQty) continue;
            $sets = intdiv($remainingQty, $requiredQty);
            $qtyOfferDiscount = max(0.0, ((float)$qtyOffer['old_price_total'] - (float)$qtyOffer['new_price_total']) * $sets);
            $leftoverQty = $remainingQty - ($sets * $requiredQty);
            $combinedDiscount = $qtyOfferDiscount + ($bestUnitDiscount * $leftoverQty);
            if ($combinedDiscount > $bestTotalDiscount) {
                $bestTotalDiscount = $combinedDiscount;
                $appliedType = 'quantity';
                $appliedTitle = (string)$qtyOffer['title'];
            }
        }

        $item['line_discount_value'] += $bestTotalDiscount;
        if ($appliedType !== '') {
            if ($item['applied_offer_title'] !== '' && $item['applied_offer_title'] !== $appliedTitle) {
                $item['applied_offer_title'] .= ' + ' . $appliedTitle;
            } else {
                $item['applied_offer_title'] = $appliedTitle;
            }
            if ($item['applied_offer_type'] === '') {
                $item['applied_offer_type'] = $appliedType;
            }
        }

        $item['line_discount_value'] = min($item['line_discount_value'], $item['original_line_total']);
        $finalLineTotal = max(0.0, $item['original_line_total'] - $item['line_discount_value']);
        $item['line_total'] = $finalLineTotal;
        $item['discounted_unit_price'] = $item['quantity'] > 0 ? $finalLineTotal / (int)$item['quantity'] : 0.0;
        $item['unit_price'] = $item['discounted_unit_price'];
    }
    unset($item);

    return $items;
}

function order_get_products_for_select(mysqli $conn): array
{
    $rows = [];
    $sql = "SELECT p.id, p.name, p.sku, p.price, p.stock_qty, p.has_variants, p.main_image, c.name AS category_name
            FROM products p
            LEFT JOIN categories c ON c.id = p.category_id
            WHERE p.status = 1
            ORDER BY p.id DESC";

    if ($res = $conn->query($sql)) {
        while ($row = $res->fetch_assoc()) {
            $row['variants'] = [];
            if ((int) $row['has_variants'] === 1) {
                $variantStmt = $conn->prepare("SELECT id, variant_name, sku, price, stock_qty, variant_image
                                               FROM product_variants
                                               WHERE product_id = ? AND status = 1
                                               ORDER BY sort_order ASC, id ASC");
                $variantStmt->bind_param('i', $row['id']);
                $variantStmt->execute();
                $variantRes = $variantStmt->get_result();
                while ($variant = $variantRes->fetch_assoc()) {
                    $row['variants'][] = $variant;
                }
                $variantStmt->close();
            }
            $rows[] = $row;
        }
        $res->close();
    }

    return $rows;
}

function order_get_promos(mysqli $conn): array
{
    $rows = [];
    $conditions = [
        "status = 1",
        "(start_at IS NULL OR start_at <= NOW())",
        "(end_at IS NULL OR end_at >= NOW())",
    ];
    if (order_table_has_column($conn, 'promo_codes', 'assigned_customer_id')) {
        $conditions[] = "(assigned_customer_id IS NULL OR assigned_customer_id = 0)";
    }
    if (order_table_has_column($conn, 'promo_codes', 'is_generated')) {
        $conditions[] = "COALESCE(is_generated, 0) = 0";
    }
    $sql = "SELECT id, title, code, discount_type, discount_value, minimum_order_amount, maximum_discount_amount
            FROM promo_codes
            WHERE " . implode(' AND ', $conditions) . "
            ORDER BY id DESC";

    if ($res = $conn->query($sql)) {
        while ($row = $res->fetch_assoc()) {
            $rows[] = $row;
        }
        $res->close();
    }

    return $rows;
}

function order_fetch_customers(mysqli $conn): array
{
    $rows = [];
    $sql = "SELECT id, full_name, phone, email, points_balance FROM customers WHERE status = 1 ORDER BY id DESC";
    if ($res = $conn->query($sql)) {
        while ($row = $res->fetch_assoc()) {
            $rows[] = $row;
        }
        $res->close();
    }
    return $rows;
}

function order_calculate_discount(float $subtotal, ?array $promo): float
{
    if (!$promo || $subtotal <= 0) {
        return 0.0;
    }

    if ($subtotal < (float) $promo['minimum_order_amount']) {
        return 0.0;
    }

    $discount = 0.0;
    if ($promo['discount_type'] === 'percent') {
        $discount = $subtotal * (((float) $promo['discount_value']) / 100);
    } else {
        $discount = (float) $promo['discount_value'];
    }

    if (!empty($promo['maximum_discount_amount']) && $discount > (float) $promo['maximum_discount_amount']) {
        $discount = (float) $promo['maximum_discount_amount'];
    }

    return max(0.0, $discount);
}

function order_find_promo(mysqli $conn, int $promoId): ?array
{
    if ($promoId <= 0) {
        return null;
    }
    $columns = [
        'id', 'title', 'code', 'discount_type', 'discount_value', 'minimum_order_amount', 'maximum_discount_amount',
        'influencer_id', 'influencer_commission_type', 'influencer_commission_value'
    ];
    if (order_table_has_column($conn, 'promo_codes', 'assigned_customer_id')) {
        $columns[] = 'assigned_customer_id';
    }
    if (order_table_has_column($conn, 'promo_codes', 'generated_by_campaign_id')) {
        $columns[] = 'generated_by_campaign_id';
    }
    if (order_table_has_column($conn, 'promo_codes', 'is_generated')) {
        $columns[] = 'is_generated';
    }
    $sql = "SELECT " . implode(', ', $columns) . "
            FROM promo_codes
            WHERE id = ? AND status = 1
              AND (start_at IS NULL OR start_at <= NOW())
              AND (end_at IS NULL OR end_at >= NOW())
            LIMIT 1";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('i', $promoId);
    $stmt->execute();
    $promo = $stmt->get_result()->fetch_assoc() ?: null;
    $stmt->close();
    return $promo;
}

function order_promo_allowed_for_customer(mysqli $conn, ?array $promo, int $customerId): bool
{
    if (!$promo) {
        return true;
    }
    $assignedCustomerId = order_table_has_column($conn, 'promo_codes', 'assigned_customer_id') ? (int)($promo['assigned_customer_id'] ?? 0) : 0;
    if ($assignedCustomerId > 0 && $assignedCustomerId !== $customerId) {
        return false;
    }
    return true;
}

function order_promo_customer_error_message(mysqli $conn, ?array $promo): string
{
    if (!$promo) {
        return 'البروموكود غير صالح.';
    }
    $assignedCustomerId = order_table_has_column($conn, 'promo_codes', 'assigned_customer_id') ? (int)($promo['assigned_customer_id'] ?? 0) : 0;
    if ($assignedCustomerId > 0) {
        return 'هذا الكوبون الشخصي مخصص لزبون آخر ولا يمكن استخدامه على هذا الطلب.';
    }
    return 'البروموكود غير صالح على هذا الطلب.';
}


function order_table_exists(mysqli $conn, string $table): bool
{
    static $cache = [];
    if (isset($cache[$table])) {
        return $cache[$table];
    }
    $safe = $conn->real_escape_string($table);
    $res = $conn->query("SHOW TABLES LIKE '{$safe}'");
    $cache[$table] = $res && $res->num_rows > 0;
    return $cache[$table];
}

function order_bind_type($value): string
{
    if (is_int($value) || is_bool($value)) {
        return 'i';
    }
    if (is_float($value)) {
        return 'd';
    }
    return 's';
}

function order_update_row_if_columns_exist(mysqli $conn, string $table, int $id, array $columns): void
{
    $sets = [];
    $values = [];
    $types = '';
    foreach ($columns as $column => $value) {
        if (!order_table_has_column($conn, $table, (string)$column)) {
            continue;
        }
        $sets[] = "`{$column}` = ?";
        $values[] = $value;
        $types .= order_bind_type($value);
    }
    if (!$sets) {
        return;
    }
    $types .= 'i';
    $values[] = $id;
    $sql = "UPDATE `{$table}` SET " . implode(', ', $sets) . " WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param($types, ...$values);
    $stmt->execute();
    $stmt->close();
}

function order_settings_values(mysqli $conn, array $keys): array
{
    $defaults = [];
    foreach ($keys as $key) {
        $defaults[$key] = null;
    }
    if (!$keys) {
        return $defaults;
    }
    $quoted = [];
    foreach ($keys as $key) {
        $quoted[] = "'" . $conn->real_escape_string((string)$key) . "'";
    }
    $sql = "SELECT setting_key, setting_value FROM settings WHERE setting_key IN (" . implode(',', $quoted) . ")";
    if ($res = $conn->query($sql)) {
        while ($row = $res->fetch_assoc()) {
            $defaults[$row['setting_key']] = $row['setting_value'];
        }
        $res->close();
    }
    return $defaults;
}

function order_loyalty_settings(mysqli $conn): array
{
    $defaults = [
        'points_enabled' => '1',
        'loyalty_points_per_amount' => '10000',
        'loyalty_amount_per_point' => '100',
        'loyalty_min_points_redeem' => '100',
        'loyalty_max_points_per_order' => '0',
        'loyalty_redeem_type' => 'amount',
        'loyalty_gift_reward_title' => 'هدية نقاط الولاء',
        'loyalty_allow_with_promo' => '1',
        'loyalty_allow_with_offers' => '1',
        'loyalty_earn_on_delivered_only' => '1',
    ];
    $dbValues = order_settings_values($conn, array_keys($defaults));
    $values = array_merge($defaults, array_filter($dbValues, static fn($v) => $v !== null));
    return [
        'enabled' => ((string)$values['points_enabled']) === '1',
        'points_per_amount' => max(1, (int)$values['loyalty_points_per_amount']),
        'amount_per_point' => max(0.0, (float)$values['loyalty_amount_per_point']),
        'min_points_redeem' => max(0, (int)$values['loyalty_min_points_redeem']),
        'max_points_per_order' => max(0, (int)$values['loyalty_max_points_per_order']),
        'redeem_type' => ((string)$values['loyalty_redeem_type']) === 'gift' ? 'gift' : 'amount',
        'gift_reward_title' => trim((string)$values['loyalty_gift_reward_title']) ?: 'هدية نقاط الولاء',
        'allow_with_promo' => ((string)$values['loyalty_allow_with_promo']) === '1',
        'allow_with_offers' => ((string)$values['loyalty_allow_with_offers']) === '1',
        'earn_on_delivered_only' => ((string)$values['loyalty_earn_on_delivered_only']) === '1',
    ];
}


function order_promotion_settings(mysqli $conn): array
{
    $defaults = [
        'promotions_priority' => 'offer_first',
        'promotions_threshold_rules_enabled' => '1',
        'promotions_allow_special_price_with_promo' => '1',
        'promotions_allow_free_delivery_with_promo' => '1',
        'orders_free_delivery_min_amount' => '0',
    ];
    $values = array_merge($defaults, order_settings_values($conn, array_keys($defaults)));
    return [
        'priority' => in_array((string)$values['promotions_priority'], ['offer_first','promo_first','best_discount'], true) ? (string)$values['promotions_priority'] : 'offer_first',
        'threshold_enabled' => ((string)$values['promotions_threshold_rules_enabled']) === '1',
        'allow_special_with_promo' => ((string)$values['promotions_allow_special_price_with_promo']) === '1',
        'allow_free_delivery_with_promo' => ((string)$values['promotions_allow_free_delivery_with_promo']) === '1',
        'free_delivery_min' => max(0.0, (float)$values['orders_free_delivery_min_amount']),
    ];
}

function order_province_id_by_name(mysqli $conn, string $provinceName): int
{
    $provinceName = trim($provinceName);
    if ($provinceName === '') return 0;
    $stmt = $conn->prepare("SELECT id FROM provinces WHERE name = ? LIMIT 1");
    if (!$stmt) return 0;
    $stmt->bind_param('s', $provinceName);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: null;
    $stmt->close();
    return (int)($row['id'] ?? 0);
}

function order_fetch_threshold_promotions(mysqli $conn, int $provinceId = 0): array
{
    $rows = [];
    if (!order_table_exists($conn, 'threshold_promotions')) return $rows;
    $sql = "SELECT * FROM threshold_promotions WHERE status = 1";
    if ($provinceId > 0) {
        $sql .= " AND (province_id IS NULL OR province_id = 0 OR province_id = {$provinceId})";
    }
    $sql .= " ORDER BY priority DESC, min_subtotal DESC, id DESC";
    if ($res = $conn->query($sql)) {
        while ($row = $res->fetch_assoc()) $rows[] = $row;
        $res->close();
    }
    return $rows;
}

function order_apply_threshold_special_price(array &$items, array $rules, float $qualifyingSubtotal): array
{
    $discount = 0.0;
    $titles = [];
    foreach ($items as $idx => $item) {
        $currentLineTotal = (float)($item['line_total'] ?? 0);
        $qty = max(1, (int)($item['quantity'] ?? 1));
        $currentUnit = $currentLineTotal / $qty;
        $bestDiscount = 0.0;
        $bestLineTotal = $currentLineTotal;
        $bestTitle = '';
        foreach ($rules as $rule) {
            if (($rule['promo_type'] ?? '') !== 'special_price_product') continue;
            if ((float)($rule['min_subtotal'] ?? 0) > $qualifyingSubtotal) continue;
            if ((int)($rule['product_id'] ?? 0) !== (int)$item['product_id']) continue;
            $variantRule = (int)($rule['variant_id'] ?? 0);
            if ($variantRule > 0 && $variantRule !== (int)($item['variant_id'] ?? 0)) continue;
            $special = (float)($rule['special_price'] ?? 0);
            if ($special <= 0 || $special >= $currentUnit) continue;
            $candidateLineTotal = $special * $qty;
            $candidateDiscount = max(0.0, $currentLineTotal - $candidateLineTotal);
            if ($candidateDiscount > $bestDiscount) {
                $bestDiscount = $candidateDiscount;
                $bestLineTotal = $candidateLineTotal;
                $bestTitle = (string)($rule['title'] ?? 'سعر خاص');
            }
        }
        if ($bestDiscount > 0) {
            $items[$idx]['line_total'] = $bestLineTotal;
            $items[$idx]['line_discount_value'] = (float)($item['line_discount_value'] ?? 0) + $bestDiscount;
            $items[$idx]['applied_offer_type'] = trim(((string)($item['applied_offer_type'] ?? '')) . ' threshold');
            $items[$idx]['applied_offer_title'] = trim((string)($item['applied_offer_title'] ?? ''));
            if ($items[$idx]['applied_offer_title'] !== '') $items[$idx]['applied_offer_title'] .= ' + ';
            $items[$idx]['applied_offer_title'] .= $bestTitle;
            $discount += $bestDiscount;
            $titles[] = $bestTitle;
        }
    }
    return ['discount' => $discount, 'titles' => array_values(array_unique($titles))];
}

function order_pick_free_delivery_rule(array $rules, float $qualifyingSubtotal): ?array
{
    foreach ($rules as $rule) {
        if (($rule['promo_type'] ?? '') !== 'free_delivery') continue;
        if ((float)($rule['min_subtotal'] ?? 0) <= $qualifyingSubtotal) return $rule;
    }
    return null;
}


function order_fetch_active_addons(mysqli $conn): array
{
    $rows = [];
    if (!order_table_exists($conn, 'order_addons')) {
        return $rows;
    }
    $sql = "SELECT oa.*, p.name AS province_name
            FROM order_addons oa
            LEFT JOIN provinces p ON p.id = oa.province_id
            WHERE oa.status = 1
            ORDER BY oa.sort_order ASC, oa.id DESC";
    if ($res = $conn->query($sql)) {
        while ($row = $res->fetch_assoc()) {
            $rows[] = $row;
        }
        $res->close();
    }
    return $rows;
}

function order_validate_selected_addons(mysqli $conn, array $addonIds, int $provinceId, array &$errors): array
{
    $addonIds = array_values(array_unique(array_filter(array_map('intval', $addonIds), static fn($v) => $v > 0)));
    if (!$addonIds || !order_table_exists($conn, 'order_addons')) {
        return [];
    }

    $placeholders = implode(',', array_fill(0, count($addonIds), '?'));
    $types = str_repeat('i', count($addonIds));
    $sql = "SELECT oa.*, p.name AS province_name
            FROM order_addons oa
            LEFT JOIN provinces p ON p.id = oa.province_id
            WHERE oa.id IN ($placeholders) AND oa.status = 1";
    $stmt = $conn->prepare($sql);
    $bindParams = [$types];
    foreach ($addonIds as $k => $v) {
        $bindParams[] = &$addonIds[$k];
    }
    call_user_func_array([$stmt, 'bind_param'], $bindParams);
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($row = $res->fetch_assoc()) {
        $provinceRestriction = (int)($row['province_id'] ?? 0);
        if ($provinceRestriction > 0 && $provinceRestriction !== $provinceId) {
            $errors[] = 'الخدمة «' . (string)$row['title'] . '» متاحة لمحافظة محددة فقط.';
            continue;
        }
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function order_addons_total($addons): float
{
    $addons = is_array($addons) ? $addons : [];
    $sum = 0.0;
    foreach ($addons as $addon) {
        $sum += (float)($addon['fee'] ?? 0);
    }
    return $sum;
}

function order_save_order_addons(mysqli $conn, int $orderId, $addons): void
{
    $addons = is_array($addons) ? $addons : [];
    if ($orderId <= 0 || !order_table_exists($conn, 'order_addon_items')) {
        return;
    }
    $conn->query('DELETE FROM order_addon_items WHERE order_id = ' . (int)$orderId);
    if (!$addons) {
        return;
    }
    $stmt = $conn->prepare("INSERT INTO order_addon_items (order_id, addon_id, addon_title, addon_code, addon_fee) VALUES (?, ?, ?, ?, ?)");
    foreach ($addons as $addon) {
        $addonId = (int)($addon['id'] ?? 0);
        $title = (string)($addon['title'] ?? '');
        $code = (string)($addon['code'] ?? '');
        $fee = (float)($addon['fee'] ?? 0);
        $stmt->bind_param('iissd', $orderId, $addonId, $title, $code, $fee);
        $stmt->execute();
    }
    $stmt->close();
}

function order_get_order_addons(mysqli $conn, int $orderId): array
{
    $rows = [];
    if ($orderId <= 0 || !order_table_exists($conn, 'order_addon_items')) {
        return $rows;
    }
    $stmt = $conn->prepare("SELECT * FROM order_addon_items WHERE order_id = ? ORDER BY id ASC");
    $stmt->bind_param('i', $orderId);
    $stmt->execute();
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function order_pricing_breakdown_json(array $data): string
{
    return json_encode($data, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
}

function order_customer_points_balance(mysqli $conn, int $customerId): int
{
    if ($customerId <= 0) {
        return 0;
    }
    $stmt = $conn->prepare("SELECT points_balance FROM customers WHERE id = ? LIMIT 1");
    $stmt->bind_param('i', $customerId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc();
    $stmt->close();
    return (int)($row['points_balance'] ?? 0);
}

function order_loyalty_calculate_earned_points(array $settings, float $earnableBase): int
{
    if (!$settings['enabled']) {
        return 0;
    }
    $base = max(0.0, $earnableBase);
    $perAmount = max(1, (int)$settings['points_per_amount']);
    return (int)floor($base / $perAmount);
}

function order_loyalty_prepare(array $settings, int $availablePoints, float $subtotalAfterOffers, float $promoDiscountValue, array $payload, bool $hasOffers, bool $hasPromo, array &$errors): array
{
    $netBase = max(0.0, $subtotalAfterOffers - $promoDiscountValue);
    $result = [
        'use_points' => false,
        'points_redeemed' => 0,
        'discount_value' => 0.0,
        'redemption_type' => (string)$settings['redeem_type'],
        'reward_title' => '',
        'earnable_base' => $netBase,
        'earned_points' => order_loyalty_calculate_earned_points($settings, $netBase),
    ];

    $requestedPoints = max(0, (int)($payload['loyalty_points_to_redeem'] ?? 0));
    $usePoints = !empty($payload['use_loyalty_points']) || $requestedPoints > 0;

    if (!$settings['enabled'] || !$usePoints) {
        return $result;
    }
    if ($hasPromo && !$settings['allow_with_promo']) {
        $errors[] = 'إعدادات النظام تمنع استخدام النقاط مع البروموكود.';
        return $result;
    }
    if ($hasOffers && !$settings['allow_with_offers']) {
        $errors[] = 'إعدادات النظام تمنع استخدام النقاط مع العروض.';
        return $result;
    }
    if ($requestedPoints <= 0) {
        $errors[] = 'حدد عدد النقاط المراد استخدامها.';
        return $result;
    }
    if ($availablePoints <= 0) {
        $errors[] = 'رصيد الزبون من النقاط غير كافٍ.';
        return $result;
    }

    $minPoints = max(0, (int)$settings['min_points_redeem']);
    if ($requestedPoints < $minPoints) {
        $errors[] = 'الحد الأدنى لاستخدام النقاط هو ' . number_format($minPoints) . ' نقطة.';
        return $result;
    }

    $effectiveMax = $availablePoints;
    if ((int)$settings['max_points_per_order'] > 0) {
        $effectiveMax = min($effectiveMax, (int)$settings['max_points_per_order']);
    }
    $effectiveMax = min($effectiveMax, $requestedPoints);

    if ($settings['redeem_type'] === 'amount') {
        $amountPerPoint = (float)$settings['amount_per_point'];
        if ($amountPerPoint <= 0) {
            $errors[] = 'قيمة النقطة غير مضبوطة داخل الإعدادات.';
            return $result;
        }
        $maxByAmount = (int)floor($netBase / $amountPerPoint);
        $effectiveMax = min($effectiveMax, $maxByAmount);
        if ($effectiveMax < $minPoints) {
            $errors[] = 'القيمة الحالية للطلب لا تسمح باستخدام هذا العدد من النقاط.';
            return $result;
        }
        $result['use_points'] = true;
        $result['points_redeemed'] = $effectiveMax;
        $result['discount_value'] = min($netBase, $effectiveMax * $amountPerPoint);
    } else {
        if ($effectiveMax < $minPoints) {
            $errors[] = 'رصيد النقاط لا يكفي لاستبدال الهدية.';
            return $result;
        }
        $result['use_points'] = true;
        $result['points_redeemed'] = $effectiveMax;
        $result['discount_value'] = 0.0;
        $result['reward_title'] = (string)$settings['gift_reward_title'];
    }

    $result['earnable_base'] = max(0.0, $netBase - $result['discount_value']);
    $result['earned_points'] = order_loyalty_calculate_earned_points($settings, $result['earnable_base']);
    return $result;
}

function order_loyalty_add_transaction(mysqli $conn, int $customerId, ?int $orderId, string $txnType, int $pointsDelta, int $balanceAfter, ?int $adminId = null, string $redemptionType = 'amount', string $rewardTitle = '', string $notes = ''): void
{
    if ($customerId <= 0 || $pointsDelta === 0 || !order_table_exists($conn, 'customer_loyalty_transactions')) {
        return;
    }
    $stmt = $conn->prepare("INSERT INTO customer_loyalty_transactions (customer_id, order_id, admin_id, txn_type, points_delta, balance_after, redemption_type, reward_title, notes) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
    $stmt->bind_param('iiisiisss', $customerId, $orderId, $adminId, $txnType, $pointsDelta, $balanceAfter, $redemptionType, $rewardTitle, $notes);
    $stmt->execute();
    $stmt->close();
}

function order_loyalty_adjust_points(mysqli $conn, int $customerId, int $delta, ?int $orderId = null, string $txnType = 'manual', ?int $adminId = null, string $redemptionType = 'amount', string $rewardTitle = '', string $notes = ''): void
{
    if ($customerId <= 0 || $delta === 0) {
        return;
    }
    $stmt = $conn->prepare("UPDATE customers SET points_balance = GREATEST(0, points_balance + ?) WHERE id = ?");
    $stmt->bind_param('ii', $delta, $customerId);
    $stmt->execute();
    $stmt->close();

    $balanceAfter = order_customer_points_balance($conn, $customerId);
    order_loyalty_add_transaction($conn, $customerId, $orderId, $txnType, $delta, $balanceAfter, $adminId, $redemptionType, $rewardTitle, $notes);
}

function order_loyalty_sync_for_order(mysqli $conn, ?array $beforeOrder, array $afterOrder, ?int $adminId = null): void
{
    $beforeStatus = (string)($beforeOrder['order_status'] ?? '');
    $afterStatus = (string)($afterOrder['order_status'] ?? '');

    if ($beforeOrder && !order_status_is_cancel_like($beforeStatus)) {
        $beforeRedeemed = (int)($beforeOrder['loyalty_points_redeemed'] ?? 0);
        if ($beforeRedeemed > 0 && !empty($beforeOrder['customer_id'])) {
            order_loyalty_adjust_points($conn, (int)$beforeOrder['customer_id'], $beforeRedeemed, (int)$beforeOrder['id'], 'redeem_restore', $adminId, (string)($beforeOrder['loyalty_redemption_type'] ?? 'amount'), (string)($beforeOrder['loyalty_reward_title'] ?? ''), 'إرجاع نقاط الطلب قبل التعديل');
        }
    }

    if ($beforeOrder && (int)($beforeOrder['loyalty_points_awarded'] ?? 0) === 1 && (int)($beforeOrder['loyalty_points_earned'] ?? 0) > 0 && !empty($beforeOrder['customer_id'])) {
        order_loyalty_adjust_points($conn, (int)$beforeOrder['customer_id'], -1 * (int)$beforeOrder['loyalty_points_earned'], (int)$beforeOrder['id'], 'earn_reverse', $adminId, 'amount', '', 'سحب نقاط مكتسبة بسبب تعديل الحالة');
    }

    $awarded = 0;
    if (!order_status_is_cancel_like($afterStatus)) {
        $afterRedeemed = (int)($afterOrder['loyalty_points_redeemed'] ?? 0);
        if ($afterRedeemed > 0 && !empty($afterOrder['customer_id'])) {
            order_loyalty_adjust_points($conn, (int)$afterOrder['customer_id'], -1 * $afterRedeemed, (int)$afterOrder['id'], 'redeem', $adminId, (string)($afterOrder['loyalty_redemption_type'] ?? 'amount'), (string)($afterOrder['loyalty_reward_title'] ?? ''), 'استخدام نقاط داخل الطلب');
        }

        if ($afterStatus === 'delivered' && (int)($afterOrder['loyalty_points_earned'] ?? 0) > 0 && !empty($afterOrder['customer_id'])) {
            order_loyalty_adjust_points($conn, (int)$afterOrder['customer_id'], (int)$afterOrder['loyalty_points_earned'], (int)$afterOrder['id'], 'earn', $adminId, 'amount', '', 'إضافة نقاط بعد تم التسليم');
            $awarded = 1;
        }
    }

    order_update_row_if_columns_exist($conn, 'orders', (int)$afterOrder['id'], [
        'loyalty_points_awarded' => $awarded,
    ]);
}

function order_stock_ref_key(int $productId, ?int $variantId): string
{
    return $variantId ? ('v:' . $variantId) : ('p:' . $productId);
}

function order_status_is_cancel_like(string $status): bool
{
    return order_normalize_status($status) === 'cancelled';
}

function order_status_requires_stock(string $status): bool
{
    return in_array(order_normalize_status($status), ['pending', 'ready', 'delivered', 'postponed'], true);
}

function order_status_awards_loyalty(string $status): bool
{
    return order_normalize_status($status) === 'delivered';
}


function order_status_awards_tier(string $status): bool
{
    return order_normalize_status($status) === 'delivered';
}

function order_customer_stats_snapshot(mysqli $conn, int $customerId): array
{
    if ($customerId <= 0) {
        return ['total_orders' => 0, 'total_spent' => 0.0, 'last_delivered_order_at' => null];
    }

    $sql = "SELECT COUNT(*) AS total_orders,
                   COALESCE(SUM(total_amount), 0) AS total_spent,
                   MAX(COALESCE(delivered_at, updated_at, created_at)) AS last_delivered_order_at
            FROM orders
            WHERE customer_id = ? AND " . order_status_sql_clause('order_status') . " = 'delivered'";
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return ['total_orders' => 0, 'total_spent' => 0.0, 'last_delivered_order_at' => null];
    }
    $stmt->bind_param('i', $customerId);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: [];
    $stmt->close();

    return [
        'total_orders' => (int)($row['total_orders'] ?? 0),
        'total_spent' => round((float)($row['total_spent'] ?? 0), 2),
        'last_delivered_order_at' => !empty($row['last_delivered_order_at']) ? (string)$row['last_delivered_order_at'] : null,
    ];
}

function order_sync_customer_order_stats(mysqli $conn, int $customerId): void
{
    if ($customerId <= 0) {
        return;
    }

    $stats = order_customer_stats_snapshot($conn, $customerId);
    $stmt = $conn->prepare('UPDATE customers SET total_orders = ?, total_spent = ?, last_delivered_order_at = ? WHERE id = ? LIMIT 1');
    if (!$stmt) {
        return;
    }
    $stmt->bind_param('idsi', $stats['total_orders'], $stats['total_spent'], $stats['last_delivered_order_at'], $customerId);
    $stmt->execute();
    $stmt->close();
}

function order_matching_tier_id(mysqli $conn, int $customerId): int
{
    if ($customerId <= 0 || !order_table_exists($conn, 'customer_tiers')) {
        return 0;
    }

    $customerStmt = $conn->prepare('SELECT id, COALESCE(tier_points,0) AS tier_points, COALESCE(total_orders,0) AS total_orders, COALESCE(total_spent,0) AS total_spent, tier_id, last_delivered_order_at, tier_cycle_started_at FROM customers WHERE id = ? LIMIT 1');
    if (!$customerStmt) {
        return 0;
    }
    $customerStmt->bind_param('i', $customerId);
    $customerStmt->execute();
    $customer = $customerStmt->get_result()->fetch_assoc() ?: null;
    $customerStmt->close();
    if (!$customer) {
        return 0;
    }

    $tiers = [];
    $result = $conn->query("SELECT id, min_points, min_total_spent, min_total_orders, sort_order FROM customer_tiers WHERE is_active = 1 ORDER BY min_points ASC, min_total_spent ASC, min_total_orders ASC, sort_order ASC, id ASC");
    if ($result instanceof mysqli_result) {
        while ($row = $result->fetch_assoc()) {
            $tiers[] = $row;
        }
        $result->close();
    }
    if (!$tiers) {
        return 0;
    }

    $effectivePoints = (int)($customer['tier_points'] ?? 0);
    $cycleMode = strtolower(trim(order_setting_value($conn, 'tier_cycle_mode', 'permanent')));
    if ($cycleMode === 'annual') {
        $cycleStartedAt = strtotime((string)($customer['tier_cycle_started_at'] ?? ''));
        if ($cycleStartedAt && $cycleStartedAt < strtotime('-365 days')) {
            $effectivePoints = 0;
        }
    }

    $inactivityDays = max(0, (int)order_setting_value($conn, 'tier_inactivity_days', '0'));
    if ($inactivityDays > 0 && !empty($customer['last_delivered_order_at'])) {
        $lastDelivered = strtotime((string)$customer['last_delivered_order_at']);
        if ($lastDelivered && $lastDelivered < strtotime('-' . $inactivityDays . ' days')) {
            $effectivePoints = max(0, (int)floor($effectivePoints / 2));
        }
    }

    $totalSpent = (float)($customer['total_spent'] ?? 0);
    $totalOrders = (int)($customer['total_orders'] ?? 0);
    $matchId = 0;
    foreach ($tiers as $tier) {
        $minPoints = (int)($tier['min_points'] ?? 0);
        $minSpent = (float)($tier['min_total_spent'] ?? 0);
        $minOrders = (int)($tier['min_total_orders'] ?? 0);
        if ($effectivePoints >= $minPoints && $totalSpent >= $minSpent && $totalOrders >= $minOrders) {
            $matchId = (int)($tier['id'] ?? 0);
        }
    }

    return $matchId;
}

function order_assign_customer_tier(mysqli $conn, int $customerId, int $tierId, ?int $adminId = null, string $note = ''): void
{
    if ($customerId <= 0 || $tierId <= 0) {
        return;
    }

    $stmt = $conn->prepare('UPDATE customers SET tier_id = ? WHERE id = ? LIMIT 1');
    if ($stmt) {
        $stmt->bind_param('ii', $tierId, $customerId);
        $stmt->execute();
        $stmt->close();
    }

    if (order_table_exists($conn, 'customer_tier_history')) {
        $historyStmt = $conn->prepare('INSERT INTO customer_tier_history (customer_id, tier_id, assigned_by_admin_id, note, created_at) VALUES (?, ?, ?, ?, NOW())');
        if ($historyStmt) {
            $historyStmt->bind_param('iiis', $customerId, $tierId, $adminId, $note);
            $historyStmt->execute();
            $historyStmt->close();
        }
    }
}

function order_sync_customer_tier(mysqli $conn, int $customerId, ?int $adminId = null, string $note = 'مزامنة تلقائية بعد تحديث الطلب'): void
{
    if ($customerId <= 0 || !order_table_exists($conn, 'customer_tiers')) {
        return;
    }

    $stmt = $conn->prepare('SELECT tier_id FROM customers WHERE id = ? LIMIT 1');
    if (!$stmt) {
        return;
    }
    $stmt->bind_param('i', $customerId);
    $stmt->execute();
    $current = $stmt->get_result()->fetch_assoc() ?: ['tier_id' => 0];
    $stmt->close();

    $matchId = order_matching_tier_id($conn, $customerId);
    if ($matchId > 0 && (int)($current['tier_id'] ?? 0) !== $matchId) {
        order_assign_customer_tier($conn, $customerId, $matchId, $adminId, $note);
    }
}

function order_sync_customer_metrics_and_tier(mysqli $conn, int $customerId, ?int $adminId = null, string $note = 'مزامنة تلقائية بعد تحديث الطلب'): void
{
    if ($customerId <= 0) {
        return;
    }
    order_sync_customer_order_stats($conn, $customerId);
    order_sync_customer_tier($conn, $customerId, $adminId, $note);
}

function order_tier_sync_for_order(mysqli $conn, ?array $beforeOrder, array $afterOrder, ?int $adminId = null): void
{
    $beforeStatus = (string)($beforeOrder['order_status'] ?? '');
    $afterStatus = (string)($afterOrder['order_status'] ?? '');
    $beforeDelivered = $beforeOrder ? order_status_awards_tier($beforeStatus) : false;
    $afterDelivered = order_status_awards_tier($afterStatus);

    $beforeCustomerId = (int)($beforeOrder['customer_id'] ?? 0);
    $afterCustomerId = (int)($afterOrder['customer_id'] ?? 0);
    $beforeAwarded = (int)($beforeOrder['tier_points_awarded'] ?? 0) === 1;
    $tierPoints = max(0, (int)($afterOrder['tier_points_earned'] ?? $beforeOrder['tier_points_earned'] ?? 0));

    if ($beforeAwarded && $beforeCustomerId > 0 && ($beforeCustomerId !== $afterCustomerId || !$afterDelivered)) {
        if ($tierPoints > 0) {
            $stmt = $conn->prepare('UPDATE customers SET tier_points = GREATEST(0, COALESCE(tier_points,0) - ?) WHERE id = ? LIMIT 1');
            if ($stmt) {
                $negative = $tierPoints;
                $stmt->bind_param('ii', $negative, $beforeCustomerId);
                $stmt->execute();
                $stmt->close();
            }
        }
        order_update_row_if_columns_exist($conn, 'orders', (int)($afterOrder['id'] ?? $beforeOrder['id'] ?? 0), ['tier_points_awarded' => 0]);
    }

    $alreadyAwardedForAfter = $beforeAwarded && $beforeCustomerId === $afterCustomerId && $beforeDelivered && $afterDelivered;
    if (!$alreadyAwardedForAfter && $afterDelivered && $afterCustomerId > 0 && $tierPoints > 0) {
        $stmt = $conn->prepare('UPDATE customers SET tier_points = COALESCE(tier_points,0) + ?, last_delivered_order_at = COALESCE(?, last_delivered_order_at), tier_cycle_started_at = COALESCE(tier_cycle_started_at, ?) WHERE id = ? LIMIT 1');
        if ($stmt) {
            $deliveredAt = !empty($afterOrder['delivered_at']) ? (string)$afterOrder['delivered_at'] : date('Y-m-d H:i:s');
            $stmt->bind_param('issi', $tierPoints, $deliveredAt, $deliveredAt, $afterCustomerId);
            $stmt->execute();
            $stmt->close();
        }
        order_update_row_if_columns_exist($conn, 'orders', (int)($afterOrder['id'] ?? 0), ['tier_points_awarded' => 1]);
    }

    $customerIds = array_values(array_unique(array_filter([$beforeCustomerId, $afterCustomerId])));
    foreach ($customerIds as $customerId) {
        order_sync_customer_metrics_and_tier($conn, (int)$customerId, $adminId);
    }
}


function order_wallet_balance(mysqli $conn, int $customerId): float
{
    if ($customerId <= 0 || !order_table_exists($conn, 'customer_wallet_transactions')) {
        return 0.0;
    }
    $stmt = $conn->prepare("SELECT amount, direction, txn_type FROM customer_wallet_transactions WHERE customer_id = ?");
    if (!$stmt) {
        return 0.0;
    }
    $stmt->bind_param('i', $customerId);
    $rows = [];
    $stmt->execute();
    $res = $stmt->get_result();
    while ($row = $res->fetch_assoc()) {
        $rows[] = $row;
    }
    $stmt->close();
    $balance = 0.0;
    foreach ($rows as $row) {
        $amount = (float)($row['amount'] ?? 0);
        $direction = strtolower((string)($row['direction'] ?? ''));
        $txnType = strtolower((string)($row['txn_type'] ?? ''));
        $isDebit = $direction === 'debit' || in_array($txnType, ['debit', 'withdraw', 'withdrawal', 'refund_debit', 'chargeback', 'order_wallet_use'], true) || $amount < 0;
        $balance += $isDebit ? -abs($amount) : abs($amount);
    }
    return round($balance, 2);
}

function order_wallet_amount_refunded(mysqli $conn, int $customerId, int $orderId): float
{
    if ($customerId <= 0 || $orderId <= 0 || !order_table_exists($conn, 'customer_wallet_transactions')) {
        return 0.0;
    }
    $sourceRef = (string)$orderId;
    $stmt = $conn->prepare("SELECT COALESCE(SUM(amount),0) AS total FROM customer_wallet_transactions WHERE customer_id = ? AND source = 'order' AND source_ref = ? AND LOWER(COALESCE(txn_type,'')) IN ('order_refund','refund','credit')");
    if (!$stmt) {
        return 0.0;
    }
    $stmt->bind_param('is', $customerId, $sourceRef);
    $stmt->execute();
    $row = $stmt->get_result()->fetch_assoc() ?: ['total' => 0];
    $stmt->close();
    return round((float)($row['total'] ?? 0), 2);
}

function order_wallet_add_transaction(mysqli $conn, int $customerId, string $txnType, float $amount, string $source = 'order', ?string $sourceRef = null, string $notes = '', ?int $adminId = null): bool
{
    if ($customerId <= 0 || $amount <= 0 || !order_table_exists($conn, 'customer_wallet_transactions')) {
        return false;
    }
    $direction = in_array(strtolower($txnType), ['debit', 'withdraw', 'withdrawal', 'refund_debit', 'chargeback', 'order_wallet_use'], true) ? 'debit' : 'credit';
    $signedAmount = $direction === 'debit' ? -abs($amount) : abs($amount);
    $balanceAfter = order_wallet_balance($conn, $customerId) + $signedAmount;
    $columns = ['customer_id', 'txn_type', 'amount', 'balance_after', 'source', 'source_ref', 'notes'];
    $values = [$customerId, $txnType, abs($amount), $balanceAfter, $source, (string)($sourceRef ?? ''), $notes];
    if (order_table_exists($conn, 'customer_wallet_transactions')) {
        $res = $conn->query("SHOW COLUMNS FROM customer_wallet_transactions LIKE 'direction'");
        if ($res instanceof mysqli_result && $res->num_rows > 0) {
            $columns[] = 'direction';
            $values[] = $direction;
        }
        $res = $conn->query("SHOW COLUMNS FROM customer_wallet_transactions LIKE 'created_by_admin_id'");
        if ($res instanceof mysqli_result && $res->num_rows > 0) {
            $columns[] = 'created_by_admin_id';
            $values[] = (int)($adminId ?? 0);
        }
    }
    $types = '';
    $placeholders = [];
    foreach ($values as $value) {
        $types .= order_bind_type($value);
        $placeholders[] = '?';
    }
    $sql = "INSERT INTO customer_wallet_transactions (" . implode(', ', $columns) . ", created_at) VALUES (" . implode(', ', $placeholders) . ", NOW())";
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return false;
    }
    $stmt->bind_param($types, ...$values);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function order_stock_boosts_from_items(array $items): array
{
    $boosts = [];
    foreach ($items as $item) {
        $productId = (int)($item['product_id'] ?? 0);
        $variantId = !empty($item['variant_id']) ? (int)$item['variant_id'] : null;
        $qty = (int)($item['quantity'] ?? 0);
        if ($productId <= 0 || $qty <= 0) {
            continue;
        }
        $key = order_stock_ref_key($productId, $variantId);
        $boosts[$key] = ($boosts[$key] ?? 0) + $qty;
    }
    return $boosts;
}

function order_validate_items(mysqli $conn, array $items, array $stockBoosts = []): array
{
    $validated = [];
    $errors = [];

    foreach ($items as $index => $item) {
        $productId = (int) ($item['product_id'] ?? 0);
        $variantId = (int) ($item['variant_id'] ?? 0);
        $qty = (int) ($item['quantity'] ?? 0);

        if ($productId <= 0 || $qty <= 0) {
            continue;
        }

        $stmt = $conn->prepare("SELECT id, name, sku, price, stock_qty, has_variants, main_image, brand_part
                                FROM products
                                WHERE id = ? AND status = 1
                                LIMIT 1");
        $stmt->bind_param('i', $productId);
        $stmt->execute();
        $product = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        if (!$product) {
            $errors[] = 'منتج غير موجود في السطر رقم ' . ($index + 1);
            continue;
        }

        $lineName = $product['name'];
        $lineSku = $product['sku'];
        $linePrice = (float) $product['price'];
        $availableQty = (int) $product['stock_qty'];
        $image = $product['main_image'];
        $brandPart = $product['brand_part'] ?? '';
        $variantName = null;

        if ((int) $product['has_variants'] === 1) {
            if ($variantId <= 0) {
                $errors[] = 'لازم تختار تعبئة / فاريانت للمنتج: ' . $product['name'];
                continue;
            }
            $variantStmt = $conn->prepare("SELECT id, variant_name, sku, price, stock_qty, variant_image
                                           FROM product_variants
                                           WHERE id = ? AND product_id = ? AND status = 1
                                           LIMIT 1");
            $variantStmt->bind_param('ii', $variantId, $productId);
            $variantStmt->execute();
            $variant = $variantStmt->get_result()->fetch_assoc();
            $variantStmt->close();

            if (!$variant) {
                $errors[] = 'الفاريانت غير موجود للمنتج: ' . $product['name'];
                continue;
            }

            $variantName = $variant['variant_name'];
            $lineName = $product['name'] . ' - ' . $variantName;
            $lineSku = $variant['sku'];
            $linePrice = (float) $variant['price'];
            $availableQty = (int) $variant['stock_qty'];
            $image = $variant['variant_image'] ?: $image;
        }

        $boostKey = order_stock_ref_key($productId, $variantId ?: null);
        $availableQty += (int)($stockBoosts[$boostKey] ?? 0);

        if ($qty > $availableQty) {
            $errors[] = 'الكمية المطلوبة أكبر من المتوفر للمنتج: ' . $lineName;
            continue;
        }

        $validated[] = [
            'product_id' => $productId,
            'variant_id' => $variantId ?: null,
            'product_name' => $lineName,
            'product_sku' => $lineSku,
            'unit_price' => $linePrice,
            'quantity' => $qty,
            'line_total' => $linePrice * $qty,
            'product_image' => $image,
            'brand_part' => $brandPart,
            'variant_name' => $variantName,
        ];
    }

    return [$validated, $errors];
}

function order_apply_stock_change(mysqli $conn, int $productId, ?int $variantId, int $qty, string $mode = 'deduct', ?int $orderId = null, ?int $adminId = null): void
{
    if ($qty <= 0) {
        return;
    }

    $target = $variantId ? 'variant' : 'product';
    $targetId = $variantId ?: $productId;

    if (function_exists('nay_stock_apply_change')) {
        nay_stock_apply_change($conn, $target, $targetId, $mode, $qty, [
            'admin_id' => $adminId,
            'source' => 'admin_order',
            'reason_code' => $mode === 'restore' ? 'order_restore' : 'order_deduction',
            'reference_type' => 'order',
            'reference_id' => $orderId,
            'notes' => $orderId ? ('حركة مخزون مرتبطة بالطلب #' . $orderId) : 'حركة مخزون مرتبطة بالطلبات',
        ]);
        return;
    }

    $operator = $mode === 'restore' ? '+' : '-';
    if ($variantId) {
        $sql = "UPDATE product_variants SET stock_qty = stock_qty {$operator} ? WHERE id = ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param('ii', $qty, $variantId);
        $stmt->execute();
        $stmt->close();
        return;
    }

    $sql = "UPDATE products SET stock_qty = stock_qty {$operator} ? WHERE id = ?";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param('ii', $qty, $productId);
    $stmt->execute();
    $stmt->close();
}

function order_insert_status_log(mysqli $conn, int $orderId, ?int $adminId, ?string $oldStatus, string $newStatus, ?string $notes = null): void
{
    $stmt = $conn->prepare("INSERT INTO order_status_logs (order_id, admin_id, old_status, new_status, notes)
                            VALUES (?, ?, ?, ?, ?)");
    $stmt->bind_param('iisss', $orderId, $adminId, $oldStatus, $newStatus, $notes);
    $stmt->execute();
    $stmt->close();
}

function order_create(mysqli $conn, array $payload, ?int $adminId = null): array
{
    $errors = [];
    $payload = order_normalize_customer_payload($conn, $payload, $errors);
    [$items, $itemErrors] = order_validate_items($conn, $payload['items'] ?? []);
    $errors = array_merge($errors, $itemErrors);
    if (!$items) {
        $errors[] = 'لا توجد منتجات صالحة داخل الطلب.';
    }
    if ($errors) {
        return [false, $errors, null];
    }

    $promoId = (int) ($payload['promo_code_id'] ?? 0);
    $promo = order_find_promo($conn, $promoId);

    $originalSubtotal = 0.0;
    $subtotal = 0.0;
    $offersDiscountValue = 0.0;
    foreach ($items as $item) {
        $originalSubtotal += (float)($item['original_line_total'] ?? $item['line_total']);
        $subtotal += (float)$item['line_total'];
        $offersDiscountValue += (float)($item['line_discount_value'] ?? 0);
    }

    $promotionSettings = order_promotion_settings($conn);
    $provinceId = order_province_id_by_name($conn, (string)($payload['province'] ?? ''));
    $selectedAddons = order_validate_selected_addons($conn, (array)($payload['addon_ids'] ?? []), $provinceId, $errors);
    if ($errors) {
        return [false, $errors, null];
    }
    $addonsTotal = order_addons_total($selectedAddons);
    $thresholdRules = $promotionSettings['threshold_enabled'] ? order_fetch_threshold_promotions($conn, $provinceId) : [];
    $thresholdApplied = ['discount' => 0.0, 'titles' => []];
    if (!$promo || $promotionSettings['allow_special_with_promo']) {
        $thresholdApplied = order_apply_threshold_special_price($items, $thresholdRules, $subtotal);
    }
    $thresholdDiscountValue = (float)$thresholdApplied['discount'];
    $subtotalAfterThreshold = max(0.0, $subtotal - $thresholdDiscountValue);

    $promoDiscountOfferFirst = order_calculate_discount($subtotalAfterThreshold, $promo);
    $promoDiscountPromoFirst = order_calculate_discount($originalSubtotal, $promo);
    if ($promotionSettings['priority'] === 'promo_first') {
        $promoDiscountValue = $promoDiscountPromoFirst;
    } elseif ($promotionSettings['priority'] === 'best_discount' && $promoDiscountPromoFirst > $promoDiscountOfferFirst) {
        $promoDiscountValue = $promoDiscountPromoFirst;
    } else {
        $promoDiscountValue = $promoDiscountOfferFirst;
    }

    $customerId = !empty($payload['customer_id']) ? (int) $payload['customer_id'] : 0;
    if ($promo && !order_promo_allowed_for_customer($conn, $promo, $customerId)) {
        return [false, [order_promo_customer_error_message($conn, $promo)], null];
    }
    $loyaltySettings = order_loyalty_settings($conn);
    $availablePoints = order_customer_points_balance($conn, $customerId);
    $loyalty = order_loyalty_prepare($loyaltySettings, $availablePoints, $subtotalAfterThreshold, $promoDiscountValue, $payload, ($offersDiscountValue + $thresholdDiscountValue) > 0, (bool)$promo, $errors);
    if ($errors) {
        return [false, $errors, null];
    }

    $deliveryFee = (float) ($payload['delivery_fee'] ?? 0);
    $deliveryFeeBase = $deliveryFee;
    $freeDeliveryDiscountValue = 0.0;
    $freeRule = order_pick_free_delivery_rule($thresholdRules, $subtotalAfterThreshold);
    if (!$freeRule && $promotionSettings['free_delivery_min'] > 0 && $subtotalAfterThreshold >= $promotionSettings['free_delivery_min']) {
        $freeRule = ['title' => 'شحن مجاني تلقائي'];
    }
    if ($freeRule && ($promotionSettings['allow_free_delivery_with_promo'] || !$promo)) {
        $freeDeliveryDiscountValue = $deliveryFeeBase;
        $deliveryFee = 0.0;
    }

    $depositAmount = (float) ($payload['deposit_amount'] ?? 0);
    $total = order_round_iqd(max(0, $subtotalAfterThreshold - $promoDiscountValue - (float)$loyalty['discount_value'] + $deliveryFee + $addonsTotal));

    $paymentMethodId = (int) ($payload['payment_method_id'] ?? 0);
    $paymentStatus = trim((string) ($payload['payment_status'] ?? 'unpaid'));
    $orderStatus = trim((string) ($payload['order_status'] ?? 'pending'));
    $orderType = trim((string) ($payload['order_type'] ?? 'delivery'));

    $validOrderTypes = array_keys(order_type_options());
    if (!in_array($orderType, $validOrderTypes, true)) {
        return [false, ['نوع الطلب غير صالح.'], null];
    }

    $validOrderStatuses = array_keys(order_status_options());
    if (!in_array($orderStatus, $validOrderStatuses, true)) {
        $orderStatus = 'pending';
    }

    $validPaymentStatuses = array_keys(payment_status_options());
    if (!in_array($paymentStatus, $validPaymentStatuses, true)) {
        $paymentStatus = 'unpaid';
    }

    $customerName = trim((string) ($payload['customer_name'] ?? ''));
    $customerPhone = trim((string) ($payload['customer_phone'] ?? ''));
    $fullAddress = trim((string) ($payload['full_address'] ?? ''));

    if ($customerName === '' || $customerPhone === '') {
        return [false, ['اسم الزبون ورقم الهاتف مطلوبات.'], null];
    }

    if ($orderType === 'delivery' && $fullAddress === '') {
        return [false, ['العنوان الكامل مطلوب لطلب التوصيل.'], null];
    }

    $stockDeducted = order_status_requires_stock($orderStatus) ? 1 : 0;
    $deliveredAt = order_status_awards_loyalty($orderStatus) ? date('Y-m-d H:i:s') : null;
    $cancelledAt = order_status_is_cancel_like($orderStatus) ? date('Y-m-d H:i:s') : null;

    $conn->begin_transaction();
    try {
        $orderNumber = order_generate_number($conn);
        $customerId = $customerId ?: null;
        $addressId = !empty($payload['customer_address_id']) ? (int) $payload['customer_address_id'] : null;
        $promoCodeId = $promo ? (int) $promo['id'] : null;
        $customerEmail = trim((string) ($payload['customer_email'] ?? '')) ?: null;
        $province = trim((string) ($payload['province'] ?? '')) ?: null;
        $city = trim((string) ($payload['city'] ?? '')) ?: null;
        $area = trim((string) ($payload['area'] ?? '')) ?: null;
        $notes = trim((string) ($payload['notes'] ?? '')) ?: null;
        $adminNotes = trim((string) ($payload['admin_notes'] ?? '')) ?: null;
        $pickupBranch = trim((string) ($payload['pickup_branch'] ?? '')) ?: null;
        $preorderExpectedAt = trim((string) ($payload['preorder_expected_at'] ?? '')) ?: null;

        $stmt = $conn->prepare(
            "INSERT INTO orders (
                order_number, customer_id, customer_address_id, payment_method_id, promo_code_id,
                customer_name, customer_phone, customer_email, province, city, area, full_address,
                subtotal, discount_type, discount_value, delivery_fee, total_amount,
                payment_status, order_status, notes, admin_notes, stock_deducted,
                order_type, pickup_branch, preorder_expected_at, deposit_amount
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        );

        $discountType = $promo['discount_type'] ?? null;
        $finalSubtotal = max(0, $subtotalAfterThreshold - $promoDiscountValue - (float)$loyalty['discount_value']);
        $stmt->bind_param(
            'siiiisssssssdsdddssssisssd',
            $orderNumber,
            $customerId,
            $addressId,
            $paymentMethodId,
            $promoCodeId,
            $customerName,
            $customerPhone,
            $customerEmail,
            $province,
            $city,
            $area,
            $fullAddress,
            $finalSubtotal,
            $discountType,
            $promoDiscountValue,
            $deliveryFee,
            $total,
            $paymentStatus,
            $orderStatus,
            $notes,
            $adminNotes,
            $stockDeducted,
            $orderType,
            $pickupBranch,
            $preorderExpectedAt,
            $depositAmount
        );
        $stmt->execute();
        $orderId = (int) $stmt->insert_id;
        $stmt->close();

        $itemStmt = $conn->prepare(
            "INSERT INTO order_items (
                order_id, product_id, variant_id, product_name, product_sku,
                unit_price, quantity, line_total, product_image
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
        );

        foreach ($items as $item) {
            $variantId = $item['variant_id'] ?: null;
            $itemStmt->bind_param(
                'iiissdids',
                $orderId,
                $item['product_id'],
                $variantId,
                $item['product_name'],
                $item['product_sku'],
                $item['unit_price'],
                $item['quantity'],
                $item['line_total'],
                $item['product_image']
            );
            $itemStmt->execute();
            $orderItemId = (int)$itemStmt->insert_id;
            if ($orderItemId > 0) {
                $extraCols = [];
                $extraVals = [];
                if (order_table_has_column($conn, 'order_items', 'original_unit_price')) { $extraCols[] = "original_unit_price = ?"; $extraVals[] = (float)($item['original_unit_price'] ?? $item['unit_price']); }
                if (order_table_has_column($conn, 'order_items', 'discounted_unit_price')) { $extraCols[] = "discounted_unit_price = ?"; $extraVals[] = (float)$item['unit_price']; }
                if (order_table_has_column($conn, 'order_items', 'original_line_total')) { $extraCols[] = "original_line_total = ?"; $extraVals[] = (float)($item['original_line_total'] ?? $item['line_total']); }
                if (order_table_has_column($conn, 'order_items', 'line_discount_value')) { $extraCols[] = "line_discount_value = ?"; $extraVals[] = (float)($item['line_discount_value'] ?? 0); }
                if (order_table_has_column($conn, 'order_items', 'applied_offer_type')) { $extraCols[] = "applied_offer_type = ?"; $extraVals[] = (string)($item['applied_offer_type'] ?? ''); }
                if (order_table_has_column($conn, 'order_items', 'applied_offer_title')) { $extraCols[] = "applied_offer_title = ?"; $extraVals[] = (string)($item['applied_offer_title'] ?? ''); }
                if ($extraCols) {
                    $types = '';
                    foreach ($extraVals as $val) {
                        $types .= is_float($val) || is_int($val) ? 'd' : 's';
                    }
                    $types .= 'i';
                    $sqlExtra = "UPDATE order_items SET " . implode(', ', $extraCols) . " WHERE id = ?";
                    $stmtExtra = $conn->prepare($sqlExtra);
                    $bind = array_merge($extraVals, [$orderItemId]);
                    $stmtExtra->bind_param($types, ...$bind);
                    $stmtExtra->execute();
                    $stmtExtra->close();
                }
            }
            if ($stockDeducted === 1) {
                order_apply_stock_change($conn, (int) $item['product_id'], $variantId ? (int) $variantId : null, (int) $item['quantity'], 'deduct', $orderId, $adminId);
            }
        }
        $itemStmt->close();

        $influencerId = !empty($promo['influencer_id']) ? (int)$promo['influencer_id'] : null;
        $influencerCode = !empty($promo['code']) ? (string)$promo['code'] : null;
        order_update_row_if_columns_exist($conn, 'orders', $orderId, [
            'original_subtotal' => $originalSubtotal,
            'offers_discount_value' => $offersDiscountValue,
            'threshold_discount_value' => $thresholdDiscountValue,
            'promo_discount_value' => $promoDiscountValue,
            'free_delivery_discount_value' => $freeDeliveryDiscountValue,
            'loyalty_discount_value' => (float)$loyalty['discount_value'],
            'loyalty_points_redeemed' => (int)$loyalty['points_redeemed'],
            'loyalty_points_earned' => (int)$loyalty['earned_points'],
            'loyalty_redemption_type' => (string)$loyalty['redemption_type'],
            'loyalty_reward_title' => (string)$loyalty['reward_title'],
            'loyalty_points_awarded' => 0,
            'delivered_at' => $deliveredAt,
            'cancelled_at' => $cancelledAt,
            'returned_at' => null,
            'influencer_id' => $influencerId,
            'influencer_code' => $influencerCode,
        ]);

        if ($promo) {
            $usageStmt = $conn->prepare("INSERT INTO promo_code_usage (promo_code_id, customer_id, order_id, used_code, discount_value)
                                         VALUES (?, ?, ?, ?, ?)");
            $usedCode = $promo['code'];
            $usageStmt->bind_param('iiisd', $promoCodeId, $customerId, $orderId, $usedCode, $promoDiscountValue);
            $usageStmt->execute();
            $usageStmt->close();
            $conn->query("UPDATE promo_codes SET used_count = used_count + 1 WHERE id = " . (int) $promoCodeId);
        }

        if ($paymentMethodId > 0) {
            $payStmt = $conn->prepare("INSERT INTO payments (order_id, payment_method_id, amount, payment_status, transaction_reference, notes)
                                       VALUES (?, ?, ?, ?, ?, ?)");
            $transactionReference = trim((string) ($payload['transaction_reference'] ?? '')) ?: null;
            $paymentNotes = trim((string) ($payload['payment_notes'] ?? '')) ?: null;
            $paymentState = $paymentStatus === 'paid' ? 'confirmed' : 'pending';
            $payStmt->bind_param('iidsss', $orderId, $paymentMethodId, $total, $paymentState, $transactionReference, $paymentNotes);
            $payStmt->execute();
            $payStmt->close();
        }

        order_insert_status_log($conn, $orderId, $adminId, null, $orderStatus, 'تم إنشاء الطلب');
        $afterOrder = [
            'id' => $orderId,
            'customer_id' => $customerId,
            'order_status' => $orderStatus,
            'loyalty_points_redeemed' => (int)$loyalty['points_redeemed'],
            'loyalty_points_earned' => (int)$loyalty['earned_points'],
            'loyalty_redemption_type' => (string)$loyalty['redemption_type'],
            'loyalty_reward_title' => (string)$loyalty['reward_title'],
        ];
        order_loyalty_sync_for_order($conn, null, $afterOrder, $adminId);
        order_tier_sync_for_order($conn, null, $afterOrder, $adminId);
        $conn->commit();
        return [true, [], $orderId];
    } catch (Throwable $e) {
        $conn->rollback();
        return [false, ['فشل إنشاء الطلب: ' . $e->getMessage()], null];
    }
}

function order_get_by_id(mysqli $conn, int $id): ?array
{
    $stmt = $conn->prepare(
        "SELECT o.*, pm.name AS payment_method_name, pm.code AS payment_method_code, pc.code AS promo_code_text
         FROM orders o
         LEFT JOIN payment_methods pm ON pm.id = o.payment_method_id
         LEFT JOIN promo_codes pc ON pc.id = o.promo_code_id
         WHERE o.id = ?
         LIMIT 1"
    );
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $order = $stmt->get_result()->fetch_assoc() ?: null;
    $stmt->close();

    if (!$order) {
        return null;
    }

    $itemStmt = $conn->prepare("SELECT * FROM order_items WHERE order_id = ? ORDER BY id ASC");
    $itemStmt->bind_param('i', $id);
    $itemStmt->execute();
    $itemsRes = $itemStmt->get_result();
    $order['items'] = [];
    while ($row = $itemsRes->fetch_assoc()) {
        $order['items'][] = $row;
    }
    $itemStmt->close();
    $order['items'] = order_fill_items_missing_images($conn, $order['items']);

    $order['addons'] = order_get_order_addons($conn, $id);

    $logStmt = $conn->prepare("SELECT osl.*, a.full_name AS admin_name
                               FROM order_status_logs osl
                               LEFT JOIN admins a ON a.id = osl.admin_id
                               WHERE osl.order_id = ?
                               ORDER BY osl.id DESC");
    $logStmt->bind_param('i', $id);
    $logStmt->execute();
    $logsRes = $logStmt->get_result();
    $order['logs'] = [];
    while ($row = $logsRes->fetch_assoc()) {
        $order['logs'][] = $row;
    }
    $logStmt->close();

    $order['order_status'] = order_normalize_status((string)($order['order_status'] ?? 'pending'));
    foreach ($order['logs'] as &$logRow) {
        $logRow['old_status'] = order_normalize_status((string)($logRow['old_status'] ?? ''));
        $logRow['new_status'] = order_normalize_status((string)($logRow['new_status'] ?? 'pending'));
    }
    unset($logRow);

    return $order;
}

function order_update(mysqli $conn, int $orderId, array $payload, ?int $adminId = null): array
{
    $existing = order_get_by_id($conn, $orderId);
    if (!$existing) {
        return [false, ['الطلب غير موجود.']];
    }

    $errors = [];
    $payload = order_normalize_customer_payload($conn, $payload, $errors);
    $oldStatus = (string)$existing['order_status'];
    $oldStockDeducted = ((int)($existing['stock_deducted'] ?? 0) === 1);
    $legacyReservedStock = (!$oldStockDeducted && order_status_requires_stock($oldStatus));
    $stockBoosts = ($oldStockDeducted || $legacyReservedStock)
        ? order_stock_boosts_from_items((array)($existing['items'] ?? []))
        : [];
    [$items, $itemErrors] = order_validate_items($conn, $payload['items'] ?? [], $stockBoosts);
    $errors = array_merge($errors, $itemErrors);
    if (!$items) {
        $errors[] = 'لا توجد منتجات صالحة داخل الطلب.';
    }
    if ($errors) {
        return [false, $errors];
    }

    $promoId = (int) ($payload['promo_code_id'] ?? 0);
    $promo = order_find_promo($conn, $promoId);
    $originalSubtotal = 0.0;
    $subtotal = 0.0;
    $offersDiscountValue = 0.0;
    foreach ($items as $item) {
        $originalSubtotal += (float)($item['original_line_total'] ?? $item['line_total']);
        $subtotal += (float)$item['line_total'];
        $offersDiscountValue += (float)($item['line_discount_value'] ?? 0);
    }
    $promotionSettings = order_promotion_settings($conn);
    $provinceId = order_province_id_by_name($conn, (string)($payload['province'] ?? ($existing['province'] ?? '')));
    $thresholdRules = $promotionSettings['threshold_enabled'] ? order_fetch_threshold_promotions($conn, $provinceId) : [];
    $thresholdApplied = ['discount' => 0.0, 'titles' => []];
    if (!$promo || $promotionSettings['allow_special_with_promo']) {
        $thresholdApplied = order_apply_threshold_special_price($items, $thresholdRules, $subtotal);
    }
    $thresholdDiscountValue = (float)$thresholdApplied['discount'];
    $subtotalAfterThreshold = max(0.0, $subtotal - $thresholdDiscountValue);
    $promoDiscountOfferFirst = order_calculate_discount($subtotalAfterThreshold, $promo);
    $promoDiscountPromoFirst = order_calculate_discount($originalSubtotal, $promo);
    if ($promotionSettings['priority'] === 'promo_first') {
        $promoDiscountValue = $promoDiscountPromoFirst;
    } elseif ($promotionSettings['priority'] === 'best_discount' && $promoDiscountPromoFirst > $promoDiscountOfferFirst) {
        $promoDiscountValue = $promoDiscountPromoFirst;
    } else {
        $promoDiscountValue = $promoDiscountOfferFirst;
    }

    $newCustomerId = !empty($payload['customer_id']) ? (int)$payload['customer_id'] : 0;
    if ($promo && !order_promo_allowed_for_customer($conn, $promo, $newCustomerId)) {
        return [false, [order_promo_customer_error_message($conn, $promo)]];
    }
    $existingRedeemed = (int)($existing['loyalty_points_redeemed'] ?? 0);
    $availablePoints = order_customer_points_balance($conn, $newCustomerId);
    if ($newCustomerId > 0 && $newCustomerId === (int)($existing['customer_id'] ?? 0) && !order_status_is_cancel_like((string)$existing['order_status'])) {
        $availablePoints += $existingRedeemed;
    }
    if (!array_key_exists('use_loyalty_points', $payload) && !array_key_exists('loyalty_points_to_redeem', $payload)) {
        $payload['use_loyalty_points'] = ((int)($existing['loyalty_points_redeemed'] ?? 0) > 0) ? 1 : 0;
        $payload['loyalty_points_to_redeem'] = (int)($existing['loyalty_points_redeemed'] ?? 0);
    }
    $loyaltySettings = order_loyalty_settings($conn);
    $loyalty = order_loyalty_prepare($loyaltySettings, $availablePoints, $subtotalAfterThreshold, $promoDiscountValue, $payload, ($offersDiscountValue + $thresholdDiscountValue) > 0, (bool)$promo, $errors);
    if ($errors) {
        return [false, $errors];
    }

    $deliveryFee = (float) ($payload['delivery_fee'] ?? 0);
    $deliveryFeeBase = $deliveryFee;
    $freeDeliveryDiscountValue = 0.0;
    $freeRule = order_pick_free_delivery_rule($thresholdRules, $subtotalAfterThreshold);
    if (!$freeRule && $promotionSettings['free_delivery_min'] > 0 && $subtotalAfterThreshold >= $promotionSettings['free_delivery_min']) {
        $freeRule = ['title' => 'شحن مجاني تلقائي'];
    }
    if ($freeRule && ($promotionSettings['allow_free_delivery_with_promo'] || !$promo)) {
        $freeDeliveryDiscountValue = $deliveryFeeBase;
        $deliveryFee = 0.0;
    }
    $depositAmount = (float) ($payload['deposit_amount'] ?? 0);
    $total = order_round_iqd(max(0, $subtotalAfterThreshold - $promoDiscountValue - (float)$loyalty['discount_value'] + $deliveryFee + $addonsTotal));

    $newStatus = trim((string) ($payload['order_status'] ?? $existing['order_status']));
    $newStockDeducted = order_status_requires_stock($newStatus);

    $validPaymentStatuses = array_keys(payment_status_options());
    $paymentStatus = trim((string) ($payload['payment_status'] ?? 'unpaid'));
    if (!in_array($paymentStatus, $validPaymentStatuses, true)) {
        $paymentStatus = 'unpaid';
    }

    $conn->begin_transaction();
    try {
        if ($oldStockDeducted) {
            foreach ($existing['items'] as $oldItem) {
                order_apply_stock_change($conn, (int) $oldItem['product_id'], $oldItem['variant_id'] ? (int)$oldItem['variant_id'] : null, (int) $oldItem['quantity'], 'restore', $orderId, $adminId);
            }
        }

        $conn->query('DELETE FROM order_items WHERE order_id = ' . $orderId);
        $itemStmt = $conn->prepare(
            "INSERT INTO order_items (
                order_id, product_id, variant_id, product_name, product_sku,
                unit_price, quantity, line_total, product_image
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"
        );
        foreach ($items as $item) {
            $variantId = $item['variant_id'] ?: null;
            $itemStmt->bind_param(
                'iiissdids',
                $orderId,
                $item['product_id'],
                $variantId,
                $item['product_name'],
                $item['product_sku'],
                $item['unit_price'],
                $item['quantity'],
                $item['line_total'],
                $item['product_image']
            );
            $itemStmt->execute();
            $orderItemId = (int)$itemStmt->insert_id;
            if ($orderItemId > 0) {
                $extraCols = [];
                $extraVals = [];
                if (order_table_has_column($conn, 'order_items', 'original_unit_price')) { $extraCols[] = "original_unit_price = ?"; $extraVals[] = (float)($item['original_unit_price'] ?? $item['unit_price']); }
                if (order_table_has_column($conn, 'order_items', 'discounted_unit_price')) { $extraCols[] = "discounted_unit_price = ?"; $extraVals[] = (float)$item['unit_price']; }
                if (order_table_has_column($conn, 'order_items', 'original_line_total')) { $extraCols[] = "original_line_total = ?"; $extraVals[] = (float)($item['original_line_total'] ?? $item['line_total']); }
                if (order_table_has_column($conn, 'order_items', 'line_discount_value')) { $extraCols[] = "line_discount_value = ?"; $extraVals[] = (float)($item['line_discount_value'] ?? 0); }
                if (order_table_has_column($conn, 'order_items', 'applied_offer_type')) { $extraCols[] = "applied_offer_type = ?"; $extraVals[] = (string)($item['applied_offer_type'] ?? ''); }
                if (order_table_has_column($conn, 'order_items', 'applied_offer_title')) { $extraCols[] = "applied_offer_title = ?"; $extraVals[] = (string)($item['applied_offer_title'] ?? ''); }
                if ($extraCols) {
                    $types = '';
                    foreach ($extraVals as $val) {
                        $types .= is_float($val) || is_int($val) ? 'd' : 's';
                    }
                    $types .= 'i';
                    $sqlExtra = "UPDATE order_items SET " . implode(', ', $extraCols) . " WHERE id = ?";
                    $stmtExtra = $conn->prepare($sqlExtra);
                    $bind = array_merge($extraVals, [$orderItemId]);
                    $stmtExtra->bind_param($types, ...$bind);
                    $stmtExtra->execute();
                    $stmtExtra->close();
                }
            }
        }
        $itemStmt->close();

        if ($newStockDeducted && !$oldStockDeducted && !$legacyReservedStock) {
            foreach ($items as $item) {
                order_apply_stock_change($conn, (int) $item['product_id'], $item['variant_id'] ? (int)$item['variant_id'] : null, (int) $item['quantity'], 'deduct', $orderId, $adminId);
            }
        } elseif ($newStockDeducted && $oldStockDeducted) {
            foreach ($items as $item) {
                order_apply_stock_change($conn, (int) $item['product_id'], $item['variant_id'] ? (int)$item['variant_id'] : null, (int) $item['quantity'], 'deduct', $orderId, $adminId);
            }
        }

        $paymentMethodId = (int) ($payload['payment_method_id'] ?? 0);
        $discountType = $promo['discount_type'] ?? null;
        $customerId = $newCustomerId ?: null;
        $addressId = !empty($payload['customer_address_id']) ? (int) $payload['customer_address_id'] : null;
        $promoCodeId = $promo ? (int) $promo['id'] : null;
        $customerName = trim((string) ($payload['customer_name'] ?? ''));
        $customerPhone = trim((string) ($payload['customer_phone'] ?? ''));
        $customerEmail = trim((string) ($payload['customer_email'] ?? '')) ?: null;
        $province = trim((string) ($payload['province'] ?? '')) ?: null;
        $city = trim((string) ($payload['city'] ?? '')) ?: null;
        $area = trim((string) ($payload['area'] ?? '')) ?: null;
        $fullAddress = trim((string) ($payload['full_address'] ?? ''));
        $notes = trim((string) ($payload['notes'] ?? '')) ?: null;
        $adminNotes = trim((string) ($payload['admin_notes'] ?? '')) ?: null;
        $orderType = trim((string) ($payload['order_type'] ?? 'delivery'));
        $pickupBranch = trim((string) ($payload['pickup_branch'] ?? '')) ?: null;
        $preorderExpectedAt = trim((string) ($payload['preorder_expected_at'] ?? '')) ?: null;
        $deliveredAt = order_status_awards_loyalty($newStatus)
            ? (($oldStatus === 'delivered' && !empty($existing['delivered_at'])) ? $existing['delivered_at'] : date('Y-m-d H:i:s'))
            : null;
        $cancelledAt = order_status_is_cancel_like($newStatus)
            ? ((order_status_is_cancel_like($oldStatus) && !empty($existing['cancelled_at'])) ? $existing['cancelled_at'] : date('Y-m-d H:i:s'))
            : null;
        $returnedAt = null;
        $finalSubtotal = max(0, $subtotalAfterThreshold - $promoDiscountValue - (float)$loyalty['discount_value']);

        $stmt = $conn->prepare(
            "UPDATE orders SET
                customer_id = ?, customer_address_id = ?, payment_method_id = ?, promo_code_id = ?,
                customer_name = ?, customer_phone = ?, customer_email = ?, province = ?, city = ?, area = ?, full_address = ?,
                subtotal = ?, discount_type = ?, discount_value = ?, delivery_fee = ?, total_amount = ?,
                payment_status = ?, order_status = ?, notes = ?, admin_notes = ?, stock_deducted = ?,
                order_type = ?, pickup_branch = ?, preorder_expected_at = ?, deposit_amount = ?,
                cancelled_at = ?, delivered_at = ?, returned_at = ?
             WHERE id = ?"
        );
        $stockFlag = $newStockDeducted ? 1 : 0;
        $stmt->bind_param(
            'iiiisssssssdsdddssssisssdsssi',
            $customerId,
            $addressId,
            $paymentMethodId,
            $promoCodeId,
            $customerName,
            $customerPhone,
            $customerEmail,
            $province,
            $city,
            $area,
            $fullAddress,
            $finalSubtotal,
            $discountType,
            $promoDiscountValue,
            $deliveryFee,
            $total,
            $paymentStatus,
            $newStatus,
            $notes,
            $adminNotes,
            $stockFlag,
            $orderType,
            $pickupBranch,
            $preorderExpectedAt,
            $depositAmount,
            $cancelledAt,
            $deliveredAt,
            $returnedAt,
            $orderId
        );
        $stmt->execute();
        $stmt->close();

        $influencerId = !empty($promo['influencer_id']) ? (int)$promo['influencer_id'] : null;
        $influencerCode = !empty($promo['code']) ? (string)$promo['code'] : null;
        $selectedAddons = is_array($selectedAddons ?? null) ? $selectedAddons : [];
        order_update_row_if_columns_exist($conn, 'orders', $orderId, [
            'original_subtotal' => $originalSubtotal,
            'offers_discount_value' => $offersDiscountValue,
            'threshold_discount_value' => $thresholdDiscountValue,
            'promo_discount_value' => $promoDiscountValue,
            'free_delivery_discount_value' => $freeDeliveryDiscountValue,
            'addons_total' => $addonsTotal,
            'loyalty_discount_value' => (float)$loyalty['discount_value'],
            'loyalty_points_redeemed' => (int)$loyalty['points_redeemed'],
            'loyalty_points_earned' => (int)$loyalty['earned_points'],
            'loyalty_redemption_type' => (string)$loyalty['redemption_type'],
            'loyalty_reward_title' => (string)$loyalty['reward_title'],
            'pricing_breakdown' => order_pricing_breakdown_json([
                'threshold_titles' => $thresholdApplied['titles'] ?? [],
                'free_delivery_title' => $freeRule['title'] ?? '',
                'addons' => array_map(static fn($a) => ['title' => (string)($a['title'] ?? ''), 'fee' => (float)($a['fee'] ?? 0)], $selectedAddons),
            ]),
            'influencer_id' => $influencerId,
            'influencer_code' => $influencerCode,
        ]);
        order_save_order_addons($conn, $orderId, $selectedAddons);

        $afterOrder = [
            'id' => $orderId,
            'customer_id' => $customerId,
            'order_status' => $newStatus,
            'loyalty_points_redeemed' => (int)$loyalty['points_redeemed'],
            'loyalty_points_earned' => (int)$loyalty['earned_points'],
            'loyalty_redemption_type' => (string)$loyalty['redemption_type'],
            'loyalty_reward_title' => (string)$loyalty['reward_title'],
        ];
        order_loyalty_sync_for_order($conn, $existing, $afterOrder, $adminId);
        $afterOrder['tier_points_awarded'] = (int)($existing['tier_points_awarded'] ?? 0);
        $afterOrder['delivered_at'] = $deliveredAt;
        order_tier_sync_for_order($conn, $existing, $afterOrder, $adminId);

        order_insert_status_log($conn, $orderId, $adminId, $oldStatus, $newStatus, 'تم تحديث الطلب');
        $conn->commit();
        return [true, []];
    } catch (Throwable $e) {
        $conn->rollback();
        return [false, ['فشل تحديث الطلب: ' . $e->getMessage()]];
    }
}

function order_cancel(mysqli $conn, int $orderId, ?int $adminId = null, ?string $notes = null): array
{
    $order = order_get_by_id($conn, $orderId);
    if (!$order) {
        return [false, ['الطلب غير موجود.']];
    }
    if (order_status_is_cancel_like((string)$order['order_status'])) {
        return [true, []];
    }

    $conn->begin_transaction();
    try {
        if ((int) $order['stock_deducted'] === 1) {
            foreach ($order['items'] as $item) {
                order_apply_stock_change($conn, (int) $item['product_id'], $item['variant_id'] ? (int)$item['variant_id'] : null, (int) $item['quantity'], 'restore', $orderId, $adminId);
            }
        }

        $stmt = $conn->prepare("UPDATE orders SET order_status = 'cancelled', stock_deducted = 0, cancelled_at = NOW() WHERE id = ?");
        $stmt->bind_param('i', $orderId);
        $stmt->execute();
        $stmt->close();

        if ((int)($order['loyalty_points_redeemed'] ?? 0) > 0 && !empty($order['customer_id']) && !order_status_is_cancel_like((string)$order['order_status'])) {
            order_loyalty_adjust_points($conn, (int)$order['customer_id'], (int)$order['loyalty_points_redeemed'], $orderId, 'redeem_restore', $adminId, (string)($order['loyalty_redemption_type'] ?? 'amount'), (string)($order['loyalty_reward_title'] ?? ''), 'إرجاع نقاط الطلب الملغي');
        }
        if ((int)($order['loyalty_points_awarded'] ?? 0) === 1 && (int)($order['loyalty_points_earned'] ?? 0) > 0 && !empty($order['customer_id'])) {
            order_loyalty_adjust_points($conn, (int)$order['customer_id'], -1 * (int)$order['loyalty_points_earned'], $orderId, 'earn_reverse', $adminId, 'amount', '', 'سحب نقاط مكتسبة بسبب إلغاء الطلب');
        }
        order_update_row_if_columns_exist($conn, 'orders', $orderId, ['loyalty_points_awarded' => 0]);

        order_insert_status_log($conn, $orderId, $adminId, $order['order_status'], 'cancelled', $notes ?: 'تم إلغاء الطلب');
        $conn->commit();
        return [true, []];
    } catch (Throwable $e) {
        $conn->rollback();
        return [false, ['فشل إلغاء الطلب: ' . $e->getMessage()]];
    }
}

function order_stats(mysqli $conn): array
{
    $stats = [
        'all' => 0,
        'pending' => 0,
        'ready' => 0,
        'delivered' => 0,
        'cancelled' => 0,
        'postponed' => 0,
        'delivery' => 0,
        'pickup' => 0,
        'preorder' => 0,
        'sales' => 0,
    ];

    if ($res = $conn->query("SELECT " . order_status_sql_clause('order_status') . " AS normalized_status, COUNT(*) AS c FROM orders GROUP BY normalized_status")) {
        while ($row = $res->fetch_assoc()) {
            $key = (string)($row['normalized_status'] ?? '');
            if (array_key_exists($key, $stats)) {
                $stats[$key] = (int)($row['c'] ?? 0);
            }
            $stats['all'] += (int)($row['c'] ?? 0);
        }
        $res->close();
    }

    foreach (['delivery', 'pickup', 'preorder'] as $type) {
        $stmt = $conn->prepare("SELECT COUNT(*) AS c FROM orders WHERE order_type = ?");
        $stmt->bind_param('s', $type);
        $stmt->execute();
        $row = $stmt->get_result()->fetch_assoc() ?: [];
        $stats[$type] = (int)($row['c'] ?? 0);
        $stmt->close();
    }

    if ($res = $conn->query("SELECT COALESCE(SUM(total_amount), 0) AS c FROM orders WHERE " . order_status_sql_clause('order_status') . " = 'delivered'")) {
        $row = $res->fetch_assoc() ?: [];
        $stats['sales'] = (float)($row['c'] ?? 0);
        $res->close();
    }

    return $stats;
}

function order_build_list_filters(array $filters = []): array
{
    $where = ['1=1'];
    $params = [];
    $types = '';
    $statusExpr = order_status_sql_clause('o.order_status');

    $quick = trim((string)($filters['quick'] ?? ''));
    if ($quick === 'needs_payment') {
        $where[] = "o.payment_status IN ('pending_confirmation','unpaid')";
        $where[] = $statusExpr . " NOT IN ('delivered','cancelled')";
    } elseif ($quick === 'needs_processing') {
        $where[] = $statusExpr . " = 'pending'";
    } elseif ($quick === 'postponed') {
        $where[] = $statusExpr . " = 'postponed'";
    } elseif ($quick === 'ready') {
        $where[] = $statusExpr . " = 'ready'";
    } elseif ($quick === 'preorder') {
        $where[] = "o.order_type = 'preorder'";
    } elseif ($quick === 'today') {
        $where[] = 'DATE(o.created_at) = CURDATE()';
    }

    if (!empty($filters['status'])) {
        $where[] = $statusExpr . ' = ?';
        $params[] = order_normalize_status((string)$filters['status']);
        $types .= 's';
    }
    if (!empty($filters['payment_status'])) {
        $where[] = 'o.payment_status = ?';
        $params[] = $filters['payment_status'];
        $types .= 's';
    }
    if (!empty($filters['order_type'])) {
        $where[] = 'o.order_type = ?';
        $params[] = $filters['order_type'];
        $types .= 's';
    }
    if (!empty($filters['payment_method_id'])) {
        $where[] = 'o.payment_method_id = ?';
        $params[] = (int)$filters['payment_method_id'];
        $types .= 'i';
    }
    if (!empty($filters['province'])) {
        $where[] = 'o.province = ?';
        $params[] = trim((string)$filters['province']);
        $types .= 's';
    }
    if (!empty($filters['pickup_branch'])) {
        $where[] = 'o.pickup_branch = ?';
        $params[] = trim((string)$filters['pickup_branch']);
        $types .= 's';
    }
    if (!empty($filters['date_from'])) {
        $where[] = 'DATE(o.created_at) >= ?';
        $params[] = trim((string)$filters['date_from']);
        $types .= 's';
    }
    if (!empty($filters['date_to'])) {
        $where[] = 'DATE(o.created_at) <= ?';
        $params[] = trim((string)$filters['date_to']);
        $types .= 's';
    }
    if (!empty($filters['keyword'])) {
        $where[] = '(o.order_number LIKE ? OR o.customer_name LIKE ? OR o.customer_phone LIKE ? OR o.id LIKE ?)';
        $kw = '%' . trim((string)$filters['keyword']) . '%';
        $params[] = $kw;
        $params[] = $kw;
        $params[] = $kw;
        $params[] = $kw;
        $types .= 'ssss';
    }

    return [$where, $params, $types, $statusExpr];
}

function order_list_paginated(mysqli $conn, array $filters = [], int $page = 1, int $perPage = 20): array
{
    [$where, $params, $types, $statusExpr] = order_build_list_filters($filters);
    $page = max(1, $page);
    $perPage = max(1, min(100, $perPage));

    $countSql = "SELECT COUNT(*) AS c FROM orders o WHERE " . implode(' AND ', $where);
    $countStmt = $conn->prepare($countSql);
    if ($types !== '') {
        $countStmt->bind_param($types, ...$params);
    }
    $countStmt->execute();
    $total = (int)(($countStmt->get_result()->fetch_assoc()['c'] ?? 0));
    $countStmt->close();

    $pages = max(1, (int)ceil($total / $perPage));
    if ($page > $pages) {
        $page = $pages;
    }
    $offset = ($page - 1) * $perPage;

    $sql = "SELECT o.*, pm.name AS payment_method_name, pm.code AS payment_method_code,
                   COALESCE(pm.requires_manual_confirmation, 0) AS requires_manual_confirmation,
                   {$statusExpr} AS normalized_status,
                   (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.id) AS items_count
            FROM orders o
            LEFT JOIN payment_methods pm ON pm.id = o.payment_method_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY o.id DESC
            LIMIT ? OFFSET ?";

    $stmt = $conn->prepare($sql);
    $bindTypes = $types . 'ii';
    $bindParams = array_merge($params, [$perPage, $offset]);
    $stmt->bind_param($bindTypes, ...$bindParams);
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($row = $res->fetch_assoc()) {
        $row['order_status'] = order_normalize_status((string)($row['normalized_status'] ?? $row['order_status'] ?? 'pending'));
        $rows[] = $row;
    }
    $stmt->close();

    return [
        'items' => $rows,
        'total' => $total,
        'page' => $page,
        'per_page' => $perPage,
        'pages' => $pages,
    ];
}

function order_list(mysqli $conn, array $filters = []): array
{
    return order_list_paginated($conn, $filters, 1, 500)['items'];
}


function order_confirm_payment(mysqli $conn, int $orderId, ?int $adminId = null): array
{
    $order = order_get_by_id($conn, $orderId);
    if (!$order) {
        return [false, ['الطلب غير موجود.']];
    }
    if (!order_can_confirm_payment($order)) {
        return [false, ['هذا الطلب لا يحتاج تأكيد دفع حالياً.']];
    }

    $conn->begin_transaction();
    try {
        $stmt = $conn->prepare("UPDATE orders SET payment_status = 'paid', updated_at = NOW() WHERE id = ? LIMIT 1");
        $stmt->bind_param('i', $orderId);
        $stmt->execute();
        $stmt->close();

        if (order_table_exists($conn, 'payments')) {
            if (order_table_has_column($conn, 'payments', 'updated_at') && order_table_has_column($conn, 'payments', 'paid_at')) {
                $payStmt = $conn->prepare("UPDATE payments SET payment_status = 'confirmed', paid_at = COALESCE(paid_at, NOW()), updated_at = NOW() WHERE order_id = ?");
            } elseif (order_table_has_column($conn, 'payments', 'paid_at')) {
                $payStmt = $conn->prepare("UPDATE payments SET payment_status = 'confirmed', paid_at = COALESCE(paid_at, NOW()) WHERE order_id = ?");
            } elseif (order_table_has_column($conn, 'payments', 'updated_at')) {
                $payStmt = $conn->prepare("UPDATE payments SET payment_status = 'confirmed', updated_at = NOW() WHERE order_id = ?");
            } else {
                $payStmt = $conn->prepare("UPDATE payments SET payment_status = 'confirmed' WHERE order_id = ?");
            }
            if ($payStmt) {
                $payStmt->bind_param('i', $orderId);
                $payStmt->execute();
                $payStmt->close();
            }
        }

        order_insert_status_log($conn, $orderId, $adminId, (string)$order['order_status'], (string)$order['order_status'], 'تم تأكيد الدفع يدوياً من قائمة الطلبات');
        $conn->commit();
        return [true, []];
    } catch (Throwable $e) {
        $conn->rollback();
        return [false, ['فشل تأكيد الدفع: ' . $e->getMessage()]];
    }
}

function order_bulk_update_status(mysqli $conn, array $orderIds, string $newStatus, ?int $adminId = null): array
{
    $normalizedStatus = order_normalize_status($newStatus);
    if (!array_key_exists($normalizedStatus, order_status_options())) {
        return [false, ['حالة الطلب غير صالحة.']];
    }

    $orderIds = array_values(array_unique(array_filter(array_map('intval', $orderIds))));
    if (!$orderIds) {
        return [false, ['لا يوجد طلبات محددة.']];
    }

    $errors = [];
    $updated = 0;
    foreach ($orderIds as $orderId) {
        $order = order_get_by_id($conn, $orderId);
        if (!$order) {
            $errors[] = 'الطلب #' . $orderId . ' غير موجود.';
            continue;
        }
        $oldStatus = order_normalize_status((string)($order['order_status'] ?? 'pending'));
        if ($oldStatus === $normalizedStatus) {
            continue;
        }

        $oldRequiresStock = ((int)($order['stock_deducted'] ?? 0) === 1) || order_status_requires_stock($oldStatus);
        $newRequiresStock = order_status_requires_stock($normalizedStatus);

        if (!$oldRequiresStock && $newRequiresStock) {
            [$validatedItems, $itemErrors] = order_validate_items($conn, array_map(static fn(array $item) => [
                'product_id' => $item['product_id'] ?? 0,
                'variant_id' => $item['variant_id'] ?? 0,
                'quantity' => $item['quantity'] ?? 0,
            ], (array)($order['items'] ?? [])));
            if ($itemErrors) {
                $errors[] = 'الطلب #' . ($order['order_number'] ?? $orderId) . ': ' . implode(' | ', $itemErrors);
                continue;
            }
            $itemsForStock = $validatedItems;
        } else {
            $itemsForStock = (array)($order['items'] ?? []);
        }

        $conn->begin_transaction();
        try {
            if ($oldRequiresStock && !$newRequiresStock && (int)($order['stock_deducted'] ?? 0) === 1) {
                foreach ($order['items'] as $item) {
                    order_apply_stock_change($conn, (int)$item['product_id'], !empty($item['variant_id']) ? (int)$item['variant_id'] : null, (int)$item['quantity'], 'restore', $orderId, $adminId);
                }
            }
            if (!$oldRequiresStock && $newRequiresStock) {
                foreach ($itemsForStock as $item) {
                    order_apply_stock_change($conn, (int)$item['product_id'], !empty($item['variant_id']) ? (int)$item['variant_id'] : null, (int)$item['quantity'], 'deduct', $orderId, $adminId);
                }
            }

            $stockFlag = $newRequiresStock ? 1 : 0;
            $deliveredAt = order_status_awards_loyalty($normalizedStatus) ? (!empty($order['delivered_at']) ? $order['delivered_at'] : date('Y-m-d H:i:s')) : null;
            $cancelledAt = order_status_is_cancel_like($normalizedStatus) ? (!empty($order['cancelled_at']) ? $order['cancelled_at'] : date('Y-m-d H:i:s')) : null;

            $stmt = $conn->prepare("UPDATE orders SET order_status = ?, stock_deducted = ?, delivered_at = ?, cancelled_at = ?, returned_at = NULL WHERE id = ?");
            $stmt->bind_param('sissi', $normalizedStatus, $stockFlag, $deliveredAt, $cancelledAt, $orderId);
            $stmt->execute();
            $stmt->close();

            order_insert_status_log($conn, $orderId, $adminId, $oldStatus, $normalizedStatus, 'تحديث جماعي من قائمة الطلبات');
            $walletUsed = round((float)($order['wallet_amount_used'] ?? 0), 2);
            $customerId = (int)($order['customer_id'] ?? 0);
            if ($walletUsed > 0 && $customerId > 0 && !order_status_is_cancel_like($oldStatus) && order_status_is_cancel_like($normalizedStatus)) {
                $alreadyRefunded = order_wallet_amount_refunded($conn, $customerId, $orderId);
                $remainingRefund = round(max(0, $walletUsed - $alreadyRefunded), 2);
                if ($remainingRefund > 0) {
                    order_wallet_add_transaction($conn, $customerId, 'order_refund', $remainingRefund, 'order', (string)$orderId, 'إرجاع رصيد المحفظة بسبب إلغاء/إرجاع الطلب', $adminId);
                }
            }
            $afterOrder = [
                'id' => $orderId,
                'customer_id' => (int)($order['customer_id'] ?? 0),
                'order_status' => $normalizedStatus,
                'loyalty_points_redeemed' => (int)($order['loyalty_points_redeemed'] ?? 0),
                'loyalty_points_earned' => (int)($order['loyalty_points_earned'] ?? 0),
                'loyalty_redemption_type' => (string)($order['loyalty_redemption_type'] ?? 'amount'),
                'loyalty_reward_title' => (string)($order['loyalty_reward_title'] ?? ''),
                'tier_points_earned' => (int)($order['tier_points_earned'] ?? 0),
                'tier_points_awarded' => (int)($order['tier_points_awarded'] ?? 0),
                'delivered_at' => $deliveredAt,
            ];
            order_loyalty_sync_for_order($conn, $order, $afterOrder, $adminId);
            order_tier_sync_for_order($conn, $order, $afterOrder, $adminId);
            $conn->commit();
            $updated++;
        } catch (Throwable $e) {
            $conn->rollback();
            $errors[] = 'الطلب #' . ($order['order_number'] ?? $orderId) . ': ' . $e->getMessage();
        }
    }

    return [empty($errors), $errors, $updated];
}
