package cart.dbUtils;

import cart.Category;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

//CategoryDBUtil
//copyright - Lawrence Truett - FluffyCat.com
//date - May 23, 2003 - San Diego, CA
public class CategoryDBUtil {
    
    public static Category getCategoryForKey(long key, 
                                             DBConnection dbc) {
        ArrayList categoriesAL = new ArrayList();
        Category categoryToReturn = null;
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "Select * from category where key = ?");
            preparedStatement.setLong(1, key);
            ResultSet resultSet = preparedStatement.executeQuery();
            
            resultSet.first();
            categoryToReturn = 
                new Category(resultSet.getLong(1), 
                             resultSet.getLong(2), 
                             resultSet.getString(3));
            resultSet.close();
            preparedStatement.close();
            
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in " +
                "CategoryDBUtil.getCategoryForKey: " + 
                sqlException);
        }
        return categoryToReturn;
    }
    
    public static ArrayList getChildCategoriesForKey(
    		long key, DBConnection dbc) {
        ArrayList categoriesAL = new ArrayList();
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "Select * from category where parent = ?");
            preparedStatement.setLong(1, key);
            ResultSet resultSet = preparedStatement.executeQuery();
            
            while (resultSet.next()) {
                Category category = 
                    new Category(resultSet.getLong(1), 
                                 resultSet.getLong(2), 
                                 resultSet.getString(3));
                if (category.getKey() != category.getParent()) {
                    categoriesAL.add(category);
                }
            }
            resultSet.close();
            preparedStatement.close();
            
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in " +
                          "CategoryDBUtil.getChildCategoryForKey: " + 
                          sqlException);
        }
        
        return categoriesAL;
    }
    
    public static ArrayList getSiblingCategoriesForKey(
    		long key, DBConnection dbc) {
        //...can also get parent category and then get children for it,
        //    which may be more efficent...
        ArrayList categoriesAL = new ArrayList();
        try {
            Connection connection = dbc.getConnection();
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "Select * from category where parent = " +
                "(Select parent from category where key = ?)");
            preparedStatement.setLong(1, key);
            ResultSet resultSet = 
                preparedStatement.executeQuery();
            while (resultSet.next()) {
                categoriesAL.add(new Category(resultSet.getLong(1), 
                                     resultSet.getLong(2), 
                                     resultSet.getString(3)));
            }
            resultSet.close();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            System.out.println(
                "The following error occured in " +
                "CategoryDBUtil.getSiblingCategoryForKey: " + 
                sqlException);
        }
        return categoriesAL;
    }
    
    public static void insert(long key, 
                              long parent, 
                              String description, 
                              Connection connection) {
        try {
            PreparedStatement preparedStatement =
            connection.prepareStatement(
                "INSERT INTO category values(?,?,?);");
            preparedStatement.setLong(1, key);
            preparedStatement.setLong(2, parent);
            preparedStatement.setString(3, description);
            preparedStatement.execute();
            preparedStatement.close();
        } catch (java.sql.SQLException sqlException) {
            System.out.println("The following error occured in "+
                "CategoryDBUtil.insert: " + 
                sqlException);
        }
    }
}