<?php  

class TreeModelBase
{
	private $bID;
	private $db;
	
	public $tableName;

	public function __construct($bID, $db, $tableName)
	{
		if(!is_int($bID))
			throw new Exception('bID must be an integer.');

		if(!ctype_alpha($tableName))
			throw new Exception('tableName must be alphanumeric.');
					
		$this->bID = $bID;
		$this->db = $db;
		$this->tableName = $tableName;
	}

	public function AddNode($nodeName, $afterId, $child)
	{
		if(!is_int($afterId) || $afterId < -1)
			throw new Exception('afterId must be an integer >= -1.');
			
		$this->lockTable();
			
		$query = $this->db->Execute("SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 AS width FROM $this->tableName WHERE id = ? AND bID = ?", array($afterId, $this->bID));
		
		if($query->RecordCount() > 0)
		{
			if($child)
			{
				// If adding to a leaf node, must use left for adding instead of right.
				$this->db->Execute("UPDATE $this->tableName SET rgt = rgt + 2 WHERE rgt > @myLeft AND bID = ?", array($this->bID));
				$this->db->Execute("UPDATE $this->tableName SET lft = lft + 2 WHERE lft > @myLeft AND bID = ?", array($this->bID));

				$this->db->Execute("INSERT INTO $this->tableName (name, bID, lft, rgt) VALUES (?, ?, @myLeft + 1, @myLeft + 2)", array($nodeName, $this->bID));
			}
			else
			{
				$this->db->Execute("UPDATE $this->tableName SET rgt = rgt + 2 WHERE rgt > @myRight AND bID = ?", array($this->bID));
				$this->db->Execute("UPDATE $this->tableName SET lft = lft + 2 WHERE lft > @myRight AND bID = ?", array($this->bID));

				$this->db->Execute("INSERT INTO $this->tableName (name, bID, lft, rgt) VALUES (?, ?, @myRight + 1, @myRight + 2)", array($nodeName, $this->bID));
			}
		}
		else if($afterId == 0)
		{
			// Special case for inserting at first position.
			$this->db->Execute("UPDATE $this->tableName SET rgt = rgt + 2, lft = lft + 2 WHERE bID = ?", array($this->bID));

			$this->db->Execute("INSERT INTO $this->tableName (name, bID, lft, rgt) VALUES (?, ?, 1, 2)", array($nodeName, $this->bID));
		}
		else if($afterId == -1)
		{
			// Special case for inserting at last position.
			$this->db->Execute("SELECT @maxRight := IFNULL(MAX(rgt), 0) + 1 FROM $this->tableName WHERE bID = ?", array($this->bID));
			
			$this->db->Execute("INSERT INTO $this->tableName (name, bID, lft, rgt) VALUES (?, ?, @maxRight, @maxRight+1)", array($nodeName, $this->bID));
		}
		else
		{
			$this->unlockTable();
			throw new Exception('id '.$afterId.' not found in tree.');
		}
		
		$this->unlockTable();
		
		return $this->db->Insert_ID();
	}

	public function DeleteNode($nodeId, $deleteChildren = false)
	{
		if(!is_int($nodeId) || $nodeId <= 0)
			throw new Exception('nodeId must be an integer > 0.');

		$query = $this->db->GetRow("SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1 AS width FROM $this->tableName WHERE id = ? AND bID = ?", array($nodeId, $this->bID));

		if(!empty($query))
		{
			$this->lockTable();
			
			if($category['width'] == 2 || $deleteChildren)
			{
				// Deleting a leaf node or whole tree is simple
				$this->db->Execute("DELETE FROM $this->tableName WHERE lft BETWEEN @myLeft AND @myRight AND bID = ?", array($this->bID));
				$status = $this->db->Affected_Rows() == 1;

				$this->db->Execute("UPDATE $this->tableName SET rgt = rgt - @myWidth WHERE rgt > @myRight AND bID = ?", array($this->bID));
				$this->db->Execute("UPDATE $this->tableName SET lft = lft - @myWidth WHERE lft > @myRight AND bID = ?", array($this->bID));
			}
			else
			{
				// Deleting node where children should be kept.
				$this->db->Execute("DELETE FROM $this->tableName WHERE lft = @myLeft AND bID = ?", array($this->bID));				
				$status = $this->db->Affected_Rows() == 1;

				$this->db->Execute("UPDATE $this->tableName SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight AND bID = ?", array($this->bID));
				$this->db->Execute("UPDATE $this->tableName SET rgt = rgt - 2 WHERE rgt > @myRight AND bID = ?", array($this->bID));
				$this->db->Execute("UPDATE $this->tableName SET lft = lft - 2 WHERE lft > @myRight AND bID = ?", array($this->bID));
			}
			
			$this->unlockTable();
			return $status;
		}
		else
			throw new Exception('nodeId '.$nodeId.' not found in table.');
	}

