/**
 * Created by Danutz on 23/01/16.
 */

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import java.util.Scanner;
import java.lang.*;
import java.io.*;


public class JavaInserts {

    private static Connection con = null;
    private static Statement stmt = null;
    private static ResultSet res = null;

    private static String database = "jdbc:oracle:thin:@oracle-lab.cs.univie.ac.at:1521:lab";
    private static String user = "a1405569";
    private static String pass = "daniel07";

    // Functions that will generate the specified atribute.

    private static String generateLocation() {

        Random rand = new Random();

        Scanner locationFile = null;
        try {
            locationFile = new Scanner(new File("Files/Locations")).useDelimiter(",");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("Because of file reader");
        }

        List<String> locationArray = new ArrayList<String>();

        while (locationFile.hasNext()) {
            // find next line
            locationArray.add(locationFile.next());
        }
        locationFile.close();

        int randUid = rand.nextInt(locationArray.size());
        String location = locationArray.get(randUid);

        return location;
    }

    private static String plz() {

        Random rand = new Random();
        int randomPlz = (rand.nextInt(10000 - 1000) + 100);
        String plz = Integer.toString(randomPlz);

        return plz;
    }

    private static String homeNumber() {

        Random rand = new Random();
        int randomNr = (rand.nextInt(1000 - 1) + 1);
        String homeNumber = Integer.toString(randomNr);

        return homeNumber;
    }

    private static String firstName() {

        Random rand = new Random();

        Scanner firstNameFile = null;
        try {
            firstNameFile = new Scanner(new File("Files/firstName.txt")).useDelimiter(",");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("Because of file reader");
        }

        List<String> firstNameArray = new ArrayList<String>();

        while (firstNameFile.hasNext()) {
            // find next line
            firstNameArray.add(firstNameFile.next());
        }
        firstNameFile.close();

        int randUid = rand.nextInt(firstNameArray.size());
        String firstName = firstNameArray.get(randUid);

        return firstName;
    }

    private static String lastName() {

        Random rand = new Random();

        Scanner lastNameFile = null;
        try {
            lastNameFile = new Scanner(new File("Files/lastName.txt")).useDelimiter(",");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("Because of file reader");
        }

        List<String> lastNameArray = new ArrayList<String>();

        while (lastNameFile.hasNext()) {
            // find next line
            lastNameArray.add(lastNameFile.next());
        }
        lastNameFile.close();

        int randUid = rand.nextInt(lastNameArray.size());
        String lastName = lastNameArray.get(randUid);

        return lastName;
    }

    private static String street() {

        Random rand = new Random();

        Scanner streetFile = null;
        try {
            streetFile = new Scanner(new File("Files/streetName.txt")).useDelimiter(",");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("Because of file reader");
        }

        List<String> streetNameArray = new ArrayList<String>();

        while (streetFile.hasNext()) {
            // find next line
            streetNameArray.add(streetFile.next());
        }
        streetFile.close();

        int randUid = rand.nextInt(streetNameArray.size());
        String street = streetNameArray.get(randUid);

        return street;
    }

    private static String telefonNr() {

        Random rand = new Random();

        Scanner telNrFile = null;
        try {
            telNrFile = new Scanner(new File("Files/phoneNr.txt")).useDelimiter(",");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("Because of file reader");
        }

        List<String> teleNrArray = new ArrayList<String>();

        while (telNrFile.hasNext()) {
            // find next line
            teleNrArray.add(telNrFile.next());
        }
        telNrFile.close();

        int randUid = rand.nextInt(teleNrArray.size());
        String telefonNr = teleNrArray.get(randUid);

        return telefonNr;
    }

    private static String email() {

        Random rand = new Random();

        Scanner emailFile = null;
        try {
            emailFile = new Scanner(new File("Files/emails.txt")).useDelimiter(",");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("Because of file reader");
        }

        List<String> emailArray = new ArrayList<String>();

        while (emailFile.hasNext()) {
            // find next line
            emailArray.add(emailFile.next());
        }
        emailFile.close();

        int randUid = rand.nextInt(emailArray.size());
        String email = emailArray.get(randUid);

        return email;
    }

    private static String getColor() {


        Random rand = new Random();

        Scanner colorFile = null;
        try {
            colorFile = new Scanner(new File("Files/colors.txt")).useDelimiter(",");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
            System.out.println("Because of file reader");
        }

        List<String> colorArray = new ArrayList<String>();

        while (colorFile.hasNext()) {
            // find next line
            colorArray.add(colorFile.next());
        }
        colorFile.close();

        int randUid = rand.nextInt(colorArray.size());
        String color = colorArray.get(randUid);

        return color;
    }

