import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.IOException;
import java.io.File;
import java.util.Scanner;

/**
   Enters an invoice into the database.
   Be sure to add Customer.sql, Product.sql, Invoice.sql, and LineItem.sql
   to the database before running this program.
*/
public class InvoiceEntry
{
   public static void main(String args[])         
   {   
      if (args.length == 0)
      {   
         System.out.println(
               "Usage: java -classpath driver_class_path"
               + File.pathSeparator 
               + ". InvoiceEntry propertiesFile");
         return;
      }

      try 
      {
         SimpleDataSource.init(args[0]);
         Connection conn = SimpleDataSource.getConnection();
         Scanner in = new Scanner(System.in);

         addInvoice(in, conn);
      }
      catch (SQLException ex)
      {
         System.out.println("Database error");
         ex.printStackTrace();
      }
      catch (ClassNotFoundException ex)
      {
         System.out.println("Error loading database driver");
         ex.printStackTrace();
      }
      catch (IOException ex)
      {
         System.out.println("Error loading database properties");
         ex.printStackTrace();
      }
   }

   public static void addInvoice(Scanner in, Connection conn)
      throws SQLException
   {      
      try
      {
         int customerNumber = newCustomer(conn, in);

         int id = getNewId(conn, "Invoice");
         PreparedStatement stat = conn.prepareStatement(
            "INSERT INTO Invoice VALUES (?, ?, 0)");
         stat.setInt(1, id);
         stat.setInt(2, customerNumber);
         stat.executeUpdate();
         stat.close();

         boolean done = false;
         while (!done)
         {
            String productCode = nextLine(in, "Product code (D=Done, L=List)");
            if (productCode.equals("D")) { done = true; }
            else if (productCode.equals("L")) { listProducts(conn); }               
            else if (findProduct(conn, productCode))
            {
               int quantity = nextInt(in, "Quantity");
               addLineItem(conn, id, productCode, quantity);
            }
            else { System.out.println("Invalid product code."); }
         }
         showInvoice(conn, id);
      }
      finally
      {
         conn.close();
      }      
   }      

   /**
      Prompts the user for the customer information and creates a new customer.
      @param conn the database connection
      @param in the scanner
      @return the ID of the new customer
   */
   private static int newCustomer(Connection conn, Scanner in)
      throws SQLException
   {
      String name = nextLine(in, "Name");
      String address = nextLine(in, "Street address");
      String city = nextLine(in, "City");
      String state = nextLine(in, "State");
      String zip = nextLine(in, "Zip");
      int id = getNewId(conn, "Customer");
      PreparedStatement stat = conn.prepareStatement(
         "INSERT INTO Customer VALUES (?, ?, ?, ?, ?, ?)");
      stat.setInt(1, id);
      stat.setString(2, name);
      stat.setString(3, address);
      stat.setString(4, city);
      stat.setString(5, state);
      stat.setString(6, zip);
      stat.executeUpdate();
      stat.close();
      return id;
   }

   /**
      Finds a product in the database.
      @param conn the database connection
      @param code the product code to search
      @return true if there is a product with the given code
   */
   private static boolean findProduct(Connection conn, String code)
      throws SQLException
   {
      PreparedStatement stat = conn.prepareStatement(
         "SELECT * FROM Product WHERE Product_Code = ?");
      stat.setString(1, code);
      ResultSet result = stat.executeQuery();
      boolean found = result.next();
      stat.close();
      return found;
   }

   /**
      Adds a line item to the database
      @param conn the database connection
      @param id the invoice ID
      @param code the product code
      @param quantity the quantity to order
   */
   private static void addLineItem(Connection conn, int id,
      String code, int quantity) throws SQLException
   {      
      PreparedStatement stat = conn.prepareStatement(
         "INSERT INTO LineItem VALUES (?, ?, ?)");
      stat.setInt(1, id);
      stat.setString(2, code);
      stat.setInt(3, quantity);
      stat.executeUpdate();
      stat.close();
   }

   /**
      Lists all products in the database.
      @param conn the database connection
   */
   private static void listProducts(Connection conn)
      throws SQLException
   {
      Statement stat = conn.createStatement();
      ResultSet result = stat.executeQuery(
         "SELECT Product_Code, Description FROM Product");
      while (result.next())
      {
         String code = result.getString(1);
         String description = result.getString(2);
         System.out.println(code + " " + description);
      }
      stat.close();
   }
   
   /**
      Gets a new ID for a table. This method should be called from
      inside a transaction that also creates the new row with this ID.
      The ID field should have name table_Number and type INTEGER.
      @param table the table name
      @return a new ID that has not yet been used.
   */
   private static int getNewId(Connection conn, String table)
      throws SQLException
   {
      Statement stat = conn.createStatement();
      ResultSet result = stat.executeQuery(
         "SELECT max(" + table + "_Number) FROM " + table);
      result.next();
      int max = result.getInt(1);
      stat.close();
      return max + 1;
   }

   /**
      Shows an invoice.
      @param conn the database connection
      @param id the invoice ID
   */
   private static void showInvoice(Connection conn, int id)
      throws SQLException
   {
      PreparedStatement stat = conn.prepareStatement(
         "SELECT Customer.Name, Customer.Address, "
         + "Customer.City, Customer.State, Customer.Zip "
         + "FROM Customer, Invoice "
         + "WHERE Customer.Customer_Number = Invoice.Customer_Number "
         + "AND Invoice.Invoice_Number = ?");
      stat.setInt(1, id);
      ResultSet result = stat.executeQuery();
      result.next();
      System.out.println(result.getString(1));
      System.out.println(result.getString(2));        
      System.out.println(result.getString(3).trim() + ", "
         + result.getString(4) + " " + result.getString(5));
      stat.close();
                 
      stat = conn.prepareStatement(
         "SELECT Product.Product_Code, Product.Description, LineItem.Quantity "
         + "FROM Product, LineItem "
         + "WHERE Product.Product_Code = LineItem.Product_Code "
         + "AND LineItem.Invoice_Number = ?");
      stat.setInt(1, id);

      result = stat.executeQuery();
      while (result.next())
      {  
         String code = result.getString(1);
         String description = result.getString(2).trim();
         int qty = result.getInt(3);

         System.out.println(qty + " x " + code + " " + description);
      }
      stat.close();         
   }

   /**
      Prompts the user and reads a line from a scanner.
      @param in the scanner
      @param prompt the prompt
      @return the string that the user entered
   */
   private static String nextLine(Scanner in, String prompt)
   {
      System.out.print(prompt + ": ");
      return in.nextLine();
   }

   /**
      Prompts the user and reads an integer from a scanner.
      @param in the scanner
      @param prompt the prompt
      @return the integer that the user entered
   */
   private static int nextInt(Scanner in, String prompt)
   {
      System.out.print(prompt + ": ");
      int result = in.nextInt();
      in.nextLine(); // Consume newline
      return result;
   }
}