-- Max PPH (all sorters combined) in 5-minute windows
-- Only where requested = actual AND requested dest is NOT a range like 'S01aa-S01af'
-- PPH = count_in_5min * 12

SELECT
    b.pph            AS max_pph,
    b.interval_start AS peak_time,
    totals.total_parcels
FROM (
    -- 5-minute bins over ALL S01 + S02 together
    SELECT
        FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(t_stamp) / 300) * 300) AS interval_start,
        COUNT(*)                                                  AS parcel_count,
        COUNT(*) * 12                                             AS pph
    FROM item_data
    WHERE t_stamp BETWEEN :starttime AND :endtime
      AND asRequested_Dest_ID_0 = sActual_Dest_ID
      AND asRequested_Dest_ID_0 NOT LIKE '%-%'     -- <- exclude S01aa-S01af, S01ag-S01af, etc
    GROUP BY
        interval_start
) AS b
CROSS JOIN (
    -- total parcels (same filter)
    SELECT
        COUNT(*) AS total_parcels
    FROM item_data
    WHERE t_stamp BETWEEN :starttime AND :endtime
      AND asRequested_Dest_ID_0 = sActual_Dest_ID
      AND asRequested_Dest_ID_0 NOT LIKE '%-%'
) AS totals
ORDER BY
    b.pph DESC,
    b.interval_start
LIMIT 1;
