import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Iterator;
import java.util.Set;
import java.util.ArrayList;
import java.util.List;

import java.sql.*;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;

import org.xml.sax.helpers.DefaultHandler;

public class XML_Parse extends DefaultHandler{
	// Create a dataSource which registered in web.xml

	static HashMap<String, Stuff> movieInfo;
	static HashMap<String, Stuff> actorInfo;
//	static int count = 0;
	private String tempVal;
	private String curMovie;
	private Stuff tempMovie;
	private Stuff tempActor;
	private String parsing;
	private String curDir;
	private String temp;
	private String mid;

	static {
		try { Class.forName("com.mysql.jdbc.Driver"); }
		catch(ClassNotFoundException ex) {
		System.err.println("Driver not found: " + ex.getMessage());
		}
	};

	public XML_Parse() {
		movieInfo = new HashMap<String, Stuff> ();
		actorInfo = new HashMap<String, Stuff> ();
		}

	public void parseCasts() {
		SAXParserFactory spf = SAXParserFactory.newInstance();
		parsing = "p";
        try {

            //get a new instance of parser
            SAXParser sp = spf.newSAXParser();

            //parse the file and also register this class for call backs
            sp.parse("casts124.xml", this);

        } catch (SAXException se) {
            se.printStackTrace();
        } catch (ParserConfigurationException pce) {
            pce.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        }
    }

	public void parseMovies() {
		SAXParserFactory spf = SAXParserFactory.newInstance();
		parsing = "m";
        try {

            //get a new instance of parser
            SAXParser sp = spf.newSAXParser();

            //parse the file and also register this class for call backs
            sp.parse("mains243.xml", this);

        } catch (SAXException se) {
            se.printStackTrace();
        } catch (ParserConfigurationException pce) {
            pce.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        }
    }

	public void parseActors() {
		SAXParserFactory spf = SAXParserFactory.newInstance();
		parsing = "a";
        try {

            //get a new instance of parser
            SAXParser sp = spf.newSAXParser();

            //parse the file and also register this class for call backs
            sp.parse("actors63.xml", this);

        } catch (SAXException se) {
            se.printStackTrace();
        } catch (ParserConfigurationException pce) {
            pce.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        }
    }

	public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
        //reset
        tempVal = "";
		if (parsing == "p") {
	        if (qName.equalsIgnoreCase("filmc"))
	        	tempMovie = new Stuff();
	        if (qName.equalsIgnoreCase("m"))
	        	tempActor = new Stuff();
		}

		if (parsing == "m") {
			if (qName.equalsIgnoreCase("director"))
				tempMovie = new Stuff();
			if (qName.equalsIgnoreCase("film"))
				tempMovie = new Stuff();
		}

