<?php

namespace plugins\help;

use HTML;
use User;
use Help;
use Model;
use Request;
use bff\db\NestedSetsTree;

/**
 * @property HelpBase $controller
 */
class HelpModel_ extends Model implements Consts
{
    /** @var array */
    public $langQuestions = [
        'title'          => TYPE_STR, # Заголовок
        'textshort'      => TYPE_STR, # Краткое описание
        'mtitle'         => TYPE_NOTAGS, # Meta Title
        'mkeywords'      => TYPE_NOTAGS, # Meta Keywords
        'mdescription'   => TYPE_NOTAGS, # Meta Description
        'content_search' => TYPE_STR, # Publicator-поиск
    ];

    /** @var NestedSetsTree для категорий */
    protected $treeCategories;
    /** @var array */
    public $langCategories = [
        'title'        => TYPE_STR, # Название
        'mtitle'       => TYPE_NOTAGS, # Meta Title
        'mkeywords'    => TYPE_NOTAGS, # Meta Keywords
        'mdescription' => TYPE_NOTAGS, # Meta Description
    ];

    /**
     * Компонент для работы с деревом категорий
     * @return NestedSetsTree
     */
    public function treeCategories()
    {
        if ($this->treeCategories === null) {
            $this->treeCategories = new NestedSetsTree(static::TABLE_CATEGORIES);
        }

        return $this->treeCategories;
    }

    /**
     * ID "Корневой категории" (изменять не рекомендуется)
     * @return int
     */
    public function categoryRootID()
    {
        return 1;
    }

    # --------------------------------------------------------------------
    # Вопросы

    /**
     * Список вопросов (admin)
     * @param array $filter фильтр списка вопросов
     * @param bool $countOnly только подсчет кол-ва вопросов
     * @param string $limit
     * @param string $orderBy
     * @param string|null $lang
     * @return mixed
     */
    public function questionsListing(array $filter, bool $countOnly = false, string $limit = '', string $orderBy = '', ?string $lang = null)
    {
        $lang = $lang ?? $this->locale->current();

        if ($countOnly) {
            $filter = $this->prepareFilter($filter, 'Q');
            $this->db->tag('help-questions-listing-count', ['filter' => &$filter]);
            return $this->db->one_data(
                'SELECT COUNT(Q.id)
                FROM ' . static::TABLE_QUESTIONS . ' Q
                     ' . $filter['where'],
                $filter['bind']
            );
        }

        $join = ' LEFT JOIN ' . static::TABLE_CATEGORIES_LANG . ' C ON C.id = Q.cat_id1 AND C.lang = :lang';

        $filter[':lang'] = $this->db->langAnd(false, 'Q', 'QL', $lang);
        $filter = $this->prepareFilter($filter, 'Q', [':lang' => $this->locale->getCurrentLanguage()]);

        $this->db->tag('help-questions-listing-data', ['filter' => &$filter, 'join' => &$join]);
        return $this->db->select(
            'SELECT Q.id, Q.link, Q.created, QL.title, Q.enabled, Q.content_no, Q.fav,
                    Q.cat_id, C.title as cat_title
            FROM ' . static::TABLE_QUESTIONS . ' Q ' . $join . ',
                 ' . static::TABLE_QUESTIONS_LANG . ' QL
            ' . $filter['where'] .
            ' GROUP BY Q.id ' .
            (!empty($orderBy) ? ' ORDER BY ' . $orderBy : '') .
            $limit,
            $filter['bind']
        );
    }

    /**
     * Поиск вопросов (frontend)
     * @param string $query фильтр списка вопросов
     * @param bool $countOnly только подсчет кол-ва вопросов
     * @param string $limit
     * @param string|null $lang
     * @return array|int
     */
    public function questionsSearch(string $query, bool $countOnly = false, string $limit = '', ?string $lang = null)
    {
        $lang = $lang ?? $this->locale->current();

        $filter = ['enabled' => 1];
        $filter[':query'] = $this->db->prepareFulltextQuery(
            $query,
            'QL.title, QL.textshort, QL.content_search'
        );
        $filter[':lang'] = $this->db->langAnd(false, 'Q', 'QL', $lang);

        $filter = $this->prepareFilter($filter, 'Q');

        if ($countOnly) {
            $this->db->tag('help-questions-search-count', ['filter' => &$filter]);
            return $this->db->one_data(
                'SELECT COUNT(Q.id)
                FROM ' . static::TABLE_QUESTIONS . ' Q,
                     ' . static::TABLE_QUESTIONS_LANG . ' QL
                ' . $filter['where'],
                $filter['bind']
            );
        }

        $this->db->tag('help-questions-search-data', ['filter' => &$filter]);
        $data = $this->db->select(
            'SELECT QL.title, Q.link, QL.textshort, Q.content_no
            FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL
            ' . $filter['where'] . '
            ORDER BY QL.title ' . $limit,
            $filter['bind']
        );
        if (empty($data)) {
            $data = [];
        }

        return $data;
    }

