-- 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) 2019 by via donau
--   – Österreichische Wasserstraßen-Gesellschaft mbH
-- Software engineering by Intevation GmbH

-- Author(s):
--  * Sascha Wilde <sascha.wilde@intevation.de>

--
-- CONSTRAINT FUNCTIONS
--

-- We still want to ensure, that there is at least a valid gauge at
-- any time of the referencing objects validity.  To ensure this we
-- need a trigger constraint:
CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS
$$
DECLARE
    -- FIXME: I'm using text for the isrs code and cast it on demand.
    -- If someone is able to get it to work with isrs or isrs_base as
    -- type, feel free to show me how its done...  ;-) [sw]
    referenced_gauge text;
    new_validity tstzrange;
BEGIN
    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
        INTO referenced_gauge
        USING NEW;
    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
        INTO new_validity
        USING NEW;
    IF EXISTS ( SELECT * FROM waterway.gauges
                  WHERE location = referenced_gauge::isrs
                  AND validity && new_validity )
    THEN
        RETURN NEW;
    ELSE
        RAISE EXCEPTION
            'new row for relation "%" violates constraint trigger "%"',
                TG_TABLE_NAME, TG_NAME
            USING
                DETAIL = format('No matching gauge %s found.',
                    isrs_AsText(referenced_gauge::isrs)),
                ERRCODE = 23505,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
END;
$$
LANGUAGE plpgsql;

-- The same for objects with a timestamp instead of a validity range.
CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS
$$
DECLARE
    -- FIXME: I'm using text for the isrs code and cast it on demand.
    -- If someone is able to get it to work with isrs or isrs_base as
    -- type, feel free to show me how its done...  ;-) [sw]
    referenced_gauge text;
    new_tstz timestamptz;
BEGIN
    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
        INTO referenced_gauge
        USING NEW;
    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
        INTO new_tstz
        USING NEW;
    IF EXISTS ( SELECT * FROM waterway.gauges
                  WHERE location = referenced_gauge::isrs
                  AND validity @> new_tstz )
    THEN
        RETURN NEW;
    ELSE
        RAISE EXCEPTION
            'new row for relation "%" violates constraint trigger "%"',
                TG_TABLE_NAME, TG_NAME
            USING
                DETAIL = format('No matching gauge %s for %s found.',
                    (isrs_AsText(referenced_gauge::isrs)), new_tstz),
                ERRCODE = 23505,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
END;
$$
LANGUAGE plpgsql;


--
-- BOTTLENECKS
--

-- Dynamic version of bottlenecks_geoserver view:
-- Instead of a static reference to a specific gauge data set via
-- primary key (location, validity) we check for a currently
-- valid gauge (for the currently valid bottleneck) at executiuon
-- time.
CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS
    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 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.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;

-- As we resolve the correct gauge data to use on runtime, we drop the
-- hard reference to the gauges vaidity:
ALTER TABLE waterway.bottlenecks DROP IF EXISTS gauge_validity;

DROP TRIGGER IF EXISTS waterway_bottlenecks_reference_gauge
    ON waterway.bottlenecks;
CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
    AFTER INSERT OR UPDATE OF gauge_location ON waterway.bottlenecks
    FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity');


--
-- GAUGE MEASUREMENTS
--

-- As we resolve the correct gauge data to use on runtime, we drop the
-- hard reference to the gauges vaidity:
ALTER TABLE waterway.gauge_measurements DROP IF EXISTS validity;

DROP TRIGGER IF EXISTS waterway_gauge_measurements_reference_gauge
    ON waterway.gauge_measurements;
CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge
    AFTER INSERT OR UPDATE OF location ON waterway.gauge_measurements
    FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date');

ALTER TABLE waterway.gauge_predictions DROP IF EXISTS validity;

DROP TRIGGER IF EXISTS waterway_gauge_predictions_reference_gauge
    ON waterway.gauge_predictions;
CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge
    AFTER INSERT OR UPDATE OF location ON waterway.gauge_predictions
    FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date');

--
-- DROP NO LONGER NEEDED TRIGGER FUNCTION
--

-- This used to update foreign key references.  As these references no
-- longer exist we dont need this magic any more...
DROP TRIGGER IF EXISTS move_referencing ON waterway.gauges;
DROP FUNCTION IF EXISTS move_gauge_referencing();


--
-- ADDITIONAL NOTES
--
-- waterway.gauges_reference_water_levels still has a hard foreign key
-- reference to waterway.gauges.  As this data actually is part of the
-- gauges data and the seperation in two tables is exclusively due to
-- schema modeling we leave it alone!