-- 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();


--
-- FAIRWAY AVAILABILITY
--

-- The current implementation associates fairway_availability data
-- hard with the internal id of an bottleneck.  Lets use the
-- bottleneck_id (official unique id) instead and to the lookup of an
-- matching bottleneck for a specific date on demand.

-- Dear reader: The gemma schema update scripts are intended to be run
--   only once, which is implemented via schema verioning and the
--   update-db.sh script.  None the less it is very helpful to be able
--   to run scripts more than once during development and testing,
--   without harm being done.  Thats the reason for the following code
--   to be, like it is:
--
-- This migration can only be done once, thanks to psql magic we are
-- able to guard it approprieatly.

SELECT data_type='integer' AS old_fwa_bnid
    FROM information_schema.columns
    WHERE table_schema = 'waterway'
        AND table_name='fairway_availability'
        AND column_name='bottleneck_id';
\gset
\if :old_fwa_bnid
  \qecho 'Migrating bottleneck_id column in fairway_availability to text id.'
  -- We temporarily keep the old if field for the migration of existing data:
  ALTER TABLE waterway.fairway_availability
      RENAME COLUMN bottleneck_id TO old_bnid;
  ALTER TABLE waterway.fairway_availability
      ADD COLUMN bottleneck_id varchar;

  -- Migrate existing data: Let's be ultra permissive and make the
  -- migration _before adding the constraint triggger.
  UPDATE waterway.fairway_availability AS fwa
      SET bottleneck_id = b.bottleneck_id
      FROM waterway.bottlenecks b
      WHERE b.id = fwa.old_bnid;
  ALTER TABLE waterway.fairway_availability
      ALTER COLUMN bottleneck_id SET NOT NULL;

  -- Set constraint trigger to make sure a matching BN exists:
  --
  -- FIXME: From the DRC it is unclear what the exact semantics of
  --   surdat and Date_Info ar unclear.  Currently we assume that
  --   (fk_bn_fid,surdat) has to be unique, but that might be false.
  --   Anyway, I will date_info here to check for an matching
  --   reference gauge at the bottleneck.  The reason for this
  --   decision is purely practical (and might be semantically
  --   disputable: the bottleneck data in the demo system is not old
  --   enough to cover rthe surdat times...
  CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck
      AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.fairway_availability
      FOR EACH ROW
      EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info');
  -- Set NOT NULL constraint for new column
  ALTER TABLE waterway.fairway_availability
      ALTER COLUMN bottleneck_id SET NOT NULL;

  -- The change also effects the geoserver bottlenecks view, which
  -- joined in fairway_availability.  We leave the rather fuzzy match
  -- (not using any validity time match), as it is unclear what the
  -- validity period of fwa data is.
  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.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;

  -- Finally dropt the old column
  ALTER TABLE waterway.fairway_availability
      DROP COLUMN old_bnid;
  ALTER TABLE waterway.fairway_availability
      ADD CONSTRAINT fairway_availability_bottleneck_id_surdat_key
      UNIQUE (bottleneck_id, surdat);
\else
  \qecho 'NOTICE: bottleneck_id column in fairway_availability alread migrated.'
\endif


--
-- 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();