-- This is Free Software under GNU Affero General Public License v >= 3.0
-- without warranty, see README.md and license for details.

-- SPDX-License-Identifier: AGPL-3.0-or-later
-- License-Filename: LICENSES/AGPL-3.0.txt

-- Copyright (C) 2018, 2019 by via donau
--   – Österreichische Wasserstraßen-Gesellschaft mbH
-- Software engineering by Intevation GmbH

-- Author(s):
--  * Tom Gottfried <tom@intevation.de>
--  * Sascha Teichmann <sascha.teichmann@intevation.de>
--  * Sascha Wilde <sascha.wilde@intevation.de>

BEGIN;

--
-- Extensions
--
CREATE EXTENSION postgis;
-- needed for multi-column GiST indexes with otherwise unsupported types:
CREATE EXTENSION btree_gist;

--
-- Functions to be used in CHECK constraints
--

-- Check if a given string can be used as a FROM item in an SQL statement
CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean
AS $$
BEGIN
    EXECUTE format('SELECT * FROM (%s) AS test', stmt);
    RETURN true;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE;
        RETURN false;
END
$$
    LANGUAGE plpgsql
    STRICT;


--
-- Trigger functions
--
-- TODO: will there ever be UPDATEs or can we drop that function due to
-- historicisation?
CREATE FUNCTION update_date_info() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
        NEW.date_info = CURRENT_TIMESTAMP;
        RETURN NEW;
    END;
$$;

-- Trigger function to be used as a constraint trigger to enforce uniqueness
-- of geometries in the column with its name given as an argument to the
-- trigger function
CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
$$
DECLARE
    new_geom geometry;
    has_equal boolean;
BEGIN
    EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
        INTO new_geom
        USING NEW;
    EXECUTE format(
            'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I '
                'WHERE id <> $2',
            TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME)
        INTO has_equal
        USING new_geom, NEW.id;
    IF has_equal THEN
        RAISE EXCEPTION
            'new row for relation "%" violates constraint trigger "%"',
                TG_TABLE_NAME, TG_NAME
            USING
                DETAIL = format('Failing row contains geometry in %s',
                    Box2D(new_geom)),
                ERRCODE = 23505,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

-- Trigger function to be used as a constraint trigger to enforce
-- existance of a referenced gauge with intersecting validity.  The
-- columns with the referenced gauge isrs code an the validity are
-- given as arguments to the trigger function.
CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS
$$
DECLARE
    -- FIXME: I'm using text for the isrs code and cast it on demand.
    -- If someone is able to get it to work with isrs or isrs_base as
    -- type, feel free to show me how its done...  ;-) [sw]
    referenced_gauge text;
    new_validity tstzrange;
BEGIN
    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
        INTO referenced_gauge
        USING NEW;
    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
        INTO new_validity
        USING NEW;
    IF EXISTS ( SELECT * FROM waterway.gauges
                  WHERE location = referenced_gauge::isrs
                  AND validity && new_validity )
    THEN
        RETURN NEW;
    ELSE
        RAISE EXCEPTION
            'new row for relation "%" violates constraint trigger "%"',
                TG_TABLE_NAME, TG_NAME
            USING
                DETAIL = format('No matching gauge %s found.',
                    isrs_AsText(referenced_gauge::isrs)),
                ERRCODE = 23503,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
END;
$$
LANGUAGE plpgsql;

-- The same for objects with a timestamp instead of a validity range.
CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS
$$
DECLARE
    -- FIXME: I'm using text for the isrs code and cast it on demand.
    -- If someone is able to get it to work with isrs or isrs_base as
    -- type, feel free to show me how its done...  ;-) [sw]
    referenced_gauge text;
    new_tstz timestamptz;
BEGIN
    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
        INTO referenced_gauge
        USING NEW;
    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
        INTO new_tstz
        USING NEW;
    IF EXISTS ( SELECT * FROM waterway.gauges
                  WHERE location = referenced_gauge::isrs
                  AND validity @> new_tstz )
    THEN
        RETURN NEW;
    ELSE
        RAISE EXCEPTION
            'new row for relation "%" violates constraint trigger "%"',
                TG_TABLE_NAME, TG_NAME
            USING
                DETAIL = format('No matching gauge %s found.',
                    isrs_AsText(referenced_gauge::isrs)),
                ERRCODE = 23503,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
