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

public class Main {

    public static void main(String[] args) throws SQLException {
        Connection db = DriverManager.getConnection("jdbc:sqlite:paketit.db");
        Scanner lukija = new Scanner(System.in);

            while (true) {
                System.out.println("Valitse toiminto (1-9): ");
                System.out.println("1-luo tietokanta");                             //luo tietokannan
                System.out.println("2-lisää paikkatieto");                          //lisää tiedon paikasta
                System.out.println("3-lisää asikastieto");                          //lisää tiedon asiakkaasta
                System.out.println("4-lisää pakettitieto");                         //lisää tiedon paketista
                System.out.println("5-lisää tapahtumatieto");                       //lisää tapahtuman
                System.out.println("6-hae paketin reitti");                         //hakee seurantakoodilla sijainnit ja aikaleimat
                System.out.println("7-hae paketit asiakkaan nimellä");              //hakee paketit asiakkaan nimellä
                System.out.println("8-hae tapahtumat paikalla ja päivällä");        //hakee tapahtumien määrän paikalla ja päivällä
                System.out.println("9-suorita tehokkuustesti ilman indeksejä"); //suorittaa tehokkkuustestin
                System.out.println("10-suorita tehokkustesti indekseillä");
                System.out.println("x-lopeta ohjelma");                             //lopeta ohjelma
                System.out.print(">> ");
                String komento = lukija.nextLine();

                if (komento.equals("1")) {
                    luoTietokanta(lukija);
                } else if (komento.equals("2")) {
                    lisaaPaikkatieto(lukija);
                } else if (komento.equals("3")) {
                    lisaaAsiakastieto(lukija);
                } else if (komento.equals("4")) {
                    lisaaPakettitieto(lukija);
                } else if (komento.equals("5")) {
                    lisaaTapahtuma(lukija);
                } else if (komento.equals("6")) {
                    haePaketinTapahtumat(lukija);
                } else if (komento.equals("7")) {
                    haePaketit(lukija);
                } else if (komento.equals("8")) {
                    haePaikanTapahtumat(lukija);
                } else if (komento.equals("9")) {
                    tehokkuustestiIlmanIndekseja();
                } else if (komento.equals("10")) {
                    tehokkuustestiIndekseilla();
                } else if (komento.equals("x")) {
                    break;
                }
            }
    }

    private static void luoTietokanta(Scanner lukija) throws SQLException {
        //Toiminto 1-luo tietokanta
        Connection db = DriverManager.getConnection("jdbc:sqlite:paketit.db");
        Statement s = db.createStatement();

        s.execute("CREATE TABLE Paikat (id INTEGER PRIMARY KEY, nimi TEXT UNIQUE)");
        s.execute("CREATE TABLE Asiakkaat (id INTEGER PRIMARY KEY, nimi TEXT UNIQUE)");
        s.execute("CREATE TABLE Tapahtumat (id INTEGER PRIMARY KEY, kuvaus TEXT, "
                + "paketti_id INTEGER REFERENCES Paketit, paikka_id INTEGER "
                + "REFERENCES Paikat, aika TEXT)");
        s.execute("CREATE TABLE Paketit (id INTEGER PRIMARY KEY, koodi TEXT UNIQUE,"
                + " asiakas_id INTEGER REFERENCES Asiakkaat)");
        System.out.println("Tietokanta luotu");
        System.out.println("");
    }

    private static void lisaaPaikkatieto(Scanner lukija) throws SQLException {
        //Toiminto 2-lisää paikkatieto
        Connection db = DriverManager.getConnection("jdbc:sqlite:paketit.db");

        System.out.print("Anna paikan nimi: ");
        String paikka = lukija.nextLine();

        try {
            PreparedStatement p = db.prepareStatement("INSERT INTO Paikat(nimi) VALUES (?)");
            p.setString(1,paikka);
            p.executeUpdate();
            System.out.println("Paikka lisätty");
            System.out.println("");

        } catch (Exception e) {
            System.out.println("VIRHE: paikka on jo olemassa");
            System.out.println("");
        }
        db.close();

    }

