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

/**
 * @author Kyle Nakano & Connor Beckett-Lemus
 */
public class JDBCProject {
    static String USER;
    static String PASS;
    static String DBNAME;
    static final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
    static String DB_URL = "jdbc:derby://localhost:1527/";
/**
 * 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 void main(String[] args) {
        Scanner in = new Scanner(System.in);
        DB_URL = DB_URL + "jdbc_project" + ";user="+ "ck" + ";password=" + "ck";
        Connection conn = null; //initialize the connection
        Statement stmt = null;  //initialize the statement that we're using
        PreparedStatement pstmt = null;
        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);

            //STEP 4: Execute a query
            System.out.println("Creating statement...");
            stmt = conn.createStatement();
            String sql;
            int userChoice;

            do {
                Scanner menu = new Scanner(System.in);
                System.out.println("1. List writing groups\n"
                        + "2. List data for a group\n"
                        + "3. List publishers\n"
                        + "4. List data for publisher\n"
                        + "5. List book titles\n"
                        + "6. List data for a book\n"
                        + "7. Insert new book\n"
                        + "8. Insert a new publisher\n"
                        + "9. Remove a book\n"
                        + "0. Exit\n");
                System.out.printf("Select Output: ");
                userChoice = menu.nextInt();
                // INPUT VALIDATION HERE

                if ( userChoice == 1 ) {
                    sql = "SELECT GroupName FROM WritingGroup";
                    ResultSet rs = stmt.executeQuery(sql);
                    System.out.println("Group Names:");
                    while (rs.next()) {
                        String gname = rs.getString("GroupName");
                        System.out.println(dispNull(gname));
                    }
                    rs.close();
                }

                else if ( userChoice == 2 ) {
                    sql = "SELECT GroupName FROM WritingGroup";
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        String gname = rs.getString("GroupName");
                        System.out.println(dispNull(gname));
                    }
                    System.out.println("Type group name: ");
                    String sqlGroup = "SELECT * FROM WritingGroup WHERE GroupName = ?";
                    String userGroup = in.nextLine();
                    try {
                        pstmt = conn.prepareStatement(sqlGroup);
                        pstmt.setString(1, userGroup);
                        ResultSet rsNew = pstmt.executeQuery();
                        rsNew.next();
                        String gname = rsNew.getString("GroupName");
                        String hwriter = rsNew.getString("HeadWriter");
                        String year = rsNew.getString("YearFormed");
                        String subject = rsNew.getString("Subject");
                        System.out.println("Group Name: " + gname + "\n"
                                + "Head Writer: " + hwriter + "\n"
                                + "Year Formed: " + year + "\n"
                                + "Subject: " + subject + "\n");
                        rsNew.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    rs.close();
                }

                else if ( userChoice == 3 ) {
                    sql = "SELECT PublisherName FROM Publisher";
                    ResultSet rs = stmt.executeQuery(sql);
                    System.out.println("Publishers: ");
                    while (rs.next()) {
                        String pname = rs.getString("PublisherName");
                        System.out.println(dispNull(pname));
                    }
                    rs.close();
                }

                else if ( userChoice == 4 ) {
                    sql = "SELECT PublisherName FROM Publisher";
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        String gname = rs.getString("PublisherName");
                        System.out.println(dispNull(gname));
                    }
                    System.out.println("Type publisher name: ");
                    String sqlGroup = "SELECT * FROM Publisher WHERE PublisherName = ?";
                    String userGroup = in.nextLine();
                    try {
                        pstmt = conn.prepareStatement(sqlGroup);
                        pstmt.setString(1, userGroup);
                        ResultSet rsNew = pstmt.executeQuery();
                        rsNew.next();
                        String pname = rsNew.getString("PublisherName");
                        String padd = rsNew.getString("PublisherAddress");
                        String pphone = rsNew.getString("PublisherPhone");
                        String pemail = rsNew.getString("PublishEmail");
                        System.out.println("Publisher Name: " + pname + "\n"
                                + "Publisher Address: " + padd + "\n"
                                + "Publisher Phone: " + pphone + "\n"
                                + "Publisher Email: " + pemail + "\n");
                        rsNew.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    rs.close();
                }

                else if ( userChoice == 5 ) {
                    sql = "SELECT BookTitle FROM Book";
                    ResultSet rs = stmt.executeQuery(sql);
                    System.out.println("Book Titles: ");
                    while (rs.next()) {
                        String bname = rs.getString("BookTitle");
                        System.out.println(dispNull(bname));
                    }
                    rs.close();
                }

                else if ( userChoice == 6 ) {
                    sql = "SELECT BookTitle FROM Book";
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        String gname = rs.getString("BookTitle");
                        System.out.println(dispNull(gname));
                    }
                    System.out.println("Type book title: ");
                    String sqlGroup = "SELECT * FROM Book WHERE BookTitle = ?";
                    String userGroup = in.nextLine();
                    try {
                        pstmt = conn.prepareStatement(sqlGroup);
                        pstmt.setString(1, userGroup);
                        ResultSet rsNew = pstmt.executeQuery();
                        rsNew.next();
                        String btitle = rsNew.getString("BookTitle");
                        String gname = rsNew.getString("GroupName");
                        String pname = rsNew.getString("PublisherName");
                        String yearpub = rsNew.getString("YearPublished");
                        String numpage = rsNew.getString("NumberPages");
                        System.out.println("Book Title: " + btitle + "\n"
                                + "Group Name: " + gname + "\n"
                                + "Publisher Name: " + pname + "\n"
                                + "Year Published: " + yearpub + "\n"
                                + "Number of pages: " + numpage + "\n");
                        rsNew.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    rs.close();
                }

                else if ( userChoice == 7 ) {
                    System.out.println("Creating new book entry..");

                    String sqlGroup = "INSERT INTO Book VALUES(?,?,?,?,?)";

                    System.out.printf("Book title: ");
                    String bookTitle = in.nextLine();
                    System.out.printf("Group Name: ");
                    String gName = in.nextLine();
                    System.out.printf("Publisher Name: ");
                    String pName = in.nextLine();
                    System.out.printf("Year Published: ");
                    String year = in.nextLine();
                    System.out.printf("Number of Pages: ");
                    String pages = in.nextLine();
                    try {
                        pstmt = conn.prepareStatement(sqlGroup);
                        pstmt.setString(1, bookTitle);
                        pstmt.setString(2, year);
                        pstmt.setString(3, pages);
                        pstmt.setString(4, gName);
                        pstmt.setString(5, pName);
                        pstmt.executeUpdate();
                        System.out.println("Entry added.");
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

                else if ( userChoice == 8 ) {
                    System.out.println("Creating new publisher entry..");

                    String sqlGroup = "INSERT INTO Publisher VALUES(?,?,?,?)";

                    System.out.printf("Publisher Name: ");
                    String pName = in.nextLine();
                    System.out.printf("Publisher Address: ");
                    String pAdd = in.nextLine();
                    System.out.printf("Publisher Phone: ");
                    String pPhone = in.nextLine();
                    System.out.printf("Publisher Email: ");
                    String pEmail = in.nextLine();
                    try {
                        pstmt = conn.prepareStatement(sqlGroup);
                        pstmt.setString(1, pName);
                        pstmt.setString(2, pAdd);
                        pstmt.setString(3, pPhone);
                        pstmt.setString(4, pEmail);
                        pstmt.executeUpdate();
                        System.out.println("Entry added.");
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                    System.out.printf("Would you like new entry to replace an old entry? Y to replace: ");
                    String replace = in.nextLine();
                    if ( replace.equalsIgnoreCase("y")) {
                        sql = "SELECT PublisherName FROM Publisher";
                        ResultSet rs = stmt.executeQuery(sql);


                        while (rs.next()) {
                            String pname = rs.getString("PublisherName");
                            System.out.println(dispNull(pname));
                        }

                        System.out.printf("Type publisher name to replace: ");
                        String oldPub = in.nextLine();
                        String updatePub = "UPDATE Book SET PublisherName = ? WHERE PublisherName = ?";
                        try {
                            pstmt = conn.prepareStatement(updatePub);
                            pstmt.setString(1, pName);
                            pstmt.setString(2, oldPub);
                            pstmt.executeUpdate();
                            System.out.println("Publisher Updated.");
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }

                        rs.close();
                    }
                }

                else if ( userChoice == 9 ) {
                    sql = "SELECT BookTitle,GroupName FROM Book";
                    ResultSet rs = stmt.executeQuery(sql);

                    while (rs.next()) {
                        String bname = rs.getString("BookTitle");
                        String gname = rs.getString("GroupName");
                        System.out.println(dispNull(bname) + "  |  " + dispNull(gname));
                    }

                    System.out.printf("Enter book title to delete: ");
                    String userBook = in.nextLine();
                    System.out.printf("Enter group name: ");
                    String userGName = in.nextLine();

                    String delBook = "DELETE FROM Book WHERE BookTitle = ? AND GroupName = ?";
                    try {
                        pstmt = conn.prepareStatement(delBook);
                        pstmt.setString(1, userBook);
                        pstmt.setString(2, userGName);
                        pstmt.executeUpdate();
                        System.out.println("Book Removed.");
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }

                    rs.close();
                }
            } while ( userChoice != 0 );

            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}