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

CREATE OR REPLACE VIEW sys_admin.list_users AS
    SELECT r.rolname, p.*
        FROM users.user_profiles p
            JOIN pg_roles u ON p.username = u.rolname
            JOIN pg_auth_members a ON u.oid = a.member
            JOIN pg_roles r ON a.roleid = r.oid;


CREATE OR REPLACE FUNCTION sys_admin.create_user(
       userrole varchar,
       username users.user_profiles.username%TYPE,
       pw varchar,
       country users.user_profiles.country%TYPE,
       map_extent users.user_profiles.map_extent%TYPE,
       email_address users.user_profiles.email_address%TYPE
    )
    RETURNS void
AS $$
BEGIN
    IF map_extent IS NULL
    THEN
        map_extent = ST_Extent(area) FROM users.responsibility_areas ra
            WHERE ra.country = create_user.country;
    END IF;
    INSERT INTO users.user_profiles VALUES (
        username, country, map_extent, email_address);
    EXECUTE format(
        'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw);
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;


CREATE OR REPLACE FUNCTION sys_admin.update_user(
       username users.user_profiles.username%TYPE,
       new_userrole varchar,
       new_username users.user_profiles.username%TYPE,
       new_pw varchar,
       new_country users.user_profiles.country%TYPE,
       new_map_extent users.user_profiles.map_extent%TYPE,
       new_email_address users.user_profiles.email_address%TYPE
    )
    RETURNS void
AS $$
DECLARE
    cur_username name;
    cur_userrole name;
BEGIN
    cur_username = username;

    UPDATE users.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_username <> cur_username
    THEN
        EXECUTE format(
            'ALTER ROLE %I RENAME TO %I', username, new_username);
        cur_username = new_username;
    END IF;

    cur_userrole = rolname FROM pg_roles r
        JOIN pg_auth_members a ON r.oid = a.roleid
        WHERE member = (
            SELECT oid FROM pg_roles WHERE rolname = cur_username);
    IF new_userrole <> cur_userrole
    THEN
        EXECUTE format(
            'REVOKE %I FROM %I', cur_userrole, cur_username);
    END IF;
    -- GRANT new_userrole unconditionally to ensure it's an error to upgrade
    -- a non-existent cur_username (GRANTing a role twice is not an error)
    EXECUTE format(
        'GRANT %I TO %I', new_userrole, cur_username);

    IF new_pw IS NOT NULL AND new_pw <> ''
    THEN
        EXECUTE format(
            'ALTER ROLE %I PASSWORD %L', cur_username, new_pw);
    END IF;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;


CREATE OR REPLACE FUNCTION sys_admin.delete_user(
       username users.user_profiles.username%TYPE
    )
    RETURNS void
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 = 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', username);
    DELETE FROM users.user_profiles p WHERE p.username = delete_user.username;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;