    private static void lisaaAsiakastieto(Scanner lukija) throws SQLException {
        //Toiminto 3-lisää asikastieto
        Connection db = DriverManager.getConnection("jdbc:sqlite:paketit.db");

        System.out.print("Anna asiakkaan nimi: ");
        String asiakas = lukija.nextLine();

        try {

            PreparedStatement p = db.prepareStatement("INSERT INTO Asiakkaat(nimi) VALUES (?)");
            p.setString(1,asiakas);
            p.executeUpdate();
            System.out.println("Asiakas lisätty");
            System.out.println("");

        } catch (Exception e) {
            System.out.println("VIRHE: asiakas on jo olemassa");
            System.out.println("");
        }
        db.close();

    }

    private static void lisaaPakettitieto(Scanner lukija) throws SQLException {
        //Toiminto 4-lisää paketti (paketin seurantakoodi ja asiakas)
        Connection db = DriverManager.getConnection("jdbc:sqlite:paketit.db");
        System.out.print("Anna paketin seurantakoodi: ");
        String koodi = lukija.nextLine();
        System.out.print("Anna asiakkaan nimi: ");
        String asiakas = lukija.nextLine();

        try {

            PreparedStatement p = db.prepareStatement("SELECT id FROM Asiakkaat WHERE nimi=?");
            p.setString(1,asiakas);
            ResultSet r = p.executeQuery();

            if (r.next()) {
                PreparedStatement q = db.prepareStatement("INSERT INTO Paketit(koodi,asiakas_id) VALUES (?,?)");
                q.setString(1,koodi);
                q.setInt(2,r.getInt("id"));
                q.executeUpdate();
                System.out.println("Paketti lisätty");
                System.out.println("");
            } else {
                System.out.println("Asiakasta ei löydy.");
                System.out.println("");
            }

        } catch (Exception e) {
            System.out.println("Tapahtui tietokantavirhe!");
            System.out.println("");
        }
        db.close();
    }

    private static void lisaaTapahtuma(Scanner lukija) throws SQLException {
        //Toiminto 5-lisää tapahtumatieto
        Connection db = DriverManager.getConnection("jdbc:sqlite:paketit.db");

        try {
            System.out.print("Anna paketin seurantakoodi: ");
            String koodi = lukija.nextLine();

            PreparedStatement seurantakoodi = db.prepareStatement("SELECT id FROM Paketit WHERE koodi=?");
            seurantakoodi.setString(1,koodi);
            ResultSet paketinID = seurantakoodi.executeQuery();

            if (paketinID.next()) {
                System.out.print("Anna tapahtuman paikka: ");
                String paikka = lukija.nextLine();

                PreparedStatement mesta = db.prepareStatement("SELECT id FROM Paikat WHERE nimi=?");
                mesta.setString(1,paikka);
                ResultSet paikkaID = mesta.executeQuery();
                if (paikkaID.next()) {
                    System.out.print("Anna tapahtuman kuvaus: ");
                    String kuvaus = lukija.nextLine();

                    PreparedStatement tapahtuma = db.prepareStatement("INSERT INTO Tapahtumat(kuvaus,paketti_id,paikka_id,aika) VALUES (?,?,?,datetime('now'))");
                    tapahtuma.setString(1,kuvaus);
                    tapahtuma.setInt(2,paketinID.getInt("id"));
                    tapahtuma.setInt(3,paikkaID.getInt("id"));

                    tapahtuma.executeUpdate();
                    System.out.println("Tapahtuma lisätty");
                    System.out.println("");
                } else {
                    System.out.println("VIRHE: Paikkaa ei ole olemassa");
                    System.out.println("");
                }
            } else {
                System.out.println("Seurantakoodia ei löydy.");
                System.out.println("");
            }

        } catch (Exception e) {
            System.out.println("Tapahtui tietokantavirhe!"+ e.getMessage());
            System.out.println("");
        }
        db.close();

    }

