SELECT
    ev.id as id
     ,ev.id as qmap_event_id
     ,ev.version
     ,to_char(ev.start_time,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as start_date
     ,to_char(ev.end_time,'YYYY-MM-DD"T"HH24:MI:SS"Z"') as end_date
     ,ev.severity::smallint AS severity
     ,es.description as event_description
     ,COALESCE(es.code, 'DEFAULT') as type_code
     ,ev.start_location
     ,ev.end_location
     ,ev.descr_it
     ,ev.descr_en
     ,ev.descr_de
     ,ev.road_dir
     ,ev.start_lcd
     ,ev.end_lcd
     ,ev.cause as cause_it
     ,ev.cause as cause_en
     ,ev.cause as cause_de
     ,ev.status
     ,tmc.lcd
     ,tmc.road_class
     ,tmc.roadnumber
     ,tmc.road_name
     ,(CASE
           WHEN ev.road_dir = 1 THEN tmc.to_name
           WHEN ev.road_dir = 2 THEN tmc.from_name
           ELSE 'Entrambe le direzioni'
      END) as dir_name
     ,tmc.from_name
     ,tmc.to_name
     ,tmc.linear_length
     ,tmc.display_order
     ,ST_X(ev.start_geometry) as point_x
     ,ST_Y(ev.start_geometry) as point_y
     ,ev.source_description as source
     ,es.name_it as title_it
     ,es.name_en as title_en
     ,es.name_de as title_de
     ,ev.write_dt as write_dt
     ,ev.note as notes
     ,es.id as event_subtype_id
     ,es.event_type_id as event_type_id
FROM
    active.events as ev
    LEFT JOIN
    data.event_subtypes as es
    ON ev.event_subtype = es.id
    LEFT JOIN
    (
        SELECT
              lcd
             ,road_class
             ,roadnumber
             ,road_name
             ,from_name
             ,to_name
             ,linear_length
             ,display_order
        FROM network.roads as tmc
        GROUP BY 1, 2, 3, 4, 5, 6, 7, 8
    ) AS tmc
    ON ev.road_lcd = tmc.lcd
WHERE $where_cond
  AND ev.status < 2 OR (ev.status = 2 AND now() - interval '15 minutes' < ev.end_time)
ORDER BY ev.severity DESC;
