WITH jam_data AS (
    /* -------------------------------------------------------
       JAM COUNT PER DEVICE (correct rising-edge detection)
    ------------------------------------------------------- */
    SELECT 
        Name,
        COUNT(*) AS Jam_count
    FROM
    (
        /* AREA JAMS */
        SELECT 
            Name,
            t_stamp
        FROM jam_area
        WHERE t_stamp BETWEEN :starttime AND :endtime

        UNION ALL

        /* DEVICE JAMS — TRUE RISING EDGE */
        SELECT
            Name,
            t_stamp
        FROM
        (
            SELECT
                Name,
                t_stamp,
                Jam,
                IF(@lastName = Name, @prevJam, 0) AS prevJam,
                @prevJam := Jam,
                @lastName := Name
            FROM lane_data
                JOIN (SELECT @prevJam := 0, @lastName := '') AS vars
            WHERE t_stamp BETWEEN :starttime AND :endtime
            ORDER BY Name, t_stamp
        ) AS x
        WHERE Jam = 1 AND prevJam = 0
    ) AS all_jams
    GROUP BY Name
)

SELECT
    jd.Name,
    ROUND(
        (jd.Jam_count * 3600.0) 
        / NULLIF(TIMESTAMPDIFF(SECOND, :starttime, :endtime), 0),
        2
    ) AS Jam_rate
FROM jam_data jd

UNION ALL

/* FALLBACK — RETURN ONLY WHEN JAM_DATA IS EMPTY */
SELECT
    'N/A' AS Name,
    0 AS Jam_rate
WHERE NOT EXISTS (SELECT 1 FROM jam_data)

ORDER BY Jam_rate DESC, Name ASC;
