DELIMITER $$

DROP FUNCTION IF EXISTS get_creditable_service_period;$$

CREATE FUNCTION  get_creditable_service_period
  (
    id INT,
    today DATE,
    onlyCurrentService BOOLEAN,
    format VARCHAR(20)
  )
  RETURNS VARCHAR (50)
  DETERMINISTIC
  READS SQL DATA
  BLOCK0: BEGIN
    DECLARE durationInDays INT;
    DECLARE daysPerMonth FLOAT;
    DECLARE daysPerYear FLOAT;

    SET daysPerMonth = 30.436875;
    SET daysPerYear = 365;
    SET durationInDays = 0;

    IF id IS NULL
    THEN
      RETURN NULL;
    END IF;

    CREATE TEMPORARY TABLE IF NOT EXISTS `ohrm_temp_date_range` (start_date DATE, end_date DATE);
    CREATE TEMPORARY TABLE IF NOT EXISTS `ohrm_temp_indexed_date_range` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, start_date DATE, end_date DATE);

    IF today IS NULL
    THEN
        SELECT CURDATE() INTO today;
    END IF;

    CALL prepare_date_ranges(id, today, onlyCurrentService);

    BLOCK1: BEGIN
        DECLARE exit_loop BOOLEAN;
        DECLARE startDate DATE;
        DECLARE endDate DATE;
        DECLARE all_ranges_cursor CURSOR FOR
            SELECT start_date, end_date FROM ohrm_temp_date_range ORDER BY start_date ASC;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;

        OPEN all_ranges_cursor;
        get_all_ranges_loop: LOOP
            FETCH all_ranges_cursor INTO startDate, endDate;
            IF exit_loop THEN
                LEAVE get_all_ranges_loop;
            END IF;
            SET durationInDays = durationInDays + DATEDIFF(endDate, DATE_SUB(startDate, INTERVAL 1 DAY));
        END LOOP get_all_ranges_loop;
        CLOSE all_ranges_cursor;
    END BLOCK1;
    DELETE FROM ohrm_temp_date_range;

    DROP TEMPORARY TABLE IF EXISTS ohrm_temp_date_range;
    DROP TEMPORARY TABLE IF EXISTS ohrm_temp_indexed_date_range;

    IF durationInDays > 0 THEN
        SET @allMonths = FLOOR(durationInDays/daysPerMonth);
        SET @years = FLOOR(@allMonths/12);
        SET @months = MOD(@allMonths, 12);
        SET @days = ROUND(MOD(durationInDays, daysPerMonth));
        RETURN CASE format
            WHEN 's' THEN durationInDays * 86400
            WHEN 'm' THEN ROUND(durationInDays / daysPerMonth, 1)
            WHEN 'y' THEN ROUND(durationInDays / daysPerYear, 1)
            ELSE CONCAT(@years, '_', @months, '_', @days)
        END;
    END IF;

    RETURN CASE format
       WHEN 's' THEN durationInDays
       WHEN 'm' THEN durationInDays
       WHEN 'y' THEN durationInDays
       ELSE '0_0_0'
    END;
  END BLOCK0;$$

DROP PROCEDURE IF EXISTS prepare_date_ranges;$$

