<?php

namespace KupShop\ElninoBundle\EventListener;

use Doctrine\DBAL\Connection;
use KupShop\ElninoBundle\Util\ReclamationReturnTicket;
use KupShop\KupShopBundle\Config;
use KupShop\KupShopBundle\Email\OrderMessageEmail;
use KupShop\KupShopBundle\Event\CronEvent;
use KupShop\KupShopBundle\Query\JsonOperator;
use Query\Operator;
use Symfony\Component\EventDispatcher\EventSubscriberInterface;

class CronListener implements EventSubscriberInterface
{
    public const DISCOUNT_INTERVAL = 31;

    public static function getSubscribedEvents()
    {
        return [
            CronEvent::RUN_NORMAL => [
                ['handleNormal', 200],
                ['sendIdealoEmails', 200],
            ],
            CronEvent::RUN_FREQUENT => [
                ['syncElninoReclamations', 200],
                ['syncElninoReturns', 200],
                ['runAbraSync', 200],
            ],
        ];
    }

    /** @var ReclamationReturnTicket */
    protected $ticketUpdater;

    /**
     * @required
     */
    public function setTicketUpdater(ReclamationReturnTicket $ticketUpdater): void
    {
        $this->ticketUpdater = $ticketUpdater;
    }

    public function syncElninoReclamations(CronEvent $cronEvent)
    {
        $settings = \Settings::getDefault();
        if (findModule(\Modules::RECLAMATIONS) && $settings['elnino_customer_service_api']['reclamations']['active'] == 'Y') {
            $this->ticketUpdater->processReclamations();
        }
    }

    public function syncElninoReturns(CronEvent $cronEvent)
    {
        $settings = \Settings::getDefault();
        if (findModule(\Modules::RETURNS) && $settings['elnino_customer_service_api']['returns']['active'] == 'Y') {
            $this->ticketUpdater->processReturns();
        }
    }

    public function runAbraSync(CronEvent $cronEvent)
    {
        if (findModule('abra', 'sync_in_cron')) {
            $abra = new \AbraElnino(Config::get()['Modules']['abra']);
            $abra->loopSychronization(1800, true);
        }
    }

    public function handleNormal(CronEvent $event)
    {
        $this->updateCatalogParameters();
        $this->sortParameterValues();
        // $this->setCategoriesSorting();

        if (findModule(\Modules::PRICE_HISTORY) && \AbraElnino::$PRODUCT_FIELDS['PriceWithVAT'] == 'price_vat_history') {
            $this->updateDisplayDiscount();
        } else {
            $this->removeOldDiscount();
        }
        $this->addDiscountFlag();
        // $this->handleImagesMissingMain();
        $this->countSoldItems();
        $this->updateBestSellers();
        $this->refreshProducts();
    }

    public function sendIdealoEmails()
    {
        $hourOfDay = (int) date('H');
        if ($hourOfDay < 8 || $hourOfDay > 20) {
            // wrong day hour...
            return;
        }

        $config = Config::get()['Modules']['orders']['idealo'] ?? null;
        if (empty($days = $config['afterDays'] ?? null) || empty($order_message = $config['order_message'] ?? null)) {
            return;
        }

        $order_message = sqlQueryBuilder()->select('e.name')->from('emails', 'e')
            ->where(Operator::equals(['e.type' => OrderMessageEmail::getType()]))
            ->andWhere(Operator::equals(['e.name' => $order_message]))
            ->execute()->fetchColumn();
        if (!$order_message) {
            return;
        }

        $referer = (empty($config['referer']) ? 'idealo.de' : $config['referer']);

        $orderResults = sqlQueryBuilder()->select('o.id')->from('orders', 'o')
            ->where(\Query\Order::byStatus(getStatuses('handled')))
            ->andWhere('o.status_storno = 0')
            ->andWhere('o.date_handle IS NOT NULL')
            ->andWhere('o.date_handle BETWEEN DATE_SUB(NOW(), INTERVAL 12 DAY) AND DATE_SUB(NOW(), INTERVAL 6 DAY)')
            ->andWhere(Operator::like([JsonOperator::value('note_admin', 'referer') => "%{$referer}%"]))
            ->andWhere(Operator::not(JsonOperator::exists('note_admin', 'idealo_email_sent')))
            ->groupBy('o.id')
            ->orderBy('o.date_handle', 'ASC')
            ->setParameter('days', $days);

        foreach ($orderResults->execute() as $orderResult) {
            $order = new \Order();
            $order->createFromDB($orderResult['id']);
            $order->changeStatus($order->status, null, true, $order_message);
            $order->setData('idealo_email_sent', true);
        }
    }

