<?php

declare(strict_types=1);

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);

        $products = json_decode($exportProduct->products, true);
        if (!\is_array($products)) {
            throw new Exception('Invalid export product JSON');
        }

        $productsId = \array_map(
            fn ($id): int => (int) $id,
            $products
        );

        $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);
                \assert(\is_array($result));
                $exporter->write($result);
            }

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