<?php

class Order extends ObjectModel
{
    public $id;
    public $id_order;
    public $id_cart;
    public $id_customer;
    public $id_address_delivery;
    public $id_address_invoice;
    public $id_currency;
    public $id_lang;
    public $id_carrier;
    public $current_state;
    public $payment;
    public $module;
    public $total_paid;
    public $total_paid_tax_incl;
    public $total_paid_tax_excl;
    public $total_products;
    public $total_products_wt;
    public $total_shipping;
    public $total_shipping_tax_incl;
    public $total_shipping_tax_excl;
    public $total_discounts;
    public $total_discounts_tax_incl;
    public $total_discounts_tax_excl;
    public $invoice_number;
    public $delivery_number;
    public $invoice_date;
    public $delivery_date;
    public $date_add;
    public $date_upd;
    public $valid;
    public $reference;
    public $secure_key;
    public $recyclable;
    public $gift;
    public $gift_message;
    public $mobile_theme;
    public $shipping_number;
    public $round_mode;
    public $round_type;
    public $conversion_rate;
    public $id_shop;
    public $id_shop_group;
    public $order_rows = [];

    public static $definition = [
        'table' => 'orders',
        'primary' => 'id_order',
        'fields' => [
            'id_cart' => ['type' => self::TYPE_INT, 'required' => true],
            'id_customer' => ['type' => self::TYPE_INT, 'required' => true],
            'id_address_delivery' => ['type' => self::TYPE_INT, 'required' => true],
            'id_address_invoice' => ['type' => self::TYPE_INT, 'required' => true],
            'id_currency' => ['type' => self::TYPE_INT, 'required' => true],
            'id_lang' => ['type' => self::TYPE_INT],
            'id_carrier' => ['type' => self::TYPE_INT],
            'current_state' => ['type' => self::TYPE_INT],
            'payment' => ['type' => self::TYPE_STRING],
            'module' => ['type' => self::TYPE_STRING],
            'total_paid' => ['type' => self::TYPE_FLOAT],
            'total_paid_tax_incl' => ['type' => self::TYPE_FLOAT],
            'total_paid_tax_excl' => ['type' => self::TYPE_FLOAT],
            'total_products' => ['type' => self::TYPE_FLOAT],
            'total_products_wt' => ['type' => self::TYPE_FLOAT],
            'total_shipping' => ['type' => self::TYPE_FLOAT],
            'total_shipping_tax_incl' => ['type' => self::TYPE_FLOAT],
            'total_shipping_tax_excl' => ['type' => self::TYPE_FLOAT],
            'total_discounts' => ['type' => self::TYPE_FLOAT],
            'total_discounts_tax_incl' => ['type' => self::TYPE_FLOAT],
            'total_discounts_tax_excl' => ['type' => self::TYPE_FLOAT],
            'invoice_number' => ['type' => self::TYPE_INT],
            'delivery_number' => ['type' => self::TYPE_INT],
            'invoice_date' => ['type' => self::TYPE_DATE],
            'delivery_date' => ['type' => self::TYPE_DATE],
            'date_add' => ['type' => self::TYPE_DATE],
            'date_upd' => ['type' => self::TYPE_DATE],
            'valid' => ['type' => self::TYPE_BOOL],
            'reference' => ['type' => self::TYPE_STRING],
            'secure_key' => ['type' => self::TYPE_STRING],
            'recyclable' => ['type' => self::TYPE_BOOL],
            'gift' => ['type' => self::TYPE_BOOL],
            'gift_message' => ['type' => self::TYPE_STRING],
            'mobile_theme' => ['type' => self::TYPE_BOOL],
            'shipping_number' => ['type' => self::TYPE_STRING],
            'round_mode' => ['type' => self::TYPE_INT],
            'round_type' => ['type' => self::TYPE_INT],
            'conversion_rate' => ['type' => self::TYPE_FLOAT],
            'id_shop' => ['type' => self::TYPE_INT],
            'id_shop_group' => ['type' => self::TYPE_INT],
        ],
    ];

    public function __construct($idOrder = null, $idLang = null, $idShop = null)
    {
        parent::__construct($idOrder, $idLang, $idShop);
    }

    // CRUD-Operationen
    public function add($autoDate = true, $nullValues = false)
    {
        $ret = parent::add($autoDate, $nullValues);
        if ($ret && !empty($this->order_rows)) {
            $this->setWsOrderRows($this->order_rows);
        }
        return $ret;
    }

    public function update($nullValues = false)
    {
        return parent::update($nullValues);
    }

    public function delete()
    {
        // Bestellpositionen löschen
        $this->setWsOrderRows([]);
        return parent::delete();
    }

