import java.sql.*;
// You should use this class so that you can represent SQL points as
// Java PGpoint objects.
import org.postgresql.geometric.PGpoint;

// If you are looking for Java data structures, these are highly useful.
// However, you can write the solution without them.  And remember
// that part of your mark is for doing as much in SQL (not Java) as you can.
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Set;
import java.sql.Timestamp;

public class Assignment2 {

   // A connection to the database
   Connection connection;

   Assignment2() throws SQLException {
      try {
         Class.forName("org.postgresql.Driver");
      } catch (ClassNotFoundException e) {
         e.printStackTrace();
      }
   }

  /**
   * Connects and sets the search path.
   *
   * Establishes a connection to be used for this session, assigning it to
   * the instance variable 'connection'.  In addition, sets the search
   * path to uber.
   *
   * @param  url       the url for the database
   * @param  username  the username to connect to the database
   * @param  password  the password to connect to the database
   * @return           true if connecting is successful, false otherwise
   */
   public boolean connectDB(String URL, String username, String password) {
      // Implement this method!
      try{
        connection = DriverManager.getConnection(URL, username, password);
      }
      catch (SQLException ex) {
      //return false;
      }
    if (connection != null ){
        String queryString = "SET search_path TO uber";
        PreparedStatement pStatement = connection.prepareStatement(queryString);
        pStatement.execute();
        return true;
      }
        return false;
      }


  /**
   * Closes the database connection.
   *
   * @return true if the closing was successful, false otherwise
   */
   public boolean disconnectDB() {
      // Implement this method!
      try {
          connection.close();
      }
      catch (SQLException ex) {
          return false;
      }
      return true;
   }

   /* ======================= Driver-related methods ======================= */

   /**
    * Records the fact that a driver has declared that he or she is available
    * to pick up a client.
    *
    * Does so by inserting a row into the Available table.
    *
    * @param  driverID  id of the driver
    * @param  when      the date and time when the driver became available
    * @param  location  the coordinates of the driver at the time when
    *                   the driver became available
    * @return           true if the insertion was successful, false otherwise.
    */
   public boolean available(int driverID, Timestamp when, PGpoint location) {
      // Implement this method!

      String UpdateString = "INSERT INTO Available Values " +
                    "(?, ?, ?)";
      PreparedStatement ps = connection.prepareStatement(UpdateString);
      ps.setInt(1, driverID);
      ps.setTimestamp(2, when);
      ps.setObject(3, location);
      if (ps.executeUpdate()==1) {
        return true;}
      else {
      return false;}
    }

   /**
    * Records the fact that a driver has picked up a client.
    *
    * If the driver was dispatched to pick up the client and the corresponding
    * pick-up has not been recorded, records it by adding a row to the
    * Pickup table, and returns true.  Otherwise, returns false.
    *
    * @param  driverID  id of the driver
    * @param  clientID  id of the client
    * @param  when      the date and time when the pick-up occurred
    * @return           true if the operation was successful, false otherwise
    */
    // Request, dispatch, pickup
   public boolean picked_up(int driverID, int clientID, Timestamp when) {
      // Implement this method!
      // Step 1: Check if driverID clientID matches the joined table request and dispatch and finds request ID
      String QueryString =  "SELECT request_id, p.timestamp " +
                            "FROM Request r JOIN Dispatch d " +
                            "ON r.request_id=d.request_id "+
                            "LEFT JOIN Pickup p "+
                            "ON d.request_id = p.request_id "+
                            "WHERE r.client_id=? AND d.driver_id=? "+
                            "ORDER BY d.timestamp DESC";// We're assuming they get picked up by the most recent request that has not been picked up for client, in case there is a null value for when
      PreparedStatement ps=connection.prepareStatement(QueryString);
      ps.setInt(1,clientID);
      ps.setInt(2,driverID);
      ResultSet rs=ps.executeQuery();
      while(rs.next()) {
          //If there are elements in the results, it will return a non-0 value.
          // Step 2: If driver ID, clientID matches the joined table, check if it exists in pickup table, if not, insert it.
          //  if the time is different, from it, it has been called a different time, defer it.
          int r_id=rs.getInt("request_id");
          Timestamp time=rs.getTimestamp("timestamp");
          if (time == null) {
              //Assumption: If dispatch is missed, it is the one we're looking for
              String UpdateString = "INSERT INTO Pickup Values " +
                            " ( ?, ?)";
              ps = connection.prepareStatement(UpdateString);
              ps.setInt(1,r_id);
              ps.setTimestamp(2, when);
              return true;
           }
           else if(time == when) {
               // If timestamps matches input, then it is already in the pickup table, so don't do anything
               return false;
           }
      }
      //If timestamp value is different from input, continue to next value.

      return false;
   }

