/*
 *
 * Copyright 2002 Sun Microsystems, Inc. All Rights Reserved.
 * 
 * Redistribution and use in source and binary forms, with or
 * without modification, are permitted provided that the following
 * conditions are met:
 * 
 * - Redistributions of source code must retain the above copyright
 *   notice, this list of conditions and the following disclaimer.
 * 
 * - Redistribution in binary form must reproduce the above
 *   copyright notice, this list of conditions and the following
 *   disclaimer in the documentation and/or other materials
 *   provided with the distribution.
 * 
 * Neither the name of Sun Microsystems, Inc. or the names of
 * contributors may be used to endorse or promote products derived
 * from this software without specific prior written permission.
 * 
 * This software is provided "AS IS," without a warranty of any
 * kind. ALL EXPRESS OR IMPLIED CONDITIONS, REPRESENTATIONS AND
 * WARRANTIES, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT, ARE HEREBY
 * EXCLUDED. SUN AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY
 * DAMAGES OR LIABILITIES SUFFERED BY LICENSEE AS A RESULT OF OR
 * RELATING TO USE, MODIFICATION OR DISTRIBUTION OF THIS SOFTWARE OR
 * ITS DERIVATIVES. IN NO EVENT WILL SUN OR ITS LICENSORS BE LIABLE
 * FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT,
 * SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER
 * CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF
 * THE USE OF OR INABILITY TO USE THIS SOFTWARE, EVEN IF SUN HAS
 * BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.
 * 
 * You acknowledge that this software is not designed, licensed or
 * intended for use in the design, construction, operation or
 * maintenance of any nuclear facility.
 * 
 */

package database;

import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import java.util.*;
import exception.*;
import cart.*;

public class BookDBAO {

  private ArrayList books;
  Connection con;

  public BookDBAO () throws Exception {

    try  { 
      InitialContext ic = new InitialContext();
      Context envCtx = (Context) ic.lookup("java:comp/env");
      DataSource ds = (DataSource) envCtx.lookup("jdbc/BookDB");
      con =  ds.getConnection();    
    } catch (Exception ex) {
      throw new Exception("Couldn't open connection to database: " + ex.getMessage());
    }       
  }

  public void remove () {
    try {
        con.close();
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
  }

  public int getNumberOfBooks() throws BooksNotFoundException {
    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.getString(4), rs.getFloat(5), rs.getInt(6), rs.getString(7), rs.getInt(8));
        if (rs.getInt(8) > 0)
          books.add(bd);
      }
      prepStmt.close();
    } catch (SQLException ex) {
      throw new BooksNotFoundException(ex.getMessage());
    }
    return books.size();
  }

  public Collection getBooks() throws BooksNotFoundException {
    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.getString(4), rs.getFloat(5), rs.getInt(6), rs.getString(7), rs.getInt(8));
        if (rs.getInt(8) > 0)
          books.add(bd);
      }
      prepStmt.close();
    } catch (SQLException ex) {
      throw new BooksNotFoundException(ex.getMessage());
    }

    Collections.sort(books);
    return books;
  }

  public BookDetails getBookDetails(String bookId) throws BookNotFoundException {
    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.getString(4), rs.getFloat(5), rs.getInt(6), rs.getString(7), rs.getInt(8));
        prepStmt.close();                
        return bd;
      }
      else {          
        prepStmt.close();
        throw new BookNotFoundException("Couldn't find book: " + bookId);
      }
    } catch (SQLException ex) {
      throw new BookNotFoundException("Couldn't find book: " + bookId + " " + ex.getMessage());
    }
  }

  public void buyBooks(ShoppingCart cart) throws OrderException{
    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) {     
      try {    
        con.rollback();       
        throw new OrderException("Transaction failed: " + ex.getMessage());  
      } catch (SQLException sqx) {     
        throw new OrderException("Rollback failed: " + sqx.getMessage());        
      }        
    }       
  }


  public void buyBook(String bookId, int quantity) throws OrderException {  
    try {
      String selectStatement = "select * " + "from books where id = ? ";
      PreparedStatement prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString(1, bookId);
      ResultSet rs = prepStmt.executeQuery();
      if (rs.next()) {
        int inventory = rs.getInt(8);
        prepStmt.close();              
        if ((inventory - quantity) >= 0) {       
          String updateStatement =
                  "update books set inventory = inventory - ? where id = ?";
          prepStmt = con.prepareStatement(updateStatement);
          prepStmt.setInt(1, quantity);
          prepStmt.setString(2, bookId);
          prepStmt.executeUpdate();
          prepStmt.close();
        } else
          throw new OrderException("Not enough of " + bookId + " in stock to complete order.");
      }
    } catch (Exception ex) {
      throw new OrderException("Couldn't purchase book: " + bookId + ex.getMessage());
    }
  }
}