-- 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 functions to be used in constraint triggers
--
/* The constraint triggers are subject to a possible race condition
   due to PostgreSQL MVCC: Trigger functions do not see uncommited changes
   of other transactions running in parallel.

   Since we serialize imports at application level, the race condition
   should not happen, though. In case serialization at application level
   should be removed, database side locking/serialization would have
   to be applied.
*/

-- 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. If additional column names are given as trigger arguments,
-- the group of given columns is tested for equality, if no operators are
-- given. Optionally, a column can be compared with a different operator
-- given after the keyword "WITH" in the trigger argument.
CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
$$
DECLARE
    new_geom geometry;
    tg_arg text;
    col varchar;
    opr varchar;
    filters text;
    has_equal boolean;
BEGIN
    EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
        INTO new_geom
        USING NEW;
    FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
        -- Test each additional argument for equality
        -- or with operator given after keyword "WITH"
        tg_arg = lower(tg_arg);
        col = split_part(tg_arg, ' with ', 1);
        opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '=');
        filters = format('%s AND %I %s $2.%2$I', filters, col, opr);
    END LOOP;
    EXECUTE format(
            'SELECT EXISTS(SELECT 1 FROM %I.%I '
                'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))'
                '%s)',
             TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters)
        INTO has_equal
        USING new_geom, NEW;
    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
                            AND staging_done),
                         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), (5);

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

INSERT INTO survey_types (survey_type) VALUES ('single'), ('multi'), ('marking');

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)
            CHECK(to_regrole(quote_ident(username)) IS NOT NULL),
        -- keep username length compatible with role identifier
        country char(2) NOT NULL REFERENCES countries,
        map_extent box2d NOT NULL,
        email_address varchar NOT NULL,
        report_reciever boolean NOT NULL DEFAULT false,
        active boolean NOT NULL DEFAULT true
    )
;


