-- 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>
--  * Sascha Teichmann <sascha.teichmann@intevation.de>

--
-- Test data used in *_tests.sql scripts
--

INSERT INTO countries VALUES ('AT'), ('RO'), ('DE');

INSERT INTO users.responsibility_areas VALUES
    ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)),
    ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326));

INSERT INTO users.list_users VALUES (
    'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx');
INSERT INTO users.list_users VALUES (
    'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy');
INSERT INTO users.list_users VALUES (
    'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy');
INSERT INTO users.list_users VALUES (
    'waterway_admin', 'test_admin_at2', 'admin_at2$', 'AT', NULL, 'yyy');
INSERT INTO users.list_users VALUES (
    'waterway_admin', 'test_admin_ro', 'admin_ro1$', 'RO', NULL, 'yyx');
INSERT INTO users.list_users VALUES (
    'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz');

INSERT INTO limiting_factors VALUES ('depth'), ('width');

WITH
gs AS (
    INSERT INTO waterway.gauges (
        location,
        validity,
        objname,
        geom,
        zero_point,
        date_info,
        source_organization,
        lastupdate)
    VALUES (
        ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
        tstzrange(current_timestamp - '1 day'::interval, current_timestamp),
        'testgauge',
        ST_geomfromtext('POINT(0 0)', 4326),
        0,
        current_timestamp,
        'testorganization',
        current_timestamp)
    RETURNING location, validity, validity),
bns AS (
    VALUES (
        'testbottleneck1',
        isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
            ('AT', 'XXX', '00001', '00000', 2)::isrs),
        ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326),
        'AT', 'AT', 'AT',
        1, 'depth', current_timestamp, 'testorganization', false
    ), (
        'testbottleneck2',
        isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
            ('AT', 'XXX', '00001', '00000', 2)::isrs),
        ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326),
        'AT', 'AT', 'AT',
        1, 'depth', current_timestamp, 'testorganization', true
    ))
INSERT INTO waterway.bottlenecks (
    gauge_location, gauge_validity, validity,
    bottleneck_id, stretch, area, rb, lb, responsible_country,
    revisiting_time, limiting, date_info, source_organization, staging_done)
    SELECT * FROM gs, bns;

INSERT INTO waterway.distance_marks_virtual VALUES (
    ('AT', 'XXX', '00001', '00000', 0)::isrs,
    ST_SetSRID('POINT(0 0)'::geometry, 4326),
    'someENC'
), (
    ('AT', 'XXX', '00001', '00000', 1)::isrs,
    ST_SetSRID('POINT(1 0)'::geometry, 4326),
    'someENC'
), (
    ('AT', 'XXX', '00001', '00000', 2)::isrs,
    ST_SetSRID('POINT(1.6 0)'::geometry, 4326),
    'someENC'
);

INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES (
    ST_SetSRID(ST_CurveToLine(
        'CIRCULARSTRING(0 0, 0.5 0.5, 0.6 0.4)'),
        4326),
    'testriver'
), (
    ST_SetSRID(ST_CurveToLine('CIRCULARSTRING(0.6 0.4, 1 0, 1.5 -0.00001)'),
        4326),
    'testriver'
), (
    ST_SetSRID('LINESTRING(0.5 0.5, 1 1)'::geometry, 4326),
    'testriver'
), (
    ST_SetSRID('LINESTRING(1.5 0, 1.55001 0)'::geometry, 4326),
    'testriver'
), (
    ST_SetSRID('LINESTRING(1.55 0, 2 0)'::geometry, 4326),
    'testriver'
);

-- Simulate waterway area as non-intersecting buffers around axis
WITH RECURSIVE
buffer AS (
    SELECT id, ST_Buffer(wtwaxs, 10000, 'endcap=flat')::geometry AS buf
        FROM waterway.waterway_axis),
cleaned AS (
    (SELECT ARRAY[id] AS ids, buf AS cbuf, buf AS others
        FROM buffer ORDER BY id FETCH FIRST ROW ONLY)
    UNION
    (SELECT ids || id,
            ST_Difference(buf, others),
            ST_Union(buf, others)
        FROM cleaned, buffer
        WHERE id <> ALL(ids)
        ORDER BY id ASC, ids DESC
        FETCH FIRST ROW ONLY)),
cleaned1 AS (
    SELECT DISTINCT geom
        FROM (SELECT (ST_Dump(cbuf)).geom FROM cleaned) AS dmp,
            waterway.waterway_axis
        WHERE ST_Intersects(geom, wtwaxs))
INSERT INTO waterway.waterway_area (area) SELECT geom FROM cleaned1;

INSERT INTO users.templates (template_name, country, template_data)
    VALUES ('AT', 'AT', '\x'), ('RO', 'RO', '\x');

WITH
job AS (
    INSERT INTO import.imports (kind, username, data) VALUES (
        'test', 'test_admin_ro', 'test') RETURNING id),
log AS (
    INSERT INTO import.import_logs (import_id, msg)
        SELECT id, 'test' FROM job)
INSERT INTO import.track_imports
    SELECT id, 'waterway.bottlenecks', 1 FROM job;

WITH
config AS (
    INSERT INTO import.import_configuration (kind, username) VALUES (
        'test', 'test_admin_ro') RETURNING id)
INSERT INTO import.import_configuration_attributes
    SELECT id, 'test key', 'test value' FROM config;