DELIMITER $$

DROP PROCEDURE IF EXISTS `getCurrentLeavePeriod`;$$

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

  Declare periodOffset INT DEFAULT 0;

  call getLeavePeriod(empNumber, leaveTypeId, periodOffset, startDate, endDate);
END;$$

DROP PROCEDURE IF EXISTS `getLeavePeriod`;$$

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

	Declare leavePeriodType, duration, startMonth, startDay, offsetValue INT DEFAULT 0;
	Declare thisYear, prevYear INT;
	Declare thisDate DATE;
    Declare startDate DATE;
    Declare createdDate DATE;
    Declare nextFromDate DATE;
    Declare isStartDayOnLeapDay boolean DEFAULT false;
    
    IF @enableCachingGetLeavePeriodValue IS NOT NULL AND @enableCachingGetLeavePeriodValue = 1 AND @cachedGetLeavePeriodFromDate <> '' AND @cachedGetLeavePeriodToDate <> '' THEN 
        SET fromDate = @cachedGetLeavePeriodFromDate; 
        SET toDate = @cachedGetLeavePeriodToDate; 
        LEAVE proc_get_leave_period_label;
    END IF;
    
	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 duration YEAR);
        END IF;
	END IF;

    IF leavePeriodType = 2 AND month(startDate) = 2 AND day(startDate) = 29 THEN
        SET isStartDayOnLeapDay = true;
    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);
    IF isStartDayOnLeapDay AND (month(nextFromDate) = 2 AND day(nextFromDate) = 28) THEN
        SET nextFromDate = DATE(CONCAT(YEAR(nextFromDate),'-03-01'));
    END IF;
    WHILE nextFromDate <= thisDate DO
        SET fromDate = nextFromDate;
        SET nextFromDate = DATE_ADD(fromDate, INTERVAL duration YEAR);
        IF isStartDayOnLeapDay AND (month(nextFromDate) = 2 AND day(nextFromDate) = 28) THEN
            SET nextFromDate = DATE(CONCAT(YEAR(nextFromDate),'-03-01'));
        END IF;
    END WHILE;

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

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

	-- end date
  SET toDate = DATE_ADD(fromDate, INTERVAL duration YEAR);
  
  IF isStartDayOnLeapDay THEN
    IF YEAR(fromDate) = YEAR(startDate) THEN
        SET fromDate = startDate;
    ELSE
        SET fromDate = DATE(CONCAT(YEAR(fromDate),'-03-01'));
    END IF;
    IF DATE(CONCAT(YEAR(toDate),'-02-29')) IS NOT NULL AND YEAR(toDate) <> YEAR(startDate) THEN
        SET toDate = DATE(CONCAT(YEAR(toDate),'-02-29'));
    ELSE
        SET toDate = DATE(CONCAT(YEAR(toDate),'-02-28'));
    END IF;
  ELSE 
    SET toDate = DATE_SUB(toDate, INTERVAL 1 DAY);
  END IF;

  IF @enableCachingGetLeavePeriodValue IS NOT NULL AND @enableCachingGetLeavePeriodValue = 1 THEN 
      SET @cachedGetLeavePeriodFromDate = fromDate; 
      SET @cachedGetLeavePeriodToDate = toDate; 
  END IF;
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 periodOffset INT DEFAULT 0;

	SET withinPeriod = withinLeavePeriod(dateValue, empNumber, leaveTypeId, periodOffset);

	RETURN withinPeriod;
END;$$

DROP FUNCTION IF EXISTS `withinLeavePeriod`;$$

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

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

	call getLeavePeriod(empNumber, leaveTypeId, periodOffset, 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 periodOffset INT DEFAULT 0;

  SET overlaps = overlapsWithLeavePeriod(fromDate, toDate, empNumber, leaveTypeId, periodOffset);

	RETURN overlaps;
END;$$

DROP FUNCTION IF EXISTS `overlapsWithLeavePeriod`;$$

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

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

	call getLeavePeriod(empNumber, leaveTypeId, periodOffset, 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;$$

DROP FUNCTION IF EXISTS `validOnAsOfDateForLeavePeriod`;$$

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

	DECLARE valid TINYINT(1);
	Declare startDate, endDate, asOfDate DATE;

	call getLeavePeriod(empNumber, leaveTypeId, periodOffset, startDate, endDate);

  -- Look at start date if period before current. Look at end date if period after current
  IF periodOffset < 0 THEN
    set asOfDate = endDate;
  ELSEIF periodOffset > 0 THEN
    set asOfDate = startDate;
  ELSE
    set asOfDate = CURDATE();
  END IF;

  -- INSERT INTO debug(message) values(CONCAT('E FromDate:', CAST(fromDate AS CHAR), ',E ToDate:', CAST(toDate AS CHAR), ',LP Start:', CAST(startDate AS CHAR), ',LP End:', CAST(endDate AS CHAR), ',Offset:', CAST(periodOffset AS CHAR), ',asOfDate:', CAST(asOfDate AS CHAR)));

	-- check if within period
	IF (asOfDate BETWEEN fromDate AND toDate) THEN
		SET valid = TRUE;
	ELSE
		SET valid = FALSE;
	END IF;

	RETURN valid;
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';
