Auto pool script.

-- This script will take data from the first entry for each creature in the entries list
-- and use that data to populate the pool_creature_info table.
-- It will take all spawn points for the creature within the range
-- Pool ID to work with. 0 means take the next one.
SET @Pool_ID = 0;
-- Creature guid to add. Keep unused = 0. Choose a guid (not entry) for an example of the creature you want.
-- guid is used so as to only get others in the "range" of that creature.
SET @Creature0 = 0;
SET @Creature1 = 0;
SET @Creature2 = 0;
SET @Creature3 = 0;
SET @Creature4 = 0;
SET @Creature5 = 0;
SET @Creature6 = 0;
SET @Creature7 = 0;
SET @Creature8 = 0;
SET @Creature9 = 0;
-- Mainly fixed variables
SET @MinPool = 100000;
SET @MaxPool = 199999;
SET @Range = 600;
SELECT IFNULL(max(entry)+1, @MinPool) INTO @NextPool
FROM pool_template
WHERE entry BETWEEN @MinPool AND @MaxPool;

SET @Pool_ID = CASE WHEN @Pool_ID = 0 THEN @NextPool ELSE @Pool_ID END;

-- Get position of creature 0
SELECT position_x, position_y, position_z INTO @posx, @posy, @posz
FROM creature
WHERE guid = @Creature0
LIMIT 1;

-- Insert creature always with qualifier 0, don't overwrite, chance 0 (equal) into pool data with default times
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature0;
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature1;
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature2;
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature3;
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature4;
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature5;
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature6;
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature7;
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature8;
INSERT IGNORE INTO pool_creature_info
SELECT @Pool_ID, id, 0, 0, spawnMask, phaseMask, modelid, equipment_id, 0, 0, 0, spawndist, currentwaypoint, curhealth, curmana, MovementType, npcflag, unit_flags, dynamicflags
FROM creature
WHERE guid = @Creature9;

SET @RowID = (SELECT IFNULL(MAX(guid),0) FROM pool_spawns WHERE pool_id = @Pool_ID);

-- Add spawn points
INSERT INTO pool_spawns
SELECT @Pool_ID, @RowID:=@RowID+1, cr. map, cr.zoneId, cr.areaId, cr. position_x, cr.position_y, cr.position_z, cr.orientation
FROM creature cr
LEFT OUTER JOIN pool_spawns ps
        ON ps.pool_id = 100000 AND ps.position_x = cr.position_x AND ps.position_y = cr.position_y
WHERE id IN
(
        SELECT id
        FROM creature
        WHERE guid IN(@Creature0, @Creature1, @Creature2, @Creature3, @Creature4, @Creature5, @Creature6, @Creature7, @Creature8, @Creature9)
)
AND cr.position_x BETWEEN @posx-@Range AND @posx+@Range
AND cr.position_y BETWEEN @posy-@Range AND @posy+@Range
AND cr.position_z BETWEEN @posz-@Range AND @posz+@Range
AND ps.guid IS NULL;

-- Create the backup table if it doesn't already exist. No records though.
CREATE TABLE IF NOT EXISTS creature_backup_pooling
(
        SELECT cr.*, CONCAT(ct.name,' on date ', DATE_FORMAT(NOW(), '%Y-%m-%d at %H:%i:%S')) as detail
        FROM creature cr INNER JOIN creature_template ct ON ct.entry = cr.id
        WHERE guid = 0
);

-- Add template record
INSERT INTO pool_template
SELECT @Pool_ID, 0, COUNT(guid), CONCAT('Creature Spawns AUTOGEN on date ', DATE_FORMAT(NOW(), '%Y-%m-%d at %H:%i:%S'))
FROM pool_spawns
WHERE pool_id = @Pool_ID;

-- Backup changed data to the creature backup table.
INSERT INTO creature_backup_pooling
SELECT cr.*, CONCAT(ct.name,' to pool ', @Pool_ID, ' on date ', DATE_FORMAT(NOW(), '%Y-%m-%d at %H:%i:%S')) as detail
FROM creature cr
INNER JOIN creature_template ct ON ct.entry = cr.id
WHERE id IN
(
        SELECT DISTINCT(id)
        FROM creature
        WHERE guid IN(@Creature0, @Creature1, @Creature2, @Creature3, @Creature4, @Creature5, @Creature6, @Creature7, @Creature8, @Creature9)
)
AND position_x BETWEEN @posx-@Range AND @posx+@Range
AND position_y BETWEEN @posy-@Range AND @posy+@Range
AND position_z BETWEEN @posz-@Range AND @posz+@Range;

-- Delete existing creatures
DELETE FROM creature
WHERE id IN
(
        SELECT DISTINCT(id)
        FROM creature_backup_pooling
        WHERE guid IN(@Creature0, @Creature1, @Creature2, @Creature3, @Creature4, @Creature5, @Creature6, @Creature7, @Creature8, @Creature9)
)
AND position_x BETWEEN @posx-@Range AND @posx+@Range
AND position_y BETWEEN @posy-@Range AND @posy+@Range
AND position_z BETWEEN @posz-@Range AND @posz+@Range;