-- Avoid orphaned entries due to changed OIDs
ALTER TABLE sys_admin.published_services ADD _name varchar;
UPDATE sys_admin.published_services SET _name = name;

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;

-- Avoid orphaned entries due to changed OIDs
UPDATE sys_admin.published_services SET name = _name;
ALTER TABLE sys_admin.published_services DROP _name;

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;