<?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($databaseInstance) {
        $this->_query = $databaseInstance;
    }

    public function getCategoryTree($category_id) {
        $sql = "
            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 = :id
            ORDER BY node.category_left;";
        $params = array(
            ':id' => array(
                'param' => $category_id,
                'type' => 1
            )
        );
        $this->_query->query($sql, $params);
        if ($this->_query->getResult()) {
            return getResult();
        }
        return false;
    }

    public function getCategoryDepth($arrange = false, $resultType = 5) {

        $result = array();
        $sql = "SELECT category_id,category_parent,category_depth,category_name FROM categories
            ORDER BY categories.category_left;
            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;";
        $query = $this->_query->query($sql, NULL, $resultType)->getResult();
        if ($arrange) {
            foreach ($query as $value) {
                $result[$value->category_id]['category_name'] = $value->category_name;
                $result[$value->category_id]['category_depth'] = $value->category_depth;
                $result[$value->category_id]['category_parent'] = $value->category_parent;
            }
            return $result;
        }
        return $query;
    }

    public function getCategoryWithPosts() {
        $sql = "
            SELECT parent.category_id, COUNT(discussions.discussion_id) 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
            GROUP BY parent.category_id
            ORDER BY node.category_left";

        return $this->_query->query($sql)->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() {
        $sql = "SELECT count(discussion_id) as all_post FROM discussions WHERE discussion_name IS NOT NULL";
        return $this->_query->query($sql)->getResult();
    }

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

    public function deleteCategory($category_id) {
        $sql = "
            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 = array(
            ':id' => array(
                'param' => $category_id,
                'type' => 1
            )
        );
        return $this->_query->_query($sql, $params)->getResult();
    }

    public function createCategory($parent_id, $category_name) {
        $sql = "
            LOCK TABLE categories WRITE;
            SELECT @parentDepth :=category_depth FROM categories WHERE category_id = :parent;
            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, category_parent,category_depth)
            VALUES(:name, @myLeft + 1, @myLeft + 2, :parent,@parentDepth+1);
            UNLOCK TABLES;";
        $params = array(
            ':parent' => array(
                'param' => $parent_id,
                'type' => 1
            ),
            ':name' => array(
                'param' => $category_name,
                'type' => 2
            )
        );
        return $this->_query->query($sql, $params);
    }

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

    public function organizeCategories($data) {
        foreach ($data as $value) {
            $sql = "UPDATE categories SET category_parent = :parent, category_depth = :depth, category_left = :left, category_right = :right "
                    . "WHERE category_id = :itemID;";
            $params = array(
                ':parent' => array(
                    'param' => (int) $value['parent_id'],
                    'type' => 1
                ),
                ':itemID' => array(
                    'param' => (int) $value['item_id'],
                    'type' => 1
                ),
                ':depth' => array(
                    'param' => ((int) $value['depth']) - 1,
                    'type' => 1
                ),
                ':left' => array(
                    'param' => ((int) $value['left']) - 1,
                    'type' => 1
                ),
                ':right' => array(
                    'param' => ((int) $value['right']) - 1,
                    'type' => 1
                )
            );
            $this->_query->query($sql, $params);
        }
    }

    public function buildTree(array $elements, $parentId = 0) {
        $branch = array();

        foreach ($elements as $element) {
            if ($element['category_parent'] == $parentId) {
                $children = $this->buildTree($elements, $element['category_id']);
                if ($children) {
                    $element['child'] = $children;
                }
                $branch[] = $element;
            }
        }
        return $branch;
    }

}