import java.sql.*;
import java.util.Scanner;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Mimi Opkins with some tweaking from Dave Brown
 */
public class JDBC_Project {
    //  Database credentials
    static String USER;
    static String PASS;
    static String DBNAME;
    //This is the specification for the printout that I'm doing:
    //each % denotes the start of a new field.
    //The - denotes left justification.
    //The number indicates how wide to make the field.
    //The "s" denotes that it's a string.  All of our output in this test are
    //strings, but that won't always be the case.
    static final String displayFormat="%-5s%-15s%-15s%-15s\n";
// JDBC driver name and database URL
    static final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
    static String DB_URL = "jdbc:derby://localhost:1527/";
//            + "testdb;user=";
/**
 * Takes the input string and outputs "N/A" if the string is empty or null.
 * @param input The string to be mapped.
 * @return  Either the input string or "N/A" as appropriate.
 */
    public static String dispNull (String input) {
        //because of short circuiting, if it's null, it never checks the length.
        if (input == null || input.length() == 0)
            return "N/A";
        else
            return input;
    }

    public static int menu(Scanner in){
        int x = 0;
        do{
            System.out.println("What do you want to do?");
            System.out.println("1. List all writing groups");
            System.out.println("2. List all data for a specific groups");
            System.out.println("3. List all publishers");
            System.out.println("4. List all data for a specific publisher");
            System.out.println("5. List all book titles");
            System.out.println("6. List all data for a specific book");
            System.out.println("7. Insert a new book");
            System.out.println("8. Insert new publisher");
            System.out.println("9. Quit");

            x = in.nextInt();
            in.nextLine();
        } while(x < 1 || x > 9);
        return x;
    }

