DROP PROCEDURE IF EXISTS `getLeavePeriod`;$$

CREATE PROCEDURE `getLeavePeriod`(IN empNumber INT, IN leaveTypeId INT, IN periodOffset INT, OUT startDate DATE, OUT endDate DATE)
BEGIN

	Declare leavePeriodType, duration, startMonth, startDay, offset INT DEFAULT 0;
	Declare thisYear, prevYear INT;
	Declare thisDate DATE;

	set thisDate = CURDATE();
	set thisYear = YEAR(thisDate);

	-- Get start month and day, duration
	SELECT leave_period_type, IF(leave_period_type = 1, 1, leave_period_duration), start_month, start_day
	INTO leavePeriodType, duration, startMonth, startDay
	FROM ohrm_leave_type_leave_period WHERE leave_type_id = leaveTypeId;

	-- DEFAULT
	IF leavePeriodType = 1 THEN
	SELECT leave_period_start_month, leave_period_start_day INTO
		startMonth, startDay
	FROM ohrm_leave_period_history ORDER by id DESC LIMIT 1;
	END IF;

	-- HIRE DATE BASED
	IF leavePeriodType = 2 THEN
	SELECT month(joined_date), day(joined_date) INTO
		startMonth, startDay
	FROM hs_hr_employee WHERE emp_number = empNumber;
	END IF;

	-- TODO: Check for start date/month not defined (eg: no joined date)

	-- GET startMonth, startDay in this year
	set startDate = STR_TO_DATE(CONCAT(thisYear, '/', startMonth, '/', startDay), '%Y/%m/%d');

	-- IF start date is after the current date, use previous years start date
	IF startDate > thisDate THEN
		set startDate = DATE_SUB(startDate, INTERVAL duration YEAR);
	END IF;

  -- Apply period offset
  SET offset = ABS(duration * periodOffset);

  IF periodOffset <  0 THEN
    set startDate = DATE_SUB(startDate, INTERVAL offset YEAR);
  ELSEIF periodOffset > 0 THEN
    set startDate = DATE_ADD(startDate, INTERVAL offset YEAR);
  END IF;

	-- end date
	SET endDate = DATE_ADD(startDate, INTERVAL duration YEAR);
	SET endDate = DATE_SUB(endDate, INTERVAL 1 DAY);

END;
