<?
// your database server login
$db_server = "localhost";
$db_username = "";
$db_password = "";
$ragnarok_db = "ragnarok";

// comment this line after you made a backup of your database
die("*WARNING* Script will alter your database !!WITHOUT!! confirmation. Please make backup of your game database! Then, comment this line in script and run again.");

// YOU SHOULD **NOT** MODIFY LINES BELOW

set_time_limit(0);

function get_time()
{
	$a = explode (' ',microtime());
	return(double) $a[0] + $a[1];
}

function tolog($str,$terminate=false) {
	print(date("d.m.y H:i:s",time())." : $str\n");
	if ($terminate) die();
}

function sql($query) {
	$q = @mysql_query($query);
	if (!$q) tolog("DB error: ".mysql_error(),true);
	return $q;
}

function remove_invalid_data() {
	$q = sql("DELETE FROM `char` WHERE account_id NOT IN (SELECT account_id FROM `login`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} characters without their accounts.");
	
	$q = sql("DELETE FROM `cart_inventory` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} items in carts which does not belong to anyone.");
	
	$q  = sql("DELETE FROM `friends` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	$q1 = sql("DELETE FROM `friends` WHERE friend_id NOT IN (SELECT char_id FROM `char`);");
	$count += mysql_affected_rows();
	tolog("Removed {$count} invalid friends (non-existent character ID).");
	
	$q = sql("DELETE FROM `global_reg_value` WHERE char_id <>'' AND char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} character variables which does not belong to anyone.");
	
	$q = sql("DELETE FROM `global_reg_value` WHERE account_id <> '' AND account_id NOT IN (SELECT account_id FROM `login`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} account variables which does not belong to anyone.");
	
	$q = sql("DELETE FROM `guild` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} guilds without valid guildmaster.");
	
	$q = sql("DELETE FROM `guild_alliance` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} guild alliances (non-existent guild ID).");
	
	$q = sql("DELETE FROM `guild_expulsion` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} guild expulsion references (non-existent guild ID).");
	
	$q = sql("DELETE FROM `guild_member` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} guild member references (non-existent guild ID).");
	
	$q = sql("DELETE FROM `guild_member` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} guild member references (non-existent character ID).");
	
	$q = sql("DELETE FROM `guild_member` WHERE account_id NOT IN (SELECT account_id FROM `login`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} guild member references (non-existent login ID).");
	
	$q = sql("DELETE FROM `guild_position` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} guild position references (non-existent guild ID).");
	
	$q = sql("DELETE FROM `guild_skill` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} guild skill references (non-existent guild ID).");
	
	$q = sql("DELETE FROM `guild_storage` WHERE guild_id NOT IN (SELECT guild_id FROM `guild`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} items from guild storages (non-existent guild ID).");
	
	$q = sql("DELETE FROM `homunculus` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} homonculuses which does not belong to anyone.");
	
	$q = sql("DELETE FROM `skill_homunculus` WHERE homun_id NOT IN (SELECT homun_id FROM `homunculus`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} homunculus skill references (non-existent homunculus ID).");
	
	$q = sql("DELETE FROM `hotkey` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} hotkey references (non-existent character ID).");
	
	$q = sql("DELETE FROM `inventory` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} items from players inventories which does not belong to anyone.");
	
	$q = sql("DELETE FROM `mail` WHERE dest_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} mails with invalid recipient (non-existent character ID).");
	
	$q = sql("DELETE FROM `memo` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} warp portal memo's (non-existent character ID).");
	
	$q = sql("DELETE FROM `mercenary` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} mercenaries which does not belong to anyone.");
	
	$q = sql("DELETE FROM `mercenary_owner` WHERE merc_id NOT IN (SELECT mer_id FROM `mercenary`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} mercenary references (non-existent mercenary ID).");
	
	$q = sql("DELETE FROM `mercenary_owner` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} mercenary references (non-existent character ID).");
	
	$q = sql("DELETE FROM `party` WHERE leader_char NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} parties. (non-existent party-leader ID).");
	
	$q = sql("DELETE FROM `pet` WHERE char_id<>'' AND account_id<>'' AND char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} pets which does not belong to anyone.");
	
	$q = sql("DELETE FROM `quest` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} quest references (non-existent character ID).");
	
	$q = sql("DELETE FROM `sc_data` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} status change values (non-existent character ID).");
	
	$q = sql("DELETE FROM `skill` WHERE char_id NOT IN (SELECT char_id FROM `char`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} player skill references (non-existent character ID).");
	
	$q = sql("DELETE FROM `storage` WHERE account_id NOT IN (SELECT account_id FROM `login`);");
	$count = mysql_affected_rows();
	tolog("Removed {$count} items from player storages. (non-existent account ID).");
}

function prune_logs() {
	//todo :)
}

$start = get_time();
tolog("Starting...");
if (!@mysql_connect($db_server,$db_username,$db_password)) tolog("Error! Can't connect to database server: ".mysql_error(),true);
if (!@mysql_select_db($ragnarok_db)) tolog("Error: Can't switch to database '{$ragnarok_db}': ".mysql_error(),true);

remove_invalid_data();
prune_logs();

$sec = number_format((get_time() - $start),3);
tolog("Finished at {$sec} seconds.");
?>