<?php

namespace External\VarioBundle\Util;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Exception\DeadlockException;
use Doctrine\DBAL\Exception\LockWaitTimeoutException;
use External\VarioBundle\Exception\SynchronizerException;
use External\VarioBundle\Exception\VarioException;
use KupShop\AdminBundle\Util\ActivityLog;
use KupShop\KupShopBundle\Util\Database\QueryHint;
use KupShop\KupShopBundle\Util\Logging\SentryLogger;
use Query\Operator;

class VarioHelper
{
    use \DatabaseCommunication;

    private $synchronizerLocator;

    private $sentryLogger;

    public function __construct(SynchronizerLocator $synchronizerLocator, SentryLogger $sentryLogger)
    {
        $this->synchronizerLocator = $synchronizerLocator;
        $this->sentryLogger = $sentryLogger;
    }

    public function logException(\Throwable $e, ?string $message = null, array $data = []): void
    {
        if (isLocalDevelopment()) {
            throw $e;
        }

        if (!($e instanceof VarioException) && !($e instanceof SynchronizerException)) {
            $this->sentryLogger->captureException($e);
        }

        if ($message === null) {
            $message = '[Vario] '.$e->getMessage();
        }

        $data = array_merge(
            [
                'exception_message' => $e->getMessage(),
            ],
            $data
        );

        // Lock wait timeout a deadlock nechci logovat do ActivityLogu
        if ($e instanceof LockWaitTimeoutException || $e instanceof DeadlockException) {
            return;
        }

        addActivityLog(
            ActivityLog::SEVERITY_ERROR,
            ActivityLog::TYPE_SYNC,
            $message,
            array_unique($data)
        );
    }

    public function createProductMapping(string $varioId, int $productId, ?int $variationId = null): void
    {
        $this->insertSQL('vario_products', [
            'id_vario' => $varioId,
            'id_product' => $productId,
            'id_variation' => $variationId,
        ]);
    }

    public function getVarioID(string $mappingTable, $eshopID)
    {
        return $this->selectSQL($mappingTable, [$this->getForeignColumnFromTable($mappingTable) => $eshopID], ['id_vario'])->fetchColumn();
    }

    public function getMapping(string $mappingTable, string $objectID)
    {
        $objectID = $this->trimVarioId($objectID);

        return $this->selectSQL($mappingTable, ['id_vario' => $objectID], [$this->getForeignColumnFromTable($mappingTable)])->fetchColumn();
    }

    public function createMapping(string $mappingTable, string $objectID, int $eshopID)
    {
        $objectID = $this->trimVarioId($objectID);

        $this->insertSQL($mappingTable, ['id_vario' => $objectID, $this->getForeignColumnFromTable($mappingTable) => $eshopID]);
    }

    public function getOrderAdditionalInfo($orderID, $key = null)
    {
        $data = $this->selectSQL('vario_orders', ['id_order' => $orderID], ['data'])->fetchColumn();
        if (!empty($data)) {
            $data = json_decode($data, true);
            if ($key === null) {
                return $data;
            }

            return $data[$key] ?? null;
        }

        return [];
    }

    public function saveOrderAdditionalInfo($orderID, $key, $value)
    {
        $data = $this->getOrderAdditionalInfo($orderID);
        $data[$key] = $value;

        $this->updateSQL('vario_orders', ['data' => json_encode($data)], ['id_order' => $orderID]);

        return true;
    }

    public function getForeignColumnFromTable(string $mappingTable)
    {
        return 'id_'.substr(str_replace('vario_', '', $mappingTable), 0, -1);
    }

    public function trimVarioId(string $varioId)
    {
        return trim($varioId, '{}');
    }

    public function getPhotoId($name)
    {
        return $this->selectSQL('photos', ['image_2' => $name], ['id'])->fetchColumn();
    }

    public function savePhotoToDB($source, $name)
    {
        $this->insertSQL('photos', [
            'source' => $source,
            'image_2' => $name,
            'sync_id' => $name,
        ]);

        return sqlInsertId();
    }

    /**
     * @return null
     *
     * @throws \Exception
     */
    public function findVariation($varioVariationID, $productID)
    {
        $variationId = null;
        if (!empty($varioVariationID)) {
            $variationIds = $this->selectSQL('vario_variations', ['id_vario' => $varioVariationID])->fetchAll();
            if (!empty($variationIds)) {
                $variationIds = array_map(
                    function ($x) {
                        return $x['id_variation'];
                    },
                    $variationIds
                );
                $id = sqlQueryBuilder()->select('id')->from('products_variations')
                    ->where(Operator::equals(['id_product' => $productID]))
                    ->andWhere(Operator::inIntArray($variationIds, 'id'))
                    ->execute()->fetchColumn();
                if ($id) {
                    $variationId = $id;
                } else {
                    return false;
                }
            } else {
                // we do not have variation
                return false;
            }
        }

        return $variationId;
    }

