package varekd.io;

import java.net.InetSocketAddress;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import varekd.model.Inventory;
import varekd.model.Point;
import varekd.model.Player;
import varekd.model.InvItem;
import varekd.model.PlayerAppearance;
import varekd.model.World;
import varekd.util.Logger;
import varekd.model.BankItem;
import varekd.Server;
import varekd.model.Bank;

/**
 * Coded at 1AM by a very pissed off varek.
 * So don't expect miracles.
 * WARNING: NOT THREAD SAFE.
 */
public class DBPlayerLoader implements PlayerLoader {
	// Statements for saving/loading playerz.
	private PreparedStatement queryPlayer,  queryInv,  queryBank,  querySettings,  queryStat,  queryList,  updatePlayer,  updateInv,  updateBank,  updateStat,  clearList,  saveList,  updateAppearance,  updateSettings,  insertSuspicious;
	// This shit is whack. Blocks people who try and log in too fast.
	private Map<String, BadPassword> attempts = new HashMap<String, BadPassword>();

	public DBPlayerLoader() throws SQLException, ClassNotFoundException {
		loadStatements();
	}

	public void loadStatements() throws SQLException {
		Connection c = DBConnection.getConnection("varekd");
		queryPlayer = c.prepareStatement("SELECT * FROM player INNER JOIN appearance ON appearance.playerid = player.id " +
				"WHERE player.username=? AND player.password=SHA1(?)");

		queryInv = c.prepareStatement("SELECT itemid, amount, wielded FROM invitem WHERE playerid=? " +
				"ORDER BY position");
		queryBank = c.prepareStatement("SELECT itemid, amount FROM bankitem WHERE playerid=? " +
				"ORDER BY position");
		querySettings = c.prepareStatement("SELECT * FROM setting WHERE playerid=? AND type=?");
		queryStat = c.prepareStatement("SELECT num, cur, exp FROM stat WHERE playerid=? " +
				"ORDER BY num");
		queryList = c.prepareStatement("SELECT playerhash, type FROM playerlist WHERE playerid=?");
		updatePlayer = c.prepareStatement("UPDATE player SET " +
				"x=?, y=?, fightmode=?, lastlogin=?, lastip=?, lastskulled=?, " +
				"changingappearance=?, male=?, combatlevel=? WHERE id=?");
		updateInv = c.prepareStatement("UPDATE invitem SET itemid=?, amount=?, wielded=? " +
				"WHERE playerid=? AND position=?");
		updateBank = c.prepareStatement("UPDATE bankitem SET itemid=?, amount=? " +
				"WHERE playerid=? AND position=?");
		updateStat = c.prepareStatement("UPDATE stat SET cur=?, exp=? " +
				"WHERE playerid=? AND num=?");
		clearList = c.prepareStatement("DELETE FROM playerlist WHERE playerid=?");
		saveList = c.prepareStatement("INSERT INTO playerlist (playerid, playerhash, type) " +
				"VALUES (?, ?, ?)");
		updateAppearance = c.prepareStatement("UPDATE appearance SET " +
				"haircolour=?, topcolour=?, trousercolour=?, skincolour=?, head=?, body=? " +
				"WHERE playerid=?");
		updateSettings = c.prepareStatement("UPDATE setting SET " +
				"bool1=?, bool2=?, bool3=?, bool4=? WHERE type=? AND playerid=?");
		insertSuspicious = c.prepareStatement("INSERT INTO suspiciousevent (playerid, value) VALUES (?, ?)");
	}

	/**
	 * This is a bit of a hack. In case the connections are somehow closed, this method will check that they're okay
	 * and if they're not okay it re-loads them.
	 */
	private static boolean checkStatements(PreparedStatement... stmnts) {
		for (PreparedStatement ps : stmnts) {
			try {
				if (ps == null || ps.getConnection().isClosed()) {
					return false;
				}
			} catch (SQLException sqe) {
				return false;
			}
		}
		return true;
	}

	public byte load(Player p) {
		try {
			return load0(p);
		} catch (Exception e) {
			Logger.err("Error loading " + p.getUsername());
			Logger.err(e);
		}
		return (byte) 17;
	}

