/**
 * TODO: data/scripts/handlers/MasterHandler.java: loadAdminHandlers()
 *   + AdminCommandHandler.getInstance().registerAdminCommandHandler(new handlers.admincommandhandlers.AdminItemsToSQL());
 */

package handlers.admincommandhandlers;

import java.io.BufferedWriter;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import com.l2jserver.gameserver.datatables.ItemTable;
import com.l2jserver.gameserver.handler.IAdminCommandHandler;
import com.l2jserver.gameserver.model.actor.instance.L2PcInstance;
import com.l2jserver.gameserver.model.holders.SkillHolder;
import com.l2jserver.gameserver.model.items.L2Armor;
import com.l2jserver.gameserver.model.items.L2EtcItem;
import com.l2jserver.gameserver.model.items.L2Item;
import com.l2jserver.gameserver.model.items.L2Weapon;
import com.l2jserver.util.Util;

public class AdminItemsToSQL implements IAdminCommandHandler
{
	private static final String TABLE_ARMOR = "armor";
	private static final String TABLE_ETCITEM = "etcitem";
	private static final String TABLE_WEAPON = "weapon";
	
	private static final String FILE_ARMOR = "log/armor.sql";
	private static final String FILE_ETCITEM = "log/etcitem.sql";
	private static final String FILE_WEAPON = "log/weapon.sql";
//	private static final String FILE_ARMOR = "../sql/server/armor.sql";
//	private static final String FILE_ETCITEM = "../sql/server/etcitem.sql";
//	private static final String FILE_WEAPON = "../sql/server/weapon.sql";
	
	private static final int SPLIT = 500;
	
	private static final String[] ADMIN_COMMANDS =
	{
		"admin_items_to_sql"
	};
	
