WITH raw AS (
    SELECT 
        Name,
        t_stamp,
        'Full' AS sig,
        Full AS val
    FROM lane_data
    WHERE t_stamp BETWEEN :startTime AND :endTime

    UNION ALL
    SELECT 
        Name,
        t_stamp,
        'Half_Full' AS sig,
        Half_Full AS val
    FROM lane_data
    WHERE t_stamp BETWEEN :startTime AND :endTime

    UNION ALL
    SELECT 
        Name,
        t_stamp,
        'Jam' AS sig,
        Jam AS val
    FROM lane_data
    WHERE t_stamp BETWEEN :startTime AND :endTime
),

-- ✨ Add artificial boundary rows so ongoing (val=1) states close at endTime
boundary AS (
    SELECT 
        Name,
        :endTime AS t_stamp,
        sig,
        0 AS val
    FROM raw
    GROUP BY Name, sig
),

ev AS (
    SELECT * FROM raw
    UNION ALL
    SELECT * FROM boundary
),

ordered AS (
    SELECT
        Name,
        sig,
        t_stamp,
        val,
        LAG(val) OVER (PARTITION BY Name, sig ORDER BY t_stamp) AS prev_val,
        LEAD(val) OVER (PARTITION BY Name, sig ORDER BY t_stamp) AS next_val,
        LEAD(t_stamp) OVER (PARTITION BY Name, sig ORDER BY t_stamp) AS next_ts
    FROM ev
),

intervals AS (
    SELECT
        Name,
        sig,
        TIMESTAMPDIFF(
            SECOND,
            t_stamp,
            next_ts
        ) AS duration_sec
    FROM ordered
    WHERE val = 1
),

dur AS (
    SELECT
        Name,
        SUM(CASE WHEN sig = 'Full' THEN duration_sec ELSE 0 END) AS Full_Duration_Sec,
        SUM(CASE WHEN sig = 'Half_Full' THEN duration_sec ELSE 0 END) AS Half_Full_Duration_Sec,
        SUM(CASE WHEN sig = 'Jam' THEN duration_sec ELSE 0 END) AS Jam_Duration_Sec
    FROM intervals
    GROUP BY Name
)

SELECT
    :startTime AS `Start Time`,
    :endTime AS `End Time`,
    d.Name AS `Lane ID`,
    COALESCE(d.Full_Duration_Sec, 0) AS `Full Duration (Sec)`,
    COALESCE(d.Half_Full_Duration_Sec, 0) AS `Half Full Duration (Sec)`,
    COALESCE(d.Jam_Duration_Sec, 0) AS `Jam Duration (Sec)`
FROM dur d

UNION ALL

SELECT
    :startTime,
    :endTime,
    'N/A',
    0,
    0,
    0
WHERE NOT EXISTS (SELECT 1 FROM dur)

ORDER BY `Lane ID`;