--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1437/01.historicise_fairway_dimensions.sql	Tue Mar 31 18:59:28 2020 +0200
@@ -0,0 +1,30 @@
+ALTER TABLE waterway.fairway_dimensions
+    ADD validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
+        CHECK (NOT isempty(validity));
+
+-- Assume existing entries have been valid since last accepted import
+WITH imps AS (
+    SELECT changed, CAST(summary AS jsonb)->'fd-area' AS fd_area
+        FROM import.imports
+        WHERE kind = 'fd' AND state = 'accepted'
+)
+UPDATE waterway.fairway_dimensions fd SET validity = tstzrange(
+    COALESCE(
+        (SELECT max(changed)
+            FROM imps
+            WHERE fd.id IN(
+                SELECT id
+                FROM jsonb_to_recordset(fd_area)
+                    AS fd_area (id bigint, lat numeric, lon numeric))),
+        current_timestamp),
+    NULL);
+
+DROP TRIGGER fairway_dimensions_area_unique ON waterway.fairway_dimensions;
+CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique
+    AFTER INSERT OR UPDATE OF area, validity, staging_done
+    ON waterway.fairway_dimensions
+    FOR EACH ROW EXECUTE FUNCTION prevent_st_equals(
+        'area', 'validity WITH &&', 'staging_done');
+
+CREATE INDEX fairway_dimensions_validity
+    ON waterway.fairway_dimensions USING GiST (validity);