<?php

namespace KupShop\ElninoBundle\Util;

use Doctrine\DBAL\Connection;
use KupShop\KupShopBundle\Context\LanguageContext;
use Query\Operator;
use Query\Order;

class UsersExport
{
    public $divide = 3000;
    protected $noPages = 0;
    protected $filters = [];

    // true value for converting to int
    protected $field_names = [
        'newsletter_logout_link' => ['unsubscribe_link', false],
        'newsletter_login_link' => ['subscribe_link', false],
        'name' => ['salutation_firstname', false],
        'surname' => ['salutation_lastname', false],
        'last_order_raw' => ['last_order', false],
        'post_code_raw' => ['post_code', true],
        'sum_orders_raw' => ['sum_orders', true],
        'sum_orders_price_higher_raw' => ['sum_orders_price_higher', true],
        'sum_orders_price_lower_raw' => ['sum_orders_price_lower', true],
        'sum_orders_price_raw' => ['sum_orders_price', true],
        'delivery_office_raw' => ['delivery_office', true],
        'id_language' => ['language', false],
    ];

    private $languageContext;

    public function __construct(LanguageContext $languageContext)
    {
        $this->languageContext = $languageContext;
    }

    public function run($params = null, $routeName = 'jsonUsersExport')
    {
        $query = $this->loadData($params);

        return $this->procesData($query, $routeName);
    }

    /**
     * @return \Doctrine\DBAL\Driver\Statement|int
     */
    public function loadData($params = null)
    {
        $fields = ['s.id', 's.email', 's.surname', 's.name'];

        if (findModule(\Modules::TRANSLATIONS)) {
            $fields[] = 'COALESCE(o.id_language, "'.$this->languageContext->getDefaultId().'") as id_language';
        }

        $pager = new \Pager();
        $pager->setOnPage($this->divide);
        $pager->setPageNumber((int) getVal('page', null, 1));

        $spec = $pager->getSpec();
        $qb = sqlQueryBuilder()->select('SQL_CALC_FOUND_ROWS email')
            ->from('users', 's')
            ->andWhere(Operator::andX($spec))
            ->groupBy('s.id')
            ->orderBy('s.id');
        if ($params) {
            $qb->andWhere($params);
        }

        $emails = array_column($qb->execute()->fetchAll(), 'email');

        $results = (int) sqlFetchAssoc(sqlQuery('SELECT FOUND_ROWS() as total_count'))['total_count'];
        $this->noPages = ceil($results / $this->divide);

        $qb = sqlQueryBuilder()->select($fields)
            ->from('users', 's')
            ->leftJoin('s', 'orders', 'o', 'o.invoice_email = s.email')
            ->andWhere('s.email IN (:emails)')
            ->setParameter('emails', $emails, Connection::PARAM_STR_ARRAY);

        $sum = '';
        if (findModule('currencies')) {
            $sum = ' * currency_rate';
        }

        foreach ($this->filters as $filter) {
            $subQuery = sqlQueryBuilder()->select('o.invoice_email')
                ->from('orders', 'o')
                ->andWhere(Order::byStatus(getStatuses('handled')))
                ->andWhere(Operator::equals(['o.status_storno' => '0']))
                ->andWhere(Operator::inStringArray($emails, 'o.invoice_email'))
                ->setParameter('emails', $emails, Connection::PARAM_STR_ARRAY)
                ->groupBy('o.invoice_email');

            switch ($filter->tag) {
                case 'last_order':
                    $subQuery->addSelect('MAX(o.date_created) as last_order_raw');

                    $qb->leftJoin('s', sprintf('(%s)', $subQuery->getSQL()), 'lo', 's.email = lo.invoice_email')
                        ->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes());
                    $fields[] = 'lo.last_order_raw';
                    break;
                case 'post_code':
                    if (!$this->checkParam($filter->params)) {
                        break;
                    }

                    $equals = [];
                    foreach ($filter->params as $id => $param) {
                        if (strlen(trim($param)) == 5) {
                            $equals[] = Operator::equals(['o.delivery_zip' => $param]);
                        } else {
                            $equals[] = Operator::like(['o.delivery_zip' => $param.'%']);
                        }
                    }

                    $subQuery->andWhere(Operator::orX($equals));

                    $qb->leftJoin('s', sprintf('(%s)', $subQuery->getSQL()), 'lo2', 's.email = lo2.invoice_email')
                        ->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes());

                    $fields[] = 'IF(lo2.invoice_email IS NULL,0,1) as post_code_raw';

                    break;
                case 'delivery_office':
                    $subQuery->andwhere(Order::byInPersonDelivery(true));

                    $qb->leftJoin('s', sprintf('(%s)', $subQuery->getSQL()), 'do', 's.email = do.invoice_email')
                        ->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes());
                    $fields[] = 'IF(do.invoice_email IS NULL,0,1) as delivery_office_raw';

