peer-at-code-backend/src/dev/peerat/backend/repository/DatabaseCompletionRepository.java

package dev.peerat.backend.repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dev.peerat.backend.Configuration;
import dev.peerat.backend.model.Completion;
import dev.peerat.backend.model.Puzzle;

public class DatabaseCompletionRepository extends BaseDatabaseQuery{
	
	private static enum Query{
		
		// COMPLETIONS
		GET_COMPLETION(
				"SELECT * FROM completions WHERE fk_puzzle = ? AND fk_player = ?"),
		GET_COMPLETION_GROUP("SELECT c.*\r\n"
				+ "FROM completions c\r\n"
				+ "WHERE c.fk_puzzle = ? AND c.fk_player IN\r\n"
				+ "(select f.fk_player FROM containsGroups cgs JOIN containsGroups f ON f.fk_group = cgs.fk_group JOIN groups g ON g.id_group = cgs.fk_group WHERE g.fk_chapter = 12 AND cgs.fk_player = ?)"),
		INSERT_COMPLETION(
				"INSERT INTO completions (fk_puzzle, fk_player, tries, code, fileName, score) values (?, ?, ?, ?, ?, ?)"),
		UPDATE_COMPLETION(
				"UPDATE completions SET tries = ?, score = ?, fk_player = ? WHERE fk_puzzle = ? AND fk_player = ?"),
		SCORE("SELECT score FROM completions WHERE fk_player = ? AND fk_puzzle = ?"),
		SCORE_GROUP("SELECT c.score\r\n"
				+ "FROM completions c\r\n"
				+ "WHERE c.fk_puzzle = ? AND c.fk_player IN\r\n"
				+ "(select f.fk_player FROM containsGroups cgs JOIN containsGroups f ON f.fk_group = cgs.fk_group JOIN groups g ON g.id_group = cgs.fk_group WHERE g.fk_chapter = 12 AND cgs.fk_player = ?)");

		private String request;

		Query(Query parent, String request) {
			this.request = parent.request + request;
		}

		Query(String request) {
			this.request = request;
		}

		public PreparedStatement prepare(BaseDatabaseQuery base) throws SQLException {
			return base.prepare(this.request);
		}

		@Override
		public String toString() {
			return this.request;
		}
	}
	
	
	private Configuration config;
	private DatabaseChapterRepository chapterRepo;
	
	public DatabaseCompletionRepository(ConnectionManager con, Configuration config, DatabaseChapterRepository chapterRepo){
		super(con);
		this.config = config;
		this.chapterRepo = chapterRepo;
	}
	
	public Completion getCompletionGroup(int user, int puzzle) {
		try {
			PreparedStatement stmt = Query.GET_COMPLETION_GROUP.prepare(this);
			stmt.setInt(1, puzzle);
			stmt.setInt(2, user);
			ResultSet result = stmt.executeQuery();
			if (result.next())
				return makeCompletion(result);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return getCompletion(user, puzzle);
	}

	public Completion getCompletion(int playerId, int puzzleId) {
		try {
			PreparedStatement completionsStmt = Query.GET_COMPLETION.prepare(this);
			completionsStmt.setInt(1, puzzleId);
			completionsStmt.setInt(2, playerId);
			ResultSet result = completionsStmt.executeQuery();
			if (result.next()) {
				return makeCompletion(result);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Completion insertOrUpdatePuzzleResponse(int puzzleId, int userId, String fileName, byte[] code,
			byte[] response, Puzzle currentPuzzle){
		try {
			ensureConnection();
			Completion completion = getCompletionGroup(userId, puzzleId);
			if (completion == null){
				System.out.println("Completion is null");
				completion = new Completion(userId, puzzleId, fileName, code, response, currentPuzzle);
				insertCompletion(completion);
			} else {
				System.out.println(completion);
				completion.addTry(currentPuzzle, response, chapterRepo.getChapter(currentPuzzle).getId());
				int lastUserId = completion.getPlayerId();
				completion.updatePlayer(userId);
				updateCompletion(completion, lastUserId);
			}
			return completion;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	private void insertCompletion(Completion newCompletion) throws SQLException {
		PreparedStatement statement = Query.INSERT_COMPLETION.prepare(this);
		statement.setInt(1, newCompletion.getPuzzleId());
		statement.setInt(2, newCompletion.getPlayerId());
		statement.setInt(3, newCompletion.getTries());
		statement.setBytes(4, newCompletion.getCode());
		statement.setString(5, newCompletion.getFileName());
		statement.setInt(6, newCompletion.getScore());
		statement.executeUpdate();
	}


	private void updateCompletion(Completion completionToUpdate, int user) throws SQLException{
		System.out.println("update "+completionToUpdate);
		PreparedStatement statement = Query.UPDATE_COMPLETION.prepare(this);
		statement.setInt(1, completionToUpdate.getTries());
		statement.setInt(2, completionToUpdate.getScore());
		statement.setInt(3, completionToUpdate.getPlayerId());
		statement.setInt(4, completionToUpdate.getPuzzleId());
		statement.setInt(5, user);
		statement.executeUpdate();
	}
	
	public int getScore(int user, int puzzle) {
		try {
			ensureConnection();
			PreparedStatement stmt = Query.SCORE_GROUP.prepare(this);
			stmt.setInt(1, puzzle);
			stmt.setInt(2, user);
			ResultSet result = stmt.executeQuery();
			if (result.next())
				return result.getInt("score");

			stmt = Query.SCORE.prepare(this);
			stmt.setInt(1, user);
			stmt.setInt(2, puzzle);

			result = stmt.executeQuery();
			if (result.next())
				return result.getInt("score");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}
}