-- 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
--
-- The only change in the following functions is the error code
-- returend in case of failure: it should be
--   23503: foreign_key_violation
-- as what we are emulating is kind of an foreign key...

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 = 23503,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
END;
$$
LANGUAGE plpgsql;

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 = 23503,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS
$$
DECLARE
    referenced_bottleneck_id text;
    new_tstz timestamptz;
BEGIN
    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
        INTO referenced_bottleneck_id
        USING NEW;
    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
        INTO new_tstz
        USING NEW;
    IF EXISTS ( SELECT * FROM waterway.bottlenecks
                  WHERE bottleneck_id = referenced_bottleneck_id
                  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 bottleneck %s for %s found.',
                    referenced_bottleneck_id, new_tstz),
                ERRCODE = 23503,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
END;
$$
LANGUAGE plpgsql;