                    break;
                case 'sum_orders':
                    if (!$this->checkParam($filter->params, 1)) {
                        break;
                    }
                    $subQuery->addSelect('COUNT(distinct o.id) as sum_orders_raw')
                        ->andWhere('o.date_created >= :d')
                        ->setParameter('d', $filter->params[0]);

                    $qb->leftJoin('s', sprintf('(%s)', $subQuery->getSQL()), 'so', 's.email = so.invoice_email')
                        ->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes());

                    $fields[] = 'sum_orders_raw';
                    break;
                case 'sum_orders_price':
                    if (!$this->checkParam($filter->params, 1)) {
                        break;
                    }

                    $subQuery->addSelect('SUM(o.total_price'.$sum.') as total_price')
                        ->andWhere('o.date_created >= :d2');

                    $qb->leftJoin('s', sprintf('(%s)', $subQuery->getSQL()), 'sop', 's.email = sop.invoice_email')
                        ->setParameter('d2', $filter->params[0])
                        ->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes());

                    $fields[] = 'sop.total_price as sum_orders_price_raw';

                    break;
                case 'sum_orders_price_higher':
                    if (!$this->checkParam($filter->params, 2)) {
                        break;
                    }

                    $subQuery->andWhere('o.date_created >= :d3')
                        ->andWhere('o.total_price'.$sum.' >= :s1 ');

                    $qb->leftJoin('s', sprintf('(%s)', $subQuery->getSQL()), 'soph', 's.email = soph.invoice_email')
                        ->setParameter('d3', $filter->params[1])
                        ->setParameter('s1', $filter->params[0])
                        ->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes());

                    $fields[] = 'IF(soph.invoice_email IS NULL,0,1) as sum_orders_price_higher_raw';

                    break;
                case 'sum_orders_price_lower':
                    if (!$this->checkParam($filter->params, 2)) {
                        break;
                    }

                    $subQuery->andWhere('o.date_created >= :d4')
                        ->andWhere('o.total_price'.$sum.' < :s2 ');

                    $qb->leftJoin('s', sprintf('(%s)', $subQuery->getSQL()), 'sopl', 's.email = sopl.invoice_email')
                        ->setParameter('d4', $filter->params[1])
                        ->setParameter('s2', $filter->params[0])
                        ->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes());

                    $fields[] = 'IF(sopl.invoice_email IS NULL,0,1) as sum_orders_price_lower_raw';

                    break;
            }
        }

        $query = $qb->select($fields)
            ->groupBy('s.email')
            ->execute();

        return $query;
    }

    /**
     * @return array
     */
    public function procesData($query, $routeName = 'jsonUsersExport')
    {
        $greeting = new \Greeting();

        $items = [];
        foreach ($query as $i => $row) {
            $row['newsletter_logout_link'] = path('usersDisable', ['email' => $row['email'], 'key' => substr(md5($row['email']), 10, 5)], 0);
            $row['newsletter_login_link'] = path('usersEnable', ['email' => $row['email'], 'key' => substr(md5($row['email']), 10, 5)], 0);

            $row['name'] = $this->finalizeName($greeting->get([$row['name']], ['greeting_name'])['greeting'] ?? '');
            $row['surname'] = $this->finalizeName($greeting->get([$row['surname']], ['greeting_surname'])['greeting'] ?? '');

            $items[] = $this->formatRow($row);
        }

        $actPage = getVal('page', null, 1);
        $nextPage = ($this->noPages > $actPage)
            ? path($routeName, ['page' => $actPage + 1], 0)
            : '';
        $prevPage = ($actPage > 1)
            ? path($routeName, ['page' => $actPage - 1], 0)
            : '';

        $data = [
            'pages_current_page' => (int) getVal('page', null, 1),
            'pages_total_count' => $this->noPages,
            'pages_previous_page_link' => $prevPage,
            'pages_next_page_link' => $nextPage,
            'items' => $items,
        ];

        return $data;
    }

    /**
     * @return array
     */
    public function formatRow($row)
    {
        $data = [];
        foreach ($this->field_names as $key => list($name, $isInt)) {
            if (isset($row[$key])) {
                $value = ($isInt) ? intval($row[$key]) : $row[$key];
                $data[] = [$name, $value];
            }
        }

        return [
            'email' => $row['email'],
            'tags' => $data,
        ];
    }

    private function checkParam($param, $length = false)
    {
        if (!is_array($param)) {
            return false;
        } elseif ($length != false && count($param) != $length || $param == []) {
            return false;
        }

        return true;
    }

    private function finalizeName($name)
    {
        return mb_convert_case($name, MB_CASE_TITLE, 'utf-8');
    }

    public function setFilters($filters)
    {
        $this->filters = $filters;
    }
}
