<?php

namespace KupShop\AdminBundle\Util\Stats;

use highcharts\Highchart;
use highcharts\HighchartJsExpr;
use KupShop\AdminBundle\Util\Filter\OrdersFilterSpecs;
use KupShop\CatalogBundle\Util\ProductsFilterSpecs;
use Query\Filter;
use Query\Operator;
use Query\QueryBuilder;
use Query\QueryBuilderBase;

class StatsDataLoader
{
    use \DatabaseCommunication;
    use StatsDataHelper;

    protected $renderTo;
    /**
     * @var array
     */
    protected $data;
    /**
     * @var bool|\DateTime
     */
    private $from;
    private $to;
    private $intervalType = 'o.date_created';

    private $months = [
        '',
        'Led',
        'Uno',
        'Bře',
        'Dub',
        'Kvě',
        'Čer',
        'Červ',
        'Srp',
        'Zář',
        'Říj',
        'Lis',
        'Pros',
    ];

    public $dates = [];
    public $type_graph = 'sales';
    public $interval = 'month';

    public $tabInstance;
    /**
     * @var OrdersFilterSpecs
     */
    private $ordersFilterSpecs;
    /**
     * @var ProductsFilterSpecs
     */
    private $productsFilterSpecs;

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

    public function getEmptyGraph()
    {
        $chart = new Highchart(Highchart::HIGHCHART, -1);

        $chart->chart = [
            'type' => 'line',
            'marginRight' => 130,
            'marginBottom' => 25,
        ];

        return $chart;
    }

