-- Copied from ../../default_sysconfig.sql
-- plus conflict resolution to achieve updates

CREATE TEMP TABLE base_views (name, def) AS VALUES (
    'gauges_base_view', $$
    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
    $$);

INSERT INTO sys_admin.published_services (schema, name, srid, view_def) VALUES
    ('waterway', 'gauges_geoserver', 4326, $$
        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 (
    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
        ) AS gauges_base_view
        WHERE NOT erased
    $$),
    ('waterway', 'sections_geoserver', 4326, $$
        SELECT
            s.id,
            s.name,
            (s.section).lower::varchar as lower,
            (s.section).upper::varchar as upper,
            s.area,
            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 (
    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
            WHERE NOT erased) AS g
                ON g.location <@ s.section
        GROUP BY s.id
    $$),
    ('waterway', 'stretches_geoserver', 4326, $$
        SELECT
            s.id,
            s.name,
            (s.stretch).lower::varchar as lower,
            (s.stretch).upper::varchar as upper,
            s.area,
            s.objnam,
            s.nobjnam,
            s.date_info,
            s.source_organization,
            (SELECT string_agg(country, ', ')
                FROM users.stretch_countries
                WHERE stretch_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 users.stretches s
            LEFT JOIN (
    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
            WHERE NOT erased) AS g
                ON g.location <@ s.stretch
        GROUP BY s.id
    $$),
    ('waterway', 'distance_marks_ashore_geoserver', 4326, $$
        SELECT id,
           country,
           geom,
           related_enc,
           hectom,
           catdis,
           position_code
        FROM waterway.distance_marks
    $$),
    ('waterway', 'distance_marks_geoserver', 4326, $$
        SELECT
           isrs_asText(location_code) AS location,
           geom,
           related_enc,
           (location_code).hectometre
        FROM waterway.distance_marks_virtual
    $$),
    ('waterway', 'sounding_results_areas_geoserver', 4326, $$
        SELECT bottleneck_id,
            date_info,
            height,
            areas
        FROM waterway.sounding_results_iso_areas ia
            JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id
    $$),
    ('waterway', 'bottlenecks_geoserver', 4326, $$
        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 (
    $$ || (SELECT def FROM base_views WHERE name = 'gauges_base_view') || $$
            ) AS 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
    $$),
    ('waterway', 'bottleneck_overview', 4326, $$
        SELECT
            objnam AS name,
            ST_Centroid(area) 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
    $$),
    ('waterway', 'sounding_differences', 4326, $$
        SELECT
            sd.id           AS id,
            bn.objnam       AS objnam,
            srm.date_info   AS minuend,
            srs.date_info   AS subtrahend,
            sdia.height     AS height,
            sdia.areas      AS areas
        FROM caching.sounding_differences sd
            JOIN caching.sounding_differences_iso_areas sdia
                ON sd.id = sdia.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.date_info::timestamptz <@ bn.validity
    $$)
ON CONFLICT (schema, name) DO UPDATE SET
    srid = EXCLUDED.srid,
    view_def = EXCLUDED.view_def;

DROP VIEW
    waterway.gauges_base_view,
    waterway.gauges_geoserver,
    waterway.distance_marks_geoserver,
    waterway.distance_marks_ashore_geoserver,
    waterway.bottlenecks_geoserver,
    waterway.stretches_geoserver,
    waterway.sections_geoserver,
    waterway.bottleneck_overview,
    waterway.sounding_results_areas_geoserver,
    waterway.sounding_differences;