START TRANSACTION;
use mysql;


/* select characters to keep*/
CREATE table to_keep AS
(
SELECT
  guid
FROM acore_characters.characters
WHERE account IN (
  SELECT
    id
  FROM acore_auth.account
  WHERE username not LIKE 'RNDBOT%'
));

/* select characters PETS to keep*/
CREATE table pets_to_keep AS
(
SELECT
  owner
FROM acore_characters.character_pet
WHERE owner IN (
  SELECT
    guid
  FROM to_keep
));

/* tables with custom logic*/
DELETE
FROM acore_playerbots.playerbots_random_bots;
UPDATE acore_characters.playerbots_names
SET in_use=0;
DELETE
FROM acore_auth.account
  WHERE username LIKE 'RNDBOT%';

/* tables with character.guid column named differently*/
DELETE
FROM acore_characters.character_pet
WHERE owner not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.groups
WHERE leaderGuid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.group_member
WHERE memberGuid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.item_instance
WHERE owner_guid not IN(SELECT * FROM to_keep) AND owner_guid <> 0;

/* tables with both guid and casterGuid */
/* We delete pet auras when they target a bot or were cast by a bot */
DELETE
FROM acore_characters.character_aura
WHERE guid not IN(SELECT * FROM to_keep); 
DELETE
FROM acore_characters.character_aura
WHERE casterGuid not IN(SELECT * FROM to_keep); /* We delete pet auras when they target a bot or were cast by a bot */

DELETE
FROM acore_characters.pet_aura
WHERE guid not IN(SELECT * FROM to_keep); /* We delete pet auras when they target a bot or were cast by a bot */
DELETE
FROM acore_characters.pet_aura
WHERE casterGuid not IN(SELECT * FROM to_keep); /* We delete pet auras when they target a bot or were cast by a bot */

/* tables with standard character.guid column*/
DELETE
FROM acore_characters.character_skills
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_account_data
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_achievement
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_achievement_progress
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_action
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_glyphs
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_homebind
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_inventory
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_queststatus
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_queststatus_rewarded
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_reputation
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_social
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_spell
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_spell_cooldown
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.character_talent
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.corpse
WHERE guid not IN(SELECT * FROM to_keep);
DELETE
FROM acore_characters.characters
WHERE guid not IN(SELECT * FROM to_keep);

/* tables using pets.guid instead */
DELETE
FROM acore_characters.pet_spell
WHERE guid not IN(SELECT * FROM pets_to_keep);
DELETE
FROM acore_characters.pet_spell_cooldown
WHERE guid not IN(SELECT * FROM pets_to_keep);

/* clean up view after usage*/
DROP table to_keep;
DROP table pets_to_keep;

COMMIT;
