<?php

/**
 * # XiunoBBS 转 Flarum 
 * ## 使用说明
 * ### 注意：本程序只转移 主题、帖子和版块，不转会员
 * 
 * 1. 将本文件上传到两个网站共同的服务器（只要支持两个站的数据库连接均可）
 * 2. 复制 xiunobbs 下的 conf/conf.php 到本目录，或者自己修改以下的引入路径
 * 3. 复制 flarum 下的 config.php 到本目录，或者自己修改以下的引入路径
 * 4. 执行 composer require s9e/text-formatter 安装转换的扩展
 * 5. 再执行 php xiuno2flarum.php 命令行进行转换即可
 * 
 * 注意：由于 Flarum 程序与 Markdown 兼容问题，本程序转换的数据支持不完美。
 * 
 */

// Get the autoloader
include __DIR__ . '/vendor/autoload.php';

// Use the Forum bundle. It supports BBCodes, emoticons and autolinking
use s9e\TextFormatter\Bundles\Forum as TextFormatter;

define('XIUNO_PATH', '');
define('FLARUM_PATH', '');

$xb_cfg = include 'conf.php';
$fl_cfg = include 'config.php';

$xn_cfg = $xb_cfg['db']['mysql']['master'];
$dsn_xn = sprintf('mysql:dbname=%s;host=%s;charset=%s', $xn_cfg['name'], $xn_cfg['host'], $xn_cfg['charset']);
$dbh_xn = new PDO($dsn_xn, $xn_cfg['user'], $xn_cfg['password']);

$fm_cfg = $fl_cfg['database'];
$dsn_fl = sprintf('mysql:dbname=%s;host=%s;charset=%s', $fm_cfg['database'], $fm_cfg['host'], $fm_cfg['charset']);
$dbh_fl = new PDO($dsn_fl, $fm_cfg['username'], $fm_cfg['password']);

$dbh_fl->exec('set @@foreign_key_checks=OFF');

function date_at(string $time): string
{
    return date('Y-m-s h:i:s', $time);
}

function insert_tags()
{
    global $xn_cfg,$fm_cfg,$dbh_xn,$dbh_fl;

    $sth = $dbh_xn->prepare('SELECT * FROM `' . $xn_cfg['tablepre'] . 'forum`');
    $sth->execute();
    // $result = $sth->fetchAll(PDO::FETCH_ASSOC);

    $dbh_fl->exec('DELETE FROM `' . $fm_cfg['prefix'] . 'tags`');
    // $dbh_fl->exec('TRUNCATE TABLE`' . $fm_cfg['prefix'] . 'tags`');

    while($row = $sth->fetch(PDO::FETCH_ASSOC))
    {
        //print_r($row);
        $stmt = $dbh_fl->prepare('INSERT INTO `' . $fm_cfg['prefix'] . 'tags` (id,name,slug,description,position) VALUES (?,?,?,?,?)');

        try {
            $data = [ 
                $row['fid'], 
                $row['name'], 
                'tag_' . $row['fid'], 
                $row['brief'], 
                $row['fid'], 
            ];

            $dbh_fl->beginTransaction();
            $stmt->execute($data);
            $dbh_fl->commit();
            // print $dbh_fl->lastInsertId();
        } catch(PDOExecption $e) {
            $dbh_fl->rollback();
            print "Error!: " . $e->getMessage() . "\n";
            exit;
        }
    }

    print 'Insert table ' . $fm_cfg['prefix'] . "tags\n";
}

