/* ATTENTION: You don't need to run or use this file!  The convert.php script does everything for you! */

/******************************************************************************/
---~ name: "phpBB3"
/******************************************************************************/
---~ version: "SMF 1.1"
---~ settings: "/config.php"
---~ defines: IN_PHPBB
---~ from_prefix: "`$dbname`.$table_prefix"
---~ table_test: "{$from_prefix}users"

/******************************************************************************/
--- Converting ranks...
/******************************************************************************/

DELETE FROM {$to_prefix}membergroups
WHERE groupName LIKE 'phpBB %';

---* {$to_prefix}membergroups
---{
if (!isset($_SESSION['convert_num_stars']))
{
	$_SESSION['convert_num_stars'] = 1;

	// Got some ranks to move, so go do it... first remove the post based membergroups from the table.
	// !!! We must keep group id 4 as a post group.  MUST!!
	convert_query("
		DELETE FROM {$to_prefix}membergroups
		WHERE minPosts != -1
			AND ID_GROUP > 4");
}

if ($row['minPosts'] > -1)
{
	$row['stars'] = sprintf("%d#star.gif", $_SESSION['convert_num_stars']);
	if ($_SESSION['convert_num_stars'] < 5)
		$_SESSION['convert_num_stars']++;
}
---}
SELECT
	SUBSTRING(CONCAT('phpBB ', rank_title), 1, 255) AS groupName,
	rank_image AS stars, IF(rank_special = 0, rank_min, -1) AS minPosts,
	'' AS onlineColor
FROM {$from_prefix}ranks
ORDER BY rank_min;
---*

/******************************************************************************/
--- Converting groups...
/******************************************************************************/

---* {$to_prefix}membergroups
SELECT
	SUBSTRING(CONCAT('phpBB ', group_name), 1, 255) AS groupName,
	-1 AS minPosts, '' AS stars, group_colour AS onlineColor
FROM {$from_prefix}groups
WHERE group_id NOT IN (1, 6);
---*

/******************************************************************************/
--- Converting members...
/******************************************************************************/

TRUNCATE {$to_prefix}members;
TRUNCATE {$to_prefix}attachments;

---* {$to_prefix}members
---{
// Got the board timezone?
if (!isset($board_timezone))
{
	$request2 = convert_query("
		SELECT config_value
		FROM {$from_prefix}config
		WHERE config_name = 'board_timezone'
		LIMIT 1");
	list ($board_timezone) = mysql_fetch_row($request2);
	mysql_free_result($request2);

	// Find out where uploaded avatars go
	$request2 = convert_query("
		SELECT value
		FROM {$to_prefix}settings
		WHERE variable = 'custom_avatar_enabled'
		LIMIT 1");

	if (mysql_num_rows($request2))
		list ($custom_avatar_enabled) = mysql_fetch_row($request2);
	else
		$custom_avatar_enabled = false;
	mysql_free_result($request2);

	if ($custom_avatar_enabled)
	{
		// Custom avatar dir.
		$request2 = convert_query("
			SELECT value
			FROM {$to_prefix}settings
			WHERE variable = 'custom_avatar_dir'
			LIMIT 1");
		list ($avatar_dir) = mysql_fetch_row($request2);
		$attachmentType = '1';
	}
	else
	{
		// Attachments dir.
		$request2 = convert_query("
			SELECT value
			FROM {$to_prefix}settings
			WHERE variable = 'attachmentUploadDir'
			LIMIT 1");
		list ($avatar_dir) = mysql_fetch_row($request2);
		$attachmentType = '0';
	}
	mysql_free_result($request2);

	$request2 = convert_query("
		SELECT config_value
		FROM {$from_prefix}config
		WHERE config_name = 'avatar_path'
		LIMIT 1");
	$phpbb_avatar_upload_path = $_POST['path_from'] . '/' . mysql_result($request2, 0, 'config_value');
	mysql_free_result($request2);
}

// timeOffset = phpBB user TZ - phpBB board TZ.
$row['timeOffset'] = $row['timeOffset'] - $board_timezone;

if ($row['user_avatar_type'] == 0)
	$row['avatar'] = '';
// If the avatar type is uploaded (type = 1) copy avatar with the correct name.
elseif ($row['user_avatar_type'] == 1 && strlen($row['avatar']) > 0)
{
	$smf_avatar_filename = 'avatar_' . $row['ID_MEMBER'] . strrchr($row['avatar'], '.');
	@copy($phpbb_avatar_upload_path . '/' . $row['avatar'], $avatar_dir . '/' . $smf_avatar_filename);

	convert_query("
		INSERT INTO {$to_prefix}attachments
			(ID_MSG, ID_MEMBER, filename, attachmentType)
		VALUES (0, $row[ID_MEMBER], SUBSTRING('" . addslashes($smf_avatar_filename) . "', 1, 255), " . $attachmentType . ")");
	$row['avatar'] = '';
}
elseif ($row['user_avatar_type'] == 3)
	$row['avatar'] = substr('gallery/' . $row['avatar'], 0, 255);
unset($row['user_avatar_type']);

if ($row['signature_uid'] != '')
	$row['signature'] = preg_replace('~(:u:|:1:|:)' . preg_quote($row['signature_uid'], '~') . '~i', '', $row['signature']);

$row['signature'] = preg_replace(
	array(
		'~\[quote=&quot;(.+?)&quot;(:.+?)?\]~is',
		'~\[/quote(:.+?)?\]~is',
		'~\[b(:.+?)?\]~is',
		'~\[/b(:.+?)?\]~is',
		'~\[url:(.+?)\]~is',
		'~\[/url:(.+?)?\]~is',
		'~\[img:(.+?)?\]~is',
		'~\[/img:(.+?)?\]~is',
		'~\[size=([789]|[012]\d)\]~is',
	),
	array(
		'[quote author="$1"]',
		'[/quote]',
		'[b]',
		'[/b]',
		'[url]',
		'[/url]',
		'[img]',
		'[/img]',
		'[size=$1px]',
	), $row['signature']);

$row['signature'] = substr($row['signature'], 0, 65534);
unset($row['signature_uid']);
---}
SELECT
	u.user_id AS ID_MEMBER, SUBSTRING(u.username, 1, 80) AS memberName,
	SUBSTRING(u.username, 1, 255) AS realName,
	SUBSTRING(u.user_password, 1, 64) AS passwd, u.user_lastvisit AS lastLogin,
	u.user_regdate AS dateRegistered,
	SUBSTRING(u.user_from, 1, 255) AS location,
	u.user_posts AS posts, IF(u.user_rank = 1, 1, mg.ID_GROUP) AS ID_GROUP,
	u.user_new_privmsg AS instantMessages,
	SUBSTRING(u.user_email, 1, 255) AS emailAddress,
	u.user_unread_privmsg AS unreadMessages,
	SUBSTRING(u.user_msnm, 1, 255) AS MSN,
	SUBSTRING(u.user_aim, 1, 16) AS AIM,
	SUBSTRING(u.user_icq, 1, 255) AS ICQ,
	SUBSTRING(u.user_yim, 1, 32) AS YIM,
	SUBSTRING(u.user_website, 1, 255) AS websiteTitle,
	SUBSTRING(u.user_website, 1, 255) AS websiteUrl,
	u.user_allow_viewonline AS showOnline, u.user_timezone AS timeOffset,
	IF(u.user_allow_viewemail = 1, 0, 1) AS hideEmail, u.user_avatar AS avatar,
	REPLACE(u.user_sig, '\n', '<br />') AS signature,
	u.user_sig_bbcode_uid AS signature_uid, u.user_avatar_type,
	u.user_notify_pm AS pm_email_notify,
	CASE u.user_inactive_reason WHEN 0 THEN 1 ELSE 0 END AS is_activated,
	'' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list, '' AS messageLabels,
	'' AS personalText, '' AS timeFormat, '' AS usertitle, u.user_ip AS memberIP,
	'' AS secretQuestion, '' AS secretAnswer, '' AS validation_code,
	'' AS additionalGroups, '' AS smileySet, '' AS passwordSalt,
	u.user_ip AS memberIP2
FROM {$from_prefix}users AS u
	LEFT JOIN {$from_prefix}ranks AS r ON (r.rank_id = u.user_rank AND r.rank_special = 1)
	LEFT JOIN {$to_prefix}membergroups AS mg ON (BINARY mg.groupName = CONCAT('phpBB ', r.rank_title))
WHERE u.group_id NOT IN (1, 6)
GROUP BY u.user_id;
---*

/******************************************************************************/
--- Converting additional member groups...
/******************************************************************************/

---# Checking memberships...
---{
while (true)
{
	pastTime($substep);

	$result = convert_query("
		SELECT mg.ID_GROUP, mem.ID_MEMBER
		FROM ({$from_prefix}groups AS g, {$from_prefix}user_group AS ug, {$to_prefix}members AS mem, {$to_prefix}membergroups AS mg)
		WHERE ug.group_id = g.group_id
			AND mem.ID_MEMBER = ug.user_id
			AND BINARY mg.groupName = CONCAT('phpBB ', g.group_name)
			AND g.group_name NOT IN ('GUESTS', 'REGISTERED_COPPA', 'BOTS')
		ORDER BY ID_MEMBER
		LIMIT $_REQUEST[start], 250");
	$additionalGroups = '';
	$last_member = 0;
	while ($row = mysql_fetch_assoc($result))
	{
		if (empty($last_member))
			$last_member = $row['ID_MEMBER'];

		if ($last_member != $row['ID_MEMBER'])
		{
			$additionalGroups = addslashes($additionalGroups);

			convert_query("
				UPDATE {$to_prefix}members
				SET additionalGroups = '$additionalGroups'
				WHERE ID_MEMBER = $last_member
				LIMIT 1");
			$last_member = $row['ID_MEMBER'];
			$additionalGroups = $row['ID_GROUP'];
		}
		else
		{
			if ($additionalGroups == '')
				$additionalGroups = $row['ID_GROUP'];
			else
				$additionalGroups = $additionalGroups . ',' . $row['ID_GROUP'];
		}
	}

	$_REQUEST['start'] += 250;
	if (mysql_num_rows($result) < 250)
		break;

	mysql_free_result($result);
}
$_REQUEST['start'] = 0;

if ($last_member != 0)
{
	$additionalGroups = addslashes($additionalGroups);

	convert_query("
		UPDATE {$to_prefix}members
		SET additionalGroups = '$additionalGroups'
		WHERE ID_MEMBER = $last_member
		LIMIT 1");
}
---}
---#

/******************************************************************************/
--- Preparing for categories conversion...
/******************************************************************************/

TRUNCATE {$to_prefix}categories;

---{
// Add a tempID column.
$knownKeys = array();
$knownColumns = array(
	'tempID' => "ADD COLUMN tempID mediumint(8) NOT NULL default 0",
);
alterTable('categories', $knownKeys, $knownColumns);
---}

/******************************************************************************/
--- Converting categories...
/******************************************************************************/

TRUNCATE {$to_prefix}categories;

---# Converting categories...
---* {$to_prefix}categories
SELECT forum_id AS tempID, SUBSTRING(forum_name, 1, 255) AS name, left_id AS catOrder
FROM {$from_prefix}forums
WHERE forum_type = 0
ORDER BY left_id;
---*
---#

---# Inserting board for uncategorized boards.
---{
$request = convert_query("
	SELECT COUNT(*)
	FROM {$to_prefix}categories
	WHERE name = 'Uncategorized Boards'");
list ($exists) = mysql_fetch_row($request);
mysql_free_result($request);

if (empty($exists))
	convert_query("
		INSERT INTO {$to_prefix}categories
			(tempID, name, catOrder)
		VALUES (0, 'Uncategorized Boards', 1)");
---}
---#

/******************************************************************************/
--- Converting boards...
/******************************************************************************/

TRUNCATE {$to_prefix}boards;

DELETE FROM {$to_prefix}board_permissions
WHERE ID_BOARD != 0;

---* {$to_prefix}boards
---{
$row['name'] = str_replace('\n', '<br />', $row['name']);
---}
SELECT
	f.forum_id AS ID_BOARD, CASE WHEN f.parent_id = c.tempID THEN 0 ELSE f.parent_id END AS ID_PARENT, f.left_id AS boardOrder, f.forum_posts AS numPosts,
	f.forum_last_post_id AS ID_LAST_MSG, SUBSTRING(f.forum_name, 1, 255) AS name, c.ID_CAT AS ID_CAT, '-1,0' AS memberGroups,
	SUBSTRING(BINARY f.forum_desc, 1, 65534) AS description, f.forum_topics AS numTopics, f.forum_last_post_id AS ID_LAST_MSG
FROM {$from_prefix}forums AS f
	LEFT JOIN {$to_prefix}categories AS c ON (c.tempID = f.parent_id)
WHERE forum_type = 1
GROUP BY ID_BOARD;
---*

/******************************************************************************/
--- Fixing categories...
/******************************************************************************/

---{
$knownColumns = array(
	'tempID' => 'DROP tempID',
);
alterTable('categories', '', $knownColumns, '', false, true);

// Lets fix the order.
$request = convert_query("
	SELECT ID_CAT, catOrder
	FROM {$to_prefix}categories
	ORDER BY catOrder");
$order = 1;
while ($row = mysql_fetch_assoc($request))
{
	convert_query("
		UPDATE {$to_prefix}categories
		SET catOrder = $order
		WHERE ID_CAT = $row[ID_CAT]");
	$order++;
}

// Lets order them.
convert_query("
	ALTER TABLE {$to_prefix}categories
	ORDER BY catOrder");
---}

/******************************************************************************/
--- Converting topics...
/******************************************************************************/

TRUNCATE {$to_prefix}topics;
TRUNCATE {$to_prefix}log_topics;
TRUNCATE {$to_prefix}log_boards;
TRUNCATE {$to_prefix}log_mark_read;

---* {$to_prefix}topics
---{
$ignore = true;
$row['ID_POLL'] = is_null($row['ID_POLL']) ? '0' : $row['ID_POLL'];
---}
SELECT
	t.topic_id AS ID_TOPIC, t.forum_id AS ID_BOARD, t.topic_first_post_id AS ID_FIRST_MSG,
	CASE t.topic_type WHEN 1 THEN 1 ELSE 0 END AS isSticky,
	t.topic_last_post_id AS ID_LAST_MSG, t.topic_poster AS ID_MEMBER_STARTED,
	t.topic_last_poster_id AS ID_MEMBER_UPDATED, po.topic_id AS ID_POLL,
	t.topic_replies AS numReplies, t.topic_views AS numViews,
	CASE t.topic_status WHEN 1 THEN 1 ELSE 0 END AS locked
FROM {$from_prefix}topics AS t
	LEFT JOIN {$from_prefix}poll_options AS po ON (po.topic_id = t.topic_id);
---*

/******************************************************************************/
--- Converting posts...
/******************************************************************************/

TRUNCATE {$to_prefix}messages;

---* {$to_prefix}messages 200
---{
$row['body'] = preg_replace(
	array(
		'~\[quote=&quot;(.+?)&quot;(:.+?)?\]~is',
		'~\[/quote(:.+?)?\]~is',
		'~\[b(:.+?)?\]~is',
		'~\[/b(:.+?)?\]~is',
		'~\[url:(.+?)\]~is',
		'~\[/url:(.+?)?\]~is',
		'~\[img:(.+?)?\]~is',
		'~\[/img:(.+?)?\]~is',
		'~\[size=([789]|[012]\d)\]~is',
	),
	array(
		'[quote author="$1"]',
		'[/quote]',
		'[b]',
		'[/b]',
		'[url]',
		'[/url]',
		'[img]',
		'[/img]',
		'[size=$1px]',
	), $row['body']);
---}
SELECT
	p.post_id AS ID_MSG, p.topic_id AS ID_TOPIC, p.forum_id AS ID_BOARD,
	p.post_time AS posterTime, p.poster_id AS ID_MEMBER, BINARY p.post_subject AS subject,
	m.username AS posterName, m.user_email AS posterEmail, p.poster_ip AS posterIP,
	p.enable_smilies AS smileysEnabled, p.post_edit_time AS modifiedTime,
	CASE p.post_edit_user WHEN 0 THEN '' ELSE m2.username END AS modifiedName,
	BINARY p.post_text AS body
FROM {$from_prefix}posts AS p
	LEFT JOIN {$from_prefix}users AS m ON (m.user_id = p.poster_id)
	LEFT JOIN {$from_prefix}users AS m2 ON (m2.user_id = p.post_edit_user);
---*

/******************************************************************************/
--- Converting polls...
/******************************************************************************/

TRUNCATE {$to_prefix}polls;
TRUNCATE {$to_prefix}poll_choices;
TRUNCATE {$to_prefix}log_polls;

---* {$to_prefix}polls
SELECT
	t.topic_id AS ID_POLL, t.poll_title AS question, t.poll_max_options AS maxVotes, (t.poll_start + t.poll_length) AS expireTime,
	t.poll_vote_change AS changeVote, t.topic_poster AS ID_MEMBER, m.username AS posterName
FROM {$from_prefix}topics AS t
	LEFT JOIN {$from_prefix}users AS m ON (m.user_id = t.topic_poster)
WHERE t.poll_title != '';
---*

/******************************************************************************/
--- Converting poll options...
/******************************************************************************/

---* {$to_prefix}poll_choices
SELECT
	topic_id AS ID_POLL, poll_option_id AS ID_CHOICE,
	SUBSTRING(BINARY poll_option_text, 1, 255) AS label, poll_option_total AS votes
FROM {$from_prefix}poll_options;
---*

/******************************************************************************/
--- Converting poll votes...
/******************************************************************************/

---* {$to_prefix}log_polls
SELECT topic_id AS ID_POLL, vote_user_id AS ID_MEMBER, poll_option_id AS ID_CHOICE
FROM {$from_prefix}poll_votes
WHERE vote_user_id > 0;
---*

/******************************************************************************/
--- Converting personal messages (step 1)...
/******************************************************************************/

TRUNCATE {$to_prefix}personal_messages;

---* {$to_prefix}personal_messages
SELECT
	pm.msg_id AS ID_PM, pm.author_id AS ID_MEMBER_FROM, pm.message_time AS msgtime,
	SUBSTRING(uf.username, 1, 255) AS fromName, SUBSTRING(pm.message_subject, 1, 255) AS subject,
	SUBSTRING(REPLACE(IF(pm.bbcode_uid = '', pm.message_text, REPLACE(REPLACE(BINARY pm.message_text, CONCAT(':1:', pm.bbcode_uid), ''), CONCAT(':', pm.bbcode_uid), '')), '\n', '<br />'), 1, 65534) AS body
FROM {$from_prefix}privmsgs AS pm
	LEFT JOIN {$from_prefix}users AS uf ON (uf.user_id = pm.author_id);
---*

/******************************************************************************/
--- Converting personal messages (step 2)...
/******************************************************************************/

TRUNCATE {$to_prefix}pm_recipients;

---* {$to_prefix}pm_recipients
SELECT
	pm.msg_id AS ID_PM, pm.user_id AS ID_MEMBER, '-1' AS labels,
	CASE pm.pm_unread WHEN 1 THEN 0 ELSE 1 END AS is_read, pm.pm_deleted AS deleted
FROM {$from_prefix}privmsgs_to AS pm;
---*

/******************************************************************************/
--- Converting attachments...
/******************************************************************************/

---* {$to_prefix}attachments
---{
$no_add = true;
$keys = array('ID_ATTACH', 'size', 'filename', 'ID_MSG', 'downloads', 'width', 'height');

if (!isset($oldAttachmentDir))
{
	$result = convert_query("
		SELECT config_value
		FROM {$from_prefix}config
		WHERE config_name = 'upload_path'
		LIMIT 1");
	list ($oldAttachmentDir) = mysql_fetch_row($result);
	mysql_free_result($result);

	if (empty($oldAttachmentDir) || !file_exists($_POST['path_from'] . '/' . $oldAttachmentDir))
		$oldAttachmentDir = $_POST['path_from'] . '/file';
	else
		$oldAttachmentDir = $_POST['path_from'] . '/' . $oldAttachmentDir;
}

// Get $ID_ATTACH.
if (empty($ID_ATTACH))
{
	$result = convert_query("
		SELECT MAX(ID_ATTACH) + 1
		FROM {$to_prefix}attachments");
	list ($ID_ATTACH) = mysql_fetch_row($result);
	mysql_free_result($result);

	$ID_ATTACH = empty($ID_ATTACH) ? 1 : $ID_ATTACH;
}

$newfilename = getAttachmentFilename($row['filename'], $ID_ATTACH);

// Set the default empty values.
$width = '0';
$height = '0';

// Is an an image?
$attachmentExtension = strtolower(substr(strrchr($row['filename'], '.'), 1));
if (in_array($attachmentExtension, array('jpg', 'jpeg', 'gif', 'png', 'bmp')))
	list ($width, $height) = getimagesize($oldAttachmentDir . '/' . $row['physical_filename']);

$newfilename = getAttachmentFilename($row['filename'], $ID_ATTACH);
if (strlen($newfilename) <= 255 && copy($oldAttachmentDir . '/' . $row['physical_filename'], $attachmentUploadDir . '/' . $newfilename))
{
	$rows[] = "$ID_ATTACH, " . filesize($attachmentUploadDir . '/' . $newfilename) . ", '" . addslashes($row['filename']) . "', $row[ID_MSG], $row[downloads], '$width', '$height'";
	$ID_ATTACH++;
}
---}
SELECT
	post_msg_id AS ID_MSG, download_count AS downloads,
	real_filename AS filename, physical_filename, filesize AS size
FROM {$from_prefix}attachments;
---*