Char-purge

-- Find chars last login before 2012 and export to a file  (to be used to create a webpage later)
SELECT `name`
INTO OUTFILE '/var/lib/mysql/purged-characters.csv'
FIELDS TERMINATED BY ''
ENCLOSED BY ''
LINES TERMINATED BY '\n'
FROM `char` WHERE
    `last_login` = 0 AND
    `account_id` IN (SELECT `account_id` FROM `login` WHERE `lastlogin` < '2012-01-01 00:00:00') AND
    `class` = 0 AND `zeny` = 120000 AND
    `str` = 1 AND `agi` = 1 AND `vit` = 1 AND `int` = 1 AND `dex` = 1 AND `luk` = 1 AND
    `last_map` = 'amatsu' AND `last_x` = 225 AND `last_y` = 215
ORDER BY `name` ASC;

-- Delete chars last login before 2012
DELETE FROM `char` WHERE
    `last_login` = 0 AND
    `account_id` IN (SELECT `account_id` FROM `login` WHERE `lastlogin` < '2012-01-01 00:00:00') AND
    `class` = 0 AND `zeny` = 120000 AND
    `str` = 1 AND `agi` = 1 AND `vit` = 1 AND `int` = 1 AND `dex` = 1 AND `luk` = 1 AND
    `last_map` = 'amatsu' AND `last_x` = 225 AND `last_y` = 215;


-- Delete old accounts last login before 2012, no donations, no chars, no inventory AND no storage items
DELETE FROM `login` WHERE
    `lastlogin` < '2012-01-01 00:00:00' AND
    `don_total` = 0 AND `last_unique_id` = 0 AND
    `account_id` NOT IN (SELECT g.account_id FROM `storage` g) AND
    `account_id` NOT IN (SELECT s.account_id FROM `char` s);

-- Delete chars with a non-existent account_id (i.e. they have no entry in login table)
DELETE FROM `char` WHERE
    `last_login` = 0 AND
    `account_id` NOT IN (SELECT g.account_id FROM `login` g) AND
    `class` = 0 AND `zeny` = 120000 AND
    `str` = 1 AND `agi` = 1 AND `vit` = 1 AND `int` = 1 AND `dex` = 1 AND `luk` = 1 AND
    `last_map` = 'amatsu' AND `last_x` = 225 AND `last_y` = 215;

-- Delete non-existent friends entries
DELETE FROM `friends` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g) OR `friend_id` NOT IN (SELECT g.char_id FROM `char` g);

-- Delete non-existent inventory entries
DELETE FROM `inventory` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);

-- Delete non-existent homunculus entries
DELETE FROM `homunculus` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);

-- Delete non-existent hotkey entries
DELETE FROM `hotkey` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);

-- Delete non-existent party entries
DELETE FROM `party` WHERE `leader_char` NOT IN (SELECT g.char_id FROM `char` g);
UPDATE `char` SET `party_id` = 0 WHERE `party_id` != 0 AND `party_id` NOT IN (SELECT g.party_id FROM `party` g);

-- Delete non-existent quest entries
DELETE FROM `quest` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);

-- Delete non-existent sc_data entries
DELETE FROM `sc_data` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);

-- Delete non-existent skill entries
DELETE FROM `skill` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);

-- Delete non-existent skill_homunculus entries
DELETE FROM `skill_homunculus` WHERE `homun_id` NOT IN (SELECT g.homun_id FROM `homunculus` g);

-- Delete non-existent char registry keys
DELETE FROM `char_reg_num_db` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
DELETE FROM `char_reg_str_db` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);

-- Find the guild master char id
SELECT `account_id` FROM `char` WHERE `char_id` IN (SELECT `char_id` FROM `guild` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g));

-- Export the above statement into a .sql for INSERT statements.
-- Don't export 'id' column. Once exported, change guild_id to account_id

-- Then delete the storage
DELETE FROM `guild_storage` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);

-- Delete guild if guild master is now non-existent
DELETE FROM `guild` WHERE `char_id` NOT IN (SELECT g.char_id FROM `char` g);
UPDATE `char` SET `guild_id` = 0 WHERE `guild_id` != 0 AND `guild_id` NOT IN (SELECT g.guild_id FROM `guild` g);

-- Remove from guild if account is non-existent
DELETE FROM `guild_member` WHERE `account_id` NOT IN (SELECT g.account_id FROM `login` g);

-- Delete any non-existent guild entries
DELETE FROM `guild_alliance` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
DELETE FROM `guild_castle` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
DELETE FROM `guild_expulsion` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
DELETE FROM `guild_member` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
DELETE FROM `guild_position` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);
DELETE FROM `guild_skill` WHERE `guild_id` NOT IN (SELECT g.guild_id FROM guild g);