/**
 * Created by Edward on 13/12/2016.
 */
package connectionMySql;

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

public class ConnectionMySql {
    static {
        try { /* Type 4 Driver */
            Class.forName("com.mysql.jdbc.Driver"); //TODO? lib
        } catch (ClassNotFoundException e) {
            System.err.println("Could not load MySql driver.");
            System.err.println(e.getMessage());
            System.exit(1);
        }
    }

    public static void main(String args[]) {
        String uname = null; //TODO vul uw username van uw databank in
        String psswrd = null; //TODO vul uw password van uw user van uw databank in
        Integer choice = 1; /* Location of the database */
        String host = "jdbc:mysql://localhost/orderdb"; //TODO
        String query = "SELECT * FROM restaurant";
        /* Reading log-in data (username and password) */
        try {
            BufferedReader br1 = new BufferedReader(new InputStreamReader(System.in));
            System.out.print("Enter your username on MySql: ");
            uname = br1.readLine();
            BufferedReader br2 = new BufferedReader(new InputStreamReader(System.in));
            System.out.print("Enter your password on MySql: ");
            psswrd = br2.readLine();
        } catch (IOException e) {
            System.out.print("Failed to get uname/passwd");
            System.out.println(":" + e.getMessage());
            System.exit(1);
        }
        /* Connect to MySql database */
        try {
            Connection conn = DriverManager.getConnection(host, uname, psswrd);
            System.out.println("Connection established...");
            System.out.println(); /* Create statement */
            Statement stmt = conn.createStatement(); /* Execute the query */
            ResultSet rs = stmt.executeQuery(query); /* Output */
            System.out.println("This is an example how you query a DBMS.");
            System.out.println();
            System.out.println(query);
            System.out.println("ID // First Name // Last Name");
            System.out.println("------------------------------");
            while (rs.next()) {
                System.out.print(rs.getString(1));
                System.out.print((" // "));
                System.out.print(rs.getString(2));
                System.out.print((" // "));
                System.out.print(rs.getString(3));
                System.out.print((" // "));
                System.out.println(rs.getString(4)); //dees ga crashe, from restaurent heeft 3 kolomme geen 4 zoals in customer.. losers
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException e) {
            System.out.println("SQL Exception: ");
            System.err.println(e.getMessage());
        }
        /* Example of choice options */
        while (choice != 0) {
            Scanner s = new Scanner(System.in); // ni zo goe als BufferedReader(=synchronized en meer performant?), maar ez code
            /*Ask for user's choice*/
            try {
                BufferedReader br1 = new BufferedReader(new InputStreamReader(System.in));
                System.out.println();
                System.out.println("This is an example of a choice menu.");
                System.out.println();
                System.out.println("Please choose between the following options:");
                System.out.println("     (1) Add registration");
                System.out.println("     (2) Show popular sessions list");
                System.out.println("     (0) Quit");
                System.out.print("Enter your choice: ");
                choice = Integer.parseInt(br1.readLine());
            } catch (NumberFormatException ex) {
                System.err.println("Not a valid number");
            } catch (IOException e) {
                e.printStackTrace();
            }
            if (choice == 1) {
                System.out.println("What is your customer id? : ");
                int customerId = s.nextInt();

                //uw query om alles "*" uit uw productentabel te halen
                //ASC is default bij ORDER BY ma snapte wardje doe graaf om da aan te tone
                String _query = "SELECT * FROM PRODUCT ORDER BY Category ASC, Name ASC;";
                System.out.println();
                System.out.println("Product list:");
                System.out.println("ID - Product Name - Category"); //mooi geformatteerde vorm, ni amateuristisch, tzzzz wollah
                System.out.println("****************************************** ");
                System.out.println();
                try {//toont heel de lijst van producten
                    Connection conn = DriverManager.getConnection(host, uname, psswrd);
                    Statement stmt = conn.createStatement(); /* Execute the query */
                    ResultSet rs = stmt.executeQuery(_query); /* Output */
                    while (rs.next()) {
                        String id = rs.getString(1);
                        String prodName = rs.getString(2);
                        String cat = rs.getString(3);
                        System.out.println();// mooi geformateerd
                    }
                    rs.close();
                    stmt.close();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

                System.out.println();
                System.out.println("Which product do you want to order? : ");
                int productId = s.nextInt();
                _query = "SELECT * FROM OFFERS WHERE Product Id=" + productId + ";";
                try {
                    Connection conn = DriverManager.getConnection(host, uname, psswrd);
                    Statement stmt = conn.createStatement(); /* Execute the query */
                    ResultSet rs = stmt.executeQuery(_query); /* Output */
                    int highestRating = 0;
                    int restIdWithHighestRating = -1; //-1 om aan te tonen dat er een error is als uiteindelijk de id op -1 blijft (moet ni per se)
                    String restoNameWithHighestRating = null;
                    while (rs.next()) {
                        int rating = rs.getInt(3);
                        if (rating > highestRating) {
                            highestRating = rating;
                            restIdWithHighestRating = rs.getInt(1);
                        }
                    }
                    _query = "SELECT * FROM RESTAURANT WHERE ID=" + restIdWithHighestRating + ";";
                    rs = stmt.executeQuery(_query);
                    restoNameWithHighestRating = rs.getString(2);

                    _query = "SELECT * FROM PRODUCT WHERE Product_ID=" + productId + ";";
                    rs = stmt.executeQuery(_query);
                    String productName = rs.getString(2);

                    System.out.println();
                    System.out.printf("Product name - Restaurant name - Rating");
                    System.out.println("********************************************");
                    System.out.println(productName + " - " + restoNameWithHighestRating + " - " + highestRating);
                    //System.out.printf("%-16s - %-16s %-2d",productName,restoNameWithHighestRating,highestRating);
                    rs.close();
                    stmt.close();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } else if (choice == 2) {
                /* TODO */
            } else {
                /* TODO */
            }
        }
        System.out.println();
        System.out.println("End of Session");
    }
}