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

--
-- pgTAP test script for privileges and RLS policies
--

CREATE FUNCTION test_privs() RETURNS SETOF TEXT AS
$$
DECLARE the_schema CONSTANT varchar = 'waterway';
DECLARE the_table varchar;
BEGIN
    FOR the_table IN
        SELECT table_name
            FROM information_schema.tables
            WHERE table_schema = the_schema
    LOOP
        RETURN NEXT table_privs_are(
            the_schema,
            the_table,
            'waterway_user',
            ARRAY['SELECT'],
            format('waterway_user can SELECT from %I.%I',
                the_schema, the_table));
    END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM test_privs();

--
-- Run tests as unprivileged user
--
SET SESSION AUTHORIZATION test_user_at;

SELECT throws_ok($$
    CREATE TABLE test()
    $$,
    42501, NULL,
    'No objects can be created');

SELECT isnt_empty($$
    SELECT * FROM waterway.bottlenecks
    $$,
    'Staged data should be visible');

SELECT is_empty($$
    SELECT * FROM waterway.bottlenecks WHERE NOT staging_done
    $$,
    'Only staged data should be visible');

SELECT isnt_empty($$
    SELECT * FROM users.templates
    $$,
    'User should see templates associated to his country');

SELECT ok(
    users.user_country() = ALL(
        SELECT country FROM users.templates),
    'User should only see templates associated to his country');

--
-- Run tests as waterway administrator
--
SET SESSION AUTHORIZATION test_admin_at;

PREPARE bn_insert (varchar, geometry(MULTIPOLYGON, 4326)) AS
    INSERT INTO waterway.bottlenecks (
        gauge_location, validity,
        bottleneck_id, stretch, area, rb, lb, responsible_country,
        revisiting_time, limiting, date_info, source_organization)
        SELECT
            location, validity,
            $1,
            isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
                ('AT', 'XXX', '00001', '00000', 2)::isrs),
            $2, 'AT', 'AT', 'AT',
            1, 'depth', current_timestamp, 'testorganization'
        FROM waterway.gauges
        WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs
            AND NOT erased;
SELECT lives_ok($$
    EXECUTE bn_insert(
        'test1',
        ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326))
    $$,
    'Waterway admin can insert data within his region');
SELECT throws_ok($$
    EXECUTE bn_insert(
        'test2',
        ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326))
    $$,
    42501, NULL,
    'Waterway admin cannot insert data outside his region');

