--- a/schema/gemma.sql	Mon Sep 16 16:56:11 2019 +0200
+++ b/schema/gemma.sql	Fri Sep 20 15:35:16 2019 +0200
@@ -4,7 +4,7 @@
 -- SPDX-License-Identifier: AGPL-3.0-or-later
 -- License-Filename: LICENSES/AGPL-3.0.txt
 
--- Copyright (C) 2018,2019 by via donau
+-- Copyright (C) 2018, 2019 by via donau
 --   – Österreichische Wasserstraßen-Gesellschaft mbH
 -- Software engineering by Intevation GmbH
 
@@ -256,59 +256,6 @@
 -- GEMMA data
 --
 
--- Namespace not to be accessed directly by any user
-CREATE SCHEMA internal
-    -- Profile data are only accessible via the view users.list_users.
-    CREATE TABLE user_profiles (
-        username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
-        -- keep username length compatible with role identifier
-        map_extent box2d NOT NULL,
-        email_address varchar NOT NULL
-    )
-    -- Columns referencing user-visible schemas added below.
-;
-
-
--- Namespace to be accessed by sys_admin only
-CREATE SCHEMA sys_admin
-    CREATE TABLE system_config (
-        config_key varchar PRIMARY KEY,
-        config_val varchar
-    )
-
-    CREATE TABLE password_reset_requests (
-        hash varchar(32) PRIMARY KEY,
-        issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        username varchar NOT NULL UNIQUE
-            REFERENCES internal.user_profiles(username)
-                ON DELETE CASCADE ON UPDATE CASCADE
-    )
-
-    -- Tables with geo data to be published with GeoServer.
-    CREATE TABLE external_services (
-        local_name varchar PRIMARY KEY,
-        remote_url varchar NOT NULL,
-        is_wfs     boolean NOT NULL DEFAULT TRUE
-    )
-
-    CREATE TABLE published_services (
-        schema varchar CHECK(to_regnamespace(schema) IS NOT NULL),
-        name varchar,
-        PRIMARY KEY (schema, name),
-        -- SQL statement used for an SQL view in GeoServer:
-        view_def text CHECK (is_valid_from_item(view_def)),
-        -- SRID to be used with SQL view:
-        srid int REFERENCES spatial_ref_sys,
-        -- SLD style document:
-        style xml CHECK(style IS DOCUMENT),
-        as_wms boolean NOT NULL DEFAULT TRUE,
-        as_wfs boolean NOT NULL DEFAULT TRUE,
-        -- Either give a valid relation or a SQL statement:
-        CHECK (to_regclass(schema || '.' || name) IS NOT NULL
-            OR view_def IS NOT NULL)
-    )
-;
-
 --
 -- Look-up tables with data that are static in a running system
 --
@@ -396,12 +343,86 @@
     'report'
 );
 
+
+-- Namespace not to be accessed directly by any user
+CREATE SCHEMA internal
+    -- Profile data are only accessible via the view users.list_users.
+    CREATE TABLE user_profiles (
+        username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
+        -- keep username length compatible with role identifier
+        country char(2) NOT NULL REFERENCES countries,
+        map_extent box2d NOT NULL,
+        email_address varchar NOT NULL
+    )
+    -- Columns referencing user-visible schemas added below.
+;
+
+
+-- Namespace to be accessed by sys_admin only
+CREATE SCHEMA sys_admin
+    CREATE TABLE system_config (
+        config_key varchar PRIMARY KEY,
+        config_val varchar
+    )
+
+    CREATE TABLE password_reset_requests (
+        hash varchar(32) PRIMARY KEY,
+        issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+        username varchar NOT NULL UNIQUE
+            REFERENCES internal.user_profiles(username)
+                ON DELETE CASCADE ON UPDATE CASCADE
+    )
+
+    -- Tables with geo data to be published with GeoServer.
+    CREATE TABLE external_services (
+        local_name varchar PRIMARY KEY,
+        remote_url varchar NOT NULL,
+        is_wfs     boolean NOT NULL DEFAULT TRUE
+    )
+
+    CREATE TABLE published_services (
+        schema varchar CHECK(to_regnamespace(schema) IS NOT NULL),
+        name varchar,
+        PRIMARY KEY (schema, name),
+        -- SQL statement used for an SQL view in GeoServer:
+        view_def text CHECK (is_valid_from_item(view_def)),
+        -- SRID to be used with SQL view:
+        srid int REFERENCES spatial_ref_sys,
+        -- SLD style document:
+        style xml CHECK(style IS DOCUMENT),
+        as_wms boolean NOT NULL DEFAULT TRUE,
+        as_wfs boolean NOT NULL DEFAULT TRUE,
+        -- Either give a valid relation or a SQL statement:
+        CHECK (to_regclass(schema || '.' || name) IS NOT NULL
+            OR view_def IS NOT NULL)
+    )
+;
+
+
 -- Namespace for user management related data
 CREATE SCHEMA users
