DROP FUNCTION IF EXISTS get_sub_unit_path;
DROP FUNCTION IF EXISTS get_sub_unit_accrued_employee_count;
DROP FUNCTION IF EXISTS get_sub_unit_at_hierarchy_by_level;
DROP FUNCTION IF EXISTS is_sub_unit_leaf_node;
DROP FUNCTION IF EXISTS get_parant_id;
DROP FUNCTION IF EXISTS get_sub_unit_title;
DROP FUNCTION IF EXISTS get_working_day_type;

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;



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;



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;



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;



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;



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;



CREATE FUNCTION  get_working_day_type
(
  dateId INT,
  operationalCountryId INT
)
RETURNS VARCHAR(20)
DETERMINISTIC
READS SQL DATA
BEGIN
  Declare dateString VARCHAR(20);
  Declare workingDayTypeId INT;
  Declare workingDayType VARCHAR(20);
  Declare isDefaultWorkingDay INT;
  IF dateId IS NULL OR dateId < 1 OR dateId > 7
    THEN
     RETURN '';
  END IF;

  IF operationalCountryId IS NULL THEN
      SELECT (CASE dateId
      WHEN 1 THEN (SELECT mon FROM `ohrm_work_week` WHERE operational_country_id IS NULL)
      WHEN 2 THEN (SELECT tue FROM `ohrm_work_week` WHERE operational_country_id IS NULL)
      WHEN 3 THEN (SELECT wed FROM `ohrm_work_week` WHERE operational_country_id IS NULL)
      WHEN 4 THEN (SELECT thu FROM `ohrm_work_week` WHERE operational_country_id IS NULL)
      WHEN 5 THEN (SELECT fri FROM `ohrm_work_week` WHERE operational_country_id IS NULL)
      WHEN 6 THEN (SELECT sat FROM `ohrm_work_week` WHERE operational_country_id IS NULL)
      WHEN 7 THEN (SELECT sun FROM `ohrm_work_week` WHERE operational_country_id IS NULL)
     END) INTO @workingDayTypeId;
  ELSE
      SELECT (CASE dateId
      WHEN 1 THEN (SELECT mon FROM `ohrm_work_week` WHERE operational_country_id = operationalCountryId)
      WHEN 2 THEN (SELECT tue FROM `ohrm_work_week` WHERE operational_country_id = operationalCountryId)
      WHEN 3 THEN (SELECT wed FROM `ohrm_work_week` WHERE operational_country_id = operationalCountryId)
      WHEN 4 THEN (SELECT thu FROM `ohrm_work_week` WHERE operational_country_id = operationalCountryId)
      WHEN 5 THEN (SELECT fri FROM `ohrm_work_week` WHERE operational_country_id = operationalCountryId)
      WHEN 6 THEN (SELECT sat FROM `ohrm_work_week` WHERE operational_country_id = operationalCountryId)
      WHEN 7 THEN (SELECT sun FROM `ohrm_work_week` WHERE operational_country_id = operationalCountryId)
     END) INTO @workingDayTypeId;
  END IF;

  SELECT (CASE @workingDayTypeId
    WHEN 0 THEN 'working day'
    WHEN 8 THEN 'non-working day'
    WHEN 4 THEN 'half day'
    ELSE NULL
    END) INTO @workingDayType;

  IF @workingDayType IS NOT NULL
    THEN
     RETURN @workingDayType;
  END IF;

  SELECT (CASE dateId
    WHEN 1 THEN '%monday%'
    WHEN 2 THEN '%tuesday%'
    WHEN 3 THEN '%wednesday%'
    WHEN 4 THEN '%thursday%'
    WHEN 5 THEN '%friday%'
    WHEN 6 THEN '%saturday%'
    WHEN 7 THEN '%sunday%'
   END) INTO @dateString;

   (SELECT COUNT(*) INTO @isDefaultWorkingDay
     FROM `ohrm_config` AS config
     WHERE `config`.`value` LIKE @dateString AND `config`.`property` = 'pim.work_week_default_working_days'
     LIMIT 1);

  IF @isDefaultWorkingDay IS NOT NULL
    THEN
     RETURN 'working day';
  END IF;
  RETURN 'non-working day';

END;

