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

--
-- Types and functions for ISRS location codes
--

BEGIN;

CREATE TABLE countries (
    country_code char(2) PRIMARY KEY -- ISO 3166 ALPHA-2 code
);

CREATE OR REPLACE FUNCTION is_country(code char) RETURNS boolean
AS $$
    SELECT EXISTS(SELECT 1 FROM public.countries WHERE country_code = code)
$$
    LANGUAGE sql
    STABLE PARALLEL SAFE;

--
-- Types
--

-- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
-- See RIS-Index Encoding Guide
CREATE TYPE isrs_base AS (
    country_code char(2), -- ISO 3166 country code
    locode char(3), -- without the country code:
    -- http://www.unece.org/cefact/locode/welcome.html
    fairway_section char(5),
    orc char(5), -- Object Reference Code
    hectometre int
);

CREATE DOMAIN isrs AS isrs_base
    CHECK (is_country((VALUE).country_code))
    CHECK ((VALUE).hectometre BETWEEN 0 AND 99999);

CREATE FUNCTION isrs_cmp(a isrs, b isrs) RETURNS int
AS $$
    /* TODO: handle non-matching combinations of country_codes and
       fairway_sections. Otherwise, this will give unexpected results if
       both hectometre values do not refer to the same river. */
    SELECT (a).hectometre - (b).hectometre
$$ LANGUAGE sql
    IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION isrslt(a isrs, b isrs) RETURNS boolean
AS $$
    SELECT isrs_cmp(a, b) < 0
$$ LANGUAGE sql
    IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION isrsle(a isrs, b isrs) RETURNS boolean
AS $$
    SELECT isrs_cmp(a, b) <= 0
$$ LANGUAGE sql
    IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION isrseq(a isrs, b isrs) RETURNS boolean
AS $$
    SELECT isrs_cmp(a, b) = 0
$$ LANGUAGE sql
    IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION isrsge(a isrs, b isrs) RETURNS boolean
AS $$
    SELECT isrs_cmp(a, b) >= 0
$$ LANGUAGE sql
    IMMUTABLE PARALLEL SAFE;

CREATE FUNCTION isrsgt(a isrs, b isrs) RETURNS boolean
AS $$
    SELECT isrs_cmp(a, b) > 0
$$ LANGUAGE sql
    IMMUTABLE PARALLEL SAFE;

CREATE OPERATOR <~ (
    leftarg = isrs,
    rightarg = isrs,
    function = isrslt
);

CREATE OPERATOR <~= (
    leftarg = isrs,
    rightarg = isrs,
    function = isrsle
);

CREATE OPERATOR ~= (
    leftarg = isrs,
    rightarg = isrs,
    function = isrseq,
    commutator = ~=
);

CREATE OPERATOR >~= (
    leftarg = isrs,
    rightarg = isrs,
    function = isrsge,
    commutator = <~=,
    negator = <~
);

CREATE OPERATOR >~ (
    leftarg = isrs,
    rightarg = isrs,
    function = isrsgt,
    commutator = <~,
    negator = <~=
);

CREATE OPERATOR CLASS isrs_ops FOR TYPE isrs USING btree AS
    OPERATOR 1 <~,
    OPERATOR 2 <~=,
    OPERATOR 3 ~=,
    OPERATOR 4 >~=,
    OPERATOR 5 >~,
    FUNCTION 1 isrs_cmp;

CREATE FUNCTION isrs_diff(a isrs, b isrs) RETURNS double precision
AS $$
    SELECT CAST(isrs_cmp(a, b) AS double precision)
$$ LANGUAGE sql
    IMMUTABLE PARALLEL SAFE;

CREATE TYPE isrsrange AS RANGE (
    subtype = isrs,
    subtype_opclass = isrs_ops,
    subtype_diff = isrs_diff
);


--
-- Functions
--

CREATE OR REPLACE FUNCTION isrs_fromText(
    isrs_text varchar
    )
    RETURNS isrs
AS $$
DECLARE
    isrs_len CONSTANT int = 20;
BEGIN
    isrs_text = upper(isrs_text);
    IF char_length(isrs_text) <> isrs_len
    THEN
        RAISE 'ISRS location code must be % characters long', isrs_len
            USING ERRCODE = 'invalid_parameter_value',
                DETAIL = 'Failing value: ' || isrs_text;
    ELSE
        RETURN CAST((
            substring(isrs_text from 1 for 2),
            substring(isrs_text from 3 for 3),
            substring(isrs_text from 6 for 5),
            substring(isrs_text from 11 for 5),
            CAST(substring(isrs_text from 16 for 5) AS int))
            AS isrs);
    END IF;
END;
$$
    LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION isrs_asText(
    code isrs
    )
    RETURNS text
AS $$
    SELECT code.country_code
        || code.locode
        || code.fairway_section
        || code.orc
        || lpad(CAST(code.hectometre AS text), 5, '0');
$$
    LANGUAGE sql
    IMMUTABLE PARALLEL SAFE;

COMMIT;