<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Category
 *
 * @author fabi0
 */

namespace Models;

class Category {

    private $_query;

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

    public function getCategoryTree($category) {
        return $this->_query->query("
            SELECT node.category_id, node.category_name FROM categories AS node, categories AS parent
            WHERE node.category_left BETWEEN parent.category_left AND parent.category_right AND parent.category_id = '$category'
            ORDER BY node.category_left;")->getResult();
    }

    public function getCategoryDepth() {

        return $this->_query->query("
            SELECT node.category_id, node.category_name, (COUNT(parent.category_name) - 1) AS category_depth
            FROM categories AS node,categories AS parent
            WHERE node.category_left BETWEEN parent.category_left AND parent.category_right
            GROUP BY node.category_id
            ORDER BY node.category_left;")->getResult();
    }

    public function getCategoryWithPosts() {
        return $this->_query->query("SELECT parent.category_id, COUNT(discussions.discussion_content) as category_posts
                              FROM categories AS node ,categories AS parent,discussions
                              WHERE node.category_left BETWEEN parent.category_left
                              AND parent.category_right AND node.category_id = discussions.discussion_category
                              AND discussions.discussion_name IS NOT NULL
                              GROUP BY parent.category_id
                              ORDER BY node.category_left")->getResult();
    }

    public function getFullCategoryTreeWithPostsCount() {
        $cat1 = $this->getCategoryDepth();
        $cat2 = $this->getCategoryWithPosts();
        $newArray = array();
        foreach ($cat1 as $k => $v) {

            $newArray[$v->category_id]['category_name'] = $v->category_name;
            $newArray[$v->category_id]['category_depth'] = $v->category_depth;
        }

        foreach ($cat2 as $k => $vv) {
            $newArray[$vv->category_id]['category_posts'] = $vv->category_posts;
        }

        foreach ($newArray as $key => $value) {
            if (!isset($newArray[$key]['category_posts'])) {
                $newArray[$key]['category_posts'] = 0;
            }
        }
        return $newArray;
    }

    public function getAllPost() {
        return $this->_query->query("SELECT count(discussion_id) as all_post FROM discussions WHERE discussion_name IS NOT NULL")->getResult();
    }

    public function renameCategory($id, $newName) {
        $sql = "UPDATE categories SET `category_name` = '$newName' WHERE category_id = $id;";
        $params = array(
            ':name' => array(
                'param' => $newName,
                'type' => 2
            ),
            ':id' => array(
                'param' => $id,
                'type' => 1
            )
        );
        $this->_query->query($sql, $params);
        return $this;
    }

    public function deleteCategory($category_id) {
        $params = array(
            ':id' => array(
                'param' => $category_id,
                'type' => 1
            )
        );
        $this->_query->_query("
            LOCK TABLE categories WRITE;
            SELECT @myLeft := category_left, @myRight := category_right, @myWidth := category_right - category_left + 1
            FROM categories WHERE category_id = :id;
            DELETE FROM categories WHERE category_left BETWEEN @myLeft AND @myRight;
            UPDATE categories SET category_right = category_right - @myWidth WHERE category_right > @myRight;
            UPDATE categories SET category_left = category_left - @myWidth WHERE category_left > @myRight;
            UNLOCK TABLES;", $params);
        return true;
    }

    public function createCategory($parent_id, $category_name) {
        $params = array(
            ':parent' => array(
                'param' => $parent_id,
                'type' => 1
            ),
            ':name' => array(
                'param' => $category_name,
                'type' => 2
            )
        );
        $sql = "
            LOCK TABLE categories WRITE;
            SELECT @myLeft := category_left FROM categories WHERE category_id = :parent;
            UPDATE categories SET category_right = category_right + 2 WHERE category_right > @myLeft;
            UPDATE categories SET category_left = category_left + 2 WHERE category_left > @myLeft;
            INSERT INTO categories(category_name, category_left, category_right) VALUES(:name, @myLeft + 1, @myLeft + 2);
            UNLOCK TABLES;";
        $this->_query->_query($sql, $params);
        return true;
    }

    public function createRootCategory($category_name) {
        $params = array(
            ':name' => array(
                'param' => $category_name,
                'type' => 2
            )
        );
        $sql = "
            LOCK TABLE categories WRITE;
            SELECT @myRight := MAX(category_right) FROM categories;
            INSERT INTO categories(category_name, category_left, category_right) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
            UNLOCK TABLES;";
        $this->_query->_query($sql, $params);
        return true;
    }

}