BEGIN

/*
History:
20170927 : Handle Unknow logic, should be unknown if 5 mins later
*/

	DECLARE var_latest_update_time DATETIME;
    DECLARE var_latest_update_time_remote DATETIME;
    
    DECLARE const_interval INT;
    
    SET const_interval := -5;
    
    SELECT 
		max(last_update_time)
	INTO 
		var_latest_update_time
	from te3logex1.db_instance_restart_time 
    LIMIT 1;

/*    
    SELECT 
		max(last_update_time)
	INTO 
		var_latest_update_time_remote
	from te3logex1.complete_db_info_remote 
    LIMIT 1;
*/    

	select 
		dbi.project,
		rs.site,
        rs.instance,
        dbi.dns_name,
        dbi.dns_site,
        rs.restart_time,
		-- // ***** [START] modified by eddie 20170927
		-- rs.status as db_status,
		CASE WHEN rs.status = 'UNKNOWN' AND rsi.instance IS NULL
		THEN
			rs.`status`
		ELSE
			rsi.`status`
		END as db_status,
		-- // ***** [END] modified by eddie 20170927
        rs.last_update_time AS last_update_time,
        now() AS last_sync_time
    from te3logex1.db_instance_restart_time rs
	-- // ***** [START] modified by eddie 20170927
	/*
		inner join
		(
			select site, instance, max(last_update_time) as max_last_update_time
			from pe3logex1.db_instance_restart_time 
			GROUP BY site, instance
		) rs1 on (rs.site = rs1.site and rs.instance = rs1.instance and rs.last_update_time = rs1.max_last_update_time)
	*/
	LEFT JOIN 
	(
		SELECT rs1.instance, rs1.`status`, max(rs1.last_update_time) AS max_last_update_time
		FROM te3logex1.db_instance_restart_time rs1
		WHERE rs1.last_update_time < var_latest_update_time AND rs1.last_update_time >= date_add(var_latest_update_time, interval const_interval minute)
			 AND rs1.`status` <> 'UNKNOWN'
		GROUP BY rs1.instance, rs1.`status`
	) rsi ON (rs.instance = rsi.instance)
	-- // ***** [END] modified by eddie 20170927
    inner join 
    (
		select distinct site, instance, project, dns_name, dns_site 
        from te3logex1.complete_db_info
	) dbi on (rs.site = dbi.site and rs.instance = dbi.instance)
	-- // ***** [START] modified by eddie 20170927
	WHERE rs.last_update_time = var_latest_update_time
	-- // ***** [END] modified by eddie 20170927

/*    
    union all
    
    select 
		dbi.project,
		rs.site,
        rs.instance,
        dbi.dns_name,
        dbi.dns_site,
        rs.restart_time,
-- // ***** [START] modified by eddie 20170927
-- rs.status as db_status,
CASE WHEN rs.status = 'UNKNOWN' AND rsi.instance IS NULL
THEN
	rs.`status`
ELSE
	rsi.`status`
END as db_status,
-- // ***** [END] modified by eddie 20170927
        rs.last_update_time AS last_update_time,
        now() AS last_sync_time
    from pe3logex1.db_instance_restart_time_remote rs
-- // ***** [START] modified by eddie 20170927
--     inner join
--     (
-- 		select site, instance, max(last_update_time) as max_last_update_time
-- 		from pe3logex1.db_instance_restart_time_remote 
--         GROUP BY site, instance
-- 	) rs1 on (rs.site = rs1.site and rs.instance = rs1.instance and rs.last_update_time = rs1.max_last_update_time)
LEFT JOIN 
(
	SELECT rs1.instance, rs1.`status`, max(rs1.last_update_time) AS max_last_update_time
    FROM pe3logex1.db_instance_restart_time_remote rs1
    WHERE rs1.last_update_time < var_latest_update_time_remote AND rs1.last_update_time >= date_add(var_latest_update_time_remote, interval const_interval minute)
		 AND rs1.`status` <> 'UNKNOWN'
	GROUP BY rs1.instance, rs1.`status`
) rsi ON (rs.instance = rsi.instance)
-- // ***** [END] modified by eddie 20170927
    inner join 
    (
		select distinct site, instance, project, dns_name, dns_site 
        from pe3logex1.complete_db_info_remote
	) dbi on (rs.site = dbi.site and rs.instance = dbi.instance)
-- // ***** [START] modified by eddie 20170927
WHERE rs.last_update_time = var_latest_update_time_remote;
-- // ***** [END] modified by eddie 20170927

*/
;


END