/*
     * A Java program that uses the JDBC driver for SQLite
     * to extract data from our movie database.
     *
     * Before compiling this program, you must download the JAR file for the
     * SQLite JDBC Driver and add it to your classpath. See the Java-specific
     * notes in the assignment for more details.
     *
     * In addition, the database file should be in the same folder
     * as the program.
     *
     * Computer Science E-66
     */

    import java.util.*;     // needed for the Scanner class
    import java.sql.*;      // needed for the JDBC-related classes
    import java.io.*;       // needed for the PrintStream class

    public class MovieToXML {
        public static void main(String[] args)
          throws ClassNotFoundException, SQLException, FileNotFoundException
        {
            Scanner console = new Scanner(System.in);

            // Connect to the database.
            System.out.print("name of database file: ");
            String db_filename = console.next();
        Class.forName("org.sqlite.JDBC");
            Connection db = DriverManager.getConnection("jdbc:sqlite:" + db_filename);

            // Create a PrintStream for the results file.
            //System.out.print("name of results file: ");
            String out_filename = "moviesXml.txt";
            PrintStream outfile = new PrintStream(out_filename);

            // Create a Statement object and use it to execute the query.
            Statement stmt = db.createStatement();

            String query = "SELECT *" +
                           "FROM Movie ;";

            ResultSet results = stmt.executeQuery(query);

            Object Xstring = "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>";
            outfile.println(Xstring);
            outfile.println();

            Object moviesTag = "<movies>";
            outfile.println(moviesTag);

            while (results.next()){
                String id = results.getString("id");
                String name = results.getString("name");
                String year = results.getString("year");
                String rating = results.getString("rating");
                String runtime = results.getString("runtime");
                String genre = results.getString("genre");
                String earnings_rank = results.getString("earnings_rank");
                String directors = "";
                String actors = "";
                String oscars = "";

                String dirQuery = "SELECT director_id FROM Director WHERE Director.movie_id = " +"\"" + id + "\"" + ";";

                Statement dirstmt = db.createStatement();
                ResultSet dirRes = dirstmt.executeQuery(dirQuery);

                while(dirRes.next()){
                   String dir_id = dirRes.getString(1);
                   if(!directors.contains(dir_id))
                       directors = directors + "P" + dir_id + " ";
                }

                String actorQuery = "SELECT actor_id FROM Actor WHERE movie_id = " + "\"" + id + "\"" + ";";

                Statement actorstmt = db.createStatement();
                ResultSet actorRes = actorstmt.executeQuery(actorQuery);

                while(actorRes.next()){
                   String act_id = actorRes.getString(1);
                   if(!actors.contains(act_id))
                      actors = actors + "P" + act_id + " ";
                }

                String oscQuery = "SELECT type,year,person_id FROM oscar WHERE movie_id = " + "\"" + id + "\""  + ";";

                Statement oscstmt = db.createStatement();
                ResultSet oscarRes = oscstmt.executeQuery(oscQuery);

                while(oscarRes.next()){
                   String type = oscarRes.getString("type");
                   String year1 = oscarRes.getString("year");
                   String oscarpid = oscarRes.getString("person_id");
                   if (!type.equals(null)){

                        if(type.matches("BEST-PICTURE"))
                            type = "0000000";
                        else {
                            type = oscarpid;
                        }
                       type = "O" + year1 + type+ " ";
                    }

                   if(!oscars.contains(type))
                      oscars = oscars + type;
                }

                // write contents to xml file
                Object MovieTag = "  <movie ";
                outfile.print(MovieTag);

                outfile.println("id="+  "\"" + "M" + id + "\""+ " " + "directors="+  "\"" +  directors.substring(0, directors.length())+ "\"");
                outfile.print("         actors=");
                outfile.println("\"" + actors.substring(0, actors.length()) + "\"" + ">");
                if (oscars != ""){
                    outfile.print("         oscars=");
                    outfile.println("\"" + oscars.substring(0, oscars.length()) + "\"" + " ");
                }
                outfile.println("    <name>"+ name + "</name>");
                outfile.println("    <year>"+ year + "</year>");
                if(rating != null)
                    outfile.println("    <rating>"+ rating + "</rating>");
                outfile.println("    <runtime>"+ runtime + "</runtime>");
                if(genre != null)
                    outfile.println("    <genre>"+ genre +"</genre>");
                if(earnings_rank != null)
                    outfile.println("    <earnings_rank>"+ earnings_rank+"</earnings_rank>");
                Object closeMovieTag = "  </movie>";
                outfile.println(closeMovieTag);

            }

            Object closeMoviesTag = "</movies>";
            outfile.println(closeMoviesTag);

            System.out.println("movies.xml has been written.");

            // Close the file and the database connection.
            outfile.close();

             /*
              *
              *    people starts here
              *
              */

                String peopleXML = "peopleXml.txt";
                PrintStream outPplXML = new PrintStream(peopleXML);

                // Create a Statement object and use it to execute the query.
                Statement pplStmt = db.createStatement();

                String pplQuery = "SELECT *" +
                                  "FROM Person ;";

                ResultSet pplQResults = pplStmt.executeQuery(pplQuery);

                outPplXML.println(Xstring);
                outPplXML.println();

                Object peopleTag = "<people>";
                outPplXML.println(peopleTag);
                while (pplQResults.next()){

                    String id = pplQResults.getString("id");
                    String name = pplQResults.getString("name");
                    String dob= pplQResults.getString("dob");
                    String pob = pplQResults.getString("pob");

                    // write contents to xml file
                    Object personTag = "  <person ";
                    outPplXML.print(personTag);
                    outPplXML.print("\"" +"id="+ "P" + id +"\"" );

                    // Create a Statement object and use it to execute the Movies query.
                    Statement dirStmt = db.createStatement();

                    String directedQuery = "SELECT Movie.id FROM Director,Movie WHERE movie_id = Movie.id AND director_id = "+ "\"" + id + "\"" +";";


                    ResultSet directedQResults = dirStmt.executeQuery(directedQuery);

                    String directed = "";
                    String dirMovieIds = "";
                    while(directedQResults.next()){
                        dirMovieIds = directedQResults.getString(1);
                       if(!directed.contains(dirMovieIds))
                          directed =  directed + "M" + dirMovieIds + " ";

                    }
                    if(directed != ""){
                        outPplXML.print(" directed=");
                        outPplXML.println("\"" + directed + "\"" + ">");
                    }

                    // Create a Statement object and use it to execute the Movies query.
                    Statement movStmt = db.createStatement();

                    String actedInQuery = "SELECT Movie.id FROM Actor,Movie WHERE movie_id = Movie.id AND actor_id = "+ "\"" + id + "\"" +";";


                    ResultSet actedInQResults = movStmt.executeQuery(actedInQuery);

                    String actedIn = "";


                    String movieIds= "";
                    while(actedInQResults.next()){
                       movieIds = actedInQResults.getString(1);

                       if(!actedIn.contains(movieIds))
                          actedIn =  actedIn + "M" + movieIds + " ";

                    }
                    if(actedIn != ""){
                        if(directed != "")
                            outPplXML.print("           actedIn=");
                        else
                            outPplXML.print(" actedIn=");
                        outPplXML.println("\"" + actedIn + "\"" + ">");
                    }



                    // Create a Statement object and use it to execute the Movies query.
                    Statement oscarsStmt = db.createStatement();

                    String oscarsQuery = "SELECT Oscar.type,Oscar.year,Oscar.person_id FROM Oscar,Movie WHERE movie_id = id AND person_id = "+ "\"" + id + "\"" +";";
                    String oscarsRecieved = "";


                    ResultSet oscarsQResults = oscarsStmt.executeQuery(oscarsQuery);


                    while(oscarsQResults.next()) {

                           String type = oscarsQResults.getString(1);
                           String year = oscarsQResults.getString(2);
                           String oscarpid = oscarsQResults.getString(3);
                           if (!type.equals(null)){

                                if(type.matches("BEST-PICTURE"))
                                    type = "0000000";
                                else {
                                    type = oscarpid;
                                }
                               type = "O" + year + type+ " ";
                            }

                           if(!oscarsRecieved.contains(type))
                              oscarsRecieved =  oscarsRecieved + type + " ";
                    }
                    if (oscarsRecieved != ""){
                        outPplXML.print("           oscars=");
                        outPplXML.println("\"" + oscarsRecieved + "\"" + " ");
                    }
                    outPplXML.println("    <name>"+ name + "</name>");
                    outPplXML.println("    <dob>"+ dob + "</dob>");
                    outPplXML.println("    <pob>"+ pob + "</pob>");

                    Object closeoscarTag = "  </person>";
                    outPplXML.println(closeoscarTag);
                }
                Object closePeopleTag = "</people>";
                outPplXML.println(closePeopleTag);
                System.out.println("people.xml has been written.");

                outPplXML.close();

         /*
          *
          *    oscar starts here
          *
          */

            String oscarsXML = "oscarsXml.txt";
            PrintStream outOscXML = new PrintStream(oscarsXML);

            // Create a Statement object and use it to execute the query.
            Statement oscStmt = db.createStatement();

            String oscQuery = "SELECT *" +
                           "FROM Oscar ;";

            ResultSet oscQResults = oscStmt.executeQuery(oscQuery);



            outOscXML.println(Xstring);
            outOscXML.println();


            Object oscarsTag = "<oscars>";
            outOscXML.println(oscarsTag);
            while (oscQResults.next()){

                String movie_id = oscQResults.getString("movie_id");
                String person_id = oscQResults.getString("person_id");
                String type = oscQResults.getString("type");
                String year = oscQResults.getString("year");

                // write contents to xml file
                Object oscarTag = "  <oscar ";
                outOscXML.print(oscarTag);
                String type1 = type;
                if (!type1.equals(null)){


                    if(type1.matches("BEST-PICTURE"))
                        type1 = "0000000";
                    else {
                        type1 = person_id;
                    }
                type1 = "O" + year + type1;
                }

                outOscXML.print("\"" +"id="+ type1 + "\"" );
                outOscXML.print(" movie_id=");
                outOscXML.print("\"" + "M" + movie_id + "\"" + " ");
                if(!type.matches("BEST-PICTURE"))
                    outOscXML.print("person_id="+"\"" + "P" + person_id + "\"" + ">");
                outOscXML.println();



                outOscXML.println("    <type>"+ type + "</type>");
                outOscXML.println("    <year>"+ year + "</year>");


                Object closeoscarTag = "  </oscar>";
                outOscXML.println(closeoscarTag);
            }
            Object closeoscarsTag = "</oscars>";
            outOscXML.println(closeoscarsTag);
            System.out.println("oscars.xml has been written.");
            outOscXML.close();

            db.close();
        }