<?php
/**
 * Magento
 *
 * NOTICE OF LICENSE
 *
 * This source file is subject to the Open Software License (OSL 3.0)
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://opensource.org/licenses/osl-3.0.php
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@magentocommerce.com so we can send you a copy immediately.
 *
 * DISCLAIMER
 *
 * Do not edit or add to this file if you wish to upgrade Magento to newer
 * versions in the future. If you wish to customize Magento for your
 * needs please refer to http://www.magentocommerce.com for more information.
 *
 * @category   Varien
 * @package    Varien_Data
 * @copyright  Copyright (c) 2008 Irubin Consulting Inc. DBA Varien (http://www.varien.com)
 * @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
 */

/**
 * Data DB tree
 *
 * Data model:
 * id  |  pid  |  level | order
 *
 * @category   Varien
 * @package    Varien_Data
 * @author      Magento Core Team <core@magentocommerce.com>
 */
class Varien_Data_Tree_Db extends Varien_Data_Tree
{
    const ID_FIELD      = 'id';
    const PARENT_FIELD  = 'parent';
    const LEVEL_FIELD   = 'level';
    const ORDER_FIELD   = 'order';

    /**
     * DB connection
     *
     * @var Zend_Db_Adapter_Abstract
     */
    protected $_conn;

    /**
     * Data table name
     *
     * @var string
     */
    protected $_table;

    /**
     * SQL select object
     *
     * @var Zend_Db_Select
     */
    protected $_select;

    /**
     * Tree ctructure field names
     *
     * @var string
     */
    protected $_idField;
    protected $_parentField;
    protected $_levelField;
    protected $_orderField;

    /**
     * Db tree constructor
     *
     * $fields = array(
     *      Varien_Data_Tree_Db::ID_FIELD       => string,
     *      Varien_Data_Tree_Db::PARENT_FIELD   => string,
     *      Varien_Data_Tree_Db::LEVEL_FIELD    => string
     *      Varien_Data_Tree_Db::ORDER_FIELD    => string
     * )
     *
     * @param Zend_Db_Adapter_Abstract $connection
     * @param string $table
     * @param array $fields
     */
    public function __construct($connection, $table, $fields)
    {
        parent::__construct();

        if (!$connection) {
            throw new Exception('Wrong "$connection" parametr');
        }

        $this->_conn    = $connection;
        $this->_table   = $table;

        if (!isset($fields[self::ID_FIELD]) ||
            !isset($fields[self::PARENT_FIELD]) ||
            !isset($fields[self::LEVEL_FIELD]) ||
            !isset($fields[self::ORDER_FIELD])) {

            throw new Exception('"$fields" tree configuratin array');
        }

        $this->_idField     = $fields[self::ID_FIELD];
        $this->_parentField = $fields[self::PARENT_FIELD];
        $this->_levelField  = $fields[self::LEVEL_FIELD];
        $this->_orderField  = $fields[self::ORDER_FIELD];

        $this->_select  = $this->_conn->select();
        $this->_select->from($this->_table, array_values($fields));
    }

    public function getDbSelect()
    {
        return $this->_select;
    }

    public function setDbSelect($select)
    {
        $this->_select = $select;
    }

    /**
     * Load tree
     *
     * @param   int || Varien_Data_Tree_Node $parentNode
     * @param   int $recursionLevel recursion level
     * @return  this
     */
    public function load($parentNode=null, $recursionLevel=100)
    {
        if (is_null($parentNode)) {
            $this->_loadFullTree();
            return $this;
        }
        elseif ($parentNode instanceof Varien_Data_Tree_Node) {
            $parentId = $parentNode->getId();
        }
        elseif (is_numeric($parentNode)) {
            $parentId = $parentNode;
            $parentNode = null;
        }
        else {
            throw new Exception('root node id is not defined');
        }

        $select = clone $this->_select;
        $select->order($this->_table.'.'.$this->_orderField . ' ASC');
        $condition = $this->_conn->quoteInto("$this->_table.$this->_parentField=?", $parentId);
        $select->where($condition);
        $arrNodes = $this->_conn->fetchAll($select);
        foreach ($arrNodes as $nodeInfo) {
            $node = new Varien_Data_Tree_Node($nodeInfo, $this->_idField, $this, $parentNode);
            $this->addNode($node, $parentNode);

            if ($recursionLevel) {
                $node->loadChildren($recursionLevel-1);
            }
        }
        return $this;
    }

    public function loadNode($nodeId)
    {
        $select = clone $this->_select;
        $condition = $this->_conn->quoteInto("$this->_table.$this->_idField=?", $nodeId);
        $select->where($condition);
        $node = new Varien_Data_Tree_Node($this->_conn->fetchRow($select), $this->_idField, $this);
        $this->addNode($node);
        return $node;
    }

