--- a/schema/gemma.sql	Wed Jul 24 18:55:09 2019 +0200
+++ b/schema/gemma.sql	Thu Jul 25 12:33:19 2019 +0200
@@ -724,7 +724,7 @@
     CREATE TABLE fairway_availability (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         position_code char(2) REFERENCES position_codes,
-        bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
+        bottleneck_id varchar NOT NULL,
         surdat date NOT NULL,
         UNIQUE (bottleneck_id, surdat),
         -- additional_data xml -- Currently not relevant for GEMMA
@@ -735,6 +735,18 @@
     CREATE TRIGGER fairway_availability_date_info
         BEFORE UPDATE ON fairway_availability
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
+    -- 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 fairway_availability
+        FOR EACH ROW
+        EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info')
 
     CREATE TABLE fa_reference_values (
         fairway_availability_id int NOT NULL REFERENCES fairway_availability,

--- a/schema/geoserver_views.sql	Wed Jul 24 18:55:09 2019 +0200
+++ b/schema/geoserver_views.sql	Thu Jul 25 12:33:19 2019 +0200
@@ -126,7 +126,7 @@
                     bottleneck_id, date_info, critical
                 FROM waterway.fairway_availability
                 ORDER BY bottleneck_id, date_info DESC) AS fal
-            ON b.id = fal.bottleneck_id
+            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

--- a/schema/updates/1100/02.remove_bottleneck_validity_refs.sql	Wed Jul 24 18:55:09 2019 +0200
+++ b/schema/updates/1100/02.remove_bottleneck_validity_refs.sql	Thu Jul 25 12:33:19 2019 +0200
@@ -130,6 +130,116 @@
 
 
 --
+-- 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;
+  -- 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');
+
+  -- Migrate existing data:
+  UPDATE waterway.fairway_availability AS fwa
+      SET bottleneck_id = b.bottleneck_id
+      FROM waterway.bottlenecks b
+      WHERE b.id = fwa.old_bnid;
+  -- 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;
+\else
+  \qecho 'NOTICE: bottleneck_id column in fairway_availability alread migrated.'
+\endif
+
+
+--
 -- DROP NO LONGER NEEDED TRIGGER FUNCTION
 --