package databaseinsert;

import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.*;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Random;
import java.util.Scanner;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Anna Hnatkowska
 */
public class DataCreator {
    ArrayList<String> name = new ArrayList<>();
    ArrayList<String> sname = new ArrayList<>();
    ArrayList<String> email = new ArrayList<>();
    ArrayList<String> date = new ArrayList<>();
    ArrayList<String> company = new ArrayList<>();
    ArrayList<String> phone = new ArrayList<>();
    ArrayList<String> hour = new ArrayList<>();
    ArrayList<String> pesel = new ArrayList<>();
    ArrayList<String> number = new ArrayList<>();
    ArrayList<String> street = new ArrayList<>();
    ArrayList<String> postal = new ArrayList<>();
    ArrayList<String> city = new ArrayList<>();
    ArrayList<String> country = new ArrayList<>();
    ArrayList<String> oplacone = new ArrayList<>();
    ArrayList<String> osobaFiz = new ArrayList<>();
    ArrayList<String> nip = new ArrayList<>();
    ArrayList<String> cena = new ArrayList<>();
    HashSet<String> nazwaKwatery = new HashSet<>();
    HashSet<String> nazwaFunkcji = new HashSet<>();
    HashSet<String> nazwaUslugi = new HashSet<>();
    HashMap<Integer, Integer> kwatery = new HashMap<>();
    ArrayList<String> uzyteNipy = new ArrayList<>();
    ArrayList<Integer> oplaconeZlecenia = new ArrayList<>();
    int addedRows = 0;
    int rowsToAdd;
    int gravesToAdd;


    public void getData() throws FileNotFoundException, IOException{
        File file = new File("E:/dane.txt");
        Scanner sc = new Scanner(file);

        while (sc.hasNextLine()){
                String line = sc.nextLine();
                line = line.replaceAll("\\s", "");
                //System.out.println(line);
                String[] details = line.split(";");
                name.add(details[1]);
                sname.add(details[2]);
                email.add(details[3]);
                date.add(details[4]);
                company.add(details[5]);
                phone.add(details[6]);
                hour.add(details[7]);
                pesel.add(details[8]);
                number.add(details[9]);
                street.add(details[10]);
                postal.add(details[11]);
                city.add(details[12]);
                country.add(details[13]);
                oplacone.add(details[14]);
                osobaFiz.add(details[15]);
                nip.add(details[16]);
                cena.add(details[17]);
                nazwaKwatery.add(details[18]);
                nazwaFunkcji.add(details[19]);
                nazwaUslugi.add(details[20]);


        }

    }


    public String getRandom(ArrayList<String> list){
        Random r = new Random();
        return list.get(r.nextInt(498));
    }

    public int getRandomKey(){
        Random r = new Random();
        return r.nextInt(1499);
    }

    public double prices() {
      Random r = new Random();
      int a = r.nextInt();

      DecimalFormat twoDForm = new DecimalFormat("#.##");
      return Double.valueOf(twoDForm.format(a));
}

    public Connection connect(){
        Connection con = null;

        String url = "jdbc:mysql://localhost:3306/mydb1";
        String user = "root";
        String password = "qqqq";

        try {

            con = DriverManager.getConnection(url, user, password);

        } catch (SQLException ex) {
            System.out.println("Connection error");
        }

        return con;
    }

    public void closeConnection(Connection con){
        try {
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataCreator.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void adresy() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        for (int i=0; i<rowsToAdd; i++){
        stmt.execute("set names 'utf8'");

         String query = "INSERT INTO mydb1.adresy (AdresID, Ulica, NrBudynku, NrLokalu, KodPocztowy, Miejscowosc, Kraj) "
                + "VALUES (NULL,'"+ getRandom(street) +"','"+ getRandom(number) +"','"+ getRandom(number) +"','"+ getRandom(postal) +"','"+ getRandom(city) +"','"+getRandom(country)+"');";
        System.out.println(query);
        stmt.executeUpdate(query);
        }
        con.close();
    }

    public void cenniki() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        Random r = new Random();
        for (int i=0; i<10; i++){
        stmt.execute("set names 'utf8'");
         String query = "INSERT INTO mydb1.cenniki (idCennik, CenaZa10, CenaZa20, CenaZaWieczyste) "
                + "VALUES (NULL,'"+ getRandom(cena) + "','"+ getRandom(cena) +"','"+ getRandom(cena) +"');";
        //System.out.println(query);
        stmt.executeUpdate(query);
        addedRows++;
        }
        con.close();
    }

