package net.ess3.protect.data;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;


public class ProtectedBlockMySQL extends ProtectedBlockJDBC
{
	public ProtectedBlockMySQL(final String url, final String username, final String password) throws PropertyVetoException
	{
		super("com.mysql.jdbc.Driver", url, username, password);
	}
	private static final String QueryCreateTable =
								"CREATE TABLE IF NOT EXISTS `EssentialsProtect` ("
								+ "`worldName` varchar(60) NOT NULL,"
								+ "`x` int(11) NOT NULL, `y` int(11) NOT NULL, `z` int(11) NOT NULL,"
								+ "`playerName` varchar(150) DEFAULT NULL,"
								+ "KEY `pos` (`worldName`,`x`,`z`,`y`)"
								+ ") ENGINE=MyISAM DEFAULT CHARSET=utf8";

	@Override
	protected PreparedStatement getStatementCreateTable(final Connection conn) throws SQLException
	{
		return conn.prepareStatement(QueryCreateTable);
	}
	private static final String QueryUpdateFrom2_0TableCheck =
								"SHOW COLUMNS FROM `EssentialsProtect` LIKE 'id';";
	private static final String QueryUpdateFrom2_0Table =
								"ALTER TABLE `EssentialsProtect` "
								+ "CHARACTER SET = utf8, ENGINE = MyISAM,"
								+ "DROP COLUMN `id`,"
								+ "CHANGE COLUMN `playerName` `playerName` VARCHAR(150) NULL AFTER `z`,"
								+ "CHANGE COLUMN `worldName` `worldName` VARCHAR(60) NOT NULL,"
								+ "ADD INDEX `position` (`worldName` ASC, `x` ASC, `z` ASC, `y` ASC),"
								+ "DROP PRIMARY KEY ;";

	@Override
	protected PreparedStatement getStatementUpdateFrom2_0Table(final Connection conn) throws SQLException
	{
		PreparedStatement testPS = null;
		ResultSet testRS = null;
		try
		{
			testPS = conn.prepareStatement(QueryUpdateFrom2_0TableCheck);
			testRS = testPS.executeQuery();
			if (testRS.first())
			{
				return conn.prepareStatement(QueryUpdateFrom2_0Table);
			}
			else
			{
				return conn.prepareStatement("SELECT 1;");
			}
		}
		finally
		{
			if (testRS != null)
			{
				try
				{
					testRS.close();
				}
				catch (SQLException ex)
				{
					Logger.getLogger(ProtectedBlockMySQL.class.getName()).log(Level.SEVERE, null, ex);
				}
			}
			if (testPS != null)
			{
				try
				{
					testPS.close();
				}
				catch (SQLException ex)
				{
					Logger.getLogger(ProtectedBlockMySQL.class.getName()).log(Level.SEVERE, null, ex);
				}
			}
		}
	}
	private static final String QueryDeleteAll = "DELETE FROM EssentialsProtect;";

	@Override
	protected PreparedStatement getStatementDeleteAll(final Connection conn) throws SQLException
	{
		return conn.prepareStatement(QueryDeleteAll);
	}
	private static final String QueryInsert =
								"INSERT INTO EssentialsProtect (worldName, x, y, z, playerName) VALUES (?, ?, ?, ?, ?);";

	@Override
	protected PreparedStatement getStatementInsert(final Connection conn, final String world, final int x, final int y, final int z,
												   final String playerName) throws SQLException
	{
		final PreparedStatement ps = conn.prepareStatement(QueryInsert);
		ps.setString(1, world);
		ps.setInt(2, x);
		ps.setInt(3, y);
		ps.setInt(4, z);
		ps.setString(5, playerName);
		return ps;
	}
	private static final String QueryCountByPlayer =
								"SELECT COUNT(playerName), SUM(playerName = ?) FROM EssentialsProtect "
								+ "WHERE worldName = ? AND x = ? AND y = ? AND z = ? GROUP BY x;";

	@Override
	protected PreparedStatement getStatementPlayerCountByLocation(final Connection conn, final String world, final int x, final int y, final int z,
																  final String playerName) throws SQLException
	{
		final PreparedStatement ps = conn.prepareStatement(QueryCountByPlayer);
		ps.setString(1, playerName);
		ps.setString(2, world);
		ps.setInt(3, x);
		ps.setInt(4, y);
		ps.setInt(5, z);
		return ps;
	}
	private static final String QueryPlayersByLocation =
								"SELECT playerName FROM EssentialsProtect WHERE worldname = ? AND x = ? AND y = ? AND z = ?;";

	@Override
	protected PreparedStatement getStatementPlayersByLocation(final Connection conn, final String world,
															  final int x, final int y, final int z) throws SQLException
	{
		final PreparedStatement ps = conn.prepareStatement(QueryPlayersByLocation);
		ps.setString(1, world);
		ps.setInt(2, x);
		ps.setInt(3, y);
		ps.setInt(4, z);
		return ps;
	}
	private static final String QueryDeleteByLocation =
								"DELETE FROM EssentialsProtect WHERE worldName = ? AND x = ? AND y = ? AND z = ?;";

	@Override
	protected PreparedStatement getStatementDeleteByLocation(final Connection conn, final String world,
															 final int x, final int y, final int z) throws SQLException
	{
		final PreparedStatement ps = conn.prepareStatement(QueryDeleteByLocation);
		ps.setString(1, world);
		ps.setInt(2, x);
		ps.setInt(3, y);
		ps.setInt(4, z);
		return ps;
	}
	private static final String QueryAllBlocks =
								"SELECT worldName, x, y, z, playerName FROM EssentialsProtect;";

	@Override
	protected PreparedStatement getStatementAllBlocks(final Connection conn) throws SQLException
	{
		return conn.prepareStatement(QueryAllBlocks);
	}
}