<?php

declare(strict_types=1);

namespace App\Models;

use Core\Database;

final class ShopModel
{
    private Database $db;

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    // ── Settings ──────────────────────────────────────────────────────────────

    public function settings(): array
    {
        $rows = $this->db->fetchAll("SELECT key_name, value FROM settings");
        $out  = [];
        foreach ($rows as $r) $out[$r['key_name']] = $r['value'];
        return $out;
    }

    // ── Categories ────────────────────────────────────────────────────────────

    public function activeCategories(): array
    {
        return $this->db->fetchAll(
            "SELECT * FROM categories WHERE is_active = 1 ORDER BY sort_order ASC, name ASC"
        );
    }

    // ── Menu items ────────────────────────────────────────────────────────────

    public function itemsByCategory(int $categoryId): array
    {
        return $this->db->fetchAll(
            "SELECT * FROM menu_items WHERE category_id = ? AND is_available = 1 ORDER BY sort_order ASC, name ASC",
            [$categoryId]
        );
    }

    public function allActiveItems(): array
    {
        return $this->db->fetchAll(
            "SELECT mi.*, c.name AS category_name
               FROM menu_items mi
               JOIN categories c ON c.id = mi.category_id
              WHERE mi.is_available = 1 AND c.is_active = 1
              ORDER BY c.sort_order ASC, mi.sort_order ASC"
        );
    }

    public function findItem(int $id): ?array
    {
        return $this->db->fetchOne(
            "SELECT mi.*, c.name AS category_name
               FROM menu_items mi
               JOIN categories c ON c.id = mi.category_id
              WHERE mi.id = ? AND mi.is_available = 1",
            [$id]
        );
    }

    public function itemChoiceGroups(int $itemId): array
    {
        $groups = $this->db->fetchAll(
            "SELECT * FROM item_choice_groups WHERE menu_item_id = ? ORDER BY sort_order ASC, id ASC",
            [$itemId]
        );
        foreach ($groups as &$g) {
            $g['choices'] = $this->db->fetchAll(
                "SELECT * FROM item_choices WHERE choice_group_id = ? ORDER BY sort_order ASC, id ASC",
                [$g['id']]
            );
        }
        return $groups;
    }

    // ── Active deals ──────────────────────────────────────────────────────────

    public function activeDeals(): array
    {
        $deals = $this->db->fetchAll(
            "SELECT * FROM deals
              WHERE is_active = 1 AND starts_at <= NOW() AND expires_at >= NOW()
              ORDER BY id DESC LIMIT 6"
        );
        foreach ($deals as &$d) {
            $d['items'] = $this->db->fetchAll(
                "SELECT di.quantity, mi.name AS item_name
                   FROM deal_items di
                   JOIN menu_items mi ON mi.id = di.menu_item_id
                  WHERE di.deal_id = ?",
                [$d['id']]
            );
        }
        return $deals;
    }

    // ── Voucher validate ──────────────────────────────────────────────────────

    public function validateVoucher(string $code, float $orderTotal, ?int $customerId = null): array
    {
        $v = $this->db->fetchOne(
            "SELECT * FROM vouchers
              WHERE code = ? AND is_active = 1
                AND starts_at <= NOW() AND expires_at >= NOW()",
            [strtoupper($code)]
        );

        if (!$v) return ['valid' => false, 'message' => 'Invalid or expired voucher.'];

        if ($v['usage_limit'] && $v['used_count'] >= $v['usage_limit']) {
            return ['valid' => false, 'message' => 'Voucher usage limit reached.'];
        }
        // Per-customer limit — once consumed, the same customer can't reuse it.
        $perUser = (int)($v['per_user_limit'] ?? 1);
        if ($customerId && $perUser > 0) {
            $usedByUser = (int)$this->db->fetchValue(
                "SELECT COUNT(*) FROM orders WHERE voucher_id = ? AND customer_id = ?",
                [$v['id'], $customerId]
            );
            if ($usedByUser >= $perUser) {
                return ['valid' => false, 'message' => 'You have already used this voucher.'];
            }
        }
        if ($orderTotal < (float)$v['min_order_amt']) {
            return ['valid' => false, 'message' => 'Minimum order Rs. ' . number_format($v['min_order_amt'], 2) . ' required.'];
        }

        $discount = $v['discount_type'] === 'percentage'
            ? min($orderTotal * $v['discount_value'] / 100, (float)($v['max_discount'] ?? PHP_FLOAT_MAX))
            : (float)$v['discount_value'];

        return ['valid' => true, 'voucher' => $v, 'discount' => round($discount, 2)];
    }

