<?php

namespace objects;

use attachments\helpers\attachment_content;
use classes\db;
use classes\email;
use classes\pdf_generator;
use classes\motorapi;
use classes\object_property;
use classes\response;
use DateTime;
use Exception;
use helpers\xlvask_usage_log;
use helpers\xlvask_wash_item;
use routes\InvoicingPeriodRoute;
use traits\db_object_t;

class orders_o extends db
{
    use db_object_t;

    public object_property $customer_id;
    public object_property $cashier_id;
    public object_property $reference;
    public object_property $notes;
    public object_property $department_id;
    public object_property $reg_1;
    public object_property $reg_2;
    public object_property $reg_3;
    public economic_module_orders $economic_module_orders;
    public object_property $created_at;
    public object_property $deleted_at;

    public object_property $completed_at;
    public stripe_module_orders_o $stripe_module_orders;
    public object_property $invoice_collection_id;
    public object_property $booking_id;
    public object_property $wash_id; // The XL Vask Wash ID, if any
    public object_property $lane; // The lane used for the order, if any
    public object_property $po; // The (optional) PO number, filled by the customer.
    public object_property $using_hand_held; // Whether the order is being processed using a handheld device

    /**
     * Temporary keys
     * These are used for calculations and should not be stored in the database.
     * @var float $temporary_net_amount
     * @see InvoicingPeriodRoute::getInvoicingPeriod()
     */
    public float $temporary_net_amount = 0.0; // Temporary net amount for the order, used for calculations


    public function structure(): void
    {
        $this->setTable('orders');
    }

    public function edit(int $id, int $customer_id, int $cashier_id, string $reference, string $notes, int $department_id): void
    {
        global $db, $response;
        $this->id = $id;
        try {
            // Avoid SQL injection
            $reference = $db->escape_string($reference);
            $notes = $db->escape_string($notes);
            // Update the record in the database
            $sql = "UPDATE $this->table SET customer_id = $customer_id, cashier_id = $cashier_id, reference = '$reference', notes = '$notes', department_id = $department_id WHERE id = $id";
            $db->query($sql);

            // Set the values of the object properties
            $this->getObjectProperties();
        } catch (Exception $e) {
            $response->error($e->getMessage());
        }
    }

    public function getObjectProperties(): void
    {
        $this->customer_id = new object_property($this->table, $this->id, 'customer_id', 'int', true);
        $this->cashier_id = new object_property($this->table, $this->id, 'cashier_id', 'int', true);
        $this->reference = new object_property($this->table, $this->id, 'reference', 'string', true);
        $this->notes = new object_property($this->table, $this->id, 'notes', 'string', false);
        $this->department_id = new object_property($this->table, $this->id, 'department_id', 'int', true);
        $this->reg_1 = new object_property($this->table, $this->id, 'reg_1', 'string', false);
        $this->reg_2 = new object_property($this->table, $this->id, 'reg_2', 'string', false);
        $this->reg_3 = new object_property($this->table, $this->id, 'reg_3', 'string', false);
        $this->created_at = new object_property($this->table, $this->id, 'created_at', 'timestamp', false);
        $this->completed_at = new object_property($this->table, $this->id, 'completed_at', 'timestamp', false);
        $this->economic_module_orders = (new economic_module_orders())->getByOrderId($this->id);
        $this->stripe_module_orders = (new stripe_module_orders_o())->select($this->id);
        $this->deleted_at = new object_property($this->table, $this->id, 'deleted_at', 'timestamp', false);
        $this->invoice_collection_id = new object_property($this->table, $this->id, 'invoice_collection_id', 'int', false);
        $this->booking_id = new object_property($this->table, $this->id, 'booking_id', 'int', false);
        $this->wash_id = new object_property($this->table, $this->id, 'wash_id', 'string', false);
        $this->lane = new object_property($this->table, $this->id, 'lane', 'string', false);
        $this->po = new object_property($this->table, $this->id, 'po', 'string', false);
        $this->using_hand_held = new object_property($this->table, $this->id, 'using_hand_held', 'bool', false);
    }

