<?php

use Doctrine\DBAL\Connection;
use KupShop\CatalogBundle\ProductList\ProductCollection;
use KupShop\KupShopBundle\Context\ContextManager;
use KupShop\KupShopBundle\Context\CurrencyContext;
use KupShop\KupShopBundle\Context\SiteContext;
use KupShop\KupShopBundle\Email\WatchdogEmail;
use KupShop\KupShopBundle\Util\Contexts;
use KupShop\KupShopBundle\Util\Database\QueryHint;
use KupShop\KupShopBundle\Util\Price\Price;
use KupShop\KupShopBundle\Util\Price\PriceCalculator;
use Query\Operator;
use Query\QueryBuilder;
use Symfony\Component\HttpFoundation\RequestStack;

class WatchdogBase
{
    protected float $deleteMultiplier = 3;

    protected int $inStoreLimit = 0;

    protected RequestStack $requestStack;

    protected ContextManager $contextManager;

    private WatchdogEmail $emailService;

    public function __construct(RequestStack $requestStack, ContextManager $contextManager, WatchdogEmail $emailService)
    {
        $this->requestStack = $requestStack;
        $this->contextManager = $contextManager;
        $this->emailService = $emailService;
    }

    protected function getEmailService(): WatchdogEmail
    {
        return $this->emailService;
    }

    /**
     * @internal
     *
     * @param $user     User
     * @param $products ProductCollection
     */
    public function sendUserNotification($user, $products)
    {
        $message = $this->getEmailService()
            ->setProducts($products)
            ->setUser($user)
            ->getEmail(['EMAIL_UZIVATELE' => $user->email]);

        $message['to'] = $user->email;
        $this->getEmailService()->sendEmail($message);
    }

    /**
     * @internal
     *
     * @throws Exception
     */
    public function generateUserWatchdog($id_user)
    {
        $user = User::createFromId($id_user);

        $userAllowed = true;
        // check if user is blocked = has password, but is not active anymore
        if (!$user || (!$user->isActive() && !empty($user->passw))) {
            $userAllowed = false;
        }

        $productList = new ProductList(true);
        $productList->applyDefaultFilterParams();
        $productList->andSpec(function (QueryBuilder $qb) use ($id_user) {
            $watchdogPriceField = $this->getWatchdogPriceField($qb);

            $qb->addSelect($watchdogPriceField.' as watchdog_price, pw.currency as watchdog_currency, pw.availability as watchdog_availability')
                ->joinVatsOnProducts()
                ->join('pv', 'products_watchdog', 'pw', 'p.id = pw.id_product AND (pv.id = pw.id_variation OR pw.id_variation IS NULL) AND pw.id_user = :id_user')
                ->join('pw', 'users', 'u', 'u.id = pw.id_user')
                ->setParameter('id_user', $id_user);

            $priceField = $this->getPriceField($qb);
            $inStoreField = $this->getInStoreField($qb);

            return Operator::andX(
                Operator::orX(
                    "pw.availability = 1 AND (pw.last_in_store IS NULL OR pw.last_in_store < {$inStoreField})",
                    "{$watchdogPriceField} IS NOT NULL AND {$watchdogPriceField} > ({$priceField})"
                ),
                "{$inStoreField} > {$this->inStoreLimit}"
            );
        });
        $productList->fetchProducers();

        $productList->addResultModifiers(function (&$productCollection) use ($id_user) {
            $this->deleteUserWatchdog($id_user, $productCollection);
        });

        $productList->addResultModifiers(function (ProductCollection $products, array $data) use ($id_user) {
            foreach ($products as $productId => $product) {
                $products[$productId]->hash = $this->getWatchdogHash($id_user, $product['id'], $product['variationId']);

                $products[$productId]->watchdogPrice = !empty($data[$productId]['watchdog_price']) ? toDecimal($data[$productId]['watchdog_price']) : null;

                if ($products[$productId]->watchdogPrice) {
                    $watchdogPrice = PriceCalculator::convert(
                        new Price(
                            $products[$productId]->watchdogPrice,
                            Contexts::get(CurrencyContext::class)->getDefault(),
                            0
                        ),
                        Contexts::get(CurrencyContext::class)->getActive()
                    );

                    $products[$productId]->watchdogPrice = $watchdogPrice->getPriceWithVat();
                    $products[$productId]->isWatchdogPriceLower = PriceCalculator::firstLower($product->getProductPrice(), $watchdogPrice);
                }

                $products[$productId]->watchdogAvailability = $data[$productId]['watchdog_availability'] ?? 1;
            }
        });

        $products = $productList->getProducts();
        if ($products->count() && $userAllowed) {
            $this->sendUserNotification($user, $products);
        }
    }

