DROP PROCEDURE IF EXISTS adjust_visit_dates;

CREATE PROCEDURE adjust_visit_dates()
  BEGIN
    DECLARE encounters_after_visit_stop_date CURSOR FOR SELECT
                                                          v.visit_id                AS visit_id,
                                                          max(e.encounter_datetime) AS updated_visit_date_stopped
                                                        FROM encounter e
                                                          JOIN visit v ON v.visit_id = e.visit_id
                                                        WHERE v.date_stopped < e.encounter_datetime
                                                        GROUP BY v.visit_id
                                                        ORDER BY v.visit_id;

    DECLARE encounters_before_visit_start_date CURSOR FOR SELECT
                                                          v.visit_id                AS visit_id,
                                                          min(e.encounter_datetime) AS updated_visit_date_stopped
                                                        FROM encounter e
                                                          JOIN visit v ON v.visit_id = e.visit_id
                                                        WHERE v.date_started > e.encounter_datetime
                                                        GROUP BY v.visit_id
                                                        ORDER BY v.visit_id;

    OPEN encounters_after_visit_stop_date;
    BEGIN
      DECLARE done INT DEFAULT FALSE;
      DECLARE visit_id_to_be_updated INT;
      DECLARE updated_visit_date_stopped DATETIME;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

      read_loop: LOOP
        FETCH encounters_after_visit_stop_date INTO visit_id_to_be_updated, updated_visit_date_stopped;
        IF done
        THEN
          LEAVE read_loop;
        END IF;
        UPDATE visit AS v SET v.date_stopped = updated_visit_date_stopped WHERE v.visit_id = visit_id_to_be_updated;
      END LOOP;
    END;

    OPEN encounters_before_visit_start_date;
    BEGIN
      DECLARE done INT DEFAULT FALSE;
      DECLARE visit_id_to_be_updated INT;
      DECLARE updated_visit_date_stopped DATETIME;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

      read_loop: LOOP
        FETCH encounters_before_visit_start_date INTO visit_id_to_be_updated, updated_visit_date_stopped;
        IF done
        THEN
          LEAVE read_loop;
        END IF;
        UPDATE visit AS v SET v.date_started = updated_visit_date_stopped WHERE v.visit_id = visit_id_to_be_updated;
      END LOOP;
    END;

  END;


CALL adjust_visit_dates();