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

--
-- 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 functions to be used as statement-level AFTER triggers,
-- associating time-based referencing objects to matching version
CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS
$$
DECLARE
    new_bn int;
    new_bns int[];
BEGIN
    -- Avoid unnecessary execution ON UPDATE if validity did not change
    IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
        UPDATE waterway.gauge_measurements
        SET validity = NEW.validity
        WHERE location = NEW.location
            AND measure_date <@ NEW.validity;

        -- build bottleneck validities from intersections with gauge validities
        FOR new_bn IN
            INSERT INTO waterway.bottlenecks (
                    bottleneck_id,
                    validity,
                    gauge_location,
                    gauge_validity,
                    objnam,
                    nobjnm,
                    stretch,
                    area,
                    rb,
                    lb,
                    responsible_country,
                    revisiting_time,
                    limiting,
                    date_info,
                    source_organization,
                    staging_done
                ) SELECT
                    b.bottleneck_id,
                    -- Anticipate non-intersecting gauge validities:
                    b.validity * CASE WHEN g.validity = NEW.validity
                        THEN NEW.validity ELSE g.validity - NEW.validity END,
                    b.gauge_location,
                    g.validity,
                    b.objnam,
                    b.nobjnm,
                    b.stretch,
                    b.area,
                    b.rb,
                    b.lb,
                    b.responsible_country,
                    b.revisiting_time,
                    b.limiting,
                    b.date_info,
                    b.source_organization,
                    b.staging_done
                FROM waterway.bottlenecks b JOIN waterway.gauges g
                    ON b.gauge_location = g.location
                WHERE b.gauge_location = NEW.location
                    AND b.validity && NEW.validity
                    -- Avoid duplicate intersection results:
                    AND NOT (b.validity <@ NEW.validity
                        AND g.validity <> NEW.validity)
            ON CONFLICT (bottleneck_id, validity) DO UPDATE SET
                -- Associate to new matching gauge version
                gauge_validity = EXCLUDED.gauge_validity
            RETURNING id
        LOOP
            new_bns = new_bns || new_bn;
        END LOOP;
        -- Delete bottleneck versions superseded by new intersections:
        DELETE FROM waterway.bottlenecks
        WHERE gauge_location = NEW.location
            AND validity && NEW.validity
            AND id <> ALL(new_bns);
    END IF;
    RETURN NULL; -- ignored
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS
$$
BEGIN
    -- Avoid unnecessary execution ON UPDATE if validity did not change
    IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
        UPDATE waterway.sounding_results
        SET bottleneck_validity = NEW.validity
        WHERE bottleneck_id = NEW.bottleneck_id
          AND CAST(date_info AS timestamptz) <@ NEW.validity;

        -- Always associate fairway availability data to newest bottleneck
        -- version to prevent problems in analysis over longer time periods
        WITH
        bn AS (SELECT id, validity FROM waterway.bottlenecks
            WHERE bottleneck_id = NEW.bottleneck_id),
        latest AS (SELECT id FROM bn
            -- Candidates are past new validity or just inserted/updated
            WHERE NOT validity &< NEW.validity OR id = NEW.id
            ORDER BY upper(validity) DESC FETCH FIRST ROW ONLY)
        UPDATE waterway.fairway_availability
        SET bottleneck_id = (SELECT id FROM latest)
        WHERE bottleneck_id IN(SELECT id FROM bn EXCEPT SELECT id FROM latest);
    END IF;
    RETURN NULL; -- ignored
END;
$$
LANGUAGE plpgsql;

-- Constraint trigger: sounding Results must intersect with the area
-- of the bottleneck they belong to.
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, validity)
                              =(NEW.bottleneck_id, NEW.bottleneck_validity)),
                         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 gemma_schema_version;
$$;


--
-- GEMMA data
--