    public function getIgnoredStores()
    {
        $ignoredStores = [];

        $stores = $this->selectSQL('vario_stores', ['disabled' => 1]);

        foreach ($stores as $store) {
            $ignoredStores[] = $store['id_store'];
        }

        return $ignoredStores;
    }

    public function mb_ucfirst(string $string): string
    {
        return mb_strtoupper(mb_substr($string, 0, 1)).mb_substr($string, 1);
    }

    public function parseUserFields($userFields)
    {
        $xmlString = '<?xml version="1.0" encoding="utf-8"?><fields>'.$userFields.'</fields>';
        $xml = simplexml_load_string($xmlString);

        return $xml;
    }

    public function addToUserFields($userFields, $elementName, $value)
    {
        $xml = $this->parseUserFields($userFields);

        $changes = $xml->xpath('/fields/'.$elementName);
        if (!empty($changes)) {
            foreach ($changes as $change) {
                $change[0] = $value;
            }
        } else {
            $xml->addChild($elementName, $value);
        }

        return trim(str_replace(['<?xml version="1.0" encoding="utf-8"?>', '<fields>', '</fields>'], ['', '', ''], $xml->asXML()));
    }

    public function recalculateStores()
    {
        $ignoredStores = $this->getIgnoredStores();

        sqlGetConnection()->transactional(function () use ($ignoredStores) {
            if (!empty($ignoredStores)) {
                sqlQuery('UPDATE products p
                    JOIN vario_products vp ON vp.id_product = p.id
                    LEFT JOIN products_variations pv ON p.id = pv.id_product
                    SET p.in_store = (
                        SELECT COALESCE(SUM(GREATEST(quantity, 0)), 0) FROM stores_items WHERE id_product = p.id AND id_variation IS NULL
                    ) WHERE pv.id IS NULL AND p.in_store != (SELECT COALESCE(SUM(quantity), 0) FROM stores_items WHERE id_product = p.id AND id_variation IS NULL AND id_store NOT IN (:ignored_stores));',
                    ['ignored_stores' => $ignoredStores],
                    ['ignored_stores' => Connection::PARAM_INT_ARRAY]
                );

                sqlQuery(
                    'UPDATE products_variations pv
                    SET pv.in_store = (SELECT COALESCE(SUM(GREATEST(quantity, 0)), 0)  FROM stores_items WHERE id_product = pv.id_product AND id_variation = pv.id AND id_store NOT IN (:ignored_stores))
                    WHERE pv.in_store != (SELECT COALESCE(SUM(GREATEST(quantity, 0)), 0)  FROM stores_items WHERE id_product = pv.id_product AND id_variation = pv.id AND id_store NOT IN (:ignored_stores));',
                    ['ignored_stores' => $ignoredStores],
                    ['ignored_stores' => Connection::PARAM_INT_ARRAY]
                );
            } else {
                sqlQuery('UPDATE products p
                    JOIN vario_products vp ON vp.id_product = p.id
                    LEFT JOIN products_variations pv ON p.id = pv.id_product    
                    SET p.in_store = (
                        SELECT COALESCE(SUM(GREATEST(quantity, 0)), 0) FROM stores_items WHERE id_product = p.id AND id_variation IS NULL
                    ) WHERE pv.id IS NULL AND p.in_store != (SELECT COALESCE(SUM(GREATEST(quantity, 0)), 0) FROM stores_items WHERE id_product = p.id AND id_variation IS NULL);');

                sqlQuery(
                    'UPDATE products_variations pv
                    SET pv.in_store = (SELECT COALESCE(SUM(GREATEST(quantity, 0)), 0)  FROM stores_items WHERE id_product = pv.id_product AND id_variation = pv.id)
                    WHERE pv.in_store != (SELECT COALESCE(SUM(GREATEST(quantity, 0)), 0)  FROM stores_items WHERE id_product = pv.id_product AND id_variation = pv.id);'
                );
            }

            \Variations::recalcInStore();
        });
    }

    public function doForceSync(string $objectId, string $type): void
    {
        $synchronizer = $this->synchronizerLocator->getServiceByType($type);
        $synchronizer->setDebug(false);
        $synchronizer->setForceSync($objectId);
        $synchronizer->sync();
        $synchronizer->setForceSync(null);
    }

    public function updateProductVisibility(): void
    {
        // hide visible products without price
        sqlQueryBuilder()
            ->update('products')
            ->directValues(['figure' => 'O'])
            ->where(
                Operator::equals(
                    [
                        'figure' => 'Y',
                    ]
                )
            )
            ->andWhere('price <= 0')
            ->execute();

        // display
        sqlQueryBuilder()
            ->update('products', 'p')
            ->leftJoin('p', 'products_of_suppliers', 'pos', 'pos.id_product = p.id')
            ->set('p.figure', ':figure')
            ->setParameter('figure', 'Y')
            ->where(
                Operator::orX(
                    Operator::equals(['p.figure' => 'N']),
                    Operator::equals(['JSON_VALUE(p.data, "$.realVisibility")' => 'Y'])
                )
            )
            ->andWhere('p.price > 0 AND (p.in_store + (SELECT COALESCE(SUM(pos.in_store), 0) FROM products_of_suppliers pos WHERE pos.id_product = p.id)) > 0')
            ->execute();
    }

    public function addDelayedUpdate(string $varioId, string $type): void
    {
        $exists = sqlQueryBuilder()
            ->select('id_vario')
            ->from('vario_delayed_update')
            ->andWhere(
                Operator::equals(
                    [
                        'id_vario' => $varioId,
                        'type' => $type,
                    ]
                )
            )->forUpdate()->execute()->fetchOne();

        if (!$exists) {
            sqlQueryBuilder()
                ->insert('vario_delayed_update')
                ->directValues(
                    [
                        'id_vario' => $varioId,
                        'type' => $type,
                    ]
                )->execute();
        }
    }

    public function synchronize(array $types): void
    {
        QueryHint::routeToMaster();

        foreach ($types as $type) {
            try {
                $synchronizer = $this->synchronizerLocator->getServiceByType($type);
                $synchronizer->setDebug(false);
                $synchronizer->sync();
            } catch (\Exception $e) {
                if (isDevelopment()) {
                    throw $e;
                }

                if ($e instanceof \SoapFault || $e instanceof VarioException) {
                    addActivityLog(
                        ActivityLog::SEVERITY_ERROR,
                        ActivityLog::TYPE_SYNC,
                        sprintf('[Vario] Chyba během synchronizace: %s', $e->getMessage()),
                        ['exception' => $e->getMessage()]
                    );
                }

                $this->sentryLogger->captureException($e);
            }
        }
    }

    public function getHTMLPurifier(): \HTMLPurifier
    {
        static $purifier;

        if (!$purifier) {
            $config = \HTMLPurifier_Config::createDefault();
            $config->set('CSS.AllowedProperties', []);
            $config->set('HTML.ForbiddenAttributes', ['class', 'id']);
            $config->set('AutoFormat.RemoveEmpty', true);
            $config->set('AutoFormat.RemoveSpansWithoutAttributes', true);

            $purifier = new \HTMLPurifier($config);
        }

        return $purifier;
    }

    public function createUnregistredUserFromOrder(\Order $order)
    {
        try {
            $synchronizer = $this->synchronizerLocator->getServiceByType('CreateOrUpdateCustomer');

            return $synchronizer->createUnregistredUserFromOrder($order);
        } catch (\Exception $e) {
            if (isDevelopment()) {
                throw $e;
            }

            if ($e instanceof \SoapFault || $e instanceof VarioException) {
                addActivityLog(
                    ActivityLog::SEVERITY_ERROR,
                    ActivityLog::TYPE_SYNC,
                    sprintf('[Vario] Chyba během synchronizace: %s', $e->getMessage()),
                    ['exception' => $e->getMessage()]
                );
            }

            $this->sentryLogger->captureException($e);
        }
    }

    // Instead of updating all products, find products ids that will really result in update and run the update only on them.
    // It reduces amount of locked rows -> reduces deadlocks
    public function updateProductsPriceToMinFromVariations()
    {
        $sqlMinPriceVariationQb = sqlQueryBuilder()->select('MIN(pv2.price)')->from('products_variations', 'pv2')
            ->where('pv2.id_product = p.id');

        $productsToUpdate = sqlQueryBuilder()->select('p.id')->from('products', 'p')
            ->join('p', 'products_variations', 'pv', 'p.id = pv.id_product')
            ->where('p.price != ('.$sqlMinPriceVariationQb->getSQL().')')
            ->setMaxResults(1000)
            ->execute()->fetchFirstColumn();

        $updateQb = sqlQueryBuilder()->update('products', 'p')
            ->join('p', 'products_variations', 'pv', 'p.id = pv.id_product')
            ->set('p.price', 'COALESCE(('.$sqlMinPriceVariationQb->getSQL().'), p.price)');

        if (count($productsToUpdate) < 1000) {
            $updateQb->where(Operator::inIntArray($productsToUpdate, 'p.id'));
        }

        $updateQb->execute();
    }
}
