class OfferWithStatsQueryBuilder
{

    const DATE_FORMAT = 'Y-m-d';

    const COLUMN_OFFER_ID = 0;
    const COLUMN_KEYWORDS = 1;
    const COLUMN_STATS = 2;
    const COLUMN_ADURL_1 = 3;
    const COLUMN_GEO_1 = 4;
    const COLUMN_EPC = 5; // ?
    const COLUMN_ADURL_2 = 6;
    const COLUMN_GEO_2 = 7;

    private $offset;

    private $limit;

    private $keywords;

    private $dateFrom;

    private $dateTo;

    private $geos;
    /**
     * @var QueryBuilder
     */
    private $queryBuilder;

    /**
     * @var bool
     */
    private $isOrderable = false;

    /**
     * @var int @see self::COLUMN_* constants
     */
    private $orderColumn;

    /**
     * @var string 'desc' or 'asc'
     */
    private $orderDirection;

    /**
     * OfferWithStatsQueryBuilder constructor.
     * @param QueryBuilder $queryBuilder
     */
    public function __construct(QueryBuilder $queryBuilder)
    {
        $this->queryBuilder = $queryBuilder;
    }

    /**
     * @param mixed $offset
     */
    public function setOffset($offset)
    {
        $this->offset = $offset;
    }

    /**
     * @param mixed $limit
     */
    public function setLimit($limit)
    {
        $this->limit = $limit;
    }

    /**
     * @param array $keywords
     */
    public function setKeywords($keywords = null)
    {

        $this->keywords = null;

        if (!empty($keywords) && is_array($keywords)) {

            $this->keywords = array_map(function ($id) {
                return (int) $id;
            }, $keywords);

        }

    }

    /**
     * @param mixed $dateFrom
     */
    public function setDateFrom($dateFrom = null)
    {

        if (!empty($dateFrom)) {
            $dateFrom = new \DateTime($dateFrom);
            $dateFrom = $dateFrom->format(self::DATE_FORMAT);
        }

        $this->dateFrom = $dateFrom;
    }

    /**
     * @param mixed $dateTo
     */
    public function setDateTo($dateTo = null)
    {
        if (!empty($dateTo)) {
            $dateTo = new \DateTime($dateTo);
            $dateTo = $dateTo->format(self::DATE_FORMAT);
        }

        $this->dateTo = $dateTo;
    }

    /**
     * @param array $geos
     */
    public function setGeos($geos = null)
    {
        $this->geos = null;

        if (!empty($geos) && is_array($geos)) {
            $this->geos = array_map(function ($geo) {
                return (int) $geo;
            }, $geos);
        }
    }

    /**
     * @param mixed $order
     */
    public function setOrder($order = null)
    {
        $isOrderable = false;

        if (!empty($order) && is_array($order)) {
            $this->orderDirection = $order[0]['dir'] == 'desc' ? 'desc' : 'asc';
            $this->orderColumn = (int) $order[0]['column'];
            $isOrderable = true;
        }

        $this->isOrderable = $isOrderable;
    }

    public function buildQueryBuilder()
    {
        $select = 'lo.id AS offer, la.id AS adurl, la.url as url, ls.stats, lolkr.keywords, lacr.countries';
        $sql = $this->build($select);
        $this->appendSorting($sql);
        return $sql;
    }

    public function buildQueryBuilderForCount()
    {
        $select = 'COUNT(DISTINCT lo.id) as count';
        $notLimitQuery = false;
        $sql = $this->build($select, $notLimitQuery);
        return $sql;
    }

    private function toSubQuerySelect(QueryBuilder $query)
    {
        return '(' . $query->getSQL() . ')';
    }

