<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */

/*
** A not curated php script to import from a mysql database to nodedb
**
** I case it helps.
** Copyleft (d)oekia
** Use at your discretion
**
** Assume:
**   - a category 2 exists (all topics and post be imported here)
**   - no topic/post exists and id will be kept identical as the initial db
**   - you need to change the after() function
**   - some imported post needs to be edited later on
**   - require https://github.com/nickcernis/html2markdown/
**   - change db connection settings in constructor
** 
*/

require_once( __DIR__ . '/html2markdown.php' );

class qa2nbb
{
    static $mydb = null;
    static $mong = null;

    public function __construct()
    {
        self::$mong = new \MongoDB\Driver\Manager('mongodb://nodebb:<nodebbpass>@127.0.0.1:27017/nodebb');
        self::$mydb = new PDO('mysql:dbname=c1_q2a;host=127.0.0.1;charset=UTF8', 'c1_q2a','<q2apass>');
    }

    public static function dj($arg){
        if (is_string($arg)){
            $arg = json_decode($arg,true);
        }
//print_r(array($arg,json_encode($arg)));
        return $arg;
    }

    public function importQAUsers()
    {
        $sqlu = self::$mydb->prepare('select
                u.*, up1.content as fullname, up2.content as location, up3.content as website, up4.content as about
            from qa_users u
            left join qa_userprofile up1 on (u.userid = up1.userid and up1.title = "name")
            left join qa_userprofile up2 on (u.userid = up2.userid and up2.title = "location")
            left join qa_userprofile up3 on (u.userid = up3.userid and up3.title = "website")
            left join qa_userprofile up4 on (u.userid = up4.userid and up4.title = "about")
            where u.userid > 3
        ');
        $sqlu->execute();

        $bulk = new \MongoDB\Driver\BulkWrite(array('ordered'=>false));
        //$bulk->delete(array());

        while( ($row = $sqlu->fetch(PDO::FETCH_ASSOC)) !== false){
            $bulk->insert( $bson = array(
                '_key' => 'user:'.(int)$row['userid'],
                'username' => $row['handle'],
                'userslug' => $row['handle'],
                'email' => $row['email'],
                'joindate' => (float)(strtotime($row['created'])*1000),
                'lastonline' => (float)(strtotime($row['loggedin'])*1000),
                'picture' => '',
                'fullname'=> $row['fullname'], 
                'location'=> '',
                'aboutme'=> $row['about'],
                'uid' => (int)$row['userid'],
                'email:confirmed' => 1,
            ));
            $bulk->insert(self::dj('{"_key":"username:uid",   "value":'.json_encode($row['handle']).',"score":'.(int)$row['userid'].'}'));
            $bulk->insert(self::dj('{"_key":"username:sorted","value":'.json_encode(strtolower($row['handle']).':'.(int)$row['userid']).',"score":0}'));
            $bulk->insert(self::dj('{"_key":"userslug:uid",   "value":'.json_encode($row['handle']).',"score":'.(int)$row['userid'].'}'));
            $bulk->insert(self::dj('{"_key":"users:joindate", "value":'.(int)$row['userid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));
            //$bulk->delete(self::dj('{"_key":"email.uid",      "value":'.json_encode($row['handle']).',"score":'.(int)$row['userid'].'}'));
            $bulk->insert(self::dj('{"_key":"email:uid",      "value":'.json_encode($row['email']).',"score":'.(int)$row['userid'].'}'));
            $bulk->insert(self::dj('{"_key":"email:sorted",   "value":'.json_encode($row['email'].':'.(int)$row['userid']).',"score":0}'));
            $bulk->insert(self::dj('{"_key":"group:registered-users:members", "value":'.(int)$row['userid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));
            //print_r($row);
        }
        //$writeConcern = new MongoDB\Driver\WriteConcern(MongoDB\Driver\WriteConcern::MAJORITY, 1000);

        try {
            $result = self::$mong->executeBulkWrite('nodebb.objects', $bulk /*, $writeConcern*/);
        } catch (MongoDB\Driver\Exception\BulkWriteException $e) {
            $result = $e->getWriteResult();

            // Check if the write concern could not be fulfilled
            if ($writeConcernError = $result->getWriteConcernError()) {
                printf("%s (%d): %s\n",
                    $writeConcernError->getMessage(),
                    $writeConcernError->getCode(),
                    var_export($writeConcernError->getInfo(), true)
                );
            }

            // Check if any write operations did not complete at all
            foreach ($result->getWriteErrors() as $writeError) {
                printf("Operation#%d: %s (%d)\n",
                    $writeError->getIndex(),
                    $writeError->getMessage(),
                    $writeError->getCode()
                );
            }
        } catch (MongoDB\Driver\Exception\Exception $e) {
            printf("Other error: %s\n", $e->getMessage());
            exit;
        }
    }
	
    public static function html2bb($html, $tags = '<a><b><i><u>')
{
    $html = strip_tags($html, $tags);
    $search = array (
                "'<script[^>]*?>.*?</script>'si",  // Strip out javascript
                "<br>",
                "</p>",
                "'([\r\n])[\s]+'",                 // Strip out white space
                "'&(quot|#34);'i",                 // Replace html entities
                "'&(amp|#38);'i",
                "'&(lt|#60);'i",
                "'&(gt|#62);'i",
                "'&(nbsp|#160);'i",
                "'&(iexcl|#161);'i",
                "'&(cent|#162);'i",
                "'&(pound|#163);'i",
                "'&(copy|#169);'i",
                // html -> bb
                "'<b>'i", "'</b>'i",
                "'<i>'i", "'</i>'i",
                "'<a>'i", "'</a>'i", 
                "'<a.*?href=\"(.+?)\".*?>'i", "'</a>'i", //[url=http://www.server.org]server[/url]
                     
    );                   
    $replace = array (
                "",
                "\r\n",
                "\r\n",
                "\\1",
                "\"",
                "&",
                "<",
                ">",
                " ",
                chr(161),
                chr(162),
                chr(163),
                chr(169),
                // html -> bb,
                "[b]", "[/b]",
                "[i]", "[/i]",
                "[url]", "[/url]",
                "[url=$1]", "[/url]"
    );
    $html = preg_replace ($search, $replace, $html);
    /*  echo "\n";
    var_dump($html); /**/
    return $html;
}

    public static function replaceAccentedChars($str)
    {
        /* One source among others:
            http://www.tachyonsoft.com/uc0000.htm
            http://www.tachyonsoft.com/uc0001.htm
            http://www.tachyonsoft.com/uc0004.htm
        */
        $patterns = array(

            /* Lowercase */
            /* a  */ '/[\x{00E0}\x{00E1}\x{00E2}\x{00E3}\x{00E4}\x{00E5}\x{0101}\x{0103}\x{0105}\x{0430}\x{00C0}-\x{00C3}\x{1EA0}-\x{1EB7}]/u',
            /* b  */ '/[\x{0431}]/u',
            /* c  */ '/[\x{00E7}\x{0107}\x{0109}\x{010D}\x{0446}]/u',
            /* d  */ '/[\x{010F}\x{0111}\x{0434}\x{0110}]/u',
            /* e  */ '/[\x{00E8}\x{00E9}\x{00EA}\x{00EB}\x{0113}\x{0115}\x{0117}\x{0119}\x{011B}\x{0435}\x{044D}\x{00C8}-\x{00CA}\x{1EB8}-\x{1EC7}]/u',
            /* f  */ '/[\x{0444}]/u',
            /* g  */ '/[\x{011F}\x{0121}\x{0123}\x{0433}\x{0491}]/u',
            /* h  */ '/[\x{0125}\x{0127}]/u',
            /* i  */ '/[\x{00EC}\x{00ED}\x{00EE}\x{00EF}\x{0129}\x{012B}\x{012D}\x{012F}\x{0131}\x{0438}\x{0456}\x{00CC}\x{00CD}\x{1EC8}-\x{1ECB}\x{0128}]/u',
            /* j  */ '/[\x{0135}\x{0439}]/u',
            /* k  */ '/[\x{0137}\x{0138}\x{043A}]/u',
            /* l  */ '/[\x{013A}\x{013C}\x{013E}\x{0140}\x{0142}\x{043B}]/u',
            /* m  */ '/[\x{043C}]/u',
            /* n  */ '/[\x{00F1}\x{0144}\x{0146}\x{0148}\x{0149}\x{014B}\x{043D}]/u',
            /* o  */ '/[\x{00F2}\x{00F3}\x{00F4}\x{00F5}\x{00F6}\x{00F8}\x{014D}\x{014F}\x{0151}\x{043E}\x{00D2}-\x{00D5}\x{01A0}\x{01A1}\x{1ECC}-\x{1EE3}]/u',
            /* p  */ '/[\x{043F}]/u',
            /* r  */ '/[\x{0155}\x{0157}\x{0159}\x{0440}]/u',
            /* s  */ '/[\x{015B}\x{015D}\x{015F}\x{0161}\x{0441}]/u',
            /* ss */ '/[\x{00DF}]/u',
            /* t  */ '/[\x{0163}\x{0165}\x{0167}\x{0442}]/u',
            /* u  */ '/[\x{00F9}\x{00FA}\x{00FB}\x{00FC}\x{0169}\x{016B}\x{016D}\x{016F}\x{0171}\x{0173}\x{0443}\x{00D9}-\x{00DA}\x{0168}\x{01AF}\x{01B0}\x{1EE4}-\x{1EF1}]/u',
            /* v  */ '/[\x{0432}]/u',
            /* w  */ '/[\x{0175}]/u',
            /* y  */ '/[\x{00FF}\x{0177}\x{00FD}\x{044B}\x{1EF2}-\x{1EF9}\x{00DD}]/u',
            /* z  */ '/[\x{017A}\x{017C}\x{017E}\x{0437}]/u',
            /* ae */ '/[\x{00E6}]/u',
            /* ch */ '/[\x{0447}]/u',
            /* kh */ '/[\x{0445}]/u',
            /* oe */ '/[\x{0153}]/u',
            /* sh */ '/[\x{0448}]/u',
            /* shh*/ '/[\x{0449}]/u',
            /* ya */ '/[\x{044F}]/u',
            /* ye */ '/[\x{0454}]/u',
            /* yi */ '/[\x{0457}]/u',
            /* yo */ '/[\x{0451}]/u',
            /* yu */ '/[\x{044E}]/u',
            /* zh */ '/[\x{0436}]/u',

            /* Uppercase */
            /* A  */ '/[\x{0100}\x{0102}\x{0104}\x{00C0}\x{00C1}\x{00C2}\x{00C3}\x{00C4}\x{00C5}\x{0410}]/u',
            /* B  */ '/[\x{0411}]/u',
            /* C  */ '/[\x{00C7}\x{0106}\x{0108}\x{010A}\x{010C}\x{0426}]/u',
            /* D  */ '/[\x{010E}\x{0110}\x{0414}]/u',
            /* E  */ '/[\x{00C8}\x{00C9}\x{00CA}\x{00CB}\x{0112}\x{0114}\x{0116}\x{0118}\x{011A}\x{0415}\x{042D}]/u',
            /* F  */ '/[\x{0424}]/u',
            /* G  */ '/[\x{011C}\x{011E}\x{0120}\x{0122}\x{0413}\x{0490}]/u',
            /* H  */ '/[\x{0124}\x{0126}]/u',
            /* I  */ '/[\x{0128}\x{012A}\x{012C}\x{012E}\x{0130}\x{0418}\x{0406}]/u',
            /* J  */ '/[\x{0134}\x{0419}]/u',
            /* K  */ '/[\x{0136}\x{041A}]/u',
            /* L  */ '/[\x{0139}\x{013B}\x{013D}\x{0139}\x{0141}\x{041B}]/u',
            /* M  */ '/[\x{041C}]/u',
            /* N  */ '/[\x{00D1}\x{0143}\x{0145}\x{0147}\x{014A}\x{041D}]/u',
            /* O  */ '/[\x{00D3}\x{014C}\x{014E}\x{0150}\x{041E}]/u',
            /* P  */ '/[\x{041F}]/u',
            /* R  */ '/[\x{0154}\x{0156}\x{0158}\x{0420}]/u',
            /* S  */ '/[\x{015A}\x{015C}\x{015E}\x{0160}\x{0421}]/u',
            /* T  */ '/[\x{0162}\x{0164}\x{0166}\x{0422}]/u',
            /* U  */ '/[\x{00D9}\x{00DA}\x{00DB}\x{00DC}\x{0168}\x{016A}\x{016C}\x{016E}\x{0170}\x{0172}\x{0423}]/u',
            /* V  */ '/[\x{0412}]/u',
            /* W  */ '/[\x{0174}]/u',
            /* Y  */ '/[\x{0176}\x{042B}]/u',
            /* Z  */ '/[\x{0179}\x{017B}\x{017D}\x{0417}]/u',
            /* AE */ '/[\x{00C6}]/u',
            /* CH */ '/[\x{0427}]/u',
            /* KH */ '/[\x{0425}]/u',
            /* OE */ '/[\x{0152}]/u',
            /* SH */ '/[\x{0428}]/u',
            /* SHH*/ '/[\x{0429}]/u',
            /* YA */ '/[\x{042F}]/u',
            /* YE */ '/[\x{0404}]/u',
            /* YI */ '/[\x{0407}]/u',
            /* YO */ '/[\x{0401}]/u',
            /* YU */ '/[\x{042E}]/u',
            /* ZH */ '/[\x{0416}]/u');

            // ö to oe
            // å to aa
            // ä to ae

        $replacements = array(
                'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p', 'r', 's', 'ss', 't', 'u', 'v', 'w', 'y', 'z', 'ae', 'ch', 'kh', 'oe', 'sh', 'shh', 'ya', 'ye', 'yi', 'yo', 'yu', 'zh',
                'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'R', 'S', 'T', 'U', 'V', 'W', 'Y', 'Z', 'AE', 'CH', 'KH', 'OE', 'SH', 'SHH', 'YA', 'YE', 'YI', 'YO', 'YU', 'ZH'
            );

        return preg_replace($patterns, $replacements, $str);
    }


    public static function str2url($str)
    {
        static $array_str = array();
        static $allow_accented_chars = null;
        static $has_mb_strtolower = null;

        if ($has_mb_strtolower === null) {
            $has_mb_strtolower = function_exists('mb_strtolower');
        }

        if (isset($array_str[$str])) {
            return $array_str[$str];
        }

        if (!is_string($str)) {
            return false;
        }

        if ($str == '') {
            return '';
        }

        $allow_accented_chars = false;

        $return_str = trim($str);

        if ($has_mb_strtolower) {
            $return_str = mb_strtolower($return_str, 'utf-8');
        }
        $return_str = self::replaceAccentedChars($return_str);

        // Remove all non-whitelist chars.
        $return_str = preg_replace('/[^a-zA-Z0-9\s\'\:\/\[\]\-]/', '', $return_str);

        $return_str = preg_replace('/[\s\'\:\/\[\]\-]+/', ' ', $return_str);
        $return_str = str_replace(array(' ', '/'), '-', $return_str);

        // If it was not possible to lowercase the string with mb_strtolower, we do it after the transformations.
        // This way we lose fewer special chars.
        if (!$has_mb_strtolower) {
            $return_str = strtolower($return_str);
        }

        $array_str[$str] = $return_str;
        return $return_str;
    }

    public function buildBBtopic($bulk,$row)
    {
		$bulk->insert(array(
			'_key' => 'topic:'.(int)$row['pid'],
            'tid' => (int)$row['pid'],
            'uid' => (int)$row['uid'],
            'cid' => 2,
            'mainPid' => (int)$row['pid'],
            'title' => trim($row['title']),
            'slug' => (int)($row['pid']).'/'.self::str2url($row['title']),
            'timestamp' => strtotime($row['created'])*1000,
            'postcount' => 0,
            'viewcount'=> $row['views'],
        ));
		$bulk->insert(self::dj('{"_key":"topics:tid",                          "value":'.(int)$row['pid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));
		$bulk->insert(self::dj('{"_key":"cid:2:tids",                          "value":'.(int)$row['pid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));
		$bulk->insert(self::dj('{"_key":"uid:'.(int)$row['uid'].':topics",     "value":'.(int)$row['pid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));
		$bulk->insert(self::dj('{"_key":"cid:2:uid:'.(int)$row['uid'].':tids", "value":'.(int)$row['pid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));
		
		
        //$bulk->update(self::dj('{"_key":"topic:'.(int)$row['pid'].'}'),self::dj('{"$inc":{"postcount":1}}'));
        $bulk->update(self::dj('{"_key":"user:'.(int)$row['uid'].'"}'),   self::dj('{"$inc":{"topiccount":1}}'));
        $bulk->update(self::dj('{"_key":"category:2"}'),                 self::dj('{"$inc":{"topic_count":1}}'));        
    }
    public function importQAPosts()
    {
    	$topics = array();
    	
        $sqlp = self::$mydb->prepare('select
                parentid as tid,postid pid,coalesce(userid,acount) as uid,title,content,
                created,views
            from qa_posts
            where type in ("Q","C","A") order by parentid, postid
        ');
        $sqlp->execute();

        $bulk = new \MongoDB\Driver\BulkWrite(array('ordered'=>true));
        //$bulk->delete(array());

        while( ($row = $sqlp->fetch(PDO::FETCH_ASSOC)) !== false){
        	
            if (empty($row['tid']) && !empty($row['title'])) {
            	self::buildBBtopic($bulk, $row);
            	$topics[$row['pid']] = array($row['pid']);
            }
            else {
            	foreach($topics as $tid => $topic){
            		if (in_array($row['tid'],$topic)){
            			$row['tid']=$tid;
            		}
            	}
            	$topics[$row['tid']][] = $row['pid'];
            }
            
            if (0) if ($row['pid'] == 344){
            	die(print_r(array($topics,$row),1));
            }
            $bulk->insert(array(
                '_key' => 'post:'.(int)$row['pid'],
                'uid' => $row['uid'],
                'tid' => (int)(empty($row['tid'])?$row['pid']:$row['tid']),
                'pid' => $row['pid'],
                'content' => (!empty($row['content'])?html2markdown($row['content']):$row['content']),
                'deleted' => 0,
                'timestamp' => strtotime($row['created'])*1000,
            ));
            $bulk->insert(self::dj('{"_key":"posts:pid",                       "value":'.(int)$row['pid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));
            $bulk->insert(self::dj('{"_key":"group:administrators:member:pids","value":'.(int)$row['pid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));
            $bulk->insert(self::dj('{"_key":"uid:'.(int)$row['uid'].':posts",  "value":'.(int)$row['pid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));

            if (!empty($row['tid'])){
                $bulk->insert(self::dj('{"_key":"tid:'.(int)$row['tid'].':posts", "value":'.(int)$row['pid'].',"score":'.(float)(strtotime($row['created'])*1000).'}'));
        		$bulk->update(self::dj('{"_key":"topic:'.(int)$row['tid'].'"}'),self::dj('{"$inc":{"postcount":1}}'));
        		$bulk->update(self::dj('{"_key":"topic:'.(int)$row['tid'].'"}'),self::dj('{"$set":{"teaserPid":'.(int)$row['tid'].'}}'));
            }
            //print_r($row);
        }
        //$writeConcern = new MongoDB\Driver\WriteConcern(MongoDB\Driver\WriteConcern::MAJORITY, 1000);

        try {
            $result = self::$mong->executeBulkWrite('nodebb.objects', $bulk /*, $writeConcern*/);
        } catch (MongoDB\Driver\Exception\BulkWriteException $e) {
            $result = $e->getWriteResult();

            // Check if the write concern could not be fulfilled
            if ($writeConcernError = $result->getWriteConcernError()) {
                printf("%s (%d): %s\n",
                    $writeConcernError->getMessage(),
                    $writeConcernError->getCode(),
                    var_export($writeConcernError->getInfo(), true)
                );
            }

            // Check if any write operations did not complete at all
            foreach ($result->getWriteErrors() as $writeError) {
                printf("Operation#%d: %s (%d)\n",
                    $writeError->getIndex(),
                    $writeError->getMessage(),
                    $writeError->getCode()
                );
            }
        } catch (MongoDB\Driver\Exception\Exception $e) {
            printf("Other error: %s\n", $e->getMessage());
            exit;
        }
    }

    public function fetchBBUsers()
    {
        $query = new \MongoDB\Driver\Query(array(
            '_key' => 'username:uid',
        ));
        $users = self::$mong->executeQuery('nodebb.objects',$query);

        foreach($users as $user){
            $this->getBBUser($user->score);
        }
    }	

    public function getBBUser($id)
    {
        $query = new \MongoDB\Driver\Query(array(
            '_key' => 'user:'.(int)$id,
        ));
        $cmain = self::$mong->executeQuery('nodebb.objects',$query);
        foreach($cmain as $main){
            print_r($main);
        }
    }

    public function after()
    {
    	$bulk = new \MongoDB\Driver\BulkWrite(array('ordered'=>true));
    	$bulk->update(self::dj('{"_key":"global"}'),self::dj('{"$set":{"nextUid": 200,"userCount": 100, "nextTid": 1000, "topicCount": 83, "nextPid": 1000, "postCount": 356}}'));
        try {
            $result = self::$mong->executeBulkWrite('nodebb.objects', $bulk /*, $writeConcern*/);
        } catch (MongoDB\Driver\Exception\BulkWriteException $e) {
            $result = $e->getWriteResult();

            // Check if the write concern could not be fulfilled
            if ($writeConcernError = $result->getWriteConcernError()) {
                printf("%s (%d): %s\n",
                    $writeConcernError->getMessage(),
                    $writeConcernError->getCode(),
                    var_export($writeConcernError->getInfo(), true)
                );
            }

            // Check if any write operations did not complete at all
            foreach ($result->getWriteErrors() as $writeError) {
                printf("Operation#%d: %s (%d)\n",
                    $writeError->getIndex(),
                    $writeError->getMessage(),
                    $writeError->getCode()
                );
            }
        } catch (MongoDB\Driver\Exception\Exception $e) {
            printf("Other error: %s\n", $e->getMessage());
            exit;
        }
    }
}

$q = new qa2nbb();
$q->importQAUsers();
$q->importQAPosts();
$q->after();
//$q-> fetchQAUsers();
//$q-> fetchBBUsers();