<?php

declare(strict_types=1);

namespace KupShop\ProductSetsBundle\Utils;

use Doctrine\DBAL\Exception as DBALException;
use Query\Operator;

class MultiSetUtil
{
    public const CALCULATE_TYPE_PRICE = 'price';
    public const CALCULATE_TYPE_PRODUCTS_PRICE_DISCOUNTED = 'productsPriceDiscounted';
    public const CALCULATE_TYPE_PRODUCTS_PRICE_DISCOUNTED_WITHOUT_PRICELEVEL = 'productsPriceDiscountedWithoutPriceLevel';

    /**
     * @return iterable<array{id_product: int, price: float|string}>
     *
     * @throws DBALException
     */
    public function getPriceFrom(?array $masterProductIds = null): iterable
    {
        // when product is variation and set_price is set on PRODUCT
        // - use product SET price even when calculating the variation price
        // sp.price (product set price) >> pv.price (variation price)

        $calculateType = \Settings::getDefault()->multisets['price_calc_type'] ?? self::CALCULATE_TYPE_PRICE;

        $priceWithDiscount = match ($calculateType) {
            self::CALCULATE_TYPE_PRICE => 'pm.price',
            // language=MariaDB
            default => <<<__SQL__
                COALESCE(spv.price, sp.price, pv.price, p.price) * (
                    IF(spv.price IS NOT NULL || sp.price IS NOT NULL, 1, 1 - (COALESCE(p.discount, 0) / 100))
                )
            __SQL__,
        };

        // multiset price >> product/variation price (without discount)
        $categoriesMinPrices = sqlQueryBuilder()
            ->select(
                'sp.id_product_master',
                'sp.category',
                "MIN({$priceWithDiscount}) AS price",
            )
            ->from('set_products', 'sp')
            ->leftJoin('sp', 'products', 'pm', 'sp.id_product_master = pm.id')
            ->leftJoin('sp', 'set_products_variations', 'spv', 'sp.id = spv.id_set_product')
            ->leftJoin('sp', 'products', 'p', 'sp.id_product = p.id')
            ->leftJoin('spv', 'products_variations', 'pv', 'spv.id_variation = pv.id')
            ->andWhere(Operator::exists(
                // only multisets that can be sold
                sqlQueryBuilder()
                    ->select('*')
                    ->from('products', '_p')
                    ->andWhere('_p.id = sp.id_product_master')
                    ->andWhere('_p.in_store > 0'),
            ))
            ->andWhere('COALESCE(pv.in_store, p.in_store) > 0')
            ->andWhere("COALESCE(pv.figure, p.figure) = 'Y'")
            ->addGroupBy('sp.id_product_master');

        // group it by category only if price should be calculated from products in categories
        // grouping is not needed when price from product should be used
        if ($calculateType !== self::CALCULATE_TYPE_PRICE) {
            $categoriesMinPrices->addGroupBy('sp.category');
        }

        $setsMinPrices = sqlQueryBuilder()
            ->select('sc.id_product_master AS id_product', 'SUM(sc.price) AS price')
            ->from('('.$categoriesMinPrices->getSQL().')', 'sc')
            ->addGroupBy('sc.id_product_master');

        if ($masterProductIds !== null) {
            $setsMinPrices->andWhere(Operator::inIntArray($masterProductIds, 'sc.id_product_master'));
        }

        return $setsMinPrices->execute();
    }

    /**
     * @return \Generator<array{id_product: int, in_store: int}>
     *
     * @throws DBALException
     */
    public function getInStore(?array $masterProductIds = null): \Generator
    {
        $sets = sqlQueryBuilder()
            ->select('id_product_master as id')
            ->from('set_products')
            ->groupBy('id_product_master');

        if ($masterProductIds !== null) {
            $sets->andWhere(Operator::inIntArray($masterProductIds, 'id'));
        }

        foreach ($sets->execute() as $set) {
            $in_store = sqlFetch(sqlQuery('SELECT MIN(m.min_instore) AS in_store FROM (
                        SELECT MAX(COALESCE(pv.in_store, p.in_store)) AS min_instore
                        FROM set_products sp
                        LEFT JOIN set_products_variations spv ON sp.id = spv.id_set_product
                        LEFT JOIN products p ON sp.id_product = p.id
                        LEFT JOIN products_variations pv ON spv.id_variation=pv.id
                        WHERE sp.id_product_master= :set
                        GROUP BY (sp.category)
                    ) m;
            ', ['set' => $set['id']]));

            yield [
                'id_product' => $set['id'],
                'in_store' => $in_store['in_store'],
            ];
        }
    }
}