	@Override
	public boolean useAdminCommand(String command, L2PcInstance activeChar)
	{
		if (command.equals("admin_items_to_sql"))
		{
			HashMap<Integer, String> materials = new HashMap<>();
			HashMap<Integer, String> crystalTypes = new HashMap<>();
			HashMap<Integer, String> slots = new HashMap<>();
			
			for (Map.Entry<String, Integer> e : ItemTable._materials.entrySet())
				materials.put(e.getValue(), e.getKey());
			for (Map.Entry<String, Integer> e : ItemTable._crystalTypes.entrySet())
				crystalTypes.put(e.getValue(), e.getKey());
			for (Map.Entry<String, Integer> e : ItemTable._slots.entrySet())
				slots.put(e.getValue(), e.getKey());
			
			try
			{
				BufferedWriter out;
				int count;
				////////////////////////////////////////////////////
				// armor
				////////////////////////////////////////////////////
				activeChar.sendMessage(FILE_ARMOR);
				out = Util.utf8BufferedWriter(FILE_ARMOR);
				count = 0;
				for (int id : ItemTable.getInstance().getAllArmorsId())
				{
					if (count == 0)
						out.write("DROP TABLE IF EXISTS `" + TABLE_ARMOR + "`;\n"
								+ "CREATE TABLE IF NOT EXISTS `" + TABLE_ARMOR + "` (\n"
								+ "  `item_id` smallint(5) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `name` varchar(120) NOT NULL DEFAULT '',\n"
								+ "  `additionalname` varchar(120) NOT NULL DEFAULT '',\n"
								+ "  `bodypart` varchar(15) NOT NULL DEFAULT 'none',\n"
								+ "  `crystallizable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `armor_type` enum('none','light','heavy','magic','sigil','pet','shield') NOT NULL DEFAULT 'none',\n"	//[JOJO] add enum 'shield'
								+ "  `weight` smallint(4) NOT NULL DEFAULT '0',\n"
								+ "  `material` varchar(15) NOT NULL DEFAULT 'wood',\n"
								+ "  `crystal_type` enum('none','d','c','b','a','s','s80','s84') NOT NULL DEFAULT 'none',\n"
								+ "  `avoid_modify` tinyint(2) NOT NULL DEFAULT '0',\n"
								+ "  `duration` mediumint(5) NOT NULL DEFAULT '-1', -- duration in minutes for shadow items\n"
								+ "  `time` mediumint(5) NOT NULL DEFAULT '-1',     -- duration in minutes for time limited items\n"
								+ "  `p_def` smallint(3) NOT NULL DEFAULT '0',\n"
								+ "  `m_def` smallint(3) NOT NULL DEFAULT '0',\n"
								+ "  `mp_bonus` smallint(3) NOT NULL DEFAULT '0',\n"
								+ "  `price` int(10) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `crystal_count` smallint(4) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `sellable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `dropable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `destroyable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `tradeable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `depositable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `enchant4_skill` varchar(8) NOT NULL DEFAULT '0-0',\n"
								+ "  `skill` varchar(100) DEFAULT '0-0;',\n"	//[JOJO] modify varchar(70) --> varchar(100)
								+ "  `icon` varchar(64) NOT NULL DEFAULT '',\n"	//[JOJO] add
								+ "  PRIMARY KEY (`item_id`)\n"
								+ ") DEFAULT CHARSET=utf8;\n"
								+ "\n"
								+ "INSERT INTO `" + TABLE_ARMOR + "` VALUES\n");
					else if (count % SPLIT == 0)
						out.write(";\nINSERT INTO `" + TABLE_ARMOR + "` VALUES\n");
					else
						out.write(",\n");
					
					L2Armor armor = (L2Armor)ItemTable.getInstance().getTemplate(id);
					String sql = "(" + armor.getItemId()				//`item_id`
						+ ",'" + escape(armor.getName()) + "'"			//`name`
						+ ",''"				//TODO: `additionalname`
						+ ",'" + slots.get(armor.getBodyPart()) + "'"	//`bodypart`
						+ ",'" + armor.isCrystallizable() + "'"			//`crystallizable`
						+ ",'" + armor.getItemType().toString() + "'"	//`armor_type`
						+ ","  + armor.getWeight()						//`weight`
						+ ",'" + materials.get(armor.getMaterialType()) + "'"	//`material`
						+ ",'" + crystalTypes.get(armor.getCrystalType()) + "'"	//`crystal_type`
						+ ",0"				//TODO: `avoid_modify`
						+ ","  + armor.getDuration()					//`duration`
						+ ","  + armor.getTime()						//`time`
						+ ",0"				//TODO: `p_def`
						+ ",0"				//TODO: `m_def`
						+ ",0"				//TODO: `mp_bonus`
						+ ","  + getPrice(armor)						//`price`
						+ ","  + armor.getCrystalCount()				//`crystal_count`
						+ ",'" + armor.isSellable() + "'"				//`sellable`
						+ ",'" + armor.isDropable() + "'"				//`dropable`
						+ ",'" + armor.isDestroyable() + "'"			//`destroyable`
						+ ",'" + armor.isTradeable() + "'"				//`tradeable`
						+ ",'" + armor.isDepositable() + "'"			//`depositable`
						+ ",'" + (armor.getEnchant4Skill() == null ? "0-0" : armor.getEnchant4Skill().getId() + "-" + armor.getEnchant4Skill().getLevel()) + "'"	//`enchant4_skill`
						+ ",'" + str(armor.getSkills(), "0-0;") + "'"	//`skill`
						+ ",'" + str(armor.getIcon(), "") + "'"			//`icon` //[JOJO] add
						+ ")";
					out.write(sql);
					++count;
				}
				out.write(";\n");
				out.close();
				////////////////////////////////////////////////////
				// etcitem
				////////////////////////////////////////////////////
				activeChar.sendMessage(FILE_ETCITEM);
				out = Util.utf8BufferedWriter(FILE_ETCITEM);
				count = 0;
				for (int id : ItemTable.getInstance().getAllEtcId())
				{
					if (count == 0)
						out.write("DROP TABLE IF EXISTS `" + TABLE_ETCITEM + "`;\n"
								+ "CREATE TABLE IF NOT EXISTS `" + TABLE_ETCITEM + "` (\n"
								+ "  `item_id` smallint(5) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `name` varchar(100) NOT NULL DEFAULT '',\n"
								+ "  `additionalname` varchar(100) NOT NULL DEFAULT '',\n"
								+ "  `crystallizable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `item_type` varchar(26) NOT NULL DEFAULT 'none',\n"					//[JOJO] modify varchar(14) --> varchar(26)
								+ "  `weight` smallint(4) NOT NULL DEFAULT '0',\n"
								+ "  `consume_type` enum('normal','stackable','asset') NOT NULL DEFAULT 'normal',\n"
								+ "  `material` varchar(19) NOT NULL DEFAULT 'wood',\n"						//[JOJO] modify varchar(11) --> varchar(19)
								+ "  `crystal_type` enum('none','d','c','b','a','s','s80','s84') NOT NULL DEFAULT 'none',\n"
								+ "  `duration` mediumint(5) NOT NULL DEFAULT '-1', -- duration in minutes for shadow items\n"
								+ "  `time` mediumint(5) NOT NULL DEFAULT '-1',     -- duration in minutes for time limited items\n"
								+ "  `price` int(10) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `crystal_count` smallint(4) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `sellable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `dropable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `destroyable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `tradeable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `depositable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `handler` varchar(70) NOT NULL DEFAULT 'none',\n"
								+ "  `skill` varchar(100) NOT NULL DEFAULT '0-0;',\n"	//[JOJO] modify varchar(70) --> varchar(100)
								+ "  `icon` varchar(64) NOT NULL DEFAULT '',\n"	//[JOJO] add
								+ "  PRIMARY KEY (`item_id`)\n"
								+ ") DEFAULT CHARSET=utf8;\n"
								+ "\n"
								+ "INSERT INTO `" + TABLE_ETCITEM + "` VALUES\n");
					else if (count % SPLIT == 0)
						out.write(";\nINSERT INTO `" + TABLE_ETCITEM + "` VALUES\n");
					else
						out.write(",\n");
					L2EtcItem etcitem = (L2EtcItem)ItemTable.getInstance().getTemplate(id);
					String sql = "(" + etcitem.getItemId()				//`item_id`
						+ ",'" + escape(etcitem.getName()) + "'"		//`name`
						+ ",''"				//TODO: `additionalname`
						+ ",'" + etcitem.isCrystallizable() + "'"		//`crystallizable`
						+ ",'" + etcitem.getItemType().toString() + "'"	//`item_type`
						+ ","  + etcitem.getWeight()					//`weight`
						+ ",'" + getConsumeType(etcitem) + "'"			//`consume_type`
						+ ",'" + materials.get(etcitem.getMaterialType()) + "'"		//`material`
						+ ",'" + crystalTypes.get(etcitem.getCrystalType()) + "'"	//`crystal_type`
						+ ","  + etcitem.getDuration()					//`duration`
						+ ","  + etcitem.getTime()						//`time`
						+ ","  + getPrice(etcitem)						//`price`
						+ ","  + etcitem.getCrystalCount()				//`crystal_count`
						+ ",'" + etcitem.isSellable() + "'"				//`sellable`
						+ ",'" + etcitem.isDropable() + "'"				//`dropable`
						+ ",'" + etcitem.isDestroyable() + "'"			//`destroyable`
						+ ",'" + etcitem.isTradeable() + "'"			//`tradeable`
						+ ",'" + etcitem.isDepositable() + "'"			//`depositable`
						+ ",'" + str(etcitem.getHandlerName(), "none") + "'"	//`handler`
						+ ",'" + str(etcitem.getSkills(), "0-0;") + "'"	//`skill`
						+ ",'" + str(etcitem.getIcon(), "") + "'"		//`icon` //[JOJO] add
						+ ")";
					out.write(sql);
					++count;
				}
				out.write(";\n");
				out.close();
				////////////////////////////////////////////////////
				// weapon
				////////////////////////////////////////////////////
				activeChar.sendMessage(FILE_WEAPON);
				out = Util.utf8BufferedWriter(FILE_WEAPON);
				count = 0;
				for (int id : ItemTable.getInstance().getAllWeaponsId())
				{
					if (count == 0)
						out.write("DROP TABLE IF EXISTS `" + TABLE_WEAPON + "`;\n"
								+ "CREATE TABLE IF NOT EXISTS `" + TABLE_WEAPON + "` (\n"
								+ "  `item_id` smallint(5) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `name` varchar(120) NOT NULL DEFAULT '0',\n"
								+ "  `additionalname` varchar(120) NOT NULL DEFAULT '',\n"
								+ "  `bodypart` varchar(15) NOT NULL DEFAULT 'none',\n"
								+ "  `crystallizable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `weight` smallint(4) NOT NULL DEFAULT '0',\n"
								+ "  `soulshots` tinyint(1) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `spiritshots` tinyint(1) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `material` varchar(11) NOT NULL DEFAULT 'wood',\n"
								+ "  `crystal_type` varchar(4) NOT NULL DEFAULT 'none',\n"
								+ "  `p_dam` smallint(4) NOT NULL DEFAULT '0',\n"
								+ "  `rnd_dam` smallint(3) NOT NULL DEFAULT '0',\n"
								+ "  `weaponType` varchar(11) NOT NULL DEFAULT 'none',\n"	//[JOJO] modify varchar(10) --> varchar(11)
								+ "  `critical` smallint(3) NOT NULL DEFAULT '0',\n"
								+ "  `hit_modify` tinyint(2) NOT NULL DEFAULT '0',\n"
								+ "  `avoid_modify` tinyint(2) NOT NULL DEFAULT '0',\n"
								+ "  `shield_def` smallint(4) NOT NULL DEFAULT '0',\n"
								+ "  `shield_def_rate` smallint(3) NOT NULL DEFAULT '0',\n"
								+ "  `atk_speed` smallint(4) NOT NULL DEFAULT '0',\n"
								+ "  `mp_consume` tinyint(1) NOT NULL DEFAULT '0',\n"
								+ "  `m_dam` smallint(4) NOT NULL DEFAULT '0',\n"
								+ "  `duration` mediumint(5) NOT NULL DEFAULT '-1', -- duration in minutes for shadow items\n"
								+ "  `time` mediumint(5) NOT NULL DEFAULT '-1',     -- duration in minutes for time limited items\n"
								+ "  `price` int(10) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `crystal_count` smallint(4) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `sellable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `dropable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `destroyable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `tradeable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `depositable` enum('true','false') NOT NULL DEFAULT 'false',\n"
								+ "  `enchant4_skill_id` smallint(5) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `enchant4_skill_lvl` tinyint(2) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `onCast_skill_id` smallint(5) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `onCast_skill_lvl` tinyint(2) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `onCast_skill_chance` smallint(3) NOT NULL DEFAULT '0',\n"
								+ "  `onCrit_skill_id` smallint(5) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `onCrit_skill_lvl` tinyint(2) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `onCrit_skill_chance` smallint(3) NOT NULL DEFAULT '0',\n"
								+ "  `change_weaponId` smallint(5) unsigned NOT NULL DEFAULT '0',\n"
								+ "  `skill` varchar(100) NOT NULL DEFAULT '0-0;',\n"	//[JOJO] modify varchar(70) --> varchar(100)
								+ "  `icon` varchar(64) NOT NULL DEFAULT '',\n"	//[JOJO] add
								+ "  PRIMARY KEY (`item_id`)\n"
								+ ") DEFAULT CHARSET=utf8;\n"
								+ "\n"
								+ "INSERT INTO `" + TABLE_WEAPON + "` VALUES\n");
					else if (count % SPLIT == 0)
						out.write(";\nINSERT INTO `" + TABLE_WEAPON + "` VALUES\n");
					else
						out.write(",\n");
					L2Weapon weapon = (L2Weapon)ItemTable.getInstance().getTemplate(id);
					String sql = "(" + weapon.getItemId()					//`item_id`
						+ ",'" + escape(weapon.getName()) + "'"				//`name`
						+ ",''"				//TODO: `additionalname`
						+ ",'" + slots.get(weapon.getBodyPart()) + "'"		//`bodypart`
						+ ",'" + weapon.isCrystallizable() + "'"			//`crystallizable`
						+ ","  + weapon.getWeight()							//`weight`
						+ ","  + weapon.getSoulShotCount()					//`soulshots`
						+ ","  + weapon.getSpiritShotCount()				//`spiritshots`
						+ ",'" + materials.get(weapon.getMaterialType()) + "'"		//`material`
						+ ",'" + crystalTypes.get(weapon.getCrystalType()) + "'"	//`crystal_type`
						+ ",0"				//TODO: `p_dam`
						+ ","  + weapon.getRandomDamage()					//`rnd_dam`
						+ ",'" + weapon.getItemType().toString() + "'"		//`weaponType`
						+ ",0"				//TODO: `critical`
						+ ",0"				//TODO: `hit_modify`
						+ ",0"				//TODO: `avoid_modify`
						+ ",0"				//TODO: `shield_def`
						+ ",0"				//TODO: `shield_def_rate`
						+ ",0"				//TODO: `atk_speed`
						+ ","  + weapon.getMpConsume()						//`mp_consume`
						+ ",0"				//TODO: `m_dam`
						+ ","  + weapon.getDuration()						//`duration`
						+ ","  + weapon.getTime()							//`time`
						+ ","  + getPrice(weapon)							//`price`
						+ ","  + weapon.getCrystalCount()					//`crystal_count`
						+ ",'" + weapon.isSellable() + "'"					//`sellable`
						+ ",'" + weapon.isDropable() + "'"					//`dropable`
						+ ",'" + weapon.isDestroyable() + "'"				//`destroyable`
						+ ",'" + weapon.isTradeable() + "'"					//`tradeable`
						+ ",'" + weapon.isDepositable() + "'"				//`depositable`
						+ ","  + (weapon.getEnchant4Skill() == null ? "0" : weapon.getEnchant4Skill().getId())		//`enchant4_skill_id`
						+ ","  + (weapon.getEnchant4Skill() == null ? "0" : weapon.getEnchant4Skill().getLevel())	//`enchant4_skill_lvl`
						+ ",0"				//TODO: `onCast_skill_id`
						+ ",0"				//TODO: `onCast_skill_lvl`
						+ ",0"				//TODO: `onCast_skill_chance`
						+ ",0"				//TODO: `onCrit_skill_id`
						+ ",0"				//TODO: `onCrit_skill_lvl`
						+ ",0"				//TODO: `onCrit_skill_chance`
						+ ","  + weapon.getChangeWeaponId()					//`change_weaponId`
						+ ",'" + str(weapon.getSkills(), "0-0;") + "'"		//`skill`
						+ ",'" + str(weapon.getIcon(), "") + "'"			//`icon` //[JOJO] add
						+ ")";
					out.write(sql);
					++count;
				}
				out.write(";\n");
				out.close();
				activeChar.sendMessage("Done.");
			}
			catch (IOException e)
			{
				e.printStackTrace();
			}
		}
		return true;
	}
	
