--
-- CONVERSIONE DB REALMD
TRUNCATE TABLE trinity_auth.account;
INSERT IGNORE INTO trinity_auth.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 on_realmd.account;
TRUNCATE TABLE trinity_auth.realmcharacters;
INSERT IGNORE INTO trinity_auth.realmcharacters SELECT * FROM on_realmd.realmcharacters;
TRUNCATE TABLE trinity_auth.account_access;
INSERT IGNORE INTO trinity_auth.account_access SELECT id,gmlevel,-1 FROM on_realmd.account;

--
-- CONVERSIONE DB CHAR
TRUNCATE TABLE account_data;
INSERT IGNORE INTO account_data SELECT * FROM mangos_characters.account_data;
TRUNCATE TABLE arena_team;
INSERT IGNORE INTO arena_team (arenateamid, name, captainguid, type, rating, seasongames, seasonwins, weekgames, weekwins, rank, backgroundColor, emblemstyle, emblemcolor, borderstyle, bordercolor) SELECT a.arenateamid, a.name, a.captainguid, a.type, b.rating, b.games_season, b.wins_season, b.games_week, b.wins_week, b.rank, a.backgroundColor, a.emblemstyle, a.emblemcolor, a.borderstyle, a.bordercolor FROM mangos_characters.arena_team a, mangos_characters.arena_team_stats b WHERE a.arenateamid = b.arenateamid;
TRUNCATE TABLE arena_team_member;
INSERT IGNORE INTO arena_team_member (arenateamId, guid, weekgames, weekwins, seasongames, seasonwins, personalrating) SELECT arenateamid, guid, played_week, wons_week, played_season, wons_season, personal_rating FROM mangos_characters.arena_team_member;
TRUNCATE TABLE auctionhouse;
INSERT IGNORE INTO auctionhouse (id, auctioneerguid, itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit) SELECT id, 8661, itemguid, itemowner, buyoutprice, time, buyguid, lastbid, startbid, deposit FROM mangos_characters.auction;
TRUNCATE TABLE character_account_data;
INSERT IGNORE INTO character_account_data SELECT * FROM mangos_characters.character_account_data;
TRUNCATE TABLE character_achievement;
INSERT IGNORE INTO character_achievement SELECT * FROM mangos_characters.character_achievement; 
TRUNCATE TABLE character_achievement_progress;
INSERT IGNORE INTO character_achievement_progress SELECT * FROM mangos_characters.character_achievement_progress;
TRUNCATE TABLE character_action;
INSERT IGNORE INTO character_action SELECT * FROM mangos_characters.character_action; 
TRUNCATE TABLE character_battleground_data;
INSERT IGNORE INTO character_battleground_data (guid, instanceid, team, joinx, joiny, joinz, joino, joinmapid, taxistart, taxiend, mountspell) SELECT guid, instance_id, team, join_x, join_y, join_z, join_o, join_map, taxi_start, taxi_end, mount_spell FROM mangos_characters.character_battleground_data;
TRUNCATE TABLE character_battleground_random;
INSERT IGNORE INTO character_battleground_random SELECT * FROM mangos_characters.character_battleground_random; 
TRUNCATE TABLE character_equipmentsets;
INSERT IGNORE INTO character_equipmentsets (guid, setguid, setindex, name, iconname, item0, item1, item2, item3, item4, item5, item6, item7, item8, item9, item10, item11, item12, item13, item14, item15, item16, item17, item18) SELECT guid, setguid, setindex, name, iconname, item0, item1, item2, item3, item4, item5, item6, item7, item8, item9, item10, item11, item12, item13, item14, item15, item16, item17, item18 FROM mangos_characters.character_equipmentsets;
TRUNCATE TABLE character_gifts;
INSERT IGNORE INTO character_gifts SELECT * FROM mangos_characters.character_gifts;
TRUNCATE TABLE character_glyphs;
INSERT IGNORE INTO character_glyphs (guid, spec) SELECT guid, spec FROM mangos_characters.character_glyphs GROUP BY guid, spec;
UPDATE character_glyphs a SET glyph1 = (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 0) WHERE EXISTS (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 0);
UPDATE character_glyphs a SET glyph2 = (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 1) WHERE EXISTS (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 1);
UPDATE character_glyphs a SET glyph3 = (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 2) WHERE EXISTS (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 2);
UPDATE character_glyphs a SET glyph4 = (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 3) WHERE EXISTS (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 3);
UPDATE character_glyphs a SET glyph5 = (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 4) WHERE EXISTS (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 4);
UPDATE character_glyphs a SET glyph6 = (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 5) WHERE EXISTS (SELECT glyph FROM mangos_characters.character_glyphs b WHERE a.guid = b.guid and b.spec = a.spec and slot = 5);
TRUNCATE TABLE character_homebind;
INSERT IGNORE INTO character_homebind SELECT * FROM mangos_characters.character_homebind;
ALTER TABLE character_inventory ENGINE=MyISAM;
TRUNCATE TABLE character_inventory;
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory;
/* SE LA QUERY SOPRA VA IN TIMEOUT, SI PUO SPEZZARE IN TANTE MINIQUERY CON LIMIT (SE CI SONO PIU DI 4000000 DI ENTRY VANNO AGGIUNTE QUERY)
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 0, 250000;
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 250000, 500000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 500000, 750000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 750000, 1000000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 1250000, 1500000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 1500000, 1750000;
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 1750000, 2000000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 2250000, 2500000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 2500000, 2750000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 2750000, 3000000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 3000000, 3250000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 3250000, 3500000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 3500000, 3750000; 
INSERT IGNORE INTO character_inventory (guid, bag, slot, item) SELECT guid, bag, slot, item FROM mangos_characters.character_inventory LIMIT 3750000, 4000000;
*/
ALTER TABLE character_inventory ENGINE=InnoDB;
TRUNCATE TABLE character_pet;
INSERT IGNORE INTO character_pet (id, entry, owner, modelid, createdbyspell, pettype, level, exp, reactstate, name, renamed, slot, curhealth, curmana, curhappiness, savetime, abdata) SELECT id, entry, owner, modelid, createdbyspell, pettype, level, exp, reactstate, name, renamed, slot, curhealth, curmana, curhappiness, savetime, abdata FROM mangos_characters.character_pet;
ALTER TABLE character_queststatus ENGINE=MyISAM;
TRUNCATE TABLE character_queststatus;
INSERT IGNORE INTO character_queststatus (guid, quest, status, explored, timer, mobcount1, mobcount2, mobcount3, mobcount4, itemcount1, itemcount2, itemcount3, itemcount4, playercount) SELECT guid, quest, status, explored, timer, mobcount1, mobcount2, mobcount3, mobcount4, itemcount1, itemcount2, itemcount3, itemcount4, 0 FROM mangos_characters.character_queststatus WHERE rewarded = 0;
TRUNCATE TABLE character_queststatus_rewarded;
INSERT IGNORE INTO character_queststatus_rewarded (guid, quest) SELECT guid, quest FROM mangos_characters.character_queststatus WHERE rewarded = 1;
ALTER TABLE character_queststatus ENGINE=InnoDB;
TRUNCATE TABLE character_queststatus_daily;
INSERT IGNORE INTO character_queststatus_daily (guid,quest) SELECT guid, quest FROM mangos_characters.character_queststatus_daily;
TRUNCATE TABLE character_queststatus_weekly;
INSERT IGNORE INTO character_queststatus_weekly SELECT * FROM mangos_characters.character_queststatus_weekly; 
TRUNCATE TABLE character_queststatus_monthly;
INSERT IGNORE INTO character_queststatus_seasonal(guid,quest) SELECT guid, quest FROM mangos_characters.character_queststatus_monthly; 
TRUNCATE TABLE character_reputation;
INSERT IGNORE INTO character_reputation SELECT * FROM mangos_characters.character_reputation;
TRUNCATE TABLE character_skills;
INSERT IGNORE INTO character_skills SELECT * FROM mangos_characters.character_skills;
TRUNCATE TABLE character_social;
INSERT IGNORE INTO character_social SELECT * FROM mangos_characters.character_social;
TRUNCATE TABLE character_spell;
INSERT IGNORE INTO character_spell SELECT * FROM mangos_characters.character_spell; 
TRUNCATE TABLE character_spell_cooldown;
INSERT IGNORE INTO character_spell_cooldown SELECT * FROM mangos_characters.character_spell_cooldown; 
TRUNCATE TABLE character_stats;
INSERT IGNORE INTO character_stats (guid, maxhealth, maxpower1, maxpower2, maxpower3, maxpower4, maxpower5, maxpower6, maxpower7, strength, agility, stamina, intellect, spirit, armor, resholy, resfire, resnature, resfrost, resshadow, resarcane, blockpct, dodgepct, parrypct, critpct, rangedcritpct, spellcritpct, attackpower, rangedattackpower, spellpower, resilience) SELECT guid, maxhealth, maxpower1, maxpower2, maxpower3, maxpower4, maxpower5, maxpower6, maxpower7, strength, agility, stamina, intellect, spirit, armor, resholy, resfire, resnature, resfrost, resshadow, resarcane, blockpct, dodgepct, parrypct, critpct, rangedcritpct, spellcritpct, attackpower, rangedattackpower, spellpower, resilience FROM mangos_characters.character_stats;
TRUNCATE TABLE guild;
INSERT IGNORE INTO guild SELECT * FROM mangos_characters.guild; 
TRUNCATE TABLE guild_bank_eventlog;
INSERT IGNORE INTO guild_bank_eventlog SELECT * FROM mangos_characters.guild_bank_eventlog; 
TRUNCATE TABLE guild_bank_item;
INSERT IGNORE INTO guild_bank_item (guildid, tabid, slotid, item_guid) SELECT guildid, tabid, slotid, item_guid FROM mangos_characters.guild_bank_item; 
TRUNCATE TABLE guild_bank_right;
INSERT IGNORE INTO guild_bank_right SELECT * FROM mangos_characters.guild_bank_right; 
TRUNCATE TABLE guild_bank_tab;
INSERT IGNORE INTO guild_bank_tab SELECT * FROM mangos_characters.guild_bank_tab; 
TRUNCATE TABLE guild_eventlog;
INSERT IGNORE INTO guild_eventlog SELECT * FROM mangos_characters.guild_eventlog; 
TRUNCATE TABLE guild_member;
INSERT IGNORE INTO guild_member SELECT * FROM mangos_characters.guild_member;
TRUNCATE TABLE guild_rank;
INSERT IGNORE INTO guild_rank SELECT * FROM mangos_characters.guild_rank; 
ALTER TABLE item_instance ENGINE=MyISAM;
TRUNCATE TABLE item_instance;
ALTER TABLE item_instance ADD COLUMN tmp longtext default '' AFTER text;
INSERT IGNORE INTO item_instance (guid, owner_guid, text, tmp) SELECT guid, owner_guid, text, data FROM mangos_characters.item_instance;
UPDATE item_instance ii, mangos_characters.auction ah SET ii.itemEntry = ah.item_template WHERE ii.guid = ah.itemguid;
UPDATE item_instance ii, mangos_characters.character_inventory ci SET ii.itemEntry = ci.item_template WHERE ii.guid = ci.item;
UPDATE item_instance ii, mangos_characters.guild_bank_item gbi SET ii.itemEntry = gbi.item_entry WHERE ii.guid = gbi.item_guid;
UPDATE item_instance ii, mangos_characters.mail_items mi SET ii.itemEntry = mi.item_template WHERE ii.guid = mi.item_guid;
DELIMITER ||
DROP FUNCTION IF EXISTS uint32toint32||
CREATE FUNCTION uint32toint32(input INT(10) UNSIGNED) RETURNS INT(11) SIGNED DETERMINISTIC
BEGIN
  RETURN input;
