-- This is Free Software under GNU Affero General Public License v >= 3.0
-- without warranty, see README.md and license for details.

-- SPDX-License-Identifier: AGPL-3.0-or-later
-- License-Filename: LICENSES/AGPL-3.0.txt

-- Copyright (C) 2018, 2019, 2020 by via donau
--   – Österreichische Wasserstraßen-Gesellschaft mbH
-- Software engineering by Intevation GmbH

-- Author(s):
--  * Sascha Wilde <wilde@intevation.de>
--  * Bernhard Reiter <bernhard.reiter@intevation.de>
--  * Fadi Abbund <fadi.abbud@intevation.de>
--  * Tom Gottfried <tom@intevation.de>

BEGIN;

--
-- Default system configurations for the GEMMA database
--

--
-- Tables and views published via GeoServer
--

-- temporary table to store portions of SQL shared by multiple SQL views
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
    $$), (
    'fairway_marks_tmpl', $$
        SELECT id,
            lower(validity) AS valid_from,
            COALESCE(upper(validity), current_timestamp) AS valid_to,
            geom,
            datsta,
            datend,
            persta,
            perend,
            objnam,
            nobjnm,
            inform,
            ninfom,
            scamin,
            picrep,
            txtdsc,
            sordat,
            sorind,
            %s
        FROM waterway.fairway_marks_%s
    $$);

-- Directly accessed tables
INSERT INTO sys_admin.published_services (schema, name) VALUES
    ('waterway', 'waterway_area'),
    ('waterway', 'waterway_profiles');

-- GeoServer SQL views without time support
INSERT INTO sys_admin.published_services (
    schema, name, srid, key_column, view_def
) VALUES
    ('waterway', 'gauges_geoserver', 4326, 'isrs_code', $$
        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, 'id', $$
        SELECT
            s.id,
            s.name,
            (s.section).lower::varchar as lower,
            (s.section).upper::varchar as upper,
            s.area,
            s.objnam,
            s.nobjnam,
            s.country,
            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, 'id', $$
        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, 'id', $$
        SELECT id,
           country,
           geom,
           related_enc,
           hectom,
           catdis,
           position_code
        FROM waterway.distance_marks
    $$),
    ('waterway', 'distance_marks_geoserver', 4326, 'location', $$
        SELECT
           isrs_asText(location_code) AS location,
           geom,
           related_enc,
           (location_code).hectometre
        FROM waterway.distance_marks_virtual
    $$),
    ('waterway', 'sounding_results_areas_geoserver', 4326, NULL, $$
        SELECT bottleneck_id,
            to_char(date_info, 'YYYY-MM-DD') AS date_info,
            height,
            areas,
            surtyp,
            zpg_exception
        FROM waterway.sounding_results_iso_areas ia
            JOIN waterway.sounding_results sr ON sr.id = ia.sounding_result_id
    $$),
    ('waterway', 'sounding_results_marking_points_geoserver', 4326, NULL, $$
        SELECT bottleneck_id,
            to_char(date_info, 'YYYY-MM-DD') AS date_info,
            height,
            points::geometry(MULTIPOINTZ, 4326),
            surtyp,
            zpg_exception
        FROM waterway.sounding_results_marking_points mp
            JOIN waterway.sounding_results sr ON sr.id = mp.sounding_result_id
    $$),
    ('waterway', 'bottleneck_overview', 4326, NULL, $$
        SELECT
            objnam AS name,
            bn.bottleneck_id,
            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, NULL, $$
        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
    $$);


