package cs610;

/**
 * Simple example application. Will load an XML file in a specific format and
 * populate a specific schema.
 * The enrollment schema, contains three tables in a many to many relationship
 */

import  javax.xml.parsers.DocumentBuilderFactory;
import  javax.xml.parsers.DocumentBuilder;
import  org.w3c.dom.Document;
import  org.w3c.dom.Node;
import  org.w3c.dom.NodeList;
import  org.w3c.dom.NamedNodeMap;
import  java.sql.DriverManager;
import  java.sql.Connection;
import  java.sql.PreparedStatement;
import  java.sql.ResultSet;
import  java.sql.Statement;
import  java.io.File;
import  java.io.FileInputStream;
import  java.io.PrintWriter;
import  java.util.Date;

public class XmlLoadExample {

  public XmlLoadExample(String userId, String userPassword, String databaseURL,
    String xmlFileName) throws Exception {
    m_dbUser = userId;
    m_dbPassword = userPassword;
    m_dbURL = databaseURL;
    constructDocument(xmlFileName);
  }

  private void constructDocument(String xmlFileName) throws Exception {
    File xmlFile = new File(xmlFileName);
    if(!xmlFile.exists()) {
      throw new Exception("File " + xmlFileName + " not found.");
    }
    DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
    DocumentBuilder documentBuilder = factory.newDocumentBuilder();
    //the document will be found in memory
    FileInputStream fileIn = new FileInputStream(xmlFile);
    m_document = documentBuilder.parse(fileIn);
    fileIn.close();
  }

  private void list() throws Exception {
    NodeList nodeList = m_document.getChildNodes();

    java.io.Writer fw = new PrintWriter(System.out);
    fw.write(new Date().toString());
    fw.write("\n");
    for(int i = 0 ; i < nodeList.getLength(); i++) {
      fw.write(nodeList.item(i).toString());
      list(nodeList.item(i));
    }
    fw.write("\n\n");
    fw.close();
  }

  private void list(Node node) throws Exception {
    if(node != null ) {
      NodeList nodeList = node.getChildNodes();
      for(int i = 0; i < nodeList.getLength(); i++) {
        list(nodeList.item(i));
      }
    }
  }

  private void loadClasses() throws Exception {
    NodeList nodeList = m_document.getChildNodes();
    int i = 0;
    for(; i < nodeList.getLength(); i++) {
      if(nodeList.item(i).getNodeType() == Node.ELEMENT_NODE) {
        if(!nodeList.item(i).getNodeName().equals("classes")) {
          throw new Exception("Invalid XML schema, expected classes.");
        }
        else {
          //we obtained our node, we should have only one ELEMENT_NODE
          break;
        }
      }
    }
    Node classesNode = nodeList.item(i);
    NodeList classesList = classesNode.getChildNodes();
    for(int j = 0; j < classesList.getLength(); j++) {
      if(classesList.item(j).getNodeType() == Node.ELEMENT_NODE) {
        loadClass(classesList.item(j));
      }
    }
  }

  private void loadClass(Node classNode) throws Exception {
    //validate and extract the class info
    NamedNodeMap classAttributes = classNode.getAttributes();
    String classId = classAttributes.getNamedItem("classid").getNodeValue();
    String description = classAttributes.getNamedItem("description").getNodeValue();
    String section = classAttributes.getNamedItem("section").getNodeValue();
    String location = classAttributes.getNamedItem("location").getNodeValue();
    //we have the desired values
    m_selectClassPS.setString(1, classId);
    ResultSet rs = m_selectClassPS.executeQuery();
    if(!rs.next()) {
      //we will have to insert since the value is non-existent
      m_insertClassPS.setString(1, classId);
      m_insertClassPS.setString(2, description);
      m_insertClassPS.setString(3, section);
      m_insertClassPS.setString(4, location);
      m_insertClassPS.executeUpdate();
      m_connection.commit();
    }
    rs.close();
    //students are next
    NodeList childNodes = classNode.getChildNodes();
    for(int i = 0; i < childNodes.getLength(); i++) {
      if(childNodes.item(i).getNodeType() == Node.ELEMENT_NODE) {
        if(!childNodes.item(i).getNodeName().equals("students")) {
          throw new Exception("Invalid XML schema, expected students.");
        }
        else {
          loadStudents(childNodes.item(i), classId);
        }
      }
    }
  }

