#!/usr/bin/env php
<?php

// Convert database to support Zot 6
require_once('include/cli_startup.php');

cli_startup();


$r = q("SELECT channel.channel_name, channel.channel_portable_id, xchan.xchan_network FROM channel 
	LEFT JOIN xchan ON channel_portable_id = xchan_hash 
	WHERE xchan.xchan_network = 'zot' 
	AND channel.channel_removed = 0"
);

$zot = [];
$zot6 = [];

// indexed columns
$core = [
        'abconfig' => ['xchan'],
        'abook' => ['abook_xchan'],
        'app' => ['app_author'],
        'chat' => ['chat_xchan'],
        'chatpresence' => ['cp_xchan'],
        'dreport' => ['dreport_recip', 'dreport_xchan'],
        'mail' => ['from_xchan', 'to_xchan'],
        'pgrp_member' => ['xchan'],
        'source' => ['src_channel_xchan', 'src_xchan'],
        'updates' => ['ud_hash'],
        'xchat' => ['xchat_xchan'],
        'xign' => ['xchan'],
        'xlink' => ['xlink_xchan', 'xlink_link'],
        'xprof' => ['xprof_hash'],
        'xtag' => ['xtag_hash']
];

foreach($r as $rr) {

	$zot_xchan = $rr['channel_portable_id'];

	$r = q("SELECT xchan_guid FROM xchan WHERE xchan_hash = '%s' AND xchan_network = 'zot'",
		dbesc($zot_xchan)
	);

	if(!$r) {
		echo '-- ' . $zot_xchan . 'failed: zot xchan not found' . PHP_EOL;
		continue;
	}

	$guid = $r[0]['xchan_guid'];

	$r = q("SELECT xchan_hash, xchan_guid_sig FROM xchan WHERE xchan_guid = '%s' AND xchan_network = 'zot6'",
		dbesc($guid)
	);

	if(!$r) {
		echo '-- ' . $zot_xchan . 'failed: zot6 xchan not found' . PHP_EOL;
		continue;
	}

	$zot[] = $zot_xchan;
	$zot6[] = $r[0]['xchan_hash'];

	echo "-- convering indexed data for " . $rr['channel_name'] . PHP_EOL;
	foreach($core as $table => $cols) {
		foreach($cols as $col) {
			q("UPDATE $table SET $col = '%s' WHERE $col = '%s'",
				dbesc($r[0]['xchan_hash']),
				dbesc($zot_xchan)
			);
		}
	}
}

// columns required whole table scan
$core = [
	'attach' => ['creator', 'allow_cid', 'deny_cid'],
	'channel' => ['channel_allow_cid', 'channel_deny_cid'],
	'chatroom' => ['allow_cid', 'deny_cid'],
	'config' => ['v'],
	'event' => ['event_xchan', 'allow_cid', 'deny_cid'],
	'iconfig' => ['v'],
	'item' => ['owner_xchan', 'author_xchan', 'source_xchan', 'route', 'allow_cid', 'deny_cid'],
	'menu_item' => ['allow_cid', 'deny_cid'],
	'obj' => ['allow_cid', 'deny_cid'],
	'pconfig' => ['v'],
	'photo' => ['xchan', 'allow_cid', 'deny_cid'],
	'xconfig' => ['xchan', 'v']
];

foreach($core as $table => $cols) {

	$fields = implode(", ", $cols);
	$idcol = db_columns($table)[0];

	$curid = 0;
	$maxid = q("SELECT MAX($idcol) AS id FROM $table")[0]['id'];

	$r = q("SELECT $idcol FROM $table WHERE $idcol > $curid ORDER BY $idcol LIMIT 100");

	echo "-- converting $table table data" . PHP_EOL;
	$i = 0;
	while ($curid < $maxid) {
		foreach($r as $rr) {

			$curid = $rr[$idcol];
			$q = '';
			$x = q("SELECT $fields FROM $table WHERE $idcol = $curid")[0];
			foreach($x as $k => $v)
				$q .= (empty($q) ? "UPDATE $table SET " : ", ") . "$k = '" . dbesc(str_replace($zot, $zot6, $x[$k])) . "'";
			$q .= " WHERE $idcol = $curid";
			q("$q");
			$i++;
		}
		echo "." . ($i % 1000 == 0 ? $i : "");
	}
	echo PHP_EOL;
}