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

/**
 *
 * @author Stefin Mathew and Jorge Martinez
 */
public class JDBC_Project {
    //  Database credentials
    static String USER;
    static String PASS;
    static String DBNAME;

    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;
    }
    /**
     * Displays the menu for the user to choose which functions of the database to perform
     * @param in    Scanner object to get input from user
     * @return  The choice of user in integer format
     */
    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. Delete a book");
            System.out.println("10. Quit");

            x = in.nextInt();
            in.nextLine();
        } while(x < 1 || x > 10);
        return x;
    }
    /**
     * Displays the details of the a particular writing group
     * @param g_name    PreparedStatement object used to execute SQL statements.
     * @param input The name of the writing group
     */
    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();
        }
    }
    /**
     * Displays the details of the a particular publisher
     * @param p_name    PreparedStatement object used to execute SQL statements.
     * @param input The name of the publisher
     */
    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();
        }
    }
    /**
     * Displays the details of the a particular book
     * @param b_name    PreparedStatement object used to execute SQL statements.
     * @param input The name of the book
     * @param conn Connection object used to execute SQL statements that involve the database connection
     */
    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 year_pub = rs.getString("YEAR_PUBLISHED");
                String numOfPages = rs.getString("NUMOFPAGES");

                //Display values
                //System.out.println("Group Name: " + dispNull(group_name));
                System.out.println("Book Title: " + dispNull(book_title));
                System.out.println();
                writingGroupRS(conn.prepareStatement("SELECT * FROM WRITINGGROUPS WHERE GROUP_NAME = ?"), group_name);
                System.out.println();
                publisherRS(conn.prepareStatement("SELECT * FROM PUBLISHERS WHERE PUBLISHER_NAME = ?"), pub_name);
                System.out.println();
                System.out.println("Year Published: " + dispNull(year_pub));
                System.out.println();
                System.out.println("Number of Pages: " + dispNull(numOfPages));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    /**
     * Displays all the books in the database
     * @param stmt Statement object used to create a ResultSet
     */
    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();
        }
    }
    /**
     * Displays all the publishers in the database
     * @param stmt Statement object used to create a ResultSet
     */
    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();
        }
    }
    /**
     * Displays all the writing groups in the database
     * @param stmt Statement object used to create a ResultSet
     */
    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 != 10){
                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;
                    String group = "";
                    listAllGroups(stmt);
                    while(true){
                        System.out.print("Enter book group's name: ");
                        group = in.nextLine();
                        g_name = conn.prepareStatement("SELECT * FROM WRITINGGROUPS WHERE GROUP_NAME = ?");
                        g_name.setString(1, group);
                        rs = g_name.executeQuery();
                        if(rs.next())
                            break;
                        System.out.println("Group does not exist! Try Again");
                    }

                    writingGroupRS(g_name, group);
                }
                else if(choice == 3){
                    listAllPubs(stmt);
                }
                else if(choice == 4){
                    PreparedStatement p_name = null;
                    String pubName = "";
                    listAllPubs(stmt);
                    while(true){
                        System.out.print("Enter publisher name: ");
                        pubName = in.nextLine();
                        p_name = conn.prepareStatement("SELECT * FROM PUBLISHERS WHERE PUBLISHER_NAME = ?");
                        p_name.setString(1, pubName);
                        rs = p_name.executeQuery();
                        if(rs.next())
                            break;
                        System.out.println("Publisher does not exist! Try Again");
                    }
                    publisherRS(p_name, pubName);
                }
                else if(choice == 5){
                    listAllBooks(stmt);
                }
                else if(choice == 6){
                    PreparedStatement b_name = null;
                    String bookTitle = "";
                    while(true){
                        System.out.print("Enter book title: ");
                        bookTitle = in.nextLine();
                        b_name = conn.prepareStatement("SELECT * FROM BOOKS WHERE BOOK_TITLE = ?");
                        b_name.setString(1, bookTitle);
                        rs = b_name.executeQuery();
                        if(rs.next())
                            break;
                        System.out.println("Book does not exist! Try Again");
                    }

                    bookRS(b_name, bookTitle, 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();
                }
                else if(choice == 8){
                    /*PreparedStatement object to be used*/
                    PreparedStatement p_name = null;
                    /*Get publisher user wishes to replace*/
                    System.out.println("Which publisher is being bought off?");
                    String oldPublisher = in.nextLine();
                    /*Prepare to look for publisher*/
                    rs = stmt.executeQuery("select * from PUBLISHERS");
                    /*Will be true if publisher found in database*/
                    boolean validPublisher = false;
                    /*Check database for publisher to be removed*/
                    while(rs.next()){
                        if(rs.getString("PUBLISHER_NAME").equals(oldPublisher)){
                            validPublisher = true;
                        }
                    }
                    /*If publisher was not found, tell user and break*/
                    if(validPublisher == false){
                        System.out.println("That publisher is not in the data base....");

                    }else{
                        /*Get all information for new Publisher*/
                        System.out.print("Enter publisher name: ");
                        String pName = in.nextLine();
                        System.out.print("Enter publisher address: ");
                        String pAdd = in.nextLine();
                        System.out.print("Enter publisher phone: ");
                        String pPhone = in.nextLine();
                        System.out.print("Enter publisher email: ");
                        String email = in.nextLine();
                        /*Create new publisher with attributes*/
                        String sql2 = "insert into PUBLISHERS values(?,?,?,?)";
                        p_name = conn.prepareStatement(sql2);
                        p_name.setString(1, pName);
                        p_name.setString(2, pAdd);
                        p_name.setString(3, pPhone);
                        p_name.setString(4, email);
                        p_name.execute();
                        /*Prepare to replace old publisher with new one*/
                        rs = stmt.executeQuery("select * from BOOKS");
                        while(rs.next()){
                            if(rs.getString("PUBLISHER_NAME").equals(oldPublisher)){
                                    /*Old publisher replaced*/
                                    sql = "update BOOKS set PUBLISHER_NAME=? where PUBLISHER_NAME=?";
                                    p_name = conn.prepareStatement(sql);
                                    p_name.setString(1, pName);
                                    p_name.setString(2,oldPublisher);
                                    p_name.executeUpdate();
                            }
                        }
                    }
                    /*Delete old publisher*/
                    PreparedStatement st = conn.prepareStatement("delete from PUBLISHERS where PUBLISHER_NAME= ?");
                    st.setString(1, oldPublisher);
                    st.executeUpdate();
                    /*Debugging*/
                    listAllPubs(stmt);
                    listAllBooks(stmt);
                }
                else if(choice == 9){
                    listAllBooks(stmt);
                    PreparedStatement removeSQL = null;
                    String removeBook = "";
                    String pubName = "";
                    while(true){
                        System.out.print("Enter book title: ");
                        removeBook = in.nextLine();
                        removeSQL = conn.prepareStatement("SELECT COUNT(BOOK_TITLE) FROM BOOKS WHERE BOOK_TITLE = ?");
                        removeSQL.setString(1, removeBook);
                        rs = removeSQL.executeQuery();
                        if(rs.next()){
                            int duplicates = rs.getInt(1);
                            if(duplicates == 1){
                                removeSQL = conn.prepareStatement("DELETE FROM BOOKS WHERE BOOK_TITLE = ?");
                                removeSQL.setString(1, removeBook);
                                removeSQL.execute();
                                System.out.println("Book Deleted!");
                                break;
                            }
                            else if(duplicates > 1){
                                listAllPubs(stmt);
                                System.out.print("Enter publisher name: ");
                                pubName = in.nextLine();
                                removeSQL = conn.prepareStatement("SELECT PUBLISHER_NAME FROM BOOKS WHERE PUBLISHER_NAME = ?");
                                removeSQL.setString(1, pubName);
                                rs = removeSQL.executeQuery();
                                if(rs.next()){
                                    removeSQL = conn.prepareStatement("DELETE FROM BOOKS WHERE BOOK_TITLE = ? AND PUBLISHER_NAME = ?");
                                    removeSQL.setString(1, removeBook);
                                    removeSQL.setString(2, pubName);
                                    removeSQL.execute();
                                    System.out.println("Book Deleted!");
                                    break;
                                }
                                System.out.println("Publisher does not exist! Try Again");
                            }
                        }
                        System.out.println("Book does not exist! Try Again");
                    }
                    removeSQL = conn.prepareStatement("SELECT PUBLISHER_NAME FROM BOOKS WHERE BOOK_TITLE = ?");
                    removeSQL.setString(1, removeBook);
                    rs = removeSQL.executeQuery();

                }

            }
            //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!");
    }
}