    /**
     * Список частых вопросов (frontend)
     * @param array $opts
     * @return mixed
     */
    public function questionsFav(array $opts = [])
    {
        $opts = $this->defaults($opts, [
            'limit' => 0,
            'lang' => $this->locale->current(),
        ]);
        $filter = ['enabled' => 1, 'fav>0'];
        $filter[':lang'] = $this->db->langAnd(false, 'Q', 'QL', $opts['lang']);
        $filter = $this->prepareFilter($filter, 'Q');

        $limit = ($opts['limit'] > 0 ? $this->db->prepareLimit(0, $opts['limit']) : ' ');

        $this->db->tag('help-questions-fav-data', ['filter' => &$filter]);
        return $this->db->select(
            'SELECT Q.link, QL.title, Q.content_no
            FROM ' . static::TABLE_QUESTIONS . ' Q, ' . static::TABLE_QUESTIONS_LANG . ' QL
            ' . $filter['where'] . ' ORDER BY fav' . $limit,
            $filter['bind']
        );
    }

    /**
     * Получение данных вопроса
     * @param int $questionID ID вопроса
     * @return array
     */
    public function questionData(int $questionID)
    {
        $data = $this->db->select_row(static::TABLE_QUESTIONS, ['*'], ['id' => $questionID]);
        if (!empty($data)) {
            $this->db->langSelect($questionID, $data, $this->langQuestions, static::TABLE_QUESTIONS_LANG);
        } else {
            $data = [];
        }

        return $data;
    }

    /**
     * Просмотр вопроса (frontend)
     * @param int $questionID ID вопроса
     * @param string|null $lang
     * @return array
     */
    public function questionView(int $questionID, ?string $lang = null)
    {
        $lang = $lang ?? $this->locale->current();

        $this->db->tag('help-question-view-data');
        $data = $this->db->one_array(
            'SELECT Q.id, Q.cat_id, Q.link, QL.title, Q.content, QL.textshort,
                QL.mtitle, QL.mkeywords, QL.mdescription, Q.mtemplate, Q.content_no
            FROM ' . static::TABLE_QUESTIONS . ' Q,
                 ' . static::TABLE_QUESTIONS_LANG . ' QL
            WHERE Q.id = :id AND Q.enabled = 1
                ' . $this->db->langAnd(true, 'Q', 'QL', $lang),
            [':id' => $questionID]
        );
        if (empty($data)) {
            return [];
        }

        # другие вопросы
        $this->db->tag('help-question-view-other');
        $data['questions_other'] = $this->db->select(
            'SELECT Q.link, QL.title, Q.content_no
            FROM ' . static::TABLE_QUESTIONS . ' Q,
                 ' . static::TABLE_QUESTIONS_LANG . ' QL
            WHERE Q.cat_id = :cat AND Q.id != :id AND Q.enabled = 1
                ' . $this->db->langAnd(true, 'Q', 'QL', $lang),
            [':cat' => $data['cat_id'], ':id' => $questionID]
        );

        # хлебные крошки
        $this->db->tag('help-question-view-crumbs');
        $data['crumbs'] = $this->db->select(
            'SELECT C.keyword, CL.title
            FROM ' . static::TABLE_CATEGORIES . ' C,
                 ' . static::TABLE_CATEGORIES_LANG . ' CL
            WHERE ' . $this->db->prepareIN('C.id', $this->categoryParentsID($data['cat_id'])) .
            $this->db->langAnd(true, 'C', 'CL', $lang)
        );

        return $data;
    }

