<?php
/**
 * 2007-2024 Sendinblue
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Academic Free License (AFL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/afl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to contact@sendinblue.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade PrestaShop to newer
 * versions in the future. If you wish to customize PrestaShop for your
 * needs please refer to http://www.prestashop.com for more information.
 *
 * @author    Sendinblue <contact@sendinblue.com>
 * @copyright 2007-2024 Sendinblue
 * @license   http://opensource.org/licenses/afl-3.0.php  Academic Free License (AFL 3.0)
 * International Registered Trademark & Property of Sendinblue
 */

namespace Sendinblue\Services;

if (!defined('_PS_VERSION_')) {
    exit;
}

class OrderService
{
    private $idShop;

    public function __construct()
    {
        $this->idShop = \ContextCore::getContext()->shop->id;
    }

    public function getTotalOrderCount()
    {
        try {
            $result = \DbCore::getInstance()->executeS(
                sprintf(
                    'SELECT count(*) AS `count`
                FROM  `%sorders` o
                WHERE o.`id_shop` = %d',
                    _DB_PREFIX_,
                    $this->idShop
                )
            );

            return isset($result[0]['count']) ? (int) $result[0]['count'] : 0;
        } catch (\Exception $e) {
            \PrestaShopLogger::addLog($e->getMessage(), 3);
        }
    }

