package obchallenge;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLChallengeExecutor extends AbstractChallengeExecutor {

  @Override
  public boolean executeChallenge(String testId, String proposedSolution) {
    // Prepare output parameter
    boolean correct = true;

    // Connection to database
    Connection connection = null;
    try {
      Class.forName("org.postgresql.Driver");
      connection = DriverManager.getConnection("jdbc:postgresql://" + Constants.DB_URL + ":"
          + Constants.DB_PORT + "/" + Constants.DB_SID, Constants.DB_USER, Constants.DB_PASSWORD);

      // Get solutionQuery
      String getSolutionQuery = "SELECT solution_query FROM sql_challenge WHERE sql_challenge_id = ?";
      PreparedStatement statement = connection.prepareStatement(getSolutionQuery);
      statement.setString(1, testId);
      ResultSet resultSet = statement.executeQuery();
      resultSet.next();
      String solutionQuery = resultSet.getString(1);

      // Execute incomingQuery
      Statement incomingStatement = connection.createStatement();
      ResultSet incomingQueryResultSet = incomingStatement.executeQuery(proposedSolution);

      // Execute solutionQuery
      Statement solutionStatement = connection.createStatement();
      ResultSet solutionQueryResultSet = solutionStatement.executeQuery(solutionQuery);

      // Compare results
      correct = compareResultSets(incomingQueryResultSet, solutionQueryResultSet);

      resultSet.close();
      statement.close();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      if (connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
    return correct;
  }

  public boolean compareResultSets(ResultSet resultSet1, ResultSet resultSet2) throws SQLException {
    while (resultSet1.next()) {
      resultSet2.next();
      ResultSetMetaData resultSetMetaData = resultSet1.getMetaData();
      int count = resultSetMetaData.getColumnCount();
      for (int i = 1; i <= count; i++) {
        if (!resultSet1.getObject(i).equals(resultSet2.getObject(i))) {
          return false;
        }
      }
    }
    return true;
  }
}