DROP FUNCTION IF EXISTS wc_incident_lost_days;

/* Takes an incident to do the calculation for and the lost time type to check */
CREATE DEFINER='root'@'localhost' FUNCTION wc_incident_lost_days(in_inc_id INT(10) UNSIGNED, in_type INT)
RETURNS INT
CONTAINS SQL
READS SQL DATA
SQL SECURITY INVOKER
COMMENT 'Function to calculate number of lost days for an incident'
BEGIN
    DECLARE done, current_type, record_type INT DEFAULT 0;
    DECLARE current_start_date, current_end_date, incident_date DATE DEFAULT NULL;
    DECLARE overlap_end_date, overlap_start_date DATE DEFAULT NULL;
    DECLARE range_id, overlap_count, exists_count, lost_days, restricted_days INT DEFAULT 0;
    DECLARE lost_time_type_id VARCHAR(100) DEFAULT '1';
    DECLARE restricted_days_type_id VARCHAR(100) DEFAULT '2';
    DECLARE lost_days_cursor CURSOR FOR 
        (SELECT a.accom_type AS lt_type, DATE(`start_date`) AS `start_datetime`, DATE(`end_date`) AS `end_datetime`, DATE(`i`.`inc_datetime`)  AS `inc_datetime`
            FROM accommodations AS a
            LEFT JOIN incident_links AS il ON il.link_id=a.accom_id AND il.module=16
            LEFT JOIN incidents AS i ON i.inc_id=il.inc_id
            WHERE `il`.`inc_id`=in_inc_id AND `a`.`status`=1
            )
        UNION
        (SELECT 0 AS lt_type, DATE(`start_date`) AS `start_datetime`, DATE(`end_date`) AS `end_datetime`, DATE(`i`.`inc_datetime`)  AS `inc_datetime`
            FROM patient_clinical_restriction AS pcr
            LEFT JOIN incident_links AS il ON il.link_id=pcr.cr_id AND il.module=9
            LEFT JOIN incidents AS i ON i.inc_id=il.inc_id
            WHERE `il`.`inc_id`=in_inc_id AND `pcr`.`active`=1 AND `pcr`.`affect_work`=1
            )
        ORDER BY `start_datetime` ASC;

    -- When the cursor has iterated through all the results it will set done to 1
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = 1;

    SET @days_away = (SELECT `days_away` FROM incidents WHERE `inc_id`=in_inc_id);
    SET @days_restricted = (SELECT `days_restricted` FROM incidents WHERE `inc_id`=in_inc_id);
    SET @case_type = (SELECT `case_type` FROM incidents WHERE `inc_id`=in_inc_id);
    -- Only recalculate lost days if something has changed for this incident
    IF (@days_away = -1 OR @days_restricted = -1) THEN
       SELECT COALESCE(
           (SELECT IF(LENGTH(`system_settings`.`value`) = 0, 1, `system_settings`.`value`)
               FROM `system_settings`
               WHERE `system_settings`.`module`='I&I' AND `system_settings`.`section`='Clinical Restriction'
               AND `system_settings`.`item`='Lost Time Type'), 1) INTO lost_time_type_id;

       -- Make a temporary table to store all the date ranges
       CREATE TEMPORARY TABLE IF NOT EXISTS `lost_time_condensed_ranges`
       (`lt_range_id` int(10) unsigned auto_increment PRIMARY KEY,
        `inc_id` int(10),
        `type` int(10),
        `start_date` date,
        `end_date` date,
        INDEX date_range(`start_date`, `end_date`),
        INDEX inc_id(`inc_id`,`type`));

       -- Check if the incident has already been processed, we can skip the loop if it already has
       SELECT count(*) INTO exists_count
       FROM lost_time_condensed_ranges AS lcrt
       WHERE `lcrt`.`inc_id`=in_inc_id;

       IF exists_count=0
       THEN
           OPEN lost_days_cursor; -- Execute the query
           lost_time_loop: LOOP
               FETCH lost_days_cursor INTO record_type, current_start_date, current_end_date, incident_date;

               IF done THEN
                   LEAVE lost_time_loop;
               END IF;

               IF record_type = 0 THEN
                   SET current_type := 2;
               ELSEIF find_in_set(record_type, lost_time_type_id) THEN
                   SET current_type := 1;
               ELSE
                   ITERATE lost_time_loop;
               END IF;

               IF current_end_date = '0000-00-00' THEN
                   SET current_end_date := DATE(NOW());
               END IF;

               -- The date of the injury is not counted in the lost or restricted days
               IF current_start_date=incident_date
               THEN
                   SET current_start_date := DATE_ADD(current_start_date, INTERVAL 1 DAY);
               END IF;

               -- Look in the temporary table for any ranges that overlap
               SELECT `lt_range_id`, `end_date`, count(*) INTO range_id, overlap_end_date, overlap_count
               FROM lost_time_condensed_ranges AS lcrt
               WHERE `lcrt`.`inc_id`=in_inc_id 
               AND `lcrt`.`type`=current_type
               AND `lcrt`.`start_date` <= current_end_date
               AND `lcrt`.`end_date` >= current_start_date;

               IF overlap_count > 0
               THEN
                   -- Only update the end date if it's after the existing ranges end date
                   -- This could happen if the range is completely contained in the current range
                   IF current_end_date > overlap_end_date
                   THEN
                       UPDATE lost_time_condensed_ranges AS lcrt
                       SET `lcrt`.`end_date`=current_end_date
                       WHERE `lt_range_id`=range_id;
                   END IF;
               ELSE
                   -- Otherwise insert it
                   INSERT INTO lost_time_condensed_ranges
                   SET `inc_id`=in_inc_id, `type`=current_type, `start_date`=current_start_date, `end_date`=current_end_date;
                   SET range_id=last_insert_id();
               END IF;

               -- Now look for any lost time records that overlap with the current restricted days record
               IF current_type = 2
               THEN
                   SELECT `start_date`, `end_date`, count(*) INTO overlap_start_date, overlap_end_date, overlap_count
                   FROM lost_time_condensed_ranges AS lcrt
                   WHERE `lcrt`.`inc_id`=in_inc_id
                   AND `lcrt`.`type` = 1
                   AND `lcrt`.`start_date` <= current_end_date
                   AND `lcrt`.`end_date` >= current_start_date;
               -- or any restricted days records that overlap with the lost time records
               ELSEIF current_type = 1
               THEN
                   SELECT `lt_range_id`, `start_date`, `end_date`, count(*) INTO range_id, overlap_start_date, overlap_end_date, overlap_count
                   FROM lost_time_condensed_ranges AS lcrt
                   WHERE `lcrt`.`inc_id`=in_inc_id
                   AND `type` = 2
                   AND `lcrt`.`start_date` <= current_end_date
                   AND `lcrt`.`end_date` >= current_start_date;
               END IF;

               IF overlap_count > 0
               THEN
                   -- If the restricted days range is completely inside the lost days record, delete it
                   IF (current_type = 2 AND current_start_date >= overlap_start_date AND current_end_date <= overlap_end_date)
                       OR (current_type = 1 AND overlap_start_date >= current_start_date AND overlap_end_date <= current_end_date)
                   THEN
                       DELETE FROM lost_time_condensed_ranges
                       WHERE `lt_range_id`=range_id;
                   ELSE
                       -- If our current type is a restricted days record, then we need to move the dates in relation to the overlap records dates
                       IF (current_type = 2 AND current_end_date >= overlap_start_date AND current_start_date < overlap_start_date)
                       THEN
                           UPDATE lost_time_condensed_ranges AS lcrt
                           SET `lcrt`.`end_date`=DATE_SUB(overlap_start_date, INTERVAL 1 DAY)
                           WHERE `lcrt`.`lt_range_id`=range_id;
                       ELSEIF (current_type = 2 AND current_start_date <= overlap_end_date AND current_end_date > overlap_end_date)
                       THEN
                           UPDATE lost_time_condensed_ranges AS lcrt
                           SET `lcrt`.`start_date`=DATE_ADD(overlap_end_date, INTERVAL 1 DAY)
                           WHERE `lcrt`.`lt_range_id`=range_id;
                       -- If the current type is a lost days record, then we need to move the dates in relation to the current records dates
                       ELSEIF (current_type = 1 AND overlap_start_date <= current_end_date AND overlap_start_date >= current_start_date)
                       THEN
                           UPDATE lost_time_condensed_ranges AS lcrt
                           SET `lcrt`.`start_date`=DATE_ADD(current_end_date, INTERVAL 1 DAY)
                           WHERE `lcrt`.`lt_range_id`=range_id;
                       ELSEIF (current_type = 1 AND overlap_end_date >= current_start_date AND overlap_end_date <= current_end_date)
                       THEN
                           UPDATE lost_time_condensed_ranges AS lcrt
                           SET `lcrt`.`end_date`=DATE_SUB(current_start_date, INTERVAL 1 DAY)
                           WHERE `lcrt`.`lt_range_id`=range_id;
                       END IF;
                   END IF;
               END IF;

           END LOOP;

           CLOSE lost_days_cursor;
       END IF;

       -- Return the number of days
       SELECT COALESCE(SUM((TO_DAYS(IF(`end_date` > DATE(NOW()), DATE(NOW()), `end_date`)) + 1) - TO_DAYS(`start_date`)), 0) INTO lost_days
       FROM lost_time_condensed_ranges AS lcrt
       WHERE `inc_id`=in_inc_id AND `type` = 1 AND `start_date` <= DATE(NOW());

       SELECT COALESCE(SUM((TO_DAYS(IF(`end_date` > DATE(NOW()), DATE(NOW()), `end_date`)) + 1) - TO_DAYS(`start_date`)), 0) INTO restricted_days
       FROM lost_time_condensed_ranges AS lcrt
       WHERE `inc_id`=in_inc_id AND `type` = 2 AND `start_date` <= DATE(NOW());

       IF @case_type IN ('Injury', 'Injury NO')
       THEN
           SET @days_away = IF(lost_days > 180, 180, lost_days);
           SET @days_restricted = IF(lost_days + restricted_days > 180, IF(lost_days > 180, 0, 180 - lost_days), restricted_days);
       ELSE
           SET @days_away = lost_days;
           SET @days_restricted = restricted_days;
       END IF;
       -- Only update days_away and days_restricted if all restriction and accommodation end dates are in the past
       SET @max_end_date = (SELECT MAX(`lcrt`.`end_date`)
               FROM lost_time_condensed_ranges AS lcrt
               WHERE `lcrt`.`inc_id`=in_inc_id
               AND `lcrt`.`type`=current_type);
       UPDATE incidents i
       INNER JOIN lost_time_condensed_ranges lt ON `i`.`inc_id`=`lt`.`inc_id`
       SET `i`.`days_away`=@days_away, 
          `i`.`days_restricted`=@days_restricted
       WHERE `i`.`inc_id`=in_inc_id
       AND @max_end_date < DATE(NOW());
    END IF;

    IF in_type = 1
    THEN
        RETURN @days_away;
    ELSE
        RETURN @days_restricted;
    END IF;
END;
