# phpBB 2.0 to XoopsBB 1.0 Converter
# Copyright (C) 2002 Randall Emery
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.

# Purpose:
#
# The script was designed to transfer categories, forums, users, and posts
# from phpBB 2.0 to XoopsBB 1.0.
#
# Assumptions:
#
# - phpBB 2.0 and XoopsBB 1.0 are installed on the same database
# - if user is in both phpBB an XoopsBB, they have the same user name in
# - XoopsBB 1.0 is currently empty and phpBB 2.0 has contents to transfer
# - phpBB 2.0 tables are prefixed with "phpbb_"
# - XoopsBB 1.0 tables are prefixed with "xoops_bb_"
#
# If the phpBB 2.0 and XoopsBB 1.0 tables are on different databases, please
# do a dump of the phpBB 2.0 tables and add then do a restore to the
# database that contains your XOOPS installation.
#
# If you have users on both systems, make sure that they have the same user
# name in each system before running this script.
#
# If your table prefixes differ, please do a global search on these prefixes,
# replacing them with the correct prefixes for your installation.
#
# Directions:
#
# This script can be executed using phpMyAdmin or from the command line.
# Using phpMyAdmin, you can first click on the name of your database, then
# scroll down until you see "Run SQL query/queries on database". You can either
# cut and paste this script into this area, or locate this script from your
# home machine by pressing the "Browse:" button and then clicking on "Go."
# To run this script from the command line, use the following format:
# MySQL -u -p -h database < phpBB2_XoopsBB_Converter.sql
#
# Notes:
#
# Due to differences in supported functionality between phpBB 2.0 and XoopsBB 1.0,
# some items cannot be converted:
#
# - Polls that accompany topics
# - Topics watch (e-mail alerts when responses are posted)
# - Security by group (groups are copied, but security is translated to indidual users)
#
# Known issues:
#
# - Number of posts is not converted for users that already exist in Xoops
# - If your users have avatars, they will have to reload them

# Preliminary step - clear out Xoops_BB tables
# This is here primarily in case there is an error in the script and you need to execute again.
#
# Modified by Hervé Thouzard the 17 april 2004
# The missing records in the xoops_groups_users_link are now added
# WARNING don't forget to modify this table manually once the operation is finished
# to change your webmaster group from 2 to 1

DELETE FROM xoops_bb_posts_text;
DELETE FROM xoops_bb_posts;
DELETE FROM xoops_bb_topics;
DELETE FROM xoops_bb_forum_access;
DELETE FROM xoops_bb_forums;
DELETE FROM xoops_bb_categories;
DELETE FROM xoops_groups_users_link;

# First, copy over categories.

INSERT INTO xoops_bb_categories (cat_id, cat_title, cat_order)
SELECT cat_id, cat_title, format(cat_order, 0)
FROM phpbb_categories ;

# Second, copy forums.
#
# The security set ups are not identical in each system. The following translation rules are used:
# If phpBB forum read access = ALL, set XoopsBB forum type to Public, else set forum type to Private.
# If phpBB forum post access = ALL, set XoopsBB forum access to Anonymous,
# else if forum post access = REG or PRIVATE, set forum access to Registered Users Only,
# else set forum access to Moderators Only.

INSERT INTO xoops_bb_forums (forum_id, forum_name, forum_desc, forum_topics, forum_last_post_id, cat_id,
forum_type, forum_access, forum_posts)
SELECT forum_id, forum_name, forum_desc, forum_topics, forum_last_post_id, cat_id,
IF(auth_read=0,0,1), IF(auth_post=0,2,IF(auth_post<3,1,3)), forum_posts
FROM phpbb_forums ;

# Third, copy users. This is a complex step because some users may exist in both systems
# and some may only exist in one system.

DROP TABLE temp_uid_map;
CREATE TABLE temp_uid_map (phpbb_uid mediumint(8), xoops_bb_uid int(5) NULL, link int(1) default 1) ;

# New users from phpBB

