import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;


public class SelectProductsByPrice {
//DERBY SERVER CONNECTION STRING
    private static String dbURL = "jdbc:derby://localhost:1527/manfdb;create=false;";
    private static Connection conn = null;
    private static Statement stmt = null;
//MAIN START
    public static void main(String[] args) throws SQLException {
//SCRIPT START
        System.out.println("Give the price that you wish to get the number of PCs, Laptops, and Printers that are a higher price\nNote: it is case sensitive\n");
//INPUT START
        System.out.println("Price: ");
//GET INPUT
        Scanner sc = new Scanner(System.in);
        float price = Float.parseFloat(sc.nextLine());
//BEGIN CONNECTION
        Connection conn = createConnection();
//VARIABLES
        float numPcs = selectNumberOfProductsGreaterThanPrice("pc", price);
        float numLaptops = selectNumberOfProductsGreaterThanPrice("laptop", price);
        float numPrinters = selectNumberOfProductsGreaterThanPrice("printer", price);

        System.out.println("\n\tNumber of PCs: " + numPcs + "\n");
        System.out.println("\tNumber of Laptops: " + numLaptops + "\n");
        System.out.println("\tNumber of Printers: " + numPrinters + "\n");

        if(!conn.isClosed()) {
            System.out.println("\nClosing....");
            conn.close();
        }
    }
//CREATE THE CONNECTION TO THE DERBY DATABASE
    private static Connection createConnection() {
        try {
//GET DERBY DRIVER
            Class.forName("org.apache.derby.jdbc.ClientDriver").newInstance();
//GET A CONNECTION
            conn = DriverManager.getConnection(dbURL);
            return conn;
        }
        catch (Exception except) {
            except.printStackTrace();
        }
        return null;
    }
//GET COUNT OF PCS GREATER THAN PRICE
    private static float selectNumberOfProductsGreaterThanPrice(String tableName, float price) {
        try {
//CREATE STATEMENT AND QUERY
            stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("select count(*) from " + tableName + " A where A.price > " + price);
//PARSE RESULTS AND SAVE TO VECTOR
            while(results.next()) {
                return results.getFloat(1);
            }
            results.close();
            stmt.close();
        }
        catch (SQLException sqlExcept) {
            sqlExcept.printStackTrace();
        }
        return 1;
    }
}