  private void loadStudents(Node studentsNode, String classId) throws Exception {
    NodeList nodeList = studentsNode.getChildNodes();
    for(int i = 0; i < nodeList.getLength(); i++) {
      if(nodeList.item(i).getNodeType() == Node.ELEMENT_NODE) {
        if(!nodeList.item(i).getNodeName().equals("student")) {
          throw new Exception("Invalid XML schema, expected student.");
        }
        else {
          NamedNodeMap classAttributes = nodeList.item(i).getAttributes();
          String studentId = classAttributes.getNamedItem("studentid").getNodeValue();
          String name = classAttributes.getNamedItem("name").getNodeValue();
          String degree = classAttributes.getNamedItem("degree").getNodeValue();
          //we have the desired values
          m_selectStudentPS.setString(1, studentId);
          ResultSet rs = m_selectStudentPS.executeQuery();
          if(!rs.next()) {
            //we will have to insert since the value is non-existent
            m_insertStudentPS.setString(1, studentId);
            m_insertStudentPS.setString(2, name);
            m_insertStudentPS.setString(3, degree);
            m_insertStudentPS.executeUpdate();
          }
          rs.close();
          //run this query only because we want to be able to run the loader
          //more than once without any exceptions being thrown
          //second time around, nothing will be modified
          m_selectEnrollmentPS.setString(1, studentId);
          m_selectEnrollmentPS.setString(2, classId);
          rs = m_selectEnrollmentPS.executeQuery();
          if(!rs.next()) {
            m_insertEnrollmentPS.setString(1, studentId);
            m_insertEnrollmentPS.setString(2, classId);
            m_insertEnrollmentPS.executeUpdate();
            m_connection.commit();
          }
          rs.close();
        }
      }
    }
  }

  private void initializePreparedStatements() throws Exception {
    m_selectClassPS = m_connection.prepareStatement(
      "select classid from class where classid = ?");
    m_insertClassPS = m_connection.prepareStatement(
      "insert into class (classid, description, section, location) values " +
        "(?, ?, ?, ?)");
    m_selectStudentPS = m_connection.prepareStatement(
      "select studentid from student where studentid = ?");
    m_insertStudentPS = m_connection.prepareStatement(
      "insert into student (studentid, name, degree) values " +
        "(?, ?, ?)");
    m_selectEnrollmentPS = m_connection.prepareStatement(
      "select studentid, classid from enrollment where studentid = ? and " +
        "classid = ?");
    m_insertEnrollmentPS = m_connection.prepareStatement(
      "insert into enrollment (studentid, classid) values (?, ?)");
  }

  private void closePreparedStatements() throws Exception {
    m_selectClassPS.close();
    m_insertClassPS.close();
    m_selectStudentPS.close();
    m_insertStudentPS.close();
    m_selectEnrollmentPS.close();
    m_insertEnrollmentPS.close();
  }

  private void load() throws Exception {
    connect();
    list(); //just a parsing exercise, nothing else
    initializePreparedStatements();
    loadClasses();
    closePreparedStatements();
    disconnect();
  }

  public static void main(String args[]) {
    if (args.length != 8) {
      usage();
      System.exit(-1);
    }
    int numArgs = 0;
    String userId = null;
    String userPassword = null;
    String databaseURL = null;
    String xmlFileName = null;
    //parse the parameters
    while (numArgs < args.length) {
      if ("-DBUSER".equalsIgnoreCase(args[numArgs])) {
        userId = args[numArgs + 1];
        numArgs += 2;
      }
      else if ("-DBPASSWORD".equalsIgnoreCase(args[numArgs])) {
        userPassword = args[numArgs + 1];
        numArgs += 2;
      }
      else if ("-DBURL".equalsIgnoreCase(args[numArgs])) {
        databaseURL = args[numArgs + 1];
        numArgs += 2;
      }
      else if ("-XMLFILE".equalsIgnoreCase(args[numArgs])) {
        xmlFileName = args[numArgs + 1];
        numArgs += 2;
      }
      else {
        System.out.println("Unknown option " + args[numArgs]);
        usage();
        System.exit(-1);
      }
    }
    if (userId != null && userPassword != null && databaseURL != null &&
            xmlFileName != null) {
      XmlLoadExample xmlLoadEx = null;
      try {
        xmlLoadEx = new XmlLoadExample(userId, userPassword, databaseURL,
          xmlFileName);
        xmlLoadEx.load();
      }
      catch (Exception ex) {
        System.out.println("Exception caught");
        ex.printStackTrace();
        try {
          xmlLoadEx.disconnect();
        }
        catch (Exception ignored) {;}
        System.exit(-1);
      }
    }
    else {
      System.out.println("Insufficient arguments specified");
      usage();
      System.exit(-1);
    }
  }

  public static void usage() {
    System.out.println("XmlLoadExample -dbUser dbUserId - dbPassword dbPasswd "
      + "-dbURL dbURL -xmlFile xmlFile");
  }

  /**
   * This method will establish a database connection
   * @exception Exception
   */
  private void connect () throws Exception {
    //load the JDBC driver class, in this case oracle
    Class.forName("oracle.jdbc.driver.OracleDriver");
    m_connection = DriverManager.getConnection(m_dbURL, m_dbUser, m_dbPassword);
    //usually a good thing, DML operations will have to call commit or rollback
    m_connection.setAutoCommit(false);
    System.out.println("Database connection obtained");
  }

  /**
   * This method will close the database connection
   * @exception Exception
   */
  private void disconnect () throws Exception {
    if (m_connection != null) {
      m_connection.close();
      System.out.println("Connection closed");
    }
  }

  private String m_dbUser;
  private String m_dbPassword;
  private String m_dbURL;
  private Connection m_connection;
  private Document m_document;
  private PreparedStatement m_selectClassPS;
  private PreparedStatement m_insertClassPS;
  private PreparedStatement m_selectStudentPS;
  private PreparedStatement m_insertStudentPS;
  private PreparedStatement m_selectEnrollmentPS;
  private PreparedStatement m_insertEnrollmentPS;
}