	public function MoveNode($nodeId, $targetId, $child)
	{
		if(!is_int($nodeId))
			throw new Exception('nodeId must be an integer > 0.');

		if(!is_int($targetId) || $targetId < 0)
			throw new Exception('targetId must be an integer >= 0.');

		$affectedIds = $this->db->GetCol("SELECT id FROM $this->tableName WHERE lft >= (SELECT lft FROM $this->tableName WHERE id = ? AND bID = ?) AND rgt <= (SELECT rgt FROM $this->tableName WHERE id = ? AND bID = ?)", array($nodeId, $this->bID, $nodeId, $this->bID));

		if(in_array($targetId, $affectedIds))
			throw new Exception('Cannot move a node down on the same branch of the tree.');

		$affectedList = ' IN(' . join(',', $affectedIds) . ') ';
		$sql = "SELECT lft, rgt, rgt - lft + 1 AS width FROM $this->tableName WHERE id = ? AND bID = ?";

		$category = $this->db->GetRow($sql, array($nodeId, $this->bID));
		
		$this->lockTable();

		// Remove the category
		$this->db->Execute("UPDATE $this->tableName SET rgt = rgt - $category[width] WHERE bID = ? AND rgt > $category[rgt] AND id NOT " . $affectedList, array($this->bID));
		$this->db->Execute("UPDATE $this->tableName SET lft = lft - $category[width] WHERE bID = ? AND lft > $category[rgt] AND id NOT " . $affectedList, array($this->bID));

		if($targetId > 0)
		{
			$target = $this->db->GetRow($sql, array($targetId, $this->bID));
		}
		else
		{
			$target = array('lft' => 0, 'rgt' => 0, 'width' => 0);
		}

		// Make room for move
		if($targetId > 0)
		{
			if($child)
			{
				$this->db->Execute("UPDATE $this->tableName SET rgt = rgt + $category[width] WHERE bID = ? AND rgt > $target[lft] AND id NOT " . $affectedList, array($this->bID));
				$this->db->Execute("UPDATE $this->tableName SET lft = lft + $category[width] WHERE bID = ? AND lft > $target[lft] AND id NOT " . $affectedList, array($this->bID));
			}
			else
			{
				$this->db->Execute("UPDATE $this->tableName SET rgt = rgt + $category[width] WHERE bID = ? AND rgt > $target[rgt] AND id NOT " . $affectedList, array($this->bID));
				$this->db->Execute("UPDATE $this->tableName SET lft = lft + $category[width] WHERE bID = ? AND lft > $target[rgt] AND id NOT " . $affectedList, array($this->bID));
			}
		}
		else
		{
			// Special case for inserting at first position.
			$this->db->Execute("UPDATE $this->tableName SET rgt = rgt + $category[width], lft = lft + $category[width] WHERE bID = ? AND id NOT " . $affectedList, array($this->bID));
		}

		// Finally, move ids. Need target update.
		if($targetId > 0)
		{
			$target = $this->db->GetRow($sql, array($targetId, $this->bID));
		}
		else
		{
			$target = array('lft' => 0, 'rgt' => 0, 'width' => 0);
		}

		$distance = ($child ? $target['lft'] : $target['rgt']) - $category['lft'] + 1;

		$this->db->Execute("UPDATE $this->tableName SET rgt = rgt + $distance, lft = lft + $distance WHERE bID = ? AND id " . $affectedList, array($this->bID));
		
		$this->unlockTable();

		return $this->db->Affected_Rows() > 0;
	}

