# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1568994071 -7200
# Node ID 9b9f60e26b398d58895171945681992666be3c95
# Parent  a3cbf5a2c1ef1845d7888b13b49b57213fed90d7
Show only forecasts issued with latest measurements or later

In passing, fixup database update script to comply with newly
documented requirements.

diff -r a3cbf5a2c1ef -r 9b9f60e26b39 schema/geoserver_views.sql
--- a/schema/geoserver_views.sql	Fri Sep 20 16:38:20 2019 +0200
+++ b/schema/geoserver_views.sql	Fri Sep 20 17:41:11 2019 +0200
@@ -27,6 +27,7 @@
             USING (location, validity)
         LEFT JOIN (SELECT DISTINCT ON (location)
                     location,
+                    date_issue,
                     measure_date,
                     water_level,
                     count(*) OVER (PARTITION BY location) AS n
@@ -38,6 +39,7 @@
             USING (location)
         LEFT JOIN (SELECT DISTINCT ON (location)
                     location,
+                    date_issue,
                     max(acc) FILTER (WHERE measure_date
                             <= current_timestamp + '1 day'::interval)
                         OVER loc_date_issue AS forecast_accuracy_1d,
@@ -54,7 +56,8 @@
                             AND current_timestamp + '3 days'::interval) AS acc
                 WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
                 ORDER BY location, date_issue DESC) AS fca
-            USING (location);
+            -- Show only forecasts issued with latest measurements or later
+            ON fca.location = g.location AND fca.date_issue >= wl.date_issue;
 
 CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
     SELECT
diff -r a3cbf5a2c1ef -r 9b9f60e26b39 schema/updates/1202/01.fix_data_availability.sql
--- a/schema/updates/1202/01.fix_data_availability.sql	Fri Sep 20 16:38:20 2019 +0200
+++ b/schema/updates/1202/01.fix_data_availability.sql	Fri Sep 20 17:41:11 2019 +0200
@@ -1,3 +1,62 @@
-\ir ../../geoserver_views.sql;
+CREATE OR REPLACE VIEW waterway.gauges_base_view AS
+    SELECT
+        g.location,
+        isrs_asText(g.location) AS isrs_code,
+        g.objname,
+        g.geom,
+        g.applicability_from_km,
+        g.applicability_to_km,
+        g.validity,
+        g.zero_point,
+        g.geodref,
+        g.date_info,
+        g.source_organization,
+        g.erased,
+        r.rwls AS reference_water_levels,
+        wl.measure_date AS gm_measuredate,
+        wl.water_level AS gm_waterlevel,
+        wl.n AS gm_n_14d,
+        fca.forecast_accuracy_3d,
+        fca.forecast_accuracy_1d
+    FROM waterway.gauges g
+        LEFT JOIN (SELECT location, validity,
+                    json_strip_nulls(json_object_agg(
+                        coalesce(depth_reference, 'empty'), value)) AS rwls
+                FROM waterway.gauges_reference_water_levels
+                GROUP BY location, validity) AS r
+            USING (location, validity)
+        LEFT JOIN (SELECT DISTINCT ON (location)
+                    location,
+                    date_issue,
+                    measure_date,
+                    water_level,
+                    count(*) OVER (PARTITION BY location) AS n
+                FROM waterway.gauge_measurements
+                -- consider all measurements within 14 days plus a tolerance
+                WHERE measure_date
+                    >= current_timestamp - '14 days 00:15'::interval
+                ORDER BY location, measure_date DESC) AS wl
+            USING (location)
+        LEFT JOIN (SELECT DISTINCT ON (location)
+                    location,
+                    date_issue,
+                    max(acc) FILTER (WHERE measure_date
+                            <= current_timestamp + '1 day'::interval)
+                        OVER loc_date_issue AS forecast_accuracy_1d,
+                    max(acc) OVER loc_date_issue AS forecast_accuracy_3d
+                FROM (SELECT location, date_issue, measure_date,
+                        GREATEST(water_level - lower(conf_interval),
+                            upper(conf_interval) - water_level) AS acc
+                    FROM waterway.gauge_predictions
+                    -- consider predictions made within last 14 days ...
+                    WHERE date_issue
+                        >= current_timestamp - '14 days 00:15'::interval
+                        -- ... for the next three days from now
+                        AND measure_date BETWEEN current_timestamp
+                            AND current_timestamp + '3 days'::interval) AS acc
+                WINDOW loc_date_issue AS (PARTITION BY location, date_issue)
+                ORDER BY location, date_issue DESC) AS fca
+            -- Show only forecasts issued with latest measurements or later
+            ON fca.location = g.location AND fca.date_issue >= wl.date_issue;
 
 DROP INDEX waterway.gauge_measurements_location_measure_date_desc