<?php

declare(strict_types=1);

namespace External\FlexiBeeBundle\Util;

use AbraFlexi\Relation;
use External\FlexiBeeBundle\Synchronizers\BaseSynchronizer;
use KupShop\CatalogBundle\Util\SetsUpdateStore;
use Query\Operator;

class FlexiBeeUtil
{
    public function __construct(
        private readonly FlexiBeeLocator $synchronizerLocator,
        private readonly FlexiBeeLogger $logger,
        private readonly SetsUpdateStore $setsUpdateStore,
    ) {
    }

    public function getOrderNumber(\Order $order): string
    {
        return 'WPJ-'.$order->order_no;
    }

    public function getMapping(string $type, int $flexiId): ?int
    {
        $id = sqlQueryBuilder()
            ->select('id_'.$type)
            ->from('flexi_'.$type.'s')
            ->where(Operator::equals(['id_flexi' => $flexiId]))
            ->execute()->fetchOne();

        if (!$id) {
            return null;
        }

        return (int) $id;
    }

    public function getFlexiId(string $type, int $shopId): ?int
    {
        $flexiId = sqlQueryBuilder()
            ->select('id_flexi')
            ->from('flexi_'.$type.'s')
            ->where(Operator::equals(['id_'.$type => $shopId]))
            ->execute()->fetchOne();

        if (!$flexiId) {
            return null;
        }

        return (int) $flexiId;
    }

    public function deleteMapping(string $type, int $flexiId): void
    {
        sqlQueryBuilder()
            ->delete('flexi_'.$type.'s')
            ->andWhere(Operator::equals(['id_flexi' => $flexiId]))
            ->execute();
    }

    public function createMapping(string $type, int $flexiId, int $shopId): void
    {
        sqlQueryBuilder()
            ->insert('flexi_'.$type.'s')
            ->directValues(
                [
                    'id_flexi' => $flexiId,
                    'id_'.$type => $shopId,
                ]
            )
            ->execute();
    }

    public function createItemMapping(int $flexiId, int $productId, ?int $variationId = null): void
    {
        sqlQueryBuilder()
            ->insert('flexi_products')
            ->directValues(
                [
                    'id_flexi' => $flexiId,
                    'id_product' => $productId,
                    'id_variation' => $variationId,
                ]
            )->execute();
    }

    public function setFlexiOrderData(int $orderId, string $key, $value): void
    {
        sqlGetConnection()->transactional(function () use ($orderId, $key, $value) {
            $data = sqlQueryBuilder()
                ->select('data')
                ->from('flexi_orders')
                ->where(Operator::equalsNullable(['id_order' => $orderId]))
                ->execute()->fetchOne();

            $data = json_decode($data ?: '', true) ?? [];

            $data[$key] = $value;

            sqlQueryBuilder()
                ->update('flexi_orders')
                ->directValues(['data' => json_encode($data)])
                ->where(Operator::equals(['id_order' => $orderId]))
                ->execute();
        });
    }

    public function getProductFlexiId(int $productId, ?int $variationId = null): ?int
    {
        $flexiId = sqlQueryBuilder()
            ->select('id_flexi')
            ->from('flexi_products')
            ->where(
                Operator::equalsNullable(
                    [
                        'id_product' => $productId,
                        'id_variation' => $variationId,
                    ]
                )
            )
            ->execute()->fetchColumn();

        if (!$flexiId) {
            return null;
        }

        return (int) $flexiId;
    }

    public function getItemMapping(int $flexiId): ?array
    {
        $data = sqlQueryBuilder()
            ->select('id_product, id_variation')
            ->from('flexi_products')
            ->where(Operator::equals(['id_flexi' => $flexiId]))
            ->execute()->fetch();

        if (!$data) {
            return null;
        }

        return [(int) $data['id_product'], $data['id_variation'] ? (int) $data['id_variation'] : null];
    }

    public function getProductId(string $code): ?int
    {
        $id = sqlQueryBuilder()
            ->select('id')
            ->from('products')
            ->where(Operator::equals(['code' => $code]))
            ->execute()->fetchOne();

        if (!$id) {
            return null;
        }

        return (int) $id;
    }

