<?php
$counter = 0;
$flags = array();
$db1;
$db2;
$db1_table_prefix = '';
$db2_table_prefix = '';
$truncate = false;
$truncate_user_groups = false;
$batch_size = 1000;
$id_padding = 10;
$overwrite_user_login_credentials = false;
$ignore_image_move_warning = false;
$i_know_what_im_doing = false;
$log_location = './log.txt';
$logging_enabled = false;
$exist_cache = array();

function starts_with($string, $start_string)
{
    $len = strlen($start_string);
    return (substr($string, 0, $len) === $start_string);
}

function array_remove(&$array, $item)
{
    $index = array_search($item, $array);
    if ($index === false)
        return false;
    array_splice($array, $index, 1);
    return true;
}

function remove_prefix($string, $prefix)
{
    if (substr($string, 0, strlen($prefix)) == $prefix) {
        $string = substr($string, strlen($prefix));
    }
    return $string;
}

for ($i = 1; $i < $argc; $i++) {
    $arg = $argv[$i];
    $is_option = starts_with($arg, '--');

    if ($is_option) {
        $kvp = remove_prefix($arg, '--');
        $kvp = explode('=', $kvp, 2);
        $key = $kvp[0];
        $value = array_key_exists(1, $kvp) ? $kvp[1] : NULL;

        switch ($key) {
            case 'db1_table_prefix':
                $db1_table_prefix = $value;
                break;

            case 'db2_table_prefix':
                $db2_table_prefix = $value;
                break;

            case 'overwrite_user_login_credentials':
                $overwrite_user_login_credentials = true;
                break;

            case 'truncate':
                $truncate = true;
                break;

            case 'truncate':
                $truncate = true;
                break;

            case 'truncate_user_groups':
                $truncate_user_groups = true;
                break;

            case 'ignore_image_move_warning':
                $ignore_image_move_warning = true;
                break;

            case 'log':
                $logging_enabled = true;
                $log_location = $value;
                break;

            case 'confirm':
                $i_know_what_im_doing = $value === 'i know what im doing';
                break;

            case 'id_padding':
                $value = intval($value);
                if ($value < 1) throw new Exception('id_padding must be larger than 1');
                if ($value > 1000000) throw new Exception('id_padding must not be larger than 1000000');
                $id_padding = $value;

            case 'batch_size':
                $value = intval($value);
                if ($batch_size < 1) throw new Exception('batch_size must be larger than 1');
                if ($batch_size > 1000000) throw new Exception('batch_size must not be larger than 1000000');
                $batch_size = $value;

                break;

            default:
                throw new Exception('Invalid option "' . $key . '"');
                break;
        }
    } else if ($counter === 0) {
        $db1 = parse_url($arg);
    } else if ($counter === 1) {
        $db2 = parse_url($arg);
    }

    if (!$is_option) $counter++;
}

if (!$i_know_what_im_doing) {
    echo "
This is a powerful and dangerous tool and must be yielded with care.
Please never execute this script on a production database.
Pass in --confirm=\"i know what im doing\" to continue" . PHP_EOL;
    die();
}

if ($logging_enabled) {
    ob_start();
}

if (!$ignore_image_move_warning) {
    echo "
Make sure to copy all images from one site to the other using ftp, rsync or whatever other method:
https://www.hikashop.com/forum/install-update/896417-move-existing-image-directory.html 
    " . PHP_EOL;
}

$arr = array($db2['host'], $db2['user'], $db2['pass'], ltrim($db2['path'], '/'), $db2['port']);

$conn1 = mysqli_connect($db1['host'], $db1['user'], $db1['pass'], ltrim($db1['path'], '/'), $db1['port']);

if ($conn1->connect_error) {
    die("Connection failed: " . $conn1->connect_error);
}

$conn2 = mysqli_connect($db2['host'], $db2['user'], $db2['pass'], ltrim($db2['path'], '/'), $db2['port']);

if ($conn2->connect_error) {
    die("Connection failed: " . $conn2->connect_error);
}