END;
$$
LANGUAGE plpgsql;

-- Trigger function to be used as a constraint trigger to enforce
-- existance of a referenced bottleneck with validity at a given time.
-- The columns with the referenced bottleneck id and the timestamp are
-- given as arguments to the trigger function.
CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS
$$
DECLARE
    referenced_bottleneck_id text;
    new_tstz timestamptz;
BEGIN
    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
        INTO referenced_bottleneck_id
        USING NEW;
    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
        INTO new_tstz
        USING NEW;
    IF EXISTS ( SELECT * FROM waterway.bottlenecks
                  WHERE bottleneck_id = referenced_bottleneck_id
                  AND validity @> new_tstz )
    THEN
        RETURN NEW;
    ELSE
        RAISE EXCEPTION
            'new row for relation "%" violates constraint trigger "%"',
                TG_TABLE_NAME, TG_NAME
            USING
                DETAIL = format('No matching bottleneck %s for %s found.',
                    referenced_bottleneck_id, new_tstz),
                ERRCODE = 23503,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                COLUMN = TG_ARGV[0],
                CONSTRAINT = TG_NAME;
    END IF;
END;
$$
LANGUAGE plpgsql;

-- Constraint trigger: sounding Results must intersect with the area
-- of the bottleneck they belong to.  The "xx" at the beginning of the
-- name is to ensure, it is fired last after other triggers.
CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NOT st_intersects((SELECT area
                          FROM waterway.bottlenecks
                          WHERE bottleneck_id = NEW.bottleneck_id
                            AND validity @> NEW.date_info::timestamptz),
                         NEW.area)
    THEN
        RAISE EXCEPTION
            'new row for relation "%" violates constraint trigger "%"',
                TG_TABLE_NAME, TG_NAME
            USING
                DETAIL = 'Failing row area has no intersection with bottleneck.',
                ERRCODE = 23514,
                SCHEMA = TG_TABLE_SCHEMA,
                TABLE = TG_TABLE_NAME,
                CONSTRAINT = TG_NAME;
    END IF;
    RETURN NEW;
END;
$$;


--
-- GEMA meta data
--
CREATE TABLE gemma_schema_version (
    version int PRIMARY KEY,
    update_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION get_schema_version() RETURNS int
    LANGUAGE sql
    AS $$
    SELECT max(version) FROM public.gemma_schema_version;
$$;


--
-- GEMMA data
--

--
-- Look-up tables with data that are static in a running system
--
CREATE TABLE language_codes (
    language_code varchar PRIMARY KEY
);

CREATE TABLE catccls (
    catccl smallint PRIMARY KEY
    -- TODO: Do we need name and/or definition from IENC feature catalogue?
    -- (see page 328 of edition 2.3)
);
INSERT INTO catccls VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);

CREATE TABLE dirimps (
    dirimp smallint PRIMARY KEY
    -- TODO: Do we need name and/or definition from IENC feature catalogue?
    -- (see page 381 of edition 2.3)
);
INSERT INTO dirimps VALUES (1), (2), (3), (4);
-- dirimp_5 left out because it cannot be used for waterway area

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 catdis (
    catdis smallint PRIMARY KEY
    -- TODO: Do we need name and/or definition from IENC feature catalogue?
    -- (see page 171 of edition 2.3)
);
INSERT INTO catdis VALUES (1), (2), (3), (4);

CREATE TABLE position_codes (
    position_code char(2) PRIMARY KEY
    -- Use smallint because of fairway availability provided on daily basis?
    -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
    -- sheet "Position_code" or RIS-Index encoding guide?
    -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
    -- Clarify!
    -- TODO: Do we need an attribute "meaning" or so?
);

