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

public class kund
{
    private Scanner reader;

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

    public kund() 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/film";
    Connection db = DriverManager.getConnection (url, settings);

    while(true){
        System.out.println("Välkommen till Philips-filmbutik!");
        System.out.println("Visa Film-Info(1), Sök film efter skådis(2), Visa Utgångsdatum för medlemskort(3), Avsluta(4)");
        System.out.print(">");

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

            String query = ("select * from Filminfo where titel = "+tmp);

            PreparedStatement ps = db.prepareStatement (query);
            ResultSet rs = ps.executeQuery ();
            rs.next();

            System.out.println("Title: "+rs.getString (1));
            System.out.println("Regissör: "+rs.getString (2));
            System.out.println("Distributör: "+rs.getString (3));
            System.out.println("Filmbolag: "+rs.getString (4));
            System.out.println("Skådespelare: ");

            String ska = ("select sNamn from Skådis where titel = "+tmp);
            PreparedStatement skadisPre = db.prepareStatement (ska);
            ResultSet skadisSet = skadisPre.executeQuery ();

            while (skadisSet.next ())
            {
                String name = skadisSet.getString (1);
                System.out.print (name + ", ");
            }
            System.out.println();

            skadisPre.close();
            skadisSet.close();
            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());
            break;
        default:System.exit (1);
       }
    }
  }
}