# HG changeset patch
# User Bjoern Schilberg <bjoern@intevation.de>
# Date 1550236122 -3600
# Node ID 3154120af837c56fec5f2f06ba31bd3c4e15bfc5
# Parent  d69bd6631362b0752d2a6d3cb40dc72301044b01
schema/gemma.sql: Added fairway_availability.critical and date_info to bottlenecks_geoserver view.

diff -r d69bd6631362 -r 3154120af837 schema/gemma.sql
--- a/schema/gemma.sql	Fri Feb 15 13:42:08 2019 +0100
+++ b/schema/gemma.sql	Fri Feb 15 14:08:42 2019 +0100
@@ -473,7 +473,12 @@
     )
 
     -- Published view for GeoServer
-    CREATE VIEW bottlenecks_geoserver AS SELECT
+    CREATE VIEW bottlenecks_geoserver AS
+    WITH fairway_availability_latest AS (
+        SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical
+        FROM fairway_availability
+        ORDER BY bottleneck_id, date_info DESC NULLS LAST)
+    SELECT
         b.id,
         b.bottleneck_id,
         b.objnam,
@@ -491,7 +496,9 @@
         g.objname AS gauge_objname,
         rwl_ldc.value AS ldc,
         rwl_mw.value AS mw,
-        rwl_hdc.value AS hdc
+        rwl_hdc.value AS hdc,
+        fal.date_info AS fa_date_info,
+        fal.critical AS fa_critical
     FROM bottlenecks b, gauges g,
         (SELECT gauge_id, value FROM gauges_reference_water_levels
            WHERE reference_water_level='LDC') rwl_ldc,
@@ -499,6 +506,10 @@
            WHERE reference_water_level='MW') rwl_mw,
         (SELECT gauge_id, value FROM gauges_reference_water_levels
            WHERE reference_water_level='HDC') rwl_hdc
+        LEFT JOIN LATERAL (
+            SELECT bottleneck_id,date_info,critical
+            FROM  fairway_availability_latest
+            WHERE b.id=bottleneck_id) fal 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