<?php

declare(strict_types=1);

namespace KupShop\StoresBundle\Utils;

use KupShop\KupShopBundle\Util\Compat\ServiceContainer;
use Query\Operator;
use Query\QueryBuilder;

class MainStoreMigrationUtil
{
    /**
     * @required
     */
    public StoresInStore $storesInStore;

    public function migrate($fix = false): void
    {
        $count = sqlQueryBuilder()->select('COUNT(id) count')
            ->from('stores_items')
            ->where(Operator::equals(['id_store' => 1]))
            ->execute()->fetchOne();

        // Migrace už proběhla
        if ($count > 0) {
            return;
        }

        $qb = sqlQueryBuilder()->select('*')->fromProducts()->joinVariationsOnProducts()
            ->andWhere($this->addStoresInStoreAmounts([1]))
            ->addSelect('pv.id variation_id, p.id product_id')
            ->andWhere('p.id > 0');

        foreach ($qb->execute() as $item) {
            sqlQueryBuilder()->insert('stores_items')
                ->directValues([
                    'id_store' => 1,
                    'id_product' => $item['product_id'],
                    'id_variation' => $item['variation_id'],
                    'quantity' => $item['store1_in_store'],
                    'min_quantity' => $item['store1_min_in_store'],
                ])
                ->execute();
        }

        if (findModule(\Modules::WAREHOUSE)) {
            ServiceContainer::getService(StoreWarehouseRecalculate::class)->fixStoreWarehouseErrors(false);
        }
    }

    public function insertOrderStores()
    {
        if (!findModule(\Modules::SELLERS)) {
            sqlQuery('INSERT INTO order_stores SELECT o.id, COALESCE(s.id, 1) id_store FROM orders o
                INNER JOIN delivery_type dt on o.id_delivery = dt.id
                LEFT JOIN stores s ON dt.id_delivery = s.id_delivery
                WHERE o.id IN (
                    SELECT id FROM orders o 
                    LEFT JOIN order_stores os ON o.id = os.id_order 
                    WHERE id_order IS NULL)');
        } else {
            sqlQuery('INSERT INTO order_stores SELECT o.id, COALESCE(s.id_store, 1) id_store FROM orders o
                LEFT JOIN order_sellers os on o.id = os.id_order
                LEFT JOIN sellers s on os.id_seller = s.id
                WHERE o.id IN (
                    SELECT id FROM orders o
                    LEFT JOIN order_stores os ON o.id = os.id_order 
                    WHERE id_order IS NULL)');
        }
    }

    private function getQuantityDecrementQueryBuilder(): QueryBuilder
    {
        return sqlQueryBuilder()
            ->select('COALESCE(SUM(si0.quantity), 0)')
            ->from('stores_items', 'si0')
            ->where('si0.id_product = p.id AND si0.id_variation <=> pv.id AND si0.id_store IN (SELECT id FROM stores WHERE type != :externalStoreType)')
            ->setParameter('externalStoreType', StoresInStore::TYPE_EXTERNAL_STORE);
    }

    private function addStoresInStoreAmounts($selectedStores = null): callable
    {
        return function (QueryBuilder $qb) use ($selectedStores) {
            $stores = ServiceContainer::getService(StoresInStore::class)->getStores();

            $qb->andWhere('1');

            // quantity decrement - vyselectuje skladovost externich skladu, aby se mohla odecist od celkoveho mnozstvi skladem
            $quantityDecrementQb = $this->getQuantityDecrementQueryBuilder();

            $store1_in_store = "COALESCE(pv.in_store, p.in_store) - ({$quantityDecrementQb->getSQL()})";
            $qb->addQueryBuilderParameters($quantityDecrementQb);

            foreach ($stores as $key => $store) {
                if ($key == 1 || ($selectedStores && !in_array($key, $selectedStores))) {
                    continue;
                }

                $table_alias = 'si'.$key;

                // prepare base sub query
                $subQuery = sqlQueryBuilder()
                    ->from('stores_items', $table_alias)
                    ->where("{$table_alias}.id_product = p.id AND {$table_alias}.id_variation <=> pv.id AND {$table_alias}.id_store = {$key}");

                // add sub query select
                $qb->addSubselect($subQuery->select("SUM(COALESCE({$table_alias}.quantity, 0))"), "store{$key}_in_store");
                $qb->addSubselect($subQuery->select("COALESCE({$table_alias}.min_quantity, 0)"), "store{$key}_min_in_store");
            }
            $store1_in_store = 'CAST(('.$store1_in_store.') AS FLOAT) AS store1_in_store';
            $qb->addSelect($store1_in_store);
            $qb->addSelect('0 AS store1_min_in_store');
        };
    }
}