SET @existing_char_name = 'Fckngperfect';
SET @cloned_char_name = 'Hothamwallet';
SET @existing_account_name = 'Jonezy';
SET @cloned_account_name = 'Jonezy';

###############################
# DO NOT EDIT BELOW THIS LINE #
###############################

SET @existing_char_guid = (
	SELECT guid FROM characters.characters WHERE name = @existing_char_name
);

SET @existing_account_id = (
	SELECT id FROM realmd.account WHERE username = @existing_account_name
);
SET @cloned_account_id = (
	SELECT id FROM realmd.account WHERE username = @cloned_account_name
);

# Character Setup

DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.characters WHERE name = @existing_char_name;
ALTER TABLE characters.tmp DROP COLUMN guid;
UPDATE characters.tmp SET name = @cloned_char_name, account = @cloned_account_id;
INSERT INTO characters.characters
	SELECT (
		SELECT max(guid) + 1 as guid
        FROM characters.characters
	) as guid, characters.tmp.* FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

SET @cloned_char_guid = (
	SELECT guid FROM characters.characters WHERE name = @cloned_char_name
);

# Item Setup
ALTER TABLE characters.item_instance
	MODIFY COLUMN guid INT
    auto_increment;
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.item_instance
    WHERE owner_guid = @existing_char_guid;
UPDATE characters.tmp SET owner_guid = @cloned_char_guid;
UPDATE characters.tmp SET cloned_item_id = guid;
UPDATE characters.item_instance SET cloned_item_id = NULL where cloned_item_id in (
	SELECT guid FROM characters.tmp
);
ALTER TABLE characters.tmp DROP guid;
INSERT INTO characters.item_instance
	SELECT 0, characters.tmp.* FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;
ALTER TABLE characters.item_instance
	MODIFY COLUMN guid INT;

# Item Setup
ALTER TABLE characters.item_loot
	MODIFY COLUMN guid INT
    auto_increment;
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.item_loot
    WHERE owner_guid = @existing_char_guid;
UPDATE characters.tmp SET owner_guid = @cloned_char_guid;
UPDATE characters.tmp SET cloned_item_id = guid;
UPDATE characters.item_loot SET cloned_item_id = NULL where cloned_item_id in (
	SELECT guid FROM characters.tmp
);
ALTER TABLE characters.tmp DROP guid;
INSERT INTO characters.item_loot
	SELECT 0, characters.tmp.* FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;
ALTER TABLE characters.item_instance
	MODIFY COLUMN guid INT;

# Pet Setup
ALTER TABLE characters.character_pet
	MODIFY COLUMN id INT
    auto_increment;
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_pet
    WHERE owner = @existing_char_guid;
UPDATE characters.tmp SET owner = @cloned_char_guid;
ALTER TABLE characters.tmp DROP id;
INSERT INTO characters.character_pet
	SELECT 0, characters.tmp.* FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;
ALTER TABLE characters.character_pet
	MODIFY COLUMN id INT;

# Action Setup
DELETE FROM characters.character_action WHERE guid = @cloned_char_guid;
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_action
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_action SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Aura Setup
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_aura WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_aura SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# BG Data Setup
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_battleground_data
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_battleground_data
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# BG Queue Setup
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_bgqueue
    WHERE PlayerGUID = @existing_char_guid;
UPDATE characters.tmp SET PlayerGUID = @cloned_char_guid;
INSERT INTO characters.character_bgqueue
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Deleted Items Setup
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_deleted_items
    WHERE player_guid = @existing_char_guid;
UPDATE characters.tmp SET player_guid = @cloned_char_guid;
INSERT INTO characters.character_deleted_items
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Forgotten Skills Setup
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_forgotten_skills
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_forgotten_skills
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Character Gifts
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_gifts
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_gifts
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Homebind Gifts
DELETE FROM characters.character_homebind WHERE guid = @cloned_char_guid;
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_homebind
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_homebind
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Honor CP
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_honor_cp
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_honor_cp
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Instance
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_instance
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_instance
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Inventory - COME BACK
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_inventory
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
UPDATE characters.tmp SET item = (
	SELECT guid FROM characters.item_instance WHERE cloned_item_id = item
);
INSERT INTO characters.character_inventory
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Quest Status
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_queststatus
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_queststatus
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Rep
DELETE FROM characters.character_reputation WHERE guid = @cloned_char_guid;
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_reputation
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_reputation
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Skills
DELETE FROM characters.character_skills WHERE guid = @cloned_char_guid;
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_skills
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_skills
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Social
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_social
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_social
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

#Spell
DELETE FROM characters.character_spell WHERE guid = @cloned_char_guid;
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_spell
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_spell
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

#Spell Cooldown
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_spell_cooldown
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_spell_cooldown
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Stats
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_stats
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_stats
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;

# Ticket
DROP TEMPORARY TABLE IF EXISTS characters.tmp;
CREATE TEMPORARY TABLE characters.tmp
	SELECT * FROM characters.character_ticket
    WHERE guid = @existing_char_guid;
UPDATE characters.tmp SET guid = @cloned_char_guid;
INSERT INTO characters.character_ticket
	SELECT * FROM characters.tmp;
DROP TEMPORARY TABLE characters.tmp;