MySQL Insert with functionality similar to Window's default file naming

I have a table with a name column. Initially a name is not added to the table but I would like to add a default name when a new row is inserted, much like window's functionally when creating a new file. I'm trying to figure out how to query the number which is suppose to be next in the sequence.

For example, if my table looks like this:

id | name
==========
1  | New Name (1)
2  | real name
3  | New Name

The next inserted row's name should be "New Name (2)". If my table looks like this:

id | name
==========
1  | New Name (2)
2  | real name

or this:

id | name
==========
1  | another name
2  | real name

The next inserted row's name should be "New Name". If my table looks like this:

id | name
==========
1  | New Name (2)
2  | real name
3  | New Name
4  | New Name (3)

The next inserted row's name should be "New Name (1)". Thus far I was able to create the query to get the existing numbers ("New Name" = 0)

    SELECT SUBSTRING_INDEX(SUBSTR(d.name,INSTR(d.name,'(') + 1), ')', 1)
    FROM data d
    WHERE d.widget_name REGEXP '^New[[:space:]]Name[[:space:]]\([[:digit:]]\)$'
    UNION 
    SELECT 0 
    FROM data d
    WHERE d.name REGEXP '^New[[:space:]]Name$' 

Now I need a way to to turn that list of numbers to a single number which will indicate whats the next default name enumeration. I've tried using NOT EXISTS from this question but I couldn't figure out how to use the code above both in FROM and the WHERE.

I also tried to do this by creating a row_num functionality using a_horse_with_no_name's answer in this question. Assuming num_data is the result of the query(trying to keep it clean) table and it's column name is name_num:

  SELECT IFNULL(row_number, (SELECT IFNULL(MAX(name_num),0)
                             FROM num_data))
  FROM (SELECT @rownum:=@rownum + 1 AS row_number,  t.*
        FROM (num_data) t,
        (SELECT @rownum := 0) r) gap_table
  WHERE gap_table.row_number <> gap_table.widget_num
  ORDER by row_number
  LIMIT 1;

But that didn't seem to get it right too.