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
       -- 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
       );

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 char(3) PRIMARY KEY
       -- Possible codes? char(3) ok?
       );

CREATE TABLE bottlenecks (
       bottleneck_id varchar PRIMARY KEY,
       -- XXX: Why encoding three different data in the ID as described in
       -- DRC?
       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,
       rb_lb varchar, -- XXX: Why two data in one attribute? Why not:
       rb char(2) NOT NULL REFERENCES countries,
       lb char(2) NOT NULL REFERENCES countries,
       riverbed varchar NOT NULL REFERENCES riverbed_materials,
       -- XXX: list of materials differs between IENC Encoding Guide and DRC
       responsible_country char(2) NOT NULL REFERENCES countries,
       revisiting_time smallint NOT NULL,
       surtyp varchar NOT NULL REFERENCES survey_types,
       -- XXX: Not an attribut of sounding result?
       coverage varchar NOT NULL REFERENCES coverage_types,
       -- XXX: Not an attribut of sounding result?
       -- CHECK allowed combinations of surtyp and coverage or
       -- different model approach?
       limiting varchar NOT NULL REFERENCES limiting_factors,
       depth_reference char(3) NOT NULL REFERENCES depth_references,
       -- XXX: Not an attribut of sounding result?
       date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
       source_organization varchar NOT NULL,
       additional_data xml -- XXX: What is that really for?
       );
CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
       FOR EACH ROW EXECUTE PROCEDURE update_date_info();

CREATE TABLE vertical_references (
       vertical_reference varchar PRIMARY KEY
       );

CREATE TABLE sounding_results (
       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
       date_info date NOT NULL,
       PRIMARY KEY (bottleneck_id, date_info),
       area int, -- XX: Check SRS/test data what this should be
       type int, -- XX: Check SRS/test data what this should be
       vertical_reference varchar NOT NULL REFERENCES vertical_references,
       sounding_data raster NOT NULL
       );

COMMIT;