<?php

namespace KupShop\ContentBundle\Util;

use KupShop\I18nBundle\Translations\ArticlesAuthorsTranslation;
use KupShop\I18nBundle\Translations\ArticlesSectionsTranslation;
use KupShop\I18nBundle\Translations\ArticlesTagsTranslation;
use KupShop\I18nBundle\Translations\ArticlesTranslation;
use KupShop\I18nBundle\Translations\PhotosTranslation;
use KupShop\KupShopBundle\Util\StringUtil;
use Query\Operator;
use Query\QueryBuilder;
use Query\Translation;

class ArticleList
{
    protected $divide;

    protected $total_count;

    private $image;

    protected $tags;

    // returns section by defined spec
    public function getSection($spec = null)
    {
        return $this->getSections($spec)[0] ?? false;
    }

    // return sections by specs
    public function getSections($spec = null)
    {
        $qb = sqlQueryBuilder()->select('ab.*')
                ->from('articles_branches', 'ab')
                ->andWhere($spec)
                ->andWhere(Translation::coalesceTranslatedFields(ArticlesSectionsTranslation::class))
                ->orderBy('ab.position');

        $qb->andWhere(Translation::joinTranslatedFields(ArticlesSectionsTranslation::class,
            function (QueryBuilder $qb, $columnName, $translatedField) {
                $qb->andWhere(Operator::coalesce($translatedField, 'ab.figure').' = "Y" ');

                return false;
            }, ['figure']));

        return $qb->execute()->fetchAll();
    }

    public function getSectionById(int $id): ?array
    {
        if ($section = $this->getSection(Operator::equals(['ab.id' => $id]))) {
            return $this->prepareArticleSection($section);
        }

        return null;
    }

    public function getSectionsTree($topSection = null)
    {
        $tree = [];

        $spec = function (QueryBuilder $qb) use ($topSection) {
            $qb->andWhere(Operator::equalsNullable(['ab.top_branch' => $topSection]));
        };

        foreach ($this->getSections($spec) as $section) {
            $tree[$section['id']] = $this->prepareArticleSection($section);
        }

        return $tree;
    }

    public function getArticlesCount($spec = null)
    {
        $qb = sqlQueryBuilder()->select('COUNT(DISTINCT a.id) as count')
            ->from('articles_relation', 'ar')
            ->leftJoin('ar', 'articles', 'a', 'ar.id_art=a.id')
            ->andWhere('a.date <= NOW()')
            ->andWhere($spec);

        $qb->andWhere(Translation::joinTranslatedFields(ArticlesTranslation::class,
            function (QueryBuilder $qb, $columnName, $translatedField) {
                $qb->andWhere(Operator::coalesce($translatedField, 'a.figure').' = "Y" ');
            }, ['figure']));

        return $qb->execute()->fetch()['count'];
    }