    // ── Working hours & delivery range ────────────────────────────────────────

    /// Whether the shop is open now + minutes until it next opens.
    public function hoursStatus(?array $settings = null): array
    {
        $s     = $settings ?? $this->settings();
        $open  = trim($s['open_time'] ?? '');
        $close = trim($s['close_time'] ?? '');
        if ($open === '' || $close === '') {
            return ['is_open' => true, 'open_time' => $open,
                    'close_time' => $close, 'opens_in_minutes' => 0];
        }
        $now = (int)date('H') * 60 + (int)date('i');
        [$oh, $om] = array_map('intval', array_pad(explode(':', $open), 2, 0));
        [$ch, $cm] = array_map('intval', array_pad(explode(':', $close), 2, 0));
        $openMin  = $oh * 60 + $om;
        $closeMin = $ch * 60 + $cm;

        $opensIn = 0;
        if ($closeMin > $openMin) {                 // same-day window
            $isOpen = ($now >= $openMin && $now < $closeMin);
            if (!$isOpen) {
                $opensIn = $now < $openMin ? $openMin - $now : (1440 - $now) + $openMin;
            }
        } else {                                     // crosses midnight
            $isOpen = ($now >= $openMin || $now < $closeMin);
            if (!$isOpen) $opensIn = $openMin - $now;
        }
        return ['is_open' => $isOpen, 'open_time' => $open,
                'close_time' => $close, 'opens_in_minutes' => max(0, $opensIn)];
    }

    /// Distance (km) from the restaurant to a point, or null if unknown.
    public function distanceFromRestaurant(?float $lat, ?float $lng, array $settings): ?float
    {
        $rlat = $settings['restaurant_lat'] ?? null;
        $rlng = $settings['restaurant_lng'] ?? null;
        if (!$rlat || !$rlng || $lat === null || $lng === null) return null;
        return round($this->haversineKm((float)$rlat, (float)$rlng, $lat, $lng), 2);
    }

    /// Whether a delivery point is within the configured radius (0 = unlimited).
    public function withinDeliveryRange(?float $lat, ?float $lng, array $settings): bool
    {
        $radius = (float)($settings['delivery_radius_km'] ?? 0);
        if ($radius <= 0) return true;
        $km = $this->distanceFromRestaurant($lat, $lng, $settings);
        if ($km === null) return true;   // can't verify → allow
        return $km <= $radius;
    }

    // ── Order-level discounts ─────────────────────────────────────────────────

    /// Generic policy-based discount computed from a set of settings keys with
    /// the given prefix (e.g. 'order_discount' or 'first_order').
    private function policyDiscount(float $subtotal, array $settings,
        string $enabledKey, string $typeKey, string $valueKey,
        string $minKey, string $capKey): float
    {
        if (($settings[$enabledKey] ?? '0') !== '1') return 0.0;
        $min = (float)($settings[$minKey] ?? 0);
        if ($subtotal < $min) return 0.0;
        $val = (float)($settings[$valueKey] ?? 0);
        if ($val <= 0) return 0.0;

        if (($settings[$typeKey] ?? 'percentage') === 'fixed') {
            return round(min($val, $subtotal), 2);
        }
        $disc = $subtotal * $val / 100;
        $cap  = (float)($settings[$capKey] ?? 0);
        if ($cap > 0) $disc = min($disc, $cap);
        return round(min($disc, $subtotal), 2);
    }

    /// Store-wide order discount (applies to every order).
    public function orderDiscount(float $subtotal, array $settings): float
    {
        return $this->policyDiscount($subtotal, $settings,
            'order_discount_enabled', 'order_discount_type', 'order_discount_value',
            'order_discount_min_amount', 'order_discount_max_discount');
    }

    // ── Discount offers (admin "Discounts" — item/category/order level) ────────

    /// Currently active offers (within their date window).
    public function activeOffers(): array
    {
        return $this->db->fetchAll(
            "SELECT * FROM discount_offers
              WHERE is_active = 1 AND starts_at <= NOW() AND expires_at >= NOW()"
        );
    }