# Note: following table is only required because MySQL does not support
# nested select functionality in UPDATE command (planned for MySQL 4)

CREATE TABLE temp_max_xoops_uid (max_uid int(5) NULL, link int(1) default 1);

INSERT INTO temp_max_xoops_uid (max_uid)
SELECT uid
FROM xoops_users
ORDER BY uid DESC
LIMIT 1;

INSERT INTO temp_uid_map (phpbb_uid, xoops_bb_uid)
SELECT p.user_id, m.max_uid+p.user_id
FROM phpbb_users p
LEFT JOIN xoops_users x ON p.username = x.uname
LEFT JOIN temp_max_xoops_uid m ON m.link = 1
WHERE x.uname IS NULL
AND p.user_id > 0;

DROP TABLE temp_max_xoops_uid;

INSERT INTO xoops_users (uid, name, uname, email, url,
user_regdate, user_from, user_sig, user_viewemail,
user_aim, user_yim, user_msnm, user_icq,
pass, posts, attachsig,
theme, umode, bio, user_avatar)
SELECT t.xoops_bb_uid, ' ', p.username, left(p.user_email,60), p.user_website,
p.user_regdate, p.user_from, p.user_sig, IF(p.user_viewemail is null, 0, p.user_viewemail),
p.user_aim, p.user_yim, p.user_msnm, p.user_icq,
p.user_password, p.user_posts, p.user_attachsig,
' ', 'flat', ' ', 'blank.gif'
FROM phpbb_users p, temp_uid_map t
WHERE p.user_id = t.phpbb_uid ;


# Ajout Hervé
INSERT INTO xoops_groups_users_link (groupid, uid) SELECT 2, u.uid FROM xoops_users u

# Matching user IDs - don't copy over any user information

INSERT INTO temp_uid_map (phpbb_uid, xoops_bb_uid)
SELECT p.user_id, x.uid
FROM phpbb_users p, xoops_users x
WHERE p.username = x.uname ;

# Anonymous user IDs

INSERT INTO temp_uid_map (phpbb_uid, xoops_bb_uid) values (-1,0) ;

# Fourth, copy security access to forums.

INSERT INTO xoops_bb_forum_access (forum_id, user_id, can_post)
SELECT a.forum_id, t.xoops_bb_uid, a.auth_post
FROM phpbb_auth_access a, phpbb_user_group u,
phpbb_forums f, temp_uid_map t
WHERE a.group_id = u.group_id
AND a.forum_id = f.forum_id
AND u.user_id = t.phpbb_uid
AND a.auth_read = 1
AND f.auth_read > 0
AND u.user_id > 0;

# Fifth, copy topics.

INSERT INTO xoops_bb_topics (topic_id, topic_title, topic_poster,
topic_time, topic_views, topic_replies, topic_last_post_id, forum_id)
SELECT p.topic_id, p.topic_title, t.xoops_bb_uid,
p.topic_time, p.topic_views, p.topic_replies, p.topic_last_post_id, p.forum_id
FROM phpbb_topics p, temp_uid_map t
WHERE p.topic_poster = t.phpbb_uid;

# Sixth, copy individual posts.

INSERT INTO xoops_bb_posts (post_id, topic_id, forum_id, post_time, uid,
poster_ip, subject, nohtml, nosmiley, attachsig)
SELECT p.post_id, p.topic_id, p.forum_id, p.post_time, t.xoops_bb_uid,
p.poster_ip, x.post_subject, NOT p.enable_html, NOT p.enable_smilies, p.enable_sig
FROM phpbb_posts p, phpbb_posts_text x, temp_uid_map t
WHERE p.poster_id = t.phpbb_uid
AND p.post_id = x.post_id;

# Seventh, copy post text.

INSERT INTO xoops_bb_posts_text (post_id, post_text)
SELECT post_id, replace(post_text, concat(':',bbcode_uid), '') FROM phpbb_posts_text;

# Clean up.

DROP TABLE temp_uid_map;