function insert_discussions()
{
    global $xn_cfg,$fm_cfg,$dbh_xn,$dbh_fl;

    $sth = $dbh_xn->prepare('SELECT * FROM `' . $xn_cfg['tablepre'] . 'thread`');
    $sth->execute();
    // $result = $sth->fetchAll(PDO::FETCH_ASSOC);

    $dbh_fl->exec('DELETE FROM `' . $fm_cfg['prefix'] . 'discussions`');
    // $dbh_fl->exec('TRUNCATE TABLE`' . $fm_cfg['prefix'] . 'discussions`');
    $dbh_fl->exec('TRUNCATE TABLE`' . $fm_cfg['prefix'] . 'discussion_tag`');
    $dbh_fl->exec('TRUNCATE TABLE`' . $fm_cfg['prefix'] . 'discussion_user`');

    while($row = $sth->fetch(PDO::FETCH_ASSOC))
    {
        // print_r($row);
        $stmt = $dbh_fl->prepare('INSERT INTO `' . $fm_cfg['prefix'] . 'discussions` (id,title,participant_count,created_at,user_id,first_post_id,last_posted_at,last_posted_user_id,last_post_id,slug,is_sticky) VALUES (?,?,1,FROM_UNIXTIME(?),?,?,FROM_UNIXTIME(?),?,?,"",?)');

        try {
            $data = [ 
                $row['tid'], 
                $row['subject'], 
                $row['create_date'],
                $row['uid'],
                $row['firstpid'],
                $row['last_date'],
                $row['lastuid'],
                $row['lastpid'],
                $row['top'] > 0 ? 1 : 0,
            ];

            $dbh_fl->beginTransaction();
            $stmt->execute($data);
            $dbh_fl->commit();
            // print $dbh_fl->lastInsertId();
        } catch(PDOExecption $e) {
            $dbh_fl->rollback();
            print "Error!: " . $e->getMessage() . "\n";
            exit;
        }

        $tag_sql = 'INSERT INTO `' . $fm_cfg['prefix'] . 'discussion_tag` (discussion_id,tag_id) VALUES (?,?)';
        $stmt = $dbh_fl->prepare($tag_sql);
        try {
            $data = [ 
                $row['tid'], 
                $row['fid'], 
            ];

            $dbh_fl->beginTransaction();
            $stmt->execute($data);
            $dbh_fl->commit();
            // print $dbh_fl->lastInsertId();
        } catch(PDOExecption $e) {
            $dbh_fl->rollback();
            print "Error!: " . $e->getMessage() . "\n";
            exit;
        }

        $tag_sql = 'INSERT INTO `' . $fm_cfg['prefix'] . 'discussion_user` (user_id,discussion_id,last_read_at,last_read_post_number) VALUES (?,?,FROM_UNIXTIME(?),1)';
        $stmt = $dbh_fl->prepare($tag_sql);
        try {
            $data = [ 
                $row['uid'], 
                $row['tid'],
                $row['last_date'],
            ];

            $dbh_fl->beginTransaction();

            $stmt->execute($data);
            $dbh_fl->commit();
            // print $dbh_fl->lastInsertId();
        } catch(PDOExecption $e) {
            $dbh_fl->rollback();
            print "Error!: " . $e->getMessage() . "\n";
            exit;
        }
    }

    print 'Insert table ' . $fm_cfg['prefix'] . "discussions\n";
}

