import java.util.*;
import java.sql.*;
import java.io.*;

public class myjdbc {
    public static void main(String[] args) {
        try {
            System.out.println("Loading the driver...");
            Class.forName("org.postgresql.Driver");
            System.out.println("Connecting...");
            Connection db = DriverManager.getConnection("jdbc:postgresql://laomedon.cs.brandeis.edu/jsheenaPA2","jsheena", "hwul2g@cs4m");
            System.out.println("Creating statement...");
            Statement statement = db.createStatement();

            while (true) {
                // Interact with the user
                System.out.print("Enter 1 for query 1, 2 for query 2,3 for all regions, or x to quit: ");
                String s = getStringFromUser();
                if (s.equals("x")) {
                    // Close all connections
                    statement.close();
                    db.close();
                    System.exit(0);
                } else if (s.equals("1")) {
                    // Run the query
                    String query = "SELECT n_name, SUM(l_extendedprice * (1 - l_discount)) AS revenue "+
                        "FROM customer, orders, lineitem, supplier, nation, region "+
                        "WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey "+
                        "AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'ASIA' AND o_orderdate >= date '1995-01-01' "+
                        "AND o_orderdate < date '1997-01-01' + interval '1' year "+
                        "GROUP BY n_name "+
                        "ORDER BY revenue DESC;";

                    ResultSet rs = statement.executeQuery(query);
                    // Show the results
                    printResult(rs);
                } else if (s.equals("2")) {
                        String query1 = "Select n_nationkey,n_name from nation;";
                        ResultSet rs1 = statement.executeQuery(query1);
                        printResult(rs1);
                        System.out.print("Select nation to use for query:");
                        String s2 = getStringFromUser();
                        String query2 = "select s_name, count(*) as numwait "+
                        "from supplier, lineitem l1, orders, nation " +
                        "where s_suppkey=l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus='F' and n_nationkey='"+
                        s2+"' and exists (select * from lineitem l2 "+
                        "where l2.l_orderkey=l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey) "+
                        "and not exists (select * from lineitem l3 "+
                        "where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey "+
                        "and l3.l_receiptdate > l3.l_commitdate) "+
                        "group by s_name order by numwait desc, s_name;";
                        ResultSet rs2 = statement.executeQuery(query2);
                        printResult(rs2);
                } else if (s.equals("3")) {
                        String query = "create table tmp (name text, contains text);";
                        statement.executeQuery(query);
                        boolean notdone = true;
                        query = "insert into tmp (select * from allregions);";
                        statement.executeQuery(query);
                        query = "select * from tmp;";
                        ResultSet base = statement.executeQuery(query);
                        while (notdone) {
                                query = "insert into tmp ( select b.name,a.contains from tmp as a cross join tmp as b where b.contains=a.name union select * from tmp;";
                                ResultSet rs = statement.executeQuery(query);
                                if (rs == base) {
                                        printResult(rs);
                                        notdone = false;
                                }else {
                                        base = rs;
                                }
                        }
                }
        } catch (Exception e) {
            System.out.println("SQLException: " + e.getMessage());
            e.printStackTrace();
        }
    }

    // Call this to print a result
    public static void printResult(ResultSet rs) {
        try {
            // Retrieve the column information from the result
            ResultSetMetaData rsmd = rs.getMetaData();
            int numberOfColumns = rsmd.getColumnCount();

            int rowCount = 1;

            // Standard way to loop through returned rows
            while (rs.next()) {
                System.out.print("Row " + rowCount + ":  ");
                for (int i = 1; i <= numberOfColumns; i++) {
                    System.out.print(rs.getString(i)+" ");
                }
                System.out.println("");
                rowCount++;
            }
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            e.printStackTrace();
            System.exit(-1);
        }
    }

    // Use this to get input from the user at the console
    public static String getStringFromUser()
    {
        BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
        String cmd = "";
        try
            {
                while (((cmd = in.readLine()) != null)) {
                    return cmd;
                }
            }
        catch(IOException aIOException)
            {
                System.out.println("Exception trying to get string from user: " + aIOE
xception.getMessage());
                aIOException.printStackTrace();
            }
        return null;
    }

}