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