		if (parsing == "a") {
			if (qName.equalsIgnoreCase("actor"))
				tempActor = new Stuff();
		}
    }

	public void characters(char[] ch, int start, int length) throws SAXException {
        tempVal = new String(ch, start, length);
    }

    public void endElement(String uri, String localName, String qName) throws SAXException {
    	if (parsing == "p") {
	        if (qName.equalsIgnoreCase("filmc")) {
	            //add it to the list
	            movieInfo.put(curMovie, tempMovie);
	            //System.out.println(curMovie);
	            //System.out.println(tempMovie.printStuff());
	        }
	        else if (qName.equalsIgnoreCase("f")) {
	        	curMovie = tempVal;
	        	tempActor.Movie_id.add(tempVal);
	        	if (!tempMovie.Movie_id.contains(tempVal))
	        		tempMovie.Movie_id.add(tempVal);
	        }
	        else if (qName.equalsIgnoreCase("t")) {
	            tempMovie.Movie_name = tempVal;
	        }
	        else if (qName.equalsIgnoreCase("a")) {
	        	temp = tempVal.replaceAll("\\s+", "");
	            if (!temp.equalsIgnoreCase("sa")) {
	            	if (!actorInfo.containsKey(tempVal))
	            		actorInfo.put(tempVal, tempActor);
	            	else
	            		if (!actorInfo.get(tempVal).Movie_id.contains(tempActor.Movie_id.get(0)))
	            			actorInfo.get(tempVal).Movie_id.add(tempActor.Movie_id.get(0));
	            }
	        }
    	}
    	if (parsing == "m") {
    		if (qName.equalsIgnoreCase("dirname")) {
    			curDir = tempVal;
    		}
    		if (qName.equalsIgnoreCase("fid")) {
    			if (!tempMovie.Movie_id.contains(tempVal))
    				tempMovie.Movie_id.add(tempVal);
    			mid = tempVal;
    		}
    		if (qName.equalsIgnoreCase("t")) {
    			tempMovie.Movie_name = tempVal;
    		}
    		if (qName.equalsIgnoreCase("year")) {
    			tempMovie.Movie_year = tempVal;
    		}
    		if (qName.equalsIgnoreCase("cat")) {
    			if (!tempMovie.Genres.contains(tempVal))
    				tempMovie.Genres.add(tempVal);
    		}
    		if (qName.equalsIgnoreCase("film")) {
    			tempMovie.Director_name = curDir;
    			//System.out.println(mid + " " + tempMovie.printStuff());
    			if (movieInfo.containsKey(mid))	{
    				movieInfo.get(mid).Movie_year = tempMovie.Movie_year;
					movieInfo.get(mid).Genres = tempMovie.Genres;
					movieInfo.get(mid).Director_name = curDir;
    			}
//    			System.out.println(mid + " " + movieInfo.get(mid).printStuff());
    		}
    	}
    	if (parsing == "a") {
    		if (qName.equalsIgnoreCase("dob"))
    			tempActor.Birth_year = tempVal;
    		if (qName.equalsIgnoreCase("stagename"))
    			tempActor.Star_name = tempVal;
    		if (qName.equalsIgnoreCase("actor")) {
    			if (!actorInfo.containsKey(tempActor.Star_name))
    				actorInfo.put(tempActor.Star_name, tempActor);
    			else
    				actorInfo.get(tempActor.Star_name).Birth_year = tempActor.Birth_year;
    		}
    	}
    }

    public static String genID(int id) {
    	id++;
    	return "nm" + Integer.toString(id);
    }

    public static void main(String[] args) throws Exception {
        XML_Parse xp = new XML_Parse();
        xp.parseCasts();
        xp.parseMovies();
        xp.parseActors();
//        System.out.println(movieInfo.size());

//        for (String key : actorInfo.keySet()) {
//            if (actorInfo.get(key).Movie_id.size()> 1)
//        		System.out.println(key + " " + actorInfo.get(key).printStuff());
//        }

        HashSet<String> genres = new HashSet<String>();
        int maxstarid = 0;
        HashMap<String, String> genreids = new HashMap<String, String>();
        try {
        	Class.forName("com.mysql.jdbc.Driver");
        	Connection con=DriverManager.getConnection(
        	"jdbc:mysql://localhost:3306/moviedb","mytestuser","mypassword");
        	String query = "select genres.name, convert(substring(max(stars.id), 3), unsigned integer) as maxid from genres, stars group by genres.name;";
        	PreparedStatement ps = con.prepareStatement(query);
        	ResultSet grs = ps.executeQuery();
			while (grs.next()) {
				maxstarid = grs.getInt("maxid");
				genres.add(grs.getString("name"));
			}
	        String genre_query = "CALL add_genre(?);";
	        PreparedStatement genre_ps = con.prepareStatement(genre_query);
	        String movie_query = "INSERT INTO movies(id, title, year, director) VALUES(?, ?, ?, ?);";
	        PreparedStatement movie_ps = con.prepareStatement(movie_query);
	        String star_query = "CALL add_star(?, ?);";
	        PreparedStatement star_ps = con.prepareStatement(star_query);
	        String star_in_movies_query = "INSERT INTO stars_in_movies(starId, movieId) VALUES(?, ?);";
	        PreparedStatement sim_ps = con.prepareStatement(star_in_movies_query);
	        String gim_query = "INSERT INTO genres_in_movies(genreId, movieId) VALUES(?, ?);";
	        PreparedStatement gim_ps = con.prepareStatement(gim_query);
	        String r_query = "INSERT INTO ratings(movieId, rating, numVotes) VALUES(?, 0.0, 0);";
	        PreparedStatement r_ps = con.prepareStatement(r_query);

	        for (String key : movieInfo.keySet()) {
	        	movie_ps.setString(1, key);
	        	r_ps.setString(1, key);
	        	r_ps.addBatch();
	        	movie_ps.setString(2, movieInfo.get(key).Movie_name);
	        	movie_ps.setString(3, movieInfo.get(key).Movie_year);
	        	movie_ps.setString(4, movieInfo.get(key).Director_name);
	            movie_ps.addBatch();
	            for (String t : movieInfo.get(key).Genres) {
	            	if (!genres.contains(t)) {
	            		genre_ps.setString(1, t);
	            		genres.add(t);
	            		genre_ps.addBatch();
	            	}
	            }
	        }

	        for (String key : actorInfo.keySet()) {
	        	String tempID = genID(maxstarid);
	        	star_ps.setString(1, key);
	        	if (actorInfo.get(key).Birth_year == "")
	        		star_ps.setString(2, "NULL");
	        	else
	        		star_ps.setString(2, actorInfo.get(key).Birth_year);
	        	star_ps.addBatch();
	        	for (String t : actorInfo.get(key).Movie_id) {
	        		sim_ps.setString(1, tempID);
	        		sim_ps.setString(2, t);
	        		sim_ps.addBatch();
	        	}
	        }

	        query = "Select * from genres;";
	        ps = con.prepareStatement(query);
        	grs = ps.executeQuery();
			while (grs.next()) {
				genreids.put(grs.getString("name"), grs.getString("id"));
			}
			for (String key : movieInfo.keySet()) {
				for(String t : movieInfo.get(key).Genres) {
					String tempgid = genreids.get(t);
					gim_ps.setString(1, tempgid);
					gim_ps.setString(2, key);
					gim_ps.addBatch();
				}
			}

			movie_ps.executeBatch();
			star_ps.executeBatch();
			sim_ps.executeBatch();
			genre_ps.executeBatch();
			gim_ps.executeBatch();
			r_ps.executeBatch();


        } catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        catch (Exception e) {
        	throw e;
        }

        for (String s : genres) {
            System.out.println(s);
        }


    }
}