DELIMITER $$

DROP PROCEDURE IF EXISTS `getCurrentLeavePeriod`$$

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

	Declare leavePeriodType, duration, startMonth, startDay 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_month 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;

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

END$$

DROP FUNCTION IF EXISTS `withinCurrentLeavePeriod`$$

CREATE FUNCTION `withinCurrentLeavePeriod`(dateValue DATE, empNumber INT, leaveTypeId INT)
RETURNS TINYINT(1)
DETERMINISTIC
READS SQL DATA
BEGIN

	DECLARE withinPeriod TINYINT(1);
	Declare startDate, endDate DATE;

	call getCurrentLeavePeriod(empNumber, leaveTypeId, startDate, endDate);

	-- check if within period
	IF dateValue >= startDate AND dateValue <= endDate THEN
		SET withinPeriod = TRUE;
	ELSE 
		SET withinPeriod = FALSE;
	END IF;

	RETURN withinPeriod;
END$$

DROP FUNCTION IF EXISTS `overlapsWithCurrentLeavePeriod`$$

CREATE FUNCTION `overlapsWithCurrentLeavePeriod`(fromDate DATE, toDate DATE, empNumber INT, leaveTypeId INT)
RETURNS TINYINT(1)
DETERMINISTIC
READS SQL DATA
BEGIN

	DECLARE overlaps TINYINT(1);
	Declare startDate, endDate DATE;

	call getCurrentLeavePeriod(empNumber, leaveTypeId, startDate, endDate);

	-- check if within period
	IF (fromDate <= startDate AND toDate >= startDate) OR (fromDate <= endDate AND toDate >= endDate) OR (fromDate >= startDate AND toDate <= endDate) THEN
		SET overlaps = TRUE;
	ELSE 
		SET overlaps = FALSE;
	END IF;

	RETURN overlaps;
END$$
DELIMITER ;

-- GRANT EXECUTE ON FUNCTION advancedleave.getCurrentLeavePeriod to 'advancedleave'@'localhost';
-- GRANT EXECUTE ON FUNCTION advancedleave.withinCurrentLeavePeriod to 'advancedleave'@'localhost';
-- GRANT EXECUTE ON FUNCTION advancedleave.overlapsWithCurrentLeavePeriod to 'advancedleave'@'localhost';
