INSERT INTO
   users
   SELECT
      username,
      password,
      FROM_UNIXTIME(time)
   FROM
      authreg LEFT JOIN active ON CONCAT(authreg.username, '@', authreg.realm) = active.`collection-owner`;
INSERT INTO
   last
   SELECT
      username,
      time AS seconds,
      "" AS state
   FROM
      authreg, logout
   WHERE
      `collection-owner` = CONCAT(username, "@", realm);
INSERT INTO
   rosterusers
   SELECT
      SUBSTRING_INDEX(`roster-items`.`collection-owner`,"@",1) AS username,
      `roster-items`.jid AS jid,
      `roster-items`.name AS nick,
      IF(`roster-items`.`to` = 1 AND `roster-items`.`from` = 1, 'B', IF(`roster-items`.`to` = 1,'T','F')) AS suscription,
      CASE `roster-items`.ask WHEN 0 THEN 'N' WHEN 1 THEN 'O' ELSE 'I' END AS ask,
      '' AS askmessage,
      'N' as server,
      '' as subscribe,
      'item' as `type`,
      CURRENT_TIMESTAMP as created_at
   FROM
      `roster-items`
   ORDER BY jid;
INSERT INTO
   rostergroups
   SELECT
      SUBSTRING_INDEX(`collection-owner`,"@", 1) AS username,
      jid,
      `group` AS grp
   FROM
      `roster-groups`;
