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

--
-- Functions encapsulating user management functionality and
-- exposing it to appropriately privileged users
--

CREATE OR REPLACE FUNCTION internal.check_password(
    pw varchar
    )
    RETURNS varchar
AS $$
DECLARE
    min_len CONSTANT int = 8;
BEGIN
    IF char_length(pw) < min_len
        OR pw NOT SIMILAR TO '%[^[:alnum:]]%'
        OR pw NOT SIMILAR TO '%[[:digit:]]%'
    THEN
        RAISE invalid_password USING MESSAGE = 'Invalid password';
    ELSE
        RETURN pw;
    END IF;
END;
$$
    LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION users.current_user_area_utm()
    RETURNS geometry
    AS $$
        DECLARE utm_area geometry;
        BEGIN
            SELECT ST_Union(ST_Transform(area::geometry, z))
                INTO STRICT utm_area
                FROM (SELECT area,
                        best_utm(ST_Collect(area::geometry) OVER ()) AS z
                    FROM users.stretches st
                        JOIN users.stretch_countries stc
                            ON stc.stretch_id = st.id
                    WHERE country = (SELECT country
                        FROM users.list_users
                        WHERE username = current_user)) AS st;
    RETURN utm_area;
        END;
    $$
    LANGUAGE plpgsql
    STABLE PARALLEL SAFE;


CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
AS $$
BEGIN
    IF NEW.map_extent IS NULL
    THEN
        NEW.map_extent = ST_Extent(CAST(area AS geometry))
            FROM users.stretches st
                JOIN users.stretch_countries stc ON stc.stretch_id = st.id
            WHERE stc.country = NEW.country;
    END IF;

    IF NEW.username IS NOT NULL
    -- otherwise let the constraint on user_profiles speak
    THEN
        EXECUTE format(
            'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
            NEW.username,
            NEW.rolname,
            internal.check_password(NEW.pw));
    END IF;

    INSERT INTO internal.user_profiles (
        username, country, map_extent, email_address)
        VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);

    -- Do not leak new password
    NEW.pw = '';
    RETURN NEW;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;

CREATE TRIGGER create_user INSTEAD OF INSERT ON users.list_users FOR EACH ROW
    EXECUTE PROCEDURE internal.create_user();

CREATE OR REPLACE FUNCTION internal.update_metamorph() RETURNS trigger
AS $$
BEGIN
    EXECUTE format('GRANT %I TO metamorph', NEW.username);
    RETURN NEW;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;

-- Note that PostgreSQL fires triggers for the same event in alphabetical
-- order! Make sure that the new role is created before this trigger is fired.
CREATE TRIGGER update_metamorph INSTEAD OF INSERT ON users.list_users
    FOR EACH ROW
    EXECUTE PROCEDURE internal.update_metamorph();


-- Prevent roles other than sys_admin to update any user but
-- themselves (affects waterway_admin)
CREATE OR REPLACE FUNCTION internal.authorize_update_user() RETURNS trigger
AS $$
BEGIN
    IF OLD.username <> current_user
        AND NOT pg_has_role('sys_admin', 'MEMBER')
    THEN
        RETURN NULL;
    ELSE
        RETURN NEW;
    END IF;
END;
$$
    LANGUAGE plpgsql;

-- Note that PostgreSQL fires triggers for the same event in alphabetical
-- order! Make sure that authorization takes place before any other trigger
-- is fired that might execute otherwise unauthorized statements!
CREATE TRIGGER authorize_update_user INSTEAD OF UPDATE ON users.list_users
    FOR EACH ROW
    EXECUTE PROCEDURE internal.authorize_update_user();


CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger
AS $$
DECLARE
    cur_username varchar;
