--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1112/01.cleanup_views.sql	Mon Sep 09 16:13:26 2019 +0200
@@ -0,0 +1,130 @@
+DROP VIEW waterway.gauges_geoserver CASCADE;
+CREATE VIEW waterway.gauges_geoserver AS
+    SELECT
+        isrs_code,
+        objname,
+        geom,
+        applicability_from_km,
+        applicability_to_km,
+        zero_point,
+        geodref,
+        date_info,
+        source_organization,
+        reference_water_levels,
+        gm_measuredate,
+        gm_waterlevel,
+        gm_n_14d,
+        forecast_accuracy_3d,
+        forecast_accuracy_1d
+    FROM waterway.gauges_base_view
+    WHERE NOT erased;
+
+DROP VIEW waterway.distance_marks_geoserver;
+CREATE VIEW waterway.distance_marks_geoserver AS
+    SELECT
+       isrs_asText(location_code) AS location,
+       geom::Geometry(POINT, 4326),
+       related_enc,
+       (location_code).hectometre
+    FROM waterway.distance_marks_virtual;
+
+DROP VIEW waterway.bottlenecks_geoserver;
+CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
+    SELECT
+        b.id,
+        b.bottleneck_id,
+        b.objnam,
+        b.nobjnm,
+        b.area,
+        b.rb,
+        b.lb,
+        b.responsible_country,
+        b.revisiting_time,
+        b.limiting,
+        b.date_info,
+        b.source_organization,
+        g.objname AS gauge_objname,
+        g.reference_water_levels,
+        fal.date_info AS fa_date_info,
+        fal.critical AS fa_critical,
+        g.gm_measuredate,
+        g.gm_waterlevel,
+        g.gm_n_14d,
+        srl.date_max,
+        g.forecast_accuracy_3d,
+        g.forecast_accuracy_1d
+    FROM waterway.bottlenecks b
+        LEFT JOIN waterway.gauges_base_view g
+            ON b.gauge_location = g.location AND g.validity @> current_timestamp
+        LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
+                    bottleneck_id, date_info, critical
+                FROM waterway.fairway_availability
+                ORDER BY bottleneck_id, date_info DESC) AS fal
+            ON b.bottleneck_id = fal.bottleneck_id
+        LEFT JOIN (SELECT DISTINCT ON (bottleneck_id)
+                    bottleneck_id, max(date_info) AS date_max
+                FROM waterway.sounding_results
+                GROUP BY bottleneck_id
+                ORDER BY bottleneck_id DESC) AS srl
+            ON b.bottleneck_id = srl.bottleneck_id
+    WHERE b.validity @> current_timestamp;
+
+CREATE VIEW waterway.stretches_geoserver AS
+    SELECT
+        s.id,
+        s.name,
+        (s.stretch).lower::varchar as lower,
+        (s.stretch).upper::varchar as upper,
+        s.area::Geometry(MULTIPOLYGON, 4326),
+        s.objnam,
+        s.nobjnam,
+        s.date_info,
+        s.source_organization,
+        (SELECT string_agg(country_code, ', ')
+            FROM waterway.stretch_countries
+            WHERE stretches_id = s.id) AS countries,
+        s.staging_done,
+        min(g.gm_measuredate) AS gm_measuredate,
+        min(g.gm_n_14d) AS gm_n_14d,
+        max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
+        max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
+    FROM waterway.stretches s
+        LEFT JOIN waterway.gauges_geoserver g
+            ON isrs_fromtext(g.isrs_code) <@ s.stretch
+    GROUP BY s.id;
+
+CREATE VIEW waterway.sections_geoserver AS
+    SELECT
+        s.id,
+        s.name,
+        (s.section).lower::varchar as lower,
+        (s.section).upper::varchar as upper,
+        s.area::Geometry(MULTIPOLYGON, 4326),
+        s.objnam,
+        s.nobjnam,
+        s.date_info,
+        s.source_organization,
+        s.staging_done,
+        min(g.gm_measuredate) AS gm_measuredate,
+        min(g.gm_n_14d) AS gm_n_14d,
+        max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
+        max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
+    FROM waterway.sections s
+        LEFT JOIN waterway.gauges_geoserver g
+            ON isrs_fromtext(g.isrs_code) <@ s.section
+    GROUP BY s.id;
+
+UPDATE waterway.gt_pk_metadata SET pk_column = 'isrs_code'
+    WHERE table_schema = 'waterway'
+        AND table_name = 'gauges_geoserver';
+UPDATE waterway.gt_pk_metadata SET pk_column = 'location'
+    WHERE table_schema = 'waterway'
+        AND table_name = 'distance_marks_geoserver';
+
+GRANT SELECT ON
+        waterway.gauges_geoserver,
+        waterway.distance_marks_geoserver,
+        waterway.bottlenecks_geoserver,
+        waterway.stretches_geoserver,
+        waterway.sections_geoserver
+    TO waterway_user;