function insert_posts()
{
    global $xn_cfg,$fm_cfg,$dbh_xn,$dbh_fl;

    $sth = $dbh_xn->prepare('SELECT * FROM `' . $xn_cfg['tablepre'] . 'post` ORDER BY `create_date` ASC');
    $sth->execute();
    // $result = $sth->fetchAll(PDO::FETCH_ASSOC);

    $dbh_fl->exec('DELETE FROM `' . $fm_cfg['prefix'] . 'posts`');
    // $dbh_fl->exec('TRUNCATE TABLE`' . $fm_cfg['prefix'] . 'posts`');

    $totals = [];
    while($row = $sth->fetch(PDO::FETCH_ASSOC))
    {
        // print_r($row);
        $stmt = $dbh_fl->prepare('INSERT INTO `' . $fm_cfg['prefix'] . 'posts` (id,discussion_id,number,created_at,user_id,type,content,ip_address) VALUES (?,?,?,FROM_UNIXTIME(?),?,"comment",?,?)');

        try {
            if (! isset($totals[$row['tid']])) {
                $totals[$row['tid']] = [];
            }
            $totals[$row['tid']][] = 1;

            // if ($row['isfirst'] === 1) {
            //     $number_id = 1;
            // } else {
            //     $totals[$row['tid']][] = 1;
            // }
            $number_id = count($totals[$row['tid']]);

            // XML representation, that's what you should store in your database
            $xml  = TextFormatter::parse($row['message']);

            // HTML rendering, that's what you display to the user
            $html = TextFormatter::render($xml);

            $data = [ 
                $row['pid'], 
                $row['tid'], 
                $number_id,
                $row['create_date'],
                $row['uid'],
                // '<r>' . $row['message'] . '</r>',
                // '<t>' . $row['message_fmt'] . '</t>',
                '<t>' . $html . '</t>',
                // $html,
                long2ip($row['userip']), 
            ];

            $dbh_fl->beginTransaction();
            $stmt->execute($data);
            $dbh_fl->commit();
            // print $dbh_fl->lastInsertId();
        } catch(PDOExecption $e) {
            $dbh_fl->rollback();
            print "Error!: " . $e->getMessage() . "\n";
            exit;
        }
    }

    print 'Insert table ' . $fm_cfg['prefix'] . "posts\n";
}

function fix_discussion_count()
{
    global $fm_cfg,$dbh_fl;

    $sql = 'SELECT count(*) AS counts, `discussion_id` FROM `' . $fm_cfg['prefix'] . 'posts` GROUP BY `discussion_id`';
    $sth = $dbh_fl->prepare($sql);
    $sth->execute();

    while($row = $sth->fetch(PDO::FETCH_ASSOC))
    {
        // print_r($row);
        $stmt = $dbh_fl->prepare('UPDATE `' . $fm_cfg['prefix'] . 'discussions` SET `comment_count` = ?, post_number_index = ?, last_post_number = ? WHERE id = ?');

        try {
            $data = [ 
                $row['counts'], 
                $row['counts'], 
                1, 
                $row['discussion_id'], 
            ];

            $dbh_fl->beginTransaction();
            $stmt->execute($data);
            $dbh_fl->commit();
            // print $dbh_fl->lastInsertId();
        } catch(PDOExecption $e) {
            $dbh_fl->rollback();
            print "Error!: " . $e->getMessage() . "\n";
            exit;
        }
    }

    print 'Update table ' . $fm_cfg['prefix'] . "discussions counts\n";
}


function fix_tag_stat()
{
    global $fm_cfg,$dbh_fl;

    $sql = 'SELECT *, count(*) AS counts FROM  `'. $fm_cfg['prefix'] . 'discussions` GROUP BY `slug` DESC;';
    $sth = $dbh_fl->prepare($sql);
    $sth->execute();

    while($row = $sth->fetch(PDO::FETCH_ASSOC))
    {
        // print_r($row);
        $stmt = $dbh_fl->prepare('UPDATE `' . $fm_cfg['prefix'] . 'tags` SET `discussion_count` = ?, last_posted_at = ? , last_posted_discussion_id = ?, last_posted_user_id = ? WHERE slug = ?');

        $last_uid = $row['last_posted_user_id'];
        if ($last_uid == 0)
        {
            $last_uid = $row['user_id'];
        }

        try {
            $data = [ 
                $row['counts'],
                $row['last_posted_at'],
                $row['id'],
                $last_uid,
                $row['slug'], 
            ];

            $dbh_fl->beginTransaction();
            $stmt->execute($data);
            $dbh_fl->commit();
            // print $dbh_fl->lastInsertId();
        } catch(PDOExecption $e) {
            $dbh_fl->rollback();
            print "Error!: " . $e->getMessage() . "\n";
            exit;
        }
    }

    print 'Update table ' . $fm_cfg['prefix'] . "tags stat\n";
}

insert_tags();
insert_discussions();
insert_posts();
fix_discussion_count();
fix_tag_stat();