USE characters;

-- CHARACTERS TRANSFER QUERIES
INSERT INTO account_data SELECT * FROM ptr_characters.account_data;
-- addons -- TC specific table, will be filled by TC
INSERT INTO arena_team SELECT * FROM ptr_characters.arena_team;
INSERT INTO arena_team_member (arenateamid, guid, played_week, wons_week, played_season, wons_season) SELECT arenateamid, guid, played_week, wons_week, played_season, wons_season FROM ptr_characters.arena_team_member;
INSERT INTO arena_team_stats SELECT * FROM ptr_characters.arena_team_stats;
INSERT INTO character_account_data SELECT * FROM ptr_characters.character_account_data;
INSERT INTO character_achievement SELECT * FROM ptr_characters.character_achievement;
INSERT INTO character_achievement_progress SELECT * FROM ptr_characters.character_achievement_progress;
INSERT INTO character_action SELECT * FROM ptr_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 ptr_characters.character_battleground_data;
INSERT INTO character_declinedname SELECT * FROM ptr_characters.character_declinedname;
INSERT INTO character_equipmentsets SELECT * FROM ptr_characters.character_equipmentsets;
INSERT INTO character_gifts SELECT * FROM ptr_characters.character_gifts;
INSERT INTO character_glyphs (guid, spec) SELECT  DISTINCT guid, spec FROM ptr_characters.character_glyphs;
UPDATE character_glyphs a SET glyph1 = (SELECT glyph FROM ptr_characters.character_glyphs WHERE guid = a.guid AND spec  = a.spec AND slot = 0);
UPDATE character_glyphs a SET glyph2 = (SELECT glyph FROM ptr_characters.character_glyphs WHERE guid = a.guid AND spec  = a.spec AND slot = 1);
UPDATE character_glyphs a SET glyph3 = (SELECT glyph FROM ptr_characters.character_glyphs WHERE guid = a.guid AND spec  = a.spec AND slot = 2);
UPDATE character_glyphs a SET glyph4 = (SELECT glyph FROM ptr_characters.character_glyphs WHERE guid = a.guid AND spec  = a.spec AND slot = 3);
UPDATE character_glyphs a SET glyph5 = (SELECT glyph FROM ptr_characters.character_glyphs WHERE guid = a.guid AND spec  = a.spec AND slot = 4);
UPDATE character_glyphs a SET glyph6 = (SELECT glyph FROM ptr_characters.character_glyphs WHERE guid = a.guid AND spec  = a.spec AND slot = 5);
INSERT INTO character_homebind SELECT * FROM ptr_characters.character_homebind;
INSERT INTO character_instance SELECT * FROM ptr_characters.character_instance;
INSERT INTO character_inventory SELECT * FROM ptr_characters.character_inventory;
INSERT INTO character_pet SELECT * FROM ptr_characters.character_pet;
INSERT INTO character_pet_declinedname SELECT * FROM ptr_characters.character_pet_declinedname;
INSERT INTO character_queststatus SELECT * FROM ptr_characters.character_queststatus;
INSERT INTO character_queststatus_daily (guid,quest) SELECT * FROM ptr_characters.character_queststatus_daily; -- ?
INSERT INTO character_queststatus_weekly SELECT * FROM ptr_characters.character_queststatus_weekly;
INSERT INTO character_reputation SELECT * FROM ptr_characters.character_reputation;
INSERT INTO character_skills SELECT * FROM ptr_characters.character_skills;
INSERT INTO character_social SELECT * FROM ptr_characters.character_social;
INSERT INTO character_spell SELECT * FROM ptr_characters.character_spell;
INSERT INTO character_spell_cooldown SELECT * FROM ptr_characters.character_spell_cooldown;
INSERT INTO character_stats SELECT * FROM ptr_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 ptr_characters.character_tutorial;

-- corpse -- skip
-- group_instance -- skip
-- group_member -- skip
-- group -- skip
INSERT INTO guild SELECT * FROM ptr_characters.guild;
INSERT INTO guild_bank_eventlog SELECT * FROM ptr_characters.guild_bank_eventlog;
INSERT INTO guild_bank_item SELECT * FROM ptr_characters.guild_bank_item;
INSERT INTO guild_bank_right SELECT * FROM ptr_characters.guild_bank_right;
INSERT INTO guild_bank_tab SELECT * FROM ptr_characters.guild_bank_tab;
INSERT INTO guild_eventlog SELECT * FROM ptr_characters.guild_eventlog;
INSERT INTO guild_member SELECT * FROM ptr_characters.guild_member;
INSERT INTO guild_rank SELECT * FROM ptr_characters.guild_rank;
INSERT INTO instance SELECT * FROM ptr_characters.instance;
INSERT INTO instance_reset SELECT * FROM ptr_characters.instance_reset;