   /* ===================== Dispatcher-related methods ===================== */

   /**
    * Dispatches drivers to the clients who've requested rides in the area
    * bounded by NW and SE.
    *
    * For all clients who have requested rides in this area (i.e., whose
    * request has a source location in this area), dispatches drivers to them
    * one at a time, from the client with the highest total billings down
    * to the client with the lowest total billings, or until there are no
    * more drivers available.
    *
    * Only drivers who (a) have declared that they are available and have
    * not since then been dispatched, and (b) whose location is in the area
    * bounded by NW and SE, are dispatched.  If there are several to choose
    * from, the one closest to the client's source location is chosen.
    * In the case of ties,  any one of the tied drivers may be dispatched.
    *
    * Area boundaries are inclusive.  For example, the point (4.0, 10.0)
    * is considered within the area defined by
    *         NW = (1.0, 10.0) and SE = (25.0, 2.0)
    * even though it is right at the upper boundary of the area.
    *
    * Dispatching a driver is accomplished by adding a row to the
    * Dispatch table.  All dispatching that results from a call to this
    * method is recorded to have happened at the same time, which is
    * passed through parameter 'when'.

    * @param  NW    x, y coordinates in the northwest corner of this area.
    * @param  SE    x, y coordinates in the southeast corner of this area.
    * @param  when  the date and time when the dispatching occurred
    */
   public void dispatch(PGpoint NW, PGpoint SE, Timestamp when) {
      // Implement this method!
      // Matching clients with drivers, and the result is to match all of them until there is no more drivers in the area.
      // Step 1: Find all the available (open request) client in the area and order by total billing, find all avaiable driver in the area.
      String Querystring = "((SELECT client_id, location"+ // Sort all clients by their patronage
                           "FROM Request r LEFT JOIN Billed b ON r.request_id=b.request_id " +
                           "GROUP BY client_id " +
                           "ORDER BY sum(amount) DESC NULLS LAST) " +
                           "JOIN " +
                           "(SELECT client_id " +// Find all open request for clients within the area
                           "FROM Request r JOIN Place p "+
                           "ON r.source = p.location " +
                           // Definition: open request - request that has not been fulfilled yet(not billed)
                           "WHERE r.request_id NOT IN  ( " +
                               "SELECT request_id " +
                               "FROM Billed b ) " +
                           "AND location[0]>= ? AND location[0]<= ? " +//within the specified area
                           "AND location[1]<= ? AND location[1]>= ? )) " +
                           "AS Availble_client"
                           ;

      PreparedStatement ps = connection.prepareStatement(Querystring);
      ps.setDouble(1,NW.x);
      ps.setDouble(2,SE.x);
      ps.setDouble(3,NW.y);
      ps.setDouble(4,SE.y);
      ResultSet c_ID=ps.executeQuery();
         // Actually available - the available timestamp > dispatched timestamp
      Querystring = "(SELECT x.driver_id, x.location, x.datetime " + //Finds most recent available time for all drivers
                            "FROM (" +
                                "SELECT driver_id , max(datetime) " +
                                "FROM Available a" +
                                "GROUP BY driver_id " +
                                ") "+
                                "AS x " +
                            "WHERE x.datetime > ( " +// Finds the dispatch
                                "SELECT max(d.datetime) " +
                                "FROM Dispatch d " +
                                "GROUP BY d.driver_id " +
                                "WHERE x.driver_id = d.driver_id " +
                                " )" +
                            "AND x.location[0]>= ? AND x.location[0]<= ? " +// with in the area
                            "AND x.location[1]<= ? AND x.location[1]>= ? " +
                            ")" +
                            "AS Available_driver";
      ps = connection.prepareStatement(Querystring);
      ps.setDouble(1,NW.x);
      ps.setDouble(2,SE.x);
      ps.setDouble(3,NW.y);
      ps.setDouble(4,SE.y);
      ResultSet d_ID=ps.executeQuery();
   }

   public static void main(String[] args) {
      // You can put testing code in here. It will not affect our autotester.
    Assignment2
    A2 = new Assignment2();
    String url = "jdbc:postgresql://localhost:5432/csc343h-c6fanjia";
    A2.connectDB(url, "c6fanjia","");
    System.out.println("Boo!");
   }

}