-- GeoServer SQL views with time support
INSERT INTO sys_admin.published_services (
    schema, name, srid, key_column,
    wmst_attribute, wmst_end_attribute,
    view_def
) VALUES
    ('waterway', 'bottlenecks_geoserver', 4326, 'id',
        'valid_from', 'valid_to', $$
        SELECT
            b.id,
            lower(b.validity) AS valid_from,
            COALESCE(upper(b.validity), current_timestamp) AS valid_to,
            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
    $$),
    ('waterway', 'fairway_dimensions', 4326, 'id',
        'valid_from', 'valid_to', $$
            SELECT id,
                lower(validity) AS valid_from,
                COALESCE(upper(validity), current_timestamp) AS valid_to,
                area,
                level_of_service,
                min_width,
                max_width,
                min_depth,
                date_info,
                source_organization,
                staging_done
            FROM waterway.fairway_dimensions
        $$),
    ('waterway', 'waterway_axis', 4326, 'id',
        'valid_from', 'valid_to', $$
        SELECT id,
            lower(validity) AS valid_from,
            COALESCE(upper(validity), current_timestamp) AS valid_to,
            wtwaxs,
            objnam,
            nobjnam
        FROM waterway.waterway_axis
        $$),
    ('waterway', 'fairway_marks_bcnlat_hydro', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            'colour, colpat, condtn, bcnshp, catlam', 'bcnlat_hydro')),
    ('waterway', 'fairway_marks_bcnlat_ienc', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            $$colour, colpat, condtn, bcnshp, catlam,
                string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$,
            $$bcnlat_ienc LEFT JOIN waterway.fairway_marks_bcnlat_dirimps
                ON id = fm_bcnlat_id GROUP BY id$$)),
    ('waterway', 'fairway_marks_boycar', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            'colour, colpat, conrad, marsys, boyshp, catcam', 'boycar')),
    ('waterway', 'fairway_marks_boylat_hydro', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_hydro')),
    ('waterway', 'fairway_marks_boylat_ienc', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_ienc')),
    ('waterway', 'fairway_marks_boysaw', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            'colour, colpat, conrad, marsys, boyshp', 'boysaw')),
    ('waterway', 'fairway_marks_boyspp', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            'colour, colpat, conrad, marsys, boyshp, catspm', 'boyspp')),
    ('waterway', 'fairway_marks_daymar_hydro', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            'colour, colpat, condtn, topshp', 'daymar_hydro')),
    ('waterway', 'fairway_marks_daymar_ienc', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            $$colour, colpat, condtn, topshp, orient,
                string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$,
            $$daymar_ienc LEFT JOIN waterway.fairway_marks_daymar_dirimps
                ON id = fm_daymar_id GROUP BY id$$)),
    ('waterway', 'fairway_marks_lights', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            $$colour, condtn, orient, catlit, exclit, litchr, litvis,
                mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status$$,
            'lights')),
    ('waterway', 'fairway_marks_rtpbcn', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            'condtn, siggrp, catrtb, radwal', 'rtpbcn')),
    ('waterway', 'fairway_marks_topmar', 4326, 'id',
        'valid_from', 'valid_to', format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            'colour, colpat, condtn, topshp', 'topmar')),
    ('waterway', 'fairway_marks_notmrk', 4326, 'id', 'valid_from', 'valid_to',
        format(
            (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
            $$condtn, marsys, orient, status, addmrk, catnmk,
                disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw,
                string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$,
            $$notmrk LEFT JOIN waterway.fairway_marks_notmrk_dirimps
                ON id = fm_notmrk_id GROUP BY id$$));

--
-- group layers
--
INSERT INTO sys_admin.layer_groups VALUES
  ('fairway_marks'),
  ('sounding_results');

INSERT INTO sys_admin.grouped_layers VALUES
  ('fairway_marks', 'waterway', 'fairway_marks_boylat_hydro', 0),
  ('fairway_marks', 'waterway', 'fairway_marks_boylat_ienc', 0),
  ('fairway_marks', 'waterway', 'fairway_marks_boycar', 1),
  ('fairway_marks', 'waterway', 'fairway_marks_bcnlat_hydro', 2),
  ('fairway_marks', 'waterway', 'fairway_marks_bcnlat_ienc', 2),
  ('fairway_marks', 'waterway', 'fairway_marks_boysaw', 3),
  ('fairway_marks', 'waterway', 'fairway_marks_boyspp', 4),
  ('fairway_marks', 'waterway', 'fairway_marks_daymar_hydro', 5),
  ('fairway_marks', 'waterway', 'fairway_marks_daymar_ienc', 5),
  ('fairway_marks', 'waterway', 'fairway_marks_lights', 6),
  ('fairway_marks', 'waterway', 'fairway_marks_rtpbcn', 7),
  ('fairway_marks', 'waterway', 'fairway_marks_topmar', 8),
  ('fairway_marks', 'waterway', 'fairway_marks_notmrk', 9),
  ('sounding_results', 'waterway', 'sounding_results_areas_geoserver', 0),
  ('sounding_results', 'waterway', 'sounding_results_marking_points_geoserver', 1);

--
-- Settings
--
INSERT INTO sys_admin.system_config VALUES ('ecdis_wms_url', 'https://service.d4d-portal.info/wms/');
INSERT INTO sys_admin.system_config VALUES ('ecdis_wms_params', '{"LAYERS": "d4d", "VERSION": "1.1.1", "TILED": true}');
INSERT INTO sys_admin.system_config VALUES ('bn_revtime_multiplier', 1.5);
INSERT INTO sys_admin.system_config VALUES ('gm_min_values_14d', 1224);
INSERT INTO sys_admin.system_config VALUES ('gm_latest_hours', 24);
INSERT INTO sys_admin.system_config VALUES ('gm_forecast_offset_24h', 15);
INSERT INTO sys_admin.system_config VALUES ('gm_forecast_offset_72h', 15);
INSERT INTO sys_admin.system_config VALUES ('gm_forecast_vs_reality_nsc_24h', -12.5);
INSERT INTO sys_admin.system_config VALUES ('gm_forecast_vs_reality_nsc_72h', -12.5);
INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks', '-2.5:#5A3300,-2,-1.5,-1,-0.5,0:#D8B050,0.5:#FF0000,1,1.5,2:#FF9999,2.5:#A6B8FA,3,3.5,4,4.5,5,6:#0101FF,7:#88DD55,8,9,10,11,12:#146E33');
INSERT INTO sys_admin.system_config VALUES ('morphology_classbreaks_compare', '-2:#06b100,-1.8,-1.6,-1.4,-1.2,-1:#1cc68e,-0.8,-0.6,-0.4,-0.2,0:#c2c2c2,0.2,0.4,0.6,0.8,1:#fff01a,1.2,1.4,1.6,1.8,2:#f80012');
INSERT INTO sys_admin.system_config VALUES ('bottlenecks_stroke','#fa28ff');
INSERT INTO sys_admin.system_config VALUES ('bottlenecks_fill','#ff25c424');
INSERT INTO sys_admin.system_config VALUES ('stretches_stroke','#fac800cc');
INSERT INTO sys_admin.system_config VALUES ('stretches_fill','#fac80a4d');
INSERT INTO sys_admin.system_config VALUES ('sections_stroke','#ff960acc');
INSERT INTO sys_admin.system_config VALUES ('sections_fill','#ff96004d');
INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos1_stroke','#0000ffcc');
INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos1_fill','#f0e60033');
INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos2_stroke','#0000ffe6');
INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos2_fill','#f0e6001a');
INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos3_stroke','#0000ff');
INSERT INTO sys_admin.system_config VALUES ('fairwaydimensionslos3_fill','#ffffff66');
INSERT INTO sys_admin.system_config VALUES ('waterwayprofiles_stroke','#0000ff80');

INSERT INTO sys_admin.system_config VALUES ('distance_marks_fill', '#ff99994d');
INSERT INTO sys_admin.system_config VALUES ('distance_marks_stroke', '#6666ff');
INSERT INTO sys_admin.system_config VALUES ('distance_marks_ashore_fill', '#8888ff1a');
INSERT INTO sys_admin.system_config VALUES ('distance_marks_ashore_stroke', '#3333ff');
INSERT INTO sys_admin.system_config VALUES ('waterway_axis_stroke', '#0000ff');
INSERT INTO sys_admin.system_config VALUES ('waterway_area_stroke', '#006600');

COMMIT;