CREATE TABLE levels_of_service (
    level_of_service smallint PRIMARY KEY,
    name varchar(4)
);
INSERT INTO levels_of_service (
    level_of_service,
    name
) VALUES (1, 'LOS1'), (2, 'LOS2'), (3, 'LOS3');

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 measure_types (
    measure_type varchar PRIMARY KEY
);

CREATE TYPE template_types AS ENUM (
    'map',
    'diagram',
    'report'
);


-- Namespace not to be accessed directly by any user
CREATE SCHEMA internal
    -- Profile data are only accessible via the view users.list_users.
    CREATE TABLE user_profiles (
        username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
        -- keep username length compatible with role identifier
        country char(2) NOT NULL REFERENCES countries,
        map_extent box2d NOT NULL,
        email_address varchar NOT NULL
    )
    -- Columns referencing user-visible schemas added below.
;


-- Namespace to be accessed by sys_admin only
CREATE SCHEMA sys_admin
    CREATE TABLE system_config (
        config_key varchar PRIMARY KEY,
        config_val varchar
    )

    CREATE TABLE password_reset_requests (
        hash varchar(32) PRIMARY KEY,
        issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        username varchar NOT NULL UNIQUE
            REFERENCES internal.user_profiles(username)
                ON DELETE CASCADE ON UPDATE CASCADE
    )

    -- Tables with geo data to be published with GeoServer.
    CREATE TABLE external_services (
        local_name varchar PRIMARY KEY,
        remote_url varchar NOT NULL,
        is_wfs     boolean NOT NULL DEFAULT TRUE
    )

    CREATE TABLE published_services (
        schema varchar CHECK(to_regnamespace(schema) IS NOT NULL),
        name varchar,
        PRIMARY KEY (schema, name),
        -- SQL statement used for an SQL view in GeoServer:
        view_def text CHECK (is_valid_from_item(view_def)),
        -- SRID to be used with SQL view:
        srid int REFERENCES spatial_ref_sys,
        -- SLD style document:
        style xml CHECK(style IS DOCUMENT),
        as_wms boolean NOT NULL DEFAULT TRUE,
        as_wfs boolean NOT NULL DEFAULT TRUE,
        -- Either give a valid relation or a SQL statement:
        CHECK (to_regclass(schema || '.' || name) IS NOT NULL
            OR view_def IS NOT NULL)
    )
;


-- Namespace for user management related data
CREATE SCHEMA users
    CREATE TABLE stretches (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        name varchar NOT NULL,
        stretch isrsrange NOT NULL,
        area geography(MULTIPOLYGON, 4326) NOT NULL
            CHECK(ST_IsValid(CAST(area AS geometry))),
        objnam varchar NOT NULL,
        nobjnam varchar,
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL,
        staging_done boolean NOT NULL DEFAULT false,
        UNIQUE(name, staging_done)
    )
    CREATE TRIGGER stretches_date_info
        BEFORE UPDATE ON stretches
        FOR EACH ROW EXECUTE PROCEDURE update_date_info()

    CREATE TABLE stretch_countries (
        stretch_id int NOT NULL REFERENCES stretches(id)
            ON DELETE CASCADE,
        country char(2) NOT NULL REFERENCES countries,
        PRIMARY KEY(stretch_id, country)
    )

    CREATE TABLE templates (
        template_name varchar NOT NULL,
        template_type template_types NOT NULL DEFAULT 'map'::template_types,
        country char(2) REFERENCES countries,
        template_data bytea NOT NULL,
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE (template_name, template_type, country)
    )
    CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
        FOR EACH ROW EXECUTE PROCEDURE update_date_info()
;


