import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Scanner;

public class TestDB {
    public static void main(String[] args) throws SQLException, IOException {

        Scanner scanner = new Scanner(System.in);

        Connection connection = null;
        ArrayList<String> re = null;
        ResultSet Rs = null;
        PreparedStatement stmt = null;

        try {
            Class.forName("org.postgresql.Driver");
            connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres", "postgres", "admin");
        } catch(Exception ex) {
            System.out.println(ex.getMessage());
        }

        Integer command;
        Integer id = 133;

        do {
            // Wyswietlanie menu wyboru
            System.out.println("---- MENU ----\n");
            System.out.println("1. Wyswietl wybrana tabele");
            System.out.println("2. Wsywietl widok INFO_PRACOWNICY");
            System.out.println("3. Dodaj pracownika");
            System.out.println("4. Usun pracownika");
            System.out.println("5. Wyjdz");
            System.out.print("Wprowadz numer operacji: ");
            command = scanner.nextInt();
            // Przyjmowanie komendy od uzytkownika i wykonywanie odpowiedniej akcji
            switch(command) {
                case 1: //Wyswietla wybrana tabele
                    Integer command2 = null;
                    System.out.println("Wybierz tabele do wyswietlenia");
                    System.out.println("1. Klienci");
                    System.out.println("2. Pracownicy");
                    System.out.println("3. Terminarz");
                    System.out.println("4. Transport");
                    System.out.println("5. Oddzialy");
                    System.out.println("6. Cofnij");
                    System.out.print("Wprowadz numer operacji: ");
                    command2 = scanner.nextInt();
                    switch(command2) {
                        case 1:
                            stmt = connection.prepareStatement("select * from klienci");
                            Rs = stmt.executeQuery();
                            System.out.println("KLIENCI");
                            while(Rs.next()) {
                                System.out.println(Rs.getString(1) + " " + Rs.getString(2) + " " + Rs.getString(3) + " " + Rs.getString(4) + " " + Rs.getString(5) + " " + Rs.getString(6) + " " + Rs.getString(7));
                            }
                            System.out.println("");
                            System.in.read();
                            break;
                        case 2:
                            stmt = connection.prepareStatement("select * from pracownicy");
                            Rs = stmt.executeQuery();
                            System.out.println("PRACOWNICY");
                            while(Rs.next()) {
                                System.out.println(Rs.getString(1) + " " + Rs.getString(2) + " " + Rs.getString(3) + " " + Rs.getString(4) + " " + Rs.getString(5) + " " + Rs.getString(6));
                            }
                            System.out.println("");
                            System.in.read();
                            break;
                        case 3:
                            stmt = connection.prepareStatement("select * from terminarz");
                            Rs = stmt.executeQuery();
                            System.out.println("TERMINARZ");
                            while(Rs.next()) {
                                System.out.println(Rs.getString(1) + " " + Rs.getString(2) + " " + Rs.getString(3) + " " + Rs.getString(4) + " " + Rs.getString(5) + " " + Rs.getString(6));
                            }
                            System.out.println("");
                            System.in.read();
                            break;
                        case 4:
                            stmt = connection.prepareStatement("select * from transport");
                            Rs = stmt.executeQuery();
                            System.out.println("TRANSPORT");
                            while(Rs.next()) {
                                System.out.println(Rs.getString(1) + " " + Rs.getString(2) + " " + Rs.getString(3));
                            }
                            System.out.println("");
                            System.in.read();
                            break;
                        case 5:
                            stmt = connection.prepareStatement("select * from oddzialy");
                            Rs = stmt.executeQuery();
                            System.out.println("ODDZIALY");
                            while(Rs.next()) {
                                System.out.println(Rs.getString(1) + " " + Rs.getString(2));
                            }
                            System.out.println("");
                            System.in.read();
                            break;
                        case 6:
                            break;
                    }
                    break;

                case 2: // Wyswietla widok
                    stmt = connection.prepareStatement("Select * from INFO_PRACOWNICY");
                    System.out.println("INFO_PRACOWNICY");
                    Rs = stmt.executeQuery();
                    while(Rs.next()) {
                        System.out.println(Rs.getString(1) + " " + Rs.getString(2) + " " + Rs.getString(3) + " " + Rs.getString(4) + " " + Rs.getString(5) + " " + Rs.getString(6));
                    }
                    System.out.println("");
                    System.in.read();
                    break;

                case 3: //Dodaje wiersz do tabeli pracownicy oraz dane_osobowe_pracownicy
                    String imie, nazwisko, kontakt, stanowisko, data_urodzenia;
                    int pesel, pensja, id_oddzial, kierownik_oddzialu, id2;
                    Scanner terminalInput = new Scanner(System.in);
                    System.out.println("Podaj imie:");
                    imie = terminalInput.nextLine();
                    System.out.println("Podaj nazwisko:");
                    nazwisko = terminalInput.nextLine();
                    System.out.println("Podaj date urodzenia (rrrr-mm-dd):");
                    data_urodzenia = terminalInput.nextLine();
                    System.out.println("Podaj numer telefonu:");
                    kontakt = terminalInput.nextLine();
                    System.out.println("Podaj stanowisko:");
                    stanowisko = terminalInput.nextLine();
                    System.out.println("Podaj pensje:");
                    pensja = terminalInput.nextInt();
                    System.out.println("Podaj id oddzialu (1-7):");
                    id_oddzial = terminalInput.nextInt();
                    System.out.println("Podaj id kierownika:");
                    kierownik_oddzialu = terminalInput.nextInt();
                    System.out.println("Podaj pesel:");
                    pesel = terminalInput.nextInt();
                    System.out.println("Podaj id:");
                    id2 = terminalInput.nextInt();
                    stmt = connection.prepareStatement("select add_pracownicy(?, ?, CAST(? AS DATE), ?, ?, ?, ?, ?, ?, ?)");
                    stmt.setString(1, imie);
                    stmt.setString(2, nazwisko);
                    stmt.setString(3, data_urodzenia);
                    stmt.setInt(4, pesel);
                    stmt.setString(5, kontakt);
                    stmt.setInt(6, id_oddzial);
                    stmt.setInt(7, kierownik_oddzialu);
                    stmt.setString(8, stanowisko);
                    stmt.setInt(9, pensja);
                    stmt.setInt(10, id2);
                    stmt.executeQuery();
                    System.out.println("Pracownik zostal dodany");
                    PreparedStatement stmt2 = connection.prepareStatement("select * from pracownicy");
                    Rs = stmt2.executeQuery();
                    while(Rs.next()) {
                        System.out.println(Rs.getString(1) + " " + Rs.getString(2) + " " + Rs.getString(3) + " " + Rs.getString(4) + " " + Rs.getString(5) + " " + Rs.getString(6));
                    }
                    System.in.read();
                    break;

                case 4: //usuwa pracownika o podanym id
                    int id3;
                    Scanner terminalInput2 = new Scanner(System.in);
                    stmt = connection.prepareStatement("select delete_pracownicy(?)");
                    id3 = terminalInput2.nextInt();
                    stmt.setInt(1, id3);
                    Rs = stmt.executeQuery();
                    break;

                case 5: // Wyjscie z aplikacji
                    break;


            }


        }while(!command.equals(5));



        try {
            connection.close();
        } catch (Exception e) {
            System.err.println("The connection can not be closed.");
            e.printStackTrace();
        }

    }
}