    public static void writingGroupRS(PreparedStatement g_name, String input){
        ResultSet rs = null;
        try {
            g_name.setString(1, input);
            g_name.execute();
            rs = g_name.getResultSet();
            while (rs.next()) {
                //Retrieve by column name
                String group_name = rs.getString("GROUP_NAME");
                String head_writer = rs.getString("HEAD_WRITER");
                String year_formed = rs.getString("YEAR_FORMED");
                String subject = rs.getString("SUBJECT");

                //Display values
                System.out.println("Group Name: " + dispNull(group_name));
                System.out.println("Head Writer: " + dispNull(head_writer));
                System.out.println("Year Formed: " + dispNull(year_formed));
                System.out.println("Subject: " + dispNull(subject));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    public static void publisherRS(PreparedStatement p_name, String input){
        ResultSet rs = null;
        try{
            p_name.setString(1, input);
            p_name.execute();
            rs = p_name.getResultSet();
            while (rs.next()) {
                //Retrieve by column name
                String pub_name = rs.getString("PUBLISHER_NAME");
                String pub_add = rs.getString("PUBlISHER_ADDRESS");
                String pub_phone = rs.getString("PUBLISHER_PHONE");
                String pub_email = rs.getString("PUBLISHER_EMAIL");

                //Display values
                System.out.println("Publisher Name: " + dispNull(pub_name));
                System.out.println("Publisher Address: " + dispNull(pub_add));
                System.out.println("Publisher Phone: " + dispNull(pub_phone));
                System.out.println("Publisher Email: " + dispNull(pub_email));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void bookRS(PreparedStatement b_name, String input, Connection conn){
        ResultSet rs = null;
        try{
            b_name.setString(1, input);
            b_name.execute();
            rs = b_name.getResultSet();
            while (rs.next()) {
                //Retrieve by column name
                String group_name = rs.getString("GROUP_NAME");
                String book_title = rs.getString("BOOK_TITLE");
                String pub_name = rs.getString("PUBLISHER_NAME");
                String numOfPages = rs.getString("NUMOFPAGES");

                //Display values
                System.out.println("Group Name: " + dispNull(group_name));
                System.out.println();
                writingGroupRS(conn.prepareStatement("SELECT * FROM WRITINGGROUPS WHERE GROUP_NAME = ?"), group_name);
                System.out.println();
                System.out.println("Book Title: " + dispNull(book_title));
                System.out.println("Publisher Name: " + dispNull(pub_name));
                System.out.println();
                publisherRS(conn.prepareStatement("SELECT * FROM PUBLISHERS WHERE PUBLISHER_NAME = ?"), pub_name);
                System.out.println();
                System.out.println("Number of Pages: " + dispNull(numOfPages));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void listAllBooks(Statement stmt){
        String sql = "SELECT * FROM BOOKS";
        try{
            ResultSet rs = stmt.executeQuery(sql);
            //STEP 5: Extract data from result set
            System.out.println("Book Titles:");
            while (rs.next()) {
                //Retrieve by column name
                String group_name = rs.getString("BOOK_TITLE");
                //Display values
                System.out.println(dispNull(group_name));
        }
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    public static void listAllPubs(Statement stmt){
        String sql = "SELECT * FROM PUBLISHERS";
        try{
            ResultSet rs = stmt.executeQuery(sql);
            //STEP 5: Extract data from result set
            System.out.println("Publisher Name:");
            while (rs.next()) {
                //Retrieve by column name
                String group_name = rs.getString("PUBLISHER_NAME");
                //Display values
                System.out.println(dispNull(group_name));
            }
        } catch(SQLException e){
            e.printStackTrace();
        }
    }

    public static void listAllGroups(Statement stmt){
        String sql = "SELECT * FROM WRITINGGROUPS";
        try{
            ResultSet rs = stmt.executeQuery(sql);
            //STEP 5: Extract data from result set
            System.out.println("Group Name:");
            while (rs.next()) {
                //Retrieve by column name
                String group_name = rs.getString("GROUP_NAME");
                //Display values
                System.out.println(dispNull(group_name));
            }
        } catch(SQLException e){
            e.printStackTrace();
        }
    }

    public static String insertNewPublisher(Scanner in, Connection conn){
        String pName, pAdd, pPhone, pEmail;
        String sql = "INSERT INTO PUBLISHERS VALUES(?, ?, ?, ?)";

        System.out.print("Enter publisher name: ");
        pName = in.nextLine();
        System.out.print("Enter publisher address: ");
        pAdd = in.nextLine();
        System.out.print("Enter publisher phone: ");
        pPhone = in.nextLine();
        System.out.print("Enter publisher email: ");
        pEmail = in.nextLine();
        try{
            PreparedStatement b_name = conn.prepareStatement(sql);
            b_name.setString(1, pName);
            b_name.setString(2, pAdd);
            b_name.setString(3, pPhone);
            b_name.setString(4, pEmail);
        }catch (SQLException e){
            e.printStackTrace();
        }
        return pName;
    }

    public static void main(String[] args) {
        //Prompt the user for the database name, and the credentials.
        //If your database has no credentials, you can update this code to
        //remove that from the connection string.
        Scanner in = new Scanner(System.in);
        System.out.print("Name of the database (not the user account): ");
        DBNAME = in.nextLine();
        System.out.print("Database user name: ");
        USER = in.nextLine();
        System.out.print("Database password: ");
        PASS = in.nextLine();
        //Constructing the database URL connection string
        DB_URL = DB_URL + DBNAME + ";user="+ USER + ";password=" + PASS;
        Connection conn = null; //initialize the connection
        Statement stmt = null;  //initialize the statement that we're using
        ResultSet rs = null;  //Initialize the result set
        try {
            //STEP 2: Register JDBC driver
            Class.forName("org.apache.derby.jdbc.ClientDriver");

            //STEP 3: Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL);
            System.out.println("Connected!");

            //Ask user what to do with database
            int choice = 0;
            while(choice != 9){
                choice = menu(in);

                //STEP 4: Execute a query
                stmt = conn.createStatement();
                String sql = "";


                if(choice == 1){
                    listAllGroups(stmt);
                }
                else if(choice == 2){
                    PreparedStatement g_name = null;
                    sql = "SELECT * FROM WRITINGGROUPS WHERE GROUP_NAME = ?";
                    g_name = conn.prepareStatement(sql);
                    System.out.print("Enter the group name: ");
                    String input = in.nextLine();
                    writingGroupRS(g_name, input);
                }
                else if(choice == 3){
                    listAllPubs(stmt);
                }
                else if(choice == 4){
                    PreparedStatement p_name = null;
                    sql = "SELECT * FROM PUBLISHERS WHERE PUBLISHER_NAME = ?";
                    p_name = conn.prepareStatement(sql);
                    System.out.print("Enter the publisher's name: ");
                    String input = in.nextLine();
                    publisherRS(p_name, input);
                }
                else if(choice == 5){
                    listAllBooks(stmt);
                }
                else if(choice == 6){
                    PreparedStatement b_name = null;
                    sql = "SELECT * FROM BOOKS WHERE BOOK_TITLE = ?";
                    b_name = conn.prepareStatement(sql);
                    System.out.print("Enter the book's title: ");
                    String input = in.nextLine();
                    bookRS(b_name, input, conn);
                }
                else if(choice == 7){
                    PreparedStatement bookSQL = null;
                    String pubName = "" , group = "";

                    listAllPubs(stmt);
                    while(true){
                        System.out.print("Enter publisher name: ");
                        pubName = in.nextLine();
                        bookSQL = conn.prepareStatement("SELECT PUBLISHER_NAME FROM PUBLISHERS WHERE PUBLISHER_NAME = ?");
                        bookSQL.setString(1, pubName);
                        rs = bookSQL.executeQuery();
                        if(rs.next())
                            break;
                        System.out.println("Publisher does not exist! Try Again");
                    }

                    listAllGroups(stmt);
                    while(true){
                        System.out.print("Enter book group's name: ");
                        group = in.nextLine();
                        bookSQL = conn.prepareStatement("SELECT GROUP_NAME FROM WRITINGGROUPS WHERE GROUP_NAME = ?");
                        bookSQL.setString(1, group);
                        rs = bookSQL.executeQuery();
                        if(rs.next())
                            break;
                        System.out.println("Group does not exist! Try Again");
                    }

                    System.out.print("Enter the book's title: ");
                    String title = in.nextLine();
                    System.out.print("Enter the year published: ");
                    String year = in.nextLine();
                    System.out.print("Enter the number of pages: ");
                    String pages = in.nextLine();


                    sql = "INSERT INTO BOOKS VALUES(?,?,?,?,?)";
                    bookSQL = conn.prepareStatement(sql);
                    bookSQL.setString(1, group);
                    bookSQL.setString(2, title);
                    bookSQL.setString(3, pubName);
                    bookSQL.setString(4, year);
                    bookSQL.setString(5, pages);
                    bookSQL.execute();
                    //rs = book.getResultSet();
                }

            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            //Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException se2) {
            }// nothing we can do
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }//end finally try
        }//end try
        System.out.println("Goodbye!");
    }//end main
}//end FirstExample}