package mypack;

import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import javax.ejb.*;


public class BookDBEJBImpl implements SessionBean {
   private ArrayList books = null;
   private Connection con = null;
   private String dbUrl =  "jdbc:mysql://localhost:3306/BookDB";
  private String dbUser="dbuser";
  private String dbPwd="1234";



   // implementation of create and remove remote methods

  public void ejbCreate() throws CreateException {
    try  {
      Class.forName("com.mysql.jdbc.Driver");
      con = java.sql.DriverManager.getConnection(dbUrl,dbUser,dbPwd);

    } catch (Exception ex) {
      throw new CreateException("Couldn't create bean: " + ex.getMessage());
    }
    books = new ArrayList();
  }

  public void ejbRemove() throws EJBException {
    try {
      con.close();
    } catch (SQLException ex) {
      throw new EJBException("unsetEntityContext: " + ex.getMessage());
    }
    con = null;
    books = null;
  }

  public BookDBEJBImpl() {}
  public void ejbActivate() {}
  public void ejbPassivate() {}
  public void setSessionContext(SessionContext sc) {}

    // remote methods

  public int getNumberOfBooks()  {
    books = new ArrayList();
    try {
      String selectStatement = "select * " + "from books";
      PreparedStatement prepStmt = con.prepareStatement(selectStatement);
      ResultSet rs = prepStmt.executeQuery();

      while (rs.next()) {
        BookDetails bd = new BookDetails(rs.getString(1), rs.getString(2), rs.getString(3),
         rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7));
        if (rs.getInt(8) > 0)
          books.add(bd);
      }
      prepStmt.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }

    return books.size();
  }

  public Collection getBooks() {
    books = new ArrayList();
    try {
      String selectStatement = "select * " + "from books";
      PreparedStatement prepStmt = con.prepareStatement(selectStatement);
      ResultSet rs = prepStmt.executeQuery();

      while (rs.next()) {

        BookDetails bd = new BookDetails(rs.getString(1), rs.getString(2), rs.getString(3),
           rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7));
        books.add(bd);
      }

      prepStmt.close();
    } catch (SQLException ex) {
      ex.printStackTrace();
    }

    Collections.sort(books);
    return books;
  }
  private String bookId;
  public void setBookId(String bookId){
    this.bookId=bookId;
  }
  public BookDetails getBookDetails()  {
     return getBookDetails(bookId);
  }
  public BookDetails getBookDetails(String bookId)  {
    try {
      String selectStatement = "select * " + "from books where id = ? ";
      PreparedStatement prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, bookId);
      ResultSet rs = prepStmt.executeQuery();

      if (rs.next()) {
        BookDetails bd = new BookDetails(rs.getString(1), rs.getString(2), rs.getString(3),
          rs.getFloat(4), rs.getInt(5), rs.getString(6),rs.getInt(7));
        prepStmt.close();

        return bd;
      }
      else {
        prepStmt.close();
        return null;
      }
    } catch (SQLException ex) {
      return null;
    }
  }

  public void buyBooks(ShoppingCart cart) {
    Collection items = cart.getItems();
    Iterator i = items.iterator();
    try {
      con.setAutoCommit(false);
      while (i.hasNext()) {
        ShoppingCartItem sci = (ShoppingCartItem)i.next();
        BookDetails bd = (BookDetails)sci.getItem();
        String id = bd.getBookId();
        int quantity = sci.getQuantity();
        buyBook(id, quantity);
      }
      con.commit();
      con.setAutoCommit(true);

    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }


  public boolean buyBook(String bookId, int quantity)  {
    try {
       String selectStatement = "select * " + "from books where id = ? ";
      PreparedStatement prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, bookId);
      ResultSet rs = prepStmt.executeQuery();
      if (rs.next()) {
        prepStmt.close();
          String updateStatement =
                  "update books set saleamount = saleamount + ? where id = ?";
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.setInt(1, quantity);
          prepStmt.setString(2, bookId);
          prepStmt.executeUpdate();
          prepStmt.close();
       }
    } catch (Exception ex) {ex.printStackTrace();}

     return false;
  }

}