    public function getCustomerByOrderId(?string $order_id): users_o
    {
        global $db;
        $sql = "SELECT customer_id FROM orders WHERE id = $order_id";
        $result = $db->query($sql);
        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            return (new users_o())->getUserByCustomerNumber($row['customer_id']);
        }
        return new users_o();
    }

    public function getCustomerOrdersPaginated(int $customer_number, int $page = 1, int $limit = 10, array $order = ['id' => 'DESC'], string $search = null, array $filters = null): array
    {
        global /** @var response $response */
        $db, $response;
        // Add the customer number to the filters
        $filters['customer_id'] = $customer_number;

        // List the objects with pagination
        $array = $this->listObjectsWithPagination($page, $limit, $search, $filters, $order);

        // Get the total number of objects
        $total = $this->getTotalObjects($search, $filters);
        $response->paginate($page, $limit, $total);
        return $array;
    }

    /**
     * @throws Exception
     */
    public function getDepartmentByOrderId($order_id): array
    {
        $tmp_order = new orders_o();
        if ($order_id === null) {
            // Get the department id from the order
            self::requireSelected();
            $order_id = $this->id;
        }
        return (new departments_o())->getDepartmentById((int)$tmp_order->select($order_id)->department_id->value(), true);
    }

    public function delete(): void
    {
        // Set the deleted_at property to the current timestamp
        $this->deleted_at->set(date('Y-m-d H:i:s'));
        $this->objectChanged();
        // Save the object
    }

    /**
     * @throws Exception If the order is not selected
     * This function is called when the order object is changed.
     */
    public function objectChanged(): void
    {
        self::requireSelected();
        // Reset the cached object
        self::deleteCached('asArray', $this->id);
        // Inform the order collection that the order has changed
        $this->getOrderCollection()->objectChanged();
    }

    /**
     * Get the order collection for the order
     * @return collected_order_invoices_o The order collection
     * @throws Exception If the order is not selected
     */
    public function getOrderCollection(): collected_order_invoices_o
    {
        self::requireSelected();
        $order_collection = new collected_order_invoices_o();
        $order_collection->select((int)$this->invoice_collection_id->value());
        $order_collection->requireSelected();
        return $order_collection;
    }

    public function restore(): void
    {
        // Set the deleted_at property to null
        $this->deleted_at->set(null);
        // Save the object
    }

    /**
     * @param string $plate The vehicle plate
     * @param int $entries The number of last entries to return (default 10)
     * @return array The orders for the vehicle plate
     */
    public function getOrderHistoryByVehiclePlate(string $plate, int $entries = 10): array
    {
        global $db;
        $sql = "SELECT * FROM $this->table WHERE reg_1 = '$plate' OR reg_2 = '$plate' OR reg_3 = '$plate' ORDER BY id DESC LIMIT $entries";
        $result = $db->query($sql);
        return $db->fetch_all($result);
    }

    public function unlinkOrdersFromInvoiceDraft(int $draftInvoiceNumber): void
    {
        $this->economic_module_orders->unlinkAllOrdersFromDraft($draftInvoiceNumber);
    }

    public function getOrderCustomer(int $orderId): users_o
    {
        $order = new orders_o();
        $order->getOrderById($orderId);
        return (new users_o())->getUserByCustomerNumber((int)$order->customer_id->value());
    }

    public function getOrderById(int $id): orders_o
    {
        global $db;
        // Get the record from the database
        $sql = "SELECT * FROM $this->table WHERE id = $id";
        $result = $db->query($sql);
        if ($result->num_rows > 0) {
            $this->id = $id;
            $this->getObjectProperties();
        }
        return $this;
    }

    /**
     * This function is used when an order is updated
     * It takes the request data and updates the order accordingly.
     * @return void
     * @example {"id":410,"field":"reg_1","value":"REG12"}
     */
    public function updateRequest(): void
    {
        global $response;
        // If the permission check is not skipped, require the user to be logged in
        $data = json_decode(file_get_contents('php://input'), true);
        $this->id = $data['id'];
        $this->getObjectProperties();
        // Throw an error if the order does not exist
        if (!$this->exists()) {
            $response->error('Order not found, or already deleted', 400);
        }
        // Validate the field value
        if (!isset($data['field']) || !isset($data['value'])) {
            $response->error('Field and value are required', 400);
        }
        // Check if the value is null
        if ($data['value'] === 'null' || $data['value'] === 'NULL') {
            $this->{$data['field']}->nullify();
        }
        $this->{$data['field']}->set($data['value']);
    }

    public function exists(): bool
    {
        // Check if the id is greater than 0, and that the deleted_at property is null
        if ($this->id > 0) {
            $this->getObjectProperties();
            return $this->deleted_at->value() === null;
        }
        return false;
    }

    /**
     * Mark the order as completed
     * @throws Exception If the order is not selected
     * @throws Exception If the order is already completed
     */
    public function markAsCompleted(): void
    {
        global /** @var db $db */
        $db;
        self::requireSelected();
        // Check if the order is already completed
        if ($this->completed_at->value() !== null) {
            throw new Exception('Order is already completed');
        }
        // Set the completed_at property to the current timestamp
        $this->completed_at->set(date('Y-m-d H:i:s'));
        $sql = "UPDATE $this->table SET completed_at = '" . $this->completed_at->value() . "' WHERE id = " . $this->id;
        $db->query($sql);
        $this->objectChanged();
    }

    /**
     * Get the order by invoice id
     * @param int $invoiceId
     * @return $this
     * @throws Exception
     */
    public function getOrderByInvoiceId(int $invoiceId): orders_o
    {
        global $db;
        $sql = "SELECT id FROM economic_module_orders WHERE invoice_id = $invoiceId";
        $result = $db->query($sql);
        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            return $this->getOrderById($row['id']);
        }
        throw new Exception('Order not found');
    }

    /**
     * Set the stripe invoicing for an order
     * @throws Exception
     */
    public function setStripeInvoicing(string $invoice_id, string $stripe_customer_id, string $url): void
    {
        self::requireSelected();
        $this->stripe_module_orders->add($this->id, $invoice_id, $stripe_customer_id, $url);
        self::objectChanged();
    }

    public function addArray(array $order_array): orders_o
    {
        global $db, $response;

        try {
            // Avoid SQL injection
            $order_array = array_map(function ($value) use ($db) {
                if (is_string($value)) {
                    // Escape string values
                    return $db->escape_string($value);
                }
                // Return other types as is
                return $value;
            }, $order_array);
            // Create a new record in the database
            $this->id = self::add_object([...$order_array]);
            $this->getObjectProperties();
            self::requireSelected();
            self::assignToInvoiceCollection();
            return $this;
        } catch (Exception $e) {
            $response->error($e->getMessage());
        }
    }

    public function add(int $customer_id, int $cashier_id, string $reference, string $notes, int $department_id, string $reg_1 = '', string $reg_2 = '', string $reg_3 = ''): orders_o
    {
        global $db, $response;
        try {
            // Avoid SQL injection
            $reference = $db->escape_string($reference);
            $notes = $db->escape_string($notes);
            $reg_1 = $db->escape_string($reg_1);
            $reg_2 = $db->escape_string($reg_2);
            $reg_3 = $db->escape_string($reg_3);
            // Create a new record in the database
            $sql = "INSERT INTO $this->table (customer_id, cashier_id, reference, notes, department_id, reg_1, reg_2, reg_3) VALUES ($customer_id, $cashier_id, '$reference', '$notes', $department_id, '$reg_1', '$reg_2', '$reg_3')";
            $db->query($sql);

            // Get the id of the new record
            $this->id = $db->insert_id();

            // Set the values of the object properties
            $this->getObjectProperties();
            self::requireSelected();
            self::assignToInvoiceCollection();
            return $this;
        } catch (Exception $e) {
            $response->error($e->getMessage());
        }
    }

    /**
     * Assign the order to an invoice collection
     * @param int|null $invoiceCollectionId The invoice collection id, if not set, the default invoice collection id will be used.
     * @throws Exception If the order is not selected
     */
    public function assignToInvoiceCollection(int $invoiceCollectionId = null): void
    {
        // If the invoice collection id is not set, get the default invoice collection id
        self::requireSelected();
        // Get the customer
        $customer = new users_o();
        $customer_id = (int)$this->customer_id->value();
        if ($customer_id === 0) {
            throw new Exception('The customer id is not set for the order!.');
        }
        $customer->getUserByCustomerNumber($customer_id);
        $customer->requireSelected();
        // Get the invoice collection id
        $invoiceCollectionId = $invoiceCollectionId ?? $customer->getNewOrderInvoiceCollectionId();
        // Assign the order to the invoice collection
        $this->invoice_collection_id->set($invoiceCollectionId);
        self::objectChanged();
    }

    /**
     * Get the recommended order items, based on the order history, vehicle plate and department
     * @return array The recommended order items
     * @throws Exception If the order is not selected
     */
    public function getRecommendedOrder(): array
    {
        self::requireSelected();
        $recommended = [
            'reg_1' => self::getRecommendedOrderPlate($this->reg_1->value()),
            'reg_2' => [],
            'reg_3' => [],
        ];
        return $recommended;
    }

    /**
     * @throws Exception If the order is not selected
     */
    public function getRecommendedOrderPlate(string $plate): array
    {
        self::requireSelected();
        $result = [
            'order_history' => self::get_vehicle_last_orders_items($plate),
        ];

        // If the MotorApi is enabled, get the recommended order items based on the vehicle plate
        $MotorApi = new motorapi();
        if ($MotorApi->config->enabled->isTrue()) {
            $MotorApi_data = $MotorApi->getRecommendedProducts($plate);
            if ($MotorApi_data) {
                // Get the recommended order items based on the vehicle plate
                $result['motorapi'] = $MotorApi_data;
            }
        }

        return $result;
    }

    /**
     * Get the last orders item ids for a vehicle plate
     * @param string $plate The vehicle plate
     * @return array The last order item ids
     */
    public function get_vehicle_last_orders_items(string $plate): array
    {
        global /** @var db $db */
        $db;
        $sql = "SELECT id FROM $this->table WHERE reg_1 = '$plate' OR reg_2 = '$plate' OR reg_3 = '$plate' AND deleted_at IS NULL ORDER BY id DESC LIMIT 5";
        $result = $db->query($sql);
        $orders = $db->fetch_all($result);
        $order_items = [
            1 => [],
            2 => [],
            3 => [],
            4 => [],
            5 => [],
        ];
        // Get the order item (ids) from the last 5 orders
        $index = 0;
        foreach ( $orders as $order ) {
            $index++;
            $order_tmp = new orders_o();
            // Get the order items for the order
            $items_tmp = $order_tmp->getOrderItems($order['id']);
            // Add the items to the result
            foreach ( $items_tmp as $item ) {
                $order_items[$index][] = [
                    'product_id' => $item['product_id'],
                    'reference' => $item['reference'],
                    'quantity' => $item['quantity'],
                    'product' => $item['product'],
                ];
            }
        }
        return $order_items;
    }

    public function getOrderItems(int $order_id): array
    {
        global $db;
        $sql = "SELECT * FROM order_items WHERE order_id = $order_id";
        $result = $db->query($sql);
        $order_items = [];
        if ($result->num_rows > 0 && $result) {
            while ($row = $result->fetch_assoc()) {
                $order_item = new order_items_o();
                $order_items[] = $order_item->getOrderItemById($row['id'])->getItemAsArray();
            }
        }
        return $order_items;
    }

    /**
     * Get the order history for a vehicle plate
     * @param string $plate The vehicle plate
     * @return array The order history
     */
    public function get_vehicle_order_history(string $plate): array
    {
        global $db;
        $sql = "SELECT * FROM $this->table WHERE reg_1 = '$plate' OR reg_2 = '$plate' OR reg_3 = '$plate' AND deleted_at IS NULL ORDER BY id DESC LIMIT 5";
        $result = $db->query($sql);
        return $db->fetch_all($result);
    }

    /**
     * @throws Exception If the order is not selected
     */
    public function getIncludeInInvoiceCount(): int
    {
        self::requireSelected();
        $order_items = new order_items_o();
        $items = $order_items->getAllItemsAsArray($this->id, [
            'include_in_invoice',
        ]);
        $count = 0;
        foreach ( $items as $item ) {
            if ($item['include_in_invoice']) {
                $count++;
            }
        }
        return $count;
    }

    /**
     * Get the wash subscription transactions for a customer
     * @throws Exception If something goes wrong
     */
    public function getWashSubscriptionTransactions(int $customer_number, bool $asArray = true, ?string $dateFrom = null, ?string $dateTo = null): array
    {
        return self::listObjectsWithPagination(
            1,
            100,
            null,
            [
                'customer_id' => $customer_number,
                'deleted_at' => null,
                'cashier_id' => (new collected_order_invoices_o())->economic_wash_subscription_user_id,
                ...(!empty($dateFrom) ? ['created_at-date_from' => $dateFrom] : []),
                ...(!empty($dateTo) ? ['created_at-date_to' => $dateTo] : []),
            ],
            [
                'id' => 'DESC',
            ],
            function ($object) use ($asArray) {
                $res = (new orders_o())->select($object['id']);
                if ($asArray) {
                    return $res->asArray();
                }
                return $res;
            }
        );
    }

    /**
     * @param bool $cacheResult Whether to cache the result or not, this is used to prevent caching simulated orders
     * @param bool $skipCache Whether to skip the cache or not, this is used to force a fresh result
     * Convert the order object to an array
     * @return array The order as an array
     * @throws Exception
     */
    public function asArray(bool $skipCache = false, bool $cacheResult = true): array
    {
        self::requireSelected();
        if (!$skipCache) {
            // Check if the object is cached
            $cached = self::getCached('asArray', $this->id);
            if ($cached) {
                return (array)$cached;
            }
        }
        $tmp = [
            'id' => $this->id,
            'customer_id' => (int)$this->customer_id->value(),
            'cashier_id' => (int)$this->cashier_id->value(),
            'reference' => $this->reference->value(),
            'notes' => $this->notes->value(),
            'department_id' => (int)$this->department_id->value(),
            'reg_1' => $this->reg_1->value(),
            'reg_2' => $this->reg_2->value(),
            'reg_3' => $this->reg_3->value(),
            'completed_at' => $this->completed_at->value(),
            'created_at' => $this->created_at->value(),
            'deleted_at' => $this->deleted_at->value(),
            'total_net_amount' => $this->temporary_net_amount ?: $this->getNetAmount(),
            'invoice_collection_id' => (int)$this->invoice_collection_id->value(),
            'booking_id' => (int)$this->booking_id->value(),
            'wash_id' => $this->wash_id->value(),
            'lane' => $this->lane->value(),
            'po' => $this->po->value(),
            'closed_at' => (int)$this->invoice_collection_id->value() ? (new collected_order_invoices_o())->select((int)$this->invoice_collection_id->value())->closed_at->value() : null,
            'pending_handheld' => $this->isPendingHandheld(),
        ];
        // Cache the object
        if ($cacheResult) {
            self::cache('asArray', $tmp, $this->id);
            self::setCachedExpiration('asArray', self::$asArrayCacheExpiration, $this->id);
        }
        return $tmp;
    }

    public function getNetAmount(): float
    {
        self::requireSelected();
        $net = 0;
        // Get the order items object
        $order_items = new order_items_o();
        // Get the price, quantity of the order items
        $items = $order_items->getAllItemsAsArray($this->id, [
            'include_in_invoice',
            'price',
            'quantity',
        ]);
        // Loop through the items and get the net amount
        foreach ( $items as $item ) {
            // Check if the item is included in the invoice
            if (!$item['include_in_invoice']) {
                continue;
            }
            $tmp_price = (int)$item['price'];
            $tmp_quantity = (int)$item['quantity'];
            // Add the price to the net amount
            $net += $tmp_price * $tmp_quantity;
        }
        return $net;
    }

    /**
     * Get the net amount of the orders
     * @param int[] $order_ids An array of order IDs to get the net amount for
     * @return array An array with the net amount for each order [ order_id => net_amount ]
     * @throws Exception If the order is not selected
     */
    public function getNetAmountForOrders(array $order_ids): array
    {
        $order_items = new order_items_o();
        $tmp = $order_items->getFieldsWhere(
            [
                'order_id' => $order_ids,
                'include_in_invoice' => true,
            ],
            [
                'price',
                'quantity',
                'include_in_invoice',
                'order_id'
            ]
        );
        $net_amounts = [];
        foreach ( $tmp as $item ) {
            $net_amounts[$item['order_id']] = (float)(($net_amounts[$item['order_id']] ?? 0) + ((int)$item['price'] * (int)$item['quantity']));
        }
        return $net_amounts;
    }

    public function includeIncludes(): orders_o
    {
        global /** @var response $response */
        $response;
        $includeEverything = $response->getRequestParameter('include_all') === 'true';
        /** orderItems  */
        if ($response->getRequestParameter('includeOrderItems') || $includeEverything) {
            $response->add_include('orderItems', $this->applyDepartmentPrices($this->getOrderItems($this->id), (int)$this->department_id->value()));
        }

        /** customer */
        if ($response->getRequestParameter('includeCustomer') || $includeEverything) {
            $customer = new users_o();
            $response->add_include('customer', $customer->getUserByCustomerNumber((int)$this->customer_id->value())->includeIncludes()->asArray());
        }

        /** cashier */
        if ($response->getRequestParameter('includeCashier') || $includeEverything) {
            $cashier = new users_o();
            $response->add_include('cashier', $cashier->getUserById($this->cashier_id->value())->asArray());
        }
        /**
         * economicModuleOrders
         */
        if ($response->getRequestParameter('includeEconomicModuleOrders') || $includeEverything) {
            $response->add_include('economicModuleOrders', $this->economic_module_orders->getByOrderId($this->id)->asArray());
        }
        /**
         * stripeModuleOrders
         */
        if ($response->getRequestParameter('includeStripeModuleOrders') || $includeEverything) {
            $response->add_include('stripeModuleOrders', $this->stripe_module_orders->exists() ? $this->stripe_module_orders->asArray() : []);
        }
        return $this;

    }

    /**
     * Apply department pricing to a list of products
     * @param array $order_items
     * @param int $department_id
     * @return array
     */
    public function applyDepartmentPrices(array $order_items, int $department_id): array
    {
        global $response;
        $department = new departments_o();
        $department->getDepartmentById($department_id);
        $department->getDepartmentProductPrices($department_id);
        foreach ( $order_items as $key => $order_item ) {
            $product = new products_o();
            $product->getProductById($order_item['product_id']);
            $order_items[$key]['product']['price'] = $product->getDepartmentPrice($department_id);
        }
        return $order_items;
    }

    public function isPlateSeenBefore(string $reg_1): bool
    {
        // Check if the plate has been seen before
        $count = self::getFieldsWhere([
            'reg_1' => $reg_1,
            'deleted_at' => null,
        ], [
            'reg_1',
        ]);
        return (bool)$count;
    }

    public function getFixedPricingTransactions(int $customer_number, false $asArray, string|null $dateFrom = null, string|null $dateTo = null): array
    {
        // Get the fixed pricing transactions for a customer
        return self::listObjectsWithPagination(
            1,
            100,
            null,
            [
                'customer_id' => $customer_number,
                'deleted_at' => null,
                'cashier_id' => (new collected_order_invoices_o())->economic_wash_subscription_user_id,
                ...(!empty($dateFrom) ? ['created_at-date_from' => $dateFrom] : []),
                ...(!empty($dateTo) ? ['created_at-date_to' => $dateTo] : []),
            ],
            [
                'id' => 'DESC',
            ],
            function ($object) use ($asArray) {
                $res = (new orders_o())->select($object['id']);
                if ($asArray) {
                    return $res->asArray();
                }
                return $res;
            }
        );
    }

    /**
     * @throws Exception
     */
    public function getTankCleaningTransactions(int $customer_number, false $asArray, string $dateFrom, string $dateTo): array
    {
        // Get the tank cleaning transactions for a customer
        return self::listObjectsWithPagination(
            1,
            100,
            null,
            [
                'customer_id' => $customer_number,
                'deleted_at' => null,
                ...(!empty($dateFrom) ? ['created_at-date_from' => $dateFrom] : []),
                ...(!empty($dateTo) ? ['created_at-date_to' => $dateTo] : []),
            ],
            [
                'id' => 'DESC',
            ],
            function ($object) use ($asArray) {
                $res = (new orders_o())->select($object['id']);
                if ($asArray) {
                    return $res->asArray();
                }
                return $res;
            }
        );
    }

    /**
     * @throws Exception
     */
    public function isBooked(bool $useCache = false): bool
    {
        $this->requireSelected();
        // Check if we should use the cached value
        if ($useCache) {
            $cached = self::getCached('isBooked', $this->id);
            if ($cached !== null) {
                return (bool)$cached;
            }
        }
        // Check the invoice collection has been booked
        if ((int)$this->invoice_collection_id->value() > 0) {
            $invoice_collection = (new collected_order_invoices_o())->select((int)$this->invoice_collection_id->value());
            self::requireSelected();
            $isBooked = $invoice_collection->isBooked();
            // Cache the result
            self::cache('isBooked', $isBooked, $this->id);
            self::setCachedExpiration('isBooked', self::$isBookedCacheExpiration, $this->id);
            return $isBooked;
        } else {
            // If there is no invoice collection, the order is not booked
            self::cache('isBooked', false, $this->id);
            self::setCachedExpiration('isBooked', self::$isBookedCacheExpiration, $this->id);
            return false;
        }
    }

    /**
     * Select an order by its wash ID
     * @param int|string|null $WashId The wash ID to select the order by
     * @return orders_o|null The selected order object or null if no order is found
     * @throws Exception
     */
    public function selectByWashId(int|string|null $WashId): orders_o|null
    {
        $result = self::getFieldsWhere([
            'wash_id' => $WashId,
            'deleted_at' => null,
        ], [
            'id'
        ]);
        if (empty($result)) {
            return null; // No order found with the given wash ID
        }
        $this->select((int)$result[0]['id']);
        return $this;
    }

    /**
     * Select an order by its registration number and date range (This is used to find potential duplicate orders)
     * @param int|string|null $RegistrationNumber The registration number to select the order by
     * @param string $dateFrom The start date of the date range (inclusive) "Y-m-d H:i:s" format
     * @param string $dateTo The end date of the date range (inclusive) "Y-m-d H:i:s" format
     * @return orders_o|null The selected order object or null if no order is found
     * @throws Exception If the select fails
     * @see xlvask_usage_log::getPotentialOrder()
     */
    public function selectByRegistrationNumberAndDateRange(int|string|null $RegistrationNumber, string $dateFrom, string $dateTo): orders_o|null
    {
        global $db;
        // Validate the date range
        if (strtotime($dateFrom) === false || strtotime($dateTo) === false) {
            throw new Exception('Invalid date range provided');
        }
        if (strtotime($dateFrom) > strtotime($dateTo)) {
            throw new Exception('The start date cannot be after the end date');
        }
        // Ensure the registration number is a string
        $RegistrationNumber = (string)$RegistrationNumber;
        if (empty($RegistrationNumber)) {
            throw new Exception('Registration number cannot be empty');
        }
        // Prepare the SQL query to find the order by registration number and date range
        $RegistrationNumber = $db->escape_string($RegistrationNumber);
        $dateFrom = $db->escape_string($dateFrom);
        $dateTo = $db->escape_string($dateTo);
        // Select the order by registration number and date range
        $sql = "SELECT id FROM $this->table WHERE (reg_1 = '$RegistrationNumber' OR reg_2 = '$RegistrationNumber' OR reg_3 = '$RegistrationNumber') AND created_at BETWEEN '$dateFrom' AND '$dateTo' AND deleted_at IS NULL";
        $result = $db->query($sql);
        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            return $this->select((int)$row['id']);
        }
        return null; // No order found with the given registration number and date range
    }

    /**
     * Add a new XL Vask order
     * @param users_o $user The user who is placing the order (Billing customer)
     * @param xlvask_usage_log $xlvask_usage_log The XL Vask usage log containing the wash items, department, and registration number
     * @return orders_o The created order object
     * @throws Exception If the order is not selected, or if the user or department is not valid
     */
    public function addXLVaskOrder(users_o $user, xlvask_usage_log $xlvask_usage_log): orders_o
    {
        // Add a new order for XL Vask
        $this->add(
            (int)$user->customer_number->value(),
            2285,
            (string)(new customer_vehicles_o())->getPlateReferenceIfExists($xlvask_usage_log->RegistrationNumber),
            '', // No notes for XL Vask orders
            (int)$xlvask_usage_log->getDepartment()->id,
            $xlvask_usage_log->RegistrationNumber,
        );
        // Set the wash ID for the order
        $this->wash_id->set($xlvask_usage_log->WashId);
        // Set the lane used for the order (if applicable)
        $this->lane->set($xlvask_usage_log->getLane());
        // Set the timestamp for the order creation to the time the wash was performed
        $this->created_at->set($xlvask_usage_log->getTimestampStart());
        // Add the products to the order
        $firstItemId = null;
        /** @var xlvask_wash_item $washItem */
        foreach ( $xlvask_usage_log->WashItems as $washItem ) {
            // Check if the item should be included in the order
            // Check if the product is with id 64 (irrelevant product, a bi product of the wash)
            if (!$washItem->shouldIncludeInOrder() || $washItem->getProduct($xlvask_usage_log)->id === 64) {
                continue; // Skip items that should not be included in the order
            }
            $order_item = new order_items_o();
            $order_item->add(
                $this->id,
                $washItem->getProduct($xlvask_usage_log)->id,
                '',
                '', //$washItem->OriginalProductName,
                2285,
                (int)((float)$washItem->getUnitPriceExVat() * (float)$washItem->Count),
                (int)1,
                $firstItemId === null ? null : $firstItemId, // Set the first item as the parent item (if applicable)
            );
            // Set the first item ID for the next item to link to (provided this is the first item)
            if ($firstItemId === null) {
                $firstItemId = (int)$order_item->id;
            }
        }
        // Validate the order matches the desired total
        $total = $this->getNetAmount();
        if ($total !== $xlvask_usage_log->getTotalPrice()) {
            echo "Order ID: " . $this->id . "\n";
            echo "Customer: " . $user->customer_number->value() . "\n";
            echo "Registration Number: " . $xlvask_usage_log->RegistrationNumber . "\n";
            echo "Wash Items: \n";
            foreach ( $xlvask_usage_log->WashItems as $item ) {
                echo "- " . $item->getProduct($xlvask_usage_log)->name . " (Count: " . $item->Count . ", Price: " . $item->getUnitPriceExVat() . ")\n";
            }
            echo "Expected Total Price: " . $xlvask_usage_log->getTotalPrice() . "\n";
            echo "Actual Total Price: $total\n";
            echo "Order Items: \n";
            foreach ( $this->getOrderItems($this->id) as $order_item ) {
                echo "- " . $order_item['product']['name'] . " (Count: " . $order_item['quantity'] . ", Price: " . $order_item['product']['price'] . ")\n";
            }
            echo "Total: $total, Expected: " . $xlvask_usage_log->getTotalPrice() . "\n";
            throw new Exception('The total amount of the order does not match the expected total. Expected: ' . $xlvask_usage_log->getTotalPrice() . ', Actual: ' . $total);
        }
        // Save the order
        $this->objectChanged();
        // Return the order object
        return $this;
    }

    /**
     * Generate a fake order from a usage log - Used to preview the order before it is created
     * @param xlvask_usage_log $xlvask_usage_log The usage log to generate the order from
     * @param bool $includeItems Whether to include the order items in the simulated order, together with the total net amount
     * @returns array['order' => array, 'order_items' => array]
     * @throws Exception
     */
    public function simulateOrderFromXLVask(xlvask_usage_log $xlvask_usage_log, bool $includeItems = true): array
    {
        $this->id = self::generateFakeId(); // Set a negative ID to indicate this is a simulated order
        $this->getObjectProperties();
        $this->customer_id->set(($xlvask_usage_log->hasBillableCustomer() ? (int)$xlvask_usage_log->CustomerId : 12345679)); // Set the customer ID from the usage log, or 0 if not billable
        $this->cashier_id->set(2285); // Set the cashier ID to a default value for simulation
        $this->reference->set((new customer_vehicles_o())->getPlateReferenceIfExists((string)$xlvask_usage_log->RegistrationNumber)); // Set the reference to the vehicle plate reference (If it exists)
        $this->notes->set(''); // Set notes to an empty string for simulation
        $this->department_id->set($xlvask_usage_log->getDepartment()->id); // Set a default department ID for simulation
        $this->reg_1->set((string)$xlvask_usage_log->RegistrationNumber); // Set the registration number from the usage log
        $this->reg_2->set('');
        $this->reg_3->set('');
        $this->completed_at->set(null); // Set completed_at to null for simulation
        $this->created_at->set($xlvask_usage_log->getTimestampStart()); // Set the created_at to the start time of the usage log
        $this->deleted_at->set(null); // Set deleted_at to null for simulation
        $this->invoice_collection_id->set(null); // Set invoice_collection_id to null for simulation
        $this->booking_id->set(null); // Set booking_id to null for simulation
        $this->wash_id->set($xlvask_usage_log->WashId); // Set the wash ID from the usage log
        $this->lane->set($xlvask_usage_log->getLane()); // Set lane to null for simulation
        if ($includeItems) {
            // If we are including items, simulate the order items from the usage log
            $order_items = $this->simulateOrderItemsFromXLVask($xlvask_usage_log);
            // Save the total net amount for the simulated order (this is the sum of all item prices)
            $this->setTemporaryNetAmount(
                array_sum(array_map(function ($item) {
                    return (int)$item['price'] * (int)$item['quantity'];
                }, $order_items))
            );
        } else {
            $order_items = []; // No items included in the simulated order
            $this->setTemporaryNetAmount(0); // Set the temporary net amount to 0
        }
        return [
            'order' => $this->asArray(true, false), // Return the simulated order as an array
            'order_items' => $order_items, // Return the simulated order items
        ];
    }

    /**
     * Get a list of customers who have placed orders within a specific date range
     * @param string $dateFrom (E.g. "2023-01-01 00:00:00")
     * @param string $dateTo (E.g. "2023-01-31 23:59:59")
     * @return users_o[]
     * @throws Exception
     */
    public function getCustomersWithOrdersInDateRange(string $dateFrom, string $dateTo): array
    {
        global $db;
        // Validate the date range
        if (strtotime($dateFrom) === false || strtotime($dateTo) === false) {
            throw new Exception('Invalid date range provided');
        }
        if (strtotime($dateFrom) > strtotime($dateTo)) {
            throw new Exception('The start date cannot be after the end date');
        }
        // Prepare the SQL query to find customers with orders in the date range (unique customer IDs)
        $dateFrom = $db->escape_string($dateFrom);
        $dateTo = $db->escape_string($dateTo);
        $sql = "SELECT DISTINCT customer_id FROM $this->table WHERE created_at BETWEEN '$dateFrom' AND '$dateTo' AND deleted_at IS NULL";
        $result = $db->query($sql);
        if ($result->num_rows === 0) {
            return []; // No customers found in the date range
        }
        // Fetch all
        $result = $db->fetch_all($result);
        // Get the customers by their customer numbers
        return (new users_o())->getUsersByCustomerNumbers(
            array_map(function ($row) {
                return (int)$row['customer_id'];
            }, $result)
        );
    }

    /**
     * Get transactions for a specific customer within a date range
     * @param int $customerNumber The customer number to filter by
     * @param string $dateFrom The start date of the date range (inclusive) "Y-m-d H:i:s" format
     * @param string $dateTo The end date of the date range (inclusive) "Y-m-d H:i:s" format
     * @return orders_o[] The transactions for the customer within the specified date range
     * @throws Exception If the date range is invalid or if no transactions are found
     */
    public function getTransactionsForCustomer(int $customerNumber, string $dateFrom, string $dateTo): array
    {
        global $db;
        // Validate the date range
        if (strtotime($dateFrom) === false || strtotime($dateTo) === false) {
            throw new Exception('Invalid date range provided');
        }
        if (strtotime($dateFrom) > strtotime($dateTo)) {
            throw new Exception('The start date cannot be after the end date');
        }
        // Prepare the SQL query to find transactions for the customer in the date range
        $dateFrom = $db->escape_string($dateFrom);
        $dateTo = $db->escape_string($dateTo);
        $sql = "SELECT id FROM $this->table WHERE customer_id = $customerNumber AND created_at BETWEEN '$dateFrom' AND '$dateTo' AND deleted_at IS NULL";
        $result = $db->query($sql);
        if ($result->num_rows === 0) {
            return []; // No transactions found for the customer in the date range
        }
        $transactions = [];
        while ($row = $result->fetch_assoc()) {
            $order = new orders_o();
            $order->select((int)$row['id']);
            $transactions[] = $order;
        }
        return $transactions;
    }

    /**
     * Get transactions for customers in a date range
     * @param int[] $customers An array of customer numbers to filter by
     * @param string $dateFrom The start date of the date range (inclusive) "Y-m-d H:i:s" format
     * @param string $dateTo The end date of the date range (inclusive) "Y-m-d H:i:s" format
     * @return array[customer_number => orders_o[]] The transactions for each customer within the specified date range
     * @throws Exception If the date range is invalid or if no transactions are found
     */
    public function getTransactionsForCustomersInDateRange(array $customers, string $dateFrom, string $dateTo): array
    {
        global $db;
        // Validate the date range
        if (strtotime($dateFrom) === false || strtotime($dateTo) === false) {
            throw new Exception('Invalid date range provided');
        }
        if (strtotime($dateFrom) > strtotime($dateTo)) {
            throw new Exception('The start date cannot be after the end date');
        }
        // Prepare the SQL query to find transactions for the customers in the date range
        $dateFrom = $db->escape_string($dateFrom);
        $dateTo = $db->escape_string($dateTo);
        $customerNumbers = implode(',', array_map('intval', $customers));
        $sql = "SELECT id, customer_id FROM $this->table WHERE customer_id IN ($customerNumbers) AND created_at BETWEEN '$dateFrom' AND '$dateTo' AND deleted_at IS NULL";
        $result = $db->query($sql);
        if ($result->num_rows === 0) {
            return []; // No transactions found for the customers in the date range
        }
        $transactions = [];
        while ($row = $result->fetch_assoc()) {
            $order = new orders_o();
            $order->select((int)$row['id']);
            $transactions[(int)$row['customer_id']][] = $order;
        }
        return $transactions;
    }

    /**
     * Get orders with possible duplicates in a date range
     * @param string $dateFrom The start date of the date range (inclusive) "Y-m-d H:i:s" format
     * @param string $dateTo The end date of the date range (inclusive) "Y-m-d H:i:s" format
     * @return array An array of possible duplicate orders, keyed by registration number.
     * @throws Exception
     */
    public function getOrdersWithPossibleDuplicates(string $dateFrom, string $dateTo): array
    {
        global $db;
        // Validate the date range
        if (strtotime($dateFrom) === false || strtotime($dateTo) === false) {
            throw new Exception('Invalid date range provided');
        }
        if (strtotime($dateFrom) > strtotime($dateTo)) {
            throw new Exception('The start date cannot be after the end date');
        }
        // Prepare the SQL query to find orders with possible duplicates in the date range
        $dateFrom = $db->escape_string($dateFrom);
        $dateTo = $db->escape_string($dateTo);
        $sql = "SELECT id, customer_id, reg_1, reg_2, reg_3, created_at FROM $this->table WHERE created_at BETWEEN '$dateFrom' AND '$dateTo' AND deleted_at IS NULL";
        $result = $db->query($sql);
        if ($result->num_rows === 0) {
            return []; // No orders found in the date range
        }
        $orders = [];
        while ($row = $result->fetch_assoc()) {
            if (empty($row['reg_1'])) {
                continue; // Skip orders without a registration number
            }
            // Prepare the order data
            $tmp = [
                'id' => (int)$row['id'],
                'reg_1' => (string)$row['reg_1'],
                'reg_2' => (string)$row['reg_2'] ?? '',
                'reg_3' => (string)$row['reg_3'] ?? '',
                'created_at' => (string)$row['created_at'],
            ];
            // Add the order to the list
            $orders[$tmp['reg_1']][] = [
                'id' => $tmp['id'],
                'reg_1' => $tmp['reg_1'],
                'reg_2' => (string)$row['reg_2'] ?? '',
                'reg_3' => (string)$row['reg_3'] ?? '',
                'created_at' => $tmp['created_at'],
                'object' => (new orders_o())->select((int)$tmp['id'])
            ];
        }
        // Filter out orders with more than one entry for the same registration numbers (in a 24 hour period)
        $possibleDuplicates = [];
        // Loop through the registration numbers
        foreach ( $orders as $reg_1 => $orderList ) {
            // If there are more than one order for the same registration number, add it to the possible duplicates
            if (count($orderList) > 1) {

                // Loop through the orders and check if they are within 24 hours of each other
                $filteredOrders = [];
                foreach ( $orderList as $order ) {
                    // Check if the order is within 24 hours of the previous order (if any)
                    if (empty($filteredOrders)) {
                        $filteredOrders[] = $order; // Add the first order
                    } else {
                        // Check if the order is within 24 hours of the previous order
                        $firstOrderTime = strtotime($filteredOrders[0]['created_at']);
                        $currentOrderTime = strtotime($order['created_at']);
                        if ($currentOrderTime - $firstOrderTime <= 86400) { // 86400 seconds = 24 hours
                            $filteredOrders[] = $order; // Add the order to the filtered list
                        }
                    }
                }
                // If there are more than one order in the filtered list, add it to the possible duplicates
                if (count($filteredOrders) > 1) {
                    $possibleDuplicates[$reg_1] = $filteredOrders;
                }
            }
        }
        // Return the possible duplicates
        return $possibleDuplicates;
    }

    public function setTemporaryNetAmount(float $amount): void
    {
        // Set a temporary net amount for the order
        $this->temporary_net_amount = $amount;
    }

    /**
     * @param xlvask_usage_log $xlvask_usage_log
     * @return array
     * @throws Exception
     */
    public function simulateOrderItemsFromXLVask(xlvask_usage_log $xlvask_usage_log): array
    {
// Add the products to the simulated order
        $firstItemId = null; // Initialize the first item ID to null
        $current_item_id = null;
        $order_items = []; // Initialize the order items array
        /** @var xlvask_wash_item $washItem */
        foreach ( $xlvask_usage_log->WashItems as $washItem ) {
            // Check if the item should be included in the order
            // Check if the product is with id 64 (irrelevant product, a bi product of the wash)
            $product = $washItem->getProduct($xlvask_usage_log);

            if (!$washItem->shouldIncludeInOrder() || $product->id === 64) {
                unset($washItem);
                continue; // Skip items that should not be included in the order
            }
            $order_item = new order_items_o();
            $order_item->id = self::generateFakeId(); // Set a negative ID to indicate this is a simulated order item
            // If the id is positive, forcefully quit
            if ($order_item->id > 0) {
                throw new Exception('The order item ID must be negative for simulation');
            }
            $order_item->getObjectProperties();
            $order_item->order_id->set($this->id); // Set the order ID to the simulated order ID
            $order_item->product_id->set((int)$product->id); // Set the product ID to the product ID from the wash item
            $order_item->reference->set('');
            // Get the product price based on the department
            $product_price = (int)$product->getDepartmentPrice((int)$this->department_id->value()); // Get the department price for the product
            // Get the customers custom price discount percentage
            $user = $xlvask_usage_log->getUser(); // Get the user from the usage log
            if (!$user->exists()) {
                throw new Exception('No user found matching the customer number in the usage log');
            }
            $product_price_discount_percentage = (int)$user->getProductDiscountPercentage((int)$order_item->product_id->value()); // Get the custom price discount percentage for the product
            // Apply the discount percentage to the product price
            $product_price = (int)round($product_price * (1 - ($product_price_discount_percentage / 100))); // Apply the discount percentage to the product price
            $order_item->notes->set(null); // Set notes for the simulated order item
            $order_item->price->set((int)$product_price); // Set the price based on the product price and discount percentage
            $order_item->quantity->set((int)$washItem->Count); // Set the quantity based on the wash item
            $order_item->related_item_id->set($firstItemId === null ? null : $firstItemId); // Set the related item ID to the first item ID (if applicable)
            $order_item->include_in_invoice->set(true); // Set include_in_invoice to true for the simulated order item
            $order_item->cashier_id->set($this->cashier_id->value()); // Set the cashier ID to the simulated order's cashier ID
            // Set the first item ID for the next item to link to (provided this is the first item)
            if ($firstItemId === null) { 
                $firstItemId = $order_item->id; // Set the first item ID to the current item ID
            }
            $order_items[] = $order_item->getItemAsArray(); // Add the simulated order item to the order items array
            // clear up memory
            unset($order_item);
        }
        return $order_items;
    }

    public function getOrderPotentialDuplicatesIfFound(string $reg_1, string $reg_2, string $reg_3, int $department_id, string $created_at): array
    {
        global /** @var db $db */
        $db;
        // Validate the registration numbers
        $reg_1 = $db->escape_string($reg_1);
        $reg_2 = $db->escape_string($reg_2);
        $reg_3 = $db->escape_string($reg_3);
        // Strip any whitespace from the registration numbers
        $reg_1 = trim($reg_1);
        $reg_2 = trim($reg_2);
        $reg_3 = trim($reg_3);
        // Created at must be between:
        $created_at_from = date('Y-m-d H:i:s', strtotime($created_at . ' - 24 hours'));
        $created_at_to = date('Y-m-d H:i:s', strtotime($created_at . ' + 24 hours'));
        // Validate the department ID
        if ($department_id <= 0) {
            throw new Exception('Invalid department ID provided');
        }
        if (empty($reg_1)) {
            return []; // No registration number provided, return empty array
        }
        // Select the unique order ids that match the registration numbers and department ID within the 24 hour range
        $sql = "SELECT id FROM $this->table WHERE (reg_1 = '$reg_1') AND department_id = $department_id AND created_at BETWEEN '$created_at_from' AND '$created_at_to' AND deleted_at IS NULL";

        //$sql = "SELECT id FROM $this->table WHERE (reg_1 = '$reg_1' AND reg_2 = '$reg_2' AND reg_3 = '$reg_3') AND department_id = $department_id AND created_at BETWEEN '$created_at_from' AND '$created_at_to' AND deleted_at IS NULL";
        // Execute the query
        $result = $db->query($sql);
        if ($result->num_rows === 0) {
            return []; // No potential duplicates found
        }
        // Fetch all potential duplicates
        $result = $db->fetch_all($result);
        return array_map(function ($row) {
            $order = new orders_o();
            $order->select((int)$row['id']);
            return [...$order->asArray(), 'order_items' => $order->getOrderItems($order->id)]; // Include order items in the result
        }, $result); // Return the potential duplicates as an array of order arrays
    }

    public function getCustomerProductPrice(products_o $product): int
    {
        self::requireSelected();
        // Get the current user
        $current_user = $this->getCustomer();
        // Get the product price for the current customer, taking into account any custom pricing
        if (!$current_user->exists()) {
            throw new Exception('No current user found');
        }
        $price = (int)$product->getDepartmentPrice((int)$this->department_id->value()); // Get the department price for the product
        $discount_percentage = (int)$current_user->getProductDiscountPercentage((int)$product->id); // Get the custom price discount percentage for the product
        // Apply the discount percentage to the product price
        // Apply the discount percentage to the product price
        return (int)round($price * (1 - ($discount_percentage / 100)));
    }

    /**
     * @throws Exception
     */
    public function getCustomer(): users_o
    {
        self::requireSelected();
        $user = new users_o();
        $user->getUserByCustomerNumber((int)$this->customer_id->value());
        return $user;
    }

    /**
     * Set or unset the pending handheld cache indicator for the order
     * This indicator is used to mark orders that are pending handheld processing
     * The indicator is stored in the cache and expires at midnight
     * @param bool $isPending Whether to set (true) or unset (false) the pending handheld indicator
     * @throws Exception If the order is not selected
     */
    public function setPendingHandheldIndicator(bool $isPending = true): void
    {
        self::requireSelected();
        // Set or unset the pending handheld cache indicator for the order
        $cache_key = 'pending_handheld_cache_indicator';
        if ($isPending) {
            // Get the amount of seconds until midnight
            $secondsUntilMidnight = (int)(strtotime('tomorrow') - time());
            $this->cache($cache_key, 1);
            $this->setCachedExpiration($cache_key, $secondsUntilMidnight);
            $this->using_hand_held->set(true); // Mark the order as using handheld
        } else {
            $this->deleteCached($cache_key);
        }
    }

    /**
     * Get whether the order is marked as pending handheld processing
     * @return bool True if the order is marked as pending handheld, false otherwise
     * @throws Exception If the order is not selected
     */
    public function isPendingHandheld(): bool
    {
        self::requireSelected();
        // Check if the pending handheld cache indicator is set for the order
        $cache_key = 'pending_handheld_cache_indicator';
        return $this->getCached($cache_key) === 1;
    }

    /**
     * @throws Exception
     */
    public function getNetAmountForOrderItemsOriginal(): int
    {
        // Get the original net amount for the order items, ignoring any temporary net amount set
        self::requireSelected();
        $order_items = $this->getOrderItems((int)$this->id);
        //echo 'Calculating net amount for order ID ' . $this->id . ' with ' . count($order_items) . " items\n";
        return array_sum(array_map(/**
         * @throws Exception
         */ function ($item) {
            if ( (int)$item['price'] > 0 && (int)$item['quantity'] > 0) {
                return (int)$item['price'] * (int)$item['quantity'];
            }
            // Otherwise we need to get the product price
            $product = (new products_o())->select((int)$item['product_id']);
            // Apply the customer discount if applicable
            $department_price_original = (int)$product->getDepartmentPrice((int)$this->department_id->value());
            // Get the customers user object
            $tmp_user = (new users_o())->getUserByCustomerNumber((int)$this->customer_id->value());
            // Get the discount percentage for the customer
            $discount = $tmp_user->getCustomPrice((int)$product->id, false);
            // Calculate the final price after discount
            $post_discount = (int)round($department_price_original * (1 - ($discount / 100))) * (int)$item['quantity'];
            //echo 'Adding ' . $post_discount . ' for product ' . $product->name . ' (Original price: ' . $department_price_original . ', Discount: ' . $discount . '%, Quantity: ' . (int)$item['quantity'] . ")\n";
            $actual_price = (int)$item['price'];
            if ($actual_price !== $post_discount) {
                //echo "Warning: The actual price ($actual_price) does not match the calculated price ($post_discount) for product " . $product->name . "\n";
            }
            return $post_discount;
        }, $order_items));
    }

    /**
     * @throws Exception
     */
    public function isOwnOrder(int $customer_id): bool
    {
        self::requireSelected();
        return (int)$this->customer_id->value() === $customer_id;
    }

    /**
     * @throws Exception
     */
    public function hasWashCertificateAttached(): bool
    {
        self::requireSelected();
        // Check if the order has a wash certificate attached
        $attachments = $this->listAttachments();
        foreach ( $attachments as $attachment ) {
            if ($attachment->isWashCertificate()) {
                return true; // Wash certificate found
            }
        }
        return false; // No wash certificate product found in the order items
    }

    /**
     * Generate and attach a wash certificate directly on an order (without a booking)
     * @param int|null $safety_seal Optional safety seal number
     * @param string|null $operator Optional operator/employee name who carried out the wash
     * @param string|DateTime|null $date Optional date of the wash (defaults to current date)
     * @throws Exception If the order is not selected or required related objects are missing
     */
    public function generateWashCertificate(int|null $safety_seal = null, string|null $operator = null, $date = null): void
    {
        self::requireSelected();
        // Avoid generating duplicate certificates
        if ($this->hasWashCertificateAttached()) {
            return;
        }
        // Get the department for the order
        $department = (new departments_o())->select((int)$this->department_id->value());
        if (!$department->exists()) {
            throw new Exception('Department not found');
        }
        // Get branding for the department
        $branding = (new branding_o())->select((int)$department->branding->value());
        if (!$branding->exists()) {
            throw new Exception('Branding not found');
        }
        // Get the customer for the order
        $customer = (new users_o())->getUserByCustomerNumber((int)$this->customer_id->value());
        $department_array = $department->asArray();
        $department_array['branding'] = $branding->asArray();
        $customer_array = $customer->asArray();
        $order_array = $this->asArray();
        // Format the date as 17:35 02-12-2025
        $date = ($date instanceof DateTime ? $date : ($date !== null ? new DateTime($date) : new DateTime()));
        $date_formatted = ($date instanceof DateTime ? $date->format('d-m-Y') : date('d-m-Y'));
        $time_formatted = ($date instanceof DateTime ? $date->format('H:i') : date('H:i'));

        // Generate PDF
        $pdf_generator = new pdf_generator();
        $pdf_generator->add_html(
            $pdf_generator->templates->getTemplate('wash_certificate')
                ->setCompany([
                    'name' => 'Truck Wash',
                    'address' => 'Letland Allé 2',
                    'zip' => 2630,
                    'city' => 'Taastrup',
                    'phone_prefix' => 45,
                    'phone' => 43717886,
                    'email' => 'cph@truckwash.dk',
                    'website' => 'www.truckwash.dk',
                    'images' => [
                        'logo' => '/truckwash-banner-png.png',
                        'banner' => '/truckwash-banner-png.png',
                        'signature' => '/truckwash-underskrift.png',
                    ],
                ])
                ->addData([
                    'booking_number' => $this->id, // Used as document number on the template
                    'seal_number' => ($safety_seal ?? null),
                    'reg_1' => $order_array['reg_1'],
                    'reg_2' => $order_array['reg_2'],
                    'date' => $date_formatted,
                    'time' => $time_formatted,
                    'carried_out_by' => ($operator ?? null),
                    'department_id' => $department->id,
                    'department_name' => $department_array['branding']['name'] ?: $department_array['name'],
                    'department_address' => $department_array['branding']['address'] ?: $department_array['description'],
                    'customer_name' => $customer->getCustomerName($customer_array['customer_number']),
                    'customer_address' => $customer->getCustomerEcocomicData($customer_array['customer_number'])->economic_customer->address ?: '-',
                    'wash_type' => 'ORDER_WASH'
                ])
                ->getHtml()
        );
        $pdf_path = $pdf_generator->generate_pdf();

        // Attach PDF to the order
        $this->addAttachment(new attachment_content((object)[
            'document' => $pdf_path,
            'other' => attachment_content::OTHER_TYPE_WASH_CERTIFICATE,
        ]));
    }

    /**
     * @throws Exception
     */
    public function createOrderFromBooking(int $id, int $user_id): orders_o
    {
        // Create an order from a booking
        $booking = (new order_bookings_o())->select($id);
        $booking->requireSelected();
        // Add the order
        $new_order = $this->add(
            (int)$booking->customer_number->value(),
            $user_id,
            (string)$booking->reference->value(),
            (string)$booking->note->value(),
            (int)$booking->department->value(),
            (string)$booking->reg_1->value(),
            (string)$booking->reg_2->value(),
            (string)$booking->reg_3->value(),
        );
        // Set the PO (if any)
        if (!empty($booking->po->value())) {
            $new_order->po->set((string)$booking->po->value());
        }
        $this->select((int)$new_order->id);
        return $this; 
    }

    /**
     * If the transaction should be included in invoicing, based on the department.
     * Some departments have a setting to exclude them from invoicing.
     * @return bool
     * @throws Exception If the order is not selected
     */
    public function isIncludedInInvoicing(): bool
    {
        self::requireSelected();
        return !(new departments_o())->select((int)$this->department_id->value())->isExcludedFromInvoicing();
    }

    /**
     * If the order is linked to a booking, get the booking object
     * @return order_bookings_o|null The booking object if linked, null otherwise
     * @throws Exception
     */
    public function getOrderBooking(): order_bookings_o|null
    {
        self::requireSelected();
        if ((int)$this->booking_id->value() <= 0) {
            return null;
        }
        $booking = new order_bookings_o();
        $booking->select((int)$this->booking_id->value());
        return $booking->exists() ? $booking : null;
    }

    public function countWashesInDateRange(string $date_start, string $date_end, int $department_id): int
    {
        global /** @var db $db */
        $db;
        // Validate the date range
        if (strtotime($date_start) === false || strtotime($date_end) === false) {
            throw new Exception('Invalid date range provided');
        }
        if (strtotime($date_start) > strtotime($date_end)) {
            throw new Exception('The start date cannot be after the end date');
        }
        // Prepare the SQL query to count washes in the date range for the department
        $date_start = $db->escape_string($date_start);
        $date_end = $db->escape_string($date_end);
        // Get the amount of orders with at least one order item that has a product with the is_wash column set to true
        $sql = "SELECT COUNT(DISTINCT o.id) AS wash_count
                FROM $this->table o
                JOIN order_items oi ON oi.order_id = o.id
                JOIN products p ON p.id = oi.product_id
                WHERE o.department_id = $department_id
                  AND o.created_at BETWEEN '$date_start' AND '$date_end'
                  AND o.deleted_at IS NULL
                  AND p.is_wash = 1";
        $result = $db->query($sql);
        if ($result->num_rows === 0) {
            return 0; // No washes found in the date range
        }
        $row = $result->fetch_assoc();
        return (int)$row['wash_count'];
    }

    /**
     * @throws Exception
     * @retuns order_items_o[]
     */
    public function getOrderItemObjects(): array
    {
        self::requireSelected();
        $order_items = $this->getOrderItems((int)$this->id);
        $order_item_objects = [];
        foreach ( $order_items as $item ) {
            $order_item = new order_items_o();
            $order_item->select((int)$item['id']);
            $order_item_objects[] = $order_item;
        }
        return $order_item_objects;
    }

    /**
     * @param \DateTime|string $daily_start E.g. 2025-01-01 08:00:00
     * @param \DateTime|string $daily_end E.g. 2025-01-01 18:00:00
     * $options array An optional array of options to filter the results. Supported options:
     * - department_id (int): Filter orders by a specific department ID.
     * @return orders_o[] An array of orders that have washes within the specified daily time range, regardless of the date. The time range is specified in "H:i:s" format (e.g. "08:00:00" for 8 AM and "18:00:00" for 6 PM).
     * @throws Exception
     */
    public function getWashesInTimeRange(\DateTime|string $daily_start, \DateTime|string $daily_end, array $options = []): array
    {        global /** @var db $db */
        $db;
        // Convert daily_start and daily_end to time strings if they are DateTime objects
        if ($daily_start instanceof \DateTime) {
            $daily_start = $daily_start->format('Y-m-d H:i:s');
        }
        if ($daily_end instanceof \DateTime) {
            $daily_end = $daily_end->format('Y-m-d H:i:s');
        }
        // Validate the time range
        if (strtotime($daily_start) === false || strtotime($daily_end) === false) {
            throw new Exception('Invalid time range provided');
        }
        if (strtotime($daily_start) > strtotime($daily_end)) {
            throw new Exception('The start time cannot be after the end time');
        }
        // Prepare the SQL query to find orders with washes in the daily time range
        $daily_start = $db->escape_string($daily_start);
        $daily_end = $db->escape_string($daily_end);
        // Options for filtering by department ID
        $department_filter = '';
        if (isset($options['department_id']) && is_int($options['department_id'])) {
            $department_id = $options['department_id'];
            $department_filter = "AND o.department_id = $department_id";
        }

        $sql = "SELECT DISTINCT o.id
                FROM $this->table o
                JOIN order_items oi ON oi.order_id = o.id
                JOIN products p ON p.id = oi.product_id
                WHERE TIME(o.created_at) BETWEEN TIME('$daily_start') AND TIME('$daily_end')
                AND o.created_at BETWEEN '$daily_start' AND '$daily_end'
                  AND o.deleted_at IS NULL
                  AND p.is_wash = 1
                  $department_filter";
        //echo "Executing SQL query to find washes in time range: $sql\n"; // Debug log to check the generated SQL query
        $result = $db->query($sql);
        if ($result->num_rows === 0) {
            return []; // No washes found in the time range
        }
        $orders = [];
        while ($row = $result->fetch_assoc()) {
            $order = new orders_o();
            $order->select((int)$row['id']);
            $orders[] = $order;
        }
        return $orders;
    }
}