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

/**
 * Created by dulciejackson on 13/12/2017.
 */
public class MainProject {

    /**
     * Connects to the database using the url, username and password supplied
     * @param user the username to be used
     * @param password the password used to access the database
     * @param database the url of the database to be accessed
     * @return the Connection once the database has been connected to
     */
    public static Connection connectToDatabase(String user, String password, String database) {
        System.out.println("-------- PostgreSQL " + "JDBC Connection Testing ------------");
        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Where is your PostgreSQL JDBC Driver? Include in your library path!");
            e.printStackTrace();
        }
        System.out.println("PostgreSQL JDBC Driver Registered!");

        Connection connection = null;
        try {
            connection = DriverManager.getConnection(database, user, password);
        } catch (SQLException e) {
            System.out.println("Connection Failed! Check output console");
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * Performs a query on the database connection provided
     * @param connection database connection to be used for the query
     * @param query the query to be executed on the database
     * @return the ResultSet returned from the query
     */
    public static ResultSet executeSelect(Connection connection, String query) {
        Statement st = null;
        try {
            st = connection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

        ResultSet rs = null;
        try {
            rs = st.executeQuery(query);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

        return rs;
    }

    /**
     * Drops a table from the database
     * @param connection database connection to drop the table from
     * @param table table to be dropped from the database
     */
    public static void dropTable(Connection connection, String table) {
        Statement st = null;
        try {
            st = connection.createStatement();
            st.execute("DROP TABLE " + table);
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Creates a table in the database
     * @param connection database connection to create the table in
     * @param tableDescription description of the table to be created
     */
    public static void createTable(Connection connection, String tableDescription) {
        // TODO: Add check for pre-existing table and drop it if there already
        Statement st = null;
        try {
            st = connection.createStatement();
            st.execute("CREATE TABLE " + tableDescription);
            st.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * Inserts file values into a database table
     * @param connection database connection to insert values in
     * @param table the table to insert data into
     * @param file the file to read information from
     * @return the number of rows read into the table
     */
    public static int insertIntoTableFromFile(Connection connection, String table, String file) {

        BufferedReader br = null;
        int numRows = 0;
        try {
            Statement st = connection.createStatement();
            String sCurrentLine, brokenLine[], composedLine = "";
            br = new BufferedReader(new FileReader(file));

            while ((sCurrentLine = br.readLine()) != null) {
                // Insert each line to the DB
                brokenLine = sCurrentLine.split(",");
                composedLine = "INSERT INTO customer VALUES (";
                int i;
                for (i = 0; i < brokenLine.length - 1; i++) {
                    composedLine += "'" + brokenLine[i] + "',";
                }
                composedLine += "'" + brokenLine[i] + "')";
                numRows = st.executeUpdate(composedLine);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (br != null)
                    br.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
        return numRows;
    }

    public static void readCredentialsFromUser(String user, String password, String database) {
        Scanner scan = new Scanner(System.in);
        System.out.println("Enter the database to be accessed");
        database = scan.nextLine();
        System.out.println("Enter your username");
        user = scan.nextLine();
        System.out.println("Enter your password");
        password = scan.nextLine();
        scan.close();
    }

    /**
     * Main method for the program to control a database
     * @param argv command line arguments
     * @throws SQLException
     */
    public static void main(String[] argv) throws SQLException {
        String user = "";
        String password = "";
        String database = "";

        readCredentialsFromUser(user, password, database);

        Connection connection = connectToDatabase(user, password, database);

        if (connection != null) {
            System.out.println("You made it, take control of your database now!");
        } else {
            System.out.println("Failed to make connection!");
            return;
        }
        // Now we're ready to work on the DB

//        String query = "SELECT * FROM branch";
//        ResultSet rs = executeSelect(connection, query);
//        try {
//            while (rs.next()) {
//                System.out.print("Column 1 returned ");
//                System.out.println(rs.getString(1));
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//        rs.close();
//
//        dropTable(connection, "customer");
//        createTable(
//                connection,
//                "customer(id int primary key, name varchar(15), street varchar(15), city varchar(15));");
//        int rows = insertIntoTableFromFile(connection, "customer",
//                "src/Table.txt");
//        System.out.println(rows + " rows inserted.");
    }
}