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_14d.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,
                    measure_date,
                    water_level
                FROM waterway.gauge_measurements
                ORDER BY location, measure_date DESC) AS wl
            USING (location)
        LEFT JOIN (SELECT location, count(water_level) 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
                GROUP BY location) AS wl_14d
            USING (location)
        LEFT JOIN (SELECT location,
                    max(acc) FILTER (WHERE
                        measure_date <= current_timestamp + '1 day'::interval)
                        AS forecast_accuracy_1d,
                    max(acc) AS forecast_accuracy_3d
                FROM waterway.gauge_predictions,
                    GREATEST(water_level - lower(conf_interval),
                        upper(conf_interval) - water_level) AS acc (acc)
                WHERE measure_date
                    BETWEEN current_timestamp
                        AND current_timestamp + '3 days'::interval
                GROUP BY location) AS fca
            USING (location);

CREATE OR REPLACE VIEW waterway.gauges_geoserver AS
    SELECT
        location,
        isrs_code,
        objname,
        geom,
        applicability_from_km,
        applicability_to_km,
        validity,
        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;

CREATE OR REPLACE VIEW waterway.distance_marks_geoserver AS
    SELECT location_code,
       isrs_asText(location_code) AS location,
       geom::Geometry(POINT, 4326),
       related_enc,
       (location_code).hectometre
    FROM waterway.distance_marks_virtual;

CREATE OR REPLACE VIEW waterway.distance_marks_ashore_geoserver AS
    SELECT id,
       country,
       geom::Geometry(POINT, 4326),
       related_enc,
       hectom,
       catdis,
       position_code
    FROM waterway.distance_marks;

CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
    WITH
    fairway_availability_latest AS (
        SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical
            FROM waterway.fairway_availability
            ORDER BY bottleneck_id, date_info DESC),
    sounding_result_latest AS (
        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)
    SELECT
        b.id,
        b.bottleneck_id,
        b.objnam,
        b.nobjnm,
        b.stretch,
        b.area,
        b.rb,
        b.lb,
        b.responsible_country,
        b.revisiting_time,
        b.limiting,
        b.date_info,
        b.source_organization,
        g.location AS gauge_isrs_code,
        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 b.gauge_validity = g.validity
        LEFT JOIN fairway_availability_latest fal
            ON b.id = fal.bottleneck_id
        LEFT JOIN sounding_result_latest srl
            ON b.bottleneck_id = srl.bottleneck_id
    WHERE b.validity @> current_timestamp;

CREATE OR REPLACE 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 g.location <@ s.stretch
    GROUP BY s.id;

CREATE OR REPLACE 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 g.location <@ s.section
    GROUP BY s.id;

CREATE OR REPLACE VIEW waterway.sounding_results_contour_lines_geoserver AS
    SELECT bottleneck_id,
        date_info,
        height,
        CAST(lines AS geometry(multilinestring, 4326)) AS lines
    FROM waterway.sounding_results_contour_lines cl
        JOIN waterway.sounding_results sr ON sr.id = cl.sounding_result_id;

CREATE OR REPLACE VIEW waterway.bottleneck_overview AS
    SELECT
        objnam AS name,
        ST_Centroid(area)::Geometry(POINT, 4326) AS point,
        (lower(stretch)).hectometre AS from,
        (upper(stretch)).hectometre AS to,
        sr.current::text,
        responsible_country
    FROM waterway.bottlenecks bn LEFT JOIN (
        SELECT bottleneck_id, max(date_info) AS current
            FROM waterway.sounding_results
            GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id
    WHERE bn.validity @> current_timestamp
    ORDER BY objnam;

CREATE OR REPLACE VIEW waterway.sounding_differences AS
    SELECT
        sd.id           AS id,
        bn.objnam       AS objnam,
        srm.date_info   AS minuend,
        srs.date_info   AS subtrahend,
        sdcl.height     AS height,
        CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
    FROM caching.sounding_differences sd
        JOIN caching.sounding_differences_contour_lines sdcl
            ON sd.id = sdcl.sounding_differences_id
        JOIN waterway.sounding_results srm
            ON sd.minuend = srm.id
        JOIN waterway.sounding_results srs
            ON sd.subtrahend = srs.id
        JOIN waterway.bottlenecks bn
            ON srm.bottleneck_id = bn.bottleneck_id
                AND srm.bottleneck_validity = bn.validity;