    /**
     * Сохранение вопроса
     * @param int $questionID ID вопроса
     * @param array $data данные вопроса
     * @return bool|int
     */
    public function questionSave(int $questionID, array $data)
    {
        if (empty($data)) {
            return false;
        }

        $data['modified'] = $this->db->now();
        if ($questionID > 0) {
            $res = $this->db->update(static::TABLE_QUESTIONS, array_diff_key($data, $this->langQuestions), ['id' => $questionID]);
            $this->db->langUpdate($questionID, $data, $this->langQuestions, static::TABLE_QUESTIONS_LANG);

            return !empty($res);
        } else {
            $data['created'] = $data['modified'];
            $data['user_id'] = $data['user_id'] ?? User::id();
            $data['num'] = (int)$this->db->one_data(
                'SELECT MAX(num)
                FROM ' . static::TABLE_QUESTIONS . ' WHERE cat_id = :cat',
                [':cat' => $data['cat_id']]
            );
            $data['num'] += 1;

            $questionID = $this->db->insert(static::TABLE_QUESTIONS, array_diff_key($data, $this->langQuestions));
            if ($questionID > 0) {
                $this->db->langInsert($questionID, $data, $this->langQuestions, static::TABLE_QUESTIONS_LANG);
            }

            return $questionID;
        }
    }

    /**
     * Переключатели параметров вопроса
     * @param int $questionID ID вопроса
     * @param string $field переключаемое поле
     * @return mixed|void
     */
    public function questionToggle(int $questionID, string $field)
    {
        switch ($field) {
            case 'enabled':
                return $this->toggleInt(static::TABLE_QUESTIONS, $questionID, $field);
            case 'fav':
                return $this->toggleInt(static::TABLE_QUESTIONS, $questionID, $field, 'id', true);
        }
    }

    /**
     * Перемещение вопроса
     * @param string $orderField поле, по которому производится сортировка
     * @param string $where дополнительные условия
     * @return bool
     */
    public function questionsRotate(string $orderField, string $where = '')
    {
        if (! empty($where)) {
            $where = ' AND ' . (is_array($where) ? join(' AND ', $where) : $where);
        }

        return $this->db->rotateTablednd(static::TABLE_QUESTIONS, $where, 'id', $orderField);
    }

    /**
     * Перемещение вопросов из одной категории ($catOldID) в другую ($catNewID)
     * @param int $catNewID ID категории, в которую перемещаем вопросы
     * @param int $catOldID ID категории, из которой перемещаем вопросы
     * @return bool
     */
    public function questionsMoveToCategory(int $catNewID, int $catOldID)
    {
        if (empty($catNewID) || empty($catOldID)) {
            return false;
        }
        $newData = $this->categoryData($catNewID);
        if (empty($newData)) {
            return false;
        }
        $update = ['cat_id' => $catNewID];
        $parentsID = $this->categoryParentsID($catNewID);
        if (! empty($parentsID)) {
            foreach ($parentsID as $lvl => $id) {
                $update['cat_id' . $lvl] = $id;
            }
        }

        return $this->db->update(static::TABLE_QUESTIONS, $update, ['cat_id' => $catOldID]);
    }

    /**
     * Перестраиваем URL на страницы всех вопросов
     * @param string $lang ключ языка
     * @return void
     */
    public function questionsUrlRefresh(string $lang)
    {
        $this->db->select_iterator(
            'SELECT Q.id, QL.title
            FROM ' . static::TABLE_QUESTIONS . ' Q,
                 ' . static::TABLE_QUESTIONS_LANG . ' QL
            WHERE Q.id = QL.id AND QL.lang = :lang
            ORDER BY Q.id',
            [':lang' => $lang],
            function ($question) {
                $url = $this->controller->url('view', $question);
                $this->db->update(static::TABLE_QUESTIONS, ['link' => $url], ['id' => $question['id']]);
            }
        );
    }

    /**
     * Удаление вопроса
     * @param int $questionID ID вопроса
     * @return bool
     */
    public function questionDelete(int $questionID)
    {
        if (empty($questionID)) {
            return false;
        }
        $res = $this->db->delete(static::TABLE_QUESTIONS, ['id' => $questionID]);
        if (! empty($res)) {
            $this->db->delete(static::TABLE_QUESTIONS_LANG, ['id' => $questionID]);
            return true;
        }
        return false;
    }

    # --------------------------------------------------------------------
    # Категории