-- Ensure a USING clause prevents access in an UPDATE
SELECT is_empty($$
    WITH a AS (SELECT users.current_user_area_utm() AS a)
    UPDATE waterway.bottlenecks
        SET objnam = 'Now it''s mine',
            area = ST_geomfromtext(
                'MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)
        WHERE bottleneck_id = 'testbottleneck3'
        RETURNING *
    $$,
    'Waterway admin cannot move data from outside his region inside');

-- Ensure a WITH CHECK or USING clause prevents writing such rows
SELECT throws_ok($$
    WITH a AS (SELECT users.current_user_area_utm() AS a)
    UPDATE waterway.bottlenecks
        SET objnam = 'Give-away',
            area = ST_geomfromtext(
                'MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326)
        WHERE bottleneck_id = 'testbottleneck2'
        RETURNING *
    $$,
    42501, NULL,
    'Waterway admin cannot move data from inside his region outside');

SELECT is_empty($$
    WITH a AS (SELECT users.current_user_area_utm() AS a)
    DELETE FROM waterway.bottlenecks
        WHERE NOT ST_Covers((SELECT a FROM a),
            ST_Transform(
                CAST(area AS geometry), ST_SRID((SELECT a FROM a))))
        RETURNING *
    $$,
    'Waterway admin cannot delete data outside his region');

-- template management
SELECT lives_ok($$
    INSERT INTO users.templates (template_name, template_data, country)
        VALUES ('New AT', '\x', 'AT')
    $$,
    'Waterway admin can add templates for his country');

SELECT throws_ok($$
    INSERT INTO users.templates (template_name, template_data, country)
        VALUES ('New RO', '\x', 'RO')
    $$,
    42501, NULL,
    'Waterway admin cannot add template for other country');

SELECT isnt_empty($$
    UPDATE users.templates SET template_data = '\xDABE'
        WHERE template_name = 'AT' RETURNING *
    $$,
    'Waterway admin can alter templates for own country');

SELECT is_empty($$
    UPDATE users.templates SET template_data = '\xDABE'
        WHERE template_name = 'RO' RETURNING *
    $$,
    'Waterway admin cannot alter templates for other country');

SELECT isnt_empty($$
    DELETE FROM users.templates WHERE template_name = 'AT' RETURNING *
    $$,
    'Waterway admin can delete templates for own country');

SELECT is_empty($$
    DELETE FROM users.templates WHERE template_name = 'RO' RETURNING *
    $$,
    'Waterway admin cannot delete templates for other country');

-- import management
SET SESSION AUTHORIZATION test_sys_admin1;
SELECT lives_ok($$
    WITH
    job AS (
        INSERT INTO import.imports (kind, username, data) VALUES (
            'test', current_user, 'test') RETURNING id),
    log AS (
        INSERT INTO import.import_logs (import_id, msg)
            SELECT id, 'test' FROM job)
    INSERT INTO import.track_imports (import_id, relation, key)
        SELECT id, 'waterway.bottlenecks', 2 FROM job
    $$,
    'Sys_admin can add import job and related data');

SELECT lives_ok($$
    WITH
    config AS (
        INSERT INTO import.import_configuration (kind, username) VALUES (
            'test', current_user) RETURNING id)
    INSERT INTO import.import_configuration_attributes
        SELECT id, 'test key', 'test value' FROM config
    $$,
    'Sys_admin can add import config and related data');

SET SESSION AUTHORIZATION test_admin_at;
SELECT lives_ok($$
    WITH
    job AS (
        INSERT INTO import.imports (kind, username, data) VALUES (
            'test', current_user, 'test') RETURNING id),
    log AS (
        INSERT INTO import.import_logs (import_id, msg)
            SELECT id, 'test' FROM job)
    INSERT INTO import.track_imports (import_id, relation, key)
        SELECT id, 'waterway.bottlenecks', 0 FROM job
    $$,
    'Waterway admin can add import job and related data');

SET SESSION AUTHORIZATION test_admin_at2;
SELECT bag_has($$
    SELECT username FROM users.list_users
    $$,
    $$
    WITH job AS (
        UPDATE import.imports SET state = 'accepted'
            RETURNING id, username),
    log AS (
        INSERT INTO import.import_logs (import_id, msg)
            SELECT id, 'test continued' FROM job)
    SELECT username FROM job
    $$,
    'Waterway admin can edit import jobs from his country only');

SELECT bag_hasnt($$
    WITH job AS (
        UPDATE import.imports SET state = 'accepted'
            RETURNING id, username),
    log AS (
        INSERT INTO import.import_logs (import_id, msg)
            SELECT id, 'test continued' FROM job)
    SELECT username FROM job
    $$,
    $$
    SELECT username FROM users.list_users WHERE rolname = 'sys_admin'
    $$,
    'Waterway admin cannot edit import jobs of sys_admins');

SELECT lives_ok($$
    WITH
    config AS (
        INSERT INTO import.import_configuration (kind, username) VALUES (
            'test', current_user) RETURNING id)
    INSERT INTO import.import_configuration_attributes
        SELECT id, 'test key', 'test value' FROM config
    $$,
    'Waterway admin can add import config and related data');

SET SESSION AUTHORIZATION test_admin_at;
SELECT bag_has($$
    SELECT username FROM users.list_users
    $$,
    $$
    WITH config AS (
        UPDATE import.import_configuration SET kind = 'test'
            RETURNING id, username),
    attrib AS (
        INSERT INTO import.import_configuration_attributes
            SELECT id, 'test continued', 'test value' FROM config),
    attrib_upd AS (
        UPDATE import.import_configuration_attributes SET v = 'test v'
            WHERE import_configuration_id IN (SELECT id FROM config))
    SELECT username FROM config
    $$,
    'Waterway admin can edit import config from his country only');

SELECT bag_hasnt($$
    WITH config AS (
        UPDATE import.import_configuration SET kind = 'test'
            RETURNING id, username),
    attrib AS (
        INSERT INTO import.import_configuration_attributes
            SELECT id, 'test continued 1', 'test value' FROM config),
    attrib_upd AS (
        UPDATE import.import_configuration_attributes SET v = 'test v'
            WHERE import_configuration_id IN (SELECT id FROM config))
    SELECT username FROM config
    $$,
    $$
    SELECT username FROM users.list_users
        WHERE rolname = 'sys_admin' AND country = 'RO'
    $$,
    'Waterway admin cannot edit import config from sys_admin');

SET SESSION AUTHORIZATION test_admin_ro;
SELECT throws_ok($$
    INSERT INTO import.import_logs (import_id, msg)
        VALUES (currval(pg_get_serial_sequence('import.imports', 'id')),
            'test')
    $$,
    42501, NULL,
    'Waterway admin cannot add log messages to other countries imports');

SELECT throws_ok($$
    DELETE FROM import.track_imports
        WHERE import_id = currval(
            pg_get_serial_sequence('import.imports', 'id'))
    $$,
    42501, NULL,
    'Waterway admin cannot delete tracking data of other countries imports');

SELECT throws_ok($$
    INSERT INTO import.import_configuration_attributes
        VALUES (currval(pg_get_serial_sequence(
                'import.import_configuration', 'id')),
            'test', 'test value')
    $$,
    42501, NULL,
    'Waterway admin cannot add attributes to other countries import config');

SELECT throws_ok($$
    UPDATE import.import_configuration_attributes SET v = 'evil'
        WHERE import_configuration_id = currval(
            pg_get_serial_sequence('import.import_configuration', 'id'))
    $$,
    42501, NULL,
    'Waterway admin cannot overwrite attributes of other countries config');