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


    System.out.println("Välkommen till Philips-filmbutik!");
    while(true){
        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();
            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:
                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();
                break;
            case 3:
                System.out.print("PersonNr: ");
                String pnr = ("Select medlemsNr,giltigTid from Kund natural join 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));
                break;
        default:System.exit (1);
       }
    }
  }
}