import java.io.DataOutput;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by Tomer Katzav (ID: 302175716) and Nir Mekin(ID: 301734158)  on 21/11/2016.
 */
public class TableCreator {
    static final String JDBC_Driver = "com.mysql.jdbc.Driver";  // jdbc driver and db url
    static final String DB_URL = "jdbc:mysql://localhost:3306/college";
    static final String UserName = "root";      // DB login information
    static final String Password = "Ktzv3404";

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try{
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to the selected database... Please hold on");
            connection = DriverManager.getConnection(DB_URL, UserName, Password);
            System.out.println("You are now successfully connected to the database!");
            statement = connection.createStatement();
            statement.execute("CREATE TABLE Teachers " +
                    "(ID INTEGER, " +
                    "Name VARCHAR(25), " +
                    "Age INTEGER, " +
                    "Phone VARCHAR(25), " +
                    "Adress VARCHAR(25), " +
                    "BirthDate VARCHAR(25), " +
                    "PRIMARY KEY ( ID ))");
            statement.execute("CREATE TABLE Courses " +
                    "(CourseNum INTEGER, " +
                    "Subject VARCHAR(25), " +
                    "Semester VARCHAR(25), " +
                    "Year VARCHAR(25), " +
                    "Weekly_Hours INTEGER, " +
                    "PRIMARY KEY ( CourseNum ))");
            statement.execute("CREATE TABLE Classes " +
                    "(ClassNum INTEGER, " +
                    "Building VARCHAR(25), " +
                    "Floor INTEGER, " +
                    "PRIMARY KEY ( ClassNum ))");
            statement.execute("CREATE TABLE Lecture " +
                    "(ID INTEGER, " +
                    "ClassNum INTEGER, " +
                    "CourseNum INTEGER, " +
                    "Day VARCHAR(25), " +
                    "Hour VARCHAR(25), " +
                    "FOREIGN KEY (ID) REFERENCES Teachers (ID), " +
                    "FOREIGN KEY (CourseNum) REFERENCES Courses (CourseNum), " +
                    "FOREIGN KEY (ClassNum) REFERENCES Classes (ClassNum))");
            System.out.println("The tables were created in the database successfully!");
            System.out.println("Now inserting the requested data into the tables and columns...");
            System.out.println("...\n...\n...\n");
            statement.executeUpdate("INSERT INTO `Classes`" +
                    "VALUES (2105, 'Mitchell', 1)");
            statement.executeUpdate("INSERT INTO `Classes`" +
                    "VALUES (2204, 'Mitchell', 2)");
            statement.executeUpdate("INSERT INTO `Classes`" +
                    "VALUES (246, 'Fernik', 3)");
            statement.executeUpdate("INSERT INTO `Teachers`" +
                    "VALUES (302175716, 'Tomer Katzav', 27, 0521112223, 'Poleg 1', '04/04/1989')");
            statement.executeUpdate("INSERT INTO `Teachers`" +
                    "VALUES (301734158, 'Nir Mekin', 27, 0508939643, 'Levi 1', '07/05/1989')");
            statement.executeUpdate("INSERT INTO `Teachers`" +
                    "VALUES (456102333, 'Moshe Levi', 40, 054101334, 'Sokolov 3', '30/02/1976')");
            statement.executeUpdate("INSERT INTO `Courses`" +
                    "VALUES (31, 'Histroy', 'A', '2nd', 4)");
            statement.executeUpdate("INSERT INTO `Courses`" +
                    "VALUES (6, 'Mathematics', 'B', '1st', 8)");
            statement.executeUpdate("INSERT INTO `Courses`" +
                    "VALUES (50, 'Arts', 'Summer', '3rd', 2)");
            statement.executeUpdate("INSERT INTO `Lecture` " +
                    "VALUES (302175716, 2105, 31, 'Monday', '08:00')");
            statement.executeUpdate("INSERT INTO `Lecture` " +
                    "VALUES (301734158, 2204, 6, 'Wednesday', '12:00')");
            statement.executeUpdate("INSERT INTO `Lecture` " +
                    "VALUES (456102333, 246, 50, 'Tuesday', '09:30')");
            System.out.println("Done!");
        }catch(SQLException se){
            System.out.println("SQL Exception");
            se.printStackTrace();
        }catch(Exception e){
            System.out.println("Exception");
            e.printStackTrace();
        }finally{
            try{
                if (statement != null)
                    connection.close();
            }catch(SQLException se){
                System.out.println("SQL Exception while disconnecting");
                se.printStackTrace();
            }
        }
        System.out.println("The program will now end!");
    }
}