[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir		= /usr
datadir = /var/lib/mysql
tmpdir		= /tmp
log-error=/var/log/mysql.err


max_connections = 20000
max_connect_errors = 10000
table_open_cache = 2048
tmp_table_size = 1000M
max_heap_table_size = 256M
#read_rnd_buffer_size = 2M
#sort_buffer_size = 4M
query_cache_size = 1024M
query_cache_limit = 2M

join_buffer_size = 2M
default-storage-engine = InnoDB

skip-external-locking
federated=ON

innodb_additional_mem_pool_size = 456M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size=128M

innodb_buffer_pool_size=5096M

innodb_log_file_size=512M
innodb_thread_concurrency = 60

memlock
language	= /usr/share/mysql/english

skip-external-locking

max_allowed_packet	= 64M
thread_stack		= 256K
thread_cache_size       = 32

myisam-recover = BACKUP

expire_logs_days = 10
max_binlog_size = 100M
big-tables

[mysqldump]
quick
quote-names
max_allowed_packet	= 64M

[myisamchk]
key_buffer_size = 768M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[isamchk]
key_buffer		= 128M

[mysqld_safe]
open-files-limit = 8192


!includedir /etc/mysql/conf.d/

/*
 * This file is part of aion-unique <aionu-unique.com>.
 *
 *  aion-unique is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  aion-unique is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with aion-unique.  If not, see <http://www.gnu.org/licenses/>.
 */
package mysql5;

import com.aionemu.commons.database.DB;
import com.aionemu.commons.database.DatabaseFactory;
import com.aionemu.commons.database.IUStH;
import com.aionemu.commons.utils.GenericValidator;
import com.google.common.base.Predicate;
import com.google.common.collect.Collections2;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import javax.annotation.Nullable;
import javolution.util.FastList;
import org.jsemu.gameserver.dao.InventoryDAO;
import org.jsemu.gameserver.model.gameobjects.Item;
import org.jsemu.gameserver.model.gameobjects.PersistentState;
import org.jsemu.gameserver.model.gameobjects.player.Equipment;
import org.jsemu.gameserver.model.gameobjects.player.Player;
import org.jsemu.gameserver.model.gameobjects.player.Storage;
import org.jsemu.gameserver.model.gameobjects.player.StorageType;
import org.jsemu.gameserver.services.item.ItemService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author ATracer, Steve
 *
 */
public class MySQL5InventoryDAO extends InventoryDAO {

    private static final Logger log = LoggerFactory.getLogger(MySQL5InventoryDAO.class);
    public static final String SELECT_QUERY = "SELECT `itemUniqueId`, `itemId`, `itemCount`, `itemColor`, `isEquiped`, `isSoulBound`, `slot`, `enchant`, `itemSkin`, `fusionedItem`, `optionalSocket`, `optionalFusionSocket`, `enchanting_damage`, `enchanting_count`, `itemCreator`, `itemCreationTime`, `itemExistTime`, `itemTradeTime`, `is_seal`, `activate_count`, `random_group` FROM `inventory` WHERE `itemOwner`=? AND `itemLocation`=? AND `isEquiped`=?";
    public static final String INSERT_QUERY = "INSERT INTO `inventory` (`itemUniqueId`, `itemId`, `itemCount`, `itemColor`, `itemOwner`, `isEquiped`, isSoulBound, `slot`, `itemLocation`, `enchant`, `itemSkin`, `fusionedItem`, `optionalSocket`, `optionalFusionSocket`, `enchanting_damage`, `enchanting_count`, `itemCreator`, `itemCreationTime`, `itemExistTime`, `itemTradeTime`, `is_seal`, `activate_count`, `random_group`) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    public static final String UPDATE_QUERY = "UPDATE inventory SET  itemCount=?, itemColor=?, itemOwner=?, isEquiped=?, isSoulBound=?, slot=?, itemLocation=?, enchant=?, itemSkin=?, fusionedItem=?, optionalSocket=?, optionalFusionSocket=?, enchanting_damage=?, enchanting_count=?, itemCreator=?, itemCreationTime=?, itemExistTime=?, itemTradeTime=?, is_seal=?, activate_count=? WHERE itemUniqueId=?";
    public static final String DELETE_QUERY = "DELETE FROM inventory WHERE itemUniqueId=?";
    public static final String DELETE_CLEAN_QUERY = "DELETE FROM inventory WHERE itemOwner=? AND (itemLocation=0 OR itemLocation=1)";
    public static final String SELECT_ACCOUNT_QUERY = "SELECT `account_id` FROM `players` WHERE `id`=?";
    public static final String DELETE_ACCOUNT_WH = "DELETE FROM inventory WHERE `itemOwner`=? AND `itemLocation`=2";
    public static final String DELETE_HOUSE_WH = "DELETE FROM inventory WHERE `itemOwner`=? AND `itemLocation`=?";
    public static final String SELECT_LEGION_QUERY = "SELECT `legion_id` FROM `legion_members` WHERE `player_id`=?";
    public static final String SELECT_QUERY2 = "SELECT * FROM `inventory` WHERE `itemOwner`=? AND `itemLocation`=?";
    private static final Predicate<Item> itemsToInsertPredicate = new Predicate<Item>() {

        @Override
        public boolean apply(@Nullable Item input) {
            return input != null && PersistentState.NEW == input.getPersistentState();
        }
    };
    private static final Predicate<Item> itemsToUpdatePredicate = new Predicate<Item>() {

        @Override
        public boolean apply(@Nullable Item input) {
            return input != null && PersistentState.UPDATE_REQUIRED == input.getPersistentState();
        }
    };
    private static final Predicate<Item> itemsToDeletePredicate = new Predicate<Item>() {

        @Override
        public boolean apply(@Nullable Item input) {
            return input != null && PersistentState.DELETED == input.getPersistentState();
        }
    };

    @Override
    public Storage loadStorage(Player player, StorageType storageType) {
        final Storage inventory = new Storage(player, storageType);
        int playerId = player.getObjectId();
        final int storage = storageType.getId();
        final int equipped = 0;

        if (storageType == StorageType.ACCOUNT_WAREHOUSE) {
            playerId = getPlayerAccountId(playerId);
        }

        final int owner = playerId;
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            stmt = con.prepareStatement(SELECT_QUERY);
            stmt.setInt(1, owner);
            stmt.setInt(2, storage);
            stmt.setInt(3, equipped);
            ResultSet rset = stmt.executeQuery();
            while (rset.next()) {
                Item item = constructItem(storage, rset, owner);
                item.setPersistentState(PersistentState.UPDATED);
                if (item.getItemTemplate() == null) {
                    log.error(playerId + "loaded error item, itemUniqueId is: " + item.getObjectId());
                } else {
                    inventory.onLoadHandler(item);
                }
            }
            rset.close();
        } catch (Exception e) {
            log.error("Could not restore storage data for player: " + playerId + " from DB: " + e.getMessage(), e);
        } finally {
            DatabaseFactory.close(stmt, con);
        }
        return inventory;
    }

    @Override
    public List<Item> loadStorageDirect(int playerId, StorageType storageType) {
        List<Item> list = FastList.newInstance();
        final int storage = storageType.getId();

        if (storageType == StorageType.ACCOUNT_WAREHOUSE) {
            playerId = getPlayerAccountId(playerId);
        }

        final int owner = playerId;
        Connection con = null;
        PreparedStatement stmt = null;
        try {
            con = DatabaseFactory.getConnection();
            stmt = con.prepareStatement(SELECT_QUERY2);
            stmt.setInt(1, owner);
            stmt.setInt(2, storageType.getId());
            ResultSet rset = stmt.executeQuery();
            while (rset.next()) {
                list.add(constructItem(storage, rset, owner));
            }
            rset.close();
        } catch (Exception e) {
            log.error("Could not restore loadStorageDirect data for player: " + playerId + " from DB: " + e.getMessage(), e);
        } finally {
            DatabaseFactory.close(stmt, con);
        }
        return list;
    }

    @Override
    public Equipment loadEquipment(Player player) {
        final Equipment equipment = new Equipment(player);

        int playerId = player.getObjectId();
        final int storage = 0;
        final int equipped = 1;
        final int owner = playerId;

        Connection con = null;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            PreparedStatement stmt = con.prepareStatement(SELECT_QUERY);
            stmt.setInt(1, owner);
            stmt.setInt(2, storage);
            stmt.setInt(3, equipped);
            ResultSet rset = stmt.executeQuery();
            while (rset.next()) {
                Item item = constructItem(storage, rset, owner);
                item.setPersistentState(PersistentState.UPDATED);
                equipment.onLoadHandler(item);
            }
            rset.close();
            stmt.close();
        } catch (Exception e) {
            log.error("Could not restore Equipment data for player: " + playerId + " from DB: " + e.getMessage(), e);
        } finally {
            DatabaseFactory.close(con);
        }
        return equipment;
    }

    @Override
    public List<Item> loadEquipment(int playerId) {
        final List<Item> items = new ArrayList<Item>();
        final int storage = 0;
        final int equipped = 1;
        final int owner = playerId;

        Connection con = null;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            PreparedStatement stmt = con.prepareStatement(SELECT_QUERY);
            stmt.setInt(1, owner);
            stmt.setInt(2, storage);
            stmt.setInt(3, equipped);
            ResultSet rset = stmt.executeQuery();
            while (rset.next()) {
                Item item = constructItem(storage, rset, owner);
                items.add(item);
            }
            rset.close();
            stmt.close();
        } catch (Exception e) {
            log.error("Could not restore Equipment data for player: " + playerId + " from DB: " + e.getMessage(), e);
        } finally {
            DatabaseFactory.close(con);
        }
        return items;
    }

    private Item constructItem(final int storage, ResultSet rset, int owner) throws SQLException {
        int itemUniqueId = rset.getInt("itemUniqueId");
        int itemId = rset.getInt("itemId");
        long itemCount = rset.getLong("itemCount");
        int itemColor = rset.getInt("itemColor");
        int isEquiped = rset.getInt("isEquiped");
        int isSoulBound = rset.getInt("isSoulBound");
        long slot = rset.getLong("slot");
        int enchant = rset.getInt("enchant");
        int itemSkin = rset.getInt("itemSkin");
        int fusionedItem = rset.getInt("fusionedItem");
        int optionalSocket = rset.getInt("optionalSocket");
        int optionalFusionSocket = rset.getInt("optionalFusionSocket");
        int enchantingValue = rset.getInt("enchanting_damage");
        int enchantingLevel = rset.getInt("enchanting_count");
        String crafterName = rset.getString("itemCreator");
        long itemCreationTime = rset.getTimestamp("itemCreationTime").getTime();
        long tempItemTime = rset.getLong("itemExistTime");
        int tempTradeTime = rset.getInt("itemTradeTime");
        int seal = rset.getInt("is_seal");
        int activatecount = rset.getInt("activate_count");
        int randomGroup = rset.getInt("random_group");
        Item item = new Item(itemUniqueId, itemId, itemCount, itemColor, isEquiped == 1,
                isSoulBound == 1, slot, storage, enchant, itemSkin, fusionedItem, optionalSocket,
                optionalFusionSocket, crafterName, owner, itemCreationTime, tempItemTime, tempTradeTime,
                enchantingValue, enchantingLevel, seal == 1, activatecount, randomGroup);
        return item;
    }

    public int getPlayerAccountId(final int playerId) {
        Connection con = null;
        int accountId = 0;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            PreparedStatement stmt = con.prepareStatement(SELECT_ACCOUNT_QUERY);
            stmt.setInt(1, playerId);
            ResultSet rset = stmt.executeQuery();
            if (rset.next()) {
                accountId = rset.getInt("account_id");
            }
            rset.close();
            stmt.close();
        } catch (Exception e) {
            log.error("Could not restore accountId data for player: " + playerId + " from DB: " + e.getMessage(), e);
        } finally {
            DatabaseFactory.close(con);
        }
        return accountId;
    }

    public int loadLegionId(final int playerId) {
        Connection con = null;
        int legionId = 0;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            PreparedStatement stmt = con.prepareStatement(SELECT_LEGION_QUERY);
            stmt.setInt(1, playerId);
            ResultSet rset = stmt.executeQuery();
            if (rset.next()) {
                legionId = rset.getInt("legion_id");
            }
            rset.close();
            stmt.close();
        } catch (Exception e) {
            log.error("Failed to load legion id for player id: " + playerId, e);
        } finally {
            DatabaseFactory.close(con);
        }

        return legionId;
    }

    @Override
    public boolean store(Player player) {
        int playerId = player.getObjectId();
        Integer accountId = player.getPlayerAccount() != null ? player.getPlayerAccount().getId() : null;
        Integer legionId = player.getLegion() != null ? player.getLegion().getLegionId() : null;
        FastList<Item> allPlayerItems = player.getDirtyItemsToUpdate();

        return store(allPlayerItems, playerId, accountId, legionId);
    }

    @Override
    public boolean store(Item item, Player player) {
        int playerId = player.getObjectId();
        int accountId = player.getPlayerAccount().getId();
        Integer legionId = player.getLegion() != null ? player.getLegion().getLegionId() : null;

        return store(item, playerId, accountId, legionId);
    }

    /**
     * @param item The item that needs to be stored
     * @param ownerId The playerObjectId of the owner of the item
     * @return true if storing succeeded
     */
    @Override
    public boolean store(List<Item> items, int ownerId) {

        Integer accountId = null;
        Integer legionId = null;

        for (Item item : items) {

            if (accountId == null && item.getItemLocation() == StorageType.ACCOUNT_WAREHOUSE.getId()) {
                accountId = getPlayerAccountId(ownerId);
            }

            if (legionId == null && item.getItemLocation() == StorageType.LEGION_WAREHOUSE.getId()) {
                int localLegionId = loadLegionId(ownerId);
                if (localLegionId > 0) {
                    legionId = localLegionId;
                }
            }
        }

        return store(items, ownerId, accountId, legionId);
    }

    @Override
    public boolean store(List<Item> items, Integer playerId, Integer accountId, Integer legionId) {
        Collection<Item> itemsToUpdate = Collections2.filter(items, itemsToUpdatePredicate);
        Collection<Item> itemsToInsert = Collections2.filter(items, itemsToInsertPredicate);
        Collection<Item> itemsToDelete = Collections2.filter(items, itemsToDeletePredicate);

        boolean deleteResult = false;
        boolean insertResult = false;
        boolean updateResult = false;

        Connection con = null;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            con.setAutoCommit(false);
            deleteResult = deleteItems(con, itemsToDelete);
            insertResult = insertItems(con, itemsToInsert, playerId, accountId, legionId);
            updateResult = updateItems(con, itemsToUpdate, playerId, accountId, legionId);
        } catch (SQLException e) {
            log.error("Can't open connection to save player inventory: " + playerId);
        } finally {
            DatabaseFactory.close(con);
        }

        for (Item item : items) {
            item.setPersistentState(PersistentState.UPDATED);
        }

        if (!itemsToDelete.isEmpty() && deleteResult) {
            ItemService.releaseItemIds(itemsToDelete);
        }

        return deleteResult && insertResult && updateResult;
    }

    private int getItemOwnerId(Item item, Integer playerId, Integer accountId, Integer legionId) {
        if (item.getItemLocation() == StorageType.ACCOUNT_WAREHOUSE.getId()) {
            return accountId;
        }

        if (item.getItemLocation() == StorageType.LEGION_WAREHOUSE.getId()) {
            return legionId != null ? legionId : playerId;
        }

        return playerId;
    }

    private boolean insertItems(Connection con, Collection<Item> items, Integer playerId, Integer accountId,
            Integer legionId) {

        if (GenericValidator.isBlankOrNull(items)) {
            return true;
        }

        PreparedStatement stmt = null;
        try {
            stmt = con.prepareStatement(INSERT_QUERY);

            for (Item item : items) {
                stmt.setInt(1, item.getObjectId());
                stmt.setInt(2, item.getItemTemplate().getTemplateId());
                stmt.setLong(3, item.getItemCount());
                stmt.setInt(4, item.getItemColor());
                stmt.setInt(5, getItemOwnerId(item, playerId, accountId, legionId));
                stmt.setBoolean(6, item.isEquipped());
                stmt.setInt(7, item.isSoulBound() ? 1 : 0);
                stmt.setLong(8, item.getEquipmentSlot());
                stmt.setInt(9, item.getItemLocation());
                stmt.setInt(10, item.getEnchantLevel());
                stmt.setInt(11, item.getItemSkinTemplate().getTemplateId());
                stmt.setInt(12, item.getFusionedItem());
                stmt.setInt(13, item.getOptionalSocket());
                stmt.setInt(14, item.getOptionalFusionSocket());
                stmt.setInt(15, item.getEnchantingValue());
                stmt.setInt(16, item.getEnchantingLevel());
                stmt.setString(17, item.getCrafterName());
                stmt.setTimestamp(18, new Timestamp(item.getItemCreationTime()));
                stmt.setLong(19, item.getTempItemSettedTime());
                stmt.setInt(20, item.getTempTradeSettedTime());
                stmt.setInt(21, item.getUnSeal() ? 1 : 0);
                stmt.setInt(22, item.getActivationCount());
                stmt.setInt(23, item.getRandomOptionGroup());
                stmt.addBatch();
            }

            stmt.executeBatch();
            con.commit();
        } catch (Exception e) {
            log.error("Failed to execute insert batch", e);
            return false;
        } finally {
            DatabaseFactory.close(stmt);
        }
        return true;
    }

    private boolean updateItems(Connection con, Collection<Item> items, Integer playerId, Integer accountId,
            Integer legionId) {

        if (GenericValidator.isBlankOrNull(items)) {
            return true;
        }

        PreparedStatement stmt = null;
        try {
            stmt = con.prepareStatement(UPDATE_QUERY);
            for (Item item : items) {
                stmt.setLong(1, item.getItemCount());
                stmt.setInt(2, item.getItemColor());
                stmt.setInt(3, getItemOwnerId(item, playerId, accountId, legionId));
                stmt.setBoolean(4, item.isEquipped());
                stmt.setInt(5, item.isSoulBound() ? 1 : 0);
                stmt.setLong(6, item.getEquipmentSlot());
                stmt.setInt(7, item.getItemLocation());
                stmt.setInt(8, item.getEnchantLevel());
                stmt.setInt(9, item.getItemSkinTemplate().getTemplateId());
                stmt.setInt(10, item.getFusionedItem());
                stmt.setInt(11, item.getOptionalSocket());
                stmt.setInt(12, item.getOptionalFusionSocket());
                stmt.setInt(13, item.getEnchantingValue());
                stmt.setInt(14, item.getEnchantingLevel());
                stmt.setString(15, item.getCrafterName());
                stmt.setTimestamp(16, new Timestamp(item.getItemCreationTime()));
                stmt.setLong(17, item.getTempItemSettedTime());
                stmt.setInt(18, item.getTempTradeSettedTime());
                stmt.setInt(19, item.getUnSeal() ? 1 : 0);
                stmt.setInt(20, item.getActivationCount());
                stmt.setInt(21, item.getObjectId());
                stmt.addBatch();
            }

            stmt.executeBatch();
            con.commit();
        } catch (Exception e) {
            log.error("Failed to execute update batch", e);
            return false;
        } finally {
            DatabaseFactory.close(stmt);
        }
        return true;
    }

    private boolean deleteItems(Connection con, Collection<Item> items) {

        if (GenericValidator.isBlankOrNull(items)) {
            return true;
        }

        PreparedStatement stmt = null;
        try {
            stmt = con.prepareStatement(DELETE_QUERY);
            for (Item item : items) {
                stmt.setInt(1, item.getObjectId());
                stmt.addBatch();
            }

            stmt.executeBatch();
            con.commit();
        } catch (Exception e) {
            log.error("Failed to execute delete batch", e);
            return false;
        } finally {
            DatabaseFactory.close(stmt);
        }
        return true;
    }

    @Override
    public void removeItem(final Collection<Item> items) {
        DB.insertUpdate(DELETE_QUERY, new IUStH() {

            @Override
            public void handleInsertUpdate(PreparedStatement stmt) throws SQLException {
                for (Item item : items) {
                    stmt.setInt(1, item.getObjectId());
                    stmt.execute();
                }
            }
        });
    }

    /**
     * Since inventory is not using FK - need to clean items
     */
    @Override
    public boolean deletePlayerItems(final int playerId) {
        Connection con = null;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            PreparedStatement stmt = con.prepareStatement(DELETE_CLEAN_QUERY);
            stmt.setInt(1, playerId);
            stmt.execute();
            stmt.close();
        } catch (Exception e) {
            log.error("Error Player all items. PlayerObjId: " + playerId, e);
            return false;
        } finally {
            DatabaseFactory.close(con);
        }
        return true;
    }

    @Override
    public void deleteAccountWH(final int accountId) {
        Connection con = null;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            PreparedStatement stmt = con.prepareStatement(DELETE_ACCOUNT_WH);
            stmt.setInt(1, accountId);
            stmt.execute();
            stmt.close();
        } catch (Exception e) {
            log.error("Error deleting all items from account WH. AccountId: " + accountId, e);
        } finally {
            DatabaseFactory.close(con);
        }
    }

    @Override
    public void deleteHouseWH(int playerId, int locationId) {
        Connection con = null;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            PreparedStatement stmt = con.prepareStatement(DELETE_HOUSE_WH);
            stmt.setInt(1, playerId);
            stmt.setInt(2, locationId);
            stmt.execute();
            stmt.close();
        } catch (Exception e) {
            log.error("Error deleting all items from house WH. PlayerId: " + playerId + " LocationId: " + locationId, e);
        } finally {
            DatabaseFactory.close(con);
        }
    }

    @Override
    public int[] getUsedIDs() {
        Connection con = null;
        try {
            con = DatabaseFactory.getConnection();
            con.setReadOnly(false);
            PreparedStatement statement = con.prepareStatement("SELECT itemUniqueId FROM inventory", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet rs = statement.executeQuery();
            rs.last();
            int count = rs.getRow();
            rs.beforeFirst();
            int[] ids = new int[count];
            for (int i = 0; i < count; i++) {
                rs.next();
                ids[i] = rs.getInt("itemUniqueId");
            }
            return ids;
        } catch (SQLException e) {
            log.error("Can't get list of id's from inventory table", e);
        } finally {
            DatabaseFactory.close(con);
        }

        return new int[0];
    }

    /**
     * {@inheritDoc}
     */
    @Override
    public boolean supports(String s, int i, int i1) {
        return MySQL5DAOUtils.supports(s, i, i1);
    }
}