    /// Best per-unit display discount for an item from item/category offers and
    /// any percentage order-level offer (used to show a struck-through price).
    public function itemDisplayDiscount(float $unitPrice, int $itemId,
        ?int $catId, array $offers): float
    {
        // Advertise the offer on the product regardless of any minimum order —
        // the minimum is enforced at checkout, and the discount policy link
        // explains the conditions to the customer.
        $best = 0.0;
        foreach ($offers as $o) {
            $val = (float)$o['discount_value'];
            $d = null;
            if ($o['applies_to'] === 'item' && (int)$o['target_id'] === $itemId) {
                $d = $o['discount_type'] === 'percentage' ? $unitPrice * $val / 100 : $val;
            } elseif ($o['applies_to'] === 'category' && $catId !== null
                      && (int)$o['target_id'] === $catId) {
                $d = $o['discount_type'] === 'percentage' ? $unitPrice * $val / 100 : $val;
            } elseif ($o['applies_to'] === 'order' && $o['discount_type'] === 'percentage') {
                $d = $unitPrice * $val / 100;   // whole-order % shows on each item
            }
            if ($d !== null) $best = max($best, min($d, $unitPrice));
        }
        return round($best, 2);
    }

    /// Customer-facing list of active discount policies (offers + settings).
    public function discountPolicies(): array
    {
        $s   = $this->settings();
        $sym = $s['currency_symbol'] ?? 'Rs.';
        $out = [];

        $label = function (string $type, float $val) use ($sym): string {
            return $type === 'fixed'
                ? "$sym " . number_format($val, 0) . ' OFF'
                : rtrim(rtrim(number_format($val, 2), '0'), '.') . '% OFF';
        };
        $conds = function (float $min, ?float $cap) use ($sym): array {
            $c = [];
            if ($min > 0) $c[] = 'Min order ' . $sym . ' ' . number_format($min, 0);
            if ($cap !== null && $cap > 0) $c[] = 'Up to ' . $sym . ' ' . number_format($cap, 0);
            return $c;
        };

        // Active discount offers
        foreach ($this->activeOffers() as $o) {
            if ($o['applies_to'] === 'item') {
                $name  = $this->db->fetchValue("SELECT name FROM menu_items WHERE id = ?", [$o['target_id']]);
                $scope = 'On ' . ($name ?: 'selected item');
            } elseif ($o['applies_to'] === 'category') {
                $name  = $this->db->fetchValue("SELECT name FROM categories WHERE id = ?", [$o['target_id']]);
                $scope = 'On all ' . ($name ?: 'selected category');
            } else {
                $scope = 'On your whole order';
            }
            $out[] = [
                'title'      => $o['title'],
                'label'      => $label($o['discount_type'], (float)$o['discount_value']),
                'scope'      => $scope,
                'conditions' => $conds((float)$o['min_order_amt'],
                    ($o['max_discount'] ?? null) !== null ? (float)$o['max_discount'] : null),
            ];
        }

        // First-order discount
        if (($s['first_order_discount_enabled'] ?? '0') === '1'
            && (float)($s['first_order_discount_value'] ?? 0) > 0) {
            $out[] = [
                'title'      => 'First Order Discount',
                'label'      => $label($s['first_order_discount_type'] ?? 'percentage',
                                       (float)$s['first_order_discount_value']),
                'scope'      => 'On your first order only',
                'conditions' => $conds((float)($s['first_order_min_amount'] ?? 0),
                                       (float)($s['first_order_max_discount'] ?? 0) ?: null),
            ];
        }

        // Store-wide order discount (settings)
        if (($s['order_discount_enabled'] ?? '0') === '1'
            && (float)($s['order_discount_value'] ?? 0) > 0) {
            $out[] = [
                'title'      => 'Order Discount',
                'label'      => $label($s['order_discount_type'] ?? 'percentage',
                                       (float)$s['order_discount_value']),
                'scope'      => 'On every order',
                'conditions' => $conds((float)($s['order_discount_min_amount'] ?? 0),
                                       (float)($s['order_discount_max_discount'] ?? 0) ?: null),
            ];
        }

        return $out;
    }

