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

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_adress users.user_profiles.email_adress%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_adress);
    EXECUTE format(
        'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw);
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;