    public function getWatchdogHash($id_user, $id_product = '', $id_variant = '')
    {
        return md5($id_user.$id_product.$id_variant.'Ac~+4Q~,h=8Sh%[!');
    }

    public function deleteUserWatchdog($id_user, $products)
    {
        foreach ($products as &$product) {
            $qb = sqlQueryBuilder()
                ->select('COUNT(pw.id_user) as id_users')
                ->from('products_watchdog', 'pw')
                ->join('pw', 'products', 'p', 'p.id = pw.id_product')
                ->joinVatsOnProducts()
                ->leftJoin('pw', 'products_variations', 'pvw', 'pvw.id = pw.id_variation')
                ->andWhere(
                    Operator::orX(
                        Operator::equalsNullable(['pw.id_product' => $product->id, 'pw.id_variation' => $product->variationId ?? null]),
                        Operator::equalsNullable(['pw.id_product' => $product->id, 'pw.id_variation' => null])
                    )
                );

            $qb->addSelect($this->getInStoreField($qb, 'p', 'pvw').'as in_store');
            $qb->addSelect($this->getWatchdogPriceField($qb).' as watchdog_price');

            $watchdogSelect = $qb->execute()->fetchAssociative();

            $qb = sqlQueryBuilder()
                ->select('pw.availability')
                ->from('products_watchdog', 'pw')
                ->join('pw', 'products', 'p', 'p.id = pw.id_product')
                ->joinVatsOnProducts()
                ->leftJoin('pw', 'products_variations', 'pvw', 'pvw.id = pw.id_variation')
                ->andWhere(
                    Operator::orX(
                        Operator::equalsNullable(['pw.id_user' => $id_user, 'pw.id_product' => $product->id, 'pw.id_variation' => $product->variationId ?? null]),
                        Operator::equalsNullable(['pw.id_user' => $id_user, 'pw.id_product' => $product->id, 'pw.id_variation' => null])
                    )
                );

            $qb->addSelect('('.$this->getPriceField($qb).') as price');
            $qb->addSelect($this->getWatchdogPriceField($qb).' as watchdog_price');

            $currentWatchdog = $qb->execute()->fetchAssociative();

            $filterSpec = Operator::andX(
                Operator::equals(['id_user' => $id_user]),
                Operator::orX(
                    Operator::equals(['id_product' => $product->id, 'id_variation' => $product->variationId ?? null]),
                    Operator::equalsNullable(['id_product' => $product->id, 'id_variation' => null])
                )
            );

            $update = [];
            // prestat hlidat cenu pokud klesla
            // do defalutni meny prevadi uz DB, takze prevadime defaultni menu na aktivni a az tu porovnavame
            if ($currentWatchdog && $currentWatchdog['watchdog_price']) {
                $watchdogPrice = PriceCalculator::convert(
                    new Price(toDecimal($currentWatchdog['watchdog_price']), Contexts::get(CurrencyContext::class)->getDefault(), 0),
                    Contexts::get(CurrencyContext::class)->getActive()
                );

                if (PriceCalculator::firstLower($product->getProductPrice(), $watchdogPrice)) {
                    $update['price'] = null;
                }
            }

            // zkontrolovat, ze aktualni watchdog ma zapnuty hlidani ceny - pokud nema, tak neresim podminku s poctem naskladnenych kusu a poctem hlidani
            if ((($currentWatchdog['availability'] ?? 1) == 0) || ($watchdogSelect['in_store'] * $this->deleteMultiplier) >= $watchdogSelect['id_users']) {
                $update['availability'] = 0;
            }

            // aktualizovat stav watchdoga
            if (!empty($update)) {
                if (array_key_exists('price', $update) && array_key_exists('availability', $update)) {
                    $product->removedWatchdog = true;
                }

                sqlQueryBuilder()
                    ->update('products_watchdog')
                    ->directValues($update)
                    ->andWhere($filterSpec)
                    ->execute();
            }
        }

        // vymazat neaktivni watchdogy - musi byt neaktivni hlidani ceny i dostupnosti
        $this->deleteDisabledWatchdogs();
    }

    public function generateWatchdogs(): void
    {
        $this->generateProductWatchdogs();
        $this->updateLastInStore();
    }