    // Bestellungslisten
    public static function getOrders($idCustomer = null, $dateFrom = null, $dateTo = null)
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'orders WHERE 1=1';
        if ($idCustomer) {
            $sql .= ' AND id_customer = ' . (int)$idCustomer;
        }
        if ($dateFrom) {
            $sql .= ' AND date_add >= "' . pSQL($dateFrom) . '"';
        }
        if ($dateTo) {
            $sql .= ' AND date_add <= "' . pSQL($dateTo) . '"';
        }
        $sql .= ' ORDER BY date_add DESC';
        return Db::getInstance()->executeS($sql);
    }

    public static function getOrdersByDate($dateFrom, $dateTo)
    {
        return self::getOrders(null, $dateFrom, $dateTo);
    }

    // Produktdetails
    public function getProducts()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_detail WHERE id_order = ' . (int)$this->id;
        return Db::getInstance()->executeS($sql);
    }

    public function getProductsDetail()
    {
        $sql = 'SELECT od.*, p.* FROM ' . _DB_PREFIX_ . 'order_detail od
                LEFT JOIN ' . _DB_PREFIX_ . 'product p ON od.product_id = p.id_product
                WHERE od.id_order = ' . (int)$this->id;
        return Db::getInstance()->executeS($sql);
    }

    // Verlaufsverwaltung
    public function getHistory()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_history WHERE id_order = ' . (int)$this->id . ' ORDER BY date_add DESC';
        return Db::getInstance()->executeS($sql);
    }

    public function addHistory($idOrderState, $employeeId = null)
    {
        $data = [
            'id_order' => $this->id,
            'id_order_state' => $idOrderState,
            'id_employee' => $employeeId,
            'date_add' => date('Y-m-d H:i:s'),
        ];
        return Db::getInstance()->insert('order_history', $data);
    }

    // Rechnungsverwaltung
    public function getInvoices()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_invoice WHERE id_order = ' . (int)$this->id;
        return Db::getInstance()->executeS($sql);
    }

    public function getDeliverySlips()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_invoice WHERE id_order = ' . (int)$this->id . ' AND delivery_number IS NOT NULL';
        return Db::getInstance()->executeS($sql);
    }

    // Summenberechnung
    public function getTotalPaid()
    {
        return $this->total_paid;
    }

    public function getTotalProducts()
    {
        return $this->total_products;
    }

    // Statusverwaltung
    public function getCurrentState()
    {
        return $this->current_state;
    }

    public function setCurrentState($idOrderState)
    {
        $this->current_state = $idOrderState;
        $this->update();
        $this->addHistory($idOrderState);
    }

    // Versand- und Steuerverwaltung
    public function getShipping()
    {
        return $this->total_shipping;
    }

    public function getTaxes()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_detail_tax WHERE id_order = ' . (int)$this->id;
        return Db::getInstance()->executeS($sql);
    }

    // Webservice-API
    public function getWsOrderRows()
    {
        return $this->getProducts();
    }

    public function setWsOrderRows($orderRows)
    {
        // Bestehende löschen
        Db::getInstance()->delete('order_detail', 'id_order = ' . (int)$this->id);
        // Neue hinzufügen
        foreach ($orderRows as $row) {
            $row['id_order'] = $this->id;
            Db::getInstance()->insert('order_detail', $row);
        }
        return true;
    }

    // Erweiterte Bestellungsfunktionen
    public function getTaxCalculationMethod()
    {
        return (int) Configuration::get('PS_TAX');
    }

    public function deleteProduct($order, $order_detail, $quantity)
    {
        if (!(int) $quantity) {
            return false;
        }

        return $this->_deleteProduct($order_detail, $quantity);
    }

    protected function _deleteProduct($order_detail, $quantity)
    {
        $product_price_tax_excl = $order_detail->unit_price_tax_excl * $quantity;
        $product_price_tax_incl = $order_detail->unit_price_tax_incl * $quantity;

        $this->total_products -= $product_price_tax_excl;
        $this->total_products_wt -= $product_price_tax_incl;

        $this->total_paid -= $product_price_tax_incl;
        $this->total_paid_tax_incl -= $product_price_tax_incl;
        $this->total_paid_tax_excl -= $product_price_tax_excl;

        return $this->update();
    }

    public function getCartProducts()
    {
        if (!$this->id_cart) {
            return [];
        }

        $products = [];
        $sql = 'SELECT cp.*, p.*, pl.*, i.*, cl.*, t.*, cp.id_product_attribute, pa.reference as product_attribute_reference
                FROM ' . _DB_PREFIX_ . 'cart_product cp
                LEFT JOIN ' . _DB_PREFIX_ . 'product p ON p.id_product = cp.id_product
                LEFT JOIN ' . _DB_PREFIX_ . 'product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = ' . (int) Context::getContext()->language->id . ')
                LEFT JOIN ' . _DB_PREFIX_ . 'image i ON (i.id_product = p.id_product AND i.cover = 1)
                LEFT JOIN ' . _DB_PREFIX_ . 'category_lang cl ON (p.id_category_default = cl.id_category AND cl.id_lang = ' . (int) Context::getContext()->language->id . ')
                LEFT JOIN ' . _DB_PREFIX_ . 'tax t ON t.id_tax = p.id_tax_rules_group
                LEFT JOIN ' . _DB_PREFIX_ . 'product_attribute pa ON (pa.id_product_attribute = cp.id_product_attribute)
                WHERE cp.id_cart = ' . (int) $this->id_cart;

        $result = Db::getInstance()->executeS($sql);

        if ($result) {
            foreach ($result as $row) {
                $row['id_product_attribute'] = (int) $row['id_product_attribute'];
                $row['cart_quantity'] = (int) $row['cart_quantity'];
                $products[] = $row;
            }
        }

        return $products;
    }

    public function deleteCustomization($id_customization, $quantity, $order_detail)
    {
        if (!(int) $quantity) {
            return false;
        }

        $customization = Db::getInstance()->getRow('
            SELECT cu.*, c.*
            FROM ' . _DB_PREFIX_ . 'customization cu
            LEFT JOIN ' . _DB_PREFIX_ . 'customized_data c ON (cu.id_customization = c.id_customization)
            WHERE cu.id_customization = ' . (int) $id_customization);

        if ($customization) {
            return $this->_deleteProduct($order_detail, $quantity);
        }

        return true;
    }

    public function getHistory($id_lang, $id_order_state = false, $no_hidden = false, $filters = 0)
    {
        if (!$id_lang) {
            $id_lang = Context::getContext()->language->id;
        }

        $sql = 'SELECT os.*, osh.*, e.*, el.*
                FROM ' . _DB_PREFIX_ . 'order_history osh
                LEFT JOIN ' . _DB_PREFIX_ . 'order_state os ON (os.id_order_state = osh.id_order_state)
                LEFT JOIN ' . _DB_PREFIX_ . 'employee e ON (e.id_employee = osh.id_employee)
                LEFT JOIN ' . _DB_PREFIX_ . 'employee_lang el ON (el.id_employee = e.id_employee AND el.id_lang = ' . (int) $id_lang . ')
                WHERE osh.id_order = ' . (int) $this->id;

        if ($id_order_state) {
            $sql .= ' AND osh.id_order_state = ' . (int) $id_order_state;
        }

        if ($no_hidden) {
            $sql .= ' AND os.hidden = 0';
        }

        $sql .= ' ORDER BY osh.date_add DESC';

        return Db::getInstance()->executeS($sql);
    }

    public static function cleanHistoryCache()
    {
        return Db::getInstance()->execute('DELETE FROM ' . _DB_PREFIX_ . 'order_history WHERE id_order NOT IN (SELECT id_order FROM ' . _DB_PREFIX_ . 'orders)');
    }

    public function getProductsDetail()
    {
        return $this->getProducts();
    }

    public function getFirstMessage()
    {
        $sql = 'SELECT m.*, c.*, cl.*
                FROM ' . _DB_PREFIX_ . 'message m
                LEFT JOIN ' . _DB_PREFIX_ . 'customer c ON (c.id_customer = m.id_customer)
                LEFT JOIN ' . _DB_PREFIX_ . 'customer_lang cl ON (cl.id_customer = c.id_customer AND cl.id_lang = ' . (int) Context::getContext()->language->id . ')
                WHERE m.id_order = ' . (int) $this->id . '
                ORDER BY m.date_add ASC';

        return Db::getInstance()->getRow($sql);
    }

    public function setProductPrices(&$row)
    {
        $tax_calculator = OrderDetail::getTaxCalculatorStatic($row['id_order_detail']);
        $row['tax_calculator'] = $tax_calculator;
        $row['tax_rate'] = $tax_calculator->getTotalRate();
        $row['product_price_tax_excl'] = $tax_calculator->removeTaxes($row['product_price']);
        $row['product_price_tax_incl'] = $tax_calculator->addTaxes($row['product_price']);
        $row['total_price_tax_excl'] = $row['product_price_tax_excl'] * $row['product_quantity'];
        $row['total_price_tax_incl'] = $row['product_price_tax_incl'] * $row['product_quantity'];
    }

    public function getProducts($products = false, $selected_products = false, $selected_qty = false, $fullInfos = true)
    {
        if (!$products) {
            $products = $this->getProductsDetail();
        }

        $result_array = [];
        foreach ($products as $row) {
            if ($selected_products === false || in_array($row['id_order_detail'], $selected_products)) {
                if ($selected_qty !== false && isset($selected_qty[$row['id_order_detail']])) {
                    $row['product_quantity'] = $selected_qty[$row['id_order_detail']];
                }

                if ($fullInfos) {
                    $this->setProductPrices($row);
                    $this->setProductCurrentStock($row);
                    $this->setProductImageInformations($row);
                }

                $result_array[] = $row;
            }
        }

        return $result_array;
    }

    protected function setProductReduction(array $product): array
    {
        $product['reduction_amount'] = 0;
        $product['reduction_percent'] = 0;
        $product['reduction_amount_tax_excl'] = 0;
        $product['reduction_amount_tax_incl'] = 0;

        return $product;
    }

    public static function getIdOrderProduct($id_customer, $id_product)
    {
        $sql = 'SELECT o.id_order
                FROM ' . _DB_PREFIX_ . 'orders o
                LEFT JOIN ' . _DB_PREFIX_ . 'order_detail od ON (o.id_order = od.id_order)
                WHERE o.id_customer = ' . (int) $id_customer . '
                AND od.product_id = ' . (int) $id_product . '
                ORDER BY o.date_add DESC';

        return Db::getInstance()->getValue($sql);
    }

    protected function setProductCustomizedDatas(&$product, $customized_datas)
    {
        $product['customizedDatas'] = $customized_datas;
    }

    protected function setProductCurrentStock(&$product)
    {
        $product['current_stock'] = StockAvailable::getQuantityAvailableByProduct($product['product_id'], $product['product_attribute_id']);
    }

    protected function setProductImageInformations(&$product)
    {
        if (isset($product['product_attribute_id']) && $product['product_attribute_id']) {
            $id_image = Db::getInstance()->getValue('
                SELECT `image_shop`.id_image
                FROM `' . _DB_PREFIX_ . 'product_attribute_image` pai
                LEFT JOIN `' . _DB_PREFIX_ . 'image_shop` image_shop ON (image_shop.id_image = pai.id_image)
                WHERE pai.id_product_attribute = ' . (int) $product['product_attribute_id'] . '
                AND image_shop.id_shop = ' . (int) Context::getContext()->shop->id);
        }

        if (!isset($id_image) || !$id_image) {
            $id_image = Db::getInstance()->getValue('
                SELECT `image_shop`.id_image
                FROM `' . _DB_PREFIX_ . 'image_shop` image_shop
                WHERE image_shop.id_product = ' . (int) $product['product_id'] . '
                AND image_shop.cover = 1
                AND image_shop.id_shop = ' . (int) Context::getContext()->shop->id);
        }

        $product['image'] = null;
        if ($id_image) {
            $product['image'] = new Image($id_image);
        }
    }

    public function getTaxesAverageUsed()
    {
        $taxes = 0;
        $total = 0;

        $products = $this->getProducts();
        foreach ($products as $product) {
            $taxes += $product['total_price_tax_incl'] - $product['total_price_tax_excl'];
            $total += $product['total_price_tax_excl'];
        }

        return $total > 0 ? ($taxes / $total) * 100 : 0;
    }

    public function getVirtualProducts()
    {
        $sql = 'SELECT `id_product`, `id_product_attribute`, `download_hash`, `download_deadline`
                FROM `' . _DB_PREFIX_ . 'order_detail` od
                LEFT JOIN `' . _DB_PREFIX_ . 'product_download` pd ON (pd.id_product = od.product_id)
                WHERE od.`id_order` = ' . (int) $this->id . '
                AND pd.`active` = 1
                AND pd.`is_shareable` = 1';

        return Db::getInstance()->executeS($sql);
    }

    public function isVirtual($strict = true)
    {
        $products = $this->getProducts();
        if (count($products) < 1) {
            return false;
        }

        $virtual = true;
        foreach ($products as $product) {
            if ($strict && !$product['is_virtual']) {
                $virtual = false;
            }
        }

        return $virtual;
    }

    public function getCartRules()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_cart_rule WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->executeS($sql);
    }

    public function getDeletedCartRules()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_cart_rule WHERE id_order = ' . (int) $this->id . ' AND deleted = 1';
        return Db::getInstance()->executeS($sql);
    }

    public static function getDiscountsCustomer($id_customer, $id_cart_rule)
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_cart_rule WHERE id_customer = ' . (int) $id_customer . ' AND id_cart_rule = ' . (int) $id_cart_rule;
        return Db::getInstance()->executeS($sql);
    }

    public function getCurrentState()
    {
        return $this->current_state;
    }

    public function getCurrentStateFull($id_lang)
    {
        $sql = 'SELECT os.*, osl.*
                FROM ' . _DB_PREFIX_ . 'order_state os
                LEFT JOIN ' . _DB_PREFIX_ . 'order_state_lang osl ON (os.id_order_state = osl.id_order_state AND osl.id_lang = ' . (int) $id_lang . ')
                WHERE os.id_order_state = ' . (int) $this->current_state;

        return Db::getInstance()->getRow($sql);
    }

    public function hasBeenDelivered()
    {
        return count($this->getHistory((int) Context::getContext()->language->id, Configuration::get('PS_OS_DELIVERED'))) > 0;
    }

    public function hasProductReturned()
    {
        return count($this->getHistory((int) Context::getContext()->language->id, Configuration::get('PS_OS_RETURNED'))) > 0;
    }

    public function hasBeenPaid()
    {
        return count($this->getHistory((int) Context::getContext()->language->id, Configuration::get('PS_OS_PAYMENT'))) > 0;
    }

    public function hasBeenShipped()
    {
        return count($this->getHistory((int) Context::getContext()->language->id, Configuration::get('PS_OS_SHIPPING'))) > 0;
    }

    public function isInPreparation()
    {
        return count($this->getHistory((int) Context::getContext()->language->id, Configuration::get('PS_OS_PREPARATION'))) > 0;
    }

    public function isPaidAndShipped()
    {
        return $this->hasBeenPaid() && $this->hasBeenShipped();
    }

    public static function getCustomerOrders($id_customer, $show_hidden_status = false, ?Context $context = null)
    {
        if (!$context) {
            $context = Context::getContext();
        }

        $sql = 'SELECT o.*, (SELECT SUM(od.product_quantity) FROM ' . _DB_PREFIX_ . 'order_detail od WHERE od.id_order = o.id_order) as nb_products
                FROM ' . _DB_PREFIX_ . 'orders o
                LEFT JOIN ' . _DB_PREFIX_ . 'order_state os ON (o.current_state = os.id_order_state)
                WHERE o.id_customer = ' . (int) $id_customer . '
                AND o.id_shop = ' . (int) $context->shop->id;

        if (!$show_hidden_status) {
            $sql .= ' AND os.hidden = 0';
        }

        $sql .= ' ORDER BY o.date_add DESC';

        return Db::getInstance()->executeS($sql);
    }

    public static function getOrdersIdByDate($date_from, $date_to, $id_customer = null, $type = null)
    {
        $sql = 'SELECT o.id_order
                FROM ' . _DB_PREFIX_ . 'orders o
                WHERE DATE(o.date_add) BETWEEN \'' . pSQL($date_from) . '\' AND \'' . pSQL($date_to) . '\'';

        if ($id_customer) {
            $sql .= ' AND o.id_customer = ' . (int) $id_customer;
        }

        if ($type) {
            $sql .= ' AND o.current_state = ' . (int) $type;
        }

        return Db::getInstance()->executeS($sql);
    }

    public static function getOrdersWithInformations($limit = null, ?Context $context = null)
    {
        if (!$context) {
            $context = Context::getContext();
        }

        $sql = 'SELECT o.*, c.*, os.*, osl.*
                FROM ' . _DB_PREFIX_ . 'orders o
                LEFT JOIN ' . _DB_PREFIX_ . 'customer c ON (c.id_customer = o.id_customer)
                LEFT JOIN ' . _DB_PREFIX_ . 'order_state os ON (os.id_order_state = o.current_state)
                LEFT JOIN ' . _DB_PREFIX_ . 'order_state_lang osl ON (os.id_order_state = osl.id_order_state AND osl.id_lang = ' . (int) $context->language->id . ')
                WHERE o.id_shop = ' . (int) $context->shop->id . '
                ORDER BY o.date_add DESC';

        if ($limit) {
            $sql .= ' LIMIT ' . (int) $limit;
        }

        return Db::getInstance()->executeS($sql);
    }

    public function getTotalProductsWithoutTaxes($products = false)
    {
        $total = 0;
        if (!$products) {
            $products = $this->getProducts();
        }

        foreach ($products as $product) {
            $total += $product['total_price_tax_excl'];
        }

        return $total;
    }

    public function getTotalProductsWithTaxes($products = false)
    {
        $total = 0;
        if (!$products) {
            $products = $this->getProducts();
        }

        foreach ($products as $product) {
            $total += $product['total_price_tax_incl'];
        }

        return $total;
    }

    public function getCustomer()
    {
        if (!$this->id_customer) {
            return false;
        }

        return new Customer($this->id_customer);
    }

    public static function getCustomerNbOrders($id_customer)
    {
        $sql = 'SELECT COUNT(*) as nb FROM ' . _DB_PREFIX_ . 'orders WHERE id_customer = ' . (int) $id_customer;
        $result = Db::getInstance()->getRow($sql);

        return isset($result['nb']) ? $result['nb'] : 0;
    }

    public static function getByCartId($id_cart)
    {
        $sql = 'SELECT id_order FROM ' . _DB_PREFIX_ . 'orders WHERE id_cart = ' . (int) $id_cart;
        return Db::getInstance()->getValue($sql);
    }

    public static function getIdByCartId($id_cart)
    {
        $sql = 'SELECT id_order FROM ' . _DB_PREFIX_ . 'orders WHERE id_cart = ' . (int) $id_cart;
        return Db::getInstance()->getValue($sql);
    }

    public function addCartRule($id_cart_rule, $name, $values, $id_order_invoice = 0, $free_shipping = null)
    {
        $data = [
            'id_order' => $this->id,
            'id_cart_rule' => $id_cart_rule,
            'name' => $name,
            'value' => $values,
            'id_order_invoice' => $id_order_invoice,
            'free_shipping' => $free_shipping,
        ];

        return Db::getInstance()->insert('order_cart_rule', $data);
    }

    public function getNumberOfDays()
    {
        $first_date = date('Y-m-d', strtotime($this->date_add));
        $last_date = date('Y-m-d', strtotime($this->date_upd));

        $first_time = mktime(0, 0, 0, substr($first_date, 5, 2), substr($first_date, 8, 2), substr($first_date, 0, 4));
        $last_time = mktime(0, 0, 0, substr($last_date, 5, 2), substr($last_date, 8, 2), substr($last_date, 0, 4));

        return ($last_time - $first_time) / (24 * 3600);
    }

    public function isReturnable()
    {
        return count($this->getHistory((int) Context::getContext()->language->id, Configuration::get('PS_OS_DELIVERED'))) > 0;
    }

    public static function getLastInvoiceNumber()
    {
        $sql = 'SELECT MAX(invoice_number) as invoice_number FROM ' . _DB_PREFIX_ . 'orders';
        $result = Db::getInstance()->getRow($sql);

        return isset($result['invoice_number']) ? $result['invoice_number'] : 0;
    }

    public static function setLastInvoiceNumber($order_invoice_id, $id_shop)
    {
        $sql = 'UPDATE ' . _DB_PREFIX_ . 'orders SET invoice_number = ' . (int) $order_invoice_id . ' WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->execute($sql);
    }

    public function getInvoiceNumber($order_invoice_id)
    {
        $sql = 'SELECT invoice_number FROM ' . _DB_PREFIX_ . 'orders WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->getValue($sql);
    }

    public function setInvoice($use_existing_payment = false)
    {
        $order_invoice = new OrderInvoice();
        $order_invoice->id_order = $this->id;
        $order_invoice->number = $this->getLastInvoiceNumber() + 1;
        $order_invoice->delivery_date = date('Y-m-d H:i:s');

        if ($order_invoice->add()) {
            $this->invoice_number = $order_invoice->number;
            $this->invoice_date = $order_invoice->delivery_date;
            $this->update();

            return $order_invoice;
        }

        return false;
    }

    public function setDeliverySlip()
    {
        $order_invoice = new OrderInvoice();
        $order_invoice->id_order = $this->id;
        $order_invoice->number = $this->getLastInvoiceNumber() + 1;
        $order_invoice->delivery_date = date('Y-m-d H:i:s');

        if ($order_invoice->add()) {
            $this->delivery_number = $order_invoice->number;
            $this->delivery_date = $order_invoice->delivery_date;
            $this->update();

            return $order_invoice;
        }

        return false;
    }

    public function setDeliveryNumber($order_invoice_id, $id_shop)
    {
        $sql = 'UPDATE ' . _DB_PREFIX_ . 'orders SET delivery_number = ' . (int) $order_invoice_id . ' WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->execute($sql);
    }

    public function getDeliveryNumber($order_invoice_id)
    {
        $sql = 'SELECT delivery_number FROM ' . _DB_PREFIX_ . 'orders WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->getValue($sql);
    }

    public function setDelivery()
    {
        $order_invoice = new OrderInvoice();
        $order_invoice->id_order = $this->id;
        $order_invoice->number = $this->getLastInvoiceNumber() + 1;
        $order_invoice->delivery_date = date('Y-m-d H:i:s');

        if ($order_invoice->add()) {
            $this->delivery_number = $order_invoice->number;
            $this->delivery_date = $order_invoice->delivery_date;
            $this->update();

            return $order_invoice;
        }

        return false;
    }

    public static function getByDelivery($id_delivery)
    {
        $sql = 'SELECT id_order FROM ' . _DB_PREFIX_ . 'orders WHERE delivery_number = ' . (int) $id_delivery;
        return Db::getInstance()->getValue($sql);
    }

    public static function getByReference($reference)
    {
        $sql = 'SELECT id_order FROM ' . _DB_PREFIX_ . 'orders WHERE reference = \'' . pSQL($reference) . '\'';
        return Db::getInstance()->getValue($sql);
    }

    public static function getByReferenceAndEmail($reference, $email)
    {
        $sql = 'SELECT o.id_order FROM ' . _DB_PREFIX_ . 'orders o
                LEFT JOIN ' . _DB_PREFIX_ . 'customer c ON (c.id_customer = o.id_customer)
                WHERE o.reference = \'' . pSQL($reference) . '\'
                AND c.email = \'' . pSQL($email) . '\'';
        return Db::getInstance()->getValue($sql);
    }

    public function getTotalWeight()
    {
        $weight = 0;
        $products = $this->getProducts();

        foreach ($products as $product) {
            $weight += $product['product_weight'] * $product['product_quantity'];
        }

        return $weight;
    }

    public function isAssociatedAtGuest($email)
    {
        $sql = 'SELECT o.id_order FROM ' . _DB_PREFIX_ . 'orders o
                LEFT JOIN ' . _DB_PREFIX_ . 'customer c ON (c.id_customer = o.id_customer)
                WHERE c.email = \'' . pSQL($email) . '\'
                AND o.id_customer = 0';
        return Db::getInstance()->getValue($sql);
    }

    public static function getCartIdStatic($id_order, $id_customer = 0)
    {
        $sql = 'SELECT id_cart FROM ' . _DB_PREFIX_ . 'orders WHERE id_order = ' . (int) $id_order;
        if ($id_customer) {
            $sql .= ' AND id_customer = ' . (int) $id_customer;
        }
        return Db::getInstance()->getValue($sql);
    }

    public function setCurrentState($id_order_state, $id_employee = 0)
    {
        $this->current_state = $id_order_state;
        $this->update();

        $this->addHistory($id_order_state, $id_employee);
    }

    public function addWs($autodate = true, $null_values = false)
    {
        $success = $this->add($autodate, $null_values);
        if ($success && !empty($this->order_rows)) {
            $this->setWsOrderRows($this->order_rows);
        }
        return $success;
    }

    public function deleteAssociations()
    {
        return Db::getInstance()->delete('order_detail', 'id_order = ' . (int) $this->id);
    }

    public function getPreviousOrderId()
    {
        $sql = 'SELECT id_order FROM ' . _DB_PREFIX_ . 'orders WHERE id_order < ' . (int) $this->id . ' ORDER BY id_order DESC LIMIT 1';
        return Db::getInstance()->getValue($sql);
    }

    public function getNextOrderId()
    {
        $sql = 'SELECT id_order FROM ' . _DB_PREFIX_ . 'orders WHERE id_order > ' . (int) $this->id . ' ORDER BY id_order ASC LIMIT 1';
        return Db::getInstance()->getValue($sql);
    }

    public function getOrderDetailList()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_detail WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->executeS($sql);
    }

    public static function generateReference()
    {
        return strtoupper(Tools::passwdGen(9, 'NO_NUMERIC'));
    }

    public function orderContainProduct($id_product)
    {
        $sql = 'SELECT COUNT(*) FROM ' . _DB_PREFIX_ . 'order_detail WHERE id_order = ' . (int) $this->id . ' AND product_id = ' . (int) $id_product;
        return Db::getInstance()->getValue($sql);
    }

    public function useOneAfterAnotherTaxComputationMethod()
    {
        return Configuration::get('PS_TAX_DISPLAY') == 1;
    }

    public function getOrderPaymentCollection()
    {
        $order_payment_collection = new PrestaShopCollection('OrderPayment');
        $order_payment_collection->where('id_order', '=', $this->id);

        return $order_payment_collection;
    }

    public function hasPayments(): bool
    {
        return $this->getOrderPaymentCollection()->count() > 0;
    }

    public function addOrderPayment(
        $amount_paid,
        $payment_method = null,
        $payment_transaction_id = null,
        $currency = null,
        $date = null,
        $order_invoice = null,
        ?int $id_employee = null
    ) {
        $order_payment = new OrderPayment();
        $order_payment->id_order = $this->id;
        $order_payment->amount = $amount_paid;
        $order_payment->payment_method = $payment_method;
        $order_payment->payment_transaction_id = $payment_transaction_id;
        $order_payment->id_currency = $currency ? $currency->id : Context::getContext()->currency->id;
        $order_payment->conversion_rate = $currency ? $currency->conversion_rate : Context::getContext()->currency->conversion_rate;
        $order_payment->date = $date ? $date : date('Y-m-d H:i:s');
        $order_payment->id_employee = $id_employee;

        if ($order_invoice) {
            $order_payment->id_order_invoice = $order_invoice->id;
        }

        return $order_payment->add();
    }

    public function getDocuments()
    {
        $documents = [];
        $documents['invoices'] = $this->getInvoices();
        $documents['delivery_slips'] = $this->getDeliverySlips();

        return $documents;
    }

    public function getReturn()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_return WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->executeS($sql);
    }

    public function getShipping()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_carrier WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->executeS($sql);
    }

    public function getOrderSlipsCollection()
    {
        $order_slips_collection = new PrestaShopCollection('OrderSlip');
        $order_slips_collection->where('id_order', '=', $this->id);

        return $order_slips_collection;
    }

    public function getInvoicesCollection()
    {
        $invoices_collection = new PrestaShopCollection('OrderInvoice');
        $invoices_collection->where('id_order', '=', $this->id);

        return $invoices_collection;
    }

    public function getDeliverySlipsCollection()
    {
        $delivery_slips_collection = new PrestaShopCollection('OrderInvoice');
        $delivery_slips_collection->where('id_order', '=', $this->id);
        $delivery_slips_collection->where('delivery_date', '!=', '0000-00-00 00:00:00');

        return $delivery_slips_collection;
    }

    public function getNotPaidInvoicesCollection()
    {
        $not_paid_invoices_collection = new PrestaShopCollection('OrderInvoice');
        $not_paid_invoices_collection->where('id_order', '=', $this->id);
        $not_paid_invoices_collection->where('total_paid_tax_incl', '=', 0);

        return $not_paid_invoices_collection;
    }

    public function getTotalPaid($currency = null)
    {
        if (!$currency) {
            $currency = Context::getContext()->currency;
        }

        $total_paid = 0;
        $payments = $this->getOrderPaymentCollection();

        foreach ($payments as $payment) {
            if ($payment->id_currency == $currency->id) {
                $total_paid += $payment->amount;
            } else {
                $total_paid += Tools::convertPrice($payment->amount, $payment->id_currency, $currency);
            }
        }

        return $total_paid;
    }

    public function getOrdersTotalPaid()
    {
        $sql = 'SELECT SUM(total_paid) as total FROM ' . _DB_PREFIX_ . 'orders WHERE id_customer = ' . (int) $this->id_customer;
        $result = Db::getInstance()->getRow($sql);

        return isset($result['total']) ? $result['total'] : 0;
    }

    public function updateShippingCost($amount)
    {
        $this->total_shipping = $amount;
        $this->total_shipping_tax_incl = $amount;
        $this->total_shipping_tax_excl = $amount;

        return $this->update();
    }

    public function getProductTaxesBreakdown()
    {
        $breakdown = [];
        $products = $this->getProducts();

        foreach ($products as $product) {
            $tax_rate = $product['tax_rate'];
            if (!isset($breakdown[$tax_rate])) {
                $breakdown[$tax_rate] = [
                    'tax_rate' => $tax_rate,
                    'total_tax_excl' => 0,
                    'total_tax_incl' => 0,
                ];
            }

            $breakdown[$tax_rate]['total_tax_excl'] += $product['total_price_tax_excl'];
            $breakdown[$tax_rate]['total_tax_incl'] += $product['total_price_tax_incl'];
        }

        return $breakdown;
    }

    public function getShippingTaxesBreakdown()
    {
        $breakdown = [];
        $shipping_tax_rate = $this->carrier_tax_rate;

        if ($shipping_tax_rate > 0) {
            $breakdown[$shipping_tax_rate] = [
                'tax_rate' => $shipping_tax_rate,
                'total_tax_excl' => $this->total_shipping_tax_excl,
                'total_tax_incl' => $this->total_shipping_tax_incl,
            ];
        }

        return $breakdown;
    }

    public function getWrappingTaxesBreakdown()
    {
        $breakdown = [];
        $wrapping_tax_rate = 0; // Default tax rate for wrapping

        if ($this->total_wrapping > 0) {
            $breakdown[$wrapping_tax_rate] = [
                'tax_rate' => $wrapping_tax_rate,
                'total_tax_excl' => $this->total_wrapping_tax_excl,
                'total_tax_incl' => $this->total_wrapping_tax_incl,
            ];
        }

        return $breakdown;
    }

    public function getEcoTaxTaxesBreakdown()
    {
        $breakdown = [];
        $products = $this->getProducts();

        foreach ($products as $product) {
            if (isset($product['ecotax']) && $product['ecotax'] > 0) {
                $tax_rate = 0; // EcoTax is usually tax-free
                if (!isset($breakdown[$tax_rate])) {
                    $breakdown[$tax_rate] = [
                        'tax_rate' => $tax_rate,
                        'total_tax_excl' => 0,
                        'total_tax_incl' => 0,
                    ];
                }

                $breakdown[$tax_rate]['total_tax_excl'] += $product['ecotax'];
                $breakdown[$tax_rate]['total_tax_incl'] += $product['ecotax'];
            }
        }

        return $breakdown;
    }

    public function hasInvoice()
    {
        return $this->invoice_number > 0;
    }

    public function hasDelivery()
    {
        return $this->delivery_number > 0;
    }

    public function getOrderInvoiceIdIfHasDelivery()
    {
        $sql = 'SELECT id_order_invoice FROM ' . _DB_PREFIX_ . 'order_invoice WHERE id_order = ' . (int) $this->id . ' AND delivery_date IS NOT NULL';
        return Db::getInstance()->getValue($sql);
    }

    public function getWarehouseList()
    {
        $sql = 'SELECT DISTINCT w.* FROM ' . _DB_PREFIX_ . 'warehouse w
                INNER JOIN ' . _DB_PREFIX_ . 'warehouse_product_location wpl ON (w.id_warehouse = wpl.id_warehouse)
                INNER JOIN ' . _DB_PREFIX_ . 'order_detail od ON (od.product_id = wpl.id_product)
                WHERE od.id_order = ' . (int) $this->id;
        return Db::getInstance()->executeS($sql);
    }

    public function getCurrentOrderState()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_state WHERE id_order_state = ' . (int) $this->current_state;
        return Db::getInstance()->getRow($sql);
    }

    public function getWebserviceObjectList($sql_join, $sql_filter, $sql_sort, $sql_limit)
    {
        $sql = 'SELECT DISTINCT o.id_order FROM ' . _DB_PREFIX_ . 'orders o' . $sql_join . ' WHERE 1 ' . $sql_filter;
        if ($sql_sort) {
            $sql .= $sql_sort;
        }
        if ($sql_limit) {
            $sql .= $sql_limit;
        }

        return Db::getInstance()->executeS($sql);
    }

    public function getBrother()
    {
        $sql = 'SELECT o.* FROM ' . _DB_PREFIX_ . 'orders o
                WHERE o.id_customer = ' . (int) $this->id_customer . '
                AND o.id_order != ' . (int) $this->id . '
                ORDER BY o.date_add DESC';
        return Db::getInstance()->executeS($sql);
    }

    public function getOrderPayments()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_payment WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->executeS($sql);
    }

    public function getUniqReference()
    {
        return $this->reference . '-' . $this->id;
    }

    public static function getUniqReferenceOf($id_order)
    {
        $sql = 'SELECT reference FROM ' . _DB_PREFIX_ . 'orders WHERE id_order = ' . (int) $id_order;
        $reference = Db::getInstance()->getValue($sql);

        return $reference . '-' . $id_order;
    }

    public function getIdOrderCarrier()
    {
        $sql = 'SELECT id_order_carrier FROM ' . _DB_PREFIX_ . 'order_carrier WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->getValue($sql);
    }

    public static function sortDocuments($a, $b)
    {
        if ($a['date_add'] == $b['date_add']) {
            return 0;
        }

        return ($a['date_add'] < $b['date_add']) ? -1 : 1;
    }

    public function getWsShippingNumber()
    {
        return $this->shipping_number;
    }

    public function getShippingNumber(): ?string
    {
        return $this->shipping_number;
    }

    public function setWsShippingNumber($shipping_number)
    {
        $this->shipping_number = $shipping_number;
        return $this->update();
    }

    public function setWsCurrentState($state)
    {
        $this->current_state = $state;
        return $this->update();
    }

    public function getProductTaxesDetails($limitToOrderDetails = false)
    {
        $breakdown = [];
        $products = $this->getProducts();

        foreach ($products as $product) {
            $tax_rate = $product['tax_rate'];
            if (!isset($breakdown[$tax_rate])) {
                $breakdown[$tax_rate] = [
                    'tax_rate' => $tax_rate,
                    'total_tax_excl' => 0,
                    'total_tax_incl' => 0,
                    'products' => [],
                ];
            }

            $breakdown[$tax_rate]['total_tax_excl'] += $product['total_price_tax_excl'];
            $breakdown[$tax_rate]['total_tax_incl'] += $product['total_price_tax_incl'];
            $breakdown[$tax_rate]['products'][] = $product;
        }

        return $breakdown;
    }

    public function updateOrderDetailTax()
    {
        $products = $this->getProducts();
        foreach ($products as $product) {
            $order_detail = new OrderDetail($product['id_order_detail']);
            $order_detail->unit_price_tax_excl = $product['unit_price_tax_excl'];
            $order_detail->unit_price_tax_incl = $product['unit_price_tax_incl'];
            $order_detail->total_price_tax_excl = $product['total_price_tax_excl'];
            $order_detail->total_price_tax_incl = $product['total_price_tax_incl'];
            $order_detail->update();
        }
    }

    public function getOrderDetailTaxes()
    {
        $sql = 'SELECT * FROM ' . _DB_PREFIX_ . 'order_detail_tax WHERE id_order = ' . (int) $this->id;
        return Db::getInstance()->executeS($sql);
    }

    public function getProductSpecificPriceId(int $productId, int $productAttributeId): ?int
    {
        $sql = 'SELECT specific_price_id FROM ' . _DB_PREFIX_ . 'order_detail 
                WHERE id_order = ' . (int) $this->id . ' 
                AND product_id = ' . (int) $productId . ' 
                AND product_attribute_id = ' . (int) $productAttributeId;
        return Db::getInstance()->getValue($sql);
    }

    public function refreshShippingCost()
    {
        $carrier = new Carrier($this->id_carrier);
        $shipping_cost = $carrier->getShippingCost($this->getTotalWeight());

        $this->total_shipping = $shipping_cost;
        $this->total_shipping_tax_incl = $shipping_cost;
        $this->total_shipping_tax_excl = $shipping_cost;

        return $this->update();
    }
}