# HG changeset patch
# User Sascha Wilde <wilde@intevation.de>
# Date 1551272548 -3600
# Node ID 975e522104740725ab6612e24aca117332e5941b
# Parent  85c47d63fdb9daf3e0820f899c46ecac5a710a5c
Don't hard code the available water level ref. systems.

diff -r 85c47d63fdb9 -r 975e52210474 schema/gemma.sql
--- a/schema/gemma.sql	Wed Feb 27 10:56:18 2019 +0100
+++ b/schema/gemma.sql	Wed Feb 27 14:02:28 2019 +0100
@@ -499,19 +499,15 @@
         b.source_organization,
         g.location AS gauge_isrs_code,
         g.objname AS gauge_objname,
-        rwl_ldc.value AS ldc,
-        rwl_mw.value AS mw,
-        rwl_hdc.value AS hdc,
+        json_object_agg(r.depth_reference, r.value) AS reference_water_levels,
         fal.date_info AS fa_date_info,
         fal.critical AS fa_critical,
         gmw.water_level as gm_waterlevel
-    FROM bottlenecks b, gauges g,
-        (SELECT gauge_id, value FROM gauges_reference_water_levels
-           WHERE depth_reference = 'LDC') rwl_ldc,
-        (SELECT gauge_id, value FROM gauges_reference_water_levels
-           WHERE depth_reference = 'MW') rwl_mw,
-        (SELECT gauge_id, value FROM gauges_reference_water_levels
-           WHERE depth_reference = 'HDC') rwl_hdc
+    FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location
+        LEFT JOIN LATERAL (
+            SELECT gauge_id,depth_reference,value
+            FROM gauges_reference_water_levels
+            ) r ON r.gauge_id = b.fk_g_fid
         LEFT JOIN LATERAL (
             SELECT bottleneck_id,date_info,critical
             FROM  fairway_availability_latest
@@ -520,8 +516,7 @@
             SELECT water_level
             FROM  gauge_measurements_waterlevel
             WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE
-    WHERE b.fk_g_fid = g.location AND g.location = rwl_ldc.gauge_id
-      AND g.location = rwl_mw.gauge_id AND g.location = rwl_hdc.gauge_id
+    GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
 
     CREATE TABLE sounding_results (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,