	public function RenameNode($id, $newName)
	{
		if(!is_int($id) || $id <= 0)
			throw new Exception('id must be an integer > 0.');

		$this->db->Execute("UPDATE $this->tableName SET name = ? WHERE id = ? AND bID = ?", array($newName, $id, $this->bID));

		return $this->db->Affected_Rows() == 1;
	}
	
	///// Locking ///////////////////////////////////////////////////
	
	private function lockTable()
	{
		$this->db->Execute("LOCK TABLES $this->tableName WRITE");
	}

	private function unlockTable()
	{
		$this->db->Execute('UNLOCK TABLES');
	}

	///// Display methods ///////////////////////////////////////////

	public function IsLeaf($nodeId)
	{
		if(!is_int($nodeId) || $nodeId <= 0)
			throw new Exception('nodeId must be an integer > 0.');

		$query = $this->db->GetOne("SELECT IF(lft=rgt-1, 1, 0) FROM $this->tableName WHERE id = ? AND bID = ?", array($nodeId, $this->bID));
		return $query == 1;
	}

	public function Breadcrumbs($nodeId)
	{
		if(!is_int($nodeId) || $nodeId <= 0)
			throw new Exception('nodeId must be an integer > 0.');

		$query = $this->db->Execute("SELECT id,`name` FROM $this->tableName WHERE bID = ? AND lft <= (SELECT lft FROM $this->tableName WHERE id = ? AND bID = ?) AND rgt >= (SELECT rgt FROM $this->tableName WHERE id = ? AND bID = ?) ORDER BY lft", array($this->bID, $nodeId, $this->bID, $nodeId, $this->bID));
		return $query->GetAll();
	}

	public function DropdownList($nodeId = 0, $depthChar = '=')
	{
		if(!is_int($nodeId) || $nodeId <= 0)
			throw new Exception('nodeId must be an integer >= 0.');

		$categories = $this->DisplayNode($nodeId);
		$output = array();

		foreach($categories as $category)
		{
			$output[$category['id']] = str_repeat($depthChar, $category['depth']) . $category['name'];
		}

		return $output;
	}

	public function DisplayNode($nodeId, $depth = null, $showTopCategory = true)
	{
		if($depth !== null)
		{
			$having = 'HAVING depth <= ?';
			$having .= $showTopCategory ? '' : ' AND depth > 0';
		}
		else
			$having = '';

		if($nodeId == 0)
		{
			// Special case for displaying all root nodes.
			$sql = "
			SELECT node.id, node.name, (COUNT(parent.id) - 1) AS depth, IF(node.rgt-node.lft=1, 1, 0) AS leaf
			FROM $this->tableName AS node,
			$this->tableName AS parent
			WHERE node.bID = ? AND parent.bID = ? 
			AND node.lft BETWEEN parent.lft AND parent.rgt
			GROUP BY node.id
			".$having."
			ORDER BY node.lft
			";
			
			$arguments = array($this->bID, $this->bID);
		}
		else
		{
			$sql = "
			SELECT node.id, node.name, (COUNT(parent.id) - (sub_tree.depth + 1)) AS depth, IF(node.rgt-node.lft=1, 1, 0) AS leaf
			FROM $this->tableName AS node,
			$this->tableName AS parent,
			$this->tableName AS sub_parent,
			(
			SELECT node.id, node.name, (COUNT(parent.id) - 1) AS depth
			FROM $this->tableName AS node,
			$this->tableName AS parent
			WHERE node.bID = ? AND parent.bID = ? AND sub_parent.bID = ?
			AND node.lft BETWEEN parent.lft AND parent.rgt
			AND node.id = ?
			GROUP BY node.id
			ORDER BY node.lft
			)AS sub_tree
			WHERE node.lft BETWEEN parent.lft AND parent.rgt
			AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
			AND sub_parent.id = sub_tree.id
			GROUP BY node.id
			".$having."
			ORDER BY node.lft
			";
			
			$arguments = array($this->bID, $this->bID, $this->bID, $nodeId);
		}

		if($depth !== null) $arguments[] = $depth;

		return $this->db->GetAll($sql, $arguments);
	}
}