package cart.dbUtils;

import cart.ItemInventory;
import java.util.ArrayList;
import java.util.ListIterator;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

//ItemInventoryDBUtil
//author - Lawrence Truett - FluffyCat.com
//date - July 7, 2003 - San Diego, CA
public class ItemInventoryDBUtil {
    private long itemKey;
    private long quantity;
    
    ItemInventoryDBUtil() {}
    
    public static long getInventoryForItemKey(long itemKeyIn, 
                                              DBConnection dbc) {
        long longToReturn = 0;
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "Select * from itemInventory where item = ?");
            preparedStatement.setLong(1, itemKeyIn);
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.first();
            longToReturn = resultSet.getLong(2);
            resultSet.close();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "ItemInventoryDBUtil.getQuantityForItem: " + 
                sqlException);
        }
        return longToReturn;
    }
    
    public static ItemInventory getItemInventoryForItemKey(
    		long itemKeyIn,
    		DBConnection dbc) {
        ItemInventory itemInventoryToReturn = null;
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement("Select * from "+
                "itemInventory where item = ?");
            preparedStatement.setLong(1, itemKeyIn);
            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.first();
            itemInventoryToReturn = 
                new ItemInventory(itemKeyIn, 
                                  resultSet.getLong(2), 
                                  resultSet.getLong(3));
            resultSet.close();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "ItemInventoryDBUtil.getQuantityForItem: " + 
                sqlException);
        }
        return itemInventoryToReturn;
    }
    
    public static ArrayList getAllItemInventory(DBConnection dbc) {
        ArrayList itemsToReturn = new ArrayList();
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement("Select * from itemInventory");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                itemsToReturn.add(
                    new ItemInventory(resultSet.getLong(1), 
                                      resultSet.getLong(2), 
                                      resultSet.getLong(3)));
            }
            resultSet.close();
            preparedStatement.close();
            
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                               "ItemInventoryDBUtil.getAllItems: " + 
                               sqlException);
        }
        return itemsToReturn;
    }
    
    public static void insert(long item, 
                              long quantity, 
                              long reserved, 
                              DBConnection dbc) {
        Connection connection = dbc.getConnection();
        insert(item, quantity, reserved, connection);
    }
    
    public static void insert(long item, 
                              long quantity, 
                              long reserved, 
                              Connection connection) {
        try {
            PreparedStatement preparedStatement =
                connection.prepareStatement(
                    "INSERT INTO itemInventory values(?,?,?);");
            preparedStatement.setLong(1, item);
            preparedStatement.setLong(2, quantity);
            preparedStatement.setLong(3, reserved);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
            "ItemInventoryDBUtil.insert: " + 
            sqlException);
        }
    }
    
    public static void updateInventory(long item, 
                                       long quantity, 
                                       DBConnection dbc) {
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
        "UPDATE itemInventory set inventory = ? where itemKey = ?");
            preparedStatement.setLong(1, quantity);
            preparedStatement.setLong(2, item);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "ItemInventoryDBUtil.insert: " + sqlException);
        }
    }
    
    public static void updateInventoryReserved(
    		ItemInventory itemInventory, DBConnection dbc) {
        updateInventoryReserved(itemInventory.getItemKey(), 
                                itemInventory.getInventory(), 
                                itemInventory.getReserved(), 
                                dbc);
    }
    
    public static void updateInventoryReserved(long item, 
                                               long quantity, 
                                               long reserved, 
                                               DBConnection dbc) {
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "UPDATE itemInventory set inventory = ?, "+
                    "reserved = ?, where itemKey = ?");
            preparedStatement.setLong(1, quantity);
            preparedStatement.setLong(2, reserved);
            preparedStatement.setLong(3, item);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "ItemInventoryDBUtil.insert: " + sqlException);
        }
    }
    
    public static void updateReserved(long item, 
                                      long reserved, 
                                      DBConnection dbc) {
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
        "UPDATE itemInventory set reserved = ?, where itemKey = ?");
            preparedStatement.setLong(1, reserved);
            preparedStatement.setLong(2, item);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "ItemInventoryDBUtil.insert: " + sqlException);
        }
    }
}