    /**
     * Список категорий (admin)
     * @param array $filter фильтр списка категорий
     * @param bool $countOnly только подсчет кол-ва категорий
     * @param string $limit
     * @param string $orderBy
     * @param string|null $lang
     * @return mixed
     */
    public function categoriesListing(array $filter, bool $countOnly = false, string $limit = '', string $orderBy = '', ?string $lang = null)
    {
        $lang = $lang ?? $this->locale->current();

        $filter[':lang'] = $this->db->langAnd(false, 'C', 'CL', $lang);
        $filter[] = 'pid != 0';
        $filter = $this->prepareFilter($filter, 'C');

        if ($countOnly) {
            $this->db->tag('help-categories-listing-count', ['filter' => &$filter]);
            return $this->db->one_data(
                'SELECT COUNT(C.id)
                FROM ' . static::TABLE_CATEGORIES . ' C,
                     ' . static::TABLE_CATEGORIES_LANG . ' CL ' .
                $filter['where'],
                $filter['bind']
            );
        }

        $this->db->tag('help-categories-listing-data', ['filter' => &$filter]);
        return $this->db->select(
            'SELECT C.id, C.created, CL.title, C.enabled, C.pid,
                    C.numlevel, ((C.numright-C.numleft)-1) as subs
            FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL
            ' . $filter['where'] .
            (!empty($orderBy) ? ' ORDER BY ' . $orderBy : '') .
            $limit,
            $filter['bind']
        );
    }

    /**
     * Список категорий (frontend)
     * @param array $opts
     * @return mixed
     */
    public function categoriesListIndex(array $opts = [])
    {
        $opts = $this->defaults($opts, [
            'lang' => $this->locale->current(),
        ]);

        $filter = [
            'pid != 0',
            'enabled' => 1,
            ':lang'   => $this->db->langAnd(false, 'C', 'CL', $opts['lang']),
        ];
        $filter = $this->prepareFilter($filter, 'C');

        $this->db->tag('help-categories-list-index-data', ['filter' => &$filter]);
        $data = $this->db->select_key(
            'SELECT C.id, CL.title, C.keyword, C.pid, COUNT(Q.id) as questions
            FROM ' . static::TABLE_CATEGORIES . ' C
                  LEFT JOIN ' . static::TABLE_QUESTIONS . ' Q ON Q.cat_id = C.id AND Q.enabled = 1
               , ' . static::TABLE_CATEGORIES_LANG . ' CL
            ' . $filter['where'] . '
            GROUP BY C.id
            ORDER BY C.numleft',
            'id',
            $filter['bind']
        );

        if (! empty($data)) {
            $categoryID = [];
            foreach ($data as $k => &$v) {
                if ($v['questions'] > 0) {
                    $v['questions_list'] = [];
                    $categoryID[] = $k;
                }
            } unset($v);

            $this->db->tag('help-categories-list-index-questions');
            $questionsList = $this->db->select(
                'SELECT Q.cat_id, Q.link, QL.title, Q.content_no
                FROM ' . static::TABLE_QUESTIONS . ' Q,
                     ' . static::TABLE_QUESTIONS_LANG . ' QL
                WHERE Q.enabled = 1 AND ' . $this->db->prepareIN('Q.cat_id', $categoryID) . '
                    AND ' . $this->db->langAnd(false, 'Q', 'QL', $opts['lang']) . '
                ORDER BY Q.num'
            );
            if (! empty($questionsList)) {
                foreach ($questionsList as $v) {
                    $data[$v['cat_id']]['questions_list'][] = $v;
                }
            }
            unset($questionsList);

            $data = $this->db->transformRowsToTree($data, 'id', 'pid', 'subcats');
        }

        return $data;
    }

    /**
     * Получение данных категории
     * @param int $categoryID ID категории
     * @param bool $edit при редактировании
     * @return array
     */
    public function categoryData(int $categoryID, bool $edit = false)
    {
        if ($edit) {
            $data = $this->db->one_array(
                'SELECT C.*, ((C.numright-C.numleft)-1) as subs, COUNT(Q.id) as questions
                FROM ' . static::TABLE_CATEGORIES . ' C
                    LEFT JOIN ' . static::TABLE_QUESTIONS . ' Q ON Q.cat_id = C.id
                WHERE C.id = :id
                GROUP BY C.id',
                [':id' => $categoryID]
            );
            if (! empty($data)) {
                $this->db->langSelect($categoryID, $data, $this->langCategories, static::TABLE_CATEGORIES_LANG);
            }
        } else {
            $data = $this->db->one_array(
                'SELECT C.*, ((C.numright-C.numleft)-1) as subs
                FROM ' . static::TABLE_CATEGORIES . ' C
                WHERE C.id = :id',
                [':id' => $categoryID]
            );
        }

        if (empty($data)) {
            $data = [];
        }
        return $data;
    }