    private function build($select = '*', $isLimited = true)
    {
        $main = clone $this->queryBuilder;

        $offerSubSelect = $this->getOfferSubQuery($isLimited);
        $statJoinSubSelect = $this->getStatJoinSubQuery();
        $keywordsSubSelect = $this->getKeywordsJoinSubQuery();
        $geoSubSelect = $this->getGeoJoinSubQuery();


        $main
            ->select($select)
            ->from($this->toSubQuerySelect($offerSubSelect), 'lo')
            ->innerJoin('lo', 'leadgen_adurls', 'la', 'lo.id = la.offer_id')
            ->leftJoin('lo', $this->toSubQuerySelect($statJoinSubSelect), 'ls', 'la.id = ls.url_id')
            ->leftJoin('lo', $this->toSubQuerySelect($keywordsSubSelect), 'lolkr', 'lo.id = lolkr.offer_id')
            ->leftJoin('lo', $this->toSubQuerySelect($geoSubSelect), 'lacr', 'la.id = lacr.leadgen_adurl_id');

        $this->appendConditions($main);
//        dd($main->getSQL());
        return $main;

        return '
            SELECT *
            FROM leadgen_offers lo
            LEFT JOIN leadgen_adurls la ON lo.id = la.offer_id
            -- STATS
            INNER JOIN (
                SELECT url_id, SUM(count) as stats
                FROM leadgen_stats
                GROUP BY url_id
            ) as ls ON la.id = ls.url_id
            -- KEYWORDS
            LEFT JOIN (
                SELECT offer_id, GROUP_CONCAT(keyword_id SEPARATOR \', \') as keywords
                FROM leadgen_offers_leadgen_keywords_rel
                GROUP BY offer_id
            ) as lolkr ON lo.id = lolkr.offer_id
            -- GEO
            LEFT JOIN (
                SELECT leadgen_adurl_id, GROUP_CONCAT(leadgen_country_id SEPARATOR \', \') as countries
                FROM leadgen_adurls_countries_rel
                GROUP BY leadgen_adurl_id
            ) as lacr ON la.id = lacr.leadgen_adurl_id

            GROUP BY lo.id, la.id
        ';
    }

    /**
     * @return QueryBuilder
     */
    private function getStatJoinSubQuery()
    {
        $statSubQuery = clone $this->queryBuilder;

        $statSubQuery
            ->select('url_id, SUM(count) AS stats')
            ->from('leadgen_stats')
            ->groupBy('url_id');

        if ($this->dateFrom) {
            // primary bind in STRING, when ::getSQL() doesNOT bind through ::setParameter()
            $statSubQuery->andWhere('date >= DATE(\'' . $this->dateFrom . '\')');
        }

        if ($this->dateTo) {
            $statSubQuery->andWhere('date <= DATE( \'' . $this->dateTo . '\')');
        }

        return $statSubQuery;
    }

    private function getKeywordsJoinSubQuery()
    {
        $keywordsSubQuery = clone $this->queryBuilder;

        $keywordsSubQuery
            ->select('offer_id, GROUP_CONCAT(keyword_id SEPARATOR \', \') as keywords')
            ->from('leadgen_offers_leadgen_keywords_rel')
            ->groupBy('offer_id');

        return $keywordsSubQuery;
    }

    private function getGeoJoinSubQuery()
    {
        $geoSubQuery = clone $this->queryBuilder;

        $geoSubQuery
            ->select('leadgen_adurl_id, GROUP_CONCAT(leadgen_country_id SEPARATOR \', \') as countries')
            ->from('leadgen_adurls_countries_rel')
            ->groupBy('leadgen_adurl_id');

        return $geoSubQuery;
    }

    /**
     * @param QueryBuilder $query
     * @return QueryBuilder
     */
    private function appendConditions(QueryBuilder $query)
    {
        if (!empty($this->geos)) {
            $joinTableAlias = 'geo_table_join';
            $query
                ->innerJoin('lo', 'leadgen_adurls_countries_rel', $joinTableAlias, 'la.id = ' . $joinTableAlias . '.leadgen_adurl_id');

            $query
                ->andWhere($joinTableAlias . '.leadgen_country_id IN (' . implode(', ', $this->geos) . ')');
        }

        if (!empty($this->keywords)) {
            $joinTableAlias = 'keywords_table_join';

            $query
                ->innerJoin('lo', 'leadgen_offers_leadgen_keywords_rel', $joinTableAlias, 'lo.id = ' . $joinTableAlias . '.lolkr.offer_id');

            $query
                ->andWhere(
                    $joinTableAlias . '.keyword_id IN (' . implode(', ', $this->keywords) . ')'
                );
        }

        return $query;
    }

    /**
     * @param QueryBuilder $queryBuilder
     * @return QueryBuilder
     */
    private function appendSorting(QueryBuilder $queryBuilder)
    {

        if ($this->isOrderable) {
            switch ($this->orderColumn) {
                case self::COLUMN_KEYWORDS:
                    $column = 'keywords';
                    break;

                case self::COLUMN_ADURL_1:
                case self::COLUMN_ADURL_2:
                    $column = 'url';
                    break;

                case self::COLUMN_STATS:
                    $column = 'stats';
                    break;

                case self::COLUMN_GEO_1:
                case self::COLUMN_GEO_2:
                    $column = 'stats';
                    break;

                default: // self::COLUMN_OFFER_ID:
                    $column = 'offer';
                    break;
            }

            $queryBuilder->orderBy($column, $this->orderDirection);
        }

        return $queryBuilder;
    }

    /**
     * @param bool $isLimited
     * @return QueryBuilder
     */
    private function getOfferSubQuery(bool  $isLimited)
    {
        $offerSubQuery = clone $this->queryBuilder;

        $offerSubQuery
            ->select('*')
            ->from('leadgen_offers');

        if ($isLimited) {
            if ($this->limit) {
                $offerSubQuery->setMaxResults((int) $this->limit);
            }

            if ($this->offset) {
                $offerSubQuery->setFirstResult((int) $this->offset);
            }
        }

        return $offerSubQuery;
    }
}