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

--
-- Types
--

-- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
-- See RIS-Index Encoding Guide
CREATE TYPE isrs AS (
    country_code char(2), -- ISO 3166 country code
    -- could be validated against countries table.
    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 -- should be constrained to five digits
);

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;

COMMIT;