	public synchronized byte load0(Player p) throws SQLException {
		String ip = ((InetSocketAddress) p.getSession().getRemoteAddress()).getAddress().getHostAddress();
		Server.getServer().getThrottleFilter().connectionOk(p.getSession());
		if (attempts.containsKey(ip)) {
			BadPassword bp = attempts.get(ip);
			if (bp.nextAttempt > 0) {
				if (System.currentTimeMillis() > bp.nextAttempt) {
					attempts.remove(ip);
				} else {
					return (byte) 7;
				}
			}
		}
		if (!checkStatements(queryPlayer, queryInv, queryBank, querySettings, queryStat, queryList)) {
			loadStatements();
		}
		queryPlayer.setString(1, p.getUsername());
		queryPlayer.setString(2, p.getPassword());
		ResultSet resultPlayer = queryPlayer.executeQuery();
		if (!resultPlayer.next()) {
			BadPassword bp = attempts.get(ip);
			if (bp == null || System.currentTimeMillis() - 10 * 60 * 1000 > bp.lastAttempt) {
				bp = new BadPassword();
				attempts.put(ip, bp);
			}
			bp.attempt++;
			bp.lastAttempt = System.currentTimeMillis();
			if (bp.attempt >= Server.getServer().getConf().getInt("security.badpasswordcount")) {
				bp.nextAttempt = System.currentTimeMillis() + Server.getServer().getConf().getInt("security.badpassworddelay");
			}
			return 0x3; //no such user/password
		} else {
			//p.setGroup(resultPlayer.getByte("group"));
			if (World.getWorld().getPlayer(p.getUsernameHash()) != null) {
				return 0x4; //user already logged in
			}
			p.setId(resultPlayer.getInt("id"));
			p.setLocation(Point.location(resultPlayer.getInt("x"), resultPlayer.getInt("y")), true);
			p.setFightMode(resultPlayer.getInt("fightmode"));
			p.setLastLogin(System.currentTimeMillis());
			p.setLastIP(resultPlayer.getString("lastip"));
			p.setLastSkulled(resultPlayer.getLong("lastskulled"));
			p.setChangingAppearance(resultPlayer.getBoolean("changingappearance"));
			p.setMale(resultPlayer.getBoolean("male"));

			//Inventory
			Inventory i = new Inventory(p);
			queryInv.setInt(1, resultPlayer.getInt("id"));
			ResultSet resultInv = queryInv.executeQuery();
			while (resultInv.next()) {
				int id = resultInv.getInt(1);
				int amount = resultInv.getInt(2);
				if (id == -1) {
					break;
				}
				InvItem it = new InvItem(id, amount);
				it.setWield(resultInv.getBoolean(3));
				i.add(it);
			}
			p.setInventory(i);

			//Bank
			Bank b = new Bank();
			queryBank.setInt(1, p.getId());
			ResultSet resultBank = queryBank.executeQuery();
			while (resultBank.next()) {
				int id = resultBank.getInt(1);
				int amount = resultBank.getInt(2);
				if (id == -1) {
					break;
				}
				BankItem it = new BankItem(id, amount);
				b.addItem(it);
			}
			p.setBank(b);

			//Settings
			//querySettings.setInt(1, p.getId());
			//querySettings.setString(2, "game");
			//ResultSet gameSettings = //querySettings.executeQuery();
			//gameSettings.next();
			//p.setGameSetting(0, //gameSettings.getBoolean("bool1"));
			//p.setGameSetting(1, //gameSettings.getBoolean("bool2"));
			//p.setGameSetting(2, //gameSettings.getBoolean("bool3"));
			//p.setGameSetting(3, //gameSettings.getBoolean("bool4"));
			//querySettings.setString(2, "privacy");
			//ResultSet privacySettings = //querySettings.executeQuery();
			//privacySettings.next();
			//p.setPrivacySetting(0, //privacySettings.getBoolean("bool1"));
			//p.setPrivacySetting(1, //privacySettings.getBoolean("bool2"));
			//p.setPrivacySetting(2, //privacySettings.getBoolean("bool3"));
			//p.setPrivacySetting(3, //privacySettings.getBoolean("bool4"));

			//Stats
			queryStat.setInt(1, p.getId());
			ResultSet resultStat = queryStat.executeQuery();
			while (resultStat.next()) {

				int stat = resultStat.getInt(1);
				System.out.println("stat: " + stat + ", cur: " + resultStat.getInt(2) + ", exp: " + resultStat.getLong(3));
				p.setCurStat(stat, resultStat.getInt(2));
				p.setExp(stat, resultStat.getLong(3));
			}

			queryList.setInt(1, p.getId());
			List<Long> friendsList = new ArrayList<Long>(), ignoreList = new ArrayList<Long>();
			ResultSet resultList = queryList.executeQuery();
			while (resultList.next()) {
				long playerHash = resultList.getLong(1);
				String type = resultList.getString(2);
				if (type.equals("ignore")) {
					ignoreList.add(playerHash);
				} else if (type.equals("friend")) {
					friendsList.add(playerHash);
				}
			}
			p.setFriendList(friendsList);
			p.setIgnoreList(ignoreList);

			//Appearance
			PlayerAppearance pa = new PlayerAppearance(resultPlayer.getInt("haircolour"), resultPlayer.getInt("topcolour"),
					resultPlayer.getInt("trousercolour"), resultPlayer.getInt("skincolour"), resultPlayer.getInt("head"),
					resultPlayer.getInt("body"));
			p.setWornItems(pa.getSprites());
			p.setAppearance(pa);

			Server.getServer().getThrottleFilter().acceptedLogin(p.getSession());
			return (p.getGroup() == 2 ? (byte) 0 : (p.getGroup() == 1 ? (byte) 24 : 0x00));
		}
	}

