#!/bin/sh
# This script remove user who haven't ever played to any xtribe game (probably spammer)
# The Select query exclude users who played at least once, the anonymus user (uid 0), the main admin user (uid), every other
# administrator (rid=3) and every researcher (rid=5)

op_line="--------------------------------------\n"
close_line="\n--------------------------------------"
exit_cause(){
	echo "$op_line $1 $close_line" ; 
	exit 1;
}

# Get user database data
echo -n "Database name: "
read db_name < /dev/tty
echo -n "Database user: "
read db_usr < /dev/tty
echo -n "Database user password: "
read db_urs_pwd < /dev/tty
cd /var/www/xtribe.eu/drupal

# Get user confirm to proceed
echo -n "$op_line"
sudo mysql -u $db_usr -p$db_urs_pwd -D $db_name -e "SELECT name FROM users WHERE uid NOT IN (SELECT DISTINCT (u.uid) FROM ets_user_stats s, users u WHERE s.uid = u.uid) AND uid NOT IN(SELECT uid FROM users_roles WHERE rid=5 OR rid=3) AND uid!=0 AND uid!=1" | while read name; do
echo $name
echo -n " " 
done
echo "$op_line All data related to users listed above, who have never played to Xtribe games, are going to be removed from drupal installation. This action cannot be undone. Are you sure you want to continue? (y/n) "
read clean_confirm < /dev/tty
while [ "$clean_confirm" != "y" ] && [ "$clean_confirm" != "n" ]; do
	echo -n "--- Please reply with a single characther: 'y' or 'n': "
	read clean_confirm < /dev/tty
done
if [ "$clean_confirm" = "n" ]; then
	exit_cause "Clean interrupted by the user."
fi

# Removing data
sudo mysql -u $db_usr -p$db_urs_pwd -D $db_name -e "SELECT name FROM users WHERE uid NOT IN (SELECT DISTINCT (u.uid) FROM ets_user_stats s, users u WHERE s.uid = u.uid) AND uid NOT IN(SELECT uid FROM users_roles WHERE rid=5 OR rid=3) AND uid!=0 AND uid!=1" | while read name; do
drush user-cancel -y $name 
done

echo "$op_line Cleaning succesfully completed. $close_line"