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

package handlers.admincommandhandlers;

import java.io.BufferedWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

import com.l2jserver.gameserver.datatables.NpcData;
import com.l2jserver.gameserver.handler.IAdminCommandHandler;
import com.l2jserver.gameserver.model.actor.instance.L2PcInstance;
import com.l2jserver.gameserver.model.actor.templates.L2NpcTemplate;
import com.l2jserver.gameserver.model.stats.MoveType;
import com.l2jserver.util.Util;

public class AdminNpcsToSQL implements IAdminCommandHandler
{
	private static final String TABLE_NPC = "npc";
	private static final String TABLE_NPCAIDATA = "npcaidata";
	@SuppressWarnings("unused") private static final String TABLE_NPCSKILLS = "npcskills";
	@SuppressWarnings("unused") private static final String TABLE_NPC_ELEMENTALS = "npc_elementals";
	@SuppressWarnings("unused") private static final String TABLE_DROPLIST = "droplist";
	
	private static final String CD = "log/";
	private static final String FILE_NPC = CD + "npc.sql";
	private static final String FILE_NPCAIDATA = CD + "npcaidata.sql";
	@SuppressWarnings("unused") private static final String FILE_NPCSKILLS = CD + "npcskills.sql";
	@SuppressWarnings("unused") private static final String FILE_NPC_ELEMENTALS = CD + "npc_elementals.sql";
	@SuppressWarnings("unused") private static final String FILE_DROPLIST = CD + "droplist.sql";
	
	private static final int SPLIT = 500;
	
	private static final String[] ADMIN_COMMANDS =
	{
		"admin_npcs_to_sql"
	};
	
	@Override
	public boolean useAdminCommand(String command, L2PcInstance activeChar)
	{
		if (command.equals("admin_npcs_to_sql"))
		{
			ArrayList<L2NpcTemplate> npcs = NpcData.getInstance().toArrayList();
			Collections.sort(npcs, new Comparator<L2NpcTemplate>(){
				@Override
				public int compare(L2NpcTemplate o1, L2NpcTemplate o2)
				{
					return o1.getNpcId() - o2.getNpcId();
				}});
			
			npc(npcs, activeChar);
			npcaidata(npcs, activeChar);
			npcskills(npcs, activeChar);
			npc_elementals(npcs, activeChar);
			droplist(npcs, activeChar);
			
			activeChar.sendMessage("Done.");
		}
		return true;
	}
	
