package chessleague;

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

public class Chessleague {

    public static void main(String[] args) throws SQLException, IOException {
    try
    {
      Class.forName ("com.mysql.jdbc.Driver");
        try ( //username = root, there is no password
                Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/chessleague","root","")) {
            Statement stmnt = con.createStatement();

            // Creating Club table

            stmnt.executeUpdate("CREATE TABLE IF NOT EXISTS Club( "
                    + "ClubName VARCHAR(30) PRIMARY KEY,"
                    + "Address VARCHAR(50), "
                    + "DateFormed DATE)");
            System.out.println("Club table created");

            // Creating Player table

            stmnt.executeUpdate("CREATE TABLE IF NOT EXISTS Player( "
                    + "PlayerName VARCHAR(30) PRIMARY KEY,"
                    + "DateOfBirth DATE,"
                    + "FIDERating INT,"
                    + "FIDETitle VARCHAR(30), "
                    + "ClubName VARCHAR(30), "
                    + "FOREIGN KEY (ClubName) REFERENCES Club(ClubName))");
            System.out.println("Player table created");

            // Creating Match table

            stmnt.executeUpdate("CREATE TABLE IF NOT EXISTS MatchTable( "
                    + "MatchID VARCHAR(10) PRIMARY KEY,"
                    + "MatchTime DATE,"
                    + "Venue VARCHAR(50),"
                    + "Score VARCHAR(10),"
                    + "WinningClub VARCHAR(30),"
                    + "LosingClub VARCHAR(30))");
            System.out.println("MatchTable created");

            // Creating Game table

            stmnt.executeUpdate("CREATE TABLE IF NOT EXISTS Game( "
                    + "GameID VARCHAR(10) PRIMARY KEY,"
                    + "DatePlayed DATE,"
                    + "BoardNum INT,"
                    + "Score VARCHAR(10),"
                    + "MatchID VARCHAR(10),"
                    + "FOREIGN KEY (MatchID) REFERENCES MatchTable (MatchID),"
                    + "WhitePlayer VARCHAR(20),"
                    + "BlackPlayer VARCHAR(20))");
            System.out.println("Game table created");

            con.commit();
            con.close();

//      ResultSet rs=stmnt.executeQuery("select * from emp");
//      while(rs.next())
//          System.out.println(rs.getInt(1)+"  "+rs.getString(2)+"  "+rs.getString(3));
        }
    }
    catch (ClassNotFoundException | SQLException e){ System.out.println(e);
    }
    }
}