import java.io.*;
import java.sql.*;
import java.util.Iterator;

/**
 *
 * @author CAH
 */
public class bakeryDB{
	public bakeryDB(){
		//
	}

	public boolean doBakery(Connection conn) throws SQLException, IOException{
		char ch, ch1;
		byte num = 0;
		bakeryDB.printMenu();
		System.out.print("Type in your option: ");
		System.out.flush();
		ch = (char)System.in.read();
		ch1 = (char)System.in.read();
		switch(ch){
			case '1':
				addProduct(conn);
				break;
			case '2':
				deleteProduct(conn);
				break;
			case '3':
				modifyProduct(conn);
				break;
			case '4':
				selectProduct(conn);
				break;
			case '5':
				showReturnsReport(conn);
				break;
			case '6':
				showTotalsReport(conn);
				break;
			case 'q':
				return (true);
			default:
				System.out.println("Type in option again.");
		}
		return (false);
	}

	public static void printMenu(){
		System.out.println("\n\n\n\n\n\t\tOU BAKERY DATABASE PROGRAM\n");
		System.out.println("(1) Add Product");
		System.out.println("(2) Remove Product");
		System.out.println("(3) Modify Product");
		System.out.println("(4) Display Product Table");
		System.out.println("(5) View Returns Report");
		System.out.println("(6) View Totals Report");
		System.out.println("(q) Quit\n\n");
	}

	void addProduct(Connection conn) throws SQLException, IOException{
		Statement stmt = conn.createStatement();
		String code = readEntry("Product Code: ");
		String name = readEntry("Product Name: ");
		String unit = readEntry("Product Units: ");
		String query = "insert into product values ('" + code + "','" + name + "','" + unit + "')";
		try{
			int nrows = stmt.executeUpdate(query);
		}catch(SQLException e){
			System.out.println("Error Adding Catalog Entry");
			while(e != null){
				System.out.println("Message     : " + e.getMessage());
				e = e.getNextException();
			}
			return;
		}
		stmt.close();
		System.out.println("Added Product");
	}

	void deleteProduct(Connection conn) throws SQLException, IOException{
		Statement stmt = conn.createStatement();
		String code = readEntry("Product Code: ");
		String query = "delete from product where code = '" + code + "'";
		try{
			int nrows = stmt.executeUpdate(query);
		}catch(SQLException e){
			System.out.println("Error Adding Catalog Entry");
			while(e != null){
				System.out.println("Message     : " + e.getMessage());
				e = e.getNextException();
			}
			return;
		}
		stmt.close();
		System.out.println("Removed Product");
	}

	void modifyProduct(Connection conn) throws SQLException, IOException{
		Statement stmt = conn.createStatement();
		String oldCode = readEntry("Product Code to Modify: ");
		String code = readEntry("New Product Code: ");
		String name = readEntry("New Product Name: ");
		String unit = readEntry("New Product Units: ");
		String query = "update product set code = '" + code + "', name = '" + name + "', unit = '" + unit + "'"
				+ "where code = '" + oldCode + "'";
		try{
			int nrows = stmt.executeUpdate(query);
		}catch(SQLException e){
			System.out.println("Error Adding Catalog Entry");
			while(e != null){
				System.out.println("Message     : " + e.getMessage());
				e = e.getNextException();
			}
			return;
		}
		stmt.close();
		System.out.println("Updated Product");
	}

	void selectProduct(Connection conn) throws SQLException, IOException{
		String query = "select * from product";
		Statement stmt = conn.createStatement();
		ResultSet rset = stmt.executeQuery(query);
		System.out.printf("\n%5s\t%45s\t%6s\n", "NAME", "CODE", "UNIT");
		while(rset.next()){
			System.out.printf("\n%5s\t%45s\t%6s", rset.getString(1), rset.getString(2), rset.getString(3));
		}
		System.out.println();
	}

	void showReturnsReport(Connection conn) throws SQLException, IOException{
		String query0 = "select Location.Name, Product.Name, ReturnDate, Reason from Location, Product, Returns where Location.Name = LocationName and Code = ProductCode";
		String query1 = "select Name, ContactPerson, Phone from Location";

		Statement stmt = conn.createStatement();
		ResultSet rset0;
		try{
			rset0 = stmt.executeQuery(query0);
		}catch(SQLException e){
			System.out.println("Problem reading returns");
			while(e != null){
				System.out.println("Message     : " + e.getMessage());
				e = e.getNextException();
			}
			return;
		}
		System.out.printf("\n%45s\t%45s\t%21s\t%100s", "LOCATION", "PRODUCT", "DATE", "REASON");
		while(rset0.next()){
			System.out.printf("\n%45s\t%45s\t%21s\t%100s\n", rset0.getString(1), rset0.getString(2), rset0.getString(3), rset0.getString(4));
		}
		System.out.println("\n\n\n");

		ResultSet rset1;
		try{
			rset1 = stmt.executeQuery(query1);
		}catch(SQLException e){
			System.out.println("Problem reading locations");
			while(e != null){
				System.out.println("Message     : " + e.getMessage());
				e = e.getNextException();
			}
			return;
		}
		System.out.println("CONTACT INFORMATION");
		while(rset1.next()){
			System.out.printf("\n%45s\t%30s\t%10s", rset1.getString(1), rset1.getString(2), rset1.getString(3));
		}
		System.out.println();
		stmt.close();
	}

	void showTotalsReport(Connection conn) throws SQLException, IOException{
		String query = "select Name, LocationName, Amount, Unit from Orders, Product where ProductCode = Code and OrderDate = '25-Feb-2011' order by Name";
		String tempProduct = "";
		int total = 0;

		Statement stmt = conn.createStatement();
		ResultSet rset0;
		try{
			rset0 = stmt.executeQuery(query);
		}catch(SQLException e){
			System.out.println("Problem reading products");
			while(e != null){
				System.out.println("Message     : " + e.getMessage());
				e = e.getNextException();
			}
			return;
		}
		System.out.printf("\n%45s\t%45s\t%6s\n", "PRODUCT", "LOCATION", "AMOUNT");
		while(rset0.next()){
			if(!rset0.getString(1).equals(tempProduct)){
				if(!tempProduct.equals("")){
					System.out.printf("\n%45s\t%45s\t%6d\n", "", "TOTAL", total);
				}
				tempProduct = rset0.getString(1);
				total = 0;
			}
			total += rset0.getInt(3);
			System.out.printf("\n%45s\t%45s\t%6d %6s", rset0.getString(1), rset0.getString(2), rset0.getInt(3), rset0.getString(4));
		}
		System.out.printf("\n%45s\t%45s\t%6d\n", "", "TOTAL", total);
		System.out.println("\n\n\n");
		stmt.close();
	}
	//readEntry function -- to read input string

	static String readEntry(String prompt){
		try{
			StringBuffer buffer = new StringBuffer();
			System.out.print(prompt);
			System.out.flush();
			int c = System.in.read();
			while(c != '\n' && c != -1){
				buffer.append((char)c);
				c = System.in.read();
			}
			return buffer.toString().trim();
		}catch(IOException e){
			return "";
		}
	}
}