-- 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
        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 (
        name regclass PRIMARY KEY,
        style xml CHECK(style IS DOCUMENT),
        as_wms boolean NOT NULL DEFAULT TRUE,
        as_wfs boolean NOT NULL DEFAULT TRUE
    )
;

--
-- 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 for user management related data
CREATE SCHEMA users
    CREATE TABLE responsibility_areas (
        country char(2) PRIMARY KEY REFERENCES countries,
        area geography(MULTIPOLYGON, 4326)
            CHECK(ST_IsValid(CAST(area AS geometry)))
    )

    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()
;
ALTER TABLE internal.user_profiles ADD
    country char(2) NOT NULL REFERENCES users.responsibility_areas;


-- Namespace for system wide configuration
CREATE SCHEMA systemconf
    CREATE TABLE feature_colours (
        feature_name varchar,
        style_attr varchar,
        r int NOT NULL CHECK (r >= 0 AND r < 256),
        g int NOT NULL CHECK (g >= 0 AND g < 256),
        b int NOT NULL CHECK (b >= 0 AND b < 256),
        a numeric NOT NULL CHECK (a >= 0 AND a <= 1),
        PRIMARY KEY (feature_name, style_attr)
    )
;

-- 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
    -- Associate referencing objects to matching gauge version
    CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
        ON gauges FOR EACH ROW EXECUTE FUNCTION move_gauge_referencing()

    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,
        validity tstzrange NOT NULL,
        CONSTRAINT gauge_key
            FOREIGN KEY (location, validity) REFERENCES gauges
            ON UPDATE CASCADE,
        measure_date timestamp with time zone NOT NULL,
        CHECK (measure_date <@ validity),
        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)
    )
    -- For fast retrieval of newest measurement per location:
    CREATE INDEX gauge_measurements_location_measure_date_desc
        ON waterway.gauge_measurements (location, measure_date DESC)

    CREATE TABLE gauge_predictions (
        location isrs NOT NULL,
        validity tstzrange NOT NULL,
        CONSTRAINT gauge_key
            FOREIGN KEY (location, validity) REFERENCES gauges
            ON UPDATE CASCADE,
        measure_date timestamp with time zone NOT NULL,
        CHECK (measure_date >= lower(validity)),
        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 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'))
    )

    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 (
        stretches_id int NOT NULL REFERENCES stretches(id)
            ON DELETE CASCADE,
        country_code char(2) NOT NULL REFERENCES countries(country_code),
        UNIQUE(stretches_id, country_code)
    )

    -- 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 stretches
        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)),
        UNIQUE (bottleneck_id, validity),
        EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
            DEFERRABLE INITIALLY DEFERRED,
        gauge_location isrs NOT NULL,
        gauge_validity tstzrange NOT NULL,
        CHECK(validity <@ gauge_validity),
        CONSTRAINT gauge_key
            FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges
                ON UPDATE CASCADE,
        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
    )
    -- Associate referencing objects to matching bottleneck version
    CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
        ON bottlenecks FOR EACH ROW
        EXECUTE FUNCTION move_bottleneck_referencing()

    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,
        bottleneck_validity tstzrange NOT NULL,
        CONSTRAINT bottleneck_key
            FOREIGN KEY (bottleneck_id, bottleneck_validity)
                REFERENCES bottlenecks (bottleneck_id, validity)
                ON UPDATE CASCADE,
        date_info date NOT NULL,
        CHECK (tstzrange(date_info::timestamptz,
            date_info::timestamptz + '1 d'::interval) && bottleneck_validity),
        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 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_code char(2) REFERENCES position_codes,
        bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
        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
    )

    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', 'location'),
  ('waterway', 'distance_marks_geoserver', 'location_code'),
  ('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 now(),
        kind log_type NOT NULL DEFAULT 'info',
        msg TEXT NOT NULL
    )

    CREATE INDEX kind_idx ON import_logs(kind)

    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 * FROM import.track_imports WHERE import_id = imp_id AND NOT deletion
    LOOP
        EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key;
    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;