package store;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class TestDB
{
	public static void main(String[] args) throws Exception
	{
		Connection conn = DriverManager
				.getConnection("jdbc:mysql://localhost/onlinestore?" + "user=andra&password=Turry123");

		PreparedStatement prepareStatementUpdate = conn.prepareStatement("UPDATE PRODUCTS SET coloUr=? WHERE ID=1103");
		prepareStatementUpdate.setString(1, "WHITE");
		prepareStatementUpdate.executeUpdate();

		PreparedStatement prepareStatementSelect = conn.prepareStatement("SELECT * FROM PRODUCTS");
		ResultSet rs = prepareStatementSelect.executeQuery();
		while (rs.next())
		{
			System.out.println(rs.getString("ID") + "\t" + rs.getString("productCode") + "\t" + rs.getString("colour"));
		}

		/*
		 * String createTable = "CREATE TABLE CART (" +
		 * " ID INT(11) NOT NULL AUTO_INCREMENT, " + " PRIMARY KEY (`ID`))";
		 *
		 * PreparedStatement prepareStatementCreate =
		 * conn.prepareStatement(createTable);
		 * prepareStatementCreate.executeUpdate();
		 */

		CartCsvParser cartParse = new CartCsvParser();
		List<Cart> cart = new ArrayList<Cart>();
		List<Product> productList = new ArrayList<>();
		String insertProduct = "INSERT INTO PRODUCTS " + "(id,productCode,productName,colour,price,quantity) VALUES"
				+ "(?,?,?,?,?,?)";
		// PreparedStatement prepareStatementUpdateCart =
		// conn.prepareStatement("INSERT INTO PRODUCTS (id) VALUES (?)");
		PreparedStatement prepareStatementUpdateProduct = conn.prepareStatement(insertProduct);
		cart = cartParse.parseCsvFile("D:\\JAVA\\workspaces\\OCA\\OnlineStore\\src\\Carts.csv",
				"D:\\JAVA\\workspaces\\OCA\\OnlineStore\\src\\ERR.csv");
		for (Cart c : cart)
		{

			prepareStatementUpdateProduct.setInt(1, c.getId());
			for (Product p : c.getProductList())
			{
				prepareStatementUpdateProduct.setInt(2, p.getCode());
				prepareStatementUpdateProduct.setString(3, p.getName());
				prepareStatementUpdateProduct.setString(4, p.getColour());
				prepareStatementUpdateProduct.setDouble(5, p.getPrice());
				prepareStatementUpdateProduct.setInt(6, p.getQuantity());
				prepareStatementUpdateProduct.executeUpdate();

			}
		}

	}
}