package DatabaseAssignment2;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.Scanner;
import oracle.jdbc.*;
import oracle.net.aso.s;
import oracle.xdb.XMLType;


public class DatabaseAssignment2 {

    private static String username, password, hostname, serviceName;
    private static Connection conn;
    private static Scanner userInput;

    public static void main(String[] args) throws SQLException {
        // TODO code application logic here

        loadOracleJdbcDriver();
        connectToServer(username, password, hostname, serviceName);
        //Statement s = conn.createStatement();

       }

    public static void loadOracleJdbcDriver() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            System.out.println("Error -" + e.toString());
            System.out.println("Could not load the driver");
        }

    }


    public static void connectToServer(String username, String password,
            String hostname, String serviceName) throws SQLException {

        try {
            Scanner sc = new Scanner(System.in);
            System.out.println("Type userid, password, hostname, servicename: ");
            username = sc.next();
            password = sc.next();
            hostname = sc.next();
            serviceName = "orcl";
            System.out.println(username + " " + password + " " + hostname);

            System.out.println("Connecting to Database...");

            //  userid, password and hostname are obtained from the console
             //try-with-resources
             try(
                    Connection conn = DriverManager.getConnection("jdbc"
                            + ":oracle:thin:"
                            + username + "/" + password + "@" + hostname + ":1521/"
                            + serviceName)){


            createFilmTable(conn);
            populateFilmTable(conn);
            showTitleOfAllFilms(conn);
            showTitleOfAllFilmWithoutTitleTags(conn);
            showNamesOfActorsWhoAppearedInGodfather(conn);
            showTitleAndYearOfAllCrimeFilms(conn);
            showTitleAndYearOfAllFilmsMarlonBrando(conn);

            conn.setAutoCommit(false);

            }

        } catch (SQLException e) {
            System.out.println("Error + " + e.toString());
            System.out.println("Connection Failure");
        }


    }

 public static void createFilmTable(Connection conn) throws SQLException {

     Statement s = conn.createStatement();


            s.executeUpdate("DROP TABLE ASS2_FILM");
            System.out.println("Table Dropped");
            s.executeUpdate("CREATE TABLE ASS2_FILM(fnum VARCHAR(20) " +
                           "CONSTRAINT fnum_PK PRIMARY KEY, " +
                           " film SYS.XMLTYPE)");

            System.out.println("Made");

 }

    public static void populateFilmTable(Connection conn)throws SQLException {

        Statement s = conn.createStatement();



        try{

    FileInputStream fis = new FileInputStream("src/DatabaseAssignment2/film.xml");
    XMLType xmlv = new XMLType(conn, fis);
    System.out.println(xmlv.getStringVal());
    //String sqltxt = "INSERT INTO ASS2_FILM VALUES (?, ?)";

    s.executeUpdate("INSERT INTO ASS2_FILM VALUES( '01', " +
            "sys.XMLType.createXML( ' " + xmlv.getStringVal() + " ' ))");

    FileInputStream fis2 = new FileInputStream("src/DatabaseAssignment2/film2.xml");
    XMLType xmlv2 = new XMLType(conn, fis2);
    System.out.println(xmlv2.getStringVal());
    //String sqltxt = "INSERT INTO ASS2_FILM VALUES (?, ?)";

    s.executeUpdate("INSERT INTO ASS2_FILM VALUES( '02', " +
            "sys.XMLType.createXML( ' " + xmlv2.getStringVal() + " ' ))");

     FileInputStream fis3 = new FileInputStream("src/DatabaseAssignment2/film3.xml");
    XMLType xmlv3 = new XMLType(conn, fis3);
    System.out.println(xmlv.getStringVal());
    //String sqltxt = "INSERT INTO ASS2_FILM VALUES (?, ?)";

    s.executeUpdate("INSERT INTO ASS2_FILM VALUES( '03', " +
            "sys.XMLType.createXML( ' " + xmlv3.getStringVal() + " ' ))");

    FileInputStream fis4 = new FileInputStream("src/DatabaseAssignment2/film4.xml");
    XMLType xmlv4 = new XMLType(conn, fis4);
    System.out.println(xmlv.getStringVal());
    //String sqltxt = "INSERT INTO ASS2_FILM VALUES (?, ?)";

    s.executeUpdate("INSERT INTO ASS2_FILM VALUES( '04', " +
            "sys.XMLType.createXML( ' " + xmlv4.getStringVal() + " ' ))");

    System.out.println("Rows added to table----------------------------------------");


        }
        catch(FileNotFoundException ex){
            System.out.println("Error + " + ex.toString());
            System.out.println("Connection Failure");
        }

    }


    public static void showTitleOfAllFilms(Connection conn) throws SQLException {

        Statement s = conn.createStatement();

        ResultSet result = s.executeQuery("SELECT a.fnum, a.film.extract('/film/title').getStringval() "
        + "FROM ASS2_FILM a " );

        System.out.println("\n Fnum and /Film: \n");

                 while(result.next()){
      System.out.println(result.getString(1) + "  " + result.getString(2) );

    }
  }



    public static void showTitleOfAllFilmWithoutTitleTags(Connection conn) throws SQLException {

                Statement s = conn.createStatement();

        ResultSet result = s.executeQuery("SELECT a.fnum, a.film.extract('film//title/text()').getStringval() "
        + "FROM ASS2_FILM a " );

        System.out.println("\n Fnum and /Title: \n");

                 while(result.next()){
      System.out.println(result.getString(1) + "  " + result.getString(2) );

    }

    }


    public static void showNamesOfActorsWhoAppearedInGodfather(Connection conn) throws SQLException {

        Statement s = conn.createStatement();

        ResultSet result = s.executeQuery("SELECT a.fnum, a.film.extract('film//actor/text()').getStringval() "
        + "FROM ASS2_FILM a "
        + "WHERE a.film.extract('film//title/text()').getStringval() = 'The Godfather'");

        System.out.println("\n Fnum and /Actors: \n");

         while(result.next()){
      System.out.println(result.getString(1) + "  " + result.getString(2) );

       }

    }


    public static void showTitleAndYearOfAllCrimeFilms(Connection conn) throws SQLException {

        Statement s = conn.createStatement();

        ResultSet result = s.executeQuery("SELECT a.fnum, a.film.extract('/film/title/@year').getStringval() "
        + "FROM ASS2_FILM a "
        + "WHERE a.film.extract('/film//genres/genre[1]/text()').getStringval() = 'Crime'");

        System.out.println("\n Fnum and /Film: \n");

         while(result.next()){
      System.out.println(result.getString(1) + "  " + result.getString(2));

       }
    }


    public static void showTitleAndYearOfAllFilmsMarlonBrando(Connection conn) throws SQLException {
                Statement s = conn.createStatement();

        ResultSet result = s.executeQuery("SELECT a.fnum, a.film.extract('/film/title/@year').getStringval() "
        + "FROM ASS2_FILM a "
        + "WHERE a.film.extract('/film//genres/genre[1]/text()').getStringval() = 'Crime'");

        System.out.println("\n Fnum and /Film: \n");

         while(result.next()){
      System.out.println(result.getString(1) + "  " + result.getString(2));

       }
    }


    public static void showOneDirectorFilms() {

    }


    public static void showTitleAndNamesMoreThanOneDirector() {

    }


    public static void ShowTheNamesOfActorsAndSoleDirectorOfSameFilm() {

    }


}