    private static void haePaketinTapahtumat(Scanner lukija) throws SQLException {
        //Toiminto 6-hae paketin tapahtumat
        Connection db = DriverManager.getConnection("jdbc:sqlite:paketit.db");

        System.out.print("Anna paketin seurantakoodi: ");
        String haluttuPaketti = lukija.nextLine();

        PreparedStatement p = db.prepareStatement("SELECT Tapahtumat.aika, Paikat.nimi, Tapahtumat.kuvaus FROM Tapahtumat, Paikat, Paketit WHERE Paketit.id = Tapahtumat.paketti_id AND Paikat.id = Tapahtumat.paikka_id AND Paketit.koodi = ?");
        p.setString(1, haluttuPaketti);
        ResultSet r = p.executeQuery();

        while (r.next()) {
            System.out.println(r.getString("aika")+", "+r.getString("nimi")+", "+r.getString("kuvaus"));
        }
        System.out.println("");
        db.close();
    }

    private static void haePaketit(Scanner lukija) throws SQLException {
        //Toiminto 7-hae paketit asiakkaan nimellä
        Connection db = DriverManager.getConnection("jdbc:sqlite:paketit.db");

        System.out.print("Anna asiakkaan nimi: ");
        String asiakas = lukija.nextLine();

        PreparedStatement p = db.prepareStatement("SELECT Paketit.koodi, COUNT(Tapahtumat.id) AS maara FROM Paketit LEFT JOIN Tapahtumat ON Paketit.id = Tapahtumat.paketti_id LEFT JOIN Asiakkaat ON Paketit.asiakas_id = Asiakkaat.id WHERE Asiakkaat.nimi = ? GROUP BY Paketit.id");
        p.setString(1, asiakas);
        ResultSet r = p.executeQuery();

        while (r.next()) {
            System.out.println(r.getString("koodi")+", "+r.getInt("maara")+" tapahtumaa");
        }

        System.out.println("");
        db.close();
    }

    private static void haePaikanTapahtumat(Scanner lukija) throws SQLException {
        //Toiminto 8-hae tapahtumat paikalla ja päivällä
        Connection db = DriverManager.getConnection("jdbc:sqlite:paketit.db");

        System.out.print("Anna paikan nimi: ");
        String paikka = lukija.nextLine();
        System.out.print("Anna päivämäärä (YYYY-MM-DD): ");
        String pvm = lukija.nextLine();

        PreparedStatement p = db.prepareStatement("SELECT COUNT(Tapahtumat.id) AS tapahtumia FROM Tapahtumat LEFT JOIN Paikat ON Tapahtumat.paikka_id = Paikat.id WHERE Tapahtumat.aika LIKE ? AND Paikat.nimi=?");
        p.setString(1, pvm+'%');
        p.setString(2, paikka);
        ResultSet r = p.executeQuery();

        System.out.println("Tapahtumien määrä: "+r.getInt("tapahtumia"));
        System.out.println("");
        db.close();
    }

