DELIMITER $$

DROP FUNCTION IF EXISTS get_sub_unit_path;$$

CREATE FUNCTION  get_sub_unit_path
(
  id INT
)
RETURNS VARCHAR(512) CHARSET UTF8
DETERMINISTIC
READS SQL DATA
BEGIN

  IF id IS NULL
  THEN
    RETURN (SELECT `name` FROM `ohrm_subunit` WHERE `level` = 0);
  END IF;

    SELECT cst.lft, cst.rgt INTO @lft, @rgt
    FROM `ohrm_subunit` AS cst
    WHERE cst.id = id
    LIMIT 1;
  
  RETURN (
    SELECT GROUP_CONCAT(`cst`.`name` SEPARATOR ' / ') AS sub_unit_path
    FROM `ohrm_subunit` AS cst
    WHERE cst.lft <= @lft AND cst.rgt >= @rgt  
    );
END;$$

DROP FUNCTION IF EXISTS is_sub_unit_leaf_node;$$

CREATE FUNCTION  is_sub_unit_leaf_node
(
  id INT
)
RETURNS BOOLEAN
DETERMINISTIC
READS SQL DATA
BEGIN
  SELECT cst.lft, cst.rgt INTO @lft, @rgt
    FROM `ohrm_subunit` AS cst
    WHERE cst.id = id
    LIMIT 1;

  SELECT COUNT(*) INTO  @children_count
    FROM `ohrm_subunit` AS cst
    WHERE cst.lft > @lft AND cst.rgt < @rgt
    LIMIT 1;

  RETURN (@children_count = 0);
END;$$

DROP FUNCTION IF EXISTS get_parant_id;$$

CREATE FUNCTION  get_parant_id
(
  id INT
)
RETURNS INT
DETERMINISTIC
READS SQL DATA
BEGIN
SELECT (SELECT t2.id 
               FROM ohrm_subunit t2 
               WHERE t2.lft < t1.lft AND t2.rgt > t1.rgt    
               ORDER BY t2.rgt-t1.rgt ASC LIMIT 1) INTO @parent
FROM ohrm_subunit t1 WHERE t1.id = id;

RETURN @parent;

END;$$

DROP FUNCTION IF EXISTS get_sub_unit_title;$$

CREATE FUNCTION  get_sub_unit_title
(
  id INT
)
RETURNS VARCHAR(255)
DETERMINISTIC
READS SQL DATA
BEGIN

  IF id IS NULL
    THEN
     RETURN '';
  END IF;

  SELECT `cst`.`name`, `cst`.`level` INTO @name, @level
     FROM `ohrm_subunit` AS cst
     WHERE `cst`.`id` = id
     LIMIT 1;

  IF @level = 0
   THEN
     RETURN 'Unassigned to Subunits';
   ELSE
     RETURN CONCAT(@name, ' (Current)');
  END IF;

END;$$

DELIMITER ;