#!/bin/bash


USER=""
PASS=""
SERVER=""
DB=""


#-- Query delete (account più vecchi di 150 giorni)


#-- cancella account mai loggati

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from realmd.account where datediff(curdate(),date(last_login))>150;"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from realmd.account where datediff(curdate(),date(joindate))>150 AND last_login ='0000-00-00';"



#-- Coerenza reami

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from realmd.account_access where id not in (select id from realmd.account);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from realmd.realmcharacters where acctid not in (select id from realmd.account);"



#-- Coerenza char

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from characters where account not in (select id from realmd.account) and account>0;"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from account_data where account not in (select id from realmd.account) and account>0;"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from arena_team where captainguid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from arena_team_member where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from arena_team_member where arenateamid not in (select arenateamid from arena_team);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from arena_team_stats where arenateamid not in (select arenateamid from arena_team);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_account_data where guid not in (select guid from characters);"



#-- Realm First Level 80  Realm First Raid Achievements Realm First Northen Vanguard + Raid Achievement

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_achievement WHERE guid not in (select guid from characters) AND achievement NOT BETWEEN '456' AND '467' AND achievement NOT BETWEEN '1400' AND '1427' AND achievement NOT IN(1463, 3117, 3259);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_achievement_progress where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_action where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_aura where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_aura where caster_guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_battleground_data where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_declinedname where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_equipmentsets where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_gifts where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_glyphs where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_homebind where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_instance where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_inventory where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_pet where owner not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_pet_declinedname where owner not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_queststatus where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_queststatus_daily where guid not in (select guid from characters);"



#-- mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_queststatus_weekly where guid not in (select guid from characters);"   <-- non esistente su 3.1.3



mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_reputation where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_skills where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_social where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_spell where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_spell_cooldown where guid not in (select guid from characters);"



#-- mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_stats where guid not in (select guid from characters);"     <-- non esistente su 3.1.3



mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_talent where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from character_tutorial where account not in (select id from realmd.account) and account>0;"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from corpse where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from guild_member where guid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from group_instance WHERE leaderGuid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from groups WHERE leaderGuid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from group_member WHERE leaderGuid not in (select guid from characters) OR memberGuid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from gm_tickets WHERE playerGuid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from item_instance where owner_guid not in (select guid from characters);"



#-- mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from item_refund_instance where player_guid not in (select guid from characters);"     <-- non esistente su 3.1.3



mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from mail where sender not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from mail where receiver not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from mail_items where mail_id not in (select id from mail);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from pet_aura where guid not in (select id from character_pet);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from pet_spell where guid not in (select id from character_pet);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from pet_spell_cooldown where guid not in (select id from character_pet);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from petition where ownerguid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from petition_sign where ownerguid not in (select guid from characters);"

mysql -u $USER -p$PASS -h $SERVER -D $DB -e "delete from petition_sign where playerguid not in (select guid from characters);"