    private function getImagesMissingMain()
    {
        return sqlFetchAll(sqlQuery('SELECT id_product FROM photos_products_relation GROUP BY id_product HAVING MAX(show_in_lead)=\'N\''), 'id_product');
    }

    private function handleImagesMissingMain()
    {
        $IDs = $this->getImagesMissingMain();

        foreach ($IDs as $id_product => $_) {
            \Photos::checkLeadPhoto('photos_products_relation', 'id_product', $id_product);
        }
    }

    private function updateCatalogParameters()
    {
        // Zaskrtnout u vyrobcu pohlavi + Produktove rady + Sortiment:
        sqlQuery('INSERT IGNORE INTO `parameters_producers` (`id_parameter`, `id_producer`)
            SELECT p.id, s.id
            FROM parameters p
            CROSS JOIN producers s
            WHERE p.id IN (18, 28)');
    }

    protected function countSoldItems()
    {
        // count sold products - one month
        $countSubquery = sqlQueryBuilder()->select('SUM(oi.pieces)')
            ->from('order_items', 'oi')
            ->innerJoin('oi', 'orders', 'o', 'oi.id_order = o.id')
            ->where('oi.id_product = p.id')
            ->andWhere('o.date_created BETWEEN DATE_SUB(NOW(), INTERVAL :interval DAY) AND NOW()');

        sqlQueryBuilder()->update('products', 'p')
            ->set('pieces_sold', '('.$countSubquery->getSQL().')')
            ->setParameters([
                'interval' => 365,
            ])
            ->execute();

        sqlQueryBuilder()->update('products', 'p')
            ->set('pieces_sold2', '('.$countSubquery->getSQL().')')
            ->setParameters([
                'interval' => 45,
            ])
            ->execute();

        sqlQueryBuilder()->update('products', 'p')
            ->set('pieces_sold3', '('.$countSubquery->getSQL().')')
            ->setParameters([
                'interval' => 7,
            ])
            ->execute();
    }

    private function sortParameterValues()
    {
        sqlQuery('SELECT @i := -1;
               UPDATE parameters_list pl
               SET position = (select @i := @i + 1)
               WHERE id_parameter=28
               ORDER BY pl.value');
    }

    private function setCategoriesSorting()
    {
        // Set sorting to all abra categories
        sqlQuery('UPDATE sections s
            JOIN abra_sections abs ON abs.id_section = s.id
            SET s.orderby = \'sell\', s.orderdir = \'DESC\';');

        sqlQueryBuilder()->update('producers')
            ->set('orderby', '"sell"')
            ->set('orderdir', '"DESC"')
            ->execute();
    }

    private function removeOldDiscount()
    {
        $SQL = sqlQueryBuilder()->update('products')
            ->set('discount_date', ':null')
            ->set('display_discount', ':null')
            ->setParameter('null', null)
            ->where('discount_date < NOW() - INTERVAL :interval DAY')
            ->setParameter('interval', self::DISCOUNT_INTERVAL)
            ->execute();
        // remove discount flag if there is not discount
        sqlQuery('UPDATE products SET campaign = REMOVE_FROM_SET("D", campaign) 
                  WHERE (display_discount IS NULL OR display_discount <= 0) AND (discount IS NULL OR discount <= 0) ');

        var_dump(['Odstraneno starych slev ', $SQL]);
    }

    private function addDiscountFlag()
    {
        sqlQuery('UPDATE products SET campaign = ADD_TO_SET("D", campaign)
                  WHERE (display_discount IS NOT NULL AND display_discount > 0) OR (discount IS NOT NULL AND discount > 0)');
    }

    private function updateBestSellers()
    {
        sqlQuery('UPDATE products SET campaign = REMOVE_FROM_SET("B", campaign) WHERE FIND_IN_SET("B", campaign);');

        // pro vsechny hlavni sekce z abry
        $topSections = sqlQuery('SELECT sr.id_section as id FROM sections_relation sr
                JOIN abra_sections abs ON abs.id_section = sr.id_section
                WHERE sr.id_topsection IS NULL AND sr.id_section != 0')->fetchAll();
        if (count($topSections) <= 0) {
            // ignore abra_sections relation when sections are divided into men/women/children (fix #9747)
            $topSections = sqlQuery('SELECT sr.id_section as id FROM sections_relation sr
                WHERE sr.id_topsection IS NULL AND sr.id_section != 0')->fetchAll();
        }
        foreach ($topSections as $topSection) {
            $sections = getDescendantCategories($topSection['id']);
            // nedal jsem to cely jednim dotazem, protoze v UPDATE nejde pouzit ORDER BY, LIMIT pokud v nem pouzivam JOIN :/
            $products = sqlQueryBuilder()->select('p.id')
                ->from('products', 'p')
                ->leftJoin('p', 'products_in_sections', 'pis', 'pis.id_product = p.id')
                ->andWhere('p.figure = \'Y\' AND in_store > 0 AND price > 0 AND p.pieces_sold > 0')
                ->andWhere(Operator::inIntArray($sections, 'pis.id_section'))
                ->orderBy('p.pieces_sold', 'DESC')
                ->setMaxResults(45)->execute()->fetchAll();
            if (!empty($products)) {
                $products = array_map(
                    function ($x) {
                        return (int) $x['id'];
                    },
                    $products
                );
                sqlQuery('UPDATE products SET campaign = ADD_TO_SET("B", campaign)
                  WHERE id IN (:id_products)', ['id_products' => $products], ['id_products' => Connection::PARAM_INT_ARRAY]);
            }
        }
    }

    private function refreshProducts()
    {
        var_dump(['Start refresh products']);

        sqlQuery("
         UPDATE products SET
             campaign = REMOVE_FROM_SET('', campaign)
         WHERE
             FIND_IN_SET('M', campaign);
         ");

        sqlQuery("
         UPDATE products as p
             JOIN (
                 SELECT pp.id
                 FROM products pp
                 JOIN (
                        SELECT MIN(price) AS minprice, id_model
                        FROM products
                        WHERE price IS NOT NULL AND in_store > 0
                        GROUP BY id_model
                      ) AS t1 ON t1.id_model = pp.id_model AND t1.minprice = pp.price
                          AND NOT FIND_IN_SET(pp.campaign, 'V')
                 GROUP BY pp.id_model
             ) as t1 ON t1.id = p.id
         SET p.campaign = CONCAT_WS(',',p.campaign,'M');
      ");

        sqlQuery("
         UPDATE products SET
              campaign = REMOVE_FROM_SET('L', campaign)
            WHERE
              FIND_IN_SET('L', campaign);
      ");

        sqlQuery("
         UPDATE products as p
            JOIN (
               SELECT p.id
               FROM products p
                 JOIN (
                        SELECT MIN(price) as minprice, id_model, size_value
                        FROM products
                        WHERE id_model IS NOT NULL AND in_store > 0
                        GROUP BY id_model, size_value
                      ) as p_minp ON p_minp.id_model=p.id_model AND p_minp.minprice=p.price AND p_minp.size_value=p.size_value
                 JOIN (
                        SELECT MAX(size_value) as maxsize, id_model
                        FROM products
                        WHERE id_model IS NOT NULL AND in_store > 0
                        GROUP BY id_model
                      ) as p_maxs ON p_maxs.id_model=p.id_model AND p_maxs.maxsize=p.size_value
               WHERE p.id_model IS NOT NULL
               GROUP BY p.id_model
            ) as t1 ON t1.id = p.id
         SET p.campaign = IF(p.campaign = '', 'L', CONCAT_WS(',',p.campaign,'L'));
      ");

        var_dump(['End refresh products']);
    }

    protected function updateDisplayDiscount()
    {
        sqlQuery('UPDATE products 
                SET display_discount=GREATEST(0, FLOOR((1 - (price / price_for_discount)) * 100)) 
                WHERE figure="Y" AND price > 0 AND price_for_discount > 0 AND display_discount != GREATEST(0, FLOOR((1 - (price / price_for_discount)) * 100)) ');

        sqlQuery('UPDATE products SET display_discount = 0 WHERE display_discount < 3');
        // remove discount flag if there is not discount
        sqlQuery('UPDATE products SET campaign = REMOVE_FROM_SET("D", campaign) 
                  WHERE (display_discount IS NULL OR display_discount <= 0) AND (discount IS NULL OR discount <= 0) ');
    }
}