    private static void tehokkuustestiIlmanIndekseja() throws SQLException {
        //LUO TIETOKANTA
        long aika1 = System.nanoTime();

        Connection db = DriverManager.getConnection("jdbc:sqlite:tehokkuustestiIlmanIndeksejaKanta.db");
        Statement s = db.createStatement();

        s.execute("CREATE TABLE Paikat (id INTEGER PRIMARY KEY, nimi TEXT UNIQUE)");
        s.execute("CREATE TABLE Asiakkaat (id INTEGER PRIMARY KEY, asNimi TEXT UNIQUE)");
        s.execute("CREATE TABLE Tapahtumat (id INTEGER PRIMARY KEY, kuvaus TEXT, "
                + "paketti_id INTEGER REFERENCES Paketit, paikka_id INTEGER "
                + "REFERENCES Paikat, aika TEXT)");
        s.execute("CREATE TABLE Paketit (id INTEGER PRIMARY KEY, koodi TEXT UNIQUE,"
                + " asiakas_id INTEGER REFERENCES Asiakkaat)");

        //LISÄÄ TUHAT PAIKKAA P1
        long paikkaAika1 = System.nanoTime();

        String paikka;
        s.execute("BEGIN TRANSACTION");
        PreparedStatement p = db.prepareStatement("INSERT INTO Paikat(nimi) VALUES (?)");
        for (int i=1; i<1001; i++) {
            paikka="P"+i;
            p.setString(1, paikka);
            p.executeUpdate();
            //System.out.println("Paikan nimi: "+paikka);
        }
        long paikkaAika2 = System.nanoTime();
        System.out.println("1000 paikan lisäämiseen kului "+(paikkaAika2-paikkaAika1)/1e9+" sekuntia");

        //LISÄÄ TUHAT ASIAKASTA A1
        long asiakasAika1 = System.nanoTime();

        String asiakas;
        PreparedStatement q = db.prepareStatement("INSERT INTO Asiakkaat(asNimi) VALUES (?)");
        for (int j=1; j<1001; j++) {
            asiakas="A"+j;
            q.setString(1,asiakas);
            q.executeUpdate();
            //System.out.println("Asiakkaan nimi: "+asiakas);
        }
        long asiakasAika2 = System.nanoTime();
        System.out.println("1000 asiakkaan lisäämiseen kului "+(asiakasAika2-asiakasAika1)/1e9+" sekuntia");

        //LISÄÄ TUHAT PAKETTIA, JOKAISELLE ASIAKAS
        long pakettiAika1 = System.nanoTime();

        String paketti;
        for (int k=1; k<1001; k++) {
            paketti = "S"+k;
            PreparedStatement r = db.prepareStatement("INSERT INTO Paketit(koodi,asiakas_id) VALUES (?,?)");
            r.setString(1,paketti);
            r.setInt(2,k);
            r.executeUpdate();
            //System.out.println("Paketin numero: "+paketti);
        }
        long pakettiAika2 = System.nanoTime();
        System.out.println("1000 paketin lisäämiseen kului "+(pakettiAika2-pakettiAika1)/1e9+" sekuntia");

        //LISÄÄ MILJOONA TAPAHTUMA, JOKAISELLE PAKETTI
        long tapahtumaAika1 = System.nanoTime();

        Random random = new Random();
        for (int l=1; l<1000001; l++) {
            PreparedStatement tapahtuma = db.prepareStatement("INSERT INTO Tapahtumat(paketti_id) VALUES (?)");
            int satunnainen = random.nextInt(1000);
            String satunnainenPaketti = "S"+satunnainen;
            tapahtuma.setInt(1, satunnainen);

            tapahtuma.executeUpdate();
            //System.out.println("Tapahtuman paketti: "+satunnainenPaketti);

        }
        s.execute("COMMIT");
        long tapahtumaAika2 = System.nanoTime();
        System.out.println("1 000 000 tapahtuman lisäämisen kului "+(tapahtumaAika2-tapahtumaAika1)/1e9+" sekuntia");

        //KYSELE TUHAT ASIAKKAIDEN PAKETTIMÄÄRISTÄ
        long pakettiKyselyAika1 = System.nanoTime();
        s.execute("BEGIN TRANSACTION");

        for (int m=1; m<1000; m++) {
            String hakuAsiakas = "A"+m;
            PreparedStatement haePaketit = db.prepareStatement("SELECT Asiakkaat.asNimi, COUNT(Paketit.id) AS maara FROM Paketit LEFT JOIN Tapahtumat ON Paketit.id = Tapahtumat.paketti_id LEFT JOIN Asiakkaat ON Paketit.asiakas_id = Asiakkaat.id WHERE Asiakkaat.asNimi = ? GROUP BY Asiakkaat.id");
            p.setString(1, hakuAsiakas);
            //ResultSet tulosAsiakkaat = haePaketit.executeQuery();
            //System.out.println("Haun asiakkaan nimi: "+hakuAsiakas);

        }
        s.execute("COMMIT");
        long pakettiKyselyAika2 = System.nanoTime();
        System.out.println("1000 asiakkaan pakettien kyselyyn kului "+(pakettiKyselyAika2-pakettiKyselyAika1)/1e9+" sekuntia");

        //KYSELE TUHAT KERTAA JONKUN PAKETIN TAPAHTUMIA
        long tapahtumaKyselyAika1 = System.nanoTime();
        s.execute("BEGIN TRANSACTION");

        for (int m=1; m<1000; m++) {
            String hakuPaketti = "P"+m;
            PreparedStatement haeTapahtumat = db.prepareStatement("SELECT Paketit.koodi, COUNT(Tapahtumat.id) AS maara FROM Paketit LEFT JOIN Tapahtumat ON Paketit.id = Tapahtumat.paketti_id WHERE Paketit.koodi = ? GROUP BY Paketit.id");
            p.setString(1, hakuPaketti);
            //ResultSet tulosPaketit = haeTapahtumat.executeQuery();
            //System.out.println("Haun paketti: "+hakuPaketti);

        }
        s.execute("COMMIT");
        long tapahtumaKyselyAika2 = System.nanoTime();
        System.out.println("1000 paketin tapahtumien kyselyyn kului "+(tapahtumaKyselyAika2-tapahtumaKyselyAika1)/1e9+" sekuntia");

        System.out.println("");

        long aika2 = System.nanoTime();
        System.out.println("Aikaa kului "+(aika2-aika1)/1e9+" sekuntia");

    }