    private function generateProductWatchdogs(?callable $filterSpec = null): void
    {
        $qb = sqlQueryBuilder()
            ->select('pw.id_site as id')
            ->from('products_watchdog', 'pw')
            ->groupBy('pw.id_site');

        foreach ($qb->execute() as $site) {
            $this->contextManager->activateSite(
                $site['id'],
                function () use ($filterSpec, $site) {
                    $qb = sqlQueryBuilder()
                        ->select('pw.id_user')
                        ->from('products_watchdog', 'pw')
                        ->join('pw', 'products', 'p', 'p.id = pw.id_product')
                        ->joinVatsOnProducts()
                        ->leftJoin('pw', 'products_variations', 'pvw', 'pvw.id = pw.id_variation')
                        ->leftJoin('pw', 'users', 'u', 'u.id = pw.id_user')
                        ->andWhere(\Query\Product::isVisible())
                        ->andWhere("(pvw.figure = 'Y' OR pvw.figure IS NULL)") // Query\Variation::isVisible() uses different alias
                        ->andWhere(Operator::equals(['pw.id_site' => $site['id']]))
                        ->groupBy('pw.id_user');

                    $watchdogPriceField = $this->getWatchdogPriceField($qb);
                    $priceField = $this->getPriceField($qb);
                    $inStoreField = $this->getInStoreField($qb, 'p', 'pvw');

                    $qb->addSelect('('.$priceField.') as price')
                        ->andWhere(
                            Operator::andX(
                                Operator::orX(
                                    "pw.availability = 1 AND (pw.last_in_store IS NULL OR pw.last_in_store < {$inStoreField})",
                                    "{$watchdogPriceField} IS NOT NULL AND {$watchdogPriceField} > ({$priceField})"
                                ),
                                "{$inStoreField} > {$this->inStoreLimit}"
                            )
                        );

                    if ($filterSpec) {
                        $qb->andWhere($filterSpec);
                    }

                    foreach ($qb->execute() as $user) {
                        $this->generateUserWatchdog($user['id_user']);
                    }
                }
            );
        }
    }

    /**
     * @param int $id_user
     */
    public function updateLastInStore($id_user = null)
    {
        $qb = sqlQueryBuilder()
            ->update('products_watchdog', 'pw')
            ->join('pw', 'products', 'p', 'p.id = pw.id_product')
            ->leftJoin('pw', 'products_variations', 'pv', 'pv.id = pw.id_variation')
            ->leftJoin('pw', 'users', 'u', 'u.id = pw.id_user');

        if ($id_user) {
            $qb->andWhere(Operator::equals(['pw.id_user' => $id_user]));
        }

        $qb->set('pw.last_in_store', $this->getInStoreField($qb));

        $qb->execute();
    }

    public function getInStoreField(QueryBuilder $qb, string $productAlias = 'p', string $variationAlias = 'pv'): string
    {
        $inStoreField = "COALESCE({$variationAlias}.in_store, {$productAlias}.in_store)";

        if ($suppliers = findModule(\Modules::WATCHDOG, Modules::SUB_WATCHDOG_SUPPLIERS)) {
            $condition = "pos.in_store > 0 AND {$productAlias}.id = pos.id_product AND ";
            $condition .= "(({$variationAlias}.id IS NULL AND pos.id_variation IS NULL) OR ({$variationAlias}.id = pos.id_variation))";
            if (is_array($suppliers)) {
                $qb->setParameter('inIntArray_suppliers', $suppliers, Connection::PARAM_INT_ARRAY);
                $condition .= ' AND pos.id_supplier IN (:inIntArray_suppliers)';
            }
            $qb->leftJoin($productAlias, 'products_of_suppliers', 'pos', $condition);
            $inStoreSupplierField = 'COALESCE(pos.in_store, 0)';
            $inStoreField = "IF({$inStoreField} > 0, {$inStoreField}, {$inStoreSupplierField})";
        }

        return $inStoreField;
    }

    public function getPriceField(QueryBuilder $qb): string
    {
        return \Query\Product::withVatAndDiscount($qb, '(COALESCE(pv.price, p.price))');
    }

    public function getWatchdogPriceField(QueryBuilder $qb): string
    {
        if (findModule(Modules::CURRENCIES)) {
            $qb->leftJoin('pw', 'currencies', 'pw_c', 'pw_c.id = pw.currency');

            return '(pw.price*pw_c.rate)';
        }

        return 'pw.price';
    }

