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,
        report_reciever, active)
        VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address,
                NEW.report_reciever, NEW.active);

    IF NEW.active THEN
        EXECUTE format(
            'ALTER ROLE %I LOGIN', NEW.username);
    ELSE
        EXECUTE format(
            'ALTER ROLE %I NOLOGIN', NEW.username);
    END IF;

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

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,
             report_reciever, active)
        = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address,
           NEW.report_reciever, NEW.active)
        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;

    IF NEW.active <> OLD.active THEN
        IF NEW.active THEN
            EXECUTE format(
                'ALTER ROLE %I LOGIN', cur_username);
        ELSE
            EXECUTE format(
                'ALTER ROLE %I NOLOGIN', cur_username);
        END IF;
    END IF;

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


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;

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

GRANT EXECUTE ON FUNCTION delete_user to sys_admin;