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 NULL;
  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` order by `level` SEPARATOR ' / ') AS sub_unit_path
    FROM `ohrm_subunit` AS cst
    WHERE cst.lft <= @lft AND cst.rgt >= @rgt
    );
END;$$

DROP FUNCTION IF EXISTS get_sub_unit_accrued_employee_count;$$

CREATE FUNCTION  get_sub_unit_accrued_employee_count
  (
    id INT
  )
  RETURNS INTEGER
DETERMINISTIC
READS SQL DATA
BEGIN
    IF id IS NULL
    THEN
      RETURN NULL;
    END IF;

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

    RETURN (
      SELECT COUNT(emp.emp_number) AS sub_unit_accrued_emp_count
      FROM `hs_hr_employee` AS emp WHERE emp.work_station IN (SELECT cst.id FROM `ohrm_subunit` AS cst WHERE cst.lft >= @lft AND cst.rgt <= @rgt)
    );
END;$$

DROP FUNCTION IF EXISTS get_sub_unit_at_hierarchy_by_level;$$

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

  IF id IS NULL
  THEN
    RETURN NULL;
  END IF;

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

  RETURN (
    SELECT `cst`.`name` AS sub_unit_path
    FROM `ohrm_subunit` AS cst
    WHERE cst.lft <= @lft AND cst.rgt >= @rgt AND cst.level = hierarchy_level
    );
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;$$

DROP FUNCTION IF EXISTS get_substring_in_position;$$

CREATE FUNCTION get_substring_in_position(
    str VARCHAR(255),
    delim VARCHAR(12),
    pos INT
)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    IF delim = '' THEN
        RETURN SUBSTRING(str, pos, 1);
    END IF;

    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos), CHAR_LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1), delim, '');
END;$$

DROP FUNCTION IF EXISTS format_seconds_to_time;$$

CREATE FUNCTION  format_seconds_to_time
(
    seconds INT,
    format VARCHAR(20)
)
RETURNS VARCHAR(50)
DETERMINISTIC
READS SQL DATA
BEGIN
    IF format IS NULL THEN
        RETURN seconds;
    END IF;

    IF INSTR(format, '%') > 0 THEN
      RETURN TIME_FORMAT(SEC_TO_TIME(seconds), format);
    END IF;

    SELECT CASE get_substring_in_position(format, '_', 1) WHEN 'h' THEN 3600 WHEN 'm' THEN 60 ELSE 1 END INTO @divider;
    SELECT get_substring_in_position(format, '_', 2) INTO @decimals;
    SELECT get_substring_in_position(format, '_', 3) INTO @suffix;

    IF @decimals = '' THEN
        SET @decimals := 2;
    END IF;

    RETURN CONCAT(ROUND(seconds/@divider, @decimals), @suffix);
END;$$

DELIMITER ;
