/*
  Description:
    Convert from Typo 4.1.1 to Wordpress 2.7.1.  May work for other similar versions.

  Author:
    Raymond Law http://rayvinly.com

  Credits:
    http://blog.ifbydesign.com/2006/06/26/automatic-migration-from-typo-to-wordpress/
    http://www.thirdbit.net/articles/2007/06/25/typo-to-wordpress-migration/
*/

/* clear up the existing posts and comments */
DELETE FROM wp_posts;
DELETE FROM wp_comments;
DELETE FROM wp_terms WHERE term_id > 2;
DELETE FROM wp_term_taxonomy WHERE term_taxonomy_id > 2;
DELETE FROM wp_term_relationships WHERE object_id > 7;
DELETE FROM wp_links WHERE link_id > 7;

/* insert categories from typo */
INSERT INTO wp_terms (name, slug, term_group)
SELECT categories.name, categories.permalink, 0 FROM categories;

INSERT INTO wp_term_taxonomy (term_id,taxonomy,parent,count)
SELECT wp_terms.term_id, 'category', 0, 0 FROM wp_terms
INNER JOIN categories ON categories.name = wp_terms.name
WHERE categories.name NOT IN (SELECT wp_terms.name FROM wp_terms INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy='category');

/* insert posts 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)
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,
	'publish',
	'closed',
	'closed',
	'page'
FROM contents
WHERE contents.type = 'Page';

/* associate categories with posts */	
INSERT INTO wp_term_relationships (object_id,term_taxonomy_id,term_order)
SELECT wp_posts.ID, wp_term_taxonomy.term_taxonomy_id, 0 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_terms ON wp_terms.slug = categories.permalink
INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy='category';

/* 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_term_taxonomy
SET count = (SELECT COUNT(*) FROM wp_term_relationships WHERE wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id AND wp_term_taxonomy.taxonomy = 'category')
WHERE taxonomy='category';

/* 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_terms (name,slug,term_group)
SELECT tags.name,tags.display_name,0 FROM tags
WHERE tags.name NOT IN (SELECT wp_terms.slug FROM wp_terms);

INSERT INTO wp_term_taxonomy (term_id,taxonomy,parent,count)
SELECT wp_terms.term_id, 'post_tag', 0, 0 FROM wp_terms
INNER JOIN tags ON tags.name = wp_terms.name
WHERE tags.name NOT IN (SELECT wp_terms.slug FROM wp_terms INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_term_taxonomy.taxonomy='post_tag');

/* associate tags with posts */
INSERT INTO wp_term_relationships (object_id,term_taxonomy_id,term_order)
SELECT wp_posts.ID, wp_term_taxonomy.term_taxonomy_id, 0 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_terms ON wp_terms.name = tags.name
INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE wp_term_taxonomy.taxonomy='post_tag';

/* update each tag with post count */
UPDATE wp_term_taxonomy
SET count = (SELECT COUNT(*) FROM wp_term_relationships WHERE wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id AND wp_term_taxonomy.taxonomy = 'post_tag')
WHERE taxonomy='post_tag';

/* 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;
DROP TABLE feedback;