/**
 *
 */
package dbHelpers;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



/**
 * @author timothyeber
 *
 */
public class DeleteQuery {

	private Connection connection;
	private ResultSet results;
	int productQtyDeleted;

	public DeleteQuery(String dbName, String uname, String pwd){

		String url = "jdbc:mysql://localhost:3306/"+dbName;

		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			this.connection = DriverManager.getConnection(url, uname, pwd);
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void doReadProductQty(int productID, int usernum) {
		String query = "SELECT quantity from ShoppingCart where Product_productID = ? and usernum = ?";
			int productQty = 0;
		try {
			PreparedStatement ps = connection.prepareStatement(query);
			ps.setInt(1, productID);
			ps.setInt(2, usernum);

			this.results = ps.executeQuery();


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public int doGetProductQty() {
		int productQtyDeleted = 0;
		try {
			while(this.results.next()) {
				productQtyDeleted = results.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return productQtyDeleted;
	}

	public void doUpdateProductQuantity(int productID, int productQtyDeleted) {
		String query = "UPDATE Product set productInventory = productInventory + ? where productID = ?";

		try {
			PreparedStatement ps = connection.prepareStatement(query);

			ps.setInt(1, productQtyDeleted);
			ps.setInt(2, productID);

			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void doDelete(int productID, int usernum){
		// set up a String to hold our query
		String query = "delete from ShoppingCart where Product_productID = ? and usernum = ?";

		// create a preparedstatement using our query string
		try {
			PreparedStatement ps = connection.prepareStatement(query);

			// fill in the preparedstatement
			ps.setInt(1, productID);
			ps.setInt(2, usernum);

			// execute the query
			ps.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}




	}

}