use composr;

## step 0a
## import the date from yayoo into a table 'messages'
## the format, and some test data is :
##CREATE TABLE `messages` (`id` int(10) unsigned NOT NULL auto_increment,`number` INTEGER NOT NULL,`date` DATETIME NOT NULL,`author` TEXT NOT NULL,`subject` TEXT NOT NULL,`content` LONGTEXT,`topic_id` INTEGER,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
##INSERT INTO `messages` VALUES(NULL,13640,'2016-12-06 22:20:16','person','Topic1','This is a topic as number = topic_id',13640);
##INSERT INTO `messages` VALUES(NULL,13641,'2016-12-06 22:50:16','person','re:Topic1','This is a post as a reply to topic 13640',13640);
##INSERT INTO `messages` VALUES(NULL,13642,'2016-12-08 22:20:16','person','Topic2','This is a new topic as number = topic_id',13642);

## step 0b, change the forum name here to match yours
select @y_id := id 
from cms_f_forums
where f_name like '%Yahoo%'; 


## step1 add a new temporary column to modify html content
ALTER TABLE messages ADD COLUMN IF NOT EXISTS content2 LONGTEXT ;

## step2 -- add function
## function from http://stackoverflow.com/questions/2627940/remove-html-tags-from-record
## this step is optional for if you do not already have this.
## DROP FUNCTION IF EXISTS strip_tags;

##CREATE FUNCTION `strip_tags`($str text) RETURNS text
##BEGIN
##    DECLARE $start, $end INT DEFAULT 1;
##    LOOP
##        SET $start = LOCATE("<", $str, $start);
##        IF (!$start) THEN RETURN $str; END IF;
##        SET $end = LOCATE(">", $str, $start);
##        IF (!$end) THEN SET $end = $start; END IF;
##        SET $str = INSERT($str, $start, $end - $start + 1, "");
##    END LOOP;
##END;

## step3a -- temporarly convery <br> so they do not get thrown away
UPDATE messages
SET content2 = REPLACE(content, "<br>", "\r\n");

## step3c -- remove all html formatting contained within <>
UPDATE messages
SET content2 = strip_tags(content2);

## step3d -- re-instate <br>
UPDATE messages
SET content2 = REPLACE(content2, "\r\n", "<br>");

## step 4 delete any Conversr topics relating to 'your' forum_id
## this step is optional for testing 
##delete from cms_f_topics where t_forum_id = @y_id;

## step5 create the topics in conversr
## providing the yahoo 'number' not equal to the 'topic_id'
## note the t_pt_from column is temporally used to track the origonal yahoo topic number
INSERT
INTO
  `cms_f_topics`(
	`t_pt_from`,
	`t_forum_id`,
   	`t_cache_first_title`,
	`t_validated`,
	`t_is_open`,
    `t_cache_first_username`)
  SELECT
  	number, 
 	 @y_id,
 	subject,
	'1',
	'0',
    author
from messages
where number = topic_id;

## step 6 delete any Conversr Posts relating to 'your' forum
## this step is optional for testing 
##delete from cms_f_posts where p_cache_forum_id = @y_id;

## step 7 insert the posts and link to the topic ID 
INSERT
INTO
  `cms_f_posts`(
    `p_title`,
    `p_post`,
    `p_ip_address`,
    `p_time`,
    `p_poster`,
    `p_poster_name_if_guest`,
    `p_validated`,
    `p_topic_id`,
    `p_cache_forum_id`
  )
  SELECT
 	subject,
 	CONCAT('[html]', content2, '[/html]'),
 	'127.0.0.1',
 	unix_timestamp(date),
	'1',
 	author,
	'1',
	cms_f_topics.id,
 	@y_id
from messages
inner join cms_f_topics on messages.topic_id = cms_f_topics.t_pt_from;



## step 8 update the conversr number or topics, and posts
UPDATE cms_f_forums
SET f_cache_num_topics = (select count(*) from cms_f_topics where t_forum_id = @y_id),
f_cache_num_posts = (select count(*) from cms_f_posts where p_cache_forum_id = @y_id)
WHERE id = @y_id;

## step 9 update the temporarly used t_pt_from column back to null
UPDATE cms_f_topics
SET t_pt_from = NULL
WHERE t_forum_id = @y_id;