    public function getArticles($spec = null)
    {
        $data = [];
        $dbcfg = \Settings::getDefault();

        $qb = sqlQueryBuilder()->select('SQL_CALC_FOUND_ROWS DISTINCT(a.id) as id, ar.id_branch, a.figure, 
        a.title, a.type, a.link, a.source, DATE_FORMAT(a.date, "'.$dbcfg->date_format.' '.$dbcfg->time_format.'") AS datef,
        a.lead_in, a.date, a.data, a.id_block, a.url, a.seen, GROUP_CONCAT(t.id) AS tags')
                ->from('articles_relation', 'ar')
                ->leftJoin('ar', 'articles', 'a', 'ar.id_art=a.id')
                ->leftJoin('a', 'articles_tags_relation', 'atr', 'a.id = atr.id_article')
                ->leftJoin('atr', 'articles_tags', 't', 'atr.id_tag = t.id')
                ->leftJoin('ar', 'articles_branches', 'ab', 'ab.id = ar.id_branch')
                ->andWhere('a.date <= NOW()')
                ->having('figure = "Y"')
                ->groupBy('a.id')
                ->andWhere($spec)
                ->andWhere(Translation::coalesceTranslatedFields(ArticlesTranslation::class));

        $qb->andWhere(Translation::joinTranslatedFields(ArticlesSectionsTranslation::class,
            function (QueryBuilder $qb, $columnName, $translatedField) {
                $qb->andWhere(Operator::coalesce($translatedField, 'ab.figure').' = "Y" ');

                return false;
            }, ['figure']));

        $qb = $qb->execute();

        $this->total_count = (int) sqlFetchAssoc(sqlQuery('SELECT FOUND_ROWS() as total_count'))['total_count'];

        foreach ($qb as $row) {
            $row['lead'] = $row['lead_in'];
            $row['seo_url'] = !empty($row['url']) ? $row['url'] : StringUtil::slugify($row['title']);

            // if isset section
            if ($id_branch = $row['id_branch']) {
                $spec = function (QueryBuilder $qb) use ($id_branch) {
                    $qb->andWhere(Operator::equals(['ab.id' => $id_branch]));
                };
            } else {
                $spec = null;
            }
            $row['section'] = $this->getSection($spec);

            $row['date'] = new \DateTime($row['date']);

            $row['data'] = json_decode($row['data'], true);

            if (!empty($row['tags'])) {
                $tag_ids = array_flip(explode(',', $row['tags']));
                $row['tags'] = array_intersect_key($this->getTags(), $tag_ids);
            }

            $data[$row['id']] = $row;
        }

        // multi fetches
        $this->fetchArticlesPhotos($data);
        $this->fetchArticlesAuthors($data);

        return $data;
    }

    public function fetchArticlesPhotos(array &$articles): void
    {
        if (!$this->image) {
            return;
        }

        if (empty($articles)) {
            return;
        }

        $qb = sqlQueryBuilder()
            ->select('pa.id_art as id_article, ph.id, ph.descr, ph.source, ph.image_2 AS image_big, ph.date_update')
            ->from('photos_articles_relation', 'pa')
            ->leftJoin('pa', 'photos', 'ph', 'pa.id_photo = ph.id')
            ->andWhere(Translation::coalesceTranslatedFields(PhotosTranslation::class))
            ->andWhere(Operator::inIntArray(array_keys($articles), 'pa.id_art'))
            ->andWhere(Operator::equals(['pa.show_in_lead' => 'Y', 'pa.active' => 'Y']));

        foreach ($qb->execute() as $item) {
            if (empty($articles[$item['id_article']])) {
                continue;
            }

            if ($this->image == 'photo') {
                $articles[$item['id_article']]['photoId'] = $item['id'];
                $articles[$item['id_article']]['photoDescr'] = $item['descr'];
                $articles[$item['id_article']]['photoDateUpdate'] = strtotime($item['date_update']);
            } else {
                $articles[$item['id_article']]['image'] = getImage($item['id'], $item['image_big'], $item['source'], $this->image, $item['descr'], strtotime($item['date_update']));
            }
        }
    }

    public function fetchArticlesAuthors(array &$articles): void
    {
        if (!findModule(\Modules::ARTICLES_AUTHORS)) {
            return;
        }

        if (empty($articles)) {
            return;
        }

        $qb = sqlQueryBuilder()
            ->select('aar.id_art as id_article, au.*')
            ->from('articles_authors', 'au')
            ->join('au', 'articles_authors_relation', 'aar', 'au.id = aar.id_auth')
            ->andWhere(Translation::coalesceTranslatedFields(ArticlesAuthorsTranslation::class))
            ->andWhere(Operator::inIntArray(array_keys($articles), 'aar.id_art'));

        foreach ($qb->execute() as $item) {
            if (empty($articles[$item['id_article']])) {
                continue;
            }

            $articles[$item['id_article']]['authors'][$item['id']] = $item;
        }
    }

    public function setImage($image): self
    {
        $this->image = $image;

        return $this;
    }

    public function createPager(int $pageNumber)
    {
        $pager = new \Pager();
        $pager->setOnPage($this->getDivide());
        $pager->setPageNumber($pageNumber);

        return $pager;
    }

    public function getDivide(): int
    {
        if (!$this->divide) {
            $this->divide = findModule(\Modules::ARTICLES, 'limit') ?: 25;
        }

        return $this->divide;
    }

    public function setDivide(int $divide)
    {
        $this->divide = $divide;
    }

    /**
     * @return int
     */
    public function getTotalCount()
    {
        return $this->total_count;
    }

    public function getTags()
    {
        if (!isset($this->tags)) {
            $this->tags = [];
            $qb = sqlQueryBuilder()
                ->select('at.*')
                ->from('articles_tags', 'at')
                ->andWhere(Translation::coalesceTranslatedFields(ArticlesTagsTranslation::class))
                ->execute();

            $tags = $qb->fetchAll();
            if (!empty($tags)) {
                $this->tags = array_combine(array_column($tags, 'id'), $tags);
            }
        }

        return $this->tags;
    }

    protected function prepareArticleSection(array $section): array
    {
        $section['url'] = path('kupshop_content_articles_articles_2', ['IDb' => $section['id'], 'slug' => StringUtil::slugify($section['name'])]);
        $section['children'] = $this->getSectionsTree($section['id']);
        $section['articles_count'] = $this->getArticlesCount(Operator::equals(['ar.id_branch' => $section['id']]));

        return $section;
    }
}