-- -------------------------- --
-- *** RealmD START *** --
-- -------------------------- --

-- Delete all accounts which has not been online for some period of time
DELETE FROM `realmd`.`account` WHERE `last_login` < '2007-02-11 00:00:00';

-- Delete accounts with no players (for 1 realm)
delete FROM `realmd`.`account` where `id` NOT IN (SELECT `account` FROM `characters`.`characters`);

-- -------------------------- --
-- *** RealmD END *** --
-- -------------------------- --

-- -------------------------- --
-- *** Char OPTIONS START *** --
-- -------------------------- --

-- *** delete orphan players ***
DELETE FROM `characters` WHERE `account` NOT IN (SELECT `id` FROM `realmd`.`account`);

-- *** delete players with playtime < 2 hours ***
DELETE FROM `characters` WHERE `totaltime` < 7200;

-- -------------------------- --
-- *** Char OPTIONS END *** --
-- -------------------------- --

TRUNCATE TABLE `groups`;
TRUNCATE TABLE `group_member`;

TRUNCATE TABLE `corpse`;

DELETE FROM `character_action` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_aura` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_gifts` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_homebind` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);

DELETE FROM `character_instance` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_instance` WHERE `leader` NOT IN (SELECT `guid` FROM `characters`);

DELETE FROM `character_inventory` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);

DELETE FROM `character_kill` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_kill` WHERE `victim_guid` NOT IN (SELECT `guid` FROM `characters`);

DELETE FROM `character_pet` WHERE `owner` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `pet_spell` WHERE `guid` NOT IN (SELECT `id` FROM `character_pet`);
DELETE FROM `pet_spell_cooldown` WHERE `guid` NOT IN (SELECT `id` FROM `character_pet`);

DELETE FROM `character_queststatus` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_queststatus_daily` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);

DELETE FROM `character_reputation` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);

DELETE FROM `character_social` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_social` WHERE `friend` NOT IN (SELECT `guid` FROM `characters`);

DELETE FROM `character_spell` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_spell_cooldown` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_ticket` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `character_tutorial` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);

DELETE FROM `item_instance` WHERE `owner_guid` NOT IN (SELECT `guid` FROM `characters`) AND `owner_guid`!=0;

DELETE FROM `mail` WHERE `receiver` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `mail` WHERE `sender` NOT IN (SELECT `guid` FROM `characters`);


DROP TABLE IF EXISTS `tmp`;
CREATE TABLE `tmp` (`id` int(11) unsigned NOT NULL default '0') ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `tmp` select SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ' , 58), ' ' , -1) as `id` from `item_instance` where SUBSTRING_INDEX(SUBSTRING_INDEX(`data`, ' ' , 58), ' ' , -1) > 0;
delete from `item_text` where `id` not in (select `id` from `tmp`) and `id` not in (select `itemtextid` from `mail`);
DROP TABLE IF EXISTS `tmp`;


-- DELETE FROM `guild` WHERE `leaderguid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `guild_member` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `guild` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild_member`);
DELETE FROM `guild_member` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
DELETE FROM `guild_rank` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);

DELETE FROM `guild_bank_eventlog` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
DELETE FROM `guild_bank_item` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
DELETE FROM `guild_bank_right` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);
DELETE FROM `guild_bank_tab` WHERE `guildid` NOT IN (SELECT `guildid` FROM `guild`);


-- DELETE FROM `arena_team` WHERE `captainguid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `arena_team_member` WHERE `guid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `arena_team` WHERE `arenateamid` NOT IN (SELECT `arenateamid` FROM `arena_team_member`);
DELETE FROM `arena_team_member` WHERE `arenateamid` NOT IN (SELECT `arenateamid` FROM `arena_team`);
DELETE FROM `arena_team_stats` WHERE `arenateamid` NOT IN (SELECT `arenateamid` FROM `arena_team`);

DELETE FROM `petition` WHERE `ownerguid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `petition_sign` WHERE `ownerguid` NOT IN (SELECT `guid` FROM `characters`);
DELETE FROM `petition_sign` WHERE `playerguid` NOT IN (SELECT `guid` FROM `characters`);

-- PORT all players to their start location

update `characters` set position_x=-8922.6, position_y=-121.2,position_z=84,map=0,orientation=2.098584,trans_x=0,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='1';
update `characters` set position_x=-599.5, position_y=-4230,position_z=39,map=1,orientation=4.530962,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='2';
update `characters` set position_x=-6220, position_y=331,position_z=385,map=0,orientation=2.906981,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='3';
update `characters` set position_x=10383, position_y=754,position_z=1325,map=1,orientation=3.860233,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='4';
update `characters` set position_x=1861.1, position_y=1587.8,position_z=94,map=0,orientation=6.209361,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='5';
update `characters` set position_x=-2997.3, position_y=-283.5,position_z=54,map=1,orientation=5.087811,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='6';
update `characters` set position_x=-6220, position_y=331,position_z=385,map=0,orientation=2.906981,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='7';
update `characters` set position_x=-599.5, position_y=-4230,position_z=39,map=1,orientation=4.530962,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='8';
update `characters` set position_x=10349, position_y=-6356,position_z=35,map=530,orientation=1.38,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='10';
update `characters` set position_x=-3961.63, position_y=-13931.2,position_z=101,map=530,orientation=0.00,trans_y=0,trans_z=0,trans_o=0,taxi_path=NULL where `race`='11';