SELECT
  COALESCE(base.Total_Jam, 0) AS Total_Jam,
  COALESCE(base.MCM03_Fluid_Inbound, 0) AS MCM03_Fluid_Inbound,
  COALESCE(base.MCM02_Fluid_Inbound, 0) AS MCM02_Fluid_Inbound,
  COALESCE(base.Bulk_Inbound, 0) AS Bulk_Inbound,
  COALESCE(base.Startstamp, :starttime) AS Startstamp,
  COALESCE(base.Endtstamp, :endtime) AS Endtstamp,
  1 AS Total_perc,
  CASE 
    WHEN COALESCE(base.Total_Jam, 0) = 0 THEN 0
    ELSE COALESCE(base.MCM03_Fluid_Inbound, 0) / base.Total_Jam 
  END AS MCM03_Fluid_Inbound_perc,
  CASE 
    WHEN COALESCE(base.Total_Jam, 0) = 0 THEN 0
    ELSE COALESCE(base.MCM02_Fluid_Inbound, 0) / base.Total_Jam 
  END AS MCM02_Fluid_Inbound_perc,
  CASE 
    WHEN COALESCE(base.Total_Jam, 0) = 0 THEN 0
    ELSE COALESCE(base.Bulk_Inbound, 0) / base.Total_Jam 
  END AS Bulk_Inbound_perc

FROM (
  SELECT
    MIN(a.eventtime) AS Startstamp,
    MAX(COALESCE(c.eventtime, a.eventtime)) AS Endtstamp,
    SUM(a.category = 'MCM03_Fluid_Inbound')       AS MCM03_Fluid_Inbound,
    SUM(a.category = 'MCM02_Fluid_Inbound')       AS MCM02_Fluid_Inbound,
    SUM(a.category = 'Bulk_Inbound')       AS Bulk_Inbound,
    SUM(a.category IN ( 'MCM03_Fluid_Inbound', 'MCM02_Fluid_Inbound', 'Bulk_Inbound')) AS Total_Jam
  FROM
    (
      SELECT
        ae.id,
        ae.eventtime,
        ae.eventid,
        ae.displaypath AS device,
        m.category
      FROM alarm_events ae
      JOIN (
        SELECT 'ULC2_2_PE1' AS device, 'Bulk_Inbound' AS category UNION ALL
        SELECT 'ULC3_3_JPE2', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC4_3_JPE2', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC3_7_PE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC3_3_JPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC4_3_JPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC3_6_JPE2', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC4_7_PE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC4_6_JPE2', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_8_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_8_TPE2', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_10_TPE2', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_11_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_12_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_10_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC4_4_JPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC2_8_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC2_11_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC2_12_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC2_10_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC3_6_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_4_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_4_TPE2', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC2_4_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC2_4_TPE2', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC2_6_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_6_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_5_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC2_5_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC4_6_TPE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC1_2_PE1', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC2_10_TPE2', 'Bulk_Inbound' UNION ALL
        SELECT 'ULC3_4_JPE1', 'Bulk_Inbound' UNION ALL
        
        SELECT 'PS1_2A_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL3_2_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_2_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL6_3_TPE3', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL5_2_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL4_3_TPE3', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_1_TPE3', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_1_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_1_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL5_2_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL6_3_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL6_3_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL4_3_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL4_3_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL3_1_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_7_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_6_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_5A_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL5_1_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL6_7_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL6_6_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL6_5_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL6_5_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL5_6_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL5_5_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL5_4_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL5_4_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL4_7_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL4_6_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_6_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL1_5_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL1_5_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL1_6_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_6_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_4_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_3_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_8_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS2_1_TPE4', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_1_TPE4', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL3_2_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL2_3_TPE3', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL1_3_TPE3', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_1_TPE3', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_1_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_1_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL1_3_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL1_3_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL2_3_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL2_3_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_8_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_7_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_6_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'PS1_3_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL3_6_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL3_5_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL3_4_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL3_4_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL2_7_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL2_6_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL2_5_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL2_5_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL1_7_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL4_5_TPE2', 'MCM02_Fluid_Inbound' UNION ALL
        SELECT 'UL4_5_TPE1', 'MCM02_Fluid_Inbound' UNION ALL
        
        SELECT 'UL11_3_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS3_6_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS3_7_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS3_8_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL12_1_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL7_1_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL12_6_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL12_5_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL12_4_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL12_4_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL11_7_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL11_6_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL11_5_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL11_5_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL10_7_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL10_6_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL10_5_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL10_5_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL9_7_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL9_6_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL9_5_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL9_5_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL8_7_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL8_6_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL8_5_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL8_5_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL7_6_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL7_5_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS3_6_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS3_5A_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_4_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_5A_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL10_3_TPE3', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_2_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_1_TPE4', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS3_2_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL12_2_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL11_3_TPE3', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL10_3_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL9_3_TPE3', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL8_3_TPE3', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL7_2_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_1_TPE3', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_1_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_1_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL7_4_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS3_1_TPE3', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS3_1_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL12_2_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL7_2_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL11_3_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL10_3_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL9_3_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL9_3_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL8_3_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL8_3_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_8_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_7_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_6_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS4_6_TPE1', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'PS3_1_TPE2', 'MCM03_Fluid_Inbound' UNION ALL
        SELECT 'UL7_4_TPE1', 'MCM03_Fluid_Inbound'
        
       
      ) AS m
        ON ae.displaypath = m.device
      WHERE ae.eventtype = 0
        AND ae.eventtime BETWEEN :starttime AND :endtime
        AND ae.displaypath NOT LIKE '%System Startup%'
        AND ae.source NOT LIKE '%System Startup%'
    ) AS a
    LEFT JOIN (
      SELECT eventid, MIN(eventtime) AS eventtime
      FROM alarm_events
      WHERE eventtype = 1
      GROUP BY eventid
    ) AS c
      ON a.eventid = c.eventid
  GROUP BY 1=1  -- Force aggregation to always return at least one row
) AS base