END||
DELIMITER ;
UPDATE item_instance ii SET creatorGuid = SUBSTRING(tmp, length(SUBSTRING_INDEX(tmp,' ',10))+2, length(SUBSTRING_INDEX(tmp,' ',10+1))-length(SUBSTRING_INDEX(tmp,' ',10))-1),
giftCreatorGuid = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',12))+2,length(SUBSTRING_INDEX(tmp,' ',12+1))-length(SUBSTRING_INDEX(tmp,' ',12))-1),
count = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',14))+2,length(SUBSTRING_INDEX(tmp,' ',14+1))-length(SUBSTRING_INDEX(tmp,' ',14))-1),
duration = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',15))+2,length(SUBSTRING_INDEX(tmp,' ',15+1))-length(SUBSTRING_INDEX(tmp,' ',15))-1),
charges = CONCAT_WS(' ',uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',16))+2,length(SUBSTRING_INDEX(tmp,' ',16+1))-length(SUBSTRING_INDEX(tmp,' ',16))-1)),uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',17))+2,length(SUBSTRING_INDEX(tmp,' ',17+1))-length(SUBSTRING_INDEX(tmp,' ',17))-1)),uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',18))+2,length(SUBSTRING_INDEX(tmp,' ',18+1))-length(SUBSTRING_INDEX(tmp,' ',18))-1)),uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',19))+2,length(SUBSTRING_INDEX(tmp,' ',19+1))-length(SUBSTRING_INDEX(tmp,' ',19))-1)),uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',20))+2,length(SUBSTRING_INDEX(tmp,' ',20+1))-length(SUBSTRING_INDEX(tmp,' ',20))-1))),
flags = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',21))+2,length(SUBSTRING_INDEX(tmp,' ',21+1))-length(SUBSTRING_INDEX(tmp,' ',21))-1),
enchantments = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',22))+2,length(SUBSTRING_INDEX(tmp,' ',57+1))-length(SUBSTRING_INDEX(tmp,' ',22))-1),
randomPropertyId = uint32toint32(SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',59))+2,length(SUBSTRING_INDEX(tmp,' ',59+1))-length(SUBSTRING_INDEX(tmp,' ',59))-1)),
durability = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',60))+2,length(SUBSTRING_INDEX(tmp,' ',60+1))-length(SUBSTRING_INDEX(tmp,' ',60))-1),
playedTime = SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',62))+2,length(SUBSTRING_INDEX(tmp,' ',62+1))-length(SUBSTRING_INDEX(tmp,' ',62))-1);
DROP FUNCTION IF EXISTS uint32toint32;
UPDATE item_instance ii SET flags = flags &~0x8 WHERE
SUBSTRING(tmp,length(SUBSTRING_INDEX(tmp,' ',3))+2,length(SUBSTRING_INDEX(tmp,' ',3+1))-length(SUBSTRING_INDEX(tmp,' ',3))-1)NOT IN (5043,5044,17302,17305,17308,21831);
ALTER TABLE item_instance DROP COLUMN tmp;
SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;
UPDATE item_instance SET flags = (flags & @allowedFlags);
ALTER TABLE item_instance ENGINE=InnoDB;
TRUNCATE TABLE mail;
INSERT IGNORE INTO mail SELECT * FROM mangos_characters.mail; 
TRUNCATE TABLE mail_items;
INSERT IGNORE INTO mail_items (mail_id, item_guid, receiver) SELECT mail_id, item_guid, receiver FROM mangos_characters.mail_items;
TRUNCATE TABLE pet_spell;
INSERT IGNORE INTO pet_spell (guid, spell, active) SELECT guid, spell, active FROM mangos_characters.pet_spell GROUP BY guid, spell;
TRUNCATE TABLE pet_spell_cooldown;
INSERT IGNORE INTO pet_spell_cooldown SELECT * FROM mangos_characters.pet_spell_cooldown; 
TRUNCATE TABLE characters;
INSERT IGNORE INTO characters (guid, account, name, race, class, gender, level, xp, money, playerbytes, playerbytes2, playerflags, position_x, position_y, position_z, map, instance_id, instance_mode_mask, 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, latency, speccount, activespec, exploredzones, equipmentcache, ammoid, knowntitles, actionbars, grantablelevels, deleteinfos_account, deleteinfos_name, deletedate) SELECT guid, account, name, race, class, gender, level, xp, money, playerbytes, playerbytes2, playerflags, position_x, position_y, position_z, map, 0, 0, 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, 0, speccount, activespec, exploredzones, equipmentcache, ammoid, knowntitles, actionbars, grantablelevels, deleteinfos_account, deleteinfos_name, deletedate FROM mangos_characters.characters;
UPDATE characters SET at_login = at_login|4;
--


--
-- INFINE AGGIORNIAMO IL REALMD CON IL NUMERO DEI PG PER OGNI ACCOUNT
UPDATE trinity_auth.realmcharacters a SET numchars = (SELECT COUNT(guid) FROM trinity_characters.characters WHERE account = a.acctid);
--
-- FINE