CREATE PROCEDURE  prepare_date_ranges
  (
    IN id INT,
    IN today DATE,
    IN onlyCurrentService BOOLEAN
  )
  BLOCK0: BEGIN
    DECLARE joinedDate DATE;
    DECLARE startDate DATE;
    DECLARE endDate DATE;
    DECLARE isTerminated TINYINT(1);

    DECLARE terminations_cursor CURSOR FOR
        SELECT termination_date, reactivation_date FROM ohrm_emp_termination WHERE emp_number = id ORDER BY termination_date ASC;

    SELECT joined_date INTO joinedDate
    FROM ohrm_job_record_history
    WHERE employee_number = id AND joined_date IS NOT NULL AND joined_date != '' AND effective_date IS NOT NULL AND effective_date != ''
    ORDER BY effective_date , id LIMIT 1;

    IF joinedDate IS NOT NULL AND joinedDate <= today THEN

        OPEN terminations_cursor;
        BLOCK1: BEGIN
            DECLARE exit_loop BOOLEAN;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
            SET isTerminated = 0;
            get_terminations_loop: LOOP
                FETCH terminations_cursor INTO startDate, endDate;
                IF exit_loop THEN
                    LEAVE get_terminations_loop;
                END IF;

                IF isTerminated = 0 AND (endDate IS NULL OR endDate = '' OR endDate > today) THEN
                    SET isTerminated = 1;
                END IF;

                IF isTerminated = 1 THEN
                    SET endDate = DATE_ADD(today, INTERVAL 1 DAY);
                END IF;

                INSERT INTO ohrm_temp_date_range (start_date, end_date) VALUES (startDate, endDate);
            END LOOP get_terminations_loop;
        END BLOCK1;
        CLOSE terminations_cursor;

        CALL sort_date_ranges();

        BLOCK2: BEGIN
            DECLARE exit_loop2 BOOLEAN;
            DECLARE startDate2 DATE;
            DECLARE endDate2 DATE;
            DECLARE dates_cursor CURSOR FOR
                SELECT start_date, end_date FROM ohrm_temp_date_range ORDER BY start_date ASC;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop2 = TRUE;

            DELETE FROM ohrm_temp_indexed_date_range;
            SET startDate = joinedDate;
            OPEN dates_cursor;
            get_dates_loop: LOOP
                FETCH dates_cursor INTO startDate2, endDate2;
                IF exit_loop2 OR startDate > today THEN
                    LEAVE get_dates_loop;
                END IF;
                SELECT LEAST(startDate2, today) INTO endDate;
                IF endDate >= startDate THEN
                    INSERT INTO ohrm_temp_indexed_date_range (start_date, end_date) VALUES (startDate, endDate);
                END IF;
                SET startDate = endDate2;
            END LOOP get_dates_loop;
            CLOSE dates_cursor;

            IF startDate <= today THEN
                INSERT INTO ohrm_temp_indexed_date_range (start_date, end_date) VALUES (startDate, today);
            END IF;
        END BLOCK2;
    END IF;

    DELETE FROM ohrm_temp_date_range;
    INSERT INTO ohrm_temp_date_range (start_date, end_date)
        SELECT start_date, end_date FROM ohrm_temp_indexed_date_range;
    DELETE FROM ohrm_temp_indexed_date_range;

    IF !onlyCurrentService THEN
        BLOCK3: BEGIN
            DECLARE exit_loop3 BOOLEAN;
            DECLARE startDate3 DATE;
            DECLARE endDate3 DATE;
            DECLARE experience_cursor CURSOR FOR
                SELECT eexp_from_date, eexp_to_date FROM hs_hr_emp_work_experience
                    WHERE emp_number = id AND eexp_creditable = 1 AND eexp_from_date IS NOT NULL AND eexp_to_date IS NOT NULL
                    AND eexp_from_date != '0000-00-00 00:00:00' AND eexp_to_date != '0000-00-00 00:00:00'
                    ORDER BY eexp_from_date, eexp_to_date ASC;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop3 = TRUE;

            OPEN experience_cursor;
            get_experiences_loop: LOOP
                FETCH experience_cursor INTO startDate3, endDate3;
                IF exit_loop3 THEN
                    LEAVE get_experiences_loop;
                END IF;

                INSERT INTO ohrm_temp_date_range (start_date, end_date) VALUES (startDate3, endDate3);
            END LOOP get_experiences_loop;
            CLOSE experience_cursor;
        END BLOCK3;
    END IF;
    CALL sort_date_ranges();
  END BLOCK0;$$

DROP PROCEDURE IF EXISTS sort_date_ranges;$$

CREATE PROCEDURE  sort_date_ranges
  (
  )
  BLOCK0: BEGIN
    DECLARE startDate DATE;
    DECLARE endDate DATE;
    DECLARE previousEndDate DATE;
    DECLARE previousId INT;
    BLOCK1: BEGIN
        DECLARE exit_start_loop BOOLEAN;
        DECLARE start_cursor CURSOR FOR
            SELECT DISTINCT start_date, end_date FROM ohrm_temp_date_range ORDER BY start_date, end_date ASC;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_start_loop = TRUE;
        SET previousId = NULL;
        OPEN start_cursor;
        get_start_dates_loop: LOOP
            FETCH start_cursor INTO startDate, endDate;
            IF exit_start_loop THEN
                LEAVE get_start_dates_loop;
            END IF;
            IF startDate <= endDate THEN
                IF previousId IS NULL OR startDate > previousEndDate THEN
                    INSERT INTO ohrm_temp_indexed_date_range (start_date, end_date) VALUES (startDate, endDate);
                    SET previousId = (SELECT LAST_INSERT_ID());
                ELSEIF endDate > previousEndDate THEN
                    UPDATE ohrm_temp_indexed_date_range SET end_date = endDate WHERE id = previousId;
                END IF;
                SET previousEndDate = endDate;
            END IF;

        END LOOP get_start_dates_loop;
        CLOSE start_cursor;
    END BLOCK1;

    DELETE FROM ohrm_temp_date_range;
    INSERT INTO ohrm_temp_date_range (start_date, end_date) SELECT  start_date, end_date FROM ohrm_temp_indexed_date_range;
    DELETE FROM ohrm_temp_indexed_date_range;
  END BLOCK0;$$
DELIMITER ;
