import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class CreateDatabaseFinal
{
public static Statement stmt;

    public static void main(String[] args)
    {
        try {
                Class.forName("com.mysql.jdbc.Driver");
                String url = "jdbc:mysql://localhost:3306";
                Connection con = DriverManager.getConnection(url,"root", "admin");
                stmt = con.createStatement();

                try
                {
                    stmt.executeUpdate("CREATE DATABASE HR3000;");
                    System.out.println("Database created");
                }catch(Exception e)
                {
                    System.out.println(e);
                }
                stmt.executeUpdate("USE HR3000;");
                try
                {
                    stmt.executeUpdate("DROP TABLE Employee;");
                    stmt.executeUpdate("DROP TABLE JobDetails;");
                    stmt.executeUpdate("DROP TABLE PrivateInfo;");
                    stmt.executeUpdate("DROP TABLE OfficeInfo;");
                    stmt.executeUpdate("DROP TABLE EmployeeDetails;");
                    stmt.executeUpdate("DROP TABLE Absence;");
                    stmt.executeUpdate("DROP TABLE Perks;");
                }catch(Exception e)
                {
                    System.out.println(e);
                }
                stmt.executeUpdate("CREATE TABLE Employee (WWID Int(9) UNSIGNED , SSN char(10) , Fname char(15) , Mname char(15) , Lname char(15) , Gender char(1) , Birthdate DATE , Alias char(12) , Comment char(140), PRIMARY KEY (WWID));");
                stmt.executeUpdate("CREATE TABLE JobDetails (Title char(20) , ManagerWWID integer(9) , EmployeeType char(20) , DepartmentDescription char(20) , SubDepartment char(20) , Relationship char(20) , EmployeeTypeC Int(10) , JobHierarchy char(5) , JobFunction char(5) , JobSubFunction integer(3) , AlternateSupervisor integer(9), CostCenter char(20) , JnJCompCodeMRC char(20) , WWID integer(9) UNSIGNED  REFERENCES Employee(WWID));");
                stmt.executeUpdate("CREATE TABLE Perks (Pension bit, Creditcard bit, Gasoline bit, Car bit, Notebook bit, Printer bit, Monitor bit, Docking bit, KeyChain bit, CHandbook bit, WWID Int(9) UNSIGNED  REFERENCES Employee(WWID));");
                stmt.executeUpdate("CREATE TABLE PrivateInfo (Telephone char(18) , Telefax char(18) , Mobile char(18) , Adress char(30) , ZipCode char(12) , WWID integer(9) UNSIGNED  REFERENCES Employee(WWID));");
                stmt.executeUpdate("CREATE TABLE OfficeInfo (Telephone char(18) , Telefax char(18) , Mobile char(18) , Adress char(30) , ZipCode char(12) , Country char(20) , Email char(28) , WWID integer(9) UNSIGNED  REFERENCES Employee(WWID));");
                stmt.executeUpdate("CREATE TABLE EmployeeDetails (OriginalHireDate date , StartDate date , StopDate date, TerminationReason char(1), AssignmentCode char(1) , AssignmentCountry char(20) , TaxCountry char(20) , WorkDemographic char(20) , ProffNonProff char(1) ,WWID integer(9) UNSIGNED  REFERENCES Employee(WWID));");
                stmt.executeUpdate("CREATE TABLE Absence (Type char(1) , DateFrom date , DateTo date , WWID integer(9) UNSIGNED REFERENCES Employee(WWID));");
                System.out.println("Table created");
                con.close();
                System.out.println("Connection closed: " + con.isClosed());
            }catch(Exception e)
            {
                System.out.print(e);
            }
    }

    public static void fillTable()
    {

        try{
            stmt.executeUpdate("INSERT INTO Employee VALUES();");
        }catch(Exception e)
        {
            System.out.println(e);
        }
    }
}