/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package databaseassignment;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
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.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;

/**
 *
 * @author Ben
 */
public class DatabaseAssignment {

      /**
     * @param args the command line arguments
     */
      /**
     * @param args the command line arguments
     */
    private static String username, password = "";
    private static String host = "localhost";
    private static String dbname = "";
    private static Connection conn;
    private static Statement s;
    private static FileReader fileReader;
    private static BufferedReader inb;
    private static File inputFile;

    private static Date javaDate;

    private static Scanner sc = new Scanner(System.in);



    public static void main(String[] args) throws SQLException, IOException, Exception {
        // TODO code application logic here

             // 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");
      }

    connectToDatabaseQuick();
    //connectToDatabase(username,password,host,dbname);
    s = conn.createStatement();

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

     conn.commit();


     conn.close();
  }

    public static void createClubTable(Statement s) throws SQLException, ParseException{

       String line = "";
       String[] tokens;

       s.execute("drop table if exists club");
       String create = "create table IF NOT EXISTS Club(ClubName VARCHAR(20) PRIMARY KEY, Address VARCHAR(40), DateFormed DATE)";
       s.executeUpdate(create);

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

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


       try{
           System.out.println("Inserting Data from text file");
           String file = "src\\databaseassignment\\Club.txt";

           inputFile = new File(file);
           fileReader = new FileReader(inputFile);
           inb = new BufferedReader(fileReader);

           System.out.println("Ready to read line");

           line = inb.readLine();
           while( (line != null) ){

               tokens = line.split(",");
               System.out.println(tokens[0] + " "+ tokens[1]+ " "+ tokens[2]+" ");
               ps.setString(1, tokens[0]);
               ps.setString(2, tokens[1]);

               String date_str = (tokens[2]);
               DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                try {
                   javaDate = (java.util.Date) formatter.parse(date_str);
                } catch (ParseException e) {
                    System.out.println("Error: " + e.toString());
                }

                long javaTime = javaDate.getTime();
                java.sql.Date sqlDate = new java.sql.Date(javaTime);


               ps.setDate(3, sqlDate);
               ps.execute();
               ps.clearParameters();

               line=inb.readLine();


           }

           inb.close();
           fileReader.close();

       }
       catch(IOException e){

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

    }

    public static void createPlayerTable(Statement s) throws SQLException{

        String line = "";
        String tokens[];

        s.execute("drop table if exists player");
        String create = "CREATE TABLE IF NOT EXISTS Player (PlayerName VARCHAR (20) PRIMARY KEY, DateOfBirth DATE, FIDERating INT not null, check(FIDERating <=3000 AND FIDERating >= 800), FIDETitle VARCHAR(30), ClubName VARCHAR(30))";


        s.execute(create);

        System.out.println("Player table created");

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


       try{
           System.out.println("Inserting Data from text file");
           String file = "src\\databaseassignment\\Player.txt";

           inputFile = new File(file);
           fileReader = new FileReader(inputFile);
           inb = new BufferedReader(fileReader);

           System.out.println("Ready to read line");

           line = inb.readLine();
           while( (line != null) ){

               tokens = line.split(",");
               System.out.println(tokens[0] + " "  + tokens[1] + " " + tokens[2] + " " + tokens[3]+ " " + tokens[4]);

               ps.setString(1, tokens[0]);

               String date_str = (tokens[1]);

               DateFormat formatter = new SimpleDateFormat("yy-MM-dd");
                try {
                   javaDate = (java.util.Date) formatter.parse(date_str);
                } catch (ParseException e) {
                    System.out.println("Error: " + e.toString());
                }

                long javaTime = javaDate.getTime();
                java.sql.Date sqlDate = new java.sql.Date(javaTime);

                ps.setDate(2, sqlDate);

                ps.setInt(3, Integer.parseInt(tokens[2]));
                ps.setString(4, tokens[3]);
                ps.setString(5, tokens[4]);

                ps.execute();
                ps.clearParameters();

               line=inb.readLine();


           }

           inb.close();
           fileReader.close();

       }
       catch(IOException e){

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

    public static void createGameTable(Statement s) throws SQLException{

        String line = "";
        String tokens[];

        s.execute("drop table if exists game");
        String create = "CREATE TABLE IF NOT EXISTS Game (GameID VARCHAR(10) PRIMARY KEY, DatePlayed DATE, BoardNum INT, Score VARCHAR(3), MatchID VARCHAR(10), WhitePlayer VARCHAR(20), BlackPlayer VARCHAR(20))";

        s.execute(create);

        System.out.println("Game table created");

        String insertGame = "insert into Game values(?,?,?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(insertGame);

        try{
           System.out.println("Inserting Data from text file");
           String file = "src\\databaseassignment\\Game.txt";

           inputFile = new File(file);
           fileReader = new FileReader(inputFile);
           inb = new BufferedReader(fileReader);

           System.out.println("Ready to read line");

           line = inb.readLine();
           while( (line != null) ){

               tokens = line.split(",");
               System.out.println(tokens[0] + " "  + tokens[1] + " " + tokens[2] + " " + tokens[3]+ " " + tokens[4]+ " " + tokens[5] + " " + tokens[6]);

               ps.setString(1, tokens[0]);

               String date_str = (tokens[1]);
               DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
                try {
                   javaDate = (java.util.Date) formatter.parse(date_str);
                } catch (ParseException e) {
                    System.out.println("Error: " + e.toString());
                }
                long javaTime = javaDate.getTime();
                java.sql.Date sqlDate = new java.sql.Date(javaTime);
                ps.setDate(2, sqlDate);

                ps.setInt(3, Integer.parseInt(tokens[2]));
                ps.setString(4, tokens[3]);
                ps.setString(5, tokens[4]);
                ps.setString(6, tokens[5]);
                ps.setString(7, tokens[6]);
                ps.execute();
                ps.clearParameters();

               line=inb.readLine();


           }

           inb.close();
           fileReader.close();

       }
       catch(IOException e){

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


    }

    public static void createMatchTable(Statement s) throws SQLException{

        String line = "";
        String tokens[];

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

        s.execute(create);

        System.out.println("Match table created");

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

        try{
           System.out.println("Inserting Data from text file");
           String file = "src\\databaseassignment\\Match.txt";

           inputFile = new File(file);
           fileReader = new FileReader(inputFile);
           inb = new BufferedReader(fileReader);

           System.out.println("Ready to read line");

           line = inb.readLine();
           while( (line != null) ){

               tokens = line.split(",");
               System.out.println(tokens[0] + " "  + tokens[1] + " " + tokens[2] + " " + tokens[3]+ " " + tokens[4]+ " " + tokens[5]);

               ps.setString(1, tokens[0]);

               String date_str = (tokens[1]);
               DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
                try {
                   javaDate = (java.util.Date) formatter.parse(date_str);
                } catch (ParseException e) {
                    System.out.println("Error: " + e.toString());
                }
                long javaTime = javaDate.getTime();
                java.sql.Date sqlDate = new java.sql.Date(javaTime);
                ps.setDate(2, sqlDate);

                ps.setString(3, tokens[2]);
                ps.setString(4, tokens[3]);
                ps.setString(5, tokens[4]);
                ps.setString(6, tokens[5]);

                ps.execute();
                ps.clearParameters();

               line=inb.readLine();


           }

           inb.close();
           fileReader.close();

       }
       catch(IOException e){

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

    }

    //doesn't work/not needed
    public static void createDatabase(Statement s) {

        try {

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

            s.executeUpdate(sql);
        } catch (SQLException e) {
            System.out.println("Error:" + e.toString());
        }

    }

    public static void connectToDatabase(String username, String password, String host, String dbname) throws SQLException{

         // userid, password and hostname are obtained from the console
        try{
        System.out.println("Type userid, password, hostname or ipaddress, database name: ");
        username = sc.next();
        password = sc.next();
        host = sc.next();
        dbname = sc.next();
        System.out.println(username+" "+password+" "+host+" "+dbname);
        }
        catch(Exception e){
            System.out.println("Error: " + e.toString());
        }

        conn = DriverManager.getConnection
                  ("jdbc:mysql://"+host+":3306/chess", username, password);
        conn.setAutoCommit(false);





    }

    public static void connectToDatabaseQuick() throws SQLException{

        host = "localhost";
        username = "root";
        password = "admin";


         conn = DriverManager.getConnection
                  ("jdbc:mysql://"+host+":3306/chess", username, password);
        conn.setAutoCommit(false);

    }



}