import javax.management.Query;
import java.sql.*;
import java.util.LinkedList;
import java.util.Scanner;

public class Main {

    static Connection conn = SQLOperations.getConnection();

    static String[] menu = {
            "------------ MENIU -------------",
            "0. Iseiti",
            "1. Prideti darbuotoja",
            "2. Perziureti darbuotojus",
            "3. Pakeisti darbuotojo informacija",
            "4. Panaikinti darbuotoja",
            "5. Perziureti informacija apie keliones"
    };

    public static void main(String[] args) {

        int next;
        Scanner input = new Scanner(System.in);

        SQLOperations.loadDriver();

        for(String s : menu){
            System.out.println(s);
        }
        System.out.println("Iveskite pasirinkima:");
        next = input.nextInt();
        input.nextLine();
        while(true){
            switch(next){
                case 0:
                    SQLOperations.closeConnection(conn);
                    System.exit(1);
                    break;
                case 1:
                    System.out.println("Iveskite pardavejo AK, Varda, Pavarde ir Tel. Nr.");
                    System.out.println("AK:");
                    String AK = input.nextLine();
                    System.out.println("Vardas:");
                    String vardas = input.nextLine();
                    System.out.println("Pavarde:");
                    String pavarde = input.nextLine();
                    System.out.println("Telefono Nr.:");
                    String telnr = input.nextLine();
                    try {
                        SQLOperations.insertToDB(conn, "INSERT INTO Darbuotojas VALUES('" + AK + "','" + vardas + "','" + pavarde + "','" + telnr
                                + "')");
                        System.out.println("Prideta!");
                    }catch(SQLException e){
                        System.out.println("Prideti nepavyko!");
                        System.out.println(e.toString());
                    }
                    break;
                case 2:
                    displayList("SELECT * FROM Darbuotojas");
                    break;
                case 3:
                    displayList("SELECT * FROM Darbuotojas");
                    System.out.println("Iveskite AK darbuotojo, kuri norite modifikuoti");
                    String ak = input.nextLine();
                    System.out.println("Pasirinkite ka norite keisti: 1 - AK, 2 - Varda, 3 - Pavarde, 4 - Tel. Nr.");
                    int read = input.nextInt();
                    System.out.println("Iveskite nauja duomeni");
                    String newData = input.nextLine();
                    String target = "";
                    switch(read){
                        case 1:
                            target = "AK";
                            break;
                        case 2:
                            target = "Vardas";
                            break;
                        case 3:
                            target = "Pavarde";
                            break;
                        case 4:
                            target = "TelNr";
                            break;
                        default:
                            System.out.println("Blogas skaicius, operacija nutraukiama");
                            continue;
                    }
                    try{
                        SQLOperations.insertToDB(conn,"UPDATE Darbuotojas SET " + target + " = '"+newData+"' WHERE AK = '" + ak + "'");
                    }catch(SQLException e){
                        System.out.println(e.toString());
                    }
                    break;
                case 4:
                    displayList("SELECT * FROM Darbuotojas");
                    System.out.println("Pasirinkite AK darbuotojo, kuri norite pasalinti");
                    System.out.println("AK: ");
                    String darbAK = input.nextLine();
                    try{
                        SQLOperations.insertToDB(conn, "DELETE FROM Darbuotojas WHERE AK = '" + darbAK + "'");
                    }catch(SQLException e){
                        System.out.println(e.toString());
                    }
                    break;
                case 5:
                    displayList("SELECT * FROM Pirkejas");
                    break;
                default:
                    System.out.println("Ivedete bloga skaiciu!");
            }
            System.out.println("Iveskite kita pasirinkima");
            next = input.nextInt();
            input.nextLine();
        }

    }

    private static void displayList(String query){
        LinkedList<LinkedList<String>> res = SQLOperations.getFromDB(conn, query);
        for(LinkedList<String> list : res){
            if(list.size() == 4) {
                    System.out.println("AK: " + list.get(0).trim());
                    System.out.println("Vardas: " + list.get(1).trim());
                    System.out.println("Pavarde: " + list.get(2).trim());
                    System.out.println("Tel nr: " + list.get(3).trim());

            }else{
                    System.out.println("ID: " + list.get(0).trim());
                    System.out.println("Darbuotojo AK: " + list.get(1).trim());
                    System.out.println("AK: " + list.get(2).trim());
                    System.out.println("Vardas: " + list.get(3).trim());
                    System.out.println("Pavarde: " + list.get(4).trim());
                    System.out.println("Tel nr: " + list.get(5).trim());
                    System.out.println("Adresas: " + list.get(6).trim());

            }
        }
    }
}

class SQLOperations{
    public static void insertToDB(Connection conn, String query) throws SQLException{
        Statement stmt = null;

        try{
            stmt = conn.createStatement();
            stmt.execute(query);
        }catch(SQLException e){
            throw e;
        }finally {
            try {
                if(stmt != null) stmt.close();
            } catch (SQLException e) {
                System.out.println("Netiketa SQL klaida!");
            }
        }
    }

    public static LinkedList<LinkedList<String>> getFromDB(Connection conn, String query){
        LinkedList<LinkedList<String>> result = new LinkedList<>();
        Statement stmt = null;
        ResultSet rslt = null;

        try{
            stmt = conn.createStatement();
            rslt = stmt.executeQuery(query);

            while(rslt.next()){
                LinkedList<String> row = new LinkedList<>();
                for(int i = 1; i<= rslt.getMetaData().getColumnCount();i++){
                    String tmp = rslt.getString(i);
                    if(tmp.equals(null)) tmp = "";
                    row.add(tmp);
                }
                result.add(row);
            }
        }catch(SQLException e){
            System.out.println(e.toString());
        }finally{
            try{
                if(rslt != null) rslt.close();
                if(stmt != null) stmt.close();
            }catch(SQLException e){
                System.out.println(e.toString());
            }
        }

        return result;
    }




    public static void loadDriver(){
        try {
            Class.forName("org.postgresql.Driver");
        }
        catch (ClassNotFoundException cnfe) {
            System.out.println("Couldn't find driver class!");
            cnfe.printStackTrace();
            System.exit(1);
        }
    }

    public static Connection getConnection() {
        Connection postGresConn = null;
        try {
            postGresConn = DriverManager.getConnection("jdbc:postgresql://pgsql2.mif/studentu", "maju3327", "maju3327") ;
        }
        catch (SQLException sqle) {
            System.out.println("Nepavyko prisijungti prie DB!");
            sqle.printStackTrace();
            return null ;
        }

        return postGresConn ;
    }

    public static void closeConnection(Connection conn){
        try{
            conn.close();
        }catch (SQLException exp){
            System.out.println("Nepavyko isjungti rysio!");
            exp.printStackTrace();
        }
    }
}