<?php

namespace KupShop\MarketingBundle\Admin\Tabs;

use KupShop\AdminBundle\Admin\WindowTab;
use KupShop\AdminBundle\Util\ActivityLog;
use KupShop\AdminBundle\Util\Filter\OrdersFilterSpecs;
use KupShop\AdminBundle\Util\UsersFilterSpecs;
use KupShop\CatalogBundle\Util\ProductsFilterSpecs;
use KupShop\KupShopBundle\Util\Excel\ExcelGenerator;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use Query\QueryBuilder;

class StatsMailerliteTab extends WindowTab
{
    protected $title = 'flapMailerlite';

    protected $template = 'window/tabs/statsMailerliteTab.tpl';

    private $ordersFilterSpecs;
    private $productsFilterSpecs;
    private $usersFilterSpecs;

    public function __construct(OrdersFilterSpecs $ordersFilterSpecs, ProductsFilterSpecs $productsFilterSpecs, UsersFilterSpecs $usersFilterSpecs)
    {
        $this->ordersFilterSpecs = $ordersFilterSpecs;
        $this->productsFilterSpecs = $productsFilterSpecs;
        $this->usersFilterSpecs = $usersFilterSpecs;
    }

    public static function getTypes()
    {
        return [
            'stats' => 94,
        ];
    }

    public function getVars($smarty_tpl_vars)
    {
        $vars = [];

        if (getVal('showUsersStat')) {
            $vars['rowNumber'] = $this->getCountUserData();
            $vars['usersData'] = $this->getShownData();
        }

        return $vars;
    }

    public function handleGetMailerlite()
    {
        set_time_limit(60 * 5);

        $generator = new ExcelGenerator();

        $filename = 'Users_'.date('Y-m-d_H-i').'.xlsx';

        $generator->generateExcel($this->getHeader(), $this->loadData(), $filename);

        addActivityLog(ActivityLog::SEVERITY_NOTICE, ActivityLog::TYPE_SECURITY, "Export uživatelů: {$filename}");
        exit;
    }

    protected function loadData()
    {
        sqlGetConnection()->getWrappedConnection()->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

        foreach ($this->GetUserData()->execute() as $product) {
            yield [
                $product['id'] ?? null,
                $product['name'] ?? null,
                $product['surname'] ?? null,
                $product['email'],
                $product['phone'] ?? null,
                (empty($product['date_reg']) || $product['date_reg'] == '0000-00-00 00:00:00') ? 'N' : 'Y',
                $product['count'] ?? null,
                $product['price'] ?? null,
                $product['average_price'] ?? null,
                $product['mindate'] ?? null,
                $product['maxdate'] ?? null,
                ($product['get_news'] == 'Y') ? 'Y' : 'N',
            ];
        }
    }

    protected function getHeader(): array
    {
        return [
            ['name' => 'ID', 'type' => DataType::TYPE_NUMERIC],
            ['name' => 'Jméno', 'type' => DataType::TYPE_STRING],
            ['name' => 'Přijmení', 'type' => DataType::TYPE_STRING],
            ['name' => 'Email', 'type' => DataType::TYPE_STRING],
            ['name' => 'Tel. číslo', 'type' => DataType::TYPE_STRING],
            ['name' => 'Registrován', 'type' => DataType::TYPE_STRING],
            ['name' => 'Počet objednávek', 'type' => DataType::TYPE_NUMERIC],
            ['name' => 'Suma', 'type' => DataType::TYPE_NUMERIC, 'format' => '0.00'],
            ['name' => 'Průměrná cena', 'type' => DataType::TYPE_NUMERIC, 'format' => '0.00'],
            ['name' => 'První nákup', 'type' => 'datetime', 'format' => 'd.m.yyyy hh:mm:ss'],
            ['name' => 'Poslední nákup', 'type' => 'datetime', 'format' => 'd.m.yyyy hh:mm:ss'],
            ['name' => 'Newsletter', 'type' => DataType::TYPE_STRING],
        ];
    }

    public function GetUserData()
    {
        $actionTypeSet = getVal('actionTypeSet');

        $query = sqlQueryBuilder()
            ->select('u.id, u.name, u.surname, u.phone, u.get_news, u.date_reg, u.email, COUNT(o.invoice_email) as count, SUM(o.total_price * o.currency_rate) as price, AVG(o.total_price * o.currency_rate) as average_price, MAX(o.date_created) as maxdate, MIN(o.date_created) as mindate')
            ->from('users', 'u')
            ->leftJoin('u', 'orders', 'o', 'u.email = o.invoice_email')
            ->andWhere('o.status_storno != 1')
            ->groupBy('u.email');

        switch ($actionTypeSet) {
            case 'newsletter':
                $query->andWhere('u.get_news = "Y"');

                break;
            case 'just_buy':
                $query->andWhere('u.get_news != "Y" AND u.date_reg IS NULL AND u.passw = ""')
                    ->having('COUNT(o.invoice_email) >= 1');

                break;
        }

        $query = $this->applySpecsToQuery($query);

        return $query;
    }

    private function applySpecsToQuery(QueryBuilder $query)
    {
        $filter = getVal('filter', null, []);
        $specs = $this->productsFilterSpecs->getSpecs($filter);
        if ($specs) {
            $subQuery = sqlQueryBuilder()->select('oi.id_order')
                ->from('order_items', 'oi')
                ->leftJoin('oi', 'products', 'p', 'oi.id_product = p.id')
                ->andWhere($specs)
                ->groupBy('oi.id_order');
            $query->joinSubQuery('o', $subQuery, 'oi', 'o.id = oi.id_order');
        }
        $specs = $this->ordersFilterSpecs->getSpecs($filter);
        if ($specs) {
            $query->andWhere($specs);
        }

        $specs = $this->usersFilterSpecs->getSpecs($filter);
        if ($specs) {
            $query->andWhere($specs);
        }

        return $query;
    }

    public function getCountUserData()
    {
        $qb = $this->GetUserData();

        return returnSQLResult("SELECT COUNT(*) FROM ({$qb->getSQL()}) a", $qb->getParameters(), $qb->getParameterTypes());
    }

    public function getShownData()
    {
        $query = $this->GetUserData()
            ->setMaxResults(20)
            ->execute()
            ->fetchAll();

        return $query;
    }
}