    public function appendChild($data=array(), $parentNode, $prevNode=null)
    {
        $orderSelect = $this->_conn->select();
        $orderSelect->from($this->_table, new Zend_Db_Expr('MAX('.$this->_conn->quoteIdentifier($this->_orderField).')'))
            ->where($this->_conn->quoteIdentifier($this->_parentField).'='.$parentNode->getId());

        $order = $this->_conn->fetchOne($orderSelect);
        $data[$this->_parentField] = $parentNode->getId();
        $data[$this->_levelField]  = $parentNode->getData($this->_levelField)+1;
        $data[$this->_orderField]  = $order+1;

        $this->_conn->insert($this->_table, $data);
        $data[$this->_idField] = $this->_conn->lastInsertId();

        return parent::appendChild($data, $parentNode, $prevNode);
    }

    /**
     * Move tree node
     *
     * @param Varien_Data_Tree_Node $node
     * @param Varien_Data_Tree_Node $parentNode
     * @param Varien_Data_Tree_Node $prevNode
     */
    public function moveNodeTo($node, $parentNode, $prevNode=null)
    {
        $data = array();
        $data[$this->_parentField]  = $parentNode->getId();
        $data[$this->_levelField]   = $parentNode->getData($this->_levelField)+1;
        // New node order
        if (is_null($prevNode) || is_null($prevNode->getData($this->_orderField))) {
            $data[$this->_orderField] = 1;
        }
        else {
            $data[$this->_orderField] = $prevNode->getData($this->_orderField)+1;
        }
        $condition = $this->_conn->quoteInto("$this->_idField=?", $node->getId());

        // For reorder new node branch
        $dataReorderNew = array(
            $this->_orderField => new Zend_Db_Expr($this->_conn->quoteIdentifier($this->_orderField).'+1')
        );
        $conditionReorderNew = $this->_conn->quoteIdentifier($this->_parentField).'='.$parentNode->getId().
                            ' AND '.$this->_conn->quoteIdentifier($this->_orderField).'>='. $data[$this->_orderField];

        // For reorder old node branch
        $dataReorderOld = array(
            $this->_orderField => new Zend_Db_Expr($this->_conn->quoteIdentifier($this->_orderField).'-1')
        );
        $conditionReorderOld = $this->_conn->quoteIdentifier($this->_parentField).'='.$node->getData($this->_parentField).
                            ' AND '.$this->_conn->quoteIdentifier($this->_orderField).'>'.$node->getData($this->_orderField);

        $this->_conn->beginTransaction();
        try {
            // Prepare new node branch
            $this->_conn->update($this->_table, $dataReorderNew, $conditionReorderNew);
            // Move node
            $this->_conn->update($this->_table, $data, $condition);
            // Update old node branch
            $this->_conn->update($this->_table, $dataReorderOld, $conditionReorderOld);
            $this->_updateChildLevels($node->getId(), $data[$this->_levelField]);
            $this->_conn->commit();
        }
        catch (Exception $e){
            $this->_conn->rollBack();
            throw new Exception('Can\'t move tree node');
        }
    }

    protected function _updateChildLevels($parentId, $parentLevel)
    {
        $select = $this->_conn->select()
            ->from($this->_table, $this->_idField)
            ->where($this->_parentField.'=?', $parentId);
        $ids = $this->_conn->fetchCol($select);

        if (!empty($ids)) {
            $this->_conn->update($this->_table,
                array($this->_levelField=>$parentLevel+1),
                $this->_conn->quoteInto($this->_idField.' IN (?)', $ids));
            foreach ($ids as $id) {
            	$this->_updateChildLevels($id, $parentLevel+1);
            }
        }
        return $this;
    }

    protected function _loadFullTree()
    {
        $select = clone $this->_select;
        $select->order($this->_table . '.' . $this->_levelField)
            ->order($this->_table.'.'.$this->_orderField);

        $arrNodes = $this->_conn->fetchAll($select);

        foreach ($arrNodes as $nodeInfo) {
            $node = new Varien_Data_Tree_Node($nodeInfo, $this->_idField, $this);
            $parentNode = $this->getNodeById($nodeInfo[$this->_parentField]);
            $this->addNode($node, $parentNode);
        }

        return $this;
    }

    public function removeNode($node)
    {
        // For reorder old node branch
        $dataReorderOld = array(
            $this->_orderField => new Zend_Db_Expr($this->_conn->quoteIdentifier($this->_orderField).'-1')
        );
        $conditionReorderOld = $this->_conn->quoteIdentifier($this->_parentField).'='.$node->getData($this->_parentField).
                            ' AND '.$this->_conn->quoteIdentifier($this->_orderField).'>'.$node->getData($this->_orderField);

        $this->_conn->beginTransaction();
        try {
            $condition = $this->_conn->quoteInto("$this->_idField=?", $node->getId());
            $this->_conn->delete($this->_table, $condition);
            // Update old node branch
            $this->_conn->update($this->_table, $dataReorderOld, $conditionReorderOld);
            $this->_conn->commit();
        }
        catch (Exception $e){
            $this->_conn->rollBack();
            throw new Exception('Can\'t remove tree node');
        }
        parent::removeNode($node);
        return $this;
    }
}