import java.time.LocalDate;
import java.time.temporal.ChronoUnit;
import java.util.Random;

public class Query {
	DatabaseConnector db;

	Query() {
		db = new DatabaseConnector("", 0, "wordle.db", "", "");
	}

	/**
	 * Speichert die Daten des Users aus der Datenbank auf dem User-Objekt
	 *
	 * @param user
	 */
	public void pullStats(User user) {
		String[][] r;
		db.executeStatement("SELECT * FROM Stats INNER JOIN won_on_n ON Stats.ID = won_on_n.Stats_ID "
				+ "INNER JOIN last_Game ON Stats.ID = last_Game.Stats_ID WHERE user_username = '" + user.getUsername()
				+ "'");
		r = db.getCurrentQueryResult().getData();
		if (r != null && r.length != 0 && r[0].length == 22) {
			user.setCurrentStreak(Integer.parseInt(r[0][2]));
			user.setMaxStreak(Integer.parseInt(r[0][3]));
			user.setWinPercentage(Float.parseFloat(r[0][4]));
			user.setTimesPlayed(Integer.parseInt(r[0][5]));
			user.setLastDayWOTDPlayed(Integer.parseInt(r[0][6]));

			for (int i = 0; i < 6; i++) {
				try {
					user.setWonInTurnIndex(Integer.parseInt(r[0][8 + i]), i);
				} catch (NumberFormatException e) {
					user.setWonInTurnIndex(0, i);
				}
			}

			for (int i = 0; i < 5; i++) {
				user.getGame().setGuessesIndex(r[0][15 + i], i);
			}
			user.getGame().setWord(r[0][20]);

			try {
				user.getGame().setIsWOTD(Integer.parseInt(r[0][21]));
			} catch (NumberFormatException e) {
				user.getGame().setIsWOTD(0);
			}
		} else {
			System.out.println("Fehler beim Abfragen der Daten für den User " + user.getUsername());
		}
	}

	/**
	 * Pusht die Daten des Users im Parameter auf die Datenbank
	 * 
	 * @param user
	 */
	public void pushStats(User user) {
		db.executeStatement("UPDATE Stats SET current_streak = " + user.getCurrentStreak() + ", max_streak = "
				+ user.getMaxStreak() + ", win_percentage = " + user.getWinPercentage() + ", times_played = "
				+ user.getTimesPlayed() + ", last_day_WOTD_finished = " + user.getLastDayWOTDPlayed()
				+ " WHERE user_username LIKE '" + user.getUsername() + "'");
		db.executeStatement("UPDATE won_on_n SET won_on_1 = " + user.getWonInTurnIndex(0) + ", won_on_2 = "
				+ user.getWonInTurnIndex(1) + ", won_on_3 = " + user.getWonInTurnIndex(2) + ", won_on_4 = "
				+ user.getWonInTurnIndex(3) + ", won_on_5 = " + user.getWonInTurnIndex(4) + ", won_on_6 = "
				+ user.getWonInTurnIndex(5)
				+ " WHERE Stats_ID IN( SELECT user_username FROM Stats WHERE user_username LIKE '" + user.getUsername()
				+ "')");
		db.executeStatement("UPDATE last_Game SET last_guess_1 = " + user.getGame().getGuessIndex(0)
				+ ", last_guess_2 = " + user.getGame().getGuessIndex(1) + ", last_guess_3 = "
				+ user.getGame().getGuessIndex(2) + ", last_guess_4 = " + user.getGame().getGuessIndex(3)
				+ ", last_guess_5 = " + user.getGame().getGuessIndex(4) + ", word = " + user.getGame().getWord()
				+ ", isWOTD = " + user.getGame().getIsWOTD()
				+ " WHERE Stats_ID IN( SELECT ID FROM Stats WHERE user_username LIKE '" + user.getUsername() + "')");
	}

	/**
	 * �berpr�ft ob es den Username und das Passwort in der Datenbank gibt und
	 * gibt den Erfolg als Boolean zur�ck
	 * 
	 * @param username
	 * @param password
	 * @return Boolean true = login erfolgreich false = login fehlgeschlagen
	 */
	public boolean checkLogin(String username, String password) {
//		TODO: testen und vllt korrigieren
		db.executeStatement("SELECT 1 FROM user WHERE username LIKE \"" + username + "\" " + "AND passwort LIKE \""
				+ password + "\"");
		QueryResult r = db.getCurrentQueryResult();
		if (r != null && r.getRowCount() == 1) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Gibt ein Zuf�lliges Wort aus der Datenbank zur�ck
	 * 
	 * @return Ein Word als String
	 */
	public String getWord() {
		db.executeStatement("SELECT word FROM words ORDER BY random() LIMIT 1");
		String[][] s = db.getCurrentQueryResult().getData();
		return s[0][0];
	}

	/**
	 * Gibt das Wordle des Tages, welches aus der Datenbank stammt, zur�ck. Ergibt
	 * pro Tag immer das gleiche Wordle, aber trotzdem für jeden neuen Tag ein
	 * anderes Wort
	 * 
	 * @return Das Wort als String
	 */
	public String getWOTD() {
		db.executeStatement("SELECT COUNT(*) FROM words");
		String[][] r = db.getCurrentQueryResult().getData();
		int words = Integer.parseInt(r[0][0]);
		Random random = new Random();
		random.setSeed((int) ChronoUnit.DAYS.between(LocalDate.ofEpochDay(0), LocalDate.now()));
		int randNumber = random.nextInt() % words;
		db.executeStatement("SELECT word FROM words WHERE ID LIKE '" + randNumber + "'");
		r = db.getCurrentQueryResult().getData();
		return r[0][0];
	}

	/**
	 * Pr�ft ob das Wort in der Datenbank vorhanden ist
	 * 
	 * @param word ,ein String mit dem Wort
	 * @return Boolean
	 */
	public boolean checkWord(String word) {
		db.executeStatement("SELECT 1 FROM words WHERE word LIKE '" + word + "'");
		String[][] r = db.getCurrentQueryResult().getData();
		return r.length != 0;
	}
}