-- 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):
--  * Sascha Wilde <wilde@intevation.de>
--  * Tom Gottfried <tom@intevation.de>

BEGIN;

--
-- Standard users for the GEMMA database
--

-- NOTE: Passwords for these roles must be set during initial setup of
--       the database.

CREATE PROCEDURE create_login_roles(adminpw varchar, metapw varchar)
AS $$
DECLARE
    dummy_country CONSTANT varchar = '--';

    -- Role names and attributes
    admin CONSTANT varchar = 'sysadmin';
    adminrole CONSTANT varchar = 'sys_admin';
    box CONSTANT box2d = 'BOX(9.52115482500011 46.3786430870001,
        17.1483378500001 49.0097744750001)';
    meta CONSTANT varchar = 'meta_login';
    metarole CONSTANT varchar = 'metamorph';

    -- Messages
    warn_message CONSTANT varchar = 'Role %I already exists';
    warn_detail CONSTANT varchar =
        'Password, role attributes and memberships are kept as is';
    err_message CONSTANT varchar =
        'Role %I already exists but lacks necessary privileges';
BEGIN
    --
    -- Admin User
    --
    -- We need an empty dummy country for the default admin, as the user is
    -- not supposed to work on data, it should be only used to create
    -- personalized accounts.
    INSERT INTO countries (country_code) VALUES (dummy_country);

    -- Initial Admin account used to bootstrap the personalized accounts
    IF to_regrole(admin) IS NULL THEN
        INSERT INTO users.list_users VALUES (
            adminrole, admin, adminpw, dummy_country, box, '', false, true);
        RAISE NOTICE 'Default admin user ''%'' created with password ''%''',
            admin, adminpw;
    ELSE
        IF pg_has_role(admin, adminrole, 'USAGE') THEN
            RAISE WARNING USING
                MESSAGE = format(warn_message, admin),
                DETAIL = warn_detail;
            INSERT INTO internal.user_profiles (
                username, map_extent, email_address, country)
                VALUES (admin, box, '', dummy_country);
        ELSE
            RAISE USING MESSAGE = format(err_message, admin);
        END IF;
    END IF;

    --
    -- Functional Users
    --

    -- Used by GeoServer and backend
    IF to_regrole(meta) IS NULL THEN
        EXECUTE format('CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
            meta, metarole, metapw);
        RAISE NOTICE 'Backend user ''%'' created with password ''%''',
            meta, metapw;
    ELSE
        IF pg_has_role(meta, metarole, 'USAGE') THEN
            RAISE WARNING USING
                MESSAGE = format(warn_message, meta),
                DETAIL = warn_detail;
        ELSE
            RAISE USING MESSAGE = format(err_message, admin);
        END IF;
    END IF;

END;
$$ LANGUAGE plpgsql;
CALL create_login_roles(:'adminpw', :'metapw');
DROP PROCEDURE create_login_roles(varchar, varchar);

COMMIT;