package jdbc_gui;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class DatabaseController {
    // These connection setting will work on the lab computers
    // If you use a different database, feel free to change these settings!
    private static final String DB_URL     = "jdbc:oracle:thin:@localhost:1521:xe";
    private static final String DB_USER    = "hr";
    private static final String DB_PW      = "hr";

    public static boolean testDriver()
    {
        // Code to test the driver is loaded
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
	}
        catch (ClassNotFoundException e)
        {
            // Display an error message on the GUI if the driver is not loaded
            ProductGUI.displayError("Missing Oracle JDBC Driver?");
            return false;
	}

        ProductGUI.displayMessage("JDBC Driver Loaded!");
        return true;
    }

    public static Connection getConnection()
    {
        try
        {
            return DriverManager.getConnection(DB_URL, DB_USER, DB_PW);
        }
        catch(SQLException e)
        {
            ProductGUI.displayError("Invalid Database Connection parameters!");
            return null;
        }
    }

    public static ArrayList<ProductCategory> getCategories() throws SQLException
    {
        Connection connection = getConnection();
        Statement statement = connection.createStatement();

        String sqlCommand = "SELECT * FROM ProductCategory";
        ResultSet rset = statement.executeQuery(sqlCommand);

        ArrayList<ProductCategory> categoryList = new ArrayList<>();
        while(rset.next())
        {
            int id = rset.getInt("PRODUCTCATEGORYID");
            String name = rset.getString("PRODUCTCATEGORYNAME");

            categoryList.add(new ProductCategory(id, name));
        }

        if (statement != null)
            statement.close();
        if (connection != null)
            connection.close();

        return categoryList;
    }

    public static ArrayList<Product> getProducts() throws SQLException
    {
        ArrayList<Product> productList = new ArrayList<Product>();

        // TODO 1 - 1- Read all product from database using a prepared statement
        //          2- From the resultset, instanciate products, add in the array


       // productList.add(new Product(1, 1, "test", "test desc", 10, 12.12));
        Connection dbConnection = getConnection();
        Statement statement = dbConnection.createStatement();

        ResultSet rs = statement.executeQuery("SELECT * FROM product");

        while(rs.next()){
            int productId = rs.getInt("PRODUCTID");
            int productCatId = rs.getInt("PRODUCTCATEGORYID");
            String prodName = rs.getString("PRODUCTNAME");
            String prodDesc = rs.getString("PRODUCTDESCRIPTION");
            int prodInv = rs.getInt("PRODUCTINVENTORYAMOUNT");
            double price = rs.getDouble("PRODUCTPRICE");

            productList.add(new Product(productId, productCatId, prodName, prodDesc, prodInv, price));
        }

        return productList;
    }

    public static void insertProduct(Product product) throws SQLException
    {
        Connection dbConnection = null;
        PreparedStatement statement = null;
        //ArrayList<Product> productList = new ArrayList<Product>();
        String insertTableSQL = "INSERT INTO product"
				+ "(productid, productcategoryid, productname, productdescription, productinventoryamount, productprice) VALUES"
				+ "(?,?,?,?,?,?)";

        try {
            dbConnection = getConnection();
            statement = dbConnection.prepareStatement(insertTableSQL);

            statement.setInt(1, product.getId());
            statement.setInt(2, product.getCategoryId());
            statement.setString(3, product.getName());
            statement.setString(4, product.getDescription());
            statement.setInt(5, product.getInventory());
            statement.setDouble(6, product.getPrice());

            statement.execute();
            getProducts();

        } catch (NumberFormatException e) {
            System.out.println(e.getMessage());
        }
        //productList.add(product);
    }

    public static void updateProduct(Product product) throws SQLException
    {
        Connection dbConnection = null;
        PreparedStatement statement = null;
        //ArrayList<Product> productList = new ArrayList<Product>();
          String updateTableSQL = "UPDATE product"
                                + " SET productid = ?, "
                                + "productcategoryid = ?, "
                                + "productname = ?,"
                                + " productdescription = ?,"
                                + " productinventoryamount = ?,"
                                + " productprice = ? "
                                + "WHERE productid = ?";
        try {
            dbConnection = getConnection();
            statement = dbConnection.prepareStatement(updateTableSQL);

            statement.setInt(1, product.getId());
            statement.setInt(2, product.getCategoryId());
            statement.setString(3, product.getName());
            statement.setString(4, product.getDescription());
            statement.setInt(5, product.getInventory());
            statement.setDouble(6, product.getPrice());
            statement.setInt(7, product.getId());

            statement.executeUpdate();
            getProducts();

        } catch (NumberFormatException e) {
            System.out.println(e.getMessage());
        }
        // TODO 3 - Run a prepared statement to Update the product in the database
    }

    public static void deleteProduct(int productId) throws SQLException
    {
        Connection dbConnection = null;
        PreparedStatement statement = null;

        String removeTableSQL = "DELETE product "
                              +"WHERE productid = ?";
        try{
            dbConnection = getConnection();
            statement = dbConnection.prepareStatement(removeTableSQL);
            statement.setInt(1, productId);
            statement.execute();
        } catch(NumberFormatException e){
            System.out.println(e.getMessage());
        }
    }

    private static int executeQuery(String select_maXproductid_FROM_product) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
}