-- 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 bottleneck
-- at any time of the referencing objects validity.  To ensure this we
-- need a trigger constraint:
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 = 23505,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
END;
$$
LANGUAGE plpgsql;

-- Redifine constraint trigger: sounding Results must intersect with
-- the area of the bottleneck they belong to.  Bottleneck is
-- determined dynamically via date_info.
CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT st_intersects((SELECT area
                          FROM waterway.bottlenecks
                          WHERE bottleneck_id = NEW.bottleneck_id
                            AND validity @> NEW.date_info::timestamptz),
                         NEW.area)
    THEN
        RAISE EXCEPTION
            'new row for relation "%" violates constraint trigger "%"',
                TG_TABLE_NAME, TG_NAME
            USING
                DETAIL = 'Failing row area has no intersection with bottleneck.',
                ERRCODE = 23514,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                CONSTRAINT = TG_NAME;
    END IF;
    RETURN NEW;
END;
$$;


--
-- SOUNDING RESULTS
--

-- Dynamic version of sounding_differences geoserver view: Instead of
-- a static reference to a specific bottleneck data set via primary
-- key (id, validity) we check for a bottleneck valid at the time of
-- the survey at executiuon time.
CREATE OR REPLACE VIEW waterway.sounding_differences AS
    SELECT
        sd.id           AS id,
        bn.objnam       AS objnam,
        srm.date_info   AS minuend,
        srs.date_info   AS subtrahend,
        sdcl.height     AS height,
        CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
    FROM caching.sounding_differences sd
        JOIN caching.sounding_differences_contour_lines sdcl
            ON sd.id = sdcl.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;

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

-- Note, we now use prefixed names, to ensure correct execution order
-- for the triggers...
DROP TRIGGER IF EXISTS a_sounding_results_reference_bottleneck
    ON waterway.sounding_results;
CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck
    AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results
    FOR EACH ROW
    EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info');

DROP TRIGGER IF EXISTS sounding_results_in_bn_area
    ON waterway.sounding_results;
DROP TRIGGER IF EXISTS b_sounding_results_in_bn_area
    ON waterway.sounding_results;
CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area
    AFTER INSERT OR UPDATE ON waterway.sounding_results
    FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area();


--
-- 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.bottlenecks;
DROP FUNCTION IF EXISTS move_bottleneck_referencing();