/* clear up the existing posts, comments and categories */
DELETE FROM wp_posts;
DELETE FROM wp_comments;
UPDATE wp_categories SET category_count = 0;
DELETE FROM wp_post2cat;

/* insert categories from typo */
INSERT INTO wp_categories (cat_name,category_nicename)
SELECT categories.name, categories.permalink FROM categories;

INSERT INTO wp_posts (post_author,post_title,post_content, post_name, post_date,
	post_date_gmt,post_modified,post_modified_gmt,
	post_status,
	ping_status,
	comment_status)
SELECT 1, title, concat(ifnull(body, ''), ifnull(extended,'')) 
/*concat(CASE body WHEN '' THEN IFNULL(body,'') 
END, CASE extended WHEN '' THEN IFNULL(extended,'')  
END),*/ 
, permalink, 
  created_at, created_at, updated_at, updated_at,
  CASE published WHEN 0 then 'draft' ELSE 'publish' END,
  CASE allow_pings WHEN 0 then 'closed' ELSE 'open' END,
  CASE allow_comments WHEN 0 then 'closed' ELSE 'open' END
FROM contents
WHERE contents.type = 'Article';


/* insert pages from typo */
INSERT INTO wp_posts (post_author,post_title,post_content,post_name,post_date,
	post_date_gmt,post_modified,post_modified_gmt,
	post_status,
	ping_status,
	comment_status,
	post_type)
SELECT 1, title, concat(ifnull(body,''), IFNULL(extended,'')), 
name, created_at, created_at, updated_at, updated_at,
	'static',
	'closed',
	'closed',
	'page'
FROM contents
WHERE contents.type = 'Page';

/* associate categories and posts */	
INSERT INTO wp_post2cat (post_id,category_id)
SELECT wp_posts.ID, wp_categories.cat_ID FROM wp_posts
INNER JOIN contents ON wp_posts.post_name = contents.permalink
INNER JOIN categorizations ON categorizations.article_id = contents.id
INNER JOIN categories ON categorizations.category_id = categories.id
INNER JOIN wp_categories ON wp_categories.category_nicename = categories.permalink;

/* update the permalink structure for posts */
UPDATE wp_options
SET option_value = '/articles/%year%/%monthnum%/%day%/%postname%/'
WHERE option_name = 'permalink_structure';

/* update the permalink structure for categories */
UPDATE wp_options
SET option_value = '/articles/category/'
WHERE option_name = 'category_base';

/* update each category with post count */
UPDATE wp_categories
SET category_count = (SELECT COUNT(*) FROM wp_post2cat WHERE cat_ID = category_id);

/* insert comments from typo */
delete from feedback where state like '%Spam' ;
INSERT INTO wp_comments (comment_author,comment_content,comment_date,comment_date_gmt,comment_post_id,comment_author_email,comment_author_url,comment_author_IP,comment_approved)
SELECT f.author, IFNULL(f.body,''), f.created_at, f.created_at, wp_posts.ID, f.email, f.url, f.ip, 1
FROM feedback f
INNER JOIN contents ON f.article_id = contents.id
INNER JOIN wp_posts ON wp_posts.post_name = contents.permalink;

/* update each post with comment count */
UPDATE wp_posts
SET comment_count = (SELECT COUNT(*) FROM wp_comments WHERE comment_post_id = id);

/* insert tags from typo */
INSERT INTO wp_tags (tag)
SELECT tags.name FROM tags
INNER JOIN articles_tags ON articles_tags.tag_id = tags.id
INNER JOIN contents on contents.id = articles_tags.article_id;

/* associate tags with posts */
INSERT INTO wp_post2tag (post_id,tag_id)
SELECT wp_posts.ID, wp_tags.tag_ID FROM wp_posts
INNER JOIN contents ON wp_posts.post_name = contents.permalink
INNER JOIN articles_tags ON articles_tags.article_id = contents.id
INNER JOIN tags ON articles_tags.tag_id = tags.id
INNER JOIN wp_tags ON wp_tags.tag = tags.name;

/* update permalink for tags */
UPDATE wp_options
SET option_value = '/tags/'
WHERE option_name = 'utw_base_url';

/* turn on pretty URLS for tags */
UPDATE wp_options
SET option_value = 'yes'
WHERE option_name = 'utw_use_pretty_urls';

/* clean up */
DROP TABLE categorizations;
DROP TABLE articles_tags;
DROP TABLE categories;
DROP TABLE contents;
DROP TABLE tags;