    public void funkcje() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        for (String s: nazwaFunkcji){
        stmt.execute("set names 'utf8'");
         String query = "INSERT INTO mydb1.funkcje (FunkcjaID, Nazwa) "
                + "VALUES (NULL,'"+ s +"');";
        System.out.println(query);
        stmt.executeUpdate(query);
        addedRows++;
        }
        con.close();
    }

        public void rodzajeKwater() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        Random r = new Random();
        for (String s: nazwaKwatery){
        stmt.execute("set names 'utf8'");
        int key = r.nextInt(nazwaKwatery.size());
        do{
            key = r.nextInt(nazwaKwatery.size());
        } while (key == 0);
         String query = "INSERT INTO mydb1.rodzajeKwater (idRodzajKwat, Nazwa, CennikID) "
                + "VALUES (NULL,'"+ s +"',"+ key +");";
        System.out.println(query);
        stmt.executeUpdate(query);
        addedRows++;
        }
        con.close();
    }

    public void kwatery() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        Random r = new Random();
        int rodzaj;
        for (int i=0; i<100; i++){
            for(int j=0; j<100; j++){
                do{
                    rodzaj = r.nextInt(nazwaKwatery.size());
                } while (rodzaj == 0);
            stmt.execute("set names 'utf8'");
            String query = "INSERT INTO mydb1.kwatery (NrSektora, NrKwatery, RodzajID) "
                    + "VALUES ('"+ i +"','"+ j +"','"+ rodzaj +"');";
            kwatery.put(i, j);
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
            }
        }
        con.close();
    }

    public void zleconoNa() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        Random r = new Random();
        for (int i=0; i<rowsToAdd-1; i++){
            int zlecenie = r.nextInt(rowsToAdd);
            int kwat = r.nextInt(kwatery.size());
            stmt.execute("set names 'utf8'");

             String query = "INSERT INTO mydb1.zleconoNa (ZlecenieID, NrSektora, NrKwatery) "
                    + "VALUES ('"+ (i+1) +"','"+ kwat +"','"+ kwatery.get(kwat) +"');";
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }

    public void zakladyPogrzebowe() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        Random r = new Random();
        String fpart = "64872";
        Integer spart = 1090;
        String strSpart;
        for (int i=0; i<rowsToAdd; i++){
            spart++;
            strSpart = spart.toString();
            String nip = fpart + strSpart;
            stmt.execute("set names 'utf8'");
             String query = "INSERT INTO mydb1.ZakladyPogrzebowe (NIP, NazwaZakladu, Telefon, OsobaKontaktowaImie, OsobaKontaktowaNazwisko) "
                    + "VALUES ('"+ nip +"','"+ getRandom(company) +"','"+ getRandom(phone) +"','" + getRandom(name) + "','" + getRandom(sname)+"');";
            uzyteNipy.add(nip);
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }


    public void zmarli() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        String pesPocz = "740102";
        Integer pesKon = 10000;
        for (int i=0; i<rowsToAdd; i++){
            stmt.execute("set names 'utf8'");
            String bday, dday;
            Integer bd, dd, bdm, ddm, bdd, ddd;
            do{
                bday = getRandom(date);
                dday = getRandom(date);
                bd = bday.charAt(0)*1000 + bday.charAt(1)*100 + bday.charAt(2)*10 + bday.charAt(3);
                dd = dday.charAt(0)*1000 + dday.charAt(1)*100 + dday.charAt(2)*10 + dday.charAt(3);
            } while (bd >= dd);
            String pesel = pesPocz + pesKon.toString();
             String query = "INSERT INTO mydb1.Zmarli (ZmarlyID, Imie, DrugieImie, Nazwisko, NazwiskoPanienskie, DrugieNazwisko, DataUrodzenia, DataZgonu, PESEL) "
                    + "VALUES (NULL,'"+ getRandom(name) +"','"+ getRandom(name) +"','"+ getRandom(sname) +"','" + getRandom(sname) + "','" + getRandom(sname)+"','" + bday +"','" + dday + "','" + pesel + "');";
            System.out.println(query);
            stmt.executeUpdate(query);
            pesKon++;
            addedRows++;
        }
        con.close();
    }

     public void wlasciciele() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Random r = new Random();
        Statement stmt = con.createStatement();
        for (int i=0; i<rowsToAdd-1; i++){
            stmt.execute("set names 'utf8'");
             String query = "INSERT INTO mydb1.Wlasciciele (WlascicielID, Imie, DrugieImie, NazwiskoWlasciciela, DrugieNazwisko, Telefon, Email, AdresID) "
                    + "VALUES (NULL,'"+ getRandom(name) +"','"+ getRandom(name) +"','"+ getRandom(sname) +"','" + getRandom(sname) + "','" + getRandom(phone)+"','" + getRandom(email) + "','" + (i+1) + "');";
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }

    public void pracownicy() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        String pesPocz = "740102";
        Integer pesKon = 10000;
        Random r = new Random();
        for (int i=0; i<rowsToAdd; i++){
            stmt.execute("set names 'utf8'");
            int funkcja;
            do {
                funkcja = r.nextInt(nazwaFunkcji.size());
            } while(funkcja == 0);
            String data = getRandom(date);
            pesKon++;
            String pesel = pesPocz + pesKon.toString();
            String query = "INSERT INTO mydb1.pracownicy (PracownikID, Imie, DrugieImie, Nazwisko, DrugieNazwisko, PESEL, Telefon, Email, FunkcjaID) "
                    + "VALUES (NULL,'"+ getRandom(name) +"','"+ getRandom(name) +"','"+ getRandom(sname) +"','" + getRandom(sname) + "','" + pesel + "','" + getRandom(phone) + "','" + getRandom(email) + "','" + funkcja + "');";
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }

        public void zleceniodawcy() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        Random r = new Random();
        String nipPocz = "98273";
        Integer nipKon = 20000;
        for (int i=0; i<rowsToAdd; i++){
            stmt.execute("set names 'utf8'");
            String of = getRandom(osobaFiz);
            String nip = nipPocz + nipKon.toString();
            String query;
            if (of.equals("true")){
            query = "INSERT INTO mydb1.zleceniodawcy (ZleceniodawcaID, Imie, Nazwisko, OsobaFizyczna, Nazwa, NIP, Telefon, Email) "
                    + "VALUES (NULL,'"+ getRandom(name) +"','"+ getRandom(sname) +"',"+ true +",NULL,NULL,'" + getRandom(phone) + "','" + getRandom(email) + "');";
            }
            else{
            query = "INSERT INTO mydb1.zleceniodawcy (ZleceniodawcaID, Imie, Nazwisko, OsobaFizyczna, Nazwa, NIP, Telefon, Email) "
                    + "VALUES (NULL,"+ "NULL,NULL" + "," + false + ",'" + getRandom(company) + "','" + nip + "','" + getRandom(phone) + "','" + getRandom(email) + "');";
            }
            nipKon++;
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }

    public void pogrzeby() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        for (int i=0; i<rowsToAdd; i++){
            int j = i+1;
            stmt.execute("set names 'utf8'");
             String query = "INSERT INTO mydb1.Pogrzeby (PogrzebID, DzienPogrzebu, GodzinaPogrzebu, ZmarlyID, NIPZakladu) "
                    + "VALUES (NULL, '"+ getRandom(date) +"','"+ getRandom(hour) +"','"+ j +"','" + uzyteNipy.get(i) + "');";
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }

    public void uslugi() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        for (String s: nazwaUslugi){
            Random r = new Random();
            stmt.execute("set names 'utf8'");
             String query = "INSERT INTO mydb1.Uslugi (UslugaID, NazwaUslugi, CenaUslugi, SzacCzas) "
                    + "VALUES (NULL,'"+ s +"','"+ getRandom(cena) +"','"+ r.nextInt(50) +"');";
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }

    public void aktyZgonu() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        int nr = 28014;
        for (int i=1; i < rowsToAdd-(rowsToAdd*0.2); i++){
            Random r = new Random();
            stmt.execute("set names 'utf8'");
             String query = "INSERT INTO mydb1.aktyZgonu (NrAktu, ZmarlyID, DataWystawienia, GodzinaZgonu, ImieOjca, NazwiskoOjca, ImieMatki, NazwiskoMatki) "
                    + "VALUES ('" + nr + "','" + i +"','"+ getRandom(date) +"','"+ getRandom(hour) + "','" + getRandom(name) + "','" + getRandom(sname) + "','" + getRandom(name) + "','" + getRandom(sname) + "');";
            System.out.println(query);
            stmt.executeUpdate(query);
            nr++;
            addedRows++;
        }
        con.close();
    }

    public void oplaty() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        for (int i=0; i<rowsToAdd-(rowsToAdd*0.2); i++){
            Random r = new Random();
            int cennik = r.nextInt(10);
            do{
                cennik = r.nextInt(10);
            } while (cennik == 0);
            String date1, date2;
            int d1, d2, d1m, d2m;
            do{
                date1 = getRandom(date);
                date2 = getRandom(date);
                d1 = date1.charAt(0)*1000 + date1.charAt(1)*100 + date1.charAt(2)*10 + date1.charAt(3);
                d2 = date2.charAt(0)*1000 + date2.charAt(1)*100 + date2.charAt(2)*10 + date2.charAt(3);
                d1m = date1.charAt(5)*10 + date1.charAt(6);
                d2m = date2.charAt(5)*10 + date2.charAt(6);
            } while (d1 > d2 || ((d1 == d2) && (d1m >= d2m)));
            int sektor = r.nextInt(kwatery.size());
            stmt.execute("set names 'utf8'");
             String query = "INSERT INTO mydb1.Oplaty (OplataID, DzienWplaty, DataObowiazywania, WlascicielID, CennikID, NrSektora, NrKwatery) "
                    + "VALUES (NULL,'"+ date1 +"','"+ date2 +"','"+ (i+1) + "','" + cennik + "','" + sektor + "','" + kwatery.get(sektor)+"');";
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }

    public void zlecenia() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        Statement stmt = con.createStatement();
        for (int i=0; i<rowsToAdd; i++){
            boolean opl = true;
            if (i >= 1000) opl = false;
            Random r = new Random();
            int pracId = r.nextInt(rowsToAdd);
            int uslId = r.nextInt(nazwaUslugi.size());
            int zlecId = r.nextInt(rowsToAdd);
            do{
                pracId = r.nextInt(rowsToAdd);
            } while (pracId == 0);
            do{
                uslId = r.nextInt(nazwaUslugi.size());
            } while (uslId == 0);
            do{
                zlecId = r.nextInt(rowsToAdd);
            } while (zlecId == 0);
            String date1, date2;
            int d1, d2, d1m, d2m;
            do{
                date1 = getRandom(date);
                date2 = getRandom(date);
                d1 = date1.charAt(0)*1000 + date1.charAt(1)*100 + date1.charAt(2)*10 + date1.charAt(3);
                d2 = date2.charAt(0)*1000 + date2.charAt(1)*100 + date2.charAt(2)*10 + date2.charAt(3);
                d1m = date1.charAt(5)*10 + date1.charAt(6);
                d2m = date2.charAt(5)*10 + date2.charAt(6);
            } while (d1 > d2 || ((d1 == d2) && (d1m >= d2m)));
            int sektor = r.nextInt(kwatery.size());
            stmt.execute("set names 'utf8'");
             String query = "INSERT INTO mydb1.Zlecenia (ZlecenieID, DataRozpoczecia, DataZakonczenia, Oplacone, PracownikID, UslugaID, ZleceniodawcaID) "
                    + "VALUES (NULL,'"+ date1 +"','"+ date2 +"',"+ opl + ",'" + pracId + "','" + uslId + "','" + zlecId +"');";
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }

    public void oplacono() throws SQLException{
        Connection con = connect();
        if (con == null) return;
        HashSet<Integer> usedPpl = new HashSet<>();
        HashSet<Integer> usedPaym = new HashSet<>();
        Statement stmt = con.createStatement();
        for (int i=0; i<rowsToAdd-(rowsToAdd*0.4); i++){
            Random r = new Random();
            Integer person = r.nextInt(rowsToAdd);
            Integer paym = r.nextInt((int) (rowsToAdd-(rowsToAdd*0.2)));
            do {
                person = r.nextInt(rowsToAdd);
            } while (person == 0 || usedPpl.contains(person));
            do {
                paym = r.nextInt((int) (rowsToAdd-(rowsToAdd*0.2)));
            } while (paym == 0 || usedPaym.contains(paym));
            usedPpl.add(person);
            usedPaym.add(paym);
            stmt.execute("set names 'utf8'");
             String query = "INSERT INTO mydb1.Oplacono (ZmarlyID, OplataID) "
                    + "VALUES ('"+ person +"','"+ paym +"');";
            System.out.println(query);
            stmt.executeUpdate(query);
            addedRows++;
        }
        con.close();
    }


    public static void main(String[] args) throws IOException, SQLException {
        DataCreator d = new DataCreator();
        d.rowsToAdd = 5000;
        d.getData();
        d.adresy();
        d.cenniki();
        d.funkcje();
        d.rodzajeKwater();
        d.kwatery();
        d.pracownicy();
        d.zleceniodawcy();
        d.zakladyPogrzebowe();
        d.zmarli();
        d.wlasciciele();
        d.pogrzeby();
        d.uslugi();
        d.aktyZgonu();
        d.oplaty();
        d.zlecenia();
        d.zleconoNa();
        d.oplacono();
        System.out.println("Dodano " + d.addedRows + " rekordów.");
    }
}