    /**
     * Просмотр категории + вопросов (или подкатегорий)
     * @param string $keyword url keyword категории
     * @param array $opts
     * @return array|bool
     */
    public function categoryView(string $keyword, array $opts = [])
    {
        $opts = $this->defaults($opts, [
            'lang' => $this->locale->current(),
        ]);

        $this->db->tag('help-category-view-data');
        $data = $this->db->one_array(
            'SELECT C.id, C.pid, C.keyword, CL.title,
                CL.mtitle, CL.mkeywords, CL.mdescription, C.mtemplate,
                ((C.numright-C.numleft)-1) as subcats
            FROM ' . static::TABLE_CATEGORIES . ' C,
                 ' . static::TABLE_CATEGORIES_LANG . ' CL
            WHERE C.keyword = :key AND C.enabled = 1
                AND ' . $this->db->langAnd(false, 'C', 'CL', $opts['lang']) . '
            ',
            [':key' => $keyword]
        );
        if (! empty($data)) {
            $categoryID = $data['id'];

            # хлебные крошки
            $this->db->tag('help-category-view-crumbs');
            $data['crumbs'] = $this->db->select(
                'SELECT C.keyword, CL.title
                FROM ' . static::TABLE_CATEGORIES . ' C,
                     ' . static::TABLE_CATEGORIES_LANG . ' CL
                WHERE ' . $this->db->prepareIN('C.id', $this->categoryParentsID($categoryID)) .
                $this->db->langAnd(true, 'C', 'CL', $opts['lang'])
            );

            if (!$data['subcats']) {
                # вопросы
                $this->db->tag('help-category-view-questions');
                $data['questions_list'] = $this->db->select(
                    'SELECT Q.link, QL.title, QL.textshort, Q.content_no
                    FROM ' . static::TABLE_QUESTIONS . ' Q,
                         ' . static::TABLE_QUESTIONS_LANG . ' QL
                    WHERE Q.enabled = 1 AND Q.cat_id = :cat
                        AND ' . $this->db->langAnd(false, 'Q', 'QL', $opts['lang']) . '
                    ORDER BY Q.num',
                    [':cat' => $categoryID]
                );
            } else {
                # подкатегории
                $this->db->tag('help-category-view-subcats');
                $data['subcats_list'] = $this->db->select(
                    'SELECT C.keyword, CL.title
                    FROM ' . static::TABLE_CATEGORIES . ' C,
                         ' . static::TABLE_CATEGORIES_LANG . ' CL
                    WHERE C.enabled = 1 AND C.pid = :cat
                        AND ' . $this->db->langAnd(false, 'C', 'CL', $opts['lang']) . '
                    ORDER BY C.numleft',
                    [':cat' => $categoryID]
                );
            }
        }

        return $data;
    }

    /**
     * Получение данных категории
     * @param string $keyword URL keyword
     * @param int $categoryID ID категории
     * @param int $categoryParentID ID parent-категории
     * @return bool
     */
    public function categoryKeywordExists(string $keyword, int $categoryID, int $categoryParentID)
    {
        $filter = $this->prepareFilter([
            'pid'          => $categoryParentID,
            'keyword_edit' => $keyword,
            ['id != :id', ':id' => $categoryID]
        ]);
        $data = $this->db->one_array(
            'SELECT id FROM ' . static::TABLE_CATEGORIES . ' ' . $filter['where'] . ' LIMIT 1',
            $filter['bind']
        );

        return !empty($data);
    }

    /**
     * Сохранение категории
     * @param int $categoryID ID категории
     * @param array $data данные категории
     * @return bool|int
     */
    public function categorySave(int $categoryID, array $data)
    {
        if (empty($data)) {
            return false;
        }

        $data['modified'] = $this->db->now();
        if ($categoryID > 0) {
            if (isset($data['pid'])) {
                unset($data['pid']); # запрет изменения pid
            }
            $res = $this->db->update(static::TABLE_CATEGORIES, array_diff_key($data, $this->langCategories), [
                'id' => $categoryID,
            ]);

            $this->db->langUpdate($categoryID, $data, $this->langCategories, static::TABLE_CATEGORIES_LANG);

            return !empty($res);
        } else {
            $data['created'] = $data['modified'];

            $categoryID = $this->treeCategories()->insertNode($data['pid']);
            if ($categoryID > 0) {
                unset($data['pid']);
                $this->db->update(static::TABLE_CATEGORIES, array_diff_key($data, $this->langCategories), [
                    'id' => $categoryID,
                ]);
                $this->db->langInsert($categoryID, $data, $this->langCategories, static::TABLE_CATEGORIES_LANG);
            }

            return $categoryID;
        }
    }

