DELIMITER $$

DROP PROCEDURE IF EXISTS `find_leave_period_start_and_end_date`$$

CREATE PROCEDURE find_leave_period_start_and_end_date (IN empNumber INT, IN leaveTypeId INT, IN fromDate DATE, OUT startDate DATE, OUT endDate DATE)
BEGIN
    DECLARE leavePeriodType, duration, startMonth, startDay INT DEFAULT 0;
    DECLARE thisYear INT;
    DECLARE thisDate DATE;
    DECLARE ADate DATE;
    DECLARE DaysInMonth INT DEFAULT 0;

    IF fromDate = 0000-00-00 THEN
        SET thisDate = CURDATE();
    END IF;

    IF fromDate != 0000-00-00 THEN
        SET thisDate = fromDate;
    END IF;

    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 empNumber != -1 THEN
        IF leavePeriodType = 2 THEN
        SELECT month(joined_date), day(joined_date) INTO
                startMonth, startDay 
        FROM hs_hr_employee WHERE emp_number = empNumber;
        END IF;
    END IF;

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

    -- GET startDate of the leave period
    SET startDate = STR_TO_DATE(CONCAT(thisYear, '/', startMonth, '/', startDay), '%Y/%m/%d');

    IF startMonth = 1 THEN
        SET endDate = STR_TO_DATE(CONCAT(thisYear, '/', 12, '/', 31), '%Y/%m/%d');
    END IF;

    IF startMonth > 1 THEN
        SET endDate = STR_TO_DATE(CONCAT(thisYear+1, '/', startMonth-1, '/', startDay), '%Y/%m/%d');
    END IF;

    SET ADate = endDate;

    SET DaysInMonth = DATEDIFF(LAST_DAY(DATE_ADD(DATE_ADD(ADate, INTERVAL 1 MONTH), INTERVAL -1 DAY)), ADate);

    IF startMonth > 1 THEN
        SET endDate = STR_TO_DATE(CONCAT(thisYear+1, '/', startMonth-1, '/', DaysInMonth+1), '%Y/%m/%d');
    END IF;

    -- 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$$

DROP FUNCTION IF EXISTS `get_leave_period_start_date`$$

CREATE FUNCTION `get_leave_period_start_date`(empNumber INT, leaveTypeId INT, fromDate DATE)
RETURNS DATE
DETERMINISTIC
READS SQL DATA
BEGIN

	Declare startDate DATE;
        Declare endDate DATE;

	call find_leave_period_start_and_end_date(empNumber, leaveTypeId, fromDate, startDate, endDate);

	RETURN startDate;
END$$

DROP FUNCTION IF EXISTS `get_leave_period_end_date`$$

CREATE FUNCTION `get_leave_period_end_date`(empNumber INT, leaveTypeId INT, fromDate DATE)
RETURNS DATE
DETERMINISTIC
READS SQL DATA
BEGIN

	Declare startDate DATE;
        Declare endDate DATE;

	call find_leave_period_start_and_end_date(empNumber, leaveTypeId, fromDate, startDate, endDate);

	RETURN endDate;
END$$

DELIMITER ;