package org.freeciv.services;

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 javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class Statistics {

	public List<Map<String, String>> getPlayByEmailWinners() {

		Connection connection = null;
		try {
			Context env;
			env = (Context) (new InitialContext().lookup("java:comp/env"));

			DataSource ds = (DataSource) env.lookup("jdbc/freeciv_mysql");
			connection = ds.getConnection();

			String query = "SELECT winner, playerOne, playerTwo, endDate, "
					+ "(SELECT COUNT(*) FROM game_results WHERE winner = playerOne) AS winsByPlayerOne, "
					+ "(SELECT COUNT(*) FROM game_results WHERE winner = playerTwo) AS winsByPlayerTwo " //
					+ "FROM game_results " //
					+ "ORDER BY id DESC LIMIT 20";

			PreparedStatement preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			List<Map<String, String>> result = new ArrayList<>();
			while (rs.next()) {
				Map<String, String> record = new HashMap<>();
				record.put("winner", rs.getString("winner"));
				record.put("playerOne", rs.getString("playerOne"));
				record.put("playerTwo", rs.getString("playerTwo"));
				record.put("endDate", rs.getDate("endDate") + "");
				record.put("winsByPlayerOne", rs.getInt("winsByPlayerOne") + "");
				record.put("winsByPlayerTwo", rs.getString("winsByPlayerTwo") + "");
				result.add(record);

			}
			return result;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} catch (NamingException e) {
			throw new RuntimeException(e);
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public List<Map<String, Object>> getPlayedGamesByType() {

		Connection connection = null;
		try {
			Context env = (Context) (new InitialContext().lookup("java:comp/env"));
			DataSource ds = (DataSource) env.lookup("jdbc/freeciv_mysql");
			connection = ds.getConnection();
			String query = "SELECT DISTINCT statsDate AS date, "
					+ "(SELECT gameCount FROM games_played_stats WHERE statsDate = date AND gameType = '0') AS webSinglePlayer, "
					+ "(SELECT gameCount FROM games_played_stats WHERE statsDate = date AND gameType = '1') AS webMultiPlayer, "
					+ "(SELECT gameCount FROM games_played_stats WHERE statsDate = date AND gameType = '2') AS webPlayByEmail, "
					+ "(SELECT gameCount FROM games_played_stats WHERE statsDate = date AND gameType = '4') AS webHotseat, "
					+ "(SELECT gameCount FROM games_played_stats WHERE statsDate = date AND gameType = '3') AS desktopMultiplayer, "
					+ "(SELECT gameCount FROM games_played_stats WHERE statsDate = date AND gameType = '5') AS webSinglePlayer3D "
					+ "FROM games_played_stats";

			PreparedStatement preparedStatement = connection.prepareStatement(query);
			ResultSet rs = preparedStatement.executeQuery();
			List<Map<String, Object>> result = new ArrayList<>();
			while (rs.next()) {
				Map<String, Object> item = new HashMap<>();
				item.put("date", rs.getDate("date"));
				item.put("webSinglePlayer", rs.getInt("webSinglePlayer"));
				item.put("webMultiPlayer", rs.getInt("webMultiPlayer"));
				item.put("webPlayByEmail", rs.getInt("webPlayByEmail"));
				item.put("webHotseat", rs.getInt("webHotseat"));
				item.put("desktopMultiplayer", rs.getInt("desktopMultiplayer"));
				item.put("webSinglePlayer3D", rs.getInt("webSinglePlayer3D"));
				result.add(item);
			}
			return result;
		} catch (Exception err) {
			throw new RuntimeException(err);
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

@@ -725,16 +725,18 @@
 winner, 
+r.
 playerOn
@@ -738,16 +738,18 @@
 yerOne, 
+r.
 playerTw
@@ -797,38 +797,44 @@
 OM game_results 
+r2 
 WHERE 
+r2.
 winner = playerO
@@ -822,24 +822,26 @@
 r2.winner = 
+r.
 playerOne) A
@@ -904,22 +904,28 @@
 results 
+r3 
 WHERE 
+r3.
 winner =
@@ -925,16 +925,18 @@
 inner = 
+r.
 playerTw
@@ -988,16 +988,17 @@
 results 
+r
 %22 //%0A%09%09%09

Fix all players with same wins