    /**
     * Переключатели параметров категории
     * @param int $categoryID ID категории
     * @param string $field переключаемое поле
     * @return mixed|void
     */
    public function categoryToggle(int $categoryID, string $field)
    {
        switch ($field) {
            case 'enabled':
                return $this->toggleInt(static::TABLE_CATEGORIES, $categoryID, $field);
        }
    }

    /**
     * Перемещение категории
     * @return array|bool
     */
    public function categoriesRotate()
    {
        return $this->treeCategories()->rotateTablednd();
    }

    /**
     * Удаление категории
     * @param int $categoryID ID категории
     * @return bool
     */
    public function categoryDelete(int $categoryID): bool
    {
        if (empty($categoryID)) {
            return false;
        }
        $subCnt = $this->categorySubCount($categoryID);
        if (! empty($subCnt)) {
            $this->errors->set(Help::langAdmin('Unable to delete category if there are subcategories'));
            return false;
        }

        $itemsCnt = $this->db->one_data('SELECT COUNT(I.id) FROM ' . static::TABLE_QUESTIONS . ' I WHERE I.cat_id = :id', [
            ':id' => $categoryID,
        ]);
        if (! empty($itemsCnt)) {
            $this->errors->set(Help::langAdmin('Unable to delete category when there are nested items'));

            return false;
        }

        $deletedID = $this->treeCategories()->deleteNode($categoryID);
        if (! empty($deletedID)) {
            $this->db->delete(static::TABLE_CATEGORIES_LANG, ['id' => $categoryID]);

            return true;
        }

        return false;
    }

    /**
     * Удаление всех категорий
     * @return bool
     */
    public function categoriesDeleteAll(): bool
    {
        # чистим таблицу категорий (+ зависимости по внешним ключам)
        $this->db->exec('DELETE FROM ' . static::TABLE_CATEGORIES . ' WHERE id > 0');
        $this->db->exec('TRUNCATE TABLE ' . static::TABLE_CATEGORIES_LANG);
        $this->db->exec('ALTER TABLE ' . static::TABLE_CATEGORIES . ' AUTO_INCREMENT = 2');
        $this->db->update(static::TABLE_QUESTIONS, ['cat_id' => 0, 'cat_id1' => 0, 'cat_id2' => 0]);

        # создаем корневую директорию
        $rootID = $this->categoryRootID();
        $rootTitle = Help::langAdmin('Root Section');
        $data = [
            'id'       => $rootID,
            'pid'      => 0,
            'numleft'  => 1,
            'numright' => 2,
            'numlevel' => 0,
            'keyword'  => 'root',
            'enabled'  => 1,
            'created'  => $this->db->now(),
            'modified' => $this->db->now(),
        ];
        $res = $this->db->insert(static::TABLE_CATEGORIES, $data);
        if (! empty($res)) {
            $dataLang = ['title' => []];
            foreach ($this->locale->getLanguages() as $lng) {
                $dataLang['title'][$lng] = $rootTitle;
            }
            $this->db->langInsert($rootID, $dataLang, $this->langCategories, static::TABLE_CATEGORIES_LANG);
        }

        return !empty($res);
    }

    /**
     * Получаем кол-во вложенных категорий
     * @param int $categoryID
     * @return int
     */
    public function categorySubCount(int $categoryID): int
    {
        return $this->treeCategories()->getChildrenCount($categoryID);
    }

    /**
     * Формирование списка подкатегорий
     * @param int $categoryID ID категории
     * @param mixed $options формировать select-options или FALSE
     * @param string|null $lang
     * @return array|string
     */
    public function categorySubOptions(int $categoryID, $options = false, ?string $lang = null)
    {
        $lang = $lang ?? $this->locale->current();
        $data = $this->db->select(
            'SELECT C.id, CL.title
            FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL
            WHERE C.pid = :pid
              ' . $this->db->langAnd(true, 'C', 'CL', $lang) . '
            ORDER BY C.numleft',
            [':pid' => $categoryID]
        );

        if (empty($options)) {
            return $data;
        }

        return HTML::selectOptions($data, $options['sel'], $options['empty'], 'id', 'title');
    }

