-- 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 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 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 TYPE isrsrange AS RANGE (
    subtype = isrs
);

--
-- Functions
--

CREATE OR REPLACE FUNCTION isrs_fromText(
    isrs_text varchar
    )
    RETURNS isrs
AS $$
DECLARE
    isrs_len CONSTANT int = 20;
BEGIN
    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;