    public function getOrders($limit, $offset)
    {
        if (version_compare(_PS_VERSION_, '1.7.7') <= 0) {
            $sql = sprintf(
                "SELECT o.`id_order`, o.`reference`, o.`total_paid_tax_incl` AS total_paid, o.`date_add`, o.`date_upd`, o.`current_state`, SUM(osd.`amount_tax_incl`) as refunded,
                    c.`firstname`, c.`lastname`, c.`email`, 
                    CONCAT(a.`address1`, ' ', a.`address2`) AS address,
                    a.`city`, a.`postcode`, a.`phone`,
                    co.`iso_code` AS countryCode,
                    s.`name` AS region,
                    b.`name` AS order_status,
                    o.`payment` AS paymentMethod,
                    o.`conversion_rate` AS conversion_rate,
                    GROUP_CONCAT(DISTINCT cr_lang.`name` SEPARATOR ', ') AS distinct_coupons,
                    GROUP_CONCAT(
                        JSON_OBJECT(
                            'id_product', od.`product_id`,
                            'id_variant', od.`product_attribute_id`,
                            'name', p_lang.`name`,
                            'price', od.`product_price`,
                            'quantity', od.`product_quantity`
                        )
                    ) AS products_json
                FROM `%sorders` o
                LEFT JOIN `%scustomer` c ON (o.`id_customer` = c.`id_customer`)
                LEFT JOIN `%saddress` a ON (o.`id_address_invoice` = a.`id_address`)
                LEFT JOIN `%sorder_state_lang` b ON (b.`id_order_state` = o.`current_state` AND b.`id_lang` = 1)
                LEFT JOIN `%scountry` co ON (co.`id_country` = a.`id_country`)
                LEFT JOIN `%sstate` s ON (s.`id_state` = a.`id_state`)
                LEFT JOIN `%sorder_cart_rule` ocr ON (ocr.`id_order` = o.`id_order`)
                LEFT JOIN `%scart_rule_lang` cr_lang ON (ocr.`id_cart_rule` = cr_lang.`id_cart_rule` AND cr_lang.`id_lang` = 1)
                LEFT JOIN `%sorder_detail` od ON (od.`id_order` = o.`id_order`)
                LEFT JOIN `%sorder_slip_detail` osd ON (od.`id_order_detail` = osd.`id_order_detail`)
                LEFT JOIN `%sproduct` p ON (p.`id_product` = od.`product_id`)
                LEFT JOIN `%sproduct_lang` p_lang ON (p.`id_product` = p_lang.`id_product` AND p_lang.`id_lang` = 1 AND p_lang.`id_shop` = %d)
                WHERE o.`id_shop` = %d
                AND o.`current_state` != 0
                GROUP BY o.`id_order`
                LIMIT %d, %d",
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                $this->idShop,
                $this->idShop,
                $offset,
                $limit
            );
        } else {
            $sql = sprintf(
                "SELECT o.`id_order`, o.`reference`, o.`total_paid_tax_incl` AS total_paid, o.`date_add`, o.`date_upd`, o.`current_state`, SUM(od.`total_refunded_tax_incl`) as refunded,
                    c.`firstname`, c.`lastname`, c.`email`, 
                    CONCAT(a.`address1`, ' ', a.`address2`) AS address,
                    a.`city`, a.`postcode`, a.`phone`,
                    co.`iso_code` AS countryCode,
                    s.`name` AS region,
                    b.`name` AS order_status,
                    o.`payment` AS paymentMethod,
                    o.`conversion_rate` AS conversion_rate,
                    GROUP_CONCAT(DISTINCT cr_lang.`name` SEPARATOR ', ') AS distinct_coupons,
                    GROUP_CONCAT(
                        JSON_OBJECT(
                            'id_product', od.`product_id`,
                            'id_variant', od.`product_attribute_id`,
                            'name', p_lang.`name`,
                            'price', od.`product_price`,
                            'quantity', od.`product_quantity`
                        )
                    ) AS products_json
                FROM `%sorders` o
                LEFT JOIN `%scustomer` c ON (o.`id_customer` = c.`id_customer`)
                LEFT JOIN `%saddress` a ON (o.`id_address_invoice` = a.`id_address`)
                LEFT JOIN `%sorder_state_lang` b ON (b.`id_order_state` = o.`current_state` AND b.`id_lang` = 1)
                LEFT JOIN `%scountry` co ON (co.`id_country` = a.`id_country`)
                LEFT JOIN `%sstate` s ON (s.`id_state` = a.`id_state`)
                LEFT JOIN `%sorder_cart_rule` ocr ON (ocr.`id_order` = o.`id_order`)
                LEFT JOIN `%scart_rule_lang` cr_lang ON (ocr.`id_cart_rule` = cr_lang.`id_cart_rule` AND cr_lang.`id_lang` = 1)
                LEFT JOIN `%sorder_detail` od ON (od.`id_order` = o.`id_order`)
                LEFT JOIN `%sproduct` p ON (p.`id_product` = od.`product_id`)
                LEFT JOIN `%sproduct_lang` p_lang ON (p.`id_product` = p_lang.`id_product` AND p_lang.`id_lang` = 1 AND p_lang.`id_shop` = %d)
                WHERE o.`id_shop` = %d
                AND o.`current_state` != 0
                GROUP BY o.`id_order`
                LIMIT %d, %d",
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                _DB_PREFIX_,
                $this->idShop,
                $this->idShop,
                $offset,
                $limit
            );
        }

        try {
            $orders = \DbCore::getInstance()->executeS($sql);

            if (empty($orders)) {
                return ['orders' => []];
            }

            foreach ($orders as &$order) {
                $billing = [
                    'address' => $order['address'],
                    'city' => $order['city'],
                    'postCode' => $order['postcode'],
                    'phone' => $order['phone'],
                    'countryCode' => $order['countryCode'],
                    'paymentMethod' => $order['paymentMethod'],
                    'region' => $order['region'],
                ];

                $order['date_add'] = gmdate('Y-m-d\TH:i:s', strtotime($order['date_add']));
                $order['date_upd'] = gmdate('Y-m-d\TH:i:s', strtotime($order['date_upd']));

                $order['billing'] = $billing;
                unset($order['address'], $order['city'], $order['postcode'], $order['phone'], $order['countryCode'], $order['paymentMethod'], $order['region']);

                $conversion_rate = $order['conversion_rate'];
                // Convert products JSON string to an array of products
                $order['products'] = [];
                $products = json_decode('[' . $order['products_json'] . ']', true);
                if (!empty($products)) {
                    foreach ($products as &$product) {
                        $product['price'] = round($product['price'] / $conversion_rate, 2);
                    }
                    $order['products'] = $products;
                }
                unset($order['products_json']);

                // Convert coupons string to an array of distinct coupons
                $order['coupons'] = [];
                if (!empty($order['distinct_coupons'])) {
                    $order['coupons'] = explode(', ', $order['distinct_coupons']);
                }
                unset($order['distinct_coupons']);

                // Payment Error or Cancelled or Refunded
                if ($order['current_state'] == 8 || $order['current_state'] == 7 || $order['current_state'] == 6) {
                    $order['total_paid'] = (string) 0;
                } else {
                    $order['total_paid'] = (string) round(($order['total_paid'] - $order['refunded']) / $conversion_rate, 2);
                }
                unset($order['refunded']);
            }

            return ['orders' => $orders];
        } catch (\Exception $e) {
            \PrestaShopLogger::addLog($e->getMessage(), 3);
        }
    }
}
