import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Scanner;
import java.io.Console;

public class Chess {

    private static String user = "";
    private static String pass = "";
    private static String host = "";
    private static String database = "";
    private static StringBuilder sb = new StringBuilder();
    private static Scanner userInput = new Scanner(System.in);
    private static String tblName = "";
    private static Connection conn;

    public static void main(String[] args)
            throws SQLException {
        // the following statement loads the MySQL jdbc driver
        // make sure it is in the CLASSPATH

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("Could not load the driver");
        }

        try {
            // connect db

            connectDatabaseMySql(user, pass, host, database);
            Statement s = conn.createStatement();

            createPlayerTable(s);
            createClubTable(s);
            createGameTable(s);
            createMatchTable(s);

            createMenu();

        } catch (Exception e) {
            System.out.println("Error: " + e.toString());
        }
        conn.commit();
        conn.close();
    }

    public static void connectDatabaseMySql(String user, String pass, String host, String database) throws SQLException {

        try {

            Scanner sc = new Scanner(System.in);
            System.out.println("Type userid");
            user = sc.next();
            System.out.println("Type password");
            pass = sc.next();
            System.out.println("Type hostname");
            host = sc.next();
            System.out.println("Type Database Name");
            database = sc.next();

            System.out.println("Username:" + user + ", Password: " + pass + ", Hostname: " + host + ", Database: " + database);

        } catch (Exception e) {
            System.out.println("Error: " + e.toString());
        }
        conn = DriverManager.getConnection("jdbc:mysql://" + host + ":3306/chess", user, pass);
        conn.setAutoCommit(false);
    }

    public static void createDatabase(Statement s) throws SQLException {

        String sql = ("CREATED DATABASE CHESS SUCCESSFULLY");
        System.out.println("Database created successfully");

        s.executeUpdate(sql);

    }

    public static void createPlayerTable(Statement s) throws Exception {
        String line = "";
        String tokens[];
        String data_file = "src//Player.txt";

        s.execute("drop table if exists player");

        String sql = "CREATE TABLE IF NOT EXISTS Player (PlayerName VARCHAR (20) PRIMARY KEY, DateOfBirth VARCHAR(20), FIDERating INT not null, FIDETitle VARCHAR(30), ClubName VARCHAR(20))";
        s.execute(sql);

        String insertPlayer = "insert into player values (?,?,?,?,?)";
        PreparedStatement p = conn.prepareStatement(insertPlayer);

        System.out.println("Created Player Table");

        try {

            File inputFile = new File(data_file);
            FileReader inf = new FileReader(inputFile);
            BufferedReader inb = new BufferedReader(inf);
            System.out.println("Ready to read line");
            line = inb.readLine(); // read a line

            while ((line != null)) {
                tokens = line.split(","); // split line into ‘,’
                // separated tokens
                System.out.println(tokens[0] + " " + tokens[1]
                        + " " + tokens[2] + " " + tokens[3] + " "
                        + tokens[4] + " ");

                //Should trim leading/trailing spaces from tokens.
                p.setString(1, tokens[0]);
                p.setString(2, tokens[1]);
                p.setInt(3, Integer.parseInt(tokens[2]));
                p.setString(4, tokens[3]);
                p.setString(5, tokens[4]);

                p.execute();
                p.clearParameters();

                line = inb.readLine(); //read next line

            }
            inb.close();
            inf.close();

        } catch (IOException e) {
            System.out.println("Error: " + e.toString());
        }

    }

    public static void createClubTable(Statement s) throws Exception {
        String line = "";
        String tokens[];
        String data_file = "src//Club.txt";

        s.execute("drop table if exists club");

        String sql = "CREATE TABLE IF NOT EXISTS Club (ClubName VARCHAR(20) PRIMARY KEY, Address VARCHAR(40), DateFormed VARCHAR(10))";
        s.executeUpdate(sql);

        String insertClub = "insert into club values (?,?,?)";
        PreparedStatement p = conn.prepareStatement(insertClub);

        System.out.println("Created Club Table");

        try {

            File inputFile = new File(data_file);
            FileReader inf = new FileReader(inputFile);
            BufferedReader inb = new BufferedReader(inf);
            System.out.println("Ready to read line");
            line = inb.readLine(); // read a line

            while ((line != null)) {
                tokens = line.split(","); // split line into ‘,’
                // separated tokens
                System.out.println(tokens[0] + " " + tokens[1]
                        + " " + tokens[2] + " ");

                //Should trim leading/trailing spaces from tokens.
                p.setString(1, tokens[0]);
                p.setString(2, tokens[1]);
                p.setString(3, tokens[2]);
                p.execute();
                p.clearParameters();
                line = inb.readLine(); //read next line

            }
            inb.close();
            inf.close();

        } catch (IOException e) {
            System.out.println("Error:" + e.toString());
        }

    }

    public static void createGameTable(Statement s) throws Exception {
        String line = "";
        String tokens[];
        String data_file = "src//Game.txt";

        s.execute("drop table if exists game");

        String sql = "CREATE TABLE IF NOT EXISTS Game (GameID VARCHAR(20) PRIMARY KEY, DatePlayed VARCHAR(10), BoardNum TINYINT, Score VARCHAR(10), MatchID VARCHAR (10), WhitePlayer VARCHAR(20), BlackPlayer VARCHAR(20))";
        s.executeUpdate(sql);

        String insertGame = "INSERT INTO game VALUES(?,?,?,?,?,?,?)";
        PreparedStatement p = conn.prepareStatement(insertGame);

        System.out.println("Created Game Table");

        try {

            File inputFile = new File(data_file);
            FileReader inf = new FileReader(inputFile);
            BufferedReader inb = new BufferedReader(inf);
            System.out.println("Ready to read line");
            line = inb.readLine(); // read a line

            while ((line != null)) {
                tokens = line.split(","); // split line into ‘,’
                // separated tokens
                System.out.println(tokens[0] + " " + tokens[1]
                        + " " + tokens[2] + " " + tokens[3] + " "
                        + tokens[4] + " "
                        + tokens[5] + " "
                        + tokens[6] + " ");

                //Should trim leading/trailing spaces from tokens.
                p.setString(1, tokens[0]);
                p.setString(2, tokens[1]);
                p.setInt(3, Integer.parseInt(tokens[2]));
                p.setString(4, tokens[3]);
                p.setString(5, tokens[4]);
                p.setString(6, tokens[5]);
                p.setString(7, tokens[6]);

                p.execute();
                p.clearParameters();
                line = inb.readLine(); //read next line

            }
            inb.close();
            inf.close();

        } catch (IOException e) {
            System.out.println("Error: " + e.toString());
        }

    }

    public static void createMatchTable(Statement s) throws Exception {
        String line = "";
        String tokens[];
        String data_file = "src//TblMatch.txt";

        s.execute("drop table if exists TblMatch");
        String sql = "CREATE TABLE IF NOT EXISTS TblMatch (MatchID VARCHAR(4) PRIMARY KEY, MatchDate VARCHAR(10), Venue VARCHAR (20), Score VARCHAR(10), WinningClub VARCHAR(20), LosingClub VARCHAR (20))";
        s.execute(sql);

        String insertMatch = "insert into TblMatch values (?,?,?,?,?,?)";
        PreparedStatement p = conn.prepareStatement(insertMatch);

        System.out.println("Created TblMatch Table");

        try {

            File inputFile = new File(data_file);
            FileReader inf = new FileReader(inputFile);
            BufferedReader inb = new BufferedReader(inf);
            System.out.println("Ready to read line");
            line = inb.readLine(); // read a line

            while ((line != null)) {
                tokens = line.split(","); // split line into ‘,’
                // separated tokens
                System.out.println(tokens[0] + " " + tokens[1]
                        + " " + tokens[2] + " " + tokens[3] + " "
                        + tokens[4] + " "
                        + tokens[5] + " ");

                //Should trim leading/trailing spaces from tokens.
                p.setString(1, tokens[0]);
                p.setString(2, tokens[1]);
                p.setString(3, tokens[2]);
                p.setString(4, tokens[3]);
                p.setString(5, tokens[4]);
                p.setString(6, tokens[5]);

                p.execute();
                p.clearParameters();
                line = inb.readLine(); //read next line

            }
            inb.close();
            inf.close();

        } catch (IOException e) {
            System.out.println("Error: " + e.toString());
        }

    }

    public static void selectTableStatement(Connection conn, String tbl) throws Exception {

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_Table_qry");
        s.execute("create procedure Select_Table_qry() BEGIN SELECT * FROM " + tbl + "; END");
        CallableStatement cs = conn.prepareCall("{CALL Select_Table_qry()}");
        ResultSet result = cs.executeQuery();

        System.out.println("Results: ");

        if(tbl.equals("player")){
        System.out.println("Player Table shows");
        while (result.next()) {
            System.out.println(result.getString(1) + " " + result.getString(2)
                    + " " + result.getInt(3) + " " + result.getString(4) + " "
                    + result.getString(5));
        }
        }else if(tbl.equals("game")){
        System.out.println("Game Table shows");
        while (result.next()) {
            System.out.println(result.getString(1) + " " + result.getString(2)
                    + " " + result.getInt(3) + " " + result.getString(4) + " "
                    + result.getString(5) + " " + result.getString(6));
        }
        } else if(tbl.equals("club")){
        System.out.println("Club Table shows");
        while (result.next()) {
            System.out.println(result.getString(1) + " " + result.getString(2)
                    + " " + result.getString(3));
        }
        } else if(tbl.equals("tblmatch")){
        System.out.println("Match Table shows");
        while (result.next()) {
            System.out.println(result.getString(1) + " " + result.getString(2)
                    + " " + result.getString(3) + " " + result.getString(4) + " "
                    + result.getString(5) + " " + result.getString(6));
        }
        }


    }

    public static void selectPlayerNameAndClub(Connection conn, String playName) throws Exception {
        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_Club_qry");
        s.execute("create procedure Select_Club_qry() BEGIN SELECT player.PlayerName, club.ClubName, club.Address, club.DateFormed\n" +
                    "FROM player\n" +
                    "LEFT JOIN club\n" +
                    "ON player.ClubName=club.ClubName\n"+
                    "WHERE PlayerName="+ "'" +playName+"'" +"; END");
        CallableStatement cs = conn.prepareCall("{CALL Select_Club_qry()}");
        ResultSet result = cs.executeQuery();

        System.out.println("Results: ");

         while (result.next()) {
        System.out.println(result.getString(1) + " " + result.getString(2)
                    + " " + result.getString(3) + " " + result.getString(4));
         }

    }

    public static void showFideOver1199(Connection conn)throws Exception{

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_FIDE_qry");
        s.execute("create procedure Select_FIDE_qry() BEGIN SELECT Count(fiderating)\n" +
                    "FROM player\n" +
                    "Where fiderating > 1199"+"; END");
        CallableStatement cs = conn.prepareCall("{CALL Select_FIDE_qry()}");
        ResultSet result = cs.executeQuery();

        System.out.println("How many players have a FIDErating greater than 1199");
        System.out.println("Results:\n ");


        while (result.next()) {
        System.out.println(result.getString(1));
        }


    }


    public static void showGroupByScore(Connection conn)throws Exception{

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_MATCHID_qry");
        s.execute("create procedure Select_MATCHID_qry() BEGIN SELECT Count(MatchID), score\n" +
                    "FROM game\n" +
                    "GROUP BY score"+"; END");
        CallableStatement cs = conn.prepareCall("{CALL Select_MATCHID_qry()}");
        ResultSet result = cs.executeQuery();

        while (result.next()) {
        System.out.println("Number of Teams: " + result.getString(1) + " " + "The Score: " + result.getString(2));
         }

    }

    public static void showGroupByFideratingUnder(Connection conn)throws Exception{

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_Under_qry");
        s.execute("create procedure Select_Under_qry() BEGIN SELECT Count(playername), fiderating\n" +
                    "FROM player\n" +
                    "GROUP BY fiderating\n" +
                    "HAVING fiderating < 1250" + "; END");
        CallableStatement cs = conn.prepareCall("{CALL Select_Under_qry()}");
        ResultSet result = cs.executeQuery();

        while (result.next()) {
        System.out.println("Number of Players: " + result.getString(1) + " " + "The Fiderating: " + result.getString(2));
         }

    }

    public static void showGroupAboveAverage(Connection conn)throws Exception{

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_Average_qry");
        s.execute("create procedure Select_Average_qry() BEGIN SELECT playername, fiderating\n" +
                    "FROM player\n" +
                    "WHERE fiderating >(\n" +
                    "SELECT AVG(fiderating)\n" +
                    "FROM player\n" +
                    "WHERE playername = playername)" + "; END");
        CallableStatement cs = conn.prepareCall("{CALL Select_Average_qry()}");
        ResultSet result = cs.executeQuery();

        while (result.next()) {
        System.out.println("Player: " + result.getString(1) + " " + "The Fiderating: " + result.getString(2));
         }

    }

    public static void createMenu() {

        int choice = 0;
        try {

            do {

                System.out.println("\nChess System"
                    + "\n1 - Stored Procedure 1 - Select a table to view"
                    + "\n2 - Stored Procedure 2 - SELECT Details of a players club"
                    + "\n3 - Stored Procedure 3 - Show fide of over 1199"
                    + "\n4 - Stored Procedure 4 - Show the number of clubs grouped by score"
                    + "\n5 - Stored Procedure 5 - Show the number of players having a fierating of under 1250"
                    + "\n6 - Stored Procedure 6 - Showing players with a Fiderating higher than the anverage"
                    + "\n7 - Stored Procedure 7 - correleted subquery"
                    + "\n8 - Exit Program\n");

                System.out.println("Select which procedure you want to use");


                System.out.println();

                choice = userInput.nextInt();

                switch (choice) {
                    case 1: {
                        String tbl = null;
                        System.out.println("Which table do you wish to SELECT");
                        String submenu1 = "1- Players \n 2- Club \n 3- Game\n 4- Match";
                        System.out.println(submenu1);
                        int tblChoice = userInput.nextInt();
                        switch (tblChoice) {
                            case 1:
                                tbl = "player";
                                break;
                            case 2:
                                tbl = "club";
                                break;
                            case 3:
                                tbl = "game";
                                break;
                            case 4:
                                tbl= "tblmatch";
                                break;
                        }
                        selectTableStatement(conn, tbl);
                    }

                    break;

                    case 2: {
                        String playName = null;
                        System.out.println("Which player do you wish to SELECT");
                        String submenu1 = "1- Ben \n 2- Beth \n 3- Darius\n 4- George\n 5- Peter";
                        System.out.println(submenu1);
                        int playerChoice = userInput.nextInt();
                        switch (playerChoice) {
                            case 1:
                                playName = "Ben";
                                break;
                            case 2:
                                playName = "Beth";
                                break;
                            case 3:
                                playName = "Darius";
                                break;
                            case 4:
                                playName= "George";
                                break;
                            case 5:
                                playName= "Peter";
                                break;
                        }

                        selectPlayerNameAndClub(conn, playName);

                    }
                    break;

                    case 3: {

                        showFideOver1199(conn);

                    }
                    break;

                    case 4: {

                        showGroupByScore(conn);

                    }
                    break;

                    case 5: {

                        showGroupByFideratingUnder(conn);

                    }
                    break;

                    case 6: {

                        showGroupAboveAverage(conn);

                    }
                    break;

                    case 7: {


                    }
                    break;

                    case 8: {
                        System.out.println("Exiting Program");
                        System.exit(0);
                    }

                }

            } while (choice != 8);

        } catch (Exception e) {
            System.out.println("Error: " + e.toString());
        }
    }
}