Nested partition in postgres

Question

I am attempting to create referral code infrastructure. I would like to use upto the first 12 characters of a user's email, deduped with an integer:

So given three users with the emails bob@gmail.com, bob@hotmail.com, and bob@yahoo.com, I would like to generate codes bob, bob1, and bob2.

I've done this with the following sql:

UPDATE user_referral_codes urc
    SET referral_code = (
      LEFT(LEFT(email, strpos(email, '@') - 1), 12)
      || (CASE WHEN seqnum > 1 THEN (seqnum-1)::TEXT ELSE '' END)
    )
    FROM (
      SELECT
       users.*,
        row_number() OVER (
          PARTITION BY LEFT(LEFT(email, strpos(email, '@') - 1), 12)
          ORDER BY id
        ) AS seqnum
      FROM users
    ) AS u
  WHERE urc.user_id = u.id;

This works fine, however there are two edgecases:

1) The referral codes must be globally unique, so adding the users bob1@gmail.com and bob2@gmail.com into the mix should result in the following pairings:

bob@gmail.com | bob
bob@hotmail.com | bob3
bob@yahoo.com | bob4
bob1@gmail.com | bob1
bob2@gmail.com | bob2

How can I account for this?

2) There is a second code, coupon_codes. How do I also ensure no duplication with this table?

e.g. if a coupon_code bob2 were to exist, then bob2@gmail.com should also get referral code bob21?

Sample schema:

    CREATE TABLE users(
  id                    bigint                      NOT NULL PRIMARY KEY,
  email                 TEXT                        NOT NULL UNIQUE
 );

CREATE TABLE user_referral_codes(
  user_id                 bigint          NOT NULL REFERENCES users (id),
  referral_code           TEXT
);

CREATE TABLE coupon_codes(
  code_id                 TEXT
);

INSERT INTO users(id, email) VALUES(1, 'bob@flockcover.com');
INSERT INTO users(id, email) VALUES(2, 'bob@google.com');
INSERT INTO users(id, email) VALUES(3, 'bob@gmail.com');
INSERT INTO users(id, email) VALUES(4, 'bob1@gmail.com');

INSERT INTO user_referral_codes(user_id) VALUES(1);
INSERT INTO user_referral_codes(user_id) VALUES(2);
INSERT INTO user_referral_codes(user_id) VALUES(3);
INSERT INTO user_referral_codes(user_id) VALUES(4);

INSERT INTO coupon_codes(code_id) VALUES('bob2');

Sample select query demonstrating current (incomplete) behavior:

SELECT LEFT(LEFT(email, strpos(email, '@') - 1), 12)
  || (CASE WHEN seqnum > 1 THEN (seqnum-1)::TEXT ELSE '' END)
  FROM (
    SELECT
      users.*,
      row_number() OVER (
        PARTITION BY LEFT(LEFT(email, strpos(email, '@') - 1), 12)
        ORDER BY id
      ) AS seqnum
    FROM users
  ) as u;

sqlfiddle link demonstrating problem: http://sqlfiddle.com/#!17/0a6ea