-    CREATE TABLE responsibility_areas (
-        country char(2) PRIMARY KEY REFERENCES countries,
-        area geography(MULTIPOLYGON, 4326)
-            CHECK(ST_IsValid(CAST(area AS geometry)))
+    CREATE TABLE stretches (
+        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+        name varchar NOT NULL,
+        stretch isrsrange NOT NULL,
+        area geography(MULTIPOLYGON, 4326) NOT NULL
+            CHECK(ST_IsValid(CAST(area AS geometry))),
+        objnam varchar NOT NULL,
+        nobjnam varchar,
+        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+        source_organization varchar NOT NULL,
+        staging_done boolean NOT NULL DEFAULT false,
+        UNIQUE(name, staging_done)
+    )
+    CREATE TRIGGER stretches_date_info
+        BEFORE UPDATE ON stretches
+        FOR EACH ROW EXECUTE PROCEDURE update_date_info()
+
+    CREATE TABLE stretch_countries (
+        stretch_id int NOT NULL REFERENCES stretches(id)
+            ON DELETE CASCADE,
+        country char(2) NOT NULL REFERENCES countries,
+        PRIMARY KEY(stretch_id, country)
     )
 
     CREATE TABLE templates (
@@ -415,8 +436,7 @@
     CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 ;
-ALTER TABLE internal.user_profiles ADD
-    country char(2) NOT NULL REFERENCES users.responsibility_areas;
+
 
 -- Namespace for waterway data that can change in a running system
 CREATE SCHEMA waterway
@@ -502,9 +522,6 @@
     CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge
         AFTER INSERT OR UPDATE OF location ON gauge_measurements
         FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date')
-    -- For fast retrieval of newest measurement per location:
-    CREATE INDEX gauge_measurements_location_measure_date_desc
-        ON waterway.gauge_measurements (location, measure_date DESC)
 
     CREATE TABLE gauge_predictions (
         location isrs NOT NULL,
@@ -575,30 +592,6 @@
         check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
     )
 
-    CREATE TABLE stretches (
-        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-        name varchar NOT NULL,
-        stretch isrsrange NOT NULL,
-        area geography(MULTIPOLYGON, 4326) NOT NULL
-            CHECK(ST_IsValid(CAST(area AS geometry))),
-        objnam varchar NOT NULL,
-        nobjnam varchar,
-        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        source_organization varchar NOT NULL,
-        staging_done boolean NOT NULL DEFAULT false,
-        UNIQUE(name, staging_done)
-    )
-    CREATE TRIGGER stretches_date_info
-        BEFORE UPDATE ON stretches
-        FOR EACH ROW EXECUTE PROCEDURE update_date_info()
-
-    CREATE TABLE stretch_countries (
-        stretches_id int NOT NULL REFERENCES stretches(id)
-            ON DELETE CASCADE,
-        country_code char(2) NOT NULL REFERENCES countries(country_code),
-        UNIQUE(stretches_id, country_code)
-    )
-
     -- Like stretches without the countries
     CREATE TABLE sections (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
@@ -614,7 +607,7 @@
         UNIQUE(name, staging_done)
     )
     CREATE TRIGGER sections_date_info
-        BEFORE UPDATE ON stretches
+        BEFORE UPDATE ON sections
         FOR EACH ROW EXECUTE PROCEDURE update_date_info()
 
     CREATE TABLE waterway_profiles (