	public void save(Player p) {
		try {
			save0(p);
		} catch (Exception e) {
			Logger.err("Error saving " + p.getUsername());
			Logger.err(e);
		}
	}

	public synchronized void save0(Player p) throws SQLException {
		int i = 0, x = 0, y = 0;
		try {
			x = p.getLocation().getX();
			y = p.getLocation().getY();
		} catch (Exception e) {
		}
		if (!checkStatements(updatePlayer, updateInv, updateBank, updateStat, clearList, saveList, updateAppearance, updateSettings)) {
			loadStatements();
		}
		updatePlayer.setInt(++i, x);
		updatePlayer.setInt(++i, y);
		updatePlayer.setInt(++i, p.getFightMode());
		updatePlayer.setLong(++i, p.getLastLogin());
		updatePlayer.setString(++i, p.getLastIP());
		updatePlayer.setLong(++i, p.getLastSkulled());
		updatePlayer.setBoolean(++i, p.isChangingAppearance());
		updatePlayer.setBoolean(++i, p.isMale());
		updatePlayer.setInt(++i, p.getCombatLevel());
		updatePlayer.setInt(++i, p.getId());
		updatePlayer.executeUpdate();

		updateInv.setInt(4, p.getId());
		for (int slot = 0; slot < 30; slot++) {
			InvItem it = p.getInventory().get(slot);
			updateInv.setInt(1, it == null ? -1 : it.getId());
			updateInv.setInt(2, it == null ? -1 : it.getAmount());
			updateInv.setBoolean(3, it == null ? false : it.isWielded());
			updateInv.setInt(5, slot);
			updateInv.addBatch();
		}
		updateInv.executeBatch();

		updateBank.setInt(3, p.getId());
		for (int slot = 0; slot < 256; slot++) {
			BankItem it = p.getBank().getItem(slot);
			updateBank.setInt(1, it == null ? -1 : it.getId());
			updateBank.setInt(2, it == null ? -1 : it.getAmount());
			updateBank.setInt(4, slot);
			updateBank.addBatch();
		}
		updateBank.executeBatch();

		updateStat.setInt(3, p.getId());
		for (int i1 = 0; i1 < 18; i1++) {
			updateStat.setInt(1, p.getCurStat(i1));
			updateStat.setLong(2, p.getExp(i1));
			updateStat.setInt(4, i1);
			updateStat.addBatch();
		}
		updateStat.executeBatch();

		clearList.setInt(1, p.getId());
		clearList.executeUpdate();

		saveList.setInt(1, p.getId());
		for (long friend : p.getFriendList()) {
			saveList.setLong(2, friend);
			saveList.setString(3, "friend");
			saveList.addBatch();
		}
		saveList.executeBatch();
		for (long ignore : p.getIgnoreList()) {
			saveList.setLong(2, ignore);
			saveList.setString(3, "ignore");
			saveList.addBatch();
		}
		saveList.executeBatch();

		PlayerAppearance pa = p.getAppearance();
		updateAppearance.setInt(1, pa.getHairColour());
		updateAppearance.setInt(2, pa.getTopColour());
		updateAppearance.setInt(3, pa.getTrouserColour());
		updateAppearance.setInt(4, pa.getSkinColour());
		updateAppearance.setInt(5, pa.getHead());
		updateAppearance.setInt(6, pa.getBody());
		updateAppearance.setInt(7, p.getId());
		updateAppearance.executeUpdate();

		updateSettings.setInt(6, p.getId());
		updateSettings.setString(5, "game");
		for (int n = 0; n < 4; n++) {
			updateSettings.setBoolean(n + 1, p.getGameSetting(n));
		}
		updateSettings.addBatch();
		updateSettings.setString(5, "privacy");
		for (int n = 0; n < 4; n++) {
			updateSettings.setBoolean(n + 1, p.getPrivacySetting(n));
		}
		updateSettings.addBatch();
		updateSettings.executeBatch();
	}

	static class BadPassword {

		int attempt = 0;
		long nextAttempt, lastAttempt;
	}
}