	////////////////////////////////////////////////////
	// npc
	////////////////////////////////////////////////////
	private void npc(ArrayList<L2NpcTemplate> npcs, L2PcInstance activeChar)
	{
		try (BufferedWriter out = Util.utf8BufferedWriter(FILE_NPC))
		{
			int count = 0;
			activeChar.sendMessage(FILE_NPC);
			
			for (L2NpcTemplate npc : npcs)
			{
				if (count == 0)
					out.write("DROP TABLE IF EXISTS `" + TABLE_NPC + "`;\n"
						+ "CREATE TABLE `" + TABLE_NPC + "`(\n"
						+ "  `id` smallint(5) unsigned NOT NULL,\n"
						+ "  `idTemplate` smallint(5) unsigned NOT NULL,\n"
						+ "  `name` varchar(200) NOT NULL DEFAULT '',\n"
						+ "  `serverSideName` tinyint(1) NOT NULL DEFAULT '0',\n"
						+ "  `title` varchar(45) NOT NULL DEFAULT '',\n"
						+ "  `serverSideTitle` tinyint(1) NOT NULL DEFAULT '0',\n"
						+ "  `class` varchar(200) DEFAULT NULL,\n"
						+ "  `collision_radius` decimal(6,2) DEFAULT NULL,\n"
						+ "  `collision_height` decimal(6,2) DEFAULT NULL,\n"
						+ "  `level` tinyint(2) DEFAULT NULL,\n"
						+ "  `sex` enum('etc','female','male') NOT NULL DEFAULT 'etc',\n"
						+ "  `type` varchar(22) DEFAULT NULL,\n"
						+ "  `attackrange` smallint(4) DEFAULT NULL,\n"
						+ "  `hp` decimal(30,15) DEFAULT NULL,\n"
						+ "  `mp` decimal(30,15) DEFAULT NULL,\n"
						+ "  `hpreg` decimal(30,15) DEFAULT NULL,\n"
						+ "  `mpreg` decimal(30,15) DEFAULT NULL,\n"
						+ "  `str` tinyint(2) NOT NULL DEFAULT '40',\n"
						+ "  `con` tinyint(2) NOT NULL DEFAULT '43',\n"
						+ "  `dex` tinyint(2) NOT NULL DEFAULT '30',\n"
						+ "  `int` tinyint(2) NOT NULL DEFAULT '21',\n"
						+ "  `wit` tinyint(2) NOT NULL DEFAULT '20',\n"
						+ "  `men` tinyint(2) NOT NULL DEFAULT '20',\n"
						+ "  `exp` int(9) NOT NULL DEFAULT '0',\n"
						+ "  `sp` int(9) NOT NULL DEFAULT '0',\n"
						+ "  `patk` decimal(12,5) DEFAULT NULL,\n"
						+ "  `pdef` decimal(12,5) DEFAULT NULL,\n"
						+ "  `matk` decimal(12,5) DEFAULT NULL,\n"
						+ "  `mdef` decimal(12,5) DEFAULT NULL,\n"
						+ "  `atkspd` smallint(4) NOT NULL DEFAULT '230',\n"
						+ "  `critical` tinyint(1) NOT NULL DEFAULT '1',\n"
						+ "  `matkspd` smallint(4) NOT NULL DEFAULT '333',\n"
						+ "  `rhand` smallint(5) unsigned NOT NULL DEFAULT '0',\n"
						+ "  `lhand` smallint(5) unsigned NOT NULL DEFAULT '0',\n"
						+ "  `enchant` tinyint(1) NOT NULL DEFAULT '0',\n"
						+ "  `walkspd` decimal(10,5) NOT NULL DEFAULT '60',\n"
						+ "  `runspd` decimal(10,5) NOT NULL DEFAULT '120',\n"
						+ "  `dropHerbGroup` tinyint(1) NOT NULL DEFAULT '0',\n"
						+ "  `basestats` tinyint(1) NOT NULL DEFAULT '0',\n"
						+ "  PRIMARY KEY (`id`,`idTemplate`)\n"
						+ ") ENGINE=MyISAM DEFAULT CHARSET=utf8;\n"
						+ "\n"
						+ "INSERT INTO `" + TABLE_NPC + "` VALUES\n");
				else if (count % SPLIT == 0)
					out.write(";\nINSERT INTO `" + TABLE_NPC + "` VALUES\n");
				else
					out.write(",\n");
				
				String sql = "(" + npc.getId()							//`id`
					+ ", "   + npc.getDisplayId()						//`idTemplate`
					+ ", \"" + escape(npc.getName()) + "\""				//`name`
					+ ", "   + str(npc.isUsingServerSideName())			//`serverSideName`
					+ ", \"" + escape(npc.getTitle()) + "\""			//`title`
					+ ", "   + str(npc.isUsingServerSideTitle())		//`serverSideTitle`
					+ ", null"											//`class`
					+ ", "   + npc.getCollisionRadius()					//`collision_radius`
					+ ", "   + npc.getCollisionHeight()					//`collision_height`
					+ ", "   + npc.getLevel()							//`level`
					+ ", \"" + npc.getSex().name()/*.toLowerCase()*/ + "\""	//`sex`
					+ ", \"" + escape(npc.getType()) + "\""				//`type`
					+ ", "   + npc.getBaseAttackRange()					//`attackrange`
					+ ", "   + npc.getBaseHpMax()						//`hp`
					+ ", "   + npc.getBaseMpMax()						//`mp`
					+ ", "   + npc.getBaseHpReg()						//`hpreg`
					+ ", "   + npc.getBaseMpReg()						//`mpreg`
					+ ", "   + npc.getBaseSTR()							//`str`
					+ ", "   + npc.getBaseCON()							//`con`
					+ ", "   + npc.getBaseDEX()							//`dex`
					+ ", "   + npc.getBaseINT()							//`int`
					+ ", "   + npc.getBaseWIT()							//`wit`
					+ ", "   + npc.getBaseMEN()							//`men`
					+ ", "   + exp(npc)									//`exp`
					+ ", "   + npc.getSP()								//`sp`
					+ ", "   + npc.getBasePAtk()						//`patk`
					+ ", "   + npc.getBasePDef()						//`pdef`
					+ ", "   + npc.getBaseMAtk()						//`matk`
					+ ", "   + npc.getBaseMDef()						//`mdef`
					+ ", "   + npc.getBasePAtkSpd()						//`atkspd`
					+ ", "   + npc.getBaseCritRate()					//`critical`
					+ ", "   + npc.getBaseMAtkSpd()						//`matkspd`
					+ ", "   + npc.getRHandId()							//`rhand`
					+ ", "   + npc.getLHandId()							//`lhand`
					+ ", "   + npc.getWeaponEnchant()					//`enchant`
					+ ", "   + npc.getBaseMoveSpeed(MoveType.WALK)		//`walkspd`
					+ ", "   + npc.getBaseMoveSpeed(MoveType.RUN)		//`runspd`
					+ ", 0"												//`dropHerbGroup`
					+ ", 0"												//`basestats`
					+ ")";
				out.write(sql);
				++count;
			}
			out.write(";\n");
			out.close();
		}
		catch (IOException e)
		{
			System.err.print(e.getMessage());
			e.printStackTrace();
		}
	}
	