    public function getGraph($json = true)
    {
        $dbcfg = \Settings::getDefault();
        $chart = $this->getEmptyGraph();

        $chart->subtitle = [
            'x' => -20,
        ];

        $chart->xAxis->categories = [];

        $chart->yAxis = [
            'plotLines' => [
                [
                    'value' => 0,
                    'width' => 1,
                    'color' => '#808080',
                ],
            ],
        ];

        $methodGraphName = ucfirst($this->type_graph);
        $fullRange = in_array($methodGraphName, ['Warehouse'], true);
        $dataset = [];

        $this->data = ($this->tabInstance ?? $this)->{"getDB{$methodGraphName}Data"}();
        foreach ($methodGraphName != 'Sales' ? [$this->data] : $this->data as $stat) {
            switch ($this->interval) {
                case 'day':
                    $chart->subtitle->text = translate('intervalDaily', 'stats');
                    list($x, $data) = $this->getDaysData($stat);
                    break;
                case 'month':
                    $chart->subtitle->text = translate('intervalMonthly', 'stats');
                    list($x, $data) = $this->getMonthsData($stat);
                    break;
                case 'year':
                default:
                    $chart->subtitle->text = translate('intervalYearly', 'stats');
                    list($x, $data) = $this->getYearsData($stat);
                    break;
            }

            if ($fullRange) {
                $dataset = $data;
            } else {
                $dataset[] = $data[0];
            }
        }

        $chart->series = $dataset;
        $chart->xAxis->categories = $x;

        // TODO: udělat dynamický
        if ($this->type_graph == 'orders') {
            $chart->yAxis['title']['text'] = translate('amount', 'stats');
            $chart->title['text'] = translate('ordersAmount', 'stats');
            $chart->series[0]['name'] = translate('ordersTotal', 'stats');

            // TODO: Tohle dát taky někam společně, je to stejný, pokud teda nebude předefinováno - viz renderTo
            $chart->tooltip->formatter = new HighchartJsExpr(
                "function() { return '<b>'+ this.series.name +'</b><br/>'+ this.x +': '+ this.y;}");
        } elseif ($this->type_graph == 'sales') {
            $chart->yAxis['title']['text'] = translate('orderValue', 'stats')."({$dbcfg->currency})";
            $chart->title['text'] = translate('revenue', 'stats');
            $chart->series[0]['name'] = translate('revenue', 'stats');
            $chart->series[1]['name'] = translate('revenueNoVat', 'stats');
            if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_BUY)) {
                $chart->series[2]['name'] = translate('priceBuy', 'stats');
                $chart->series[3]['name'] = translate('profit', 'stats');
            }
            $chart->tooltip->formatter = new HighchartJsExpr(
                "function() { return '<b>'+ this.series.name +'</b><br/>'+ this.x +': '+ this.y.toString().replace(/\B(?=(\d{3})+(?!\d))/g, \" \") +' {$dbcfg->currency} ' + ([1, 2, 3].includes(this.series._i) ? '".translate('withoutVat', 'stats')."' : '".translate('withVat', 'stats')."');}");
        } elseif ($this->type_graph == 'product') {
            $chart->yAxis['title']['text'] = translate('pcsSold', 'stats');
            $chart->title['text'] = $this->getProductName();
            $chart->series[0]['name'] = translate('pcsSold', 'stats');

            $chart->tooltip->formatter = new HighchartJsExpr(
                "function() { return '<b>'+ this.series.name +'</b><br/>'+ this.x +': '+ this.y;}");
        } elseif ($this->type_graph == 'warehouse') {
            $chart->yAxis['title']['text'] = translate('score', "stats{$methodGraphName}Tab");
            $chart->title['text'] = $this->getProductName();

            $chart->tooltip->formatter = new HighchartJsExpr(
                "function() { return '<b>'+ this.series.name +'</b><br/>'+ this.x +': '+ this.y;}");
        } elseif ($this->type_graph == 'store') {
            $chart->yAxis['title']['text'] = translate('score', "stats{$methodGraphName}Tab");
            $chart->title['text'] = $this->getProductName();
            $chart->series[0]['name'] = translate('stockStore', 'stats');
            $chart->tooltip->formatter = new HighchartJsExpr(
                "function() { return '<b>'+ this.series.name +'</b><br/>'+ this.x +': '+ this.y;}");
        }

        $chart->series[0]['color'] = '#0d4db6';
        $chart->title['x'] = -20;
        $chart->chart['renderTo'] = $this->renderTo ? $this->renderTo : $this->type_graph;

        $chart->legend = [
            'layout' => 'vertical',
            'align' => 'right',
            'verticalAlign' => 'top',
            'x' => -5,
            'y' => 100,
            'borderWidth' => 0,
        ];

        if ($json) {
            echo json_encode($chart->renderOptions());
            exit;
        } else {
            return $chart;
        }
    }

    public function getProductName()
    {
        $id_product = getVal('id_product');
        $id_variation = getVal('id_variation');

        if (empty($id_product)) {
            return '';
        }
        // /////////////
        /** @var QueryBuilderBase $SQL */
        $SQL = sqlQueryBuilder()->select('p.title as product_name')
            ->from('products', 'p');
        if (!empty($id_variation)) {
            $SQL->addSelect('pv.title as variation_name')
                ->leftJoin('p', 'products_variations', 'pv', 'pv.id = :id_variation AND pv.id_product = :id_product')
                ->setParameter('id_variation', $id_variation)
                ->setParameter('id_product', $id_product);
        }
        $SQL = $SQL->where(Operator::equals(['p.id' => $id_product]))
            ->execute();

        $names = sqlFetchAssoc($SQL);
        if (!empty($names)) {
            $name = $names['product_name'];
            if (!empty($id_variation)) {
                $name .= " ({$names['variation_name']})";
            }

            return $name;
        } else {
            return '';
        }
    }

    public function getDBOrdersShareData($field)
    {
        $SQL = sqlQueryBuilder()
            ->from('orders', 'o')
            ->andWhere($this->addIntervalSpec(true))
            ->orderBy('year', 'ASC');

        $SQL->addSelect($field);

        $filter = getVal('filter', null, []);
        $specs = $this->ordersFilterSpecs->getSpecs($filter);
        if ($specs) {
            $SQL->andWhere($specs);
        }

        $SQL = $SQL->execute();

        return $this->data = $this->formatData($SQL);
    }

    public function getDBOrdersData()
    {
        return $this->getDBOrdersShareData('COUNT(*) as sum');
    }

    public function getDBSalesData()
    {
        $sum = (findModule('currencies')) ? ' * o.currency_rate' : '';

        $data = [
            $this->getDBOrdersShareData('SUM(o.total_price'.$sum.') as sum'),
            $this->getDBOrdersShareData('SUM(o.total_price_without_vat'.$sum.') as sum'),
        ];

        if (findModule(\Modules::PRODUCTS, \Modules::SUB_PRICE_BUY)) {
            $data[] = $this->getDBOrdersShareData('SUM((SELECT SUM(COALESCE(oi.price_buy'.$sum.', pv.price_buy, p.price_buy) * oi.pieces) FROM order_items oi LEFT JOIN products p ON p.id <=> oi.id_product LEFT JOIN products_variations pv ON pv.id <=> oi.id_variation WHERE oi.id_order=o.id)) as sum');
            $data[] = $this->getDBOrdersShareData('(SUM(o.total_price_without_vat'.$sum.') - SUM((SELECT SUM(COALESCE(oi.price_buy'.$sum.', pv.price_buy, p.price_buy) * oi.pieces) FROM order_items oi LEFT JOIN products p ON p.id <=> oi.id_product LEFT JOIN products_variations pv ON pv.id <=> oi.id_variation WHERE oi.id_order=o.id))) as sum');
        }

        return $data;
    }

    public function getProductsSellingSubQuery()
    {
        $subQuery = sqlQueryBuilder()
            ->from('order_items', 'oi')
            ->leftJoin('oi', 'orders', 'o', 'o.id=oi.id_order')
            ->join('oi', 'products', 'p', 'p.id = oi.id_product')
            ->andWhere('o.status_storno = 0')
            ->andWhere($this->addIntervalSpec(true));

        $filter = getVal('filter', null, []);
        $specs = $this->productsFilterSpecs->getSpecs($filter);
        if ($specs) {
            $subQuery->andWhere($specs);
        }

        $specs = $this->ordersFilterSpecs->getSpecs($filter);
        if ($specs) {
            $subQuery->andWhere($specs);
        }

        if (isset($filter['variations'])) {
            $subQuery->andWhere('pv.id = oi.id_variation');
        }

        $subQuery->select('o.date_created as date_created, o.date_handle as date_handle, oi.pieces as pieces');
        $subQuery->resetQueryPart('orderBy');
        $subQuery->groupBy('oi.id');

        return $subQuery;
    }

    public function getDBProductData()
    {
        $subQuery = $this->getProductsSellingSubQuery();

        $SQL = sqlQueryBuilder()
            ->from('('.$subQuery->getSQL().')', 'o')
            ->andWhere($this->addIntervalSpec(true))
            ->addSelect('SUM(o.pieces) as sum')
            ->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes())
            ->orderBy('year', 'ASC');

        $SQL = $SQL->execute();

        return $this->data = $this->formatData($SQL);
    }

    public function getProductsSelling($page = null, $limit = null)
    {
        $subQuery = $this->getProductsSellingSubQuery()->addSelect('p.id, p.title, p.price');
        /** @var QueryBuilderBase $sql_products */
        $sql_products = sqlQueryBuilder()
            ->select('o.id, o.title, SUM(o.pieces) AS sold, o.price')
            ->from('('.$subQuery->getSQL().')', 'o')
            ->addParameters($subQuery->getParameters(), $subQuery->getParameterTypes())
            ->groupBy('o.id')
            ->orderBy('sold', 'DESC');

        if (!is_null($page) && !is_null($limit)) {
            $sql_products->setFirstResult(($page == 0) ? $page : $limit * $page)
                ->setMaxResults($limit);
        }

        $products = [];
        foreach ($sql_products->execute() as $product) {
            $product['variations'] = [];

            $sql_variations = $this->getProductsSellingVariationsQueryBuilder()
                ->andWhere('o.status_storno = 0')
                ->andWhere($this->addIntervalSpec())
                ->andWhere(Operator::equals(['oi.id_product' => $product['id']]))
                ->andWhere($this->getSpecVariations());

            foreach ($sql_variations->execute() as $variation) {
                $product['variations'][] = $variation;
            }

            $products[$product['id']] = $product;
        }

        return $products;
    }

    protected function getSpecVariations()
    {
        $filter = getVal('filter', null, []);

        return function (QueryBuilder $qb) use ($filter) {
            if (isset($filter['variations'])) {
                $variationIds = $filter['variations'];
                $invert = isset($filter['variations_invert']);
                $variationsValuesOperator = $filter['variationsValuesOperator'];
                if (in_array($variationsValuesOperator, ['AND', 'OR'])) {
                    $expression = Filter::variationsFilterUtil($variationIds, null, $variationsValuesOperator == 'AND');

                    return ($invert) ? Operator::not($expression) : $expression;
                }
            }

            return null;
        };
    }

    protected function getProductsSellingVariationsQueryBuilder(): QueryBuilder
    {
        $qb = sqlQueryBuilder()->select('COALESCE(oi.id_variation, "no_variation") id_variation', 'COALESCE(pv.title, "Bez varianty") title', 'SUM(oi.pieces) AS sold', 'pv.price')
            ->from('order_items', 'oi')
            ->leftJoin('oi', 'products_variations', 'pv', 'oi.id_product = pv.id_product AND oi.id_variation = pv.id')
            ->leftJoin('oi', 'orders', 'o', 'o.id=oi.id_order');
        $filter = getVal('filter', null, []);
        unset($filter['orderproducts']);
        $specs = $this->ordersFilterSpecs->getSpecs($filter);
        if ($specs) {
            $qb->andWhere($specs);
        }
        $qb->resetQueryParts(['groupBy', 'orderBy']);
        $qb->groupBy('id_variation')
            ->orderBy('sold', 'DESC');

        return $qb;
    }

    private function addIntervalSpec($addInterval = false)
    {
        return function (QueryBuilder $qb) use ($addInterval) {
            $date = $this->intervalType;

            $qb->setParameter('from', $this->prepareDate($this->from).' 00:00:00')
                ->setParameter('to', $this->prepareDate($this->to).' 23:59:59');

            if ($this->to) {
                $qb->andWhere("{$date} >= :from");
            }

            if ($this->from) {
                $qb->andWhere("{$date} <= :to");
            }

            if ($addInterval) {
                $qb->select("(YEAR({$date})- YEAR(:from)) as year")
                    ->groupBy("YEAR({$date})");

                switch ($this->interval) {
                    case 'month':
                        $qb->addSelect("MONTH({$date}) as month")
                            ->addGroupBy("MONTH({$date})")
                            ->addOrderBy('month');
                        break;

                    case 'year':
                        break;

                    case 'day':
                    default:
                        $qb->addSelect("DAY({$date}) as day, MONTH({$date}) as month")
                                ->addGroupBy("MONTH({$date}), DAY({$date})")
                                ->addOrderBy('month,day');

                        break;
                }
            }
        };
    }

    /**
     * @param bool|\DateTime $from
     *
     * @return StatsDataLoader
     */
    public function setFrom($from)
    {
        if (empty($from)) {
            $from = date(\Settings::getDateFormat(), time() - (365 * 86400));
        }

        $this->from = \DateTime::createFromFormat(\Settings::getDateFormat(), $from);

        return $this;
    }

    /**
     * @return $this
     */
    public function setTo($to)
    {
        if (empty($to)) {
            $to = date(\Settings::getDateFormat(), time());
        }

        $this->to = \DateTime::createFromFormat(\Settings::getDateFormat(), $to);

        return $this;
    }

    public function setTypeInterval($type)
    {
        if ($type) {
            $this->intervalType = $type;
        }

        return $this;
    }

    /**
     * @param string $type_graph
     *
     * @return StatsDataLoader
     */
    public function setTypeGraph($type_graph)
    {
        $this->type_graph = $type_graph;

        return $this;
    }

    /**
     * @return $this
     */
    public function setInterval($interval)
    {
        $this->interval = $interval;

        return $this;
    }

    /**
     * @return StatsDataLoader
     */
    public function setRenderTo($renderTo)
    {
        $this->renderTo = $renderTo;

        return $this;
    }

    /**
     * @return StatsDataLoader
     */
    public function setTabInstance($tabInstance)
    {
        $this->tabInstance = $tabInstance;

        return $this;
    }
}