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 createMenu(Connection conn) throws SQLException {

        try {
            conn = DriverManager.getConnection("jdbc"
                    + ":oracle:thin:"
                    + getUsername() + "/" + getPassword() + "@" + getHostname() + ":1521/"
                    + getServicename());
        } catch (SQLException e) {
            System.out.println("Error-" + e.toString());
        }
        StringBuilder sb = new StringBuilder();
        int choice = 0;
        try {

            sb.append("\nOracle Film Database"
                    + "\n1 - Get the titles of all films"
                    + "\n2 - Get the titles of all films without the surrounding <title> tag"
                    + "\n3 - Get the names of all actors who appeard in the Godfather"
                    + "\n4 - Get the title and year of all crime films"
                    + "\n5 - Get the title and year of all films in which Marlon Brando has acted"
                    + "\n6 - Get the titles of all films which have only one director"
                    + "\n7 - Get the title and names of directors of all films which have more than one director"
                    + "\n8 -  Get the names of persons who have acted in a film and also been the sole director of the same film"
                    + "\n9 - Exit Program");

            do {

                System.out.println(sb.toString());

                System.out.println("Enter Option (1-9)");
                Scanner userInput = new Scanner(System.in);
                choice = userInput.nextInt();

                switch (choice) {
                    case 1:

                        showTitleOfAllFilms(conn);

                        break;

                    case 2:
                        showTitleOfAllFilmWithoutTitleTags(conn);

                        break;
                    case 3:
                        showNamesOfActorsWhoAppearedInGodfather(conn);

                        break;
                    case 4:
                        showTitleAndYearOfAllCrimeFilms(conn);

                        break;

                    case 5:
                        showTitleAndYearOfAllFilmsMarlonBrando(conn);

                        break;
                      case 6:
                        showOneDirectorFilms(conn);

                        break;
                   case 7:
                        showTitleAndNamesMoreThanOneDirector(conn);

                        break;
                    case 8:
                        ShowTheNamesOfActorsAndSoleDirectorOfSameFilm(conn);

                        break;

                    case 9:
                        System.out.println("Exiting Program");
                        System.exit(0);

                        break;

                }

            } while (choice != 9);

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

    }

    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();
            setUsername(username);
            password = sc.next();
            setPassword(password);
            hostname = sc.next();
            setHostname(hostname);
            serviceName = "orcl";
            setServiceName(serviceName);
            System.out.println(username + " " + password + " " + hostname);

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


            try (
                    Connection conn = DriverManager.getConnection("jdbc"
                            + ":oracle:thin:"
                            + username + "/" + password + "@" + hostname + ":1521/"
                            + serviceName)) {

                createFilmTable(conn);
                populateFilmTable(conn);
                createMenu(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 Get the titles of all films;---------- \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 Get the titles of all films without the surrounding <title> tag;---------- \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 Get the names of all actors who appeard in the Godfather;---------- \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.film.extract('/film/title/@year').getStringval, "
                + "a.film.extract('/film/title/text()').getStringval() "
                + "FROM ASS2_FILM a "
                + "WHERE a.film.extract('/film//genres/genre[1]/text()').getStringval() = 'Crime'");

        System.out.println("\n Get the title and year of all crime films;---------- \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.film.extract('/film/title/@year').getStringval, "
                + "a.film.extract('/film/title/text()').getStringval() "
                + "FROM ASS2_FILM a "
                + "WHERE a.film.extract('/film//cast/performer[1]/actor/text()').getStringval() = 'Marlon Brando'");

        System.out.println("\n Get the title and year of all films in which Marlon Brando has acted;---------- \n");

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

        }
    }

    public static void showOneDirectorFilms(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 "
                + "WHERE a.film.existsNode('/film//directors/director[2]') = 0");

        System.out.println("\n Get the titles of all films which have only one director;---------- \n");

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

        }
    }

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

        ResultSet result = s.executeQuery("SELECT a.film.extract('/film//director/text()').getStringval(),"
                + "a.film.extract('/film/title/text()').getStringval() FROM ASS2_FILM a "
                + "WHERE a.film.existsNode('/film//directors/director[2]') =1");

        System.out.println("\n Get the title and names of directors of all films which have more than one director;---------- \n");

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

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

        ResultSet result = s.executeQuery("SELECT a.film.extract('/film//director/text()').getStringval() FROM ASS2_FILM a "
                + "WHERE a.film.existsNode('/film//directors/director[2]') =0"
                + "AND a.film.extract('/film//directors/director[1]').getStringval() = a.film.extract('/film//cast/performer/actor').getStringval()");

        System.out.println("Get the names of persons who have acted in a film and also been the sole director of the same film;---------- \n");

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

        }
    }
    public static String getUsername() {
        return username;
    }

    public static void setUsername(String username) {
        DatabaseAssignment2.username = username;
    }

    public static String getPassword() {
        return password;
    }

    public static void setPassword(String password) {
        DatabaseAssignment2.password = password;
    }

    public static String getHostname() {
        return hostname;
    }

    public static void setHostname(String hostname) {
        DatabaseAssignment2.hostname = hostname;
    }

    public static String getServicename() {
        return serviceName;
    }

    public static void setServiceName(String serviceName) {
        DatabaseAssignment2.serviceName = serviceName;
    }

}