BEGIN
    DECLARE t_site char(2);
    DECLARE t_project varchar(7);
    DECLARE t_app_server varchar(30);
    DECLARE t_instance varchar(20);

    DECLARE last_status varchar(10);
    DECLARE last_status_time datetime;

    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR
        SELECT DISTINCT site, project, app_server, instance 
        FROM complete_db_info 
        WHERE site = local_site
        GROUP BY site, project, app_server, instance;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO t_site, t_project, t_app_server, t_instance;

        IF done THEN
            LEAVE read_loop;
        END IF;

	set last_status = (SELECT status FROM app_to_db_status_report
			 WHERE site = local_site
                         AND project = t_project
                         AND app_server = t_app_server
                         AND instance = t_instance
			 ORDER BY to_time DESC
			 LIMIT 1);

	IF (last_status = 'MISSING') THEN
		
		SET last_status_time = (SELECT MAX(from_time) FROM app_to_db_status_report
	  		 WHERE site = local_site 
                         AND project = t_project
                         AND app_server = t_app_server
                         AND instance = t_instance);
	ELSE
		SET last_status_time = (SELECT MAX(to_time) FROM app_to_db_status_report
                         WHERE site = local_site
                         AND project = t_project
                         AND app_server = t_app_server
                         AND instance = t_instance);
	END IF;

	IF (last_status_time IS NOT NULL) THEN 
		INSERT INTO tmp_app_to_db_status_report
        	(site, project, app_server, instance, status, check_time)
		SELECT site, project, app_server, instance, status, check_time
        	FROM app_to_db_status_history
        	WHERE site = local_site
        	AND project = t_project
        	AND app_server = t_app_server
        	AND instance = t_instance
        	AND check_time >= DATE_FORMAT(last_status_time, '%Y-%m-%d %H:%i');
	ELSE
		
		INSERT INTO tmp_app_to_db_status_report
                (site, project, app_server, instance, status, check_time)
                SELECT site, project, app_server, instance, status, check_time
                FROM app_to_db_status_history
                WHERE site = local_site
                AND project = t_project
                AND app_server = t_app_server
                AND instance = t_instance
		AND check_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);
	END IF;

    END LOOP;

    CLOSE cur;

    
    DELETE FROM tmp_app_to_db_status_report_null
    WHERE site = local_site;

    INSERT INTO tmp_app_to_db_status_report_null (
    SELECT * FROM tmp_app_to_db_status_report a 
    WHERE a.status = ''
    AND a.site = local_site
    AND EXISTS (SELECT b.status FROM tmp_app_to_db_status_report b 
	        WHERE a.site = b.site 
	        AND a.project = b.project 
		AND a.app_server = b.app_server 
		AND a.instance = b.instance 
		AND b.status != ''
		AND DATE_FORMAT(a.check_time, '%Y-%m-%d %H:%i') = DATE_FORMAT(b.check_time, '%Y-%m-%d %H:%i')
		)
    );

    DELETE FROM tmp_app_to_db_status_report 
    WHERE EXISTS (SELECT 1 FROM tmp_app_to_db_status_report_null b
		  WHERE tmp_app_to_db_status_report.site = b.site
		  AND tmp_app_to_db_status_report.project = b.project
		  AND tmp_app_to_db_status_report.app_server = b.app_server
		  AND tmp_app_to_db_status_report.instance = b.instance
		  AND tmp_app_to_db_status_report.status = ''
 		  AND b.status = ''
                  AND b.site = local_site
		  AND tmp_app_to_db_status_report.check_time = b.check_time)
    AND site = local_site;

END