package obchallenge;

import java.io.IOException;
import java.io.PrintWriter;
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 javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.codehaus.jettison.json.JSONObject;

public class EvaluateSQL extends HttpServlet {

  /**
   *
   */
  private static final long serialVersionUID = 1L;

  public void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    // Output object
    JSONObject jsonResponse = new JSONObject();

    // Parameter retrieving
    String sqlChallengeId = request.getParameter("sqlChallengeId");
    String incomingQuery = request.getParameter("sqlString");

    // 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, sqlChallengeId);
      ResultSet resultSet = statement.executeQuery();
      resultSet.next();
      String solutionQuery = resultSet.getString(1);

      // Execute incomingQuery
      ResultSet incomingQueryResultSet = statement.executeQuery(incomingQuery);

      // Execute solutionQuery
      ResultSet solutionQueryResultSet = statement.executeQuery(solutionQuery);

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

      // Add result to the response
      jsonResponse.put("correct", correct);

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

    response.setContentType("application/json");
    PrintWriter out = response.getWriter();
    out.print(jsonResponse);
  }

  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;
  }
}