    public function addWatchdog(int $userId, int $productId, ?int $variationId = null, bool $availability = true, ?float $price = null): void
    {
        $id = sqlQueryBuilder()
            ->select('id')
            ->from($variationId ? 'products_variations' : 'products')
            ->where(Operator::equals(['id' => $variationId ?: $productId, 'figure' => 'Y']))
            ->execute()->fetchOne();

        if (!$id) {
            return;
        }

        $siteId = Contexts::get(SiteContext::class)->getActiveId();

        // pokud watchdog neexistuje, tak ho insertnu
        QueryHint::routeToMaster();
        if (!$this->isWatchdog($userId, $productId, $variationId)) {
            sqlQueryBuilder()
                ->insert('products_watchdog')
                ->directValues(
                    [
                        'id_site' => $siteId,
                        'id_user' => $userId,
                        'id_product' => $productId,
                        'id_variation' => $variationId,
                        'availability' => (int) $availability,
                        'price' => $price,
                        'currency' => $price ? Contexts::get(CurrencyContext::class)->getActiveId() : null,
                    ]
                )->execute();
        } else { // pokud watchdog uz existuje, tak ho aktualizuju podle zadanych parametru
            $update = [
                'id_site' => $siteId,
                'availability' => (int) $availability,
            ];
            if ($price) {
                $update['price'] = $price;
                $update['currency'] = Contexts::get(CurrencyContext::class)->getActiveId();
            }

            sqlQueryBuilder()
                ->update('products_watchdog')
                ->directValues($update)
                ->andWhere(
                    Operator::equalsNullable(
                        [
                            'id_user' => $userId,
                            'id_product' => $productId,
                            'id_variation' => $variationId,
                        ]
                    )
                )->execute();
        }
        QueryHint::routeToMaster(false);

        $this->setAddedToWatchdogSession(
            [
                'id_product' => $productId,
                'id_variation' => $variationId,
            ]
        );
    }

    /**
     * If $productId is not selected, all the user's watched products will be deleted.
     */
    public function dropWatchdog(int $userId, ?int $productId = null, ?int $variationId = null, bool $availability = true, bool $price = true): void
    {
        $qb = sqlQueryBuilder()
            ->andWhere(Operator::equals(['id_user' => $userId]));

        if ($availability && $price) {
            $qb->delete('products_watchdog');
        } else {
            $update = [];
            if ($availability) {
                $update['availability'] = 0;
            }

            if ($price) {
                $update['price'] = null;
            }

            $qb->update('products_watchdog')
                ->directValues($update);
        }

        // filter
        if ($productId) {
            $qb->andWhere(Operator::equals(['id_product' => $productId]));

            if ($variationId) {
                $qb->andWhere(Operator::equals(['id_variation' => $variationId]));
            }
        }

        $qb->execute();

        // dropnu vsechny watchdogy, ktere maji vypnute jak hlidani dostupnosti, tak hlidani ceny
        $this->deleteDisabledWatchdogs();
    }

    public function deleteDisabledWatchdogs(): void
    {
        // dropnu vsechny watchdogy, ktere maji vypnute jak hlidani dostupnosti, tak hlidani ceny
        sqlQueryBuilder()
            ->delete('products_watchdog')
            ->where(
                Operator::equalsNullable(
                    [
                        'availability' => 0,
                        'price' => null,
                    ]
                )
            )->execute();
    }

    public function getWatchdog(int $userId, int $productId, ?int $variationId = null): ?array
    {
        $watchdog = sqlQueryBuilder()
            ->select('*')
            ->from('products_watchdog')
            ->where(
                Operator::equalsNullable(
                    [
                        'id_user' => $userId,
                        'id_product' => $productId,
                        'id_variation' => $variationId,
                    ]
                )
            )->execute()->fetchAssociative();

        return $watchdog ?: null;
    }

    public function isWatchdog(int $userId, int $productId, ?int $variationId = null): bool
    {
        return (bool) $this->getWatchdog($userId, $productId, $variationId);
    }

    private function setAddedToWatchdogSession(array $data): void
    {
        if ($session = $this->requestStack->getSession()) {
            $session->set('addedToWatchdog', $data);
        }
    }

    public function deletePurchasedWatchdogProducts(): void
    {
        // STRAIGHT_JOIN použit proto, že maria vyhodnotila špatně indexy
        sqlQuery('
            DELETE pw FROM products_watchdog as pw 
            STRAIGHT_JOIN orders o ON o.id_user = pw.id_user 
            INNER JOIN order_items oi ON pw.id_product = oi.id_product AND oi.id_order = o.id 
            WHERE o.status_storno=0 AND (o.date_handle > pw.date_created and o.id_user = pw.id_user) 
                AND (o.status IN(:status)) AND (o.date_handle > (CURDATE() - INTERVAL 1 DAY))',
            ['status' => implode(',', getStatuses('handled'))]);
    }
}

if (empty($subclass)) {
    class Watchdog extends WatchdogBase
    {
    }
}