ALTER TABLE `item_instance`
 ADD `data` longtext;

INSERT INTO item_instance (guid, owner_guid, charges, enchantments) SELECT  DISTINCT guid, owner_guid, data, data FROM ptr_characters.item_instance;
UPDATE item_instance a SET `data` = (SELECT data FROM ptr_characters.item_instance WHERE guid = a.guid AND owner_guid = a.owner_guid);
-- Temporarily change delimiter to prevent SQL syntax errors
DELIMITER ||

-- Function to convert ints from unsigned to signed
DROP FUNCTION IF EXISTS `uint32toint32`||
CREATE FUNCTION `uint32toint32`(input INT(10) UNSIGNED) RETURNS INT(11) SIGNED DETERMINISTIC
BEGIN
  RETURN input;
END||

-- Restore original delimiter
DELIMITER ;

-- Move data to new fields
UPDATE `item_instance` SET
`creatorGuid` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',10))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',10+1))-LENGTH(SUBSTRING_INDEX(data,' ',10))-1),

`giftCreatorGuid` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',12))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',12+1))-LENGTH(SUBSTRING_INDEX(data,' ',12))-1),

`COUNT` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',14))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',14+1))-LENGTH(SUBSTRING_INDEX(data,' ',14))-1),

`duration` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',15))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',15+1))-LENGTH(SUBSTRING_INDEX(data,' ',15))-1),

`charges` = CONCAT_WS(' ',
uint32toint32(SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',16))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',16+1))-LENGTH(SUBSTRING_INDEX(data,' ',16))-1)),
uint32toint32(SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',17))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',17+1))-LENGTH(SUBSTRING_INDEX(data,' ',17))-1)),
uint32toint32(SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',18))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',18+1))-LENGTH(SUBSTRING_INDEX(data,' ',18))-1)),
uint32toint32(SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',19))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',19+1))-LENGTH(SUBSTRING_INDEX(data,' ',19))-1)),
uint32toint32(SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',20))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',20+1))-LENGTH(SUBSTRING_INDEX(data,' ',20))-1))),

`flags` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',21))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',21+1))-LENGTH(SUBSTRING_INDEX(data,' ',21))-1),

`enchantments` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',22))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',57+1))-LENGTH(SUBSTRING_INDEX(data,' ',22))-1),

`randomPropertyId` = uint32toint32(SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',59))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',59+1))-LENGTH(SUBSTRING_INDEX(data,' ',59))-1)),

`durability` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',60))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',60+1))-LENGTH(SUBSTRING_INDEX(data,' ',60))-1),

`playedTime` = SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',62))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',62+1))-LENGTH(SUBSTRING_INDEX(data,' ',62))-1);

-- Drop function
DROP FUNCTION IF EXISTS `uint32toint32`;

-- Fix heroic item flag
UPDATE `item_instance` SET `flags`=`flags`&~0x8 WHERE
SUBSTRING(`data`,
LENGTH(SUBSTRING_INDEX(`data`,' ',3))+2,
LENGTH(SUBSTRING_INDEX(`data`,' ',3+1))-LENGTH(SUBSTRING_INDEX(data,' ',3))-1)
NOT IN (5043,5044,17302,17305,17308,21831);

SET @allowedFlags := 0x00000001 | 0x00000008 | 0x00000200 | 0x00001000 | 0x00008000 | 0x00010000;

UPDATE `item_instance` SET `flags` = (`flags` & @allowedFlags);

-- Drop old field
ALTER TABLE `item_instance` DROP `data`;

INSERT INTO mail SELECT * FROM ptr_characters.mail;
INSERT INTO mail_items SELECT * FROM ptr_characters.mail_items;
-- pet_aura -- skip
INSERT INTO pet_spell SELECT * FROM ptr_characters.pet_spell;
INSERT INTO pet_spell_cooldown SELECT * FROM ptr_characters.pet_spell_cooldown;
INSERT INTO petition SELECT * FROM ptr_characters.petition;
INSERT INTO petition_sign SELECT * FROM ptr_characters.petition_sign;
INSERT INTO characters (guid, account, name, race, class, gender, LEVEL, xp, money, playerBytes, playerBytes2, playerFlags, position_x, position_y, position_z, map, 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, 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 ptr_characters.characters;