    /// Total discount from active offers for a whole cart (used at checkout).
    /// Each offer respects its own minimum order amount and max-discount cap.
    public function offersDiscount(float $subtotal, array $cartItems): float
    {
        $offers = $this->activeOffers();
        if (empty($offers)) return 0.0;

        // Look up categories for the cart's menu items in one query.
        $ids = array_values(array_filter(array_map(
            fn($i) => (int)($i['item_id'] ?? 0), $cartItems)));
        $catOf = [];
        if ($ids) {
            $ph = implode(',', array_fill(0, count($ids), '?'));
            foreach ($this->db->fetchAll(
                "SELECT id, category_id FROM menu_items WHERE id IN ($ph)", $ids) as $r) {
                $catOf[(int)$r['id']] = (int)$r['category_id'];
            }
        }

        $total = 0.0;
        foreach ($offers as $o) {
            // Minimum order amount — applies to every offer type.
            if ($subtotal < (float)$o['min_order_amt']) continue;

            $offerDisc = 0.0;
            if ($o['applies_to'] === 'order') {
                $offerDisc = $o['discount_type'] === 'percentage'
                    ? $subtotal * (float)$o['discount_value'] / 100
                    : (float)$o['discount_value'];
            } else {
                // item / category: sum the discount across matching lines.
                foreach ($cartItems as $ci) {
                    $iid = (int)($ci['item_id'] ?? 0);
                    if ($iid === 0) continue;
                    $qty = (int)($ci['qty'] ?? 1);
                    $up  = (float)($ci['unit_price'] ?? 0);
                    $cat = $catOf[$iid] ?? null;
                    $match = ($o['applies_to'] === 'item' && (int)$o['target_id'] === $iid)
                          || ($o['applies_to'] === 'category' && $cat !== null
                              && (int)$o['target_id'] === $cat);
                    if (!$match) continue;
                    $d = $o['discount_type'] === 'percentage'
                        ? $up * (float)$o['discount_value'] / 100 : (float)$o['discount_value'];
                    $offerDisc += min($d, $up) * $qty;
                }
            }

            // Max-discount cap (0 / NULL = no cap).
            $cap = ($o['max_discount'] ?? null) !== null ? (float)$o['max_discount'] : 0.0;
            if ($cap > 0) $offerDisc = min($offerDisc, $cap);

            $total += $offerDisc;
        }
        return round(min($total, $subtotal), 2);
    }

    // ── First-order discount ──────────────────────────────────────────────────

    /// Discount amount for a customer's first order, per the admin policy.
    /// Returns 0 when disabled, below the minimum, or misconfigured.
    public function firstOrderDiscount(float $subtotal, array $settings): float
    {
        if (($settings['first_order_discount_enabled'] ?? '0') !== '1') return 0.0;
        $min = (float)($settings['first_order_min_amount'] ?? 0);
        if ($subtotal < $min) return 0.0;
        $val = (float)($settings['first_order_discount_value'] ?? 0);
        if ($val <= 0) return 0.0;

        if (($settings['first_order_discount_type'] ?? 'percentage') === 'fixed') {
            return round(min($val, $subtotal), 2);
        }
        $disc = $subtotal * $val / 100;
        $cap  = (float)($settings['first_order_max_discount'] ?? 0);
        if ($cap > 0) $disc = min($disc, $cap);
        return round(min($disc, $subtotal), 2);
    }

    /// Whether this customer has never placed an order.
    public function isFirstOrder(int $customerId): bool
    {
        if ($customerId <= 0) return false;
        return (int)$this->db->fetchValue(
            "SELECT COUNT(*) FROM orders WHERE customer_id = ?", [$customerId]) === 0;
    }

    // ── Delivery fee calculation ──────────────────────────────────────────────

    public function calculateDeliveryFee(
        string $orderType,
        float $subtotal,
        ?float $lat,
        ?float $lng,
        array $settings
    ): float {
        if ($orderType === 'pickup') return 0.0;

        $chargeType = $settings['delivery_charge_type'] ?? 'fixed';
        $freeAbove  = (float)($settings['delivery_free_above'] ?? 0);

        if ($freeAbove > 0 && $subtotal >= $freeAbove) return 0.0;

        if ($chargeType === 'per_km') {
            $restLat   = (float)($settings['restaurant_lat']      ?? 0);
            $restLng   = (float)($settings['restaurant_lng']      ?? 0);
            $minFee    = (float)($settings['delivery_min_fee']    ?? 0);
            $perKmRate = (float)($settings['delivery_per_km_rate'] ?? 0);

            if ($lat && $lng && ($restLat || $restLng)) {
                $km = $this->haversineKm($restLat, $restLng, $lat, $lng);
                return max($minFee, round($km * $perKmRate));
            }
            return $minFee;
        }

        // fixed (default)
        return (float)($settings['delivery_fixed_fee'] ?? $settings['delivery_fee'] ?? 0);
    }

