-- USE realmd;

-- REALM TRANSFER QUERIES
-- INSERT INTO realmd.account (id,username,sha_pass_hash,sessionkey,v,s,email,joindate,last_ip,failed_logins,locked,last_login,expansion,mutetime,locale) SELECT id,username,sha_pass_hash,sessionkey,v,s,email,joindate,last_ip,failed_logins,locked,last_login,expansion,mutetime,locale FROM tc_realm.account;
-- INSERT INTO realmd.realmcharacters SELECT * FROM tc_realm.realmcharacters;
-- INSERT INTO realmd.account_access SELECT id,gmlevel,active_realm_id FROM tc_realm.account;

USE characters;

-- CHARACTERS TRANSFER QUERIES
INSERT INTO account_data SELECT * FROM tc_characters.account_data;
INSERT INTO arena_team SELECT * FROM tc_characters.arena_team;
INSERT INTO arena_team_member SELECT * FROM tc_characters.arena_team_member;
INSERT INTO arena_team_stats SELECT * FROM tc_characters.arena_team_stats;
INSERT INTO auction SELECT * FROM tc_characters.auctionhouse;
UPDATE auction SET houseid = 7;
INSERT INTO character_account_data SELECT * FROM tc_characters.character_account_data;
INSERT INTO character_achievement SELECT * FROM tc_characters.character_achievement;
INSERT INTO character_achievement_progress SELECT * FROM tc_characters.character_achievement_progress;
INSERT INTO character_action SELECT * FROM tc_characters.character_action;
-- character_aura -- this table is filled with buffs and similar spells, it can be dropped
INSERT INTO character_battleground_data SELECT * FROM tc_characters.character_battleground_data;
INSERT INTO character_battleground_random SELECT * FROM tc_characters.character_battleground_random;
INSERT INTO character_equipmentsets SELECT * FROM tc_characters.character_equipmentsets;
INSERT INTO character_gifts SELECT * FROM tc_characters.character_gifts;

INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 0, glyph1 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 0 AND glyph1 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 1, glyph2 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 0 AND glyph2 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 2, glyph3 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 0 AND glyph3 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 3, glyph4 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 0 AND glyph4 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 4, glyph5 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 0 AND glyph5 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 5, glyph6 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 0 AND glyph6 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 0, glyph1 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 1 AND glyph1 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 1, glyph2 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 1 AND glyph2 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 2, glyph3 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 1 AND glyph3 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 3, glyph4 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 1 AND glyph4 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 4, glyph5 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 1 AND glyph5 <> 0;
INSERT INTO character_glyphs (guid, spec, slot, glyph) SELECT guid, spec, 5, glyph6 AS glyph  FROM tc_characters.character_glyphs WHERE  spec = 1 AND glyph6 <> 0;

INSERT INTO character_homebind SELECT * FROM tc_characters.character_homebind;
INSERT INTO character_instance SELECT * FROM tc_characters.character_instance;
INSERT INTO character_inventory SELECT * FROM tc_characters.character_inventory;
INSERT INTO character_pet SELECT * FROM tc_characters.character_pet;
INSERT INTO character_queststatus SELECT * FROM tc_characters.character_queststatus;
INSERT INTO character_queststatus_daily (guid,quest) SELECT guid, quest FROM tc_characters.character_queststatus_daily;
INSERT INTO character_queststatus_weekly SELECT * FROM tc_characters.character_queststatus_weekly;
INSERT INTO character_reputation SELECT * FROM tc_characters.character_reputation;
INSERT INTO character_skills SELECT * FROM tc_characters.character_skills;
INSERT INTO character_social SELECT * FROM tc_characters.character_social;
INSERT INTO character_spell SELECT * FROM tc_characters.character_spell;
INSERT INTO character_spell_cooldown SELECT * FROM tc_characters.character_spell_cooldown;
INSERT INTO character_stats SELECT * FROM tc_characters.character_stats;
-- character_talent -- talents will be reseted, sry
-- character_ticket -- tickets will differ alot, new table is now gm_ticket
INSERT INTO character_tutorial SELECT * FROM tc_characters.character_tutorial;
-- corpse -- skip this table, revive all characters
-- group_instance -- skip
-- group_member -- skip
-- group -- skip
INSERT INTO guild SELECT * FROM tc_characters.guild;
INSERT INTO guild_bank_eventlog SELECT * FROM tc_characters.guild_bank_eventlog;
INSERT INTO guild_bank_item SELECT * FROM tc_characters.guild_bank_item;
INSERT INTO guild_bank_right SELECT * FROM tc_characters.guild_bank_right;
INSERT INTO guild_bank_tab SELECT * FROM tc_characters.guild_bank_tab;
INSERT INTO guild_eventlog SELECT * FROM tc_characters.guild_eventlog;
INSERT INTO guild_member SELECT * FROM tc_characters.guild_member;
INSERT INTO guild_rank SELECT * FROM tc_characters.guild_rank;
-- instance -- skip
-- instance_reset -- skip
INSERT INTO item_instance SELECT * FROM tc_characters.item_instance;
INSERT INTO mail SELECT * FROM tc_characters.mail;
INSERT INTO mail_items SELECT * FROM tc_characters.mail_items;
-- pet_aura -- skip
INSERT INTO pet_spell SELECT * FROM tc_characters.pet_spell;
INSERT INTO pet_spell_cooldown SELECT * FROM tc_characters.pet_spell_cooldown;
INSERT INTO characters (guid, account, name, race, class, gender, level, xp, money, playerBytes, playerBytes2, playerFlags, position_x, position_y, position_z, map, dungeon_difficulty, orientation, taximask, online, cinematic, totaltime, leveltime, logout_time, is_logout_resting, rest_bonus, resettalents_cost, resettalents_time, trans_x, trans_y, trans_z, trans_o, transguid, extra_flags, stable_slots, at_login, zone, death_expire_time, taxi_path, arenaPoints, totalHonorPoints, todayHonorPoints, yesterdayHonorPoints, totalKills, todayKills, yesterdayKills, chosenTitle, knownCurrencies, watchedFaction, drunk, health, power1, power2, power3, power4, power5, power6, power7, specCount, activeSpec, exploredZones, equipmentCache, ammoId, knownTitles, actionBars)
 SELECT guid, account, name, race, class, gender, level, xp, money, playerBytes, playerBytes2, playerFlags, position_x, position_y, position_z, map, dungeon_difficulty, orientation, taximask, online, cinematic, totaltime, leveltime, logout_time, is_logout_resting, rest_bonus, resettalents_cost, resettalents_time, trans_x, trans_y, trans_z, trans_o, transguid, extra_flags, stable_slots, at_login, zone, death_expire_time, taxi_path, arenaPoints, totalHonorPoints, todayHonorPoints, yesterdayHonorPoints, totalKills, todayKills, yesterdayKills, chosenTitle, knownCurrencies, watchedFaction, drunk, health, power1, power2, power3, power4, power5, power6, power7, specCount, activeSpec, exploredZones, equipmentCache, ammoId, knownTitles, actionBars FROM tc_characters.characters;

UPDATE realmd.realmcharacters a SET numchars = (SELECT COUNT(guid) FROM characters WHERE account = a.acctid);