try {

    $conn1->begin_transaction();
    $conn2->begin_transaction();

    $truncate_list = array();

    if ($truncate) {
        array_push(
            $truncate_list,
            'hikashop_product',
            'hikashop_product_category',
            'hikashop_product_related',
            'hikashop_shipping',
            'hikashop_shipping_price',
            'hikashop_tax',
            'hikashop_taxation',
            'hikashop_price',
            'hikashop_file',
            'hikashop_field',
            'hikashop_entry',
            'hikashop_email_log',
            'hikashop_download',
            'hikashop_discount',
            'hikashop_currency',
            'hikashop_click',
            'hikashop_characteristic',
            'hikashop_category',
            'hikashop_cart_product',
            'hikashop_cart',
            'hikashop_address',
            'hikashop_user',
            'hikashop_file',
            'hikashop_variant',
            'hikashop_zone',
            'hikashop_zone_link'
        );
    }

    if ($truncate_user_groups || $truncate) {
        $truncate_list[] = 'viewlevels';
        $truncate_list[] = 'usergroups';
        $truncate_list[] = 'user_usergroup_map';
    }

    function get_columns($table)
    {
        static $cache = array();

        if (array_key_exists($table, $cache)) return $cache[$table];

        echo "get list of columns for table: " . $table . PHP_EOL;

        global $db2_table_prefix;
        global $conn2;
        $t2 = $db2_table_prefix . $table;

        $result = $conn2->query("
SELECT column_name
FROM information_schema.columns 
WHERE table_name = '" . $t2 . "'
  ");

        if (!$result) throw new Exception(mysqli_error($conn2));
        $column_names = array();
        if ($result->num_rows > 0) {
            while ($data = $result->fetch_assoc()) {
                $column_names[] = $data['column_name'];
            }
        }
        $cache[$table] = $column_names;
        return $column_names;
    }

    function table_exists($conn, $table_with_prefix)
    {
        echo "check if table exists " . $table_with_prefix . PHP_EOL;

        $t2 = $table_with_prefix;

        $result = $conn->query("
SELECT column_name
FROM information_schema.columns 
WHERE table_name = '" . $t2 . "'
  ");

        if (!$result) throw new Exception(mysqli_error($conn));
        return $result->num_rows > 0;
    }

    function table_exists_on_both_dbs($table)
    {
        static $cache = array();

        if (array_key_exists($table, $cache)) return $cache[$table];

        echo "get list of columns for table: " . $table . PHP_EOL;

        global $db2_table_prefix, $db1_table_prefix, $conn2, $conn1;

        $exists = table_exists($conn1, $db1_table_prefix . $table)
            && table_exists($conn2, $db2_table_prefix . $table);

        $cache[$table] = $exists;
        return $exists;
    }

    function get_primary_id_column_name($table)
    {
        static $cache = array();

        if (array_key_exists($table, $cache)) return $cache[$table];

        echo "get primary id column for table: " . $table . PHP_EOL;

        global $db1_table_prefix;
        global $conn1;
        $t1 = $db1_table_prefix . $table;

        $stmt = $conn1->prepare("
SELECT COLUMN_NAME as column_name
FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = ? 
  AND CONSTRAINT_NAME = 'PRIMARY'
  ");
        if (!$stmt) throw new Exception(mysqli_error($conn1));
        $stmt->bind_param('s', $t1);
        $stmt->execute();
        $stmt->bind_result($column_name);
        $stmt->fetch();
        $stmt->close();
        $cache[$table] = $column_name;
        return $column_name;
    }

    function id_offset($table)
    {
        static $cache = array();
        if (array_key_exists($table, $cache)) return $cache[$table];
        echo "get id offset for table: " . $table . PHP_EOL;
        global $db1_table_prefix;
        global $db2_table_prefix;
        global $conn1;
        global $conn2;
        $primary_column = get_primary_id_column_name($table);
        $t1 = $db1_table_prefix . $table;
        $t2 = $db2_table_prefix . $table;

        $q1 = "SELECT MIN(" . $primary_column . ") as min1 FROM " . $t1;
        $res1 = $conn1->query($q1);
        if (!$res1) throw new Exception("statement: " . $q1 . ", error: " . mysqli_error($conn1));
        $q2 = "SELECT MAX(" . $primary_column . ") as max2 FROM " . $t2;
        $res2 = $conn2->query($q2);
        if (!$res2) throw new Exception("statement: " . $q2 . ", error: " . mysqli_error($conn2));

        $row1 = $res1->fetch_assoc();
        $row2 = $res2->fetch_assoc();

        $max2 = $row2['max2'] || 0;
        $min1 = $row1['min1'] || 0;

        $result = $max2 - $min1;
        echo "id offset for table: " . $table . ' is ' . $result . ', minimum id for db1 is ' . $min1 . ' and maximum id for db2 is ' . $max2 . PHP_EOL;
        $cache[$table] = $result;
        return $result;
    }

    function process_user_id($db1user_id)
    {
        static $cache = array();
        global $exist_cache;

        if (array_key_exists($db1user_id, $cache)) return $cache[$db1user_id];
        global $db1_table_prefix, $db2_table_prefix, $conn1, $conn2;
        $t1 = $db1_table_prefix . 'users';
        $t2 = $db2_table_prefix . 'users';
        $sql = 'SELECT * FROM ' . $t1 . ' WHERE id = ' . $db1user_id;
        $result = $conn1->query($sql);
        if (!$result) throw new Exception(mysqli_error($conn1) . ', ' . $sql);
        $row = $result->fetch_assoc();
        $result = $conn2->query('SELECT * FROM ' . $t2 . " WHERE email = '" . mysqli_real_escape_string($conn2, $row['email']) . "'");
        if (!$result) {
            throw new Exception(mysqli_error($conn2));
        }
        if ($result->num_rows === 0) {
            global $id_padding;
            $offset = id_offset('users');
            $id = $db1user_id + $offset + $id_padding;
            echo 'did not find a match for user with email: ' . $row['email'] . ' and id: ' . $db1user_id . '...' . PHP_EOL;
            $exists = false;
            $cache[$db1user_id] = $id;
            $exist_cache[$db1user_id] = $exists;
            return $id;
        } else if ($result->num_rows === 1) {
            $row = $result->fetch_assoc();
            $id = $row['id'];
            echo 'found a match for user with email: ' . $row['email'] . ' it already exists and has id: ' . $id . '...' . PHP_EOL;
            $exists = true;
            $cache[$db1user_id] = $id;
            $exist_cache[$db1user_id] = $exists;
            return $id;
        }
    }

    function process_id($table, $id)
    {
        if ($id == 0) return '0';
        if ($table === 'usergroups') return $id;
        if ($table === 'users') {
            return '' . process_user_id($id);
        } else {
            global $id_padding;
            $offset = id_offset($table);
            return '' . ($id + $offset + $id_padding);
        }
    }

    // truncate
    if (!$truncate && !$truncate_user_groups) {
        throw new Exception('You need to at least truncate usergroups');
    }
    foreach ($truncate_list as &$table) {
        $result = $conn2->query('TRUNCATE TABLE ' . $db2_table_prefix . $table);
        echo "truncated " . $db2_table_prefix . $table . ' table' . PHP_EOL;
        if (!$result) throw new Exception(mysqli_error($conn2));
    }

    // process categories

    echo 'processing categories...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_category';
    $t2 = $db2_table_prefix . 'hikashop_category';

    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $column_names = get_columns('hikashop_category');
    $list = array();

    function flush_data()
    {
        global $list, $conn2, $t2, $column_names;
        if (count($list) === 0) return;
        // flush buffer if count becomes too large
        $query_values = implode(",\n", $list);
        $list = array();
        $query = "INSERT INTO " . $t2 . " (" . implode(',', $column_names) . ") VALUES " . $query_values;
        $result = $conn2->query($query);
        if (!$result) {
            echo "table: " . $t2 . PHP_EOL;
            echo "statement: " . $query . PHP_EOL;
            throw new Exception(mysqli_error($conn2));
        }
    }

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['category_id'] = process_id('hikashop_category', $record['category_id']);
            if ($record['category_type'] !== 'root') {
                $record['category_parent_id'] = process_id('hikashop_category', $record['category_parent_id']);
            }
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process users
    echo 'processing users...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'users';
    $t2 = $db2_table_prefix . 'users';
    $column_names = get_columns('users');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $old_id = $record['id'];
            $columns = implode(',', array_keys($record));
            $new_id = process_user_id($old_id);
            $record['id'] = $new_id;
            // old id will now be in the exists cache (side effect)
            $exists = $exist_cache[$old_id];
            if ($exists) {
                $columns_to_update = $columns;
                array_remove($column_names, 'username');
                array_remove($column_names, 'email');
                array_remove($column_names, 'password');
                array_remove($column_names, 'password');
                echo 'user with email: ' . $record['email'] . ' already exists...' . PHP_EOL;
                $values = array_map(function ($column) use ($conn2, $record) {
                    return $column . " = '" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
                }, $column_names);
                $escaped_values = implode(', ', $values);
                $sql = 'UPDATE ' . $t2 . ' SET ' . $escaped_values . ' WHERE id = ' . $record['id'];
                $r = $conn2->query($sql);
                if (!$r) throw new Exception(mysqli_error($conn2) . ', sql: ' . $sql);
            } else {
                echo 'old id: ' . $old_id . PHP_EOL;
                echo 'old email: ' . $record['email'] . PHP_EOL;
                $values = array_map(function ($column) use ($conn2, $record) {
                    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
                }, $column_names);
                $escaped_values = implode(', ', $values);
                $sql = 'INSERT INTO ' . $t2 . ' (' . $columns . ') VALUES (' . $escaped_values . ')';
                $r = $conn2->query($sql);
                if (!$r) throw new Exception(mysqli_error($conn2) . ', sql: ' . $sql);
            }
        }
    }

    // process usergroup map
    echo 'processing usergroup map...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'user_usergroup_map';
    $t2 = $db2_table_prefix . 'user_usergroup_map';
    $column_names = get_columns('user_usergroup_map');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $old_id = $record['user_id'];
            if ($old_id == 833) var_dump($record);
            $record['user_id'] = process_id('users', $record['user_id']);
            $record['group_id'] = process_id('usergroups', $record['group_id']);

            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();

            if ($old_id == 833) var_dump($record);
        }
        flush_data();
    }

    // process usergroups
    echo 'processing usergroups...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'usergroups';
    $t2 = $db2_table_prefix . 'usergroups';
    $column_names = get_columns('usergroups');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();
    $records = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop users
    echo 'processing hikashop users...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_user';
    $t2 = $db2_table_prefix . 'hikashop_user';
    $column_names = get_columns('hikashop_user');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['user_id'] = process_id('hikashop_user', $record['user_id']);
            $record['user_cms_id'] = process_id('users', $record['user_cms_id']);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop viewlevels
    echo 'processing hikashop viewlevels...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'viewlevels';
    $t2 = $db2_table_prefix . 'viewlevels';
    $column_names = get_columns('viewlevels');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop products
    echo 'processing hikashop products...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_product';
    $t2 = $db2_table_prefix . 'hikashop_product';
    $column_names = get_columns('hikashop_product');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['product_id'] = process_id('hikashop_product', $record['product_id']);
            $record['product_parent_id'] = process_id('hikashop_product', $record['product_parent_id']);
            $record['product_tax_id'] = process_id('hikashop_tax', $record['product_tax_id']);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop product-category relations
    echo 'processing hikashop product-category relations...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_product_category';
    $t2 = $db2_table_prefix . 'hikashop_product_category';
    $column_names = get_columns('hikashop_product_category');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['product_category_id'] = process_id('hikashop_product_category', $record['product_category_id']);
            $record['category_id'] = process_id('hikashop_category', $record['category_id']);
            $record['product_id'] = process_id('hikashop_product', $record['product_id']);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop product-product relations
    echo 'processing hikashop product-product relations...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_product_related';
    $t2 = $db2_table_prefix . 'hikashop_product_related';
    $column_names = get_columns('hikashop_product_related');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['product_id'] = process_id('hikashop_product', $record['product_id']);
            $record['product_related_id'] = process_id('hikashop_product', $record['product_related_id']);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop currency
    echo 'processing hikashop currencies...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_currency';
    $t2 = $db2_table_prefix . 'hikashop_currency';
    $column_names = get_columns('hikashop_currency');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['currency_id'] = process_id('hikashop_currency', $record['currency_id']);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }


    // process hikashop product-product relations
    echo 'processing hikashop price...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_price';
    $t2 = $db2_table_prefix . 'hikashop_price';
    $column_names = get_columns('hikashop_price');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['price_id'] = process_id('hikashop_price', $record['price_id']);
            $record['price_currency_id'] = process_id('hikashop_currency', $record['price_currency_id']);
            $record['price_product_id'] = process_id('hikashop_product', $record['price_product_id']);

            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }


    // process hikashop shipping
    echo 'processing hikashop shipping...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_shipping';
    $t2 = $db2_table_prefix . 'hikashop_shipping';
    $column_names = get_columns('hikashop_shipping');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['shipping_id'] = process_id('hikashop_shipping', $record['shipping_id']);
            $record['shipping_tax_id'] = process_id('hikashop_tax', $record['shipping_tax_id']);
            $record['shipping_currency_id'] = process_id('hikashop_currency', $record['shipping_currency_id']);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop shipping prices
    echo 'processing hikashop shipping prices...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_shipping_price';
    $t2 = $db2_table_prefix . 'hikashop_shipping_price';
    $column_names = get_columns('hikashop_shipping_price');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['shipping_price_id'] = process_id('hikashop_shipping_price', $record['shipping_price_id']);
            $record['shipping_id'] = process_id('hikashop_shipping', $record['shipping_id']);
            $record['shipping_currency_id'] = process_id('hikashop_currency', $record['shipping_currency_id']);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop shipping tax data
    echo 'processing hikashop tax data...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_tax';
    $t2 = $db2_table_prefix . 'hikashop_tax';
    $column_names = get_columns('hikashop_tax');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop zones data
    echo 'processing hikashop zones data...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_zone';
    $t2 = $db2_table_prefix . 'hikashop_zone';
    $column_names = get_columns('hikashop_zone');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['zone_id'] = process_id('hikashop_zone', $record['zone_id']);
            $record['zone_currency_id'] = process_id('hikashop_currency', $record['zone_currency_id']);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop zone links
    echo 'processing hikashop zone links...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_zone_link';
    $t2 = $db2_table_prefix . 'hikashop_zone_link';
    $column_names = get_columns('hikashop_zone_link');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop taxation data
    echo 'processing hikashop taxation data...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_taxation';
    $t2 = $db2_table_prefix . 'hikashop_taxation';
    $column_names = get_columns('hikashop_taxation');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['taxation_id'] = process_id('hikashop_taxation', $record['taxation_id']);
            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    if (table_exists_on_both_dbs('hikashop_file')) {
        // process hikashop files
        echo 'processing hikashop files data...' . PHP_EOL;

        $t1 = $db1_table_prefix . 'hikashop_file';
        $t2 = $db2_table_prefix . 'hikashop_file';
        $column_names = get_columns('hikashop_file');
        $result = $conn1->query("SELECT * FROM " . $t1 . "");
        if (!$result) throw new Exception(mysqli_error($conn1));
        $columns;
        $list = array();

        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $record = array_merge(array(), $row);
                $record['file_id'] = process_id('hikashop_file', $record['file_id']);
                if ($record['file_type'] === 'product') {
                    $record['file_ref_id'] = process_id('hikashop_product', $record['file_ref_id']);
                } else if ($record['file_type'] === 'category') {
                    $record['file_ref_id'] = process_id('hikashop_category', $record['file_ref_id']);
                }
                $columns = implode(',', array_keys($record));
                $escaped_values = array_map(function ($column) use ($conn2, $record) {
                    return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
                }, $column_names);
                $vals = implode(", ", $escaped_values);
                array_push($list, "(" . $vals . ")");
                if (count($list) >= $batch_size) flush_data();
            }
            flush_data();
        }
    } else {
        echo "skipping hikashop_file table because it does not exist on both databases..." . PHP_EOL;
    }

    if (table_exists_on_both_dbs('hikashop_field')) {

        // process hikashop field 
        echo 'processing hikashop field data...' . PHP_EOL;

        $t1 = $db1_table_prefix . 'hikashop_field';
        $t2 = $db2_table_prefix . 'hikashop_field';
        $column_names = get_columns('hikashop_field');
        $result = $conn1->query("SELECT * FROM " . $t1 . "");
        if (!$result) throw new Exception(mysqli_error($conn1));
        $columns;
        $list = array();

        if ($result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $record = array_merge(array(), $row);
                $record['field_id'] = process_id('hikashop_field', $record['field_id']);
                $columns = implode(',', array_keys($record));
                $escaped_values = array_map(function ($column) use ($conn2, $record) {
                    $val = array_key_exists($column, $record) ? $record[$column] : NULL;
                    return "'" . mysqli_real_escape_string($conn2, $val) . "'";
                }, $column_names);
                $vals = implode(", ", $escaped_values);
                array_push($list, "(" . $vals . ")");
                if (count($list) >= $batch_size) flush_data();
            }
            flush_data();
        }
    } else {
        echo "skipping hikashop_field table because it does not exist on both databases..." . PHP_EOL;
    }

    // process hikashop discounts
    echo 'processing hikashop discounts...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_discount';
    $t2 = $db2_table_prefix . 'hikashop_discount';
    $column_names = get_columns('hikashop_discount');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['discount_id'] = process_id('hikashop_discount', $record['discount_id']);

            $discount_access_ids = explode(',', $record['discount_access']);
            $new_dca_list = array();
            foreach ($discount_access_ids as $dca) {
                if (empty($dci)) {
                    $new_dca_list[] = $dca;
                } else {
                    $new_dca_list[] = process_id('usergroups', $dca);
                }
            }
            $record['discount_access'] = implode(',', $new_dca_list);

            $discount_category_ids = explode(',', $record['discount_category_id']);
            $new_dci_list = array();
            foreach ($discount_category_ids as $dci) {
                if (empty($dci)) {
                    $new_dci_list[] = $dci;
                } else {
                    $new_dci_list[] = process_id('hikashop_category', $dci);
                }
            }
            $record['discount_category_id'] = implode(',', $new_dci_list);
            $record['discount_product_id'] = process_id('hikashop_product', $record['discount_product_id']);
            $record['discount_zone_id'] = process_id('hikashop_zone', $record['discount_zone_id']);
            $record['discount_currency_id'] = process_id('hikashop_currency', $record['discount_currency_id']);
            $record['discount_tax_id'] = process_id('hikashop_tax', $record['discount_tax_id']);
            if (!empty($record['discount_user_id'])) $record['discount_user_id'] = process_id('hikashop_user', $record['discount_user_id']);

            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop characteristics
    echo 'processing hikashop characteristics...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_characteristic';
    $t2 = $db2_table_prefix . 'hikashop_characteristic';
    $column_names = get_columns('hikashop_characteristic');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['characteristic_id'] = process_id('hikashop_characteristic', $record['characteristic_id']);
            $record['characteristic_parent_id'] = process_id('hikashop_characteristic', $record['characteristic_parent_id']);

            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }


    // process hikashop adresses
    echo 'processing hikashop adresses...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_address';
    $t2 = $db2_table_prefix . 'hikashop_address';
    $column_names = get_columns('hikashop_address');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['address_id'] = process_id('hikashop_address', $record['address_id']);
            $record['address_user_id'] = process_id('hikashop_user', $record['address_user_id']);

            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // process hikashop variants
    echo 'processing hikashop variants...' . PHP_EOL;

    $t1 = $db1_table_prefix . 'hikashop_variant';
    $t2 = $db2_table_prefix . 'hikashop_variant';
    $column_names = get_columns('hikashop_variant');
    $result = $conn1->query("SELECT * FROM " . $t1 . "");
    if (!$result) throw new Exception(mysqli_error($conn1));
    $columns;
    $list = array();

    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $record = array_merge(array(), $row);
            $record['variant_characteristic_id'] = process_id('hikashop_characteristic', $record['variant_characteristic_id']);
            $record['variant_product_id'] = process_id('hikashop_product', $record['variant_product_id']);

            $columns = implode(',', array_keys($record));
            $escaped_values = array_map(function ($column) use ($conn2, $record) {
                return "'" . mysqli_real_escape_string($conn2, $record[$column]) . "'";
            }, $column_names);
            $vals = implode(", ", $escaped_values);
            array_push($list, "(" . $vals . ")");
            if (count($list) >= $batch_size) flush_data();
        }
        flush_data();
    }

    // commit

    echo "done!" . PHP_EOL;

    if ($logging_enabled) {
        $output = ob_get_contents();
        ob_end_clean();
        file_put_contents($log_location, $output);
    }

    // $conn1->commit();
    // $conn2->commit();
    mysqli_rollback($conn1);
    mysqli_rollback($conn2);
} catch (mysqli_sql_exception $exception) {
    mysqli_rollback($conn1);
    mysqli_rollback($conn2);

    if ($logging_enabled) {
        $output = ob_get_contents();
        ob_end_clean();
        file_put_contents($log_location, $output);
    }

    throw $exception;
}