# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1527784757 -7200
# Node ID c71e43f88ae03501010d2761f2813e06c2ee26ae
# Parent  acaa485c0c1eda45cb76a91727537362ba621253
Add auxilliary tables.

diff -r acaa485c0c1e -r c71e43f88ae0 wamos.sql
--- a/wamos.sql	Thu May 31 17:54:32 2018 +0200
+++ b/wamos.sql	Thu May 31 18:39:17 2018 +0200
@@ -41,13 +41,45 @@
 --
 -- Auxilliary tables
 --
+CREATE TABLE wamos.system_config (
+       config_key varchar PRIMARY KEY,
+       config_val varchar
+       );
 
--- TODO: Add table to manage users e-mail adresses (APUC9)
--- TODO: Add table for responsibility areas and link to users
--- TODO: Store reports/statistics templates per user (APUC6, SPUC13)
+CREATE TABLE wamos.countries (
+       country_code char(2) PRIMARY KEY -- ISO 3166 country code
+       -- A smallint PK would require even less disk space i.e. on the FK side.
+       -- This might be an issue in case cache space becomes a limiting
+       -- factor when there are many FKs pointing here.
+       );
+
+CREATE TABLE wamos.responsibility_areas (
+       country char(2) PRIMARY KEY REFERENCES countries,
+       area geometry(POLYGON, 4326)
+       );
 
--- TODO: Do we need to store system config (APUC10) in database?
--- TODO: Will we store any pre-calculated cross sections in database (SPUC3)?
+CREATE TABLE wamos.user_profiles (
+       username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
+       country char(2) REFERENCES countries,
+       email_adress varchar NOT NULL UNIQUE
+       );
+
+CREATE TABLE wamos.templates (
+       id int PRIMARY KEY,
+       dummy_attrib varchar,
+       "..." varchar
+       -- TODO: template attributes tbd.
+       );
+
+CREATE TABLE wamos.user_templates (
+       username varchar NOT NULL REFERENCES user_profiles,
+       template_id int NOT NULL REFERENCES templates,
+       PRIMARY KEY (username, template_id)
+       );
+
+--
+-- General river information
+--
 
 -- Eventually obsolete.
 -- See https://roundup-intern.intevation.de/wamos/issue5
@@ -64,16 +96,6 @@
 --        EXCLUDE USING GIST (stretch WITH &&)
 --        );
 
-CREATE TABLE wamos.countries (
-       country_code char(2) PRIMARY KEY -- ISO 3166 country code
-       -- A smallint PK would require even less disk space i.e. on the FK side.
-       -- This might be an issue in case cache space becomes a limiting
-       -- factor when there are many FKs pointing here.
-       );
-
---
--- General river information
---
 CREATE TABLE wamos_waterway.waterway_area (
        dummy_attrib varchar,
        "..." varchar