    private function haversineKm(float $lat1, float $lng1, float $lat2, float $lng2): float
    {
        $R    = 6371;
        $dLat = \deg2rad($lat2 - $lat1);
        $dLng = \deg2rad($lng2 - $lng1);
        $a    = \sin($dLat / 2) ** 2 + \cos(\deg2rad($lat1)) * \cos(\deg2rad($lat2)) * \sin($dLng / 2) ** 2;
        return $R * 2 * \atan2(\sqrt($a), \sqrt(1 - $a));
    }

    // ── Place order ───────────────────────────────────────────────────────────

    public function placeOrder(?int $customerId, array $guestData, array $data, array $cartItems): int
    {
        return $this->db->transaction(function () use ($customerId, $guestData, $data, $cartItems) {
            $prefix    = date('ymd') . '-';
            $prefixPos = strlen($prefix) + 1; // SUBSTRING() is 1-indexed
            $maxSeq    = (int)$this->db->fetchValue(
                "SELECT COALESCE(MAX(CAST(SUBSTRING(order_number, ?) AS UNSIGNED)), 0)
                   FROM orders WHERE order_number LIKE ?",
                [$prefixPos, $prefix . '%']
            );

            // Find next unused number (handles race conditions)
            $number = null;
            for ($i = 1; $i <= 10; $i++) {
                $candidate = $prefix . str_pad((string)($maxSeq + $i), 4, '0', STR_PAD_LEFT);
                $taken     = $this->db->fetchValue(
                    "SELECT 1 FROM orders WHERE order_number = ?", [$candidate]
                );
                if (!$taken) { $number = $candidate; break; }
            }
            // Fallback: microsecond-based suffix guarantees uniqueness
            if ($number === null) {
                $number = $prefix . substr(str_replace('.', '', (string)microtime(true)), -6);
            }

            // For registered customers, snapshot their name/phone/email so the
            // order stays readable even if the account is later deleted.
            if ($customerId) {
                $cu = $this->db->fetchOne(
                    "SELECT u.name, u.phone, u.email
                       FROM users u JOIN customers c ON c.user_id = u.id
                      WHERE c.id = ?", [$customerId]
                );
                if ($cu) {
                    $guestData['name']  = ($guestData['name']  ?? '') ?: $cu['name'];
                    $guestData['phone'] = ($guestData['phone'] ?? '') ?: $cu['phone'];
                    $guestData['email'] = ($guestData['email'] ?? '') ?: $cu['email'];
                }
            }

            $orderId = $this->db->insert('orders', [
                'order_number'    => $number,
                'customer_id'     => $customerId,
                'guest_name'      => $guestData['name']  ?? null,
                'guest_phone'     => $guestData['phone'] ?? null,
                'guest_email'     => $guestData['email'] ?? null,
                'order_type'      => $data['order_type'] ?? 'delivery',
                'delivery_address'=> $data['address'] ?? null,
                'delivery_lat'    => $data['lat'] ?? null,
                'delivery_lng'    => $data['lng'] ?? null,
                'payment_method'  => $data['payment_method'] ?? 'cash',
                'subtotal'        => $data['subtotal'],
                'discount_amount' => $data['discount'] ?? 0,
                'delivery_fee'    => $data['delivery_fee'] ?? 0,
                'tax_amount'      => $data['tax'] ?? 0,
                'total_amount'    => $data['total'],
                'voucher_id'      => $data['voucher_id'] ?? null,
                'special_notes'   => $data['notes'] ?? null,
                'status'          => 'pending',
            ]);

            foreach ($cartItems as $item) {
                $isDeal = (($item['type'] ?? '') === 'deal') || !empty($item['deal_id']);
                $qty    = (int)($item['qty'] ?? 1);
                $price  = (float)($item['unit_price'] ?? 0);

                if ($isDeal) {
                    // A deal is stored as a single order line (like a menu item).
                    // The combo contents are snapshotted into choices_summary so
                    // the KOT prints all included items beneath the deal name.
                    $dealId = (int)($item['deal_id'] ?? 0);
                    $combo  = $this->dealItemsSummary($dealId);
                    if ($combo === '') $combo = (string)($item['choices_label'] ?? '');
                    $this->db->insert('order_items', [
                        'order_id'       => $orderId,
                        'menu_item_id'   => null,
                        'deal_id'        => $dealId ?: null,
                        'item_name'      => $item['name'] ?? 'Deal',
                        'unit_price'     => $price,
                        'quantity'       => $qty,
                        'line_total'     => $price * $qty,
                        'choices_summary'=> $combo ?: null,
                    ]);
                    continue;
                }

                $lineId = $this->db->insert('order_items', [
                    'order_id'       => $orderId,
                    'menu_item_id'   => $item['item_id'] ?? null,
                    'item_name'      => $item['name'],
                    'unit_price'     => $price,
                    'quantity'       => $qty,
                    'line_total'     => $price * $qty,
                    'choices_summary'=> $item['choices_label'] ?? null,
                ]);

                if (!empty($item['choices'])) {
                    foreach ($item['choices'] as $groupName => $choice) {
                        $this->db->insert('order_item_choices', [
                            'order_item_id'  => $lineId,
                            'group_name'     => $groupName,
                            'choice_label'   => $choice['label'],
                            'price_modifier' => $choice['price'] ?? 0,
                        ]);
                    }
                }
            }

            $this->db->insert('order_status_log', [
                'order_id' => $orderId,
                'status'   => 'pending',
                'note'     => 'Order placed by customer',
            ]);

            // Increment voucher usage atomically
            if (!empty($data['voucher_id'])) {
                $this->db->query(
                    "UPDATE vouchers SET used_count = used_count + 1 WHERE id = ?",
                    [$data['voucher_id']]
                );
            }

            return $orderId;
        });
    }

