--- a/schema/gemma.sql	Sat Jun 15 09:24:28 2019 +0200
+++ b/schema/gemma.sql	Sat Jun 15 14:36:50 2019 +0200
@@ -76,6 +76,9 @@
 -- associating time-based referencing objects to matching version
 CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS
 $$
+DECLARE
+    new_bn int;
+    new_bns int[];
 BEGIN
     -- Avoid unnecessary execution ON UPDATE if validity did not change
     IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
@@ -84,10 +87,63 @@
         WHERE location = NEW.location
             AND measure_date <@ NEW.validity;
 
-        UPDATE waterway.bottlenecks
-        SET gauge_validity = NEW.validity
+        -- build bottleneck validities from intersections with gauge validities
+        FOR new_bn IN
+            INSERT INTO waterway.bottlenecks (
+                    bottleneck_id,
+                    validity,
+                    gauge_location,
+                    gauge_validity,
+                    objnam,
+                    nobjnm,
+                    stretch,
+                    area,
+                    rb,
+                    lb,
+                    responsible_country,
+                    revisiting_time,
+                    limiting,
+                    date_info,
+                    source_organization,
+                    staging_done
+                ) SELECT
+                    b.bottleneck_id,
+                    -- Anticipate non-intersecting gauge validities:
+                    b.validity * CASE WHEN g.validity = NEW.validity
+                        THEN NEW.validity ELSE g.validity - NEW.validity END,
+                    b.gauge_location,
+                    g.validity,
+                    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,
+                    b.staging_done
+                FROM waterway.bottlenecks b JOIN waterway.gauges g
+                    ON b.gauge_location = g.location
+                WHERE b.gauge_location = NEW.location
+                    AND b.validity && NEW.validity
+                    -- Avoid duplicate intersection results:
+                    AND NOT (b.validity <@ NEW.validity
+                        AND g.validity <> NEW.validity)
+            ON CONFLICT (bottleneck_id, validity) DO UPDATE SET
+                -- Associate to new matching gauge version
+                gauge_validity = EXCLUDED.gauge_validity
+            RETURNING id
+        LOOP
+            new_bns = new_bns || new_bn;
+        END LOOP;
+        -- Delete bottleneck versions superseded by new intersections:
+        DELETE FROM waterway.bottlenecks
         WHERE gauge_location = NEW.location
-            AND lower(validity) <@ NEW.validity;
+            AND validity && NEW.validity
+            AND id <> ALL(new_bns);
     END IF;
     RETURN NULL; -- ignored
 END;
@@ -103,6 +159,19 @@
         SET bottleneck_validity = NEW.validity
         WHERE bottleneck_id = NEW.bottleneck_id
           AND CAST(date_info AS timestamptz) <@ NEW.validity;
+
+        -- Always associate fairway availability data to newest bottleneck
+        -- version to prevent problems in analysis over longer time periods
+        WITH
+        bn AS (SELECT id, validity FROM waterway.bottlenecks
+            WHERE bottleneck_id = NEW.bottleneck_id),
+        latest AS (SELECT id FROM bn
+            -- Candidates are past new validity or just inserted/updated
+            WHERE NOT validity &< NEW.validity OR id = NEW.id
+            ORDER BY upper(validity) DESC FETCH FIRST ROW ONLY)
+        UPDATE waterway.fairway_availability
+        SET bottleneck_id = (SELECT id FROM latest)
+        WHERE bottleneck_id IN(SELECT id FROM bn EXCEPT SELECT id FROM latest);
     END IF;
     RETURN NULL; -- ignored
 END;
@@ -534,7 +603,7 @@
             DEFERRABLE INITIALLY DEFERRED,
         gauge_location isrs NOT NULL,
         gauge_validity tstzrange NOT NULL,
-        CHECK(lower(validity) <@ gauge_validity),
+        CHECK(validity <@ gauge_validity),
         CONSTRAINT gauge_key
             FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges
                 ON UPDATE CASCADE,
@@ -558,13 +627,8 @@
         -- XXX: Also an attribut of sounding result?
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL,
-        erased boolean NOT NULL DEFAULT false,
         staging_done boolean NOT NULL DEFAULT false
     )
-    -- Allow only one non-erased entry per bottleneck
-    CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint
-        ON bottlenecks (bottleneck_id)
-        WHERE NOT erased
     -- Associate referencing objects to matching bottleneck version
     CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
         ON bottlenecks FOR EACH ROW