BEGIN;

CREATE EXTENSION postgis;

CREATE FUNCTION update_date_info() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
        NEW.date_info = CURRENT_TIMESTAMP;
        RETURN NEW;
    END;
$$;

-- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
-- See RIS-Index Encoding Guide
CREATE TYPE isrs AS (
       country_code char(2), -- ISO 3166 country code
       -- could be validated against countries table.
       locode char(3), -- without the country code:
       -- http://www.unece.org/cefact/locode/welcome.html
       fairway_section char(5),
       object_reference char(5),
       hectometre int -- should be constrained to five digits
);

CREATE TYPE isrsrange AS RANGE (
       subtype = isrs
       );

CREATE TABLE rwdrs (
       stretch isrsrange PRIMARY KEY,
       -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
       -- Only B-tree supports UNIQUE indexes, but we need the GIST index
       -- below anyhow.
       -- Is it a good idea to build B-tree indexes on relatively large
       -- composites of string values or should we use inter PKs?
       -- => In case the index is used and cache space becomes a limiting
       -- factor, this might be an issue.
       rwdr double precision NOT NULL,
       EXCLUDE USING GIST (stretch WITH &&)
       );

CREATE TABLE gauges (
       location isrs PRIMARY KEY,
       dummy_attrib varchar,
       "..." varchar
       -- TODO: add real gauge attributes (DRC 2.1.4)
       );

CREATE TABLE 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 riverbed_materials (
       material varchar PRIMARY KEY
       -- XXX: Should this table contain choices from DRC 2.2.3 or
       -- from IENC Encoding Guide M.4.3, attribute NATSUR?
       );

CREATE TABLE survey_types (
       survey_type varchar PRIMARY KEY
       );

CREATE TABLE coverage_types (
       coverage_type varchar PRIMARY KEY
       );

CREATE TABLE limiting_factors (
       limiting_factor varchar PRIMARY KEY
       );

CREATE TABLE depth_references (
       depth_reference varchar(4) PRIMARY KEY
       -- See col. AB and AI RIS-Index Encoding Guide
       -- XXX: We need a way to distinguish between geodetic (eg. col. AP
       -- RIS-Index) and other references (e.g. col. AB and AI):
       -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
       --   i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
       -- _ Do not mixup things with different meanings in one table at all
       --   (which would mean a model differing a bit from RIS-Index ideas)
       );

CREATE TABLE bottlenecks (
       bottleneck_id varchar PRIMARY KEY,
       fk_g_fid isrs REFERENCES gauges,
       -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
       objnam varchar UNIQUE NOT NULL,
       nobjnm varchar UNIQUE,
       stretch isrsrange NOT NULL,
       area geometry(POLYGON, 3146) NOT NULL,
       rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
       lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
       responsible_country char(2) NOT NULL REFERENCES countries,
       revisiting_time smallint NOT NULL,
       limiting varchar NOT NULL REFERENCES limiting_factors,
       surtyp varchar NOT NULL REFERENCES survey_types,
       -- XXX: Also an attribut of sounding result?
       coverage varchar NOT NULL REFERENCES coverage_types,
       -- XXX: Also an attribut of sounding result?
       -- CHECK allowed combinations of surtyp and coverage or
       -- different model approach?
       depth_reference char(3) NOT NULL REFERENCES depth_references,
       -- XXX: Also an attribut of sounding result?
       date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
       source_organization varchar NOT NULL
       -- additional_data xml -- Currently not relevant for WAMOS
       );
CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
       FOR EACH ROW EXECUTE PROCEDURE update_date_info();

CREATE TABLE bottlenecks_riverbed_materials (
       bottleneck_id varchar REFERENCES bottlenecks,
       riverbed varchar REFERENCES riverbed_materials,
       -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
       PRIMARY KEY (bottleneck_id, riverbed)
       );

CREATE TABLE sounding_results (
       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
       date_info date NOT NULL,
       PRIMARY KEY (bottleneck_id, date_info),
       area geometry(POLYGON, 3146) NOT NULL,
       surtyp varchar NOT NULL REFERENCES survey_types,
       coverage varchar NOT NULL REFERENCES coverage_types,
       depth_reference char(3) NOT NULL REFERENCES depth_references,
       sounding_data raster NOT NULL
       );

COMMIT;