package dbj.jdbc;

import java.sql.*;
import java.io.*;

public class Deliver {
  Connection con;
  
  public Deliver (Connection c) {
    con = c;
   }

  private boolean isDeliverable (int orderNo) {
    boolean result = false;
    try {
      PreparedStatement stmt = con.prepareStatement (
       "SELECT COUNT(oi.isbn) " +
       "FROM order_item oi, book b " +
       "WHERE oi.order_id = ? AND " +
       "oi.isbn = b.isbn AND oi.num > b.stock");
     
      stmt.setInt (1, orderNo);
      ResultSet rs = stmt.executeQuery (); 
      if (rs.next ()) 
        result = (rs.getInt (1) == 0);
    } catch (SQLException exc) {
      System.out.println (exc.getMessage ());
    }
    return result;
  }
    
  private void updateStocks (int orderNo) throws SQLException {
    PreparedStatement stmt1 = con.prepareStatement (
      "SELECT oi.num, oi.isbn " +
      "FROM book_order b, order_item oi " +
      "WHERE b.order_id = ? AND b.order_id = oi.order_id");
    PreparedStatement stmt2 = con.prepareStatement (
        "UPDATE book SET stock = stock - ? " +
        "WHERE isbn = ?");
    stmt1.setInt (1, orderNo);
    ResultSet rs = stmt1.executeQuery ();
    while (rs.next ()) {
      stmt2.setInt (1, rs.getInt (1));
      stmt2.setString (2, rs.getString (2));
      stmt2.executeUpdate ();
    }
  }
    
  public void deliverOrders () throws SQLException {
    Statement stmt1;
    PreparedStatement stmt2;
    
    try {
      con.setAutoCommit (false);
      stmt1 = con.createStatement ();
      stmt2 = con.prepareStatement (
        "UPDATE book_order SET status = 1 " +
        "WHERE order_id = ?");
        
      ResultSet rs = stmt1.executeQuery (
        "SELECT order_id FROM book_order " +
        "WHERE status = 0 ORDER BY o_date, order_id");
      while (rs.next ()) {
        int orderNo = rs.getInt (1);
        if (isDeliverable (orderNo)) {
          System.out.println ("deliver order #" + orderNo);
          updateStocks (orderNo);
          stmt2.setInt (1, orderNo);
          stmt2.executeUpdate ();
          con.commit ();
        }
      }
    }
    catch (SQLException exc) {
      con.rollback ();
      throw exc;
    }
  }
  
    public static void main (String[] args) {
    try {
      Connection con = Utils.getConnection ();

      Deliver app = new Deliver (con);
      app.deliverOrders ();
    }
    catch (SQLException exc) {
      exc.printStackTrace ();
    }
  }
}