package nl.avans.database.dashboard;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

import nl.avans.database.Database;

public class StatisticsDB {

	public static int getTotalScore(String username) {
		String query = "SELECT MAX(score) AS max_score FROM player WHERE username = ?";
		try {
			PreparedStatement stmt = Database.connection.prepareStatement(query);
			stmt.setString(1, username);
			ResultSet result = stmt.executeQuery();
			while (result.next()) {
				return result.getInt("max_score");

			}
			stmt.close();
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();

		}
		return 0;
	}

	public static HashMap<String, Integer> getWinsAndLosses(String username) {
		HashMap<String, Integer> results = new HashMap<>();
		int win = 0;
		int loss = 0;

		String query = "SELECT (SELECT IF(username=p.username,'Yes','No')FROM player WHERE game_idgame=p.game_idgame ORDER BY score DESC LIMIT 1)AS won FROM player AS p WHERE p.playstatus_playstatus='Uitgespeeld' AND p.username=?";
		try {
			PreparedStatement stmt = Database.connection.prepareStatement(query);
			stmt.setString(1, username);
			ResultSet result = stmt.executeQuery();
			while (result.next()) {
				if (result.getString("won").equalsIgnoreCase("yes")) {
					win++;
				} else {
					loss++;
				}
			}
			stmt.close();
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		results.put("win", win);
		results.put("loss", loss);

		return results;
	}

	public static String getMostPlacedColor(String username) {
		String query = "SELECT diecolor FROM ( SELECT diecolor, player_idplayer, COUNT(diecolor) p\r\n" +
				"FROM playerframefield\r\n" +
				"GROUP BY player_idplayer, diecolor) s\r\n" +
				"WHERE player_idplayer IN (SELECT idplayer FROM player WHERE username = ?)\r\n" +
				"GROUP BY diecolor\r\n" +
				"ORDER BY SUM(p) DESC\r\n" +
				"LIMIT 1";
		try {
			PreparedStatement stmt = Database.connection.prepareStatement(query);
			stmt.setString(1, username);
			ResultSet result = stmt.executeQuery();
			while (result.next()) {
				return result.getString("diecolor");

			}

			stmt.close();
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public static int getMostPlacedEye(String username) {
		String query = "SELECT eyes FROM (SELECT eyes, player_idplayer, COUNT(eyes) p FROM playerframefield JOIN gamedie\r\n" +
				"ON gamedie.dienumber = playerframefield.dienumber AND gamedie.diecolor = playerframefield.diecolor\r\n" +
				"GROUP BY player_idplayer, eyes) t\r\n" +
				"WHERE player_idplayer IN (SELECT idplayer FROM player WHERE username = ?)\r\n" +
				"GROUP BY eyes\r\n" +
				"ORDER BY SUM(p) DESC\r\n" +
				"LIMIT 1;";
		try {
			PreparedStatement stmt = Database.connection.prepareStatement(query);
			stmt.setString(1, username);
			ResultSet result = stmt.executeQuery();
			while(result.next()) {
				return result.getInt("eyes");
			}
			stmt.close();
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
	public static int getAmountOfOpponentsPlayed(String username) {
		String query = "SELECT COUNT(DISTINCT(username)) - 1 AS aantal_verschillende_tegenstanders\r\n" +
				"FROM player\r\n" +
				"WHERE game_idgame IN(SELECT game_idgame FROM player WHERE username = ?);";
		try {
			PreparedStatement stmt = Database.connection.prepareStatement(query);
			stmt.setString(1, username);
			ResultSet result = stmt.executeQuery();
			while(result.next()) {
				return result.getInt("aantal_verschillende_tegenstanders");
			}
			stmt.close();
			result.close();
		} catch (SQLException e) {
			e.printStackTrace();

		}

		return 0;

	}


}