DROP PROCEDURE IF EXISTS `getLeavePeriod`;$$

CREATE PROCEDURE `getLeavePeriod`(IN empNumber INT, IN leaveTypeId INT, IN periodOffset INT, OUT fromDate DATE, OUT toDate DATE)
BEGIN

	Declare leavePeriodType, duration, startMonth, startDay, offset INT DEFAULT 0;
	Declare thisYear, prevYear INT;
	Declare thisDate DATE;
    Declare startDate DATE;
    Declare createdDate DATE;
    Declare nextFromDate 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, created_at
	INTO leavePeriodType, duration, startMonth, startDay, createdDate
	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, created_at INTO
            startMonth, startDay, createdDate
        FROM ohrm_leave_period_history ORDER by id DESC LIMIT 1;
	END IF;

	-- HIRE DATE BASED
	IF leavePeriodType = 2 THEN
        SELECT joined_date INTO startDate
        FROM hs_hr_employee WHERE emp_number = empNumber;
    ELSE
    -- CUSTOM OR DEFAULT - start date based on created date of record
        SET startDate = STR_TO_DATE(CONCAT(YEAR(createdDate), '/', startMonth, '/', startDay), '%Y/%m/%d');
        IF createdDate < startDate THEN
            SET startDate = DATE_SUB(startDate, INTERVAL 1 YEAR);
        END IF;
	END IF;

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

    -- Loop until we get current period
    SET fromDate = startDate;
    SET nextFromDate = DATE_ADD(fromDate, INTERVAL duration YEAR);
    WHILE nextFromDate <= thisDate DO
        SET fromDate = nextFromDate;
        SET nextFromDate = DATE_ADD(fromDate, INTERVAL duration YEAR);
    END WHILE;

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

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

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

END;