/*
 *
 */

import java.sql.*;
import java.util.Properties;
import java.util.Scanner;

public class my_DB
{
    private Scanner reader;

    public static void main (String[]   args) throws Exception{
        new my_DB();
    }

    public my_DB() throws Exception{

    reader = new Scanner(System.in);

    Class.forName ("org.postgresql.Driver");
    Properties settings = new Properties ();
    settings.setProperty ("user", System.getProperty ("user.name"));
    String url = "jdbc:postgresql://localhost/jacobry";
    Connection db = DriverManager.getConnection (url, settings);

    while(true){
    System.out.println("Find person (1), Insert person(2), Delete person (3),DBinfo(4), Exit(5)");
    System.out.print(">");

    String inputLine = reader.nextLine();
    switch(Integer.parseInt(inputLine)){
        case 1:
            System.out.print("Name: ");

            String query = ("select namn, lön "  +
            "from anställd  "    +
            "where namn like ? " +
            "order by lön desc");

            PreparedStatement ps = db.prepareStatement (query);
            ps.setString (1, "%" + reader.nextLine() + "%");
            ResultSet rs = ps.executeQuery ();

            while (rs.next ())
            {
                String name = rs.getString (1);
                System.out.print (name);
                String space = "";
                for (int i = 0; i < 20 - name.length (); ++i)
                space  = space + " ";
                System.out.println (space + rs.getString (2));
            }

            rs.close();
            ps.close();
            break;
        case 2:
            System.out.print("Insert(name sallory): ");
            String inLine[] = reader.nextLine().split(" ");
            String insert = "INSERT INTO anställd (namn,lön) VALUES('"+inLine[0]+"',"+inLine[1]+")";
            PreparedStatement insertStmt = db.prepareStatement (insert);
            insertStmt.executeUpdate();
            break;
        case 3:
            System.out.print("Delete name: ");
            String delete = "DELETE FROM anställd where namn = '" + reader.nextLine()+"'";
            PreparedStatement deleteStmt = db.prepareStatement(delete);
            deleteStmt.executeUpdate();
            break;
        case 4:
            String antal = "select count(distinct namn) from anställd";
            PreparedStatement antalStmt = db.prepareStatement(antal);
            System.out.println("Nr employees: " + antalStmt.executeUpdate());

            String max = "select max(lön) from anställd";
            PreparedStatement maxStmt = db.prepareStatement(max);
            System.out.println("Highest Sallory: " + maxStmt.executeUpdate());

            String min = "select min(lön) from anställd";
            PreparedStatement minStmt = db.prepareStatement(min);
            System.out.println("Lowest Sallory: " + minStmt.executeUpdate());

            String avg = "select avg(lön) from anställd";
            PreparedStatement avgStmt = db.prepareStatement(avg);
            System.out.println("Average Sallory: " + avgStmt.executeUpdate());
            break;
        default:System.exit (1);
       }
    }
  }
}