reguid, guid reformat, TC wotlk creature

-- Credits:
-- Rochet2 ~creator
-- LilleCarl ~base code

-- Start guid is 500000. Change it everywhere if need different.

-- This code attempts to rebase all creature guids so that instead of 234, 345, 567 hey will be 34, 35, 36
-- Other tables using creature guids are taken into account as well.

-- NOTE: This code does not format characters DB tables! ~ You should probably try emptying them, like creature_respawn table.

-- Warning: Make a backup before trying. Conflicts may happen due to smart_scripts table complexity and assumptions.
-- Changes to the DB structure can mess up parts of the scripts makeing only half of the code run leaving you with partially changed guids leading to errors.
-- Use with caution if the guids you format have C++ scripts or are linked in tables like smart_scripts or waypoints etc.
-- Free guids may collide as they are primary keys or unique and you may be reinserting them.

-- Does not create a perfect result and smart_scripts will have problems

SET @START_GUID := 500000; -- Change 500000 everywhere in code as well!

DROP TABLE IF EXISTS reorder;
CREATE TABLE reorder (
    `new` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Global Unique Identifier',
    `old` INT(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Global Unique Identifier',
    PRIMARY KEY (`new`),
    UNIQUE INDEX OLD (`old`)
) ENGINE=InnoDB AUTO_INCREMENT=500000 DEFAULT CHARSET=utf8;

INSERT INTO reorder (OLD) SELECT creature.guid FROM creature WHERE guid >= @START_GUID ORDER BY guid DESC;

DROP TABLE IF EXISTS reguid_temp_x;
CREATE TABLE reguid_temp_x AS (SELECT * FROM creature);
UPDATE reguid_temp_x u, reorder r SET u.guid = r.NEW WHERE r.OLD = u.guid;
DELETE FROM creature;
INSERT INTO creature SELECT * FROM reguid_temp_x;
DROP TABLE IF EXISTS reguid_temp_x;

DROP TABLE IF EXISTS reguid_temp_x;
CREATE TABLE reguid_temp_x AS (SELECT * FROM conditions);
UPDATE reguid_temp_x u, reorder r SET u.SourceEntry = -r.NEW WHERE -r.OLD = u.SourceEntry AND u.ConditionTypeOrReference = 22 AND u.SourceEntry < 0;
DELETE FROM conditions;
INSERT INTO conditions SELECT * FROM reguid_temp_x;
DROP TABLE IF EXISTS reguid_temp_x;

ALTER TABLE creature_addon DROP PRIMARY KEY;
UPDATE creature_addon u, reorder r SET u.guid = r.NEW WHERE r.OLD = u.guid;
ALTER TABLE creature_addon ADD PRIMARY KEY (guid);

UPDATE creature_formations u, reorder r SET u.leaderGUID = r.NEW WHERE r.OLD = u.leaderGUID;

ALTER TABLE creature_formations DROP PRIMARY KEY;
UPDATE creature_formations u, reorder r SET u.memberGUID = r.NEW WHERE r.OLD = u.memberGUID;
ALTER TABLE creature_formations ADD PRIMARY KEY (memberGUID);

ALTER TABLE game_event_creature DROP PRIMARY KEY;
UPDATE game_event_creature u, reorder r SET u.guid = r.NEW WHERE r.OLD = u.guid;
ALTER TABLE game_event_creature ADD PRIMARY KEY (eventEntry, guid);

ALTER TABLE game_event_model_equip DROP PRIMARY KEY;
UPDATE game_event_model_equip u, reorder r SET u.guid = r.NEW WHERE r.OLD = u.guid;
ALTER TABLE game_event_model_equip ADD PRIMARY KEY (guid);

ALTER TABLE game_event_npcflag DROP PRIMARY KEY;
UPDATE game_event_npcflag u, reorder r SET u.guid = r.NEW WHERE r.OLD = u.guid;
ALTER TABLE game_event_npcflag ADD PRIMARY KEY (eventEntry, guid);

ALTER TABLE game_event_npc_vendor DROP PRIMARY KEY;
UPDATE game_event_npc_vendor u, reorder r SET u.guid = r.NEW WHERE r.OLD = u.guid;
ALTER TABLE game_event_npc_vendor ADD PRIMARY KEY (guid, item);

ALTER TABLE linked_respawn DROP PRIMARY KEY;
UPDATE linked_respawn u, reorder r SET u.guid = r.NEW WHERE r.OLD = u.guid AND u.linkType IN (0, 1);
ALTER TABLE linked_respawn ADD PRIMARY KEY (guid, linkType);

UPDATE linked_respawn u, reorder r SET u.linkedGuid = r.NEW WHERE r.OLD = u.linkedGuid AND u.linkType IN (0, 3);

ALTER TABLE pool_creature DROP PRIMARY KEY;
UPDATE pool_creature u, reorder r SET u.guid = r.NEW WHERE r.OLD = u.guid;
ALTER TABLE pool_creature ADD PRIMARY KEY (guid);

ALTER TABLE vehicle_accessory DROP PRIMARY KEY;
UPDATE vehicle_accessory u, reorder r SET u.guid = r.NEW WHERE r.OLD = u.guid;
ALTER TABLE vehicle_accessory ADD PRIMARY KEY (guid, seat_id);

-- Not complete for timed action list~
ALTER TABLE smart_scripts DROP PRIMARY KEY;
UPDATE smart_scripts u, reorder r SET u.entryorguid = -r.NEW WHERE -r.OLD = u.entryorguid AND u.source_type = 0;
ALTER TABLE smart_scripts ADD PRIMARY KEY (entryorguid, source_type, id, link);

UPDATE smart_scripts u, reorder r SET u.event_param1 = r.NEW WHERE r.OLD = u.event_param1 AND u.event_type IN(75, 76);
UPDATE smart_scripts u, reorder r SET u.action_param1 = -r.NEW WHERE -r.OLD = u.action_param1 AND u.action_type IN(80);
--

ALTER TABLE creature AUTO_INCREMENT=500000;
-- DROP TABLE IF EXISTS reorder;