-- 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)),
        -- Column in output of SQL statement to be used as primary key:
        key_column varchar,
        -- SRID to be used with SQL view:
        srid int REFERENCES spatial_ref_sys,
        -- SLD style document or ZIP blob:
        style bytea,
        as_wms boolean NOT NULL DEFAULT TRUE,
        as_wfs boolean NOT NULL DEFAULT TRUE,
        wmst_attribute     varchar DEFAULT NULL,
        wmst_end_attribute varchar DEFAULT NULL,
        -- Either give a valid relation or a SQL statement:
        CHECK (to_regclass(schema || '.' || name) IS NOT NULL
            OR view_def IS NOT NULL)
    )

    CREATE TABLE layer_groups (
       name varchar PRIMARY KEY
    )

    CREATE TABLE grouped_layers (
       group_name varchar REFERENCES layer_groups ON DELETE CASCADE,
       schema     varchar,
       name       varchar,
       ord        int NOT NULL DEFAULT 0,
       PRIMARY KEY (group_name, schema, name),
       UNIQUE (group_name, schema, name, ord),
       FOREIGN KEY(schema, name) REFERENCES published_services
    )

    -- Table to store scripts which updates aggregated data.
    CREATE TABLE stats_updates (
        name   varchar PRIMARY key,
        script TEXT    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()

    CREATE VIEW list_users WITH (security_barrier) AS
        SELECT
            r.rolname,
            p.username,
            CAST('' AS varchar) AS pw,
            p.country,
            p.map_extent,
            p.email_address,
            p.report_reciever,
            p.active
        FROM internal.user_profiles p
            JOIN pg_roles u ON p.username = u.rolname
            JOIN pg_auth_members a ON u.oid = a.member
            JOIN pg_roles r ON a.roleid = r.oid
        WHERE p.username = current_user
            OR pg_has_role('waterway_admin', 'MEMBER')
                AND p.country = (
                    SELECT country FROM internal.user_profiles
                        WHERE username = current_user)
                AND r.rolname <> 'sys_admin'
            OR pg_has_role('sys_admin', 'MEMBER')
;


--
-- Functions to be used in DEFAULT expresions
--

-- Return current_user's country code
CREATE FUNCTION users.user_country(user_name name DEFAULT current_user)
    RETURNS internal.user_profiles.country%TYPE
    AS $$
        SELECT country FROM users.list_users
            WHERE username = user_name
    $$
    LANGUAGE SQL
    STABLE PARALLEL SAFE;


-- 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(MULTILINESTRING, 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,
        validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
            CHECK (NOT isempty(validity)),
        -- Last time an import job found this entry in a data source:
        last_found timestamp with time zone NOT NULL DEFAULT current_timestamp
    )
    CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
        AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway_axis
        DEFERRABLE INITIALLY DEFERRED
        FOR EACH ROW
        EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity WITH &&')
    CREATE INDEX waterway_axis_validity
        ON waterway_axis USING GiST (validity)

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

    -- 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,
        country char(2) NOT NULL REFERENCES countries
            DEFAULT users.user_country(),
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL,
        staging_done boolean NOT NULL DEFAULT false,
        -- Disallow the same name for different countries
        EXCLUDE USING GiST (name WITH =, country WITH <>),
        -- Allow the same name one time in and outside staging area, each
        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(MULTIPOLYGON, 4326) NOT NULL
            CHECK(ST_IsValid(CAST(area AS geometry))
                AND NOT ST_IsEmpty(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,
        validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
            CHECK (NOT isempty(validity))
    )
    CREATE TRIGGER fairway_dimensions_date_info
        BEFORE UPDATE ON fairway_dimensions
        FOR EACH ROW EXECUTE PROCEDURE update_date_info()
    CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique
        AFTER INSERT OR UPDATE OF area, validity, staging_done
        ON fairway_dimensions
        FOR EACH ROW EXECUTE FUNCTION prevent_st_equals(
            'area', 'validity WITH &&', 'staging_done')
    CREATE INDEX fairway_dimensions_validity
        ON fairway_dimensions USING GiST (validity)

    --
    -- 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,
        zpg_exception bool NOT NULL DEFAULT FALSE, -- Found no LDC in ZPG case.
        mesh_checksum varchar,
        mesh_index_version int,
        mesh_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_iso_areas (
        sounding_result_id int NOT NULL REFERENCES sounding_results
            ON DELETE CASCADE,
        height numeric NOT NULL,
        areas geography(MULTIPOLYGON, 4326) NOT NULL,
        -- TODO: generate valid simple features and add constraint:
            -- CHECK(ST_IsSimple(CAST(areas AS geometry))),
        PRIMARY KEY (sounding_result_id, height)
    )

    CREATE TABLE sounding_results_marking_points (
        sounding_result_id int NOT NULL REFERENCES sounding_results
          ON DELETE CASCADE,
        height numeric NOT NULL,
        -- XXX: GeoServer does not like geography(MULTIPOINTZ)
        --      We need to track this down. Maybe with
        --      GeoServer upstream.
        points geography(MULTIPOINTZ, 4326) NOT NULL,
        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,
                     measure_type),
        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)
    )

    -- Attributes common to all fairway marks
    CREATE TABLE fairway_marks (
        validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
            CHECK (NOT isempty(validity)),
        -- Last time an import job found this entry in a data source:
        last_found timestamp with time zone NOT NULL DEFAULT current_timestamp,
        geom geography(POINT, 4326) NOT NULL,
        -- Attributes according to IENC Feature Catalogue:
        datsta varchar,
        datend varchar,
        persta varchar,
        perend varchar,
        objnam varchar,
        nobjnm varchar,
        inform varchar,
        ninfom varchar,
        scamin int,
        picrep varchar,
        txtdsc varchar,
        sordat varchar,
        sorind varchar
    )

    -- Additional attributes for IENC features BCNLAT/bcnlat
    CREATE TABLE fairway_marks_bcnlat (
        colour varchar,
        colpat varchar,
        condtn int,
        bcnshp int,
        catlam int
    ) INHERITS (fairway_marks)

    CREATE TABLE fairway_marks_bcnlat_hydro (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    ) INHERITS (fairway_marks_bcnlat)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows
        ON fairway_marks_bcnlat_hydro
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                colour, colpat, condtn, bcnshp, catlam, 0
            ) AS fairway_marks_bcnlat_hydro)
        ))
    CREATE INDEX fairway_marks_bcnlat_hydro_validity
        ON fairway_marks_bcnlat_hydro USING GiST (validity)

    CREATE TABLE fairway_marks_bcnlat_ienc (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    ) INHERITS (fairway_marks_bcnlat)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows
        ON fairway_marks_bcnlat_ienc
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                colour, colpat, condtn, bcnshp, catlam, 0
            ) AS fairway_marks_bcnlat_ienc)
        ))
    CREATE INDEX fairway_marks_bcnlat_ienc_validity
        ON fairway_marks_bcnlat_ienc USING GiST (validity)

    CREATE TABLE fairway_marks_bcnlat_dirimps (
        fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc,
        dirimp smallint REFERENCES dirimps,
        PRIMARY KEY (fm_bcnlat_id, dirimp)
    )

    -- Additional attributes for IENC feature BOYCAR
    CREATE TABLE fairway_marks_boycar (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        colour varchar,
        colpat varchar,
        conrad int,
        marsys int,
        boyshp int,
        catcam int
    ) INHERITS (fairway_marks)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows
        ON fairway_marks_boycar
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                0, colour, colpat, conrad, marsys, boyshp, catcam
            ) AS fairway_marks_boycar)
        ))
    CREATE INDEX fairway_marks_boycar_validity
        ON fairway_marks_boycar USING GiST (validity)

    -- Additional attributes for IENC feature BOYLAT
    CREATE TABLE fairway_marks_boylat (
        colour varchar,
        colpat varchar,
        conrad int,
        marsys int,
        boyshp int,
        catlam int
    ) INHERITS (fairway_marks)

    CREATE TABLE fairway_marks_boylat_hydro (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    ) INHERITS (fairway_marks_boylat)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_boylat_hydro_distinct_rows
        ON fairway_marks_boylat_hydro
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                colour, colpat, conrad, marsys, boyshp, catlam, 0
            ) AS fairway_marks_boylat_hydro)
        ))
    CREATE INDEX fairway_marks_boylat_hydro_validity
        ON fairway_marks_boylat_hydro USING GiST (validity)

    CREATE TABLE fairway_marks_boylat_ienc (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    ) INHERITS (fairway_marks_boylat)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_boylat_ienc_distinct_rows
        ON fairway_marks_boylat_ienc
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                colour, colpat, conrad, marsys, boyshp, catlam, 0
            ) AS fairway_marks_boylat_ienc)
        ))
    CREATE INDEX fairway_marks_boylat_ienc_validity
        ON fairway_marks_boylat_ienc USING GiST (validity)

    -- Additional attributes for IENC feature BOYSAW
    CREATE TABLE fairway_marks_boysaw (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        colour varchar,
        colpat varchar,
        conrad int,
        marsys int,
        boyshp int
    ) INHERITS (fairway_marks)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows
        ON fairway_marks_boysaw
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                0, colour, colpat, conrad, marsys, boyshp
            ) AS fairway_marks_boysaw)
        ))
    CREATE INDEX fairway_marks_boysaw_validity
        ON fairway_marks_boysaw USING GiST (validity)

    -- Additional attributes for IENC feature BOYSPP
    CREATE TABLE fairway_marks_boyspp (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        colour varchar,
        colpat varchar,
        conrad int,
        marsys int,
        boyshp int,
        catspm varchar
    ) INHERITS (fairway_marks)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows
        ON fairway_marks_boyspp
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                0, colour, colpat, conrad, marsys, boyshp, catspm
            ) AS fairway_marks_boyspp)
        ))
    CREATE INDEX fairway_marks_boyspp_validity
        ON fairway_marks_boyspp USING GiST (validity)

    -- Additional attributes for IENC features DAYMAR/daymar
    CREATE TABLE fairway_marks_daymar (
        colour varchar,
        colpat varchar,
        condtn int,
        topshp int
    ) INHERITS (fairway_marks)

    CREATE TABLE fairway_marks_daymar_hydro (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    ) INHERITS (fairway_marks_daymar)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_daymar_hydro_distinct_rows
        ON fairway_marks_daymar_hydro
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                colour, colpat, condtn, topshp, 0
            ) AS fairway_marks_daymar_hydro)
        ))
    CREATE INDEX fairway_marks_daymar_hydro_validity
        ON fairway_marks_daymar_hydro USING GiST (validity)

    CREATE TABLE fairway_marks_daymar_ienc (
        orient double precision,
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
    ) INHERITS (fairway_marks_daymar)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_daymar_ienc_distinct_rows
        ON fairway_marks_daymar_ienc
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                colour, colpat, condtn, topshp, orient, 0
            ) AS fairway_marks_daymar_ienc)
        ))
    CREATE INDEX fairway_marks_daymar_ienc_validity
        ON fairway_marks_daymar_ienc USING GiST (validity)

    CREATE TABLE fairway_marks_daymar_dirimps (
        fm_daymar_id int REFERENCES fairway_marks_daymar_ienc,
        dirimp smallint REFERENCES dirimps,
        PRIMARY KEY (fm_daymar_id, dirimp)
    )

    -- Additional attributes for IENC feature LIGHTS
    CREATE TABLE fairway_marks_lights (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        colour varchar,
        condtn int,
        orient double precision,
        catlit varchar,
        exclit int,
        litchr int,
        litvis varchar,
        mltylt int,
        sectr1 double precision,
        sectr2 double precision,
        siggrp varchar,
        sigper double precision,
        sigseq varchar,
        status varchar
    ) INHERITS (fairway_marks)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows
        ON fairway_marks_lights
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                0, colour, condtn, orient, catlit, exclit, litchr, litvis,
                mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status
            ) AS fairway_marks_lights)
        ))
    CREATE INDEX fairway_marks_lights_validity
        ON fairway_marks_lights USING GiST (validity)

    -- Additional attributes for IENC feature RTPBCN
    CREATE TABLE fairway_marks_rtpbcn (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        condtn int,
        siggrp varchar,
        catrtb int,
        radwal varchar
    ) INHERITS (fairway_marks)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows
        ON fairway_marks_rtpbcn
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                0, condtn, siggrp, catrtb, radwal
            ) AS fairway_marks_rtpbcn)
        ))
    CREATE INDEX fairway_marks_rtpbcn_validity
        ON fairway_marks_rtpbcn USING GiST (validity)

    -- Additional attributes for IENC feature TOPMAR
    CREATE TABLE fairway_marks_topmar (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        colour varchar,
        colpat varchar,
        condtn int,
        topshp int
    ) INHERITS (fairway_marks)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows
        ON fairway_marks_topmar
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                0, colour, colpat, condtn, topshp
            ) AS fairway_marks_topmar)
        ))
    CREATE INDEX fairway_marks_topmar_validity
        ON fairway_marks_topmar USING GiST (validity)

    -- Additional attributes for IENC feature NOTMRK
    CREATE TABLE fairway_marks_notmrk (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        condtn int,
        marsys int,
        orient double precision,
        status varchar,
        addmrk varchar,
        catnmk int,
        disipd double precision,
        disipu double precision,
        disbk1 double precision,
        disbk2 double precision,
        fnctnm int,
        bnkwtw int
    ) INHERITS (fairway_marks)
    -- Prevent identical entries using composite type comparison
    -- (i.e. considering two NULL values in a field equal):
    CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows
        ON fairway_marks_notmrk
        ((CAST((validity, last_found, geom,
                datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
                scamin, picrep, txtdsc, sordat, sorind,
                0, condtn, marsys, orient, status, addmrk, catnmk,
                disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw
            ) AS fairway_marks_notmrk)
        ))
    CREATE INDEX fairway_marks_notmrk_validity
        ON fairway_marks_notmrk USING GiST (validity)

    CREATE TABLE fairway_marks_notmrk_dirimps (
        fm_notmrk_id int REFERENCES fairway_marks_notmrk,
        dirimp smallint REFERENCES dirimps,
        PRIMARY KEY (fm_notmrk_id, dirimp)
    )
;


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

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,
        changed    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
    )
    -- Mainly for listing imports in clients:
    CREATE INDEX enqueued_idx ON imports(enqueued)
    -- For fast retrieval of queued imports by the import queue in backend:
    CREATE INDEX state_idx ON imports(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_iso_areas (
        sounding_differences_id int NOT NULL REFERENCES sounding_differences(id)
                                    ON DELETE CASCADE,
        height numeric NOT NULL,
        areas  geography(MULTIPOLYGON, 4326) NOT NULL,
        PRIMARY KEY (sounding_differences_id, height)
    )
;

COMMIT;