/*+ MAX_EXECUTION_TIME(8000) */

SELECT
  ae.id AS ID,
  ae.eventtime AS StartTimestamp,
  TIME_FORMAT(SEC_TO_TIME(TIMESTAMPDIFF(SECOND, ae.eventtime, NOW())), '%H:%i:%s') AS Duration,
  CONCAT(REPLACE(ae.displaypath, '_', '-'), ' ', SUBSTRING_INDEX(ae.source, ':/alm:', -1)) AS Description,

  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,

  CONCAT(ae.displaypath, '.HMI.Alarm.', SUBSTRING_INDEX(IFNULL(aed.strValue, ''), '/', -1)) AS Tag,
  
  CASE 
    WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(IFNULL(aed.strValue, ''), '/', 2), '/', -1) = 'Chute' THEN 'SMC'
    ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(IFNULL(aed.strValue, ''), '/', 2), '/', -1)
  END AS Location,
  
  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,
  
  aed.strValue AS FullTag,
  ae.displaypath AS Device

FROM alarm_events ae FORCE INDEX (idx_alarm_events_active)
LEFT JOIN alarm_event_data aed FORCE INDEX (idx_alarm_event_data_lookup)
  ON aed.id = ae.id AND aed.propname = 'myTag'

WHERE 
  ae.eventtype = 0
  AND ae.displaypath NOT LIKE '%System Startup%'
  AND ae.source NOT LIKE '%System Startup%'

  -- Priority filter (same logic as other queries)
  AND (
       :priority IS NULL OR :priority = '' OR :priority = 0
    OR (: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 IS NULL OR :location = ''
    OR SUBSTRING_INDEX(SUBSTRING_INDEX(IFNULL(aed.strValue, ''), '/', 2), '/', -1)
         LIKE CONCAT('%', :location, '%')
  )

  -- Exclude cleared alarms
  AND NOT EXISTS (
    SELECT 1 
    FROM alarm_events clr FORCE INDEX (idx_alarm_events_clear)
    WHERE clr.eventid = ae.eventid 
      AND clr.eventtype = 1
  )

ORDER BY ae.eventtime DESC;