BEGIN
    cur_username = OLD.username;

    IF NEW.username <> cur_username
    THEN
        EXECUTE format(
            'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username);
        cur_username = NEW.username;
    END IF;

    UPDATE internal.user_profiles p
        SET (username, country, map_extent, email_address)
        = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address)
        WHERE p.username = cur_username;

    IF NEW.rolname <> OLD.rolname
    THEN
        EXECUTE format(
            'REVOKE %I FROM %I', OLD.rolname, cur_username);
        EXECUTE format(
            'GRANT %I TO %I', NEW.rolname, cur_username);
    END IF;

    IF NEW.pw IS NOT NULL AND NEW.pw <> ''
    THEN
        EXECUTE format(
            'ALTER ROLE %I PASSWORD %L',
            cur_username,
            internal.check_password(NEW.pw));
    END IF;

    -- Do not leak new password
    NEW.pw = '';
    RETURN NEW;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;

CREATE TRIGGER update_user INSTEAD OF UPDATE ON users.list_users FOR EACH ROW
    EXECUTE PROCEDURE internal.update_user();


CREATE OR REPLACE FUNCTION internal.delete_user() RETURNS trigger
AS $$
DECLARE
    bid int;
BEGIN
    -- Terminate the users backends started before the current transaction
    FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = OLD.username
    LOOP
        PERFORM pg_terminate_backend(bid);
    END LOOP;
    -- Note that any backend that might be started during the transaction
    -- in which this function is executed will not be terminated but lost
    -- without any privileges after commiting this transaction

    -- Delete user
    EXECUTE format('DROP ROLE %I', OLD.username);
    DELETE FROM internal.user_profiles p
        WHERE p.username = OLD.username;

    RETURN OLD;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;

CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW
    EXECUTE PROCEDURE internal.delete_user();

--
-- delete_user deletes a user if she/he is not an admin or an admin w/o imports.
-- It deactivates admins w/ imports.
-- Returns 0 if the role does not exsist, 1 if the role was deleted, 2 if it was
-- deactivated.
--
CREATE OR REPLACE FUNCTION delete_user(rolename text) RETURNS int
AS $$
DECLARE
    user_exists bool;
    is_admin    bool;
    has_imports bool;
BEGIN
    SELECT EXISTS (SELECT 1 FROM pg_roles
        WHERE rolname = rolename)
        INTO user_exists;

    IF NOT user_exists THEN
        RETURN 0;
    END IF;

    SELECT EXISTS (SELECT 1 FROM pg_roles
        WHERE pg_has_role(rolename, oid, 'member') AND
            rolname IN ('waterway_admin', 'sys_admin'))
        INTO is_admin;

    -- None admins can be deleted.
    IF NOT is_admin THEN
        DELETE FROM users.list_users WHERE username = rolename;
        RETURN 1;
    END IF;

    SELECT EXISTS (SELECT 1 FROM import.imports
        WHERE username = rolename)
        INTO has_imports;

    -- Admins w/o imports can be deleted.
    IF NOT has_imports THEN
        DELETE FROM users.list_users WHERE username = rolename;
        RETURN 1;
    END IF;

    -- Admins w/ imports have to be deactivated.
    UPDATE users.list_users
        SET (email_address, report_reciever, active) =
            ('nomail@example.com', false, false)
        WHERE username = rolename;

    -- Do not allow to login any more.
    EXECUTE format(
        'ALTER ROLE %I NOLOGIN', rolename);

    RETURN 2;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;



-- To set a role from a hex-encoded user name (which is save from SQL injections).
CREATE OR REPLACE FUNCTION public.setrole(role text) RETURNS void
AS $$
BEGIN
    IF role IS NOT NULL AND role <> '' THEN
        EXECUTE format('SET ROLE %I', convert_from(decode(role, 'hex'), 'UTF-8'));
    END IF;
END;
$$
    LANGUAGE plpgsql;

-- To set a role in form of a plannable statement (which is save from SQL injections).
CREATE OR REPLACE FUNCTION public.setrole_plan(role text) RETURNS void
AS $$
BEGIN
    IF role IS NOT NULL AND role <> '' THEN
        EXECUTE format('SET ROLE %I', role);
    END IF;
END;
$$
    LANGUAGE plpgsql;