#!/usr/bin/php
<?php
########################################################
# this script removes duplicate users from the appdb   #
# it has been tested on my local system but you should #
# still backup your db using:                          #
# mysql_dump -u USERNAME apidb > backup.sql            #
########################################################

# configuration: please put the connection infos here
$hAppdbLink = mysql_connect("localhost", "root", "");
mysql_select_db("apidb");


#the following doesn't need to be changed
$sql = "SELECT email, count(email) AS mycount FROM user_list GROUP BY email ORDER BY mycount DESC";
$result = mysql_query($sql);

while($row=mysql_fetch_array($result)) {
    if($row[mycount] < 2) {
        echo "\nWork done (hopefully)!\n";
        echo "\nNow altering the db to don't let it happen again ;-)...\n";
        mysql_query("ALTER TABLE user_list ADD PRIMARY KEY ('userid')");
        mysql_query("ALTER TABLE user_list ADD UNIQUE ('userid')");
        mysql_query("ALTER TABLE user_list CHANGE 'email' 'email' VARCHAR( 128 ) NOT NULL");
        mysql_query("ALTER TABLE user_list ADD UNIQUE ('email')");
        echo "\nDone!\n";
        echo "\nBye!\n";
        break; // we finished to process all duplicates
    }
    else {
        echo "\nWorking on ".$row[email]." (".$row[mycount]." occurences found)\n";
        fix($row[email]);
    }
}

function fix($email) {
    $sql = "SELECT userid FROM user_list WHERE email='$email' ORDER BY stamp DESC";
    $result = mysql_query($sql);
    $row=mysql_fetch_array($result);
    $userIdToKeep = $row[userid];
    $userIdsToThrow=array();
    while($row=mysql_fetch_array($result)) {
        mysql_query("UPDATE appComments set userId='".$userIdToKeep."' WHERE userId='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE appDataQueue set userId='".$userIdToKeep."' WHERE userId='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE appMaintainerQueue set userId='".$userIdToKeep."' WHERE userId='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE appMaintainers set userId='".$userIdToKeep."' WHERE userId='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE appOwners set ownerId='".$userIdToKeep."' WHERE ownerId='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE appRating set userId='".$userIdToKeep."' WHERE userId='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE appVotes set userId='".$userIdToKeep."' WHERE userId='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE session_list set userid='".$userIdToKeep."' WHERE userid='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE userExperience set userId='".$userIdToKeep."' WHERE userId='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE user_prefs set userid='".$userIdToKeep."' WHERE userid='".$row[userid]."'");
        echo mysql_error();
        mysql_query("UPDATE user_privs set userid='".$userIdToKeep."' WHERE userid='".$row[userid]."'");
        echo mysql_error();
        mysql_query("DELETE FROM user_list WHERE userid='".$row[userid]."'");
        echo mysql_error();        
        echo "Duplicate user ".$row[userid]." deleted...\n";
        echo "Linked data transfered to user ".$userIdToKeep."...\n\n";
    }
}
?>