/*
This query returns a table like this:

Alphabot	Connected	Inducted	SafetyOK
1			1			1			1
2			0			1			1
3			1			1			1
.			.			.			.
.			.			.			.
.			.			.			.
N			1			1			1
*/

SELECT 
Alphabot,
	MAX(CASE WHEN channel = "connectionsts" THEN 1 ELSE 0 END)  Connected,
	MAX(CASE WHEN channel = "inductionsts" THEN 1 ELSE 0 END) AS Inducted,
	MAX(CASE WHEN channel = "safetysts" THEN 1 ELSE 0 END) AS Safety_OK

FROM(
	/*This part gets the bot and channel name where the channel is TRUE or 1*/
	SELECT 
	CAST(REPLACE(substring_index(substring_index(tagpath, "/", -2), "/" , 1), "bot", "") AS INT) as Alphabot, 
	substring_index(substring_index(tagpath, "/", -2), "/" , -1) AS channel

	FROM(
		/* This inner query gets the most recent data */
		SELECT 
		sqlt_data_1_2021_02.tagid, 
		sqlt_data_1_2021_02.intvalue, 
		sqlt_data_1_2021_02.t_stamp
		
		FROM(
			SELECT tagid, intvalue, MAX(t_stamp) as t_stamp
			FROM  sqlt_data_1_2021_02 
			WHERE tagid >= 19 AND tagid <= 1313
			GROUP BY tagid
		) AS most_recent
		
		INNER JOIN  
			sqlt_data_1_2021_02 
		
		ON
		sqlt_data_1_2021_02.tagid = most_recent.tagid AND
		sqlt_data_1_2021_02.t_stamp = most_recent.t_stamp
	) AS data
	
	/* The data table is joined with the "_te" table to get the tag path */
	INNER JOIN  sqlth_te AS names
	ON data.tagid = names.id
	WHERE intvalue = 1
	) AS everything

WHERE Alphabot >0
GROUP BY Alphabot
ORDER BY Alphabot