CREATE TABLE user_username_history (
  period PERIOD_LOWER NOT NULL,
  user_id USER_ID NOT NULL,
  username USERNAME NOT NULL,
  created_by USER_ID NOT NULL,
  deleted_by USER_ID NULL,
  PRIMARY KEY (period, user_id),
  CHECK ((UPPER(period) IS NULL) = (deleted_by IS NULL)),
  CONSTRAINT user_username__user__fk FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT user_username__created_by__fk FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT user_username__deleted_by__fk FOREIGN KEY (deleted_by) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  EXCLUDE USING gist (username WITH =, period WITH &&),
  EXCLUDE USING gist (user_id WITH =, period WITH &&)
);

WITH usernames_agg AS (
  SELECT user_id, username, range_agg(period) AS period
  FROM users_history
  GROUP BY user_id, username
),
usernames AS (
  SELECT unnest(period) AS period, user_id, username
  FROM usernames_agg
),
usernames_matched AS (
  SELECT u.period, u.user_id, u.username, c.updated_by AS created_by, d.updated_by AS deleted_by
  FROM usernames AS u
         LEFT OUTER JOIN users_history AS c ON (u.user_id = c.user_id AND LOWER(u.period) = LOWER(c.period))
         LEFT OUTER JOIN users_history AS d ON (d.user_id = c.user_id AND UPPER(u.period) = LOWER(d.period))
  WHERE u.username IS NOT NULL
)
INSERT INTO user_username_history(period, user_id, username, created_by, deleted_by)
SELECT period, user_id, username, created_by, deleted_by
FROM usernames_matched;

DROP VIEW users_current;

ALTER TABLE users_history DROP COLUMN username;

ALTER TABLE users
ADD COLUMN period PERIOD_LOWER NULL,
ADD COLUMN deleted_by USER_ID NULL,
ADD CONSTRAINT users__deleted_by__fk FOREIGN KEY (deleted_by) REFERENCES users(user_id) ON DELETE RESTRICT ON UPDATE CASCADE;

UPDATE users
SET period = PERIOD(created_at, NULL);