--- a/wamos.sql	Wed May 23 17:37:19 2018 +0200
+++ b/wamos.sql	Wed May 23 18:24:02 2018 +0200
@@ -56,6 +56,9 @@
        -- factor when there are many FKs pointing here.
        );
 
+--
+-- Bottlenecks
+--
 CREATE TABLE riverbed_materials (
        material varchar PRIMARY KEY
        -- XXX: Should this table contain choices from DRC 2.2.3 or
@@ -134,4 +137,61 @@
        sounding_data raster NOT NULL
        );
 
+--
+-- Fairway availability
+--
+CREATE TABLE spot_marks (
+       mark_name varchar PRIMARY KEY
+       -- Use smallint because of fairway availability provided on daily basis?
+       );
+
+CREATE TABLE levels_of_service (
+       level_of_service smallint PRIMARY KEY
+       );
+
+CREATE TABLE fairway_availability (
+       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+       surdat date NOT NULL,
+       PRIMARY KEY (bottleneck_id, surdat),
+       position varchar REFERENCES spot_marks,
+       -- additional_data xml -- Currently not relevant for WAMOS
+       critical boolean,
+       date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL
+       );
+CREATE TRIGGER fairway_availability_date_info
+       BEFORE UPDATE ON fairway_availability
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+
+CREATE TABLE fa_reference_values (
+       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+       surdat date NOT NULL,
+       FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
+       level_of_service smallint NOT NULL REFERENCES levels_of_service,
+       PRIMARY KEY (bottleneck_id, surdat, level_of_service),
+       fairway_depth smallint,
+       fairway_width smallint,
+       fairway_radius int,
+       CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
+       shallowest_spot geometry(POINT, 3146)
+       );
+
+CREATE TABLE bottleneck_pdfs (
+       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+       surdat date NOT NULL,
+       FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,
+       profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
+       profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
+       PRIMARY KEY (bottleneck_id, surdat, profile_pdf_url),
+       pdf_generation_date timestamp NOT NULL,
+       source_organization varchar NOT NULL
+       );
+
+CREATE TABLE effective_fairway_availability (
+       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+       surdat date NOT NULL,
+       FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability
+       -- TODO
+       );
+
 COMMIT;