	////////////////////////////////////////////////////
	// npcaidata
	////////////////////////////////////////////////////
	private void npcaidata(ArrayList<L2NpcTemplate> npcs, L2PcInstance activeChar)
	{
		try (BufferedWriter out = Util.utf8BufferedWriter(FILE_NPCAIDATA))
		{
			int count = 0;
			activeChar.sendMessage(FILE_NPCAIDATA);
			
			for (L2NpcTemplate npc : npcs)
			{
				if (count == 0)
					out.write("DROP TABLE IF EXISTS `" + TABLE_NPCAIDATA + "`;\n"
						+ "CREATE TABLE `" + TABLE_NPCAIDATA + "` (\n"
						+ "  `npcId` smallint(5) unsigned NOT NULL,\n"
						+ "  `minSkillChance` tinyint(3) unsigned NOT NULL DEFAULT '7',\n"
						+ "  `maxSkillChance` tinyint(3) unsigned NOT NULL DEFAULT '15',\n"
						+ "  `primarySkillId` smallint(5) unsigned DEFAULT '0',\n"
						+ "  `agroRange` smallint(4) unsigned NOT NULL DEFAULT '0',\n"
						+ "  `canMove` tinyint(1) unsigned NOT NULL DEFAULT '1',\n"
						+ "  `targetable` tinyint(1) unsigned NOT NULL DEFAULT '1',\n"
						+ "  `showName` tinyint(1) unsigned NOT NULL DEFAULT '1',\n"
						+ "  `minRangeSkill` smallint(5) unsigned DEFAULT '0',\n"
						+ "  `minRangeChance` tinyint(3) unsigned DEFAULT '0',\n"
						+ "  `maxRangeSkill` smallint(5) unsigned DEFAULT '0',\n"
						+ "  `maxRangeChance` tinyint(3) unsigned DEFAULT '0',\n"
						+ "  `soulShot` smallint(4) unsigned DEFAULT '0',\n"
						+ "  `spiritShot` smallint(4) unsigned DEFAULT '0',\n"
						+ "  `spsChance` tinyint(3) unsigned DEFAULT '0',\n"
						+ "  `ssChance` tinyint(3) unsigned DEFAULT '0',\n"
						+ "  `aggro` smallint(4) unsigned NOT NULL DEFAULT '0',\n"
						+ "  `isChaos` smallint(4) unsigned DEFAULT '0',\n"
						+ "  `clan` varchar(40) DEFAULT NULL,\n"
						+ "  `clanRange` smallint(4) unsigned DEFAULT '0',\n"
						+ "  `enemyClan` varchar(40) DEFAULT NULL,\n"
						+ "  `enemyRange` smallint(4) unsigned DEFAULT '0',\n"
						+ "  `dodge` tinyint(3) unsigned DEFAULT '0',\n"
						+ "  `aiType` varchar(8) NOT NULL DEFAULT 'fighter',\n"
						+ "  PRIMARY KEY (`npcId`)\n"
						+ ") ENGINE=MyISAM DEFAULT CHARSET=utf8;\n"
						+ "\n"
						+ "INSERT INTO `" + TABLE_NPCAIDATA + "` VALUES\n");
				else if (count % SPLIT == 0)
					out.write(";\nINSERT INTO `" + TABLE_NPCAIDATA + "` VALUES\n");
				else
					out.write(",\n");
				
				String sql = "(" + npc.getId()					//`npcId`
					+ ", "   + npc.getMinSkillChance()			//`minSkillChance`
					+ ", "   + npc.getMaxSkillChance()			//`maxSkillChance`
					+ ", 0"										//TODO:`primarySkillId` smallint(5) unsigned DEFAULT '0'
					+ ", "   + npc.getKnownRange()				//TODO:`agroRange` smallint(4) unsigned NOT NULL DEFAULT '0'
					+ ", "   + str(npc.canMove())				//`canMove`
					+ ", "   + str(npc.isTargetable())			//`targetable`
					+ ", "   + str(npc.isShowName())			//`showName`
					+ ", "   + npc.getShortRangeSkillId()		//TODO:`minRangeSkill` smallint(5) unsigned DEFAULT '0'
					+ ", "   + npc.getShortRangeSkillChance()	//TODO:`minRangeChance` tinyint(3) unsigned DEFAULT '0'
					+ ", "   + npc.getLongRangeSkillId()		//TODO:`maxRangeSkill` smallint(5) unsigned DEFAULT '0'
					+ ", "   + npc.getLongRangeSkillChance()	//TODO:`maxRangeChance` tinyint(3) unsigned DEFAULT '0'
					+ ", "   + npc.getSoulShot()				//`soulShot`
					+ ", "   + npc.getSpiritShot()				//`spiritShot`
					+ ", "   + npc.getSpiritShotChance()		//`spsChance`
					+ ", "   + npc.getSoulShotChance()			//`ssChance`
					+ ", "   + npc.getAggroRange()				//`aggro`
					+ ", "   + str(npc.isChaos())				//`isChaos`
					+ ", \"" + npc.getClanNames() + "\""		//`clan`
					+ ", "   + npc.getClanHelpRange()			//`clanRange`
					+ ", NULL"//", \"" + npc.getEnemyClanNames() + "\""	//TODO:`enemyClan`
					+ ", 0"										//TODO:`enemyRange` smallint(4) unsigned DEFAULT '0'
					+ ", "   + npc.getDodge()					//`dodge`
					+ ", \"" + npc.getAIType().name()/*.toLowerCase()*/ + "\""	//`aiType`
					+ ")";
				out.write(sql);
				++count;
			}
			out.write(";\n");
			out.close();
		}
		catch (IOException e)
		{
			System.err.print(e.getMessage());
			e.printStackTrace();
		}
	}
	
