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();

            }
        }

    }
}