xF1 Add-on bbPress Importer 1.0.0

START TRANSACTION;

SET @user_id_jump = 2;

INSERT INTO
	xf_user (
		user_id,
		username,
	email,
		user_group_id,
	register_date,
	last_activity
	)
SELECT
	ID + @user_id_jump /* New ID */,
	LOWER(REPLACE(user_login, '.', '_')) /* Sanitised username */,
	user_email /* Email address */,
	2 /* Group ("Registered") */,
	UNIX_TIMESTAMP(user_registered) /* Date the user signed up */,
	UNIX_TIMESTAMP(user_registered) /* Date of the user's last activity */
FROM
	wp_qjpq_users
WHERE
ID > 1;

INSERT INTO
	xf_user_privacy (
		user_id,
	allow_post_profile,
		allow_send_personal_conversation
	)
SELECT
	ID + @user_id_jump /* New ID */,
	'members',
	'members'
FROM
	wp_qjpq_users
WHERE
ID > 1;

INSERT INTO
	xf_user_authenticate (
		user_id,
		scheme_class
)
SELECT
	ID + @user_id_jump /* New ID */,
	'XenForo_Authentication_Core12'
FROM
	wp_qjpq_users
WHERE
ID > 1;

INSERT INTO
	xf_user_option (
		user_id
)
SELECT
	ID + @user_id_jump /* New ID */
FROM
	wp_qjpq_users
WHERE
ID > 1;

INSERT INTO xf_node (
	node_id,
	title,
	description,
	node_type_id,
	parent_node_id,
	depth
) SELECT
	f.id, /* forumid */
	f.post_title /* Title */,
	f.post_content /* Description */,
	'Forum',
	1,
	1
FROM
	wp_qjpq_posts AS f
WHERE
f.post_type = 'forum';

INSERT INTO xf_forum (
	node_id,
	message_count,
	discussion_count,
	last_post_id,
	last_post_user_id,
	last_post_username,
	last_thread_title
) SELECT
	f.id,
	(
		SELECT
			COUNT(*)
		FROM
			wp_qjpq_posts AS r
		INNER JOIN
			wp_qjpq_posts AS t ON r.post_parent = t.id
		WHERE
			t.post_parent = f.id
		AND
			r.post_type = 'reply'
		AND
			t.post_type = 'topic'
) /* Number of posts */,
	(
		SELECT
			COUNT(*)
		FROM
			wp_qjpq_posts AS t
		WHERE
			t.post_parent = f.id
		AND
			t.post_type = 'topic'
) /* Number of topics */,
	(
		SELECT
			id
		FROM
			wp_qjpq_posts AS p
		WHERE
			p.post_parent = f.id
		AND
			p.post_type = 'topic'
		ORDER BY
			p.post_date DESC LIMIT 1
	) /* Last post ID */,
	(
		SELECT
			post_author + @user_id_jump
		FROM
			wp_qjpq_posts AS p
		WHERE
			p.post_parent = f.id
		AND
			p.post_type = 'topic'
		ORDER BY
			p.post_date DESC LIMIT 1
) /* Last poster ID */,
	(
		SELECT
			u.user_login
		FROM
			wp_qjpq_posts AS p
		INNER JOIN
			wp_qjpq_users AS u ON p.post_author = u.id
		WHERE
			p.post_parent = f.id
		AND
			p.post_type = 'topic'
		ORDER BY
			p.post_date DESC LIMIT 1
) /* Last poster name */,
	(
		SELECT
			post_title
		FROM
			wp_qjpq_posts AS p
		WHERE
			p.post_parent = f.id
		AND
			p.post_type = 'topic'
		ORDER BY
			p.post_date DESC LIMIT 1
) /* Last post subject */
FROM
	wp_qjpq_posts AS f
WHERE
f.post_type = 'forum';

INSERT INTO
	xf_thread (
		thread_id,
	node_id,
	title,
	user_id,
	username,
	post_date
)
SELECT
	t.id /* Topic ID */,
	t.post_parent /* Forum ID */,
	t.post_title /* Topic title */,
	CASE t.post_author
		WHEN 1 THEN
			t.post_author + 1
		ELSE
			t.post_author + @user_id_jump
	END,
	IFNULL(
		(
			SELECT
				lu.user_login
			FROM
				wp_qjpq_posts AS l
			INNER JOIN
				wp_qjpq_users AS lu ON l.post_author = lu.id
			WHERE
				l.post_parent = t.id
			AND
				l.post_type = 'reply'
			ORDER BY
				l.post_date
			LIMIT 1
		),
		u.user_login
	) /* Username of the first poster (or topic creator if none exists) */,
	UNIX_TIMESTAMP(t.post_date) /* Date it was posted */
FROM
	wp_qjpq_posts AS t
INNER JOIN
	wp_qjpq_users AS u ON t.post_author = u.id
WHERE
t.post_type = 'topic';

INSERT INTO
	xf_post (
		thread_id,
		user_id,
		username,
		post_date,
	message
	)
SELECT
	p.id /* Topic ID */,
	CASE
		p.post_author
	WHEN 1 THEN
		p.post_author + 1
	ELSE
	p.post_author + @user_id_jump
	END /* The ID of the poster, upping the number for non-admins */,
	u.user_login /* Username of the poster */,
	UNIX_TIMESTAMP(p.post_date) /* Date of the post */,
	p.post_content /* Post text */
FROM
	wp_qjpq_posts AS p
INNER JOIN
	wp_qjpq_users AS u ON p.post_author = u.id
WHERE
	p.post_type = 'topic';

INSERT INTO
	xf_post (
		post_id,
		thread_id,
		user_id,
		username,
		post_date,
	message
	)
SELECT
	p.id /* Post ID */,
	t.id /* Topic ID */,
	CASE
		p.post_author
	WHEN 1 THEN
		p.post_author + 1
	ELSE
	p.post_author + @user_id_jump
	END /* The ID of the poster, upping the number for non-admins */,
	u.user_login /* Username of the poster */,
	UNIX_TIMESTAMP(p.post_date) /* Date of the post */,
	p.post_content /* Post text */
FROM
	wp_qjpq_posts AS p
INNER JOIN
	wp_qjpq_users AS u ON p.post_author = u.id
INNER JOIN
	wp_qjpq_posts AS t ON p.post_parent = t.id
WHERE
	p.post_type = 'reply'
AND
	t.post_type = 'topic';

UPDATE xf_user AS user
SET message_count = (
	SELECT COUNT(*)
	FROM xf_post AS post
	LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
	WHERE post.user_id = user.user_id
	AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
	GROUP BY post.user_id
);
COMMIT;