	////////////////////////////////////////////////////
	// npcskills
	////////////////////////////////////////////////////
	private void npcskills(ArrayList<L2NpcTemplate> npcs, L2PcInstance activeChar)
	{
/*
DROP TABLE IF EXISTS `npcskills`;
CREATE TABLE `npcskills` (
  `npcid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `skillid` smallint(5) unsigned NOT NULL DEFAULT '0',
  `level` tinyint(2) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`npcid`,`skillid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `npcskills` VALUES
-- Thomas D. Turkey
(100, 4390, 1), -- NPC Abnormal Immunity
(100, 4408, 1), -- HP Modifiers
*/
	}
	
	////////////////////////////////////////////////////
	// npc_elementals
	////////////////////////////////////////////////////
	private void npc_elementals(ArrayList<L2NpcTemplate> npcs, L2PcInstance activeChar)
	{
/*
DROP TABLE IF EXISTS `npc_elementals`;
CREATE TABLE IF NOT EXISTS `npc_elementals` (
  `npc_id` smallint(5) unsigned NOT NULL,
  `elemAtkType` tinyint(1) NOT NULL DEFAULT '-1',
  `elemAtkValue` int(3) NOT NULL DEFAULT '0',
  `fireDefValue` int(3) NOT NULL DEFAULT '0',
  `waterDefValue` int(3) NOT NULL DEFAULT '0',
  `windDefValue` int(3) NOT NULL DEFAULT '0',
  `earthDefValue` int(3) NOT NULL DEFAULT '0',
  `holyDefValue` int(3) NOT NULL DEFAULT '0',
  `darkDefValue` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`npc_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `npc_elementals` VALUES
(100, 0, 0, 20, 20, 20, 20, 20, 20), -- Thomas D. Turkey
*/
	}
	
	////////////////////////////////////////////////////
	// droplist
	////////////////////////////////////////////////////
	private void droplist(ArrayList<L2NpcTemplate> npcs, L2PcInstance activeChar)
	{
/*
DROP TABLE IF EXISTS `droplist`;
CREATE TABLE `droplist` (
  `mobId` smallint(5) unsigned NOT NULL,
  `itemId` smallint(5) unsigned NOT NULL,
  `min` int(8) unsigned NOT NULL DEFAULT '0',
  `max` int(8) unsigned NOT NULL DEFAULT '0',
  `category` smallint(3) NOT NULL DEFAULT '0',
  `chance` decimal(30,15) unsigned NOT NULL,
  PRIMARY KEY (`mobId`,`itemId`,`category`),
  KEY `key_mobId` (`mobId`)
) ENGINE=MyISAM DEFAULT CHARSET=UTF8;

INSERT INTO `droplist` VALUES
-- Huge Pig
(13031, 9142, 1, 2, 0, 1000000), -- Apiga
-- Super Huge Pig
(13034, 9142, 4, 6, 0, 1000000), -- Apiga
(13034, 9144, 1, 1, 1, 500000), -- Pirate's Booty
*/
	}
	
	private long exp(L2NpcTemplate npc)
	{
		return (long)(npc.getLevel() * npc.getLevel() * npc.getExpRate()/*  * Config.RATE_XP */);	// L2Npc.getExpReward()
	}
	
	private String str(boolean b)
	{
		return b ? "1" : "0";
	}
	
	private String escape(String s)
	{
		return s;
	//	return s.replace("'", "''");
	}
	
	@Override
	public String[] getAdminCommandList()
	{
		return ADMIN_COMMANDS;
	}
}
