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

/**
* Example app using Pointbase. This program contains such features as:
*    - poor error handling
*    - very few useful comments
*    - bad overall design (everything's static, yuck!)
*
* All these fabulous features were added intentionally. Your apps that use
* JDBC database access are expected to be written with a great deal more care
* than this one. The sole purpose of this application is to give you a
* quick-and-dirty look at database access in java (and with Pointbase in
* particular). Enjoy!
*/
public class DBInteractive {
    public static void main(String[] args) {
        // connect to the database
        Connection c = connect("friends", true);
        if (c == null) {
            System.err.println("Couldn't get a connection to the database, " + 
             "aborting.");
            System.exit(1);
        }

        // Create the table
        createFriendsTable(c);

        while (true) {
            switch (displayMenu()) {
                case 1 :
                    addNewFriend(c);
                    break;
                case 2 :
                    listFriends(c);
                    break;
                case 3 :
                    disconnect(c);
                    System.out.println("Goodbye.\n");
                    System.exit(0);
                    break;
                default :
                    break;
            }
        }
    }

    private static int displayMenu() {
        System.out.println("\nMy friends database - main menu");
        System.out.println("-------------------------------\n");

        System.out.println("What do you want to do?");
        System.out.println("  1) Add a new friend");
        System.out.println("  2) List all my friends");
        System.out.println("  3) Quit");
        System.out.print("Enter your choice [1|2|3]: ");

        BufferedReader stdin = new BufferedReader(
         new InputStreamReader(System.in));
        int choice = 0;
        try { choice = Integer.parseInt(stdin.readLine()); }
        catch (Exception e) {
            System.err.println("Are you messing with me? " +
             "what's your problem?");
            System.exit(1);
        }
        if (choice < 1 || choice > 3) {
            System.err.println("That wasn't one of the choices... " +
             "I'm confused, going away now.");
            System.exit(1);
        }

        return choice;
    }

    private static void addNewFriend(Connection connection) {
        String name = null;
        String phone = null;
        String address = null;

        BufferedReader stdin = new BufferedReader(
         new InputStreamReader(System.in));

        try {
            System.out.print("Enter new friend's name: ");
            name = stdin.readLine();
            System.out.print("Enter new friend's phone number: ");
            phone = stdin.readLine();
            System.out.print("Enter new friend's address: ");
            address = stdin.readLine();

            // build the insert statement
            String sql = "INSERT INTO FRIENDS VALUES('"
             + name + "', '" + phone + "', '" + address + "')";

            // record new friend in database
            connection.createStatement().executeUpdate(sql);
        } catch (Exception e) {
            System.err.println("Caught exception: " + e);
            disconnect(connection);
            System.exit(1);
        }

        System.out.println("New friend added.");
    }

    private static void listFriends(Connection connection) {
        String sql = "SELECT * FROM FRIENDS";
        ResultSet rs = null;

        try {
            // execute the SQL query
            rs = connection.createStatement().executeQuery(sql);

            // loop through the result set and print out one friend at a time
            int i = 1;
            while (rs.next()) {
                System.out.println("Friend #" + i + ": " + rs.getString(1) +
                 ", " + rs.getString(2) + ", " + rs.getString(3));
                i++;
            }
        }
        catch (Exception e) {
            System.err.println("Caught exception: " + e);
            disconnect(connection);
            System.exit(1);
        }
    }

    /*
    * Create the "FRIENDS" table (what if there's an error creating the
    * table?)
    */
    private static void createFriendsTable(Connection connection) {
        String sql = "CREATE TABLE FRIENDS ( " +
         "NAME CHAR(20) NOT NULL, " +
         "PHONE CHAR(20) NOT NULL, " +
         "ADDRESS CHAR(20) NOT NULL)";

        // swallow any exceptions, probably just a "table already exists"
        // exception which is fine (but what if it isn't???)
        try { connection.createStatement().executeUpdate(sql); 
        } catch (Exception e) { }
    }

    /**
    * This method creates and returns a JDBC connection object to the named
    * Pointbase database.
    *
    * @param dbName  name of the database to connect to
    * @param createDb  if true create the database if it doesn't already exist
    * @return  the database Connection if successful, null otherwise
    */
    // TODO: should print out error messages to System.err
    public static Connection connect(String dbName, boolean createDb) {
        boolean connected = false;
        Connection connection = null;

        String dburl = "jdbc:pointbase:" + dbName;

        // try to load the JDBC driver
        try {
            String driver = "com.pointbase.jdbc.jdbcUniversalDriver";
            Class.forName(driver).newInstance();
        } catch (Exception e) {
            return null;
        }

        // try to connect to an existing database as the "public" user
        try {
            // always connecting as the "public" user isn't the best, but it's
            // fine for csc309
            connection = DriverManager.getConnection(
             dburl, "public", "public");
        } catch (Exception e) {
            // swallow the exception here
        }

        // if we're connected return the connection object
        if (connection != null) return connection;

        // still not connected eh? DB probably doesn't exist, create a new
        // database if we're allowed to...
        if (createDb) {
            dburl = dburl + ",new";

            try {
                connection = DriverManager.getConnection(
                 dburl, "public", "public");
            } catch (Exception e) {
                // swallow the exception here
            }
        }

        // ok now I've tried everything, just return the connection object
        // wether it's null or not
        return connection;
    }

    /**
    * Close the given database connection.
    *
    * @param connection  the connection to close
    * @return  true if successful, false otherwise
    */
    // TODO: should print out error messages to System.err
    public static boolean disconnect(Connection connection) {
        try { connection.close(); } catch (Exception e) { return false; }
        return true;
    }
}