import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet; 
import java.sql.SQLException;
import java.sql.Statement;

import java.util.Scanner;

public class UserFrontendSQLInjection {


    public static void main(String[] agrs) {

        String dbname = ""; // Input your UiO-username
        String user = ""; // Input your UiO-username + _priv
        String pwd = ""; // Input the password for the _priv-user you got in a mail
        
        // Connection details
        String connectionStr = 
            "user=" + user + "&" + 
            "port=5432&" +  
            "password=" + pwd + "";

        String host = "jdbc:postgresql://dbpg-ifi-kurs01.uio.no"; 
        String connectionURL = 
            host + "/" + dbname +
            "?sslmode=require&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory&" +
            connectionStr;

        try {
            // Load driver for PostgreSQL
            Class.forName("org.postgresql.Driver");
            // Create a connection to the database
            Connection connection = DriverManager.getConnection(host + "/" + dbname
                    + "?" + connectionStr);

            String username = null;

            while (username == null) {
                System.out.println("-- USER FRONTEND --");
                System.out.println("Please choose an option:\n 1. Register\n 2. Login\n 3. Exit");
                int ch = getIntFromUser("Option: ", false);

                if (ch == 1) {
                    register(connection); // Register new user
                } else if (ch == 2) {
                    username = login(connection); // Login existing user
                } else if (ch == 3) {
                    return; // Exit program
                }
            }
            // Once logged in, allow user to search for products
            search(connection, username);
        } catch (SQLException|ClassNotFoundException ex) {
            System.err.println("Error encountered: " + ex.getMessage());
        }
    }

    private static void register(Connection connection) throws SQLException {

        System.out.println(" -- REGISTER NEW USER --");
        // Get credentials for new user
        String username = getStrFromUser("Username: "); 
        String password = getStrFromUser("Password: "); 
        String name = getStrFromUser("Name: "); 
        String address = getStrFromUser("Address: "); 
        
        Statement statement = connection.createStatement();
        statement.execute("INSERT INTO ws.users(name, username, password, address) VALUES ('" + name + "', '" + username + "', '" + password + "', '" + address + "');");

        System.out.println("New user " + username + " added!");
    }

    private static String login(Connection connection) throws SQLException {
        System.out.println(" -- LOGIN --");
        // Get login details
        String username = getStrFromUser("Username: "); 
        String password = getStrFromUser("Password: "); 

        Statement statement = connection.createStatement();
        ResultSet rows = statement.executeQuery("SELECT username, name FROM ws.users WHERE username = '" + username + "' AND password = '" + password + "';");
        
        if (!rows.next()) {
            // The query returned no results, thus the user-password pair does not exist in the DB
            System.out.println("Incorrect username or password.");
            return null;
        } else {
            // Query returned a result, thus correct username and password
            System.out.println("Welcome " + rows.getString(2));
            return rows.getString(1);
        }
    }

    private static void search(Connection connection, String username) throws SQLException {

        // We start by gathering input from user, defining the search
        System.out.println(" -- SEARCH --");
        String name = getStrFromUser("Search: "); 
        String category = getStrFromUser("Category: "); 

        // We will now construct the search query based on the user's input
        String q = 
            "SELECT p.pid, p.name, p.price, c.name AS category, p.description " + 
            "FROM ws.products AS p INNER JOIN ws.categories AS c USING (cid)" + 
            "WHERE p.name LIKE '%" + name + "%'";

        if (!category.equals("")) {
            q += " AND c.name = '" + category + "'";
        }

        q += ";";

        Statement statement = connection.createStatement();

        ResultSet rows = statement.executeQuery(q);

        if (!rows.next()) { // No row to move to, thus empty result set
            System.out.println("No results.");
            return;
        }

        // The user should be able to pick which product to order based on the product's pid

        System.out.println(" -- RESULTS --\n");
        do {
            // To get values from the current row in the ResultSet
            // we use getString(int) for Strings, getFloat(int) for floats, etc.
            // The argument int denotes which column to get a value from, starting from index 1
            System.out.println("===" + rows.getString(2) + "===\n" + 
                  "Product ID: " + rows.getInt(1) + "\n" + 
                  "Price: " + rows.getFloat(3) + "\n" + 
                  "Category: " + rows.getString(4));

            if (!rows.getString(3).equals("NULL")) {
                System.out.println("Description: " + rows.getString(5));
            }
            System.out.print("\n");

        } while (rows.next());

        // Now that we have made a search, we will allow the user to order products from the
        // search result
        orderProducts(connection, username);
    }

    private static void orderProducts(Connection connection, String username) throws SQLException {
        // Let user pick a product based on its numbering in the search result
        Integer order = getIntFromUser("Order: ", true);
        if (order == null) {
            return;
        }

        // We will let users order several of the same product in a single order
        Integer num = getIntFromUser("How many: ", false);

        String oq = "INSERT INTO ws.orders (uid, pid, num, date, payed) ";
        oq += "SELECT uid, '" + order + "', '" + num + "', current_date, 0 " + 
              "FROM ws.users " + 
              "WHERE username = '" + username + "';";
        Statement statement = connection.createStatement();
        statement.execute(oq); 
        System.out.println("Product(s) ordered.");
    }

    /**
     * Utility method that gets an int as input from user
     * Prints the argument message before getting input
     * If second argument is true, the user does not need to give input and can leave
     * the field blank (resulting in a null)
     */
    private static Integer getIntFromUser(String message, boolean canBeBlank) {
        while (true) {
            String str = getStrFromUser(message);
            if (str.equals("") && canBeBlank) {
                return null;
            }
            try {
                return Integer.valueOf(str);
            } catch (NumberFormatException ex) {
                System.out.println("Please provide an integer or leave blank.");
            }
        }
    }

    /**
     * Utility method that gets a String as input from user
     * Prints the argument message before getting input
     */
    private static String getStrFromUser(String message) {
        Scanner s = new Scanner(System.in);
        System.out.print(message);
        return s.nextLine();
    }
}