/*
 * Template JAVA User Interface
 * =============================
 *
 * Database Management Systems
 * Department of Computer Science & Engineering
 * University of California - Riverside
 *
 * Target DBMS: 'Postgres'
 *
 */


import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.io.File;
import java.io.FileReader;
import java.io.BufferedReader;
import java.io.InputStreamReader;

/**
 * This class defines a simple embedded SQL utility class that is designed to
 * work with PostgreSQL JDBC drivers.
 *
 */
public class EmbeddedSQL {

   // reference to physical database connection.
   private Connection _connection = null;

   // handling the keyboard inputs through a BufferedReader
   // This variable can be global for convenience.
   static BufferedReader in = new BufferedReader(
                                new InputStreamReader(System.in));

   /**
    * Creates a new instance of EmbeddedSQL
    *
    * @param hostname the MySQL or PostgreSQL server hostname
    * @param database the name of the database
    * @param username the user name used to login to the database
    * @param password the user login password
    * @throws java.sql.SQLException when failed to make a connection.
    */
   public EmbeddedSQL (String database, String username, String password) throws SQLException {

     System.out.print("Connecting to database...");
       try
       {
         // constructs the connection URL
         String url = "jdbc:postgresql://localhost/" + database;
         System.out.println ("Connection URL: " + url + "\n");

         // obtain a physical connection
         this._connection = DriverManager.getConnection (url, username, password);
         System.out.println("Done");
       }
       catch (Exception e)
       {
               System.err.println( "Error - Unable to Connect to Database: " + e.getMessage() );
               System.out.println("Make sure you started postgres on this machine");
               System.exit(-1);
       }

   }

   /**
    * Method to execute an update SQL statement.  Update SQL instructions
    * includes CREATE, INSERT, UPDATE, DELETE, and DROP.
    *
    * @param sql the input SQL string
    * @throws java.sql.SQLException when update failed
    */
   public void executeUpdate (String sql) throws SQLException {
      // creates a statement object
      Statement stmt = this._connection.createStatement ();

      // issues the update instruction
      stmt.executeUpdate (sql);

      // close the instruction
      stmt.close ();
   }

   /**
    * Method to execute an input query SQL instruction (i.e. SELECT).  This
    * method issues the query to the DBMS and outputs the results to
    * standard out.
    *
    * @param query the input query string
    * @return the number of rows returned
    * @throws java.sql.SQLException when failed to execute the query
    */
   public int executeQuery (String query) throws SQLException {
      // creates a statement object
      Statement stmt = this._connection.createStatement ();

      // issues the query instruction
      ResultSet rs = stmt.executeQuery (query);

      /*
       ** obtains the metadata object for the returned result set.  The metadata
       ** contains row and column info.
       */
      ResultSetMetaData rsmd = rs.getMetaData ();
      int numCol = rsmd.getColumnCount ();
      int rowCount = 0;

      // iterates through the result set and output them to standard out.
      while (rs.next ()) {
         for (int i=1; i<=numCol; ++i)
            System.out.println (rsmd.getColumnName (i) +
               " = " + rs.getString (i));
         System.out.println ();
         ++rowCount;
      }
      stmt.close ();
      return rowCount;
   }

   /**
    * Method to close the physical connection if it is open.
    */
   public void cleanup () {
      try {
         if (this._connection != null)
            this._connection.close ();
      } catch (SQLException e) {
         // ignored.
      }
   }

   /**
    * The main execution method
    *
    * @param args the command line arguments this inclues the <mysql|pgsql> <login file>
    */
   public static void main (String[] args) {
      if (args.length != 3) {
         System.err.println (
            "Usage: " +
            "java [-classpath <classpath>] " +
            EmbeddedSQL.class.getName () +
            " <database> <username> <password>");
         return;
      }
      Greeting();
      EmbeddedSQL esql = null;
      try {

         // use postgres JDBC driver.
         Class.forName ("org.postgresql.Driver").newInstance ();
         // instantiate the EmbeddedSQL object and creates a physical
         // connection.
         String database = args[0];
         String username = args[1];
         String password = args[2];
         esql = new EmbeddedSQL (database, username, password);

         boolean keepon = true;
         while(keepon)
         {
                 // These are sample SQL statements
                 System.out.println("MAIN MENU");
                 System.out.println("---------");
                 System.out.println("1. Find the pid of parts with cost lower than $____");
                 System.out.println("2. Find the name of parts with cost lower than $_____");
                 System.out.println("3. Find the address of the suppliers who supply _____________");
                 System.out.println("0. < EXIT");

                 switch (readChoice())
                 {
                         case 1:   Query1(esql); break;
                         case 2:   Query2(esql); break;
                         case 3:   Query3(esql); break;
                         case 0:   keepon = false; break;
                         default : System.out.println("Unrecognized choice!");
                                   break;
                 }
         }

      } catch (Exception e) {
         System.err.println (e.getMessage ());
      } finally {
         // make sure to cleanup the created table and close the connection.
         try {
            if (esql != null) {
               System.out.print("Disconnecting from database...");
               esql.cleanup ();
               System.out.println("Done\n\nBye !");
            }
         } catch (Exception e) {
            // ignored.
         }
      }
   }
   public static void Greeting()
   {
        System.out.println(
        "\n\n*******************************************************\n" +
        "              User Interface      	               \n" +
        "*******************************************************\n");
   }

/*
 * Reads the users choice given from the keyboard
 * @int
 **/
  public static int readChoice() {
    int input;
    // returns only if a correct value is given.
    do {
      System.out.print("Please make your choice: ");
      try { // read the integer, parse it and break.
        input = Integer.parseInt(in.readLine());
        break;
      }
      catch (Exception e) {
        System.out.println("Your input is invalid!");
        continue;
      }
    }
    while (true);
    return input;
  }

    public static void Query1(EmbeddedSQL esql)
    {
        try{
        String query = "SELECT * FROM Catalog WHERE cost < ";

        System.out.print("\tEnter cost: $");
        String input = in.readLine();
	query += input;

        int rowCount = esql.executeQuery (query);
        System.out.println ("total row(s): " + rowCount);
        
	}catch(Exception e)
        {
          System.err.println (e.getMessage ());
        }
     }
     public static void Query2(EmbeddedSQL esql)
     {
 
     }
      public static void Query3(EmbeddedSQL esql)
     {
 
     }
} //END OF CLASS