    private static String birthDate() {
        Random generator = new Random();
        int day = generator.nextInt(27) + 1;
        int month = generator.nextInt(11) + 1;
        int year = 1940 + generator.nextInt(58);
        String date = "TO_DATE('" + day + "/" + month + "/" + year + "','DD/MM/YYYY')";

        return date;

    }

    private static String reparation_contract_date() {

        Random generator = new Random();
        int day = generator.nextInt(27) + 1;
        int month = generator.nextInt(11) + 1;
        int year = 2000 + generator.nextInt(14);
        int hour = generator.nextInt(22) + 1;
        int min = generator.nextInt(59) + 1;
        int s = generator.nextInt(59) + 1;
        String date = "TO_DATE('" + day + "/" + month + "/" + year + " " + hour + ":" + min + ":" + s + "', 'DD/MM/YYYY hh24:mi:ss')";

        return date;
    }

    public static void main(String args[]) throws Exception {

        int countMitarbeiter = 0;
        int countClientNumber = 0;
        int countMechanik = 0;
        int countKundebetreuer = 0;
        int initiation = 10;
        int numberOfIns = 2000;

        //salvate in fisier

        String car;

        //  Variables with themporary value

        List<String> temporaryUid = new ArrayList<String>();
        List<String> temporaryKfz = new ArrayList<String>();
        List<Integer> temporaryMechID = new ArrayList<Integer>();
        List<Integer> temporaryBetID = new ArrayList<Integer>();


        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(database, user, pass);
            stmt = con.createStatement();
            Random rand = new Random();

            System.out.println("please enter the table you want to update:");
            BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
            System.out.print("Enter String");
            String control = br.readLine();


            do {

                switch ("autovermietungsfirma") {

                    // -------------------- insert Autovermietlungsfirma
                    case "autovermietungsfirma": {
                        int randomNumber = (rand.nextInt(1000000000 - 100000000) + 1000000000);
                        String uid = "\'ATU" + Integer.toString(randomNumber) + "\'";

                        temporaryUid.add(uid);


                        Scanner firmaFile = null;
                        try {
                            firmaFile = new Scanner(new File("Files/firmaname.txt")).useDelimiter(",");
                        } catch (FileNotFoundException e) {
                            e.printStackTrace();
                            System.out.println("Because of file reader");
                        }

                        List<String> firmaArrya = new ArrayList<String>();

                        while (firmaFile.hasNext()) {
                            // find next line
                            firmaArrya.add(firmaFile.next());
                        }
                        firmaFile.close();

                        int randUid = rand.nextInt(firmaArrya.size());
                        String firmaNamen = firmaArrya.get(randUid);

                        try {

                            String insertIntoAutovermietung = "INSERT INTO Autovermietungsfirma (Umsatzsteuer_id, Name, Land, Ort, PLZ, Strasse, Nummer, Telefon)  VALUES (" + uid + ", " + firmaNamen + ", 'Austria' ," + generateLocation() + ", " + plz() + ", " + street() + ", " + homeNumber() + ", " + telefonNr() + ")";


                            stmt.executeUpdate(insertIntoAutovermietung);
                        } catch (Exception e) {
                            System.err.println("Insert ... + " + e.getMessage());
                        }

                    }


                    // ------------------- insert Into Mitarbeiter
                    case "mitarbeiter": {

                        do {
                            countMitarbeiter++;

                            String mitarbeiterType[] = {"'Mechaniker/in'", "'Kundebetreuer/in'"};

                            double salary[] = {1200, 1450, 1700, 2150.99, 2400.84, 3100};
                            double randSalary = salary[rand.nextInt(6)];

                            int mitarbeiterRandUid = rand.nextInt(temporaryUid.size());
                            while (mitarbeiterRandUid < 0) {
                                mitarbeiterRandUid = rand.nextInt(temporaryUid.size());
                            }
                            String mitarbeiterUid = temporaryUid.get(mitarbeiterRandUid);

                            int oneORzero;
                            if (rand.nextBoolean()) {
                                oneORzero = 1;
                            } else {
                                oneORzero = 0;
                            }

                            String typeOfEmployee = mitarbeiterType[oneORzero];

                            int mitarbeitersChef = 1;

                            if (typeOfEmployee.equals("'Mechaniker/in'")) {
                                temporaryMechID.add(countMitarbeiter);
                                if (temporaryMechID.size() > 10) {
                                    mitarbeitersChef = temporaryMechID.get(rand.nextInt(8));
                                }
                                countMechanik++;
                            } else {
                                temporaryBetID.add(countMitarbeiter);
                                if (temporaryMechID.size() > 10) {
                                    mitarbeitersChef = temporaryBetID.get(rand.nextInt(8));
                                }
                                countKundebetreuer++;
                            }


                            try {

                                String insertIntoMitarbeiter = "INSERT INTO Mitarbeiter (Nachname, Vorname, Geb_datum, Gehalt , Umsatzsteuer_id, Angestelt_als, ChefMNr) VALUES  (" + lastName() + ", " + firstName() + ", " + birthDate() + ", " + randSalary + ", " + mitarbeiterUid + ", " + typeOfEmployee + ", " + mitarbeitersChef + ")";


                                stmt.executeUpdate(insertIntoMitarbeiter);
                            } catch (Exception e) {
                                System.err.println("Insert ... + " + e.getMessage());
                            }


                            // -----------------    Insert into Mechankiker

                            if (typeOfEmployee.equals(mitarbeiterType[0])) {

                                String erfahrung[] = {"'Ja'", "'Nein'", "'Medium'", "'Ich habe fur kurze Zeit in diesem Bereich gearbeitet'", "'Einschulung'"};


                                try {

                                    String insertIntoMechankiker = "INSERT INTO Mechanik (MID, Erfahrung)  VALUES (" + countMitarbeiter + ", " + erfahrung[rand.nextInt(4)] + ")";


                                    stmt.executeUpdate(insertIntoMechankiker);
                                } catch (Exception e) {
                                    System.err.println("Insert ... + " + e.getMessage());
                                }


                            } else {

                                // ---------------      Insert into Kundebetreuer

                                try {

                                    String insertIntoKundebetreuer = "INSERT INTO Kundebetreuer (MID, Telefon, E_Mail)  VALUES (" + countMitarbeiter + ", " + telefonNr() + ", " + email() + ")";


                                    stmt.executeUpdate(insertIntoKundebetreuer);
                                } catch (Exception e) {
                                    System.err.println("Insert ... + " + e.getMessage());
                                }


                            }
                            initiation--;
                        } while (initiation > 0);
                    }
                    case "Kunde": {
                        // --------------        Insert into Kunde


                        countClientNumber++;

                        int randBet = rand.nextInt(temporaryBetID.size());
                        while (randBet < 1) {
                            randBet = rand.nextInt(temporaryBetID.size());
                        }


                        int bonus = rand.nextInt(100);


                        try {
                            String insertIntoKunde = "INSERT INTO Kunde (Nachname, Vorname, Geb_datum, Telefon, E_Mail, Bonus, MID)  VALUES (" + lastName() + ", " + firstName() + ", " + birthDate() + ", " + telefonNr() + ", " + email() + ", " + bonus + ", " + temporaryBetID.get(randBet) + ")";


                            stmt.executeUpdate(insertIntoKunde);
                        } catch (Exception e) {
                            System.err.println("Insert ... + " + e.getMessage());
                        }

                    }


                    case "auto": {
                        // ------------     Insert into Auto


                        Scanner autoFile = null;
                        try {
                            autoFile = new Scanner(new File("Files/Cars/Cars")).useDelimiter(",");
                        } catch (FileNotFoundException e) {
                            e.printStackTrace();
                            System.out.println("Because of file reader");
                        }

                        List<String> autoArray = new ArrayList<String>();

                        while (autoFile.hasNext()) {
                            // find next line
                            autoArray.add(autoFile.next());
                        }
                        autoFile.close();

                        int randUid = rand.nextInt(autoArray.size());
                        while (randUid < 1) {
                            randUid = rand.nextInt(autoArray.size());
                        }
                        car = autoArray.get(randUid);


                        Scanner inSecondFile = null;
                        try {
                            inSecondFile = new Scanner(new File("Files/Cars/" + car)).useDelimiter(",");
                        } catch (FileNotFoundException e) {
                            e.printStackTrace();
                            System.out.println("Because of file reader");
                        }

                        List<String> temps2 = new ArrayList<String>();

                        while (inSecondFile.hasNext()) {
                            // find next line
                            temps2.add(inSecondFile.next());
                        }
                        inSecondFile.close();

                        int randModel = rand.nextInt(temps2.size());
                        while (randModel < 1) {
                            randModel = rand.nextInt(temps2.size());
                        }
                        String model = temps2.get(randModel);


                        int randNumber = (rand.nextInt(1000000 - 100000) + 100000);
                        String kfz = "\'W" + Integer.toString(randNumber) + "\'";
                        temporaryKfz.add(kfz);


                        int autoRandUid = rand.nextInt((temporaryUid.size() - 1) + 1);
                        while (temporaryUid.size() < 1) {
                            autoRandUid = 1;
                        }
                        String autoUid = temporaryUid.get(autoRandUid);

                        int kundeRandomID = rand.nextInt(countClientNumber);
                        while (kundeRandomID < 1) {
                            kundeRandomID = 1;
                        }
                        double randomSize = rand.nextInt(4 - 1) + 2;
                        int randomPlaces = rand.nextInt(7 - 2) + 2;

                        try {
                            String insertIntoAuto = "INSERT INTO Auto (KfzKennzeichen, Farbe, Große, Platze, Brand, Model, Umsatzsteuer_id, KundeID)  VALUES (" + kfz + ", " + getColor() + ", " + randomSize + ", " + randomPlaces + ", " + "'" + car + "'" + ", " + model + ", " + autoUid + ", " + kundeRandomID + ")";

                            stmt.executeUpdate(insertIntoAuto);
                        } catch (Exception e) {
                            System.err.println("Insert ... + " + e.getMessage());
                        }
                    }

                    // -----------      Insert Into reparatur
                    case "reparatur": {

                        int reparaturRandKfz = rand.nextInt(temporaryKfz.size());
                        while (temporaryKfz.size() < 1) {
                            reparaturRandKfz = 1;
                        }
                        String reparaturKfz = temporaryKfz.get(reparaturRandKfz);


                        int randMech = temporaryMechID.get(rand.nextInt(countMechanik));

                        int dauern = rand.nextInt(200);


                        String reparationType[] = {"'Abgasuntersuchung'", "'Achsvermessung'", "'Auswuchten'", "'Anhängerkupplung'", "'Autobatterie'", "'Autoreifen'", "'Bremsen'", "'Hauptuntersuchung'", "'Hebebühne'", "'Lackdoktor'", "'Klimaservice\'", "\'Keilriemen\'"};

                        try {

                            String insertIntoKReparatur = "INSERT INTO Reparatur (KfzKennzeichen, MID, Datum, Dauern, Reparaturtype)  VALUES (" + reparaturKfz + ", " + randMech + ", " + reparation_contract_date() + ", " + dauern + ", " + reparationType[rand.nextInt(11)] + ")";

                            stmt.executeUpdate(insertIntoKReparatur);
                        } catch (Exception e) {
                            System.err.println("Insert ... + " + e.getMessage());
                        }

                    }
                    // ----------   Insert Into Vertrag
                    case "vertrag": {

                        int randBet = temporaryBetID.get(rand.nextInt(countKundebetreuer));

                        String zahlungsart[] = {"'Bar'", "'Kreditkarte'", "'Paypal'"};

                        double randomPreis = (rand.nextInt(4000 - 30) + 30);

                        try {

                            String insertIntoVertrag = "INSERT INTO Vertrag (MID, KundeID, Datum, Preis, Zahlungsart)  VALUES (" + randBet + ", " + countClientNumber + ", " + reparation_contract_date() + ", " + randomPreis + ", " + zahlungsart[2] + ")";
                            System.out.println(insertIntoVertrag);

                            stmt.executeUpdate(insertIntoVertrag);
                        } catch (Exception e) {
                            System.err.println("Insert ... + " + e.getMessage());
                        }

                    }
                    break;

                    numberOfIns--;
                }
                while (numberOfIns > 0) ;

       /* try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            con = DriverManager.getConnection(database, user, pass);
            stmt = con.createStatement();

            try {
                String innsertInto = "INSERT INTO Autovermietungsfirma VALUES ('ATU144171777',' Rent a car','Austria','Wien','1020','Obere Augartenstrße','12A','+43 673351112')";

                stmt.executeUpdate(innsertInto);
            } catch (Exception e) {
                System.err.println("Insert ... + " + e.getMessage());
            }


            res = stmt.executeQuery("SELECT Name FROM Autovermietungsfirma WHERE (Umsatzsteuer_id = 'ATU214356546')");
            if (res.next()) {
                String nameOf = res.getString(1);
                System.out.println("Name = " + nameOf);
            }

            try {
                con.close();
                stmt.close();
                res.close();


            } catch (Exception e) {
                System.out.println("End ... + " + e.getMessage());
            }
        }
        catch (Exception e) {
            System.err.println("Connection ... + " + e.getMessage());
        }*/


                try {
                    con.close();
                    stmt.close();
                    res.close();

                } catch (Exception e) {
                    System.out.println("End ... + " + e.getMessage());
                }

            }

        } catch (Exception e) {
            System.err.println("Connection ... + " + e.getMessage());
        }

    }
}