/**
 *
 */
package dbHelpers;

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

import model.Product;
import model.ShoppingCart;
import model.User;

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

	private Connection connection;
	private ResultSet results;

	public ReadShoppingCartQuery(String dbName, String uName, String pwd) {
		String url = "jdbc:mysql://localhost:3306/" + dbName;

		//set up driver
		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();
		}
	}
	//use usernum as a paramater ***
	public void doReadShoppingCart(User user) {
		String query = "SELECT * FROM ShoppingCart WHERE usernum = ?";

		try {
			PreparedStatement ps = this.connection.prepareStatement(query);
			ps.setInt(1, user.getUsernum());
			this.results = ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void doReadShoppingCartUpdate(User user, ShoppingCart cart) {
		String query = "SELECT * FROM ShoppingCart WHERE usernum = ? and Product_productID = ?";
		try {
			PreparedStatement ps = this.connection.prepareStatement(query);
			ps.setInt(1, user.getUsernum());
			ps.setInt(2, cart.getProductID());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public String getHTMLTable(ShoppingCart cart) {
		String table = "";
		table += "<table border=1>";
		table += "<tr>";
		table += "<td>";
		table += "Product Name";
		table += "</td>";
		table += "<td>";
		table += "Product Qty";
		table += "</td>";
		table += "<td>";
		table += "Product Price";
		table += "</td>";
		table += "<td>";
		table += "Update";
		table += "</td>";
		table += "<td>";
		table += "Delete";
		table += "</td>";
		table += "</tr>";

		try {
			while(this.results.next()) {
//				product.setProductName(this.results.getString("Product_productName"));
//				product.setProductPrice(Double.parseDouble(this.results.getString("Product_productPrice")));
//				product.setProductInventory(Integer.parseInt(this.results.getString("quantity")));
				cart.setUserID(this.results.getString("User_userID"));
				cart.setProductID(Integer.parseInt(this.results.getString("Product_productID")));
				cart.setProductQty(Integer.parseInt(this.results.getString("quantity")));
				cart.setUsernum(Integer.parseInt(this.results.getString("usernum")));
				cart.setProductPrice(Double.parseDouble(this.results.getString("Product_productPrice")));
				cart.setProductName(this.results.getString("Product_productName"));

				table += "<form name=deleteForm action=delete value=Delete>";
				table += "<tr>";
				table += "<td>";
				table += cart.getProductName();
				table += "</td>";
				table += "<td>";
				table += cart.getProductQty();
				table += "<input type=hidden name=productQty value=" + cart.getProductQty() + ">";
				table += "</td>";
				table += "<td>";
				table += cart.getProductPrice();
				table += "<input type=hidden name=productPrice value=" + cart.getProductPrice() + ">";
				table += "</td>";
				table += "<td>";
					table += "<a href=updateForm?usernum=" + cart.getUsernum() + "?productID=" + cart.getProductID() + " >update item</a>";
				table += "</td>";
				table += "<td>";
				table += "<input type=hidden name=productID value=" + cart.getProductID() + ">";
				table += "<input type=hidden name=userID vaalue =" + cart.getUserID() + ">";
				table += "<input type=submit name=deleteButton value=Delete>";
				table += "</td>";
				table += "</tr>";
				table += "</form>";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		table += "</table>";
		return table;
	}

	public String getHTMLUpdateForm(ShoppingCart cart) {
		String table = "";
		table += "<table border=1>";
		table += "<tr>";
		table += "<td>";
		table += "Product Name";
		table += "</td>";
		table += "<td>";
		table += "Product Qty";
		table += "</td>";
		table += "<td>";
		table += "Product Price";
		table += "</td>";
		table += "<td>";
		table += "Update";
		table += "</td>";
		table += "</tr>";




		try {
			while(this.results.next()) {
//				product.setProductName(this.results.getString("Product_productName"));
//				product.setProductPrice(Double.parseDouble(this.results.getString("Product_productPrice")));
//				product.setProductInventory(Integer.parseInt(this.results.getString("quantity")));
				cart.setUserID(this.results.getString("User_userID"));
				cart.setProductID(Integer.parseInt(this.results.getString("Product_productID")));
				cart.setProductQty(Integer.parseInt(this.results.getString("quantity")));
				cart.setUsernum(Integer.parseInt(this.results.getString("usernum")));
				cart.setProductPrice(Double.parseDouble(this.results.getString("Product_productPrice")));
				cart.setProductName(this.results.getString("Product_productName"));

				table += "<form name=updateForm action=update method=post";
				table += "<tr>";
				table += "<td>";
				table += cart.getProductName();
				table += "<input type=hidden name=productName value=" + cart.getProductName() + ">";
				table += "</td>";
				table += "<td>";
				table += "<input type=hidden name=oldProductQty value="+  cart.getProductQty() + ">";
				table += "<input type=text name=productQty value=" + cart.getProductQty() + ">";
				table += "</td>";
				table += "<td>";
				table += cart.getProductPrice();
				table += "<input type=hidden name=productPrice value=" + cart.getProductPrice() + ">";
				table += "<input type=hidden name=productID value=" +  cart.getProductID() + ">";
				table += "<input type=hidden name=usernum value=" +  cart.getUsernum() + ">";
				table += "<input type=hidden name=userID value=" + cart.getUserID() + ">";
				table += "</td>";
				table += "<td>";
				table += "<input type=submit name=updateButton value=Update>";
				table += "</tr>";
				table += "</form>";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		table += "</table>";
		return table;
	}

}