    public function getVariationId(string $code): ?int
    {
        $id = sqlQueryBuilder()
            ->select('id')
            ->from('products_variations')
            ->where(Operator::equals(['code' => $code]))
            ->execute()->fetchOne();

        if (!$id) {
            return null;
        }

        return (int) $id;
    }

    public function getFlexiIdFromRef(string $ref): int
    {
        $parts = explode('/', $ref);

        return (int) end($parts);
    }

    public function parseFlexiCode($itemPart): ?string
    {
        if ($itemPart instanceof Relation) {
            $itemPart = $itemPart->value;
        }

        if (is_array($itemPart)) {
            $itemPart = reset($itemPart);
        }

        $code = explode(':', $itemPart);
        $code = end($code);

        return !empty($code) ? $code : null;
    }

    public function recalculateStores(): void
    {
        $getQuantitySubQuery = function (bool $variations = false) {
            $alias = 'p';
            $productIdColumn = 'id';
            if ($variations) {
                $alias = 'pv';
                $productIdColumn = 'id_product';
            }

            $qb = sqlQueryBuilder()
                ->select('COALESCE(SUM(GREATEST(si.quantity, 0)), 0)')
                ->from('stores_items', 'si')
                ->where('si.id_product = '.$alias.'.'.$productIdColumn);

            if ($variations) {
                $qb->andWhere('si.id_variation = pv.id');
            } else {
                $qb->andWhere('si.id_variation IS NULL');
            }

            return $qb;
        };

        $productsSubQuery = $getQuantitySubQuery();

        $productsQuantityQb = sqlQueryBuilder()
            ->update('products', 'p')
            ->leftJoin('p', 'products_variations', 'pv', 'pv.id_product = p.id')
            ->set('p.in_store', "({$productsSubQuery->getSQL()})")
            ->andWhere('pv.id IS NULL AND p.in_store != ('.$productsSubQuery->getSQL().')')
            ->addQueryBuilderParameters($productsSubQuery);

        // ignorujeme sety - protoze ty se prepocitaji zvlast pomoci `setsUpdateStore->updateSetsStore()`
        if (findModule(\Modules::PRODUCT_SETS, \Modules::SUB_CALCULATE_STOCK)) {
            $productsQuantityQb->leftJoin('p', 'products_sets', 'ps', 'ps.id_product = p.id')
                ->andWhere('ps.id_product IS NULL');
        }

        $productsQuantityQb->execute();

        $variationsSubQuery = $getQuantitySubQuery(true);
        sqlQuery(
            'UPDATE products_variations pv
        SET pv.in_store = ('.$variationsSubQuery->getSQL().')
        WHERE pv.in_store != ('.$variationsSubQuery->getSQL().');', $variationsSubQuery->getParameters(), $variationsSubQuery->getParameterTypes());

        \Variations::recalcInStore();

        if (findModule(\Modules::PRODUCT_SETS, \Modules::SUB_CALCULATE_STOCK)) {
            $this->setsUpdateStore->updateSetsStore();
        }
    }

    public function synchronize(array $types, int $mode = BaseSynchronizer::MODE_NORMAL): void
    {
        foreach ($types as $type) {
            try {
                $synchronizer = $this->synchronizerLocator->getServiceByType($type);
                $synchronizer->setMode($mode);
                $synchronizer->sync();
            } catch (\Throwable $e) {
                if (isDevelopment()) {
                    throw $e;
                }

                $this->logger->exception($e, '[FlexiBee] Během synchronizace se vyskytla chyba!', [
                    'type' => $type,
                    'mode' => $mode,
                ]);
            }
        }
    }

    public function findItemByCode(string $code): ?array
    {
        $result = null;

        if ($variationId = $this->getVariationId($code)) {
            $productId = sqlQueryBuilder()
                ->select('id_product')
                ->from('products_variations')
                ->andWhere(Operator::equals(['id' => $variationId]))
                ->execute()->fetchOne();

            $result = [(int) $productId, $variationId];
        } elseif ($productId = $this->getProductId($code)) {
            $result = [$productId, null];
        }

        return $result;
    }
}