    private static void tehokkuustestiIndekseilla() throws SQLException {
             //LUO TIETOKANTA
        long aika1 = System.nanoTime();

        Connection db = DriverManager.getConnection("jdbc:sqlite:tehokkuustestiIndekseillaKanta.db");
        Statement s = db.createStatement();

        s.execute("CREATE TABLE Paikat (id INTEGER PRIMARY KEY, nimi TEXT UNIQUE)");
        s.execute("CREATE TABLE Asiakkaat (id INTEGER PRIMARY KEY, asNimi TEXT UNIQUE)");
        s.execute("CREATE TABLE Tapahtumat (id INTEGER PRIMARY KEY, kuvaus TEXT, "
                + "paketti_id INTEGER REFERENCES Paketit, paikka_id INTEGER "
                + "REFERENCES Paikat, aika TEXT)");
        s.execute("CREATE TABLE Paketit (id INTEGER PRIMARY KEY, koodi TEXT UNIQUE,"
                + " asiakas_id INTEGER REFERENCES Asiakkaat)");
        s.execute("CREATE INDEX idx_paiknimi ON Paikat (nimi)");
        s.execute("CREATE INDEX idx_asnimi ON Asiakkaat (asNimi)");
        s.execute("CREATE INDEX idx_koodi ON Paketit (koodi)");
        s.execute("CREATE INDEX idx_tapahtuma ON Tapahtumat (paketti_id)");

        //LISÄÄ TUHAT PAIKKAA P1
        long paikkaAika1 = System.nanoTime();

        String paikka;
        s.execute("BEGIN TRANSACTION");
        PreparedStatement p = db.prepareStatement("INSERT INTO Paikat(nimi) VALUES (?)");
        for (int i=1; i<1001; i++) {
            paikka="P"+i;
            p.setString(1, paikka);
            p.executeUpdate();
            //System.out.println("Paikan nimi: "+paikka);
        }
        s.execute("COMMIT");
        long paikkaAika2 = System.nanoTime();
        System.out.println("1000 paikan lisäämiseen kului "+(paikkaAika2-paikkaAika1)/1e9+" sekuntia");

        //LISÄÄ TUHAT ASIAKASTA A1
        long asiakasAika1 = System.nanoTime();

        s.execute("BEGIN TRANSACTION");
        String asiakas;
        PreparedStatement q = db.prepareStatement("INSERT INTO Asiakkaat(asNimi) VALUES (?)");
        for (int j=1; j<1001; j++) {
            asiakas="A"+j;
            q.setString(1,asiakas);
            q.executeUpdate();
            //System.out.println("Asiakkaan nimi: "+asiakas);

        }
        s.execute("COMMIT");
        long asiakasAika2 = System.nanoTime();
        System.out.println("1000 asiakkaan lisäämiseen kului "+(asiakasAika2-asiakasAika1)/1e9+" sekuntia");

        //LISÄÄ TUHAT PAKETTIA, JOKAISELLE ASIAKAS
        long pakettiAika1 = System.nanoTime();

        s.execute("BEGIN TRANSACTION");
        String paketti;
        for (int k=1; k<1001; k++) {
            paketti = "S"+k;
            PreparedStatement r = db.prepareStatement("INSERT INTO Paketit(koodi,asiakas_id) VALUES (?,?)");
            r.setString(1,paketti);
            r.setInt(2,k);
            r.executeUpdate();
            //System.out.println("Paketin numero: "+paketti);

        }
        s.execute("COMMIT");
        long pakettiAika2 = System.nanoTime();
        System.out.println("1000 paketin lisäämiseen kului "+(pakettiAika2-pakettiAika1)/1e9+" sekuntia");

        //LISÄÄ MILJOONA TAPAHTUMA, JOKAISELLE PAKETTI
        long tapahtumaAika1 = System.nanoTime();

        s.execute("BEGIN TRANSACTION");
        Random random = new Random();
        for (int l=1; l<1000001; l++) {
            PreparedStatement tapahtuma = db.prepareStatement("INSERT INTO Tapahtumat(paketti_id) VALUES (?)");
            int satunnainen = random.nextInt(1000);
            String satunnainenPaketti = "S"+satunnainen;
            tapahtuma.setInt(1, satunnainen);

            tapahtuma.executeUpdate();
            //System.out.println("Tapahtuman paketti: "+satunnainenPaketti);

        }
        s.execute("COMMIT");
        long tapahtumaAika2 = System.nanoTime();
        System.out.println("1 000 000 tapahtuman lisäämisen kului "+(tapahtumaAika2-tapahtumaAika1)/1e9+" sekuntia");

        //KYSELE TUHAT ASIAKKAIDEN PAKETTIMÄÄRISTÄ
        long pakettiKyselyAika1 = System.nanoTime();
        s.execute("BEGIN TRANSACTION");

        for (int m=1; m<1000; m++) {
            String hakuAsiakas = "A"+m;
            PreparedStatement haePaketit = db.prepareStatement("SELECT Asiakkaat.asNimi, COUNT(Paketit.id) AS maara FROM Paketit LEFT JOIN Tapahtumat ON Paketit.id = Tapahtumat.paketti_id LEFT JOIN Asiakkaat ON Paketit.asiakas_id = Asiakkaat.id WHERE Asiakkaat.asNimi = ? GROUP BY Asiakkaat.id");
            p.setString(1, hakuAsiakas);
            //ResultSet tulosAsiakkaat = haePaketit.executeQuery();
            //System.out.println("Haun asiakkaan nimi: "+hakuAsiakas);

        }
        s.execute("COMMIT");
        long pakettiKyselyAika2 = System.nanoTime();
        System.out.println("1000 asiakkaan pakettien kyselyyn kului "+(pakettiKyselyAika2-pakettiKyselyAika1)/1e9+" sekuntia");

        //KYSELE TUHAT KERTAA JONKUN PAKETIN TAPAHTUMIA
        long tapahtumaKyselyAika1 = System.nanoTime();
        s.execute("BEGIN TRANSACTION");

        for (int m=1; m<1000; m++) {
            String hakuPaketti = "P"+m;
            PreparedStatement haeTapahtumat = db.prepareStatement("SELECT Paketit.koodi, COUNT(Tapahtumat.id) AS maara FROM Paketit LEFT JOIN Tapahtumat ON Paketit.id = Tapahtumat.paketti_id WHERE Paketit.koodi = ? GROUP BY Paketit.id");
            p.setString(1, hakuPaketti);
            //ResultSet tulosPaketit = haeTapahtumat.executeQuery();
            //System.out.println("Haun paketti: "+hakuPaketti);

        }
        s.execute("COMMIT");
        long tapahtumaKyselyAika2 = System.nanoTime();
        System.out.println("1000 paketin tapahtumien kyselyyn kului "+(tapahtumaKyselyAika2-tapahtumaKyselyAika1)/1e9+" sekuntia");

        System.out.println("");

        long aika2 = System.nanoTime();
        System.out.println("Aikaa kului "+(aika2-aika1)/1e9+" sekuntia");

    }

}