-- get the depth of a subtree
--
-- the depth includes the current level too
-- if the tree is empty, the depth is 0
--
-- @param: tblname    name of the tree-table
-- @param: nid        id of the root-node of the subtree
-- @returns: odepth   depth of the subtree

DELIMITER $$
DROP PROCEDURE IF EXISTS sp_tree_depth$$
CREATE PROCEDURE sp_tree_depth (IN tblname VARCHAR(64), IN nid INT, OUT odepth INT) 
BEGIN
  DECLARE depth INT DEFAULT 0;
  DECLARE n INT DEFAULT 0;
  -- the idea:
  -- 
  -- 1. we start with inserting the start-node into __a
  -- 2. search all nodes in <tblname> which have one of the nodes in __a 
  --    as parent and insert them into __b
  -- 3. take __b as the new __a and continue with 2. as long as we
  --    have something to join
  -- ... on the way count the iterations
  --
  -- each iterations goes one level deeper into the tree

  CREATE TEMPORARY TABLE __a (id int);
  CREATE TEMPORARY TABLE __b LIKE __a;

  SET @stmt := CONCAT('insert into __a select t.id FROM ', tblname, ' AS t WHERE t.parent_id = ', nid);
  PREPARE stmt_sp_tree_depth FROM @stmt;

  SET @stmt := CONCAT('insert into __b select t.id from ', tblname, ' AS t JOIN __a AS t1 ON t1.id = t.parent_id');
  PREPARE stmt_sp_tree_depth_sub FROM @stmt;

  EXECUTE stmt_sp_tree_depth;

  SELECT count(*) from __a INTO n;
  IF n != 0 THEN
    SET depth = depth + 1;
  END IF;

loopy: LOOP
    EXECUTE stmt_sp_tree_depth_sub;

    delete from __a;
    insert into __a select * from __b;
    delete from __b;

    SELECT count(*) from __a INTO n;
    IF n = 0 THEN
      LEAVE loopy;
    END IF;
    SET depth = depth + 1;
  END LOOP loopy;
  DEALLOCATE PREPARE stmt_sp_tree_depth_sub;
  DEALLOCATE PREPARE stmt_sp_tree_depth;

  DROP TEMPORARY TABLE __a;
  DROP TEMPORARY TABLE __b;

  SET odepth = depth;
END$$

-- get all the nodes of a subtree
-- 
-- @note: you have to free the table <res_tblname> afterwards with 
--        DROP TEMPORARY TABLE <res_tblname>;
--
-- @param: tblname    name of the tree-table
-- @param: nid        id of the root-node of the subtree
-- @param: res_tblname  name of the temporary result table
-- @returns: a result-set with the node-ids

DROP PROCEDURE IF EXISTS sp_tree_get_subnodes$$
CREATE PROCEDURE sp_tree_get_subnodes (IN tblname VARCHAR(64), IN nid INT, IN res_tblname VARCHAR(64)) 
BEGIN
  DECLARE n INT DEFAULT 0;
  CREATE TEMPORARY TABLE __a (id int);
  CREATE TEMPORARY TABLE __b LIKE __a;

  SET @stmt := CONCAT('insert into __a select t.id FROM ', tblname, ' AS t WHERE t.parent_id = ', nid);
  PREPARE stmt_sp_insert_a FROM @stmt;
  EXECUTE stmt_sp_insert_a;
  DEALLOCATE PREPARE stmt_sp_insert_a;

  SET @stmt := CONCAT('insert into __b select t.id from ', tblname, ' AS t JOIN __a AS t1 ON t1.id = t.parent_id');
  PREPARE stmt_sp_insert_b FROM @stmt;

  SET @stmt := CONCAT('CREATE TEMPORARY TABLE ', res_tblname, ' LIKE __a');
  PREPARE stmt_sp_create_res_tbl FROM @stmt;
  EXECUTE stmt_sp_create_res_tbl;
  DEALLOCATE PREPARE stmt_sp_create_res_tbl;

  SET @stmt := CONCAT('INSERT INTO ', res_tblname, ' SELECT * FROM __a');
  PREPARE stmt_sp_insert_res_tbl FROM @stmt;
  EXECUTE stmt_sp_insert_res_tbl;
  DEALLOCATE PREPARE stmt_sp_insert_res_tbl;

  SET @stmt := CONCAT('INSERT INTO ', res_tblname, ' SELECT * FROM __b');
  PREPARE stmt_sp_insert_res_tbl FROM @stmt;

