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.User;

public class ReadQuery {
// this is going to allow us to establish connection with database
// this will allow us to view all of the items in the data

	private Connection connection;
	private ResultSet results;

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

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

	public void doRead() {
		String query = "select * from Product";

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

	public String getHTMLTable(User user, User user2) {
		String table = "";
		table += "<table border=1>";
		table += "<tr>";
		table += "<td>";
		table += "Product Name";
		table += "</td>";
		table += "<td>";
		table += "Product Image";
		table += "</td>";
		table += "<td>";
		table += "Product Qty";
		table += "</td>";
		table += "<td>";
		table += "Product ID";
		table += "</td>";
		table += "<td>";
		table += "Order Qty";
		table += "</td>";
		table += "<td>";
		table += "Add To Cart";
		table += "</td>";
		table += "</tr>";
		try {
			while(this.results.next()) {
				Product product = new Product();
				product.setProductID(this.results.getInt("productID"));
				product.setProductName(this.results.getString("productName"));
				product.setProductImage(this.results.getString("productImage"));
				product.setProductPrice(this.results.getDouble("productPrice"));
				product.setProductCategory(this.results.getString("productCategory"));
				product.setProductDescription(this.results.getString("productDescription"));
				product.setProductInventory(this.results.getInt("productInventory"));


				table += "<form name=AddToCart action=AddToCart method=post>";


				table += "<tr>";
				table += "<td>";
				table += product.getProductName();
				table += "<input type=hidden name=productName value=" + product.getProductName() + ">";
				table += "</td>";
				table += "<td>";
				table += product.getProductImage();
				table += "</td>";
				table += "<td>";
				table += product.getProductPrice();
				table += "<input type=hidden name=productPrice value=" + product.getProductPrice() + ">";
				table += "</td>";
				table += "<td>";
				table += product.getProductID();
				table += "<input type=hidden name=productID value=" + product.getProductID() + ">";
				table += "</td>";
				table += "<td>";
				table += "<input type=number name=productQty value=0 min=0 max=" + product.getProductInventory() + ">";
				//table += "<input type=text name=productQty value=0>";
				table += "</td>";
				table += "<td>";
				table += "<input type=submit name=submit value=Add>";
				table += "<input type=hidden name=productName value=" + product.getProductName() + ">";
				table += "<input type=hidden name=userID value=" + user.getUserID() + " >";
				table += "<input type=hidden name=usernum2 value=" + user2.getUsernum() + ">";

				// check product against inventory
				table += "</td>";
				table += "</tr>";

				table += "</form>";
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		table += "</table>";
		return table;

	}

}