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

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

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

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


-- 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,
        country char(2) REFERENCES countries,
        template_data bytea NOT NULL,
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE (template_name, 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 TABLE gauges (
        location isrs PRIMARY KEY 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,
        -- pasted text from a more general specification is given
        -- (a gauge is not a berth!)
        -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
        zero_point double precision NOT NULL,
        geodref varchar,
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar
    )
    CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
        FOR EACH ROW EXECUTE PROCEDURE update_date_info()

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

    CREATE VIEW gauges_geoserver AS
    SELECT
        g.location,
        isrs_asText(g.location) AS isrs_code,
        g.objname,
        g.geom,
        g.applicability_from_km,
        g.applicability_to_km,
        g.validity,
        g.zero_point,
        g.geodref,
        g.date_info,
        g.source_organization,
        json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
                                                  r.value))
            AS reference_water_levels
    FROM gauges g LEFT JOIN LATERAL (
            SELECT gauge_id, depth_reference, value
            FROM gauges_reference_water_levels
            ) r ON r.gauge_id = g.location
    GROUP BY g.location

    CREATE TABLE gauge_measurements (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        fk_gauge_id isrs NOT NULL REFERENCES gauges,
        measure_date timestamp with time zone NOT NULL,
        country_code char(2) NOT NULL REFERENCES countries,
        -- TODO: add relations to stuff provided as enumerations
        sender varchar NOT NULL, -- "from" attribute from DRC
        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,
        predicted boolean NOT NULL,
        is_waterlevel boolean NOT NULL,
        -- XXX: "measure_code" if really only W or Q
        -- XXX: Do we need "unit" attribute or can we normalise on import?
        value_min double precision, -- XXX: NOT NULL if predicted?
        value_max double precision, -- XXX: NOT NULL if predicted?
        --- TODO: Add a double range type for checking?
        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL, -- "originator"
        staging_done boolean NOT NULL DEFAULT false,
        -- So we can have a staged and
        -- a non-staged fk_gauge_id/measure_date pair.
        UNIQUE (fk_gauge_id, measure_date, staging_done)
    )

    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
    )

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

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

    -- A table to help geoserver serve the distance marks as WFS 1.1.0.
    -- At least geoserver-2.13.2 does not serve type geography correctly
    -- and does not serve the location_code as isrs type
    CREATE VIEW distance_marks_geoserver AS
        SELECT location_code,
               isrs_asText(location_code) AS location,
               geom::Geometry(POINT, 4326),
               related_enc,
               (location_code).hectometre
            FROM distance_marks_virtual

    CREATE VIEW distance_marks_ashore_geoserver AS
        SELECT id,
               country,
               geom::Geometry(POINT, 4326),
               related_enc,
               hectom,
               catdis,
               position_code
            FROM distance_marks

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

    -- Published view for GeoServer
    CREATE VIEW stretches_geoserver AS SELECT
        id,
        name,
        (stretch).lower::varchar as lower,
        (stretch).upper::varchar as upper,
        area::Geometry(MULTIPOLYGON, 4326),
        objnam,
        nobjnam,
        date_info,
        source_organization,
        (SELECT string_agg(country_code, ', ')
            FROM stretch_countries
            WHERE stretches_id = id) AS countries,
        staging_done
    FROM stretches


    CREATE TRIGGER sections_stretches_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
    --
    -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
    -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
    -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
    CREATE TABLE bottlenecks (
        id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
        bottleneck_id varchar UNIQUE NOT NULL,
        fk_g_fid isrs NOT NULL REFERENCES gauges,
        -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
        objnam varchar,
        nobjnm varchar,
        stretch isrsrange NOT NULL,
        area geography(MULTIPOLYGON, 4326) NOT NULL
            CHECK(ST_IsValid(CAST(area AS geometry))),
        rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
        lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
        responsible_country char(2) NOT NULL REFERENCES countries,
        revisiting_time smallint NOT NULL,
        limiting varchar NOT NULL REFERENCES limiting_factors,
        -- surtyp varchar NOT NULL REFERENCES survey_types,
        -- XXX: Also an attribut of sounding result?
        -- coverage varchar 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 DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL,
        -- additional_data xml -- Currently not relevant for GEMMA
        staging_done boolean NOT NULL DEFAULT false
    )
    CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
        FOR EACH ROW EXECUTE PROCEDURE update_date_info()

    CREATE TABLE bottlenecks_riverbed_materials (
        bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
        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 int NOT NULL REFERENCES bottlenecks(id),
        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(4) NOT NULL, -- REFERENCES depth_references,
        octree_checksum varchar,
        octree_index bytea,
        staging_done boolean NOT NULL DEFAULT false
    )

    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)
    )
    -- A view to help geoserver serve contour lines.
    -- At least geoserver-2.13.2 does not serve type geography correctly
    CREATE VIEW sounding_results_contour_lines_geoserver AS
        SELECT bottleneck_id,
                date_info,
                height,
                CAST(lines AS geometry(multilinestring, 4326)) AS lines
            FROM sounding_results_contour_lines cl
                JOIN sounding_results sr
                    ON sr.id = cl.sounding_result_id

    --
    -- 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 DEFAULT CURRENT_TIMESTAMP,
        source_organization varchar NOT NULL
    )
    CREATE TRIGGER fairway_availability_date_info
        BEFORE UPDATE ON fairway_availability
        FOR EACH ROW EXECUTE PROCEDURE update_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)
    )

    CREATE VIEW bottleneck_overview AS
    SELECT
      objnam AS name,
      ST_Centroid(area)::Geometry(POINT, 4326) AS point,
      (lower(stretch)).hectometre AS from,
      (upper(stretch)).hectometre AS to,
      sr.current::text
    FROM bottlenecks bn LEFT JOIN (
      SELECT bottleneck_id, max(date_info) AS current FROM sounding_results
      GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
    ORDER BY objnam

    -- Published view for GeoServer
    CREATE VIEW bottlenecks_geoserver AS
    WITH fairway_availability_latest AS (
        SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical
        FROM fairway_availability
        ORDER BY bottleneck_id, date_info DESC NULLS LAST),
    gauge_measurements_waterlevel AS (
        SELECT DISTINCT ON (fk_gauge_id)
            fk_gauge_id, measure_date, predicted, water_level
        FROM gauge_measurements WHERE predicted ='false'
        ORDER BY fk_gauge_id, measure_date DESC NULLS LAST)
    SELECT
        b.id,
        b.bottleneck_id,
        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,
        g.location AS gauge_isrs_code,
        g.objname AS gauge_objname,
        json_object_agg(r.depth_reference, r.value) AS reference_water_levels,
        fal.date_info AS fa_date_info,
        fal.critical AS fa_critical,
        gmw.water_level as gm_waterlevel
    FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location
        LEFT JOIN LATERAL (
            SELECT gauge_id,depth_reference,value
            FROM gauges_reference_water_levels
            ) r ON r.gauge_id = b.fk_g_fid
        LEFT JOIN LATERAL (
            SELECT bottleneck_id,date_info,critical
            FROM  fairway_availability_latest
            WHERE b.id=bottleneck_id) fal ON TRUE
        LEFT JOIN LATERAL (
            SELECT water_level
            FROM  gauge_measurements_waterlevel
            WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE
    GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
;

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

--
-- 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 NOT NULL DEFAULT CURRENT_TIMESTAMP,
        due        timestamp 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 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,
        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
    LOOP
        EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

CREATE FUNCTION import.del_import() RETURNS trigger AS
$$
BEGIN
    EXECUTE format('DELETE FROM %s WHERE id = $1', OLD.relation) USING OLD.key;
    RETURN NULL;
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)
    )
;

CREATE VIEW waterway.sounding_differences AS SELECT
    sd.id           AS id,
    bn.objnam       AS objnam,
    srm.date_info   AS minuend,
    srs.date_info   AS subtrahend,
    sdcl.height     AS height,
    CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
FROM
    caching.sounding_differences sd JOIN
    caching.sounding_differences_contour_lines sdcl ON sd.id = sdcl.sounding_differences_id JOIN
    waterway.sounding_results srm ON sd.minuend    = srm.id JOIN
    waterway.sounding_results srs ON sd.subtrahend = srs.id JOIN
    waterway.bottlenecks bn ON srm.bottleneck_id = bn.id;

COMMIT;