import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import com.google.gson.JsonObject;

import java.sql.*;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

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 DomXmlParser {
	List<Movie> myMov;
	List<Star> myStar;
	Document domMov;
	Document domStar;

	public DomXmlParser() {
		myMov = new ArrayList<>();
		myStar = new ArrayList<>();
	}

	private void parseXmlFile() {
		//get the factory
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();

        try {

            //Using factory get an instance of document builder
            DocumentBuilder db = dbf.newDocumentBuilder();

            //parse using builder to get DOM representation of the XML file
            domMov = db.parse("mains243.xml");
            domStar = db.parse("actors63.xml");
        }
	    catch (ParserConfigurationException pce) {
	        pce.printStackTrace();
	    } catch (SAXException se) {
	        se.printStackTrace();
	    } catch (IOException ioe) {
	        ioe.printStackTrace();
	    }
	}


    private void parseDocument() throws FileNotFoundException {

        PrintWriter writer = new PrintWriter(new File("outputMovie.txt"));

        //get the root elememt
        Element docEle = domMov.getDocumentElement();

        //get a nodelist of <employee> elements
        // <film> tag contains all the movies.
        NodeList nl = docEle.getElementsByTagName("film");
        if (nl != null && nl.getLength() > 0) {
            for (int i = 0; i < nl.getLength(); i++) {

                //get the directorfilms element
                Element el = (Element) nl.item(i);

                //get the Employee object
                Movie e = getMovie(el);

                //add it to list
                myMov.add(e);
                writer.write(e.toString());
            }
        }

        System.out.println("No of Movies = " + myMov.size());
        System.out.println("writing to file is done");

      //////////////////////////////////////////////////////////////////////////////////////////////////
      PrintWriter writerStar = new PrintWriter(new File("outputStar.txt"));

        Element docEleStar = domStar.getDocumentElement();
        NodeList nlStar = docEleStar.getElementsByTagName("actor");
        if (nlStar != null && nlStar.getLength() > 0) {
            for (int i = 0; i < nlStar.getLength(); i++) {

                //get the directorfilms element
                Element elStar = (Element) nlStar.item(i);

                //get the Employee object
                Star e = getStar(elStar);

                //add it to list
                myStar.add(e);
                writerStar.write(e.toString());
            }
        }

        System.out.println("No of Star = " + myStar.size());
        System.out.println("writing to file is done");
    }
    private Star getStar(Element el) {
    	String name = getTextValue(el, "stagename");
    	int year = getIntValue(el, "dob");

    	Star s = new Star(null, name, year);
    	return s;
    }

    private Movie getMovie(Element el) {

        String id = getTextValue(el, "fid");
        String title = getTextValue(el, "t");
        int year = getIntValue(el, "year");
        String directorName = getTextValue(el, "dirn");
        String genre = getTextValue(el, "cat");

//        System.out.println("id = " + id);
//        System.out.println("title = " + title);
//        System.out.println("year = " + year);
//        System.out.println("directorName = " + directorName);

        //Create a new Employee with the value read from the xml nodes
        Movie e = new Movie(id, title, year, directorName, genre);

        return e;
    }

    private String getTextValue(Element ele, String tagName) {
        String textVal = "";
        NodeList nl = ele.getElementsByTagName(tagName);
        if (nl != null && nl.getLength() > 0) {
            Element el = (Element) nl.item(0);
            textVal = el.getTextContent();
        }
        if (textVal == null)
        	textVal = "";
        return textVal;
    }

    private int getIntValue(Element ele, String tagName) {
    	int intVal = 0;
    	boolean numeric = true;
    	try {
    		if (getTextValue(ele, tagName) != null || getTextValue(ele, tagName).length() != 0)
    			intVal = Integer.parseInt(getTextValue(ele, tagName));
    	}
    	catch (NumberFormatException e) {
    		numeric = false;
    	}

    	if (!numeric)
    		return 0;

    	return Integer.parseInt(getTextValue(ele, tagName));

    }


	public void run() throws IOException {
        //parse the xml file and get the dom object
        parseXmlFile();

        //get each employee element and create a Employee object
        parseDocument();

	}

	public static void main(String[] args) throws IOException {
        long startTime = System.nanoTime();
        System.out.println("Timer started: " + startTime);

        DomXmlParser dp = new DomXmlParser();
        dp.run();
        System.out.println("Parser Finished");
        try {

        	Class.forName("com.mysql.jdbc.Driver").newInstance();

            // Connect to the test database
            Connection connection = DriverManager.getConnection("jdbc:mysql:///moviedb?autoReconnect=true&useSSL=false",
                    "mytestuser", "mypassword");
            String queryStar = "{CALL insertingStar(?, ?, ?)}";
            CallableStatement StarStatement = connection.prepareCall(queryStar);
            System.out.println("Please wait for stars to be inserted to the database!");

            int j = 0;
            while (j < dp.myStar.size()) {
            	StarStatement.setString(1, null);
            	StarStatement.setString(2, dp.myStar.get(j).getName());
            	StarStatement.setInt(3, dp.myStar.get(j).getBirthYear());
            	StarStatement.executeQuery();
            	j++;
            }
            System.out.println("Inserting to stars database is done!");

			String query = "{CALL add_movie(?, ?, ?, ?, ?, ?, ?)}";
			CallableStatement queryStatement = connection.prepareCall(query);
        	System.out.println("Please wait for movies to be inserted!");
        	int i = 0;
        	while (i < dp.myMov.size()) {
    			queryStatement.setString(1, dp.myMov.get(i).getId());
    			queryStatement.setString(2, dp.myMov.get(i).getTitle());
    			queryStatement.setInt(3, dp.myMov.get(i).getYear());
    			queryStatement.setString(4, dp.myMov.get(i).getDirector());
    			queryStatement.setString(5, "");
    			queryStatement.setString(6, "");
    			queryStatement.setString(7, "");

        		queryStatement.executeQuery();

        		i++;
        	}
        	connection.close();

		}
		catch (Exception e) {
			System.out.println(e.getMessage());
		}
        System.out.println("Inserting to movies database is done!");
        long endTime = System.nanoTime();
        System.out.println("Took "+(endTime - startTime) + " ns");
    }

}