// Below are the imports required for the program

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Scanner;

public class Chess { // Main class

    // List of all the variables used in the program
    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

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

        try {
            // connect to the database

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

            // Below are the Methods that are used to create the sql tables
            createClubTable(s);
            createPlayerTable(s);
            createMatchTable(s);
            createGameTable(s);
            clubTrigger();
            createMenu();

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

    // Method used to connect to the database
    public static void connectDatabaseMySql(String user, String pass, String host, String database) throws SQLException {

        try {

            // Below appears in the console for the user to type in userid, password, hostname and database name
            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();

            //This will be displayed in the console
            System.out.println("Username:" + user + ", Password: " + pass + ", Hostname: " + host + ", Database: " + database);

            //Will throw exception if error is caught
        } catch (Exception e) {
            System.out.println("Error: " + e.toString());
        }
        //This is connecting netbeans to phpmyadmin
        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);

    }

    //Below is the code and sql statements used to create the club table
    public static void createClubTable(Statement s) throws Exception {
        String line = "";
        String tokens[];//Creating the string tokens
        //Accessing the text file
        String data_file = "src//Club.txt";

        // If the tables below already exist then they are dropped and created again
        s.execute("drop table if exists player");
        s.execute("drop table if exists club");

        //This is the sql used to create the Club table
        String sql = "CREATE TABLE IF NOT EXISTS Club (ClubName VARCHAR(20) NOT NULL, Address VARCHAR(40), DateFormed VARCHAR(10), PRIMARY KEY(ClubName))";
        s.executeUpdate(sql);

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

        System.out.println("\nCreated Club Table");

        try {

            File inputFile = new File(data_file);
            FileReader inf = new FileReader(inputFile);//Making the file reader
            BufferedReader inb = new BufferedReader(inf);//Making the buffer reader
            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());//The exception that will appear if an error is caught
        }
        conn.commit();

    }

    //Below is the code and sql statements used to create the player table
    public static void createPlayerTable(Statement s) throws Exception {
        String line = "";
        String tokens[];
        // Below is used to read in the text file
        String data_file = "src//Player.txt";

        // This is the sql statement  used to create  the player table
        String sql = "CREATE TABLE IF NOT EXISTS Player (PlayerName VARCHAR (20) NOT NULL, DateOfBirth VARCHAR(20), FIDERating INT not null, FIDETitle VARCHAR(30), ClubName VARCHAR(20), PRIMARY KEY(PlayerName), FOREIGN KEY(Clubname) REFERENCES Club(ClubName))";
        s.execute(sql);
        //Below is used to show how many variables need to be inserted in the player table
        String insertPlayer = "insert into player values (?,?,?,?,?)";
        PreparedStatement p = conn.prepareStatement(insertPlayer);

        System.out.println("\nCreated Player Table");

        try {

            File inputFile = new File(data_file);
            FileReader inf = new FileReader(inputFile);//Making the file reader
            BufferedReader inb = new BufferedReader(inf);//Making the buffer reader
            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());//This is the exception that will appear if an error is caught
        }

        conn.commit();
    }

    //Below is the code and sql statements used to create the Match table
    public static void createMatchTable(Statement s) throws Exception {
        String line = "";
        String tokens[];
        // Below is used to read in the text file
        String data_file = "src//TblMatch.txt";

        // If the tables below already exist then they are dropped and created again
        s.execute("drop table if exists Game");
        s.execute("drop table if exists TblMatch");
        //Below is the sql statement used to create tblMatch table
        String sql = "CREATE TABLE IF NOT EXISTS TblMatch (MatchID VARCHAR(4) NOT NULL, MatchDate VARCHAR(10), Venue VARCHAR (20), Score VARCHAR(20), WinningClub VARCHAR(20), LosingClub VARCHAR(20),PRIMARY KEY(MatchID))";
        s.execute(sql);

        //Below is used to show how many variables need to be inserted in the player table
        String insertMatch = "insert into TblMatch values (?,?,?,?,?,?)";
        PreparedStatement p = conn.prepareStatement(insertMatch);

        System.out.println("\nCreated TblMatch Table");

        try {

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

            //while line is not null print the following
            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());//This is the exception that will appear if an error is caught
        }

        conn.commit();

    }

    public static void createGameTable(Statement s) throws Exception {
        String line = "";
        String tokens[];
        // Below is used to read in the text file
        String data_file = "src//Game.txt";

        //Below is the sql statement used to create the game table in the database
        String sql = "CREATE TABLE IF NOT EXISTS Game (GameID VARCHAR(20) NOT NULL, DatePlayed VARCHAR(10), BoardNum TINYINT, Score VARCHAR(10), MatchID VARCHAR(10), WhitePlayer VARCHAR(20), BlackPlayer VARCHAR(20), PRIMARY KEY(GameID), FOREIGN KEY(MatchID) REFERENCES TblMatch(MatchID))";
        s.execute(sql);

        //Below is used to show how many variables need to be inserted in the player table
        String insertGame = "INSERT INTO game VALUES(?,?,?,?,?,?,?)";
        PreparedStatement p = conn.prepareStatement(insertGame);

        System.out.println("\nCreated Game Table");

        try {

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

            //while line is not null print the following
            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());//This is the exception that will appear if an error is caught
        }

        conn.commit();

    }
    //The trigger below stops the user from deleting a club due to data integrity
    public static void clubTrigger() throws SQLException {
        StringBuilder tb = new StringBuilder();

        try{
            System.out.println("\nTrigger Created");
            tb.append(" CREATE trigger ClubDeletion before delete on club ");
            tb.append(" for each ROW Begin ");
            tb.append(" declare msg varchar(140); ");
            tb.append(" set msg = concat('TriggerError: Club can not be deleted due to the integrity restraint'); ");
            tb.append(" signal sqlstate '45000' ");
            tb.append(" set message_text = msg; ");
            tb.append(" END; ");//This is the sql that was used to create the trigger

            conn.createStatement().execute(tb.toString());



        }
        catch (SQLException e)
            {

            System.out.println("Error: " + e.toString());//This is the exception that will appear if an error is caught
        }

        conn.commit();
    }

    //Below is my first stored procedure, which just shows table player details
    public static void selectTableStatement(Connection conn, String tbl) throws Exception {

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_Table_qry");//Drops this procdure if it extists
        s.execute("create procedure Select_Table_qry(IN player VARCHAR(20)) BEGIN SELECT * FROM  player " + "; END");//The sql statement used to show player table details, also contains IN parameters
        CallableStatement cs = conn.prepareCall("{CALL Select_Table_qry(?)}");
        cs.setString(1, tbl);//What I am trying to output
        ResultSet result = cs.executeQuery();

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

        System.out.println("Display the details of player table:");

        //Below is how the results will be shown in the console
        System.out.println("\nPlayer Table shows");
        //While there is a result that applies rows will continue to be added to the results
        while (result.next()) {
            System.out.println(result.getString(1) + " " + result.getString(2)
                    + " " + result.getInt(3) + " " + result.getString(4) + " "
                    + result.getString(5));
        }

    }
    //Below is my second stored procedure, this show a players name and details of the club that they belong to

    public static void selectPlayerNameAndClub(Connection conn, String playName) throws Exception {
        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_Club_qry");//Drops this procdure if it extists
        s.execute("create procedure Select_Club_qry(IN player VARCHAR(20)) BEGIN SELECT player.PlayerName, club.ClubName, club.Address, club.DateFormed\n"
                +//Select statement
                "FROM player\n"
                +//From statement
                "LEFT JOIN club\n"
                +//Left Join
                "ON player.ClubName=club.ClubName\n"
                + "WHERE PlayerName= player " + "; END");//The sql statement used to show player and club details, also contains IN parameters
        CallableStatement cs = conn.prepareCall("{CALL Select_Club_qry(?)}");
        cs.setString(1, playName);//What I am trying to output
        ResultSet result = cs.executeQuery();

        System.out.println("Results: ");
        System.out.println("\nShow the name of the player and details of the club that they belong to:\n");
        //Below is how the results will be shown in the console
        while (result.next()) {//While there is a result that applies rows will continue to be added to the results
            System.out.println(result.getString(1) + " " + result.getString(2)
                    + " " + result.getString(3) + " " + result.getString(4));
        }

    }

    //Below is my third stored procedure, this shows the number of players with a FIDERating over 1199
    public static void showFideOver1199(Connection conn, String rating) throws Exception {

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_FIDE_qry");//Drops this procdure if it extists
        s.execute("create procedure Select_FIDE_qry(IN player VARCHAR(20))BEGIN SELECT Count(fiderating) \n"
                + "FROM player\n"
                + //From Statement (what table it has to get information from)
                "Where fiderating > 1199" + "; END");//The sql statement used to show the number of players with FIDERating greater than 1199, also contains IN parameters
        CallableStatement cs = conn.prepareCall("{CALL Select_FIDE_qry(?)}");
        cs.setString(1, rating);//What I am trying to output
        ResultSet result = cs.executeQuery();
        //Below is how the results will be shown in the console
        System.out.println("How many players have a FIDErating greater than 1199:\n");
        System.out.println("Results:\n ");

        while (result.next()) {//While there is a result that applies rows will continue to be added to the results
            System.out.println(result.getString(1));
        }

    }

    //Below is my fourth procedure, this shows the different scores and the number of games that had those scores
    public static void showGroupByScore(Connection conn, String matches) throws Exception {

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_MATCHID_qry");//Drops this procdure if it extists
        s.execute("create procedure Select_MATCHID_qry(IN tblMatch VARCHAR(20)) BEGIN SELECT Count(MatchID), score\n"
                + "FROM game\n"
                + "GROUP BY score" + "; END");//This is the sql statement that is used to show the scores and the number of games that had that score
        CallableStatement cs = conn.prepareCall("{CALL Select_MATCHID_qry(?)}");
        cs.setString(1, matches);//What I am trying to output
        ResultSet result = cs.executeQuery();
        System.out.println("Results:\n ");
        System.out.println("\nShow the different scores and the number of games that had that score:\n");

        while (result.next()) {//While there is a result that applies rows will continue to be added to the results

            System.out.println("Number of games: " + result.getString(1) + " " + "The Score: " + result.getString(2));
        }

    }

    //This is my fifth procedure, this is used to find the players that have a FIDERating less than 1250
    public static void showGroupByFideratingUnder(Connection conn, String FIDE) throws Exception {

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_Under_qry");//Drops this procdure if it extists
        s.execute("create procedure Select_Under_qry(IN player VARCHAR(20)) BEGIN SELECT Count(playername), fiderating\n"
                + "FROM player\n"
                + "GROUP BY fiderating\n"
                + "HAVING fiderating < 1250" + "; END");//This is the sql statement that is used to find the players with a FIDERating less than 1250
        CallableStatement cs = conn.prepareCall("{CALL Select_Under_qry(?)}");
        cs.setString(1, FIDE);//What I am trying to output
        ResultSet result = cs.executeQuery();
        System.out.println("Results: ");
        System.out.println("\nDisplay the number of players that have a FIDERating less than 1250 and their score:\n");

        while (result.next()) {//While there is a result that applies rows will continue to be added to the results

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

    }

    //This is my sixth procedure, this is used to find the scores of games that were not played on board 1
    public static void selectAllBoardsAcceptBoardNum1(Connection conn, String bNum) throws Exception {

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_BoardNum_qry");//Drops this procdure if it extists
        s.execute("create procedure Select_BoardNum_qry(IN game VARCHAR(20)) BEGIN SELECT gameid, score\n"
                + "FROM game\n"
                + "WHERE boardnum >(\n"
                + "SELECT MIN(boardnum)\n"
                + "FROM game , tblmatch\n"
                + "WHERE game.matchID = tblmatch.matchID)\n" + "; END");//This is the sql statement that is used to find score of games not played on board 1
        CallableStatement cs = conn.prepareCall("{CALL Select_BoardNum_qry(?)}");
        cs.setString(1, bNum);//What I am trying to output
        ResultSet result = cs.executeQuery();
        System.out.println("Results: ");
        System.out.println("\nShow the Score of games that werent played on board 1:\n");

        while (result.next()) {//While there is a result that applies rows will continue to be added to the results

            System.out.println("Game: " + result.getString(1) + " " + "Game Score: " + result.getString(2));
        }

    }

    //Below is my seventh procedure, this is used to find the players that have a FIDERating greatert than the average and their FIDERatings
    public static void showGroupAboveAverage(Connection conn, String playName) throws Exception {

        Statement s = conn.createStatement();
        s.execute("drop procedure if exists Select_Average_qry");//Drops this procdure if it extists
        s.execute("create procedure Select_Average_qry(IN player VARCHAR(20)) BEGIN SELECT playername, fiderating\n"
                + "FROM player\n"
                + "WHERE fiderating >(\n"
                + "SELECT AVG(fiderating)\n"
                + "FROM player)\n" + "; END");//This is the sql statement that is used to find the players that have a FIDERating greater than the average FIDERating
        CallableStatement cs = conn.prepareCall("{CALL Select_Average_qry(?)}");
        cs.setString(1, playName);//What I am trying to output
        ResultSet result = cs.executeQuery();
        System.out.println("Results: ");
        System.out.println("\nShow the players that have a FIDERating greater than the average FIDERating:\n");

        while (result.next()) {//While there is a result that applies rows will continue to be added to the results

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

    }

    //This is the menu that I have created to display the stored procedures that can be selected
    public static void createMenu() {

        int choice = 0;
        try {

            do {

                System.out.println("\n-----------------------Chess System---------------------"
                        + "\n1 - Stored Procedure 1 - Display player table"
                        + "\n2 - Stored Procedure 2 - Show the Details of a player and the players club"
                        + "\n3 - Stored Procedure 3 - Show the number of players with FIDErating over 1199"
                        + "\n4 - Stored Procedure 4 - Show the different scores and the number of games that had those scores"
                        + "\n5 - Stored Procedure 5 - Show the number of players and their FIDERating having a FIDErating under 1250"
                        + "\n6 - Stored Procedure 6 - Showing players with a Fiderating higher than the anverage FIDERating"
                        + "\n7 - Stored Procedure 7 - Show the gameID and Score on all boards accept Boards numbered 1"
                        + "\n8 - Exit Program"
                        + "\n-----------------------Chess System---------------------");

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

                System.out.println();

                //The user is able to select what procedure they want to see
                choice = userInput.nextInt();

                //This is the first procedure
                switch (choice) {
                    case 1: {
                        String tbl = null;

                        selectTableStatement(conn, tbl);//The name of the method for procedure 1
                    }

                    break;

                    //This is the Second procedure
                    case 2: {
                        String playName = null;
                        System.out.println("Which player do you wish to SELECT");
                        String submenu1 = "1- George\n 2- Dave\n 3- Pete\n 4- Sarah\n 5- Beth\n 6- Jordan\n 7- Andrew\n 8- Neil\n 9- Tom\n 10- Tim\n 11- John\n 12- Joel\n 13- Kerri\n 14- Joanne\n 15- Cora\n 16- Ben";
                        System.out.println(submenu1);
                        int playerChoice = userInput.nextInt();
                        //The user selects a number to display the details of one of the players club
                        switch (playerChoice) {
                            case 1:
                                playName = "George";
                                break;
                            case 2:
                                playName = "Dave";
                                break;
                            case 3:
                                playName = "Pete";
                                break;
                            case 4:
                                playName = "Sarah";
                                break;
                            case 5:
                                playName = "Beth";
                                break;
                            case 6:
                                playName = "Jordan";
                                break;
                            case 7:
                                playName = "Andrew";
                                break;
                            case 8:
                                playName = "Neil";
                                break;
                            case 9:
                                playName = "Tom";
                                break;
                            case 10:
                                playName = "Tim";
                                break;
                            case 11:
                                playName = "John";
                                break;
                            case 12:
                                playName = "Joel";
                                break;
                            case 13:
                                playName = "Kerri";
                                break;
                            case 14:
                                playName = "Joanne";
                                break;
                            case 15:
                                playName = "Cora";
                                break;
                            case 16:
                                playName = "Ben";
                                break;
                        }

                        selectPlayerNameAndClub(conn, playName);//The name of the method for procedure 2

                    }
                    break;

                    //This is the third procedure
                    case 3: {

                        String rating = null;

                        showFideOver1199(conn, rating);//The name of the method for procedure 3

                    }
                    break;

                    //This is the fourth procedure
                    case 4: {

                        String matches = null;

                        showGroupByScore(conn, matches);//The name of the method for procedure 4

                    }
                    break;

                    //This is the fifth procedure
                    case 5: {

                        String FIDE = null;

                        showGroupByFideratingUnder(conn, FIDE);//The name of the method for procedure 5

                    }
                    break;

                    //This is the sixth procedure
                    case 6: {

                        String playName = null;

                        showGroupAboveAverage(conn, playName);//The name of the method for procedure 6

                    }
                    break;

                    //This is the seventh procedure
                    case 7: {

                        String bNum = null;

                        selectAllBoardsAcceptBoardNum1(conn, bNum);//The name of the method for procedure 7

                    }
                    break;

                    //This is used to exit the program when the procedures have been run or when the user wnats to exit
                    case 8: {
                        System.out.println("Exiting Program");
                        System.exit(0);
                    }

                }

            } while (choice != 8);//this creates a loop so that the user can keep running procedures until they wish to exit/select 8

        } catch (Exception e) {
            System.out.println("Error: " + e.toString());//This is the exception that will appear if an error is caught
        }
    }
}