# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1527878586 -7200
# Node ID 611dc09dbcd8aec28d6caddcf6498b63b21a131b
# Parent  765906789840d91467a386cdf296af1323814b5d
Add gauge measurement attributes.

diff -r 765906789840 -r 611dc09dbcd8 wamos.sql
--- a/wamos.sql	Fri Jun 01 20:25:21 2018 +0200
+++ b/wamos.sql	Fri Jun 01 20:43:06 2018 +0200
@@ -58,14 +58,14 @@
        area geometry(POLYGON, 4326)
        );
 
-CREATE TABLE wamos.languages (
+CREATE TABLE wamos.language_codes (
        language_code varchar PRIMARY KEY
        );
 
 CREATE TABLE wamos.user_profiles (
        username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
        country char(2) NOT NULL REFERENCES countries,
-       language_code varchar REFERENCES languages,
+       language_code varchar REFERENCES language_codes,
        map_extent box2d,
        email_adress varchar NOT NULL UNIQUE
        );
@@ -162,13 +162,29 @@
 
 CREATE TABLE wamos_fairway.gauge_measurements (
        fk_gauge_id isrs NOT NULL REFERENCES gauges,
+       measure_date timestamp with time zone NOT NULL,
+       PRIMARY KEY (fk_gauge_id, measure_date),
        -- XXX: Is country_code really relevant for WAMOS or just NtS?
        -- country_code char(2) NOT NULL REFERENCES countries,
        -- TODO: add relations to stuff provided as enumerations
-       dummy_attrib varchar,
-       "..." varchar
-       -- TODO: add real gauge measurement attributes (DRC 2.1.5)
+       sender varchar NOT NULL, -- "from" attribute from DRC
+       language_code varchar NOT NULL REFERENCES language_codes,
+       date_issue timestamp with time zone NOT NULL,
+       reference_code varchar(4) NOT NULL REFERENCES depth_references,
+       -- XXX: Always ZPG?
+       water_level double precision NOT NULL,
+       predicted boolean NOT NULL,
+       is_waterlevel boolean NOT NULL,
+       -- XXX: "measure_code" if really only W or Q
+       -- XXX: Do we need "unit" attribute or can we normalise on import?
+       value_min double precision, -- XXX: NOT NULL if predicted?
+       value_max double precision, -- XXX: NOT NULL if predicted?
+       --- TODO: Add a double range type for checking?
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL -- "originator"
        );
+CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
 
 CREATE TABLE wamos_waterway.waterway_axis (
        wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,