<?php

class ExportGenerator
{
    /**
     * @throws PrestaShopDatabaseException
     * @throws PrestaShopException
     */
    public function getQueryWithoutSelect(ExportProduct $exportProduct, int $limit = 0, int $offset = 0): string
    {
        $db = Db::getInstance();
        $clinique = new Clinique($exportProduct->id_myvetshop_clinique);

        $productsId = array_map(
            function ($id) {
                return (int) $id;
            },
            json_decode($exportProduct->products, true)
        );

        $productsId = implode(', ', $productsId);

        $query = ' 
             FROM `' . _DB_PREFIX_ . 'order_detail` AS detail
            INNER JOIN `' . _DB_PREFIX_ . 'orders`  AS orders ON detail.id_order = orders.id_order
            INNER JOIN `' . _DB_PREFIX_ . 'product` AS product ON product.id_product = detail.product_id
            INNER JOIN `' . _DB_PREFIX_ . 'customer` AS customer ON customer.id_customer = orders.id_customer
            INNER JOIN `' . _DB_PREFIX_ . 'myvetshop_clinique` clinique ON (orders.id_carrier = clinique.id_carrier OR orders.id_carrier = clinique.id_carrier_home)

            WHERE orders.valid = 1
            AND clinique.id_group = ' . (int) $clinique->id_group . '
            AND (orders.invoice_date >=  "' . $db->escape($exportProduct->date_start) . '" AND orders.invoice_date <=  "' . $db->escape($exportProduct->date_end) . '")
            AND detail.product_id IN (' . $productsId . ')
            ORDER BY customer.id_customer 
            ';

        if ($limit) {
            $query = $query . ' LIMIT ' . $offset . ', ' . $limit;
        }

        return $query;
    }

    /**
     * @throws PrestaShopDatabaseException
     * @throws PrestaShopException
     */
    public function generateExport(ExportProduct $exportProduct): CsvExporter
    {
        $db = Db::getInstance();

        $numberOfResult = $db->executeS('SELECT COUNT(customer.id_customer) AS numberOfResult ' . $this->getQueryWithoutSelect($exportProduct));

        if (isset($numberOfResult['0']['numberOfResult']) && $numberOfResult['0']['numberOfResult'] > 0) {
            $numberOfResult = (int) $numberOfResult['0']['numberOfResult'];
            $nrbPage = ceil($numberOfResult / 500);

            $exporter = new CsvExporter($exportProduct->name, ';', ['Id client', 'Prenom', 'Nom', 'email', 'Date de la commande', 'Reference de la commande', 'Id produit', 'Nom du produit', 'Total ht']);

            for ($i = 0; $i < $nrbPage; ++$i) {
                $query = '
                SELECT
                customer.id_customer,
                customer.firstname,
                customer.lastname,
                customer.email,
                DATE_FORMAT(orders.invoice_date, "%d/%m/%Y %H:%i:%s"),
                orders.reference,
                product.id_product,
                detail.product_name,
                detail.total_price_tax_excl
                ' . $this->getQueryWithoutSelect($exportProduct, 500, $i * 500);

                $result = $db->executeS($query);
                $exporter->write($result);
            }

            return $exporter;
        } else {
            throw new Exception('Aucun résultat');
        }
    }
}