UNION ALL

-- Default row with zeros when no jam events exist
SELECT
  0 AS Total_Jam,
  0 AS MCM03_Fluid_Inbound,
  0 AS MCM02_Fluid_Inbound,
  0 AS Bulk_Inbound,
  :starttime AS Startstamp,
  :endtime AS Endtstamp,
  1 AS Total_perc,
  0 AS MCM03_Fluid_Inbound_perc,
  0 AS MCM02_Fluid_Inbound_perc,
  0 AS Bulk_Inbound_perc

WHERE NOT EXISTS (
  SELECT 1 FROM alarm_events ae
  WHERE ae.eventtype = 0
    AND ae.eventtime BETWEEN :starttime AND :endtime
    AND ae.displaypath NOT LIKE '%System Startup%'
    AND ae.source NOT LIKE '%System Startup%'
    AND ae.displaypath IN (
      'ULC2_2_PE1', 'ULC3_3_JPE2', 'ULC4_3_JPE2', 'ULC3_7_PE1', 'ULC3_3_JPE1', 'ULC4_3_JPE1', 'ULC3_6_JPE2', 'ULC4_7_PE1', 'ULC4_6_JPE2', 'ULC1_8_TPE1', 'ULC1_8_TPE2', 'ULC1_10_TPE2', 'ULC1_11_TPE1', 'ULC1_12_TPE1', 'ULC1_10_TPE1', 'ULC4_4_JPE1', 'ULC2_8_TPE1', 'ULC2_11_TPE1', 'ULC2_12_TPE1', 'ULC2_10_TPE1', 'ULC3_6_TPE1', 'ULC1_4_TPE1', 'ULC1_4_TPE2', 'ULC2_4_TPE1', 'ULC2_4_TPE2', 'ULC2_6_TPE1', 'ULC1_6_TPE1', 'ULC1_5_TPE1', 'ULC2_5_TPE1', 'ULC4_6_TPE1', 'ULC1_2_PE1', 'ULC2_10_TPE2', 'ULC3_4_JPE1',
      'PS1_2A_TPE1', 'UL3_2_TPE1', 'PS2_2_TPE1', 'UL6_3_TPE3', 'UL5_2_TPE2', 'UL4_3_TPE3', 'PS2_1_TPE3', 'PS2_1_TPE2', 'PS2_1_TPE1', 'UL5_2_TPE1', 'UL6_3_TPE1', 'UL6_3_TPE2', 'UL4_3_TPE1', 'UL4_3_TPE2', 'UL3_1_TPE1', 'PS2_7_TPE1', 'PS2_6_TPE1', 'PS2_5A_TPE1', 'UL5_1_TPE1', 'UL6_7_TPE1', 'UL6_6_TPE1', 'UL6_5_TPE2', 'UL6_5_TPE1', 'UL5_6_TPE1', 'UL5_5_TPE1', 'UL5_4_TPE2', 'UL5_4_TPE1', 'UL4_7_TPE1', 'UL4_6_TPE1', 'PS2_6_TPE2', 'UL1_5_TPE1', 'UL1_5_TPE2', 'UL1_6_TPE1', 'PS1_6_TPE1', 'PS1_4_TPE1', 'PS1_3_TPE2', 'PS2_8_TPE1', 'PS2_1_TPE4', 'PS1_1_TPE4', 'UL3_2_TPE2', 'UL2_3_TPE3', 'UL1_3_TPE3', 'PS1_1_TPE3', 'PS1_1_TPE2', 'PS1_1_TPE1', 'UL1_3_TPE1', 'UL1_3_TPE2', 'UL2_3_TPE1', 'UL2_3_TPE2', 'PS1_8_TPE1', 'PS1_7_TPE1', 'PS1_6_TPE2', 'PS1_3_TPE1', 'UL3_6_TPE1', 'UL3_5_TPE1', 'UL3_4_TPE2', 'UL3_4_TPE1', 'UL2_7_TPE1', 'UL2_6_TPE1', 'UL2_5_TPE2', 'UL2_5_TPE1', 'UL1_7_TPE1', 'UL4_5_TPE2', 'UL4_5_TPE1',
      'UL11_3_TPE1', 'PS3_6_TPE2', 'PS3_7_TPE1', 'PS3_8_TPE1', 'UL12_1_TPE1', 'UL7_1_TPE1', 'UL12_6_TPE1', 'UL12_5_TPE1', 'UL12_4_TPE2', 'UL12_4_TPE1', 'UL11_7_TPE1', 'UL11_6_TPE1', 'UL11_5_TPE2', 'UL11_5_TPE1', 'UL10_7_TPE1', 'UL10_6_TPE1', 'UL10_5_TPE2', 'UL10_5_TPE1', 'UL9_7_TPE1', 'UL9_6_TPE1', 'UL9_5_TPE2', 'UL9_5_TPE1', 'UL8_7_TPE1', 'UL8_6_TPE1', 'UL8_5_TPE2', 'UL8_5_TPE1', 'UL7_6_TPE1', 'UL7_5_TPE1', 'PS3_6_TPE1', 'PS3_5A_TPE1', 'PS4_4_TPE1', 'PS4_5A_TPE1', 'UL10_3_TPE3', 'PS4_2_TPE1', 'PS4_1_TPE4', 'PS3_2_TPE1', 'UL12_2_TPE2', 'UL11_3_TPE3', 'UL10_3_TPE1', 'UL9_3_TPE3', 'UL8_3_TPE3', 'UL7_2_TPE2', 'PS4_1_TPE3', 'PS4_1_TPE2', 'PS4_1_TPE1', 'UL7_4_TPE2', 'PS3_1_TPE3', 'PS3_1_TPE1', 'UL12_2_TPE1', 'UL7_2_TPE1', 'UL11_3_TPE2', 'UL10_3_TPE2', 'UL9_3_TPE1', 'UL9_3_TPE2', 'UL8_3_TPE1', 'UL8_3_TPE2', 'PS4_8_TPE1', 'PS4_7_TPE1', 'PS4_6_TPE2', 'PS4_6_TPE1', 'PS3_1_TPE2', 'UL7_4_TPE1'
    )
)

LIMIT 1;