-- Namespace for waterway data that can change in a running system
CREATE SCHEMA waterway

    -- Eventually obsolete.
    -- See https://roundup-intern.intevation.de/gemma/issue5
    -- CREATE TABLE rwdrs (
    --     tretch 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 waterway_area (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        area geography(POLYGON, 4326) NOT NULL
            CHECK(ST_IsValid(CAST(area AS geometry))),
        catccl smallint REFERENCES catccls,
        dirimp smallint REFERENCES dirimps
    )
    CREATE CONSTRAINT TRIGGER waterway_area_area_unique
        AFTER INSERT OR UPDATE OF area ON waterway_area
        FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area')


    CREATE TABLE gauges (
        location isrs CHECK(
            (location).orc SIMILAR TO 'G[[:digit:]]{4}'
            AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
        objname varchar NOT NULL,
        geom geography(POINT, 4326) NOT NULL,
        applicability_from_km int8,
        applicability_to_km int8,
        validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
        zero_point double precision NOT NULL,
        geodref varchar,
        date_info timestamp with time zone NOT NULL,
        source_organization varchar,
        lastupdate timestamp with time zone NOT NULL,
        -- entry removed from external data source (RIS-Index)/historicised:
        erased boolean NOT NULL DEFAULT false,
        PRIMARY KEY (location, validity),
        EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&)
            DEFERRABLE INITIALLY DEFERRED
    )
    -- Allow only one non-erased entry per location
    CREATE UNIQUE INDEX gauges_erased_unique_constraint
        ON gauges (location)
        WHERE NOT erased

    CREATE TABLE gauges_reference_water_levels (
        location isrs NOT NULL,
        validity tstzrange NOT NULL,
        FOREIGN KEY (location, validity) REFERENCES gauges ON UPDATE CASCADE,
        -- Omit foreign key constraint to be able to store not NtS-compliant
        -- names, too:
        depth_reference varchar NOT NULL, -- REFERENCES depth_references,
        PRIMARY KEY (location, validity, depth_reference),
        value int NOT NULL
    )

    CREATE TABLE gauge_measurements (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        location isrs NOT NULL,
        measure_date timestamp with time zone NOT NULL,
        country_code char(2) NOT NULL REFERENCES countries,
        sender varchar NOT NULL, -- "from" element from NtS response
        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,
        water_level double precision NOT NULL,
        date_info timestamp with time zone NOT NULL,
        source_organization varchar NOT NULL, -- "originator" from NtS response
        staging_done boolean NOT NULL DEFAULT false,
        UNIQUE (measure_date, location, staging_done)
    )
    CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge
        AFTER INSERT OR UPDATE OF location ON gauge_measurements
        FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date')

    CREATE TABLE gauge_predictions (
        location isrs NOT NULL,
        measure_date timestamp with time zone NOT NULL,
        country_code char(2) NOT NULL REFERENCES countries,
        sender varchar NOT NULL, -- "from" element from NtS response
        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,
        water_level double precision NOT NULL,
        conf_interval numrange
            CHECK (conf_interval @> CAST(water_level AS numeric)),
        date_info timestamp with time zone NOT NULL,
        source_organization varchar NOT NULL, -- "originator" from NtS response
        PRIMARY KEY (measure_date, location, date_issue)
    )
    CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge
        AFTER INSERT OR UPDATE OF location ON gauge_predictions
        FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date')

    CREATE TABLE waterway_axis (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        wtwaxs geography(LINESTRING, 4326) NOT NULL
            CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))),
        -- TODO: Do we need to check data set quality (DRC 2.1.6)?
        objnam varchar NOT NULL,
        nobjnam varchar
    )
    CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
        AFTER INSERT OR UPDATE OF wtwaxs ON waterway_axis
        FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs')

    -- This table allows linkage between 1D ISRS location codes and 2D space
    -- e.g. for cutting bottleneck area out of waterway area based on virtual
    -- distance marks along waterway axis (see SUC7).
    CREATE TABLE distance_marks_virtual (
        location_code isrs PRIMARY KEY,
        geom geography(POINT, 4326) NOT NULL,
        related_enc varchar(12) NOT NULL,
        wwname varchar
    )

    CREATE TABLE distance_marks (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        country char(2) REFERENCES countries,
        hectom int,
        geom geography(POINT, 4326) NOT NULL,
        -- include location in primary key, because we have no fairway code:
        catdis smallint REFERENCES catdis,
        position_code char(2) REFERENCES position_codes,
        related_enc varchar(12)
    )

    -- We need to configure primary keys for the views used by
    -- geoserver for wfs, otherwise it will generate ids on the fly,
    -- which will change for the same feature...
    -- See
    -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html
    -- for details.
    CREATE TABLE gt_pk_metadata (
        table_schema VARCHAR(32) NOT NULL,
        table_name VARCHAR(32) NOT NULL,
        pk_column VARCHAR(32) NOT NULL,
        pk_column_idx INTEGER,
        pk_policy VARCHAR(32),
        pk_sequence VARCHAR(64),
        unique (table_schema, table_name, pk_column),
        check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
    )

    -- Like stretches without the countries
    CREATE TABLE sections (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        name varchar NOT NULL,
        section isrsrange NOT NULL,
        area geography(MULTIPOLYGON, 4326) NOT NULL
            CHECK(ST_IsValid(CAST(area AS geometry))),
        objnam varchar NOT NULL,
        nobjnam varchar,
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL,
        staging_done boolean NOT NULL DEFAULT false,
        UNIQUE(name, staging_done)
    )
    CREATE TRIGGER sections_date_info
        BEFORE UPDATE ON sections
        FOR EACH ROW EXECUTE PROCEDURE update_date_info()

    CREATE TABLE waterway_profiles (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        location isrs NOT NULL REFERENCES distance_marks_virtual,
        geom geography(linestring, 4326),
        validity tstzrange,
        lnwl double precision,
        mwl double precision,
        hnwl double precision,
        fe30 double precision,
        fe100 double precision,
        -- XXX: further normalise using reference_water_levels?
        CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
            OR validity IS NOT NULL),
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL,
        staging_done boolean NOT NULL DEFAULT false,
        EXCLUDE USING GIST (
            isrs_asText(location) WITH =,
            validity WITH &&,
            CAST(staging_done AS int) WITH =)
    )
    CREATE TRIGGER waterway_profiles_date_info
        BEFORE UPDATE ON waterway_profiles
        FOR EACH ROW EXECUTE PROCEDURE update_date_info()

    CREATE TABLE fairway_dimensions (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        area geography(POLYGON, 4326) NOT NULL
            CHECK(ST_IsValid(CAST(area AS geometry))),
        level_of_service smallint NOT NULL REFERENCES levels_of_service,
        min_width smallint NOT NULL,
        max_width smallint NOT NULL,
        min_depth smallint NOT NULL,
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL,
        staging_done boolean NOT NULL DEFAULT false
    )
    CREATE TRIGGER fairway_dimensions_date_info
        BEFORE UPDATE ON fairway_dimensions
        FOR EACH ROW EXECUTE PROCEDURE update_date_info()

    --
    -- Bottlenecks
    --
    CREATE TABLE bottlenecks (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        bottleneck_id varchar NOT NULL,
        validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
        gauge_location isrs NOT NULL,
        objnam varchar,
        nobjnm varchar,
        stretch isrsrange NOT NULL,
        area geography(MULTIPOLYGON, 4326) NOT NULL
            CHECK(ST_IsValid(CAST(area AS geometry))),
        rb char(2) REFERENCES countries, -- from rb_lb in interface
        lb char(2) REFERENCES countries, -- from rb_lb in interface
        responsible_country char(2) NOT NULL REFERENCES countries,
        revisiting_time smallint,
        limiting varchar NOT NULL REFERENCES limiting_factors,
        -- surtyp varchar NOT NULL REFERENCES survey_types,
        -- XXX: Also an attribut of sounding result?
        -- coverage varchar 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 with time zone NOT NULL,
        source_organization varchar NOT NULL,
        staging_done boolean NOT NULL DEFAULT false,
        UNIQUE (bottleneck_id, validity, staging_done),
        EXCLUDE USING GiST (bottleneck_id WITH =,
                            validity WITH &&,
                            CAST(staging_done AS int) WITH =)
            DEFERRABLE INITIALLY DEFERRED
    )
    CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
        AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks
        FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity')

    CREATE TABLE bottlenecks_riverbed_materials (
        bottleneck_id int NOT NULL REFERENCES bottlenecks(id)
            ON DELETE CASCADE,
        riverbed varchar NOT NULL REFERENCES riverbed_materials,
        -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
        PRIMARY KEY (bottleneck_id, riverbed)
    )

    CREATE TABLE sounding_results (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        bottleneck_id varchar NOT NULL,
        date_info date NOT NULL,
        UNIQUE (bottleneck_id, date_info),
        area geography(POLYGON, 4326) NOT NULL
            CHECK(ST_IsValid(CAST(area AS geometry))),
        surtyp varchar REFERENCES survey_types,
        coverage varchar REFERENCES coverage_types,
        depth_reference varchar NOT NULL, -- REFERENCES depth_references,
        octree_checksum varchar,
        octree_index bytea,
        staging_done boolean NOT NULL DEFAULT false
    )
    CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck
        AFTER INSERT OR UPDATE OF bottleneck_id ON sounding_results
        FOR EACH ROW
        EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info')
    CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area
        AFTER INSERT OR UPDATE ON sounding_results
        FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area()

    CREATE TABLE sounding_results_contour_lines (
        sounding_result_id int NOT NULL REFERENCES sounding_results
            ON DELETE CASCADE,
        height numeric NOT NULL,
        lines geography(multilinestring, 4326) NOT NULL,
        -- TODO: generate valid simple features and add constraint:
            -- CHECK(ST_IsSimple(CAST(lines AS geometry))),
        PRIMARY KEY (sounding_result_id, height)
    )
    --
    -- Fairway availability
    --
    CREATE TABLE fairway_availability (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        position varchar,
        bottleneck_id varchar NOT NULL,
        surdat date NOT NULL,
        UNIQUE (bottleneck_id, surdat),
        -- additional_data xml -- Currently not relevant for GEMMA
        critical boolean,
        date_info timestamp with time zone NOT NULL,
        source_organization varchar NOT NULL
    )
    -- FIXME: From the DRC it is unclear what the exact semantics of
    --   surdat and Date_Info ar unclear.  Currently we assume that
    --   (fk_bn_fid,surdat) has to be unique, but that might be false.
    --   Anyway, I will date_info here to check for an matching
    --   reference gauge at the bottleneck.  The reason for this
    --   decision is purely practical (and might be semantically
    --   disputable: the bottleneck data in the demo system is not old
    --   enough to cover rthe surdat times...
    CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck
        AFTER INSERT OR UPDATE OF bottleneck_id ON fairway_availability
        FOR EACH ROW
        EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info')

    CREATE TABLE fa_reference_values (
        fairway_availability_id int NOT NULL REFERENCES fairway_availability,
        level_of_service smallint NOT NULL REFERENCES levels_of_service,
        PRIMARY KEY (fairway_availability_id, 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 geography(POINT, 4326)
    )

    CREATE TABLE bottleneck_pdfs (
        fairway_availability_id int NOT NULL 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 (fairway_availability_id, profile_pdf_url),
        pdf_generation_date timestamp with time zone NOT NULL,
        source_organization varchar NOT NULL
    )

    CREATE TABLE effective_fairway_availability (
        fairway_availability_id int NOT NULL REFERENCES fairway_availability,
        measure_date timestamp with time zone NOT NULL,
        level_of_service smallint NOT NULL REFERENCES levels_of_service,
        PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
        available_depth_value smallint,
        available_width_value smallint,
        water_level_value smallint,
        CHECK(COALESCE(available_depth_value, available_width_value,
            water_level_value) IS NOT NULL),
        measure_type varchar NOT NULL REFERENCES measure_types,
        source_organization varchar NOT NULL,
        forecast_generation_time timestamp with time zone,
        CHECK(measure_type <> 'forecasted'
            OR forecast_generation_time IS NOT NULL),
        value_lifetime timestamp with time zone,
        CHECK(measure_type = 'minimum guaranteed'
            OR value_lifetime IS NOT NULL)
    )
;

-- Configure primary keys for geoserver views
INSERT INTO waterway.gt_pk_metadata VALUES
  ('waterway', 'gauges_geoserver', 'isrs_code'),
  ('waterway', 'distance_marks_geoserver', 'location'),
  ('waterway', 'distance_marks_ashore_geoserver', 'id'),
  ('waterway', 'bottlenecks_geoserver', 'id'),
  ('waterway', 'stretches_geoserver', 'id'),
  ('waterway', 'sections_geoserver', 'id');

--
-- Import queue and respective logging
--
CREATE TYPE import_state AS ENUM (
    'queued',
    'running',
    'failed', 'unchanged', 'pending',
    'accepted', 'declined'
);

CREATE TYPE log_type AS ENUM ('info', 'warn', 'error');

-- Namespace for import queue and respective logging
CREATE SCHEMA import

    CREATE TABLE import_configuration (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        username varchar NOT NULL
            REFERENCES internal.user_profiles(username)
                ON DELETE CASCADE
                ON UPDATE CASCADE,
        kind varchar NOT NULL
    )

    CREATE TABLE import_configuration_attributes (
        import_configuration_id int NOT NULL
          REFERENCES import_configuration(id)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
        k VARCHAR NOT NULL,
        v TEXT NOT NULL,
        UNIQUE (import_configuration_id, k)
    )

    CREATE TABLE imports (
        id         int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        state      import_state NOT NULL DEFAULT 'queued',
        kind       varchar   NOT NULL,
        enqueued   timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        due        timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        retry_wait interval
            CHECK(retry_wait IS NULL
                OR retry_wait >= interval '0 microseconds'),
        trys_left  int, -- if NULL and retry_wait NOT NULL, endless
        username   varchar   NOT NULL
            REFERENCES internal.user_profiles(username)
                ON DELETE CASCADE
                ON UPDATE CASCADE,
        signer varchar
            REFERENCES internal.user_profiles(username)
                ON DELETE SET NULL
                ON UPDATE CASCADE,
        send_email boolean NOT NULL DEFAULT false,
        data       TEXT,
        summary    TEXT
    )

    CREATE INDEX enqueued_idx ON imports(enqueued, state)

    CREATE TABLE import_logs (
        import_id int NOT NULL REFERENCES imports(id)
            ON DELETE CASCADE,
        time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        kind log_type NOT NULL DEFAULT 'info',
        msg TEXT NOT NULL
    )
    CREATE INDEX import_logs_import_id ON import.import_logs (import_id)

    CREATE TABLE track_imports (
        import_id int      NOT NULL REFERENCES imports(id)
            ON DELETE CASCADE,
        deletion  bool     NOT NULL DEFAULT false,
        relation  regclass NOT NULL,
        key       int      NOT NULL,
        UNIQUE (relation, key)
    )
;

CREATE FUNCTION import.del_import(imp_id int) RETURNS void AS
$$
DECLARE
    tmp RECORD;
BEGIN
    FOR tmp IN
        SELECT relation, array_agg(key) AS keys
            FROM import.track_imports
            WHERE import_id = imp_id AND NOT deletion
            GROUP BY relation
    LOOP
        EXECUTE format('DELETE FROM %s WHERE id = ANY($1)', tmp.relation)
            USING tmp.keys;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE SCHEMA caching

    CREATE TABLE sounding_differences (
        id         int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        minuend    int NOT NULL REFERENCES waterway.sounding_results(id)
                       ON DELETE CASCADE,
        subtrahend int NOT NULL REFERENCES waterway.sounding_results(id)
                       ON DELETE CASCADE,
        UNIQUE (minuend, subtrahend)
    )

    CREATE TABLE sounding_differences_contour_lines (
        sounding_differences_id int NOT NULL REFERENCES sounding_differences(id)
                                    ON DELETE CASCADE,
        height numeric NOT NULL,
        lines  geography(multilinestring, 4326) NOT NULL,
        PRIMARY KEY (sounding_differences_id, height)
    )
;

COMMIT;