#
# Convert a phpNuke User Database to a phpBB 2.0 User Database
#
# Queries build by Matthijs van de Water @ 03-31-2002
#

#
# Do the user table
#
# You can alter the timezone by editing the '0'
# Date conversion is a bit messy, but it works ;)
#
INSERT INTO phpbb_users (user_id, username, user_email, user_website, 
    user_regdate, user_icq, user_occ, user_from, user_interests, 
    user_aim, user_yim, user_msnm, user_password, user_active, user_timezone)
  SELECT uid, name, email, url, 
    UNIX_TIMESTAMP(
      CONCAT(
        SUBSTRING_INDEX(user_regdate, ' ', -1),
        CASE SUBSTRING_INDEX(user_regdate, ' ', 1) 
          WHEN 'Jan' THEN '01' 
          WHEN 'Feb' THEN '02' 
          WHEN 'Mar' THEN '03' 
          WHEN 'Apr' THEN '04' 
          WHEN 'May' THEN '05' 
          WHEN 'Jun' THEN '06'
          WHEN 'Jul' THEN '07'
          WHEN 'Aug' THEN '08'
          WHEN 'Sep' THEN '09'
          WHEN 'Oct' THEN '10'
          WHEN 'Nov' THEN '11'
          WHEN 'Dec' THEN '12'
        END,
        SUBSTRING_INDEX(SUBSTRING_INDEX(user_regdate, ' ', -2), ',', 1)  
      )
    ) AS user_regdate,
    user_icq, user_occ, user_from, user_intrest,
    user_aim, user_yim, user_msnm, MD5(pass), 1, '0'
  FROM nuke_users
  ORDER BY uid;

#
# Make single user groups
#
INSERT INTO phpbb_groups (group_name, group_description, group_single_user)
  SELECT username, 'Personal User', 1
  FROM phpbb_users
  ORDER BY user_id;

#
# Make user -> group connection
#
INSERT INTO phpbb_user_group (group_id, user_id)
  SELECT g.group_id, u.user_id 
  FROM phpbb_groups g LEFT JOIN phpbb_users u ON g.group_name=u.username
  ORDER BY u.user_id;