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

public class Anställd
{
    private Scanner reader;

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

    public Anställd() 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/robhed";
    Connection db = DriverManager.getConnection (url, settings);


    System.out.println("Välkommen till Philips-filmbutik!");
    while(true){
        System.out.println("Visa Film-Info(1) \nSök film efter skådis(2) \nVisa Utgångsdatum för medlemskort(3) \nLägg till Kund(4) \nLägg till Medlem(5) \nTa bort Medlem(6) \nLägg till Film(7) \nAvsluta(8)");
        System.out.print(">");

        String inputLine = reader.nextLine();
        switch(Integer.parseInt(inputLine)){
            case 1:
                System.out.print("Film titel: ");
                String tmp = reader.nextLine();
            try{
                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.print("Skådespelare: ");

                String agerar = ("select sNamn from agerar where titel = '"+tmp+"'");
                PreparedStatement agerarPre = db.prepareStatement (agerar);
                ResultSet agerarSet = agerarPre.executeQuery ();

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

                agerarPre.close();
                agerarSet.close();
                rs.close();
                ps.close();
            }catch(Exception e){
                System.out.println("Filmen finns inte");
            }
                break;
            case 2:
            try{
                System.out.print("Skådis: ");

                String agerar2 = ("select titel from agerar where sNamn = '"+reader.nextLine()+"'");
                PreparedStatement agerarPre2 = db.prepareStatement (agerar2);
                ResultSet agerarSet2 = agerarPre2.executeQuery ();

                System.out.println("Filmer: ");

                while (agerarSet2.next ())
                {
                    System.out.println(agerarSet2.getString(1));
                }
                System.out.println();
            }catch(Exception e){
                System.out.println("Hittade inte den skådespelaren");
            }
                break;
            case 3:
            try{
                System.out.print("PersonNr: ");
                String pnr = ("Select medlemsNr,giltigTid from Medlem where pNr = '" + reader.nextLine()+"'");

                PreparedStatement agerarPre3 = db.prepareStatement (pnr);
                ResultSet agerarSet3 = agerarPre3.executeQuery ();

                System.out.print("Ditt medlemsNr ");

                agerarSet3.next ();

                String name = agerarSet3.getString (1);
                System.out.print(agerarSet3.getString (1) + " ");
                System.out.print("går ut " + agerarSet3.getString (2));
            }catch(Exception e){
                System.out.println("Hitta inte den personen");
            }
                break;
            case 4:
                System.out.print("PersonNr,Namn,Tel,Adr: ");
                String kInsert[] = reader.nextLine().split(", ");
                String kIns = ("Insert into Kund values ('"+kInsert[1]+"','"+ kInsert[2] +"','" + kInsert[3] + "','" +kInsert[4]+"');");

                PreparedStatement ins = db.prepareStatement (kIns);
                ins.executeUpdate();
                break;
            case 5:
                System.out.print("MedlemsNr,PersonNr,giltigTid(dd/mm-åå: ");
                String mInsert[] = reader.nextLine().split(", ");
                String mIns = ("Insert into Kund values ('"+mInsert[1]+"','"+ mInsert[2] +"','" + mInsert[3] + "');");

                PreparedStatement mins = db.prepareStatement (mIns);
                mins.executeUpdate();
                break;
            case 6:
                System.out.print("Ta bort medlem(personNr): ");
                String mDel = ("delete from medlem where pnr = '"+reader.nextLine()+"';");
                PreparedStatement mDe = db.prepareStatement (mDel);
                mDe.executeUpdate();
                break;
            case 7:
                System.out.print("Ta bort Film(Titel): ");
                String fDel = ("delete from filminfo where titel = '"+reader.nextLine()+"';");
                PreparedStatement fDe = db.prepareStatement (fDel);
                fDe.executeUpdate();
            break;
        default:System.exit (1);
       }
    }
  }
}