	private float getPrice(L2Item item)
	{
	//	return item.isConsumable() ? item.getReferencePrice() / Config.RATE_CONSUMABLE_COST : item.getReferencePrice();
		return item.getReferencePrice();
	//	return item._referencePrice;		//TODO:
	}
	
	private String str(String s, String empty)
	{
		if (s == null)
			return empty;
		return escape(s);
	}
	
	private String escape(String s)
	{
		return s.replace("'", "\\'");
	}
	
	private String str(SkillHolder[] skills, String empty)
	{
		if (skills == null)
			return empty;
		StringBuilder sb = new StringBuilder(100);
		for (SkillHolder s : skills)
			sb.append(s.getSkillId()).append('-').append(s.getSkillLvl()).append(';');
		return sb.length() > 0 ? sb.toString() : empty;
	}
	
	private String getConsumeType(L2EtcItem item)
	{
		switch (item.getItemId())
		{
			case 57:	//Adena
			case 5575:	//Ancient Adena
			case 6360:	//Blue Seal Stone
			case 6361:	//Green Seal Stone
			case 6362:	//Red Seal Stone
			return "asset";
		}
		return item.isStackable() ? "stackable" : "normal";
	}
	
	@Override
	public String[] getAdminCommandList()
	{
		return ADMIN_COMMANDS;
	}
}