loopy: LOOP
    EXECUTE stmt_sp_insert_b;

    delete from __a;
    insert into __a select * from __b;
    EXECUTE stmt_sp_insert_res_tbl;
    delete from __b;

    SELECT count(*) from __a INTO n;
    IF n = 0 THEN
      LEAVE loopy;
    END IF;
  END LOOP loopy;
  DEALLOCATE PREPARE stmt_sp_insert_b;
  DEALLOCATE PREPARE stmt_sp_insert_res_tbl;

  DROP TEMPORARY TABLE __a;
  DROP TEMPORARY TABLE __b;
END$$

-- get parent of a subtree
-- 
-- @param: tblname    name of the tree-table
-- @param: nid        id of the child-node
-- @returns: parent_id  parent node-id of the child-node

DROP PROCEDURE IF EXISTS sp_tree_get_parent$$
CREATE PROCEDURE sp_tree_get_parent (IN tblname VARCHAR(64), IN nid INT, OUT parent_id INT) 
BEGIN
  set @stmt := concat('SELECT parent_id INTO @parent_id FROM ', tblname, ' AS t WHERE t.id = ', nid);
  prepare stmt_sp_insert_a from @stmt;
  execute stmt_sp_insert_a;
  deallocate prepare stmt_sp_insert_a;

  SET parent_id = @parent_id;
END$$

-- delete all the subnodes of a subtree
-- 
-- @param: tblname    name of the tree-table
-- @param: nid        id of the parent-node

DROP PROCEDURE IF EXISTS sp_tree_delete_subnodes$$
CREATE PROCEDURE sp_tree_delete_subnodes (IN tblname VARCHAR(64), IN nid INT) 
BEGIN
  CALL sp_tree_get_subnodes(tblname, nid, 'res');

  set @stmt := concat('DELETE FROM t USING ', tblname, ' AS t, res WHERE t.id = res.id');
  prepare stmt_sp_delete_a from @stmt;
  execute stmt_sp_delete_a;
  deallocate prepare stmt_sp_delete_a;

  DROP TEMPORARY TABLE res;
END$$

-- delete node
--
-- the node should have no subnodes
-- 
-- @param: tblname    name of the tree-table
-- @param: nid        id of the parent-node

DROP PROCEDURE IF EXISTS sp_tree_delete_node$$
CREATE PROCEDURE sp_tree_delete_node (IN tblname VARCHAR(64), IN nid INT) 
BEGIN
  set @stmt := concat('DELETE FROM ', tblname, ' WHERE id = ', nid);
  prepare stmt_sp_delete_a from @stmt;
  execute stmt_sp_delete_a;
  deallocate prepare stmt_sp_delete_a;
END$$


-- move a node to a new parent
--
-- @param: tblname    name of the tree-table
-- @param: new_id     id of the new parent-node
-- @param: old_id     id of the node

DROP PROCEDURE IF EXISTS sp_tree_move_node$$
CREATE PROCEDURE sp_tree_move_node (IN tblname VARCHAR(64), IN new_id INT, IN old_id INT) 
BEGIN
  set @stmt := concat('UPDATE ', tblname, ' SET parent_id = ', new_id, ' WHERE id = ', old_id);
  prepare stmt_sp_delete_a from @stmt;
  execute stmt_sp_delete_a;
  deallocate prepare stmt_sp_delete_a;
END$$

-- move a subnodes to a new parent
--
-- @param: tblname    name of the tree-table
-- @param: new_id     id of the new parent-node
-- @param: old_id     id of the old parent-node

DROP PROCEDURE IF EXISTS sp_tree_move_subnodes$$
CREATE PROCEDURE sp_tree_move_subnodes (IN tblname VARCHAR(64), IN new_id INT, IN old_id INT) 
BEGIN
  set @stmt := concat('UPDATE ', tblname, ' SET parent_id = ', new_id, ' WHERE parent_id = ', old_id);
  prepare stmt_sp_delete_a from @stmt;
  execute stmt_sp_delete_a;
  deallocate prepare stmt_sp_delete_a;
END$$


-- insert is left out as it is application specific