    /**
     * Обработка редактирования keyword'a в категории с подменой его в путях подкатегорий
     * @param int $categoryID ID категории
     * @param string $keywordBefore предыдущий keyword
     * @return bool
     */
    public function categoryRebuildSubsKeyword(int $categoryID, string $keywordBefore)
    {
        $catData = $this->categoryData($categoryID);
        if (empty($catData)) {
            return false;
        }
        if ($catData['pid'] == $this->categoryRootID()) {
            $from = $keywordBefore . '/';
        } else {
            $parentCatData = $this->categoryData($catData['pid']);
            if (empty($parentCatData)) {
                return false;
            }
            $from = $parentCatData['keyword'] . '/' . $keywordBefore . '/';
        }

        # перестраиваем полный путь подкатегорий
        $res = $this->db->update(
            static::TABLE_CATEGORIES,
            ['keyword = REPLACE(keyword, :from, :to)'],
            'numleft > :left AND numright < :right',
            [
                ':from'  => $from,
                ':to'    => $catData['keyword'] . '/',
                ':left'  => $catData['numleft'],
                ':right' => $catData['numright'],
            ]
        );

        return !empty($res);
    }

    /**
     * Формирование списка основных категорий
     * @param int $selectedID ID выбранной категории
     * @param mixed $emptyOption невыбранное значение
     * @param int $type тип списка: 0 - все(кроме корневого), 1 - список при добавлении категории, 2 - список при добавлении записи
     * @param array $opts доп. параметры
     * @return string <option></option>...
     */
    public function categoriesOptions(int $selectedID = 0, $emptyOption = false, int $type = 0, array $opts = [])
    {
        $opts = $this->defaults($opts, [
            'onlyID' => [], # только список определенных категорий
            'lang' => $this->locale->current(),
        ]);

        $filter = [];
        if ($type == 1) {
            $filter[] = 'numlevel < 2';
        } else {
            $filter[] = 'numlevel > 0';
        }
        if (!empty($opts['onlyID'])) {
            $filter[':only'] = '(C.id IN (' . join(',', $opts['onlyID']) . ') OR C.pid IN(' . join(',', $opts['onlyID']) . '))';
        }

        # Chrome не понимает style="padding" в option
        $usePadding = (mb_stripos(Request::userAgent(), 'chrome') === false);
        $joinItems = ($type > 0);
        $filter[':lang'] = $this->db->langAnd(false, 'C', 'CL', $opts['lang']);
        $filter = $this->prepareFilter($filter, 'C');

        $catsList = $this->db->select(
            'SELECT C.id, CL.title, C.numlevel, ((C.numright-C.numleft)-1) as subs
                ' . ($joinItems ? ', COUNT(I.id) as items ' : '') . '
            FROM ' . static::TABLE_CATEGORIES . ' C
                ' . ($joinItems ? ' LEFT JOIN ' . static::TABLE_QUESTIONS . ' I ON C.id = I.cat_id ' : '') . '
                , ' . static::TABLE_CATEGORIES_LANG . ' CL
            ' . $filter['where'] . '
            GROUP BY C.id
            ORDER BY C.numleft',
            $filter['bind']
        );

        $options = '';
        foreach ($catsList as &$v) {
            if ($v['id'] == $this->categoryRootID()) {
                $v['title'] = Help::langAdmin('Root Section');
            }
            $numLevel = & $v['numlevel'];
            $disable = ($type > 0 && ($type == 2 ? $v['subs'] > 0 : ($numLevel > 0 && $v['items'] > 0)));
            $options .= '<option value="' . $v['id'] . '" ' .
                ($usePadding && $numLevel > 1 ? 'style="padding-left:' . ($numLevel * 10) . 'px;" ' : '') .
                ($v['id'] == $selectedID ? ' selected' : '') .
                ($disable ? ' disabled' : '') .
                '>' . (!$usePadding && $numLevel > 1 ? str_repeat('  ', $numLevel) : '') . $v['title'] . '</option>';
        } unset($v);

        if ($emptyOption !== false) {
            $value = 0;
            if (is_array($emptyOption)) {
                $value = key($emptyOption);
                $emptyOption = current($emptyOption);
            }
            $emptyOption = '<option value="' . $value . '" class="bold">' . $emptyOption . '</option>';
            $options = $emptyOption . $options;
        }

        return $options;
    }

