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

/**
 * This program provides an example of accessing a database using JDBC. 
 * Compile and run, after making sure that the hsqldb server is running on 
 * the port you pass on the command line.  You'll also need to include the 
 * location of hsqldb.jar in the classpath.  For example, if hsqldb.jar is 
 * in c:\hsqldb\lib, and you want to run the hsqldb server on port 4288, you 
 * would do:
 * 
 * 1) In a command window, run the hsqldb server by typing:
 * 
 * 		java -classpath c:\hsqldb\lib\hsqldb.jar org.hsqldb.Server -port 4288 -database example
 * 
 * (if the hsqldb.jar file is somewhere else, you'll need to change the classpath
 * to match the actual location)
 * 
 * Be sure that the directory you are in when you run the server is the directory
 * where you want the database files to be created.
 * 
 * 2) In another command window, run the CreateDatabase program to create the table by typing:
 * 
 * 		java -classpath c:\hsqldb\lib\hsqldb.jar;. DatabaseExample 4288
 * 
 * (Again, if hsqldb.jar is located in a different directory, change the classpath
 * to match the actual location)
 * 
 * Note that if you are running this on Unix, replace the semicolon in the
 * classpath with a colon.
 * 
 * You only need to run this program once...in fact, running it a second time should
 * generate an exception because the table already exists.
 * 
 * You should write your server in a similar manner to this, allowing the port number
 * for the database server to be passed into the program...this allows me to run the
 * database server and test programs against it without changing port numbers in your
 * code.  
 * 
 */

public class DatabaseExample
{
	Connection conn = null;
	
	public DatabaseExample (Connection c)
	{
		conn = c;
	}
	
	public void go ()
	{
		while (true)
		{
			int choice = getMenuChoice ();

			switch (choice)
			{
				case 1:
					showAllCustomers ();
					break;
					
				case 2:
					deleteCustomer ();
					break;
					
				case 3:
					insertCustomer ();
					break;
					
				case 4:
					changeBalance ();
					break;
					
				case 5:
					return;
			}
		}
	}
	
	public static void main (String[] args)
	{
		if (args.length != 1)
		{
			System.out.println ("You must provide the port number for hsqldb on the command line");
			return;
		}
		
		/* Load in the JDBC driver for hsqldb */
		try
		{
			Class.forName("org.hsqldb.jdbcDriver");
		} 
		catch (ClassNotFoundException e)
		{
			e.printStackTrace();
			return;
		}
		
		/* Create the connection and run the menu */

		try
		{
			Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:" + args [0], "sa", "");
			DatabaseExample dbe = new DatabaseExample (c);
			dbe.go ();
			c.close ();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
			return;
		}
	}
	
	private int getMenuChoice ()
	{
		int result;
		
		do
		{
			displayMenu ();
			
			try
			{
				result = Integer.parseInt (getUserInput ());
			} 
			catch (NumberFormatException e)
			{
				System.out.println ("You must enter a number between 1 and 5");
				result = 0;
			}
			
		} while (result < 1 || result > 5);
		
		return result;
	}
	
	private void displayMenu ()
	{
		System.out.println ("Database Example");
		System.out.println ("1. Show all customers");
		System.out.println ("2. Delete a customer");
		System.out.println ("3. Insert a new customer");
		System.out.println ("4. Change a customer's balance");
		System.out.println ("5. Quit");
	}
	
	private String getUserInput ()
	{
		BufferedReader keyboard = new BufferedReader (new InputStreamReader (System.in));
		String userInput = "";
		
		try
		{
			userInput = keyboard.readLine ();
		}
		catch (Exception e)
		{
			e.printStackTrace ();
		}
		
		return userInput;
	}
	
	private void showAllCustomers ()
	{
		try
		{
			Statement stmt = conn.createStatement ();
			ResultSet rs = stmt.executeQuery ("select * from CUSTOMERS order by id");
			
			while (rs.next())
			{
				System.out.print (rs.getInt("id") + "\t");
				System.out.print (rs.getString("name") + "\t");
				System.out.print (rs.getString("state") + "\t");
				System.out.println (rs.getDouble("balance"));
			}
			
			rs.close ();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	private void deleteCustomer ()
	{
		try
		{
			System.out.print ("Enter the id of the customer to delete: ");
			String id = getUserInput ();
			Integer.parseInt (id);
			
			Statement stmt = conn.createStatement ();
			stmt.executeUpdate ("delete from CUSTOMERS where id='" + id + "'");
		} 
		catch (NumberFormatException e1)
		{
			System.out.println ("You must enter a number for the id.");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	private void insertCustomer ()
	{
		try
		{
			System.out.print ("Enter the id of the customer to insert: ");
			String id = getUserInput ();
			Integer.parseInt (id);

			System.out.print ("Enter the name of the customer to insert: ");
			String name = getUserInput ();

			System.out.print ("Enter the state of the customer to insert: ");
			String state = getUserInput ();
			
			if (state.length () != 2)
			{
				System.out.println ("The state must be a two letter abbreviation.");
				return;
			}
			
			Statement stmt = conn.createStatement ();
			stmt.executeUpdate ("insert into CUSTOMERS values ('" + id + "', '" + name + "', '" + state + "', 0.0)");
		} 
		catch (NumberFormatException e1)
		{
			System.out.println ("You must enter a number for the id.");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	
	private void changeBalance ()
	{
		try
		{
			System.out.print ("Enter the id of the customer to change: ");
			String id = getUserInput ();
			Integer.parseInt (id);

			System.out.print ("Enter the new balance: ");
			String balance = getUserInput ();

			Double.parseDouble (balance);
			
			Statement stmt = conn.createStatement ();
			stmt.executeUpdate ("update CUSTOMERS set balance='" + balance + "' where id='" + id + "'");
		} 
		catch (NumberFormatException e1)
		{
			System.out.println ("You must enter a number for the balance.");
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
}