CREATE OR REPLACE FUNCTION get_users_that_can_see_user(target_user_id UUID)
    RETURNS TABLE (user_id UUID) AS $$
BEGIN
    RETURN QUERY
        -- Users directly related to the target user
        SELECT ur.user_id
        FROM user_relation ur
        WHERE ur.other_id = target_user_id

        UNION

        -- Users where target user is related to them
        SELECT ur.other_id AS user_id
        FROM user_relation ur
        WHERE ur.user_id = target_user_id

        UNION

        -- Users who share a server with the target user
        SELECT sm.user_id
        FROM server_member sm
                 JOIN server_member sm2 ON sm.server_id = sm2.server_id
        WHERE sm2.user_id = target_user_id
          AND sm.user_id != target_user_id

        UNION

        -- Users who share a channel with the target user (DM or group)
        SELECT cr.user_id
        FROM channel_recipient cr
                 JOIN channel_recipient cr2 ON cr.channel_id = cr2.channel_id
        WHERE cr2.user_id = target_user_id
          AND cr.user_id != target_user_id;
END;
$$ LANGUAGE plpgsql;