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);

            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();
        String data_file = "src/DatabaseAssignment2/film.xml";


        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( '123', " +
            "sys.XMLType.createXML( ' " + xmlv.getStringVal() + " ' ))");

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

    }


    public static void showTitleOfAllFilms() {

    }


    public static void showTitleOfAllFilmWithoutTitleTags() {

    }


    public static void showNamesOfActorsWhoAppearedInGodfather() {

    }


    public static void showTitleAndYearOfAllCrimeFilms() {

    }


    public static void showTitleAndYearOfAllFilmsMarlonBrando() {

    }

    public static void showOneDirectorFilms() {

    }


    public static void showTitleAndNamesMoreThanOneDirector() {

    }


    public static void ShowTheNamesOfActorsAndSoleDirectorOfSameFilm() {

    }


}