/*+ MAX_EXECUTION_TIME(8000) */

-- Group same alarms in time period - just count occurrences
-- Filter by priority, location, and time range

SELECT
  CONCAT(IFNULL(ae.displaypath, 'Unknown'), ' - ',
         SUBSTRING_INDEX(IFNULL(ae.source, ''), ':/alm:', -1)) AS Description,

  IFNULL(tag.strValue, '') AS Tag,

  -- Use myLocation property directly (99.96% coverage) - MUCH faster than parsing tags!
  IFNULL(loc.strValue, '') AS Location,

  CASE ae.priority
    WHEN 0 THEN 'Diagnostic' 
    WHEN 1 THEN 'Low' 
    WHEN 2 THEN 'Medium'
    WHEN 3 THEN 'High' 
    WHEN 4 THEN 'Critical' 
    ELSE 'Unknown'
  END AS Priority,
  
  CASE 
    WHEN ae.priority = 3 THEN 'Alarms-Styles/High'
    WHEN ae.priority = 2 THEN 'Alarms-Styles/Medium'
    WHEN ae.priority = 1 THEN 'Alarms-Styles/Low'
    WHEN ae.priority = 0 THEN 'Alarms-Styles/Diagnostic'
    ELSE 'Alarms-Styles/NoAlarm'
  END AS Style,

  MIN(ae.eventtime) AS FirstTimestamp,
  MAX(ae.eventtime) AS LastTimestamp,
  
  TIME_FORMAT(
    SEC_TO_TIME(
      TIMESTAMPDIFF(SECOND, 
        MIN(ae.eventtime),
        MAX(
          (SELECT MIN(clr.eventtime) 
           FROM alarm_events clr USE INDEX (idx_alarm_events_eventid)
           WHERE clr.eventid = ae.eventid 
             AND clr.eventtype IN (1, 2)
             AND clr.id > ae.id
          )
        )
      )
    ),
    '%H:%i:%s'
  ) AS Duration,

  COUNT(*) AS "Count"

FROM alarm_events ae FORCE INDEX (idx_alarm_events_grouping)
LEFT JOIN alarm_event_data tag FORCE INDEX (idx_alarm_event_data_lookup)
  ON tag.id = ae.id AND tag.propname = 'myTag'
LEFT JOIN alarm_event_data loc FORCE INDEX (idx_alarm_event_data_lookup)
  ON loc.id = ae.id AND loc.propname = 'myLocation'

WHERE 
  ae.eventtype = 0
  AND ae.displaypath NOT LIKE '%System Startup%'
  AND ae.source NOT LIKE '%System Startup%'
  AND ae.displaypath NOT LIKE '%System Shutdown%'
  AND ae.source NOT LIKE '%System Shutdown%'
  AND (
    (:startTime IS NULL OR :startTime = '' OR ae.eventtime >= :startTime)
    AND
    (:endTime   IS NULL OR :endTime   = '' OR ae.eventtime <= :endTime)
  )
  -- Exclude active alarms: only show alarms that have been cleared
  -- Check if a later clear/ack event exists for this eventid
  AND EXISTS (
    SELECT 1 
    FROM alarm_events clr USE INDEX (idx_alarm_events_eventid)
    WHERE clr.eventid = ae.eventid 
      AND clr.eventtype IN (1, 2)
      AND clr.id > ae.id
    LIMIT 1
  )
  -- Priority filter: Only evaluate priority conditions when parameter is provided
  AND (
       :priority IS NULL OR :priority = '' OR :priority = 0
    OR (
      :priority IS NOT NULL AND :priority != '' AND :priority != 0 AND (
        (:priority = 3 AND ae.priority = 3)
    OR (:priority = 2 AND ae.priority BETWEEN 2 AND 3)
    OR (:priority = 1 AND ae.priority BETWEEN 1 AND 3)
  )
    )
  )
  -- LOCATION FILTER
  AND (
        (:location = 'all' 
            AND (loc.strValue LIKE '%MCM01%' OR loc.strValue LIKE '%MCM02%')
        )
     OR (:location <> 'all' 
            AND loc.strValue LIKE CONCAT('%', :location, '%')
        )
  )

GROUP BY
  ae.source, 
  ae.displaypath, 
  ae.priority,
  tag.strValue

ORDER BY
  FirstTimestamp DESC;