<?php

namespace Models;

class Category {

    private $_query;

    public function __construct() {
        $this->_query = \Models\DB::getInstance();
    }

    /**
     * Category name is mandatory.
     *
     * Return category and all subcategories.
     *
     * @param string $category Category Name
     * @param string $select Optional - select clause. Default is 'node.category_id, node.name, node.lft' .
     */
    public function getCategoryTree($category, $select = 'node.category_id, node.name, node.lft') {
        return $this->_query->query("
            SELECT $select FROM categories AS node, categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt AND parent.name = '$category' AND parent.allowed = 1
            ORDER BY node.lft;")->getResult();
    }

    /**
     * Return categories depths.
     */
    public function getCategoryDepth() {

        $result = $this->_query->query("
            SELECT node.name, node.category_id, (COUNT(parent.name) - 1) AS depth
            FROM categories AS node,
            categories AS parent
            WHERE node.lft BETWEEN parent.lft AND parent.rgt AND node.name != 'bbdbe444288550204c968fe7002a97a9'
            GROUP BY node.name
            ORDER BY node.lft;")->getResult();
        foreach ($result as $key => $value) {
            $return[] = (array) $value;
        }
        return $return;
    }

    public function insertCategory($categoryName, $leftCategory) {


        return ($leftCategory == '' ?
                        $this->_query->query("
                        LOCK TABLE categories WRITE;
                        SELECT @myRight := rgt FROM categories WHERE name = 'bbdbe444288550204c968fe7002a97a9';
                        UPDATE categories SET rgt = rgt + 2 WHERE rgt > @myRight;
                        UPDATE categories SET lft = lft + 2 WHERE lft > @myRight;
                        INSERT INTO categories(name, lft, rgt) VALUES('$categoryName', @myRight + 1, @myRight + 2);
                        UNLOCK TABLES;") :
                        $this->_query->query("
                        LOCK TABLE categories WRITE;
                        SELECT @myLeft := lft FROM categories
                        WHERE name = '$leftCategory';
                        UPDATE categories SET rgt = rgt + 2 WHERE rgt > @myLeft;
                        UPDATE categories SET lft = lft + 2 WHERE lft > @myLeft;
                        INSERT INTO categories(name, lft, rgt) VALUES('$categoryName', @myLeft + 1, @myLeft + 2);
                        UNLOCK TABLES;")
                );
    }

    public function renameCategory($id, $newName) {
        $this->_query->query("UPDATE `forum`.`categories` SET `name` = '$newName' WHERE `categories`.`category_id` = $id;");
        return $this;
    }

    public function setPrivilege($id, $privilege) {
        $this->_query->query("UPDATE `forum`.`categories` SET `allowed` = $privilege WHERE `categories`.`category_id` = $id;");
        return $this;
    }

}