WITH ALARM_TIMELAPSES AS (
	/* Group related active & clear events into a single row */
	SELECT
		id, /* Unique to each row in alarm_events, used to look up metadata in alarm_event_data */
		source, /* Unique alarm path in Ignition */
		eventtime AS starttime,
		displaypath, /* Get description */
        /* Get the last time this event was active within the given time range*/
		CAST(LEAST(COALESCE((
          /* Search for the clear event (if exists) for the outer query's active event */ 
		  SELECT MIN(eventtime)
		  FROM alarm_events e2
		  WHERE e2.eventid = e.eventid /* eventid is unique per alarm instance */
			AND e2.eventtime >= e.eventtime
			AND e2.eventtype = 1 /* Look only for the clear event */
		  ORDER BY eventtime ASC
		), NOW()), :endDate) AS DATETIME) AS endtime, /* Clamp to end of time range if clear event is after end time */
		priority
	FROM alarm_events e
	/* The range for both start/end allows for index optimizations */
	WHERE eventtime BETWEEN :startDate AND :endDate
	  /* Filter shifts */
	  {shifts}
	  /* Filter alarm types */
	  AND displaypath IN ({alarms})
	  /* Filter out active events */
	  AND eventtype = 0
), HITLIST AS (
	/* Group the same type of alarms together to get hit counts and total durations */
	SELECT
        MAX(id) AS id, /* Use latest id to get the latest metadata */
		MIN(starttime) AS firsttime, /* Get the earliest it's been active in the given time range */
		MAX(endtime) AS lasttime, /* Get the latest it's been active in the given time range */
		MIN(displaypath) AS displaypath, /* Get one of the displaypaths associated with source */
        MAX(priority) AS priority,
		COUNT(*) AS count,
        SUM(TIME_TO_SEC(TIMEDIFF(endtime, starttime))) AS duration
	FROM ALARM_TIMELAPSES
    GROUP BY source
    ORDER BY count DESC
	LIMIT 2000
)
/* Finally, merge metadata into hitlist */
SELECT
  /*e.firsttime, e.lasttime,*/
  /* Translate duration to a string */
  CAST(SEC_TO_TIME(FLOOR(e.duration)) AS CHAR) AS duration,
  /* Retrieve instance count and total duration */
  e.count,
  dtag.strvalue AS plctag,
  ddevice.strvalue AS device,
  displaypath AS description
FROM HITLIST e
/* Lookup Device */
JOIN alarm_event_data dtag ON e.id = dtag.id AND dtag.propname = "PLCTag"
/* Lookup Device */
JOIN alarm_event_data ddevice ON e.id = ddevice.id AND ddevice.propname = "Device"
/* Lookup Class */
JOIN alarm_event_data dclass ON e.id = dclass.id AND dclass.propname = "Class"
/* Lookup DeviceType */
JOIN alarm_event_data dtype ON e.id = dtype.id AND dtype.propname = "DeviceType"
WHERE dclass.strvalue IN ("Error", "Warning")
  AND ddevice.strvalue NOT IN ("Ignition")
  AND SUBSTRING_INDEX(dtype.strvalue, "_", 1) IN ({devices});