    // ── Customer orders ───────────────────────────────────────────────────────

    /// Comma-separated combo contents of a deal, e.g. "2× Zinger Burger, Fries".
    private function dealItemsSummary(int $dealId): string
    {
        if ($dealId <= 0) return '';
        $rows = $this->db->fetchAll(
            "SELECT mi.name, di.quantity
               FROM deal_items di
               JOIN menu_items mi ON mi.id = di.menu_item_id
              WHERE di.deal_id = ?
              ORDER BY di.id ASC",
            [$dealId]
        );
        $parts = [];
        foreach ($rows as $r) {
            $q = (int)$r['quantity'];
            $parts[] = ($q > 1 ? $q . '× ' : '') . $r['name'];
        }
        return implode(', ', $parts);
    }

    public function myOrders(int $customerId, string $phone = ''): array
    {
        // Include past guest orders placed with the same phone number so a
        // customer's full history is visible after they create an account.
        $phone = trim($phone);
        if ($phone !== '') {
            return $this->db->fetchAll(
                "SELECT * FROM orders
                  WHERE customer_id = ?
                     OR (customer_id IS NULL AND guest_phone = ?)
                  ORDER BY placed_at DESC",
                [$customerId, $phone]
            );
        }
        return $this->db->fetchAll(
            "SELECT * FROM orders WHERE customer_id = ? ORDER BY placed_at DESC",
            [$customerId]
        );
    }

    public function orderDetail(int $orderId, ?int $customerId = null, string $phone = ''): ?array
    {
        if ($customerId) {
            $order = $this->db->fetchOne(
                "SELECT o.*, u.name AS rider_name, u.phone AS rider_phone,
                        u.current_lat AS rider_lat, u.current_lng AS rider_lng,
                        u.location_at AS rider_location_at
                   FROM orders o
                   LEFT JOIN users u ON u.id = o.rider_id
                  WHERE o.id = ?
                    AND (o.customer_id = ?
                         OR (o.customer_id IS NULL AND o.guest_phone = ?))",
                [$orderId, $customerId, trim($phone)]
            );
        } else {
            $order = $this->db->fetchOne(
                "SELECT o.*, u.name AS rider_name, u.phone AS rider_phone,
                        u.current_lat AS rider_lat, u.current_lng AS rider_lng,
                        u.location_at AS rider_location_at
                   FROM orders o
                   LEFT JOIN users u ON u.id = o.rider_id
                  WHERE o.id = ? AND o.customer_id IS NULL",
                [$orderId]
            );
        }
        if (!$order) return null;

        $order['items'] = $this->db->fetchAll(
            "SELECT oi.*, oi.line_total AS subtotal, oi.choices_summary AS choices_detail
               FROM order_items oi WHERE oi.order_id = ? ORDER BY oi.id ASC",
            [$orderId]
        );
        $order['status_log'] = $this->db->fetchAll(
            "SELECT * FROM order_status_log WHERE order_id = ? ORDER BY changed_at ASC",
            [$orderId]
        );
        return $order;
    }
}