    /**
     * Формирование списков категорий (при добавлении/редактировании записи)
     * @param array $categoriesID ID категорий [lvl=>selectedID, ...]
     * @param mixed $options формировать select-options или нет (false)
     * @param string|null $lang
     * @return array [lvl=>[a=>selectedID, categories=>список категорий(массив или options)],...]
     */
    public function categoriesOptionsByLevel(array $categoriesID, $options = false, ?string $lang = null)
    {
        if (empty($categoriesID)) {
            return [];
        }

        $lang = $lang ?? $this->locale->current();

        # формируем список требуемых уровней категорий
        $levels = [];
        $fill = true;
        $parentID = $this->categoryRootID();
        foreach ($categoriesID as $lvl => $categoryID) {
            if ($categoryID || $fill) {
                $levels[$lvl] = $parentID;
                if (!$categoryID) {
                    break;
                }
                $parentID = $categoryID;
            } else {
                break;
            }
        }

        if (empty($levels)) {
            return [];
        }

        $data = $this->db->select(
            'SELECT C.id, CL.title, C.numlevel as lvl
            FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL
            WHERE C.numlevel IN (' . join(',', array_keys($levels)) . ')
              AND C.pid IN(' . join(',', $levels) . ')
              ' . $this->db->langAnd(true, 'C', 'CL', $lang) . '
            ORDER BY C.numleft'
        );
        if (empty($data)) {
            return [];
        }

        $levels = [];
        foreach ($data as $v) {
            $levels[$v['lvl']][$v['id']] = $v;
        }
        unset($data);

        foreach ($categoriesID as $lvl => $selectedID) {
            if (isset($levels[$lvl])) {
                $categoriesID[$lvl] = [
                    'a'          => $selectedID,
                    'categories' => (!empty($options) ?
                        HTML::selectOptions($levels[$lvl], $selectedID, $options['empty'], 'id', 'title') :
                        $levels[$lvl]),
                ];
            } else {
                $categoriesID[$lvl] = [
                    'a'          => $selectedID,
                    'categories' => false,
                ];
            }
        }

        return $categoriesID;
    }

    /**
     * Получаем данные parent-категорий
     * @param int $categoryID ID категории
     * @param bool $includingSelf включать текущую в итоговых список
     * @param bool $exludeRoot исключить корневой раздел
     * @return array [lvl=>id, ...]
     */
    public function categoryParentsID(int $categoryID, bool $includingSelf = true, bool $exludeRoot = true)
    {
        if (empty($categoryID)) {
            return [1 => 0];
        }
        $parentsData = $this->treeCategories()->getNodeParentsID(
            $categoryID,
            ($exludeRoot ? ' AND numlevel > 0' : ''),
            $includingSelf,
            ['id','numlevel']
        );
        $parentsID = [];
        if (! empty($parentsData)) {
            foreach ($parentsData as $v) {
                $parentsID[$v['numlevel']] = $v['id'];
            }
        }

        return $parentsID;
    }

    /**
     * Получаем названия parent-категорий
     * @param int $categoryID ID категории
     * @param bool $includingSelf включать текущую в итоговых список
     * @param bool $exludeRoot исключить корневой раздел
     * @param mixed $separator объединить в одну строку или FALSE
     * @param string|null $lang
     * @return array|string [lvl=>id, ...]
     */
    public function categoryParentsTitle(int $categoryID, bool $includingSelf = false, bool $exludeRoot = false, $separator = true, ?string $lang = null)
    {
        $lang = $lang ?? $this->locale->current();

        $parentsID = $this->treeCategories()
            ->getNodeParentsID($categoryID, ($exludeRoot ? ' AND numlevel > 0' : ''), $includingSelf);
        if (empty($parentsID)) {
            return ($separator !== false ? '' : []);
        }

        $data = $this->db->select_one_column(
            'SELECT CL.title
            FROM ' . static::TABLE_CATEGORIES . ' C, ' . static::TABLE_CATEGORIES_LANG . ' CL
            WHERE ' . $this->db->prepareIN('C.id', $parentsID) . $this->db->langAnd(true, 'C', 'CL', $lang) . '
            ORDER BY C.numleft'
        );
        if (empty($data)) {
            $data = [];
        }

        if ($separator !== false) {
            return join('  ' . ($separator === true ? '>' : $separator) . '  ', $data);
        } else {
            return $data;
        }
    }

    /**
     * Список таблиц модели с мультиязычными данными
     * @return array
     */
    public function getLocaleTables(): array
    {
        return [
            static::TABLE_QUESTIONS  => ['type' => 'table', 'fields' => $this->langQuestions, 'title' => Help::lang('Questions')],
            static::TABLE_CATEGORIES => ['type' => 'table', 'fields' => $this->langCategories, 'title' => Help::lang('Categories')],
        ];
    }

}