import java.io.*;
import java.net.*;
import org.w3c.dom.*;
import org.w3c.dom.Node;
import oracle.xml.parser.v2.*;
import java.sql.*;

public class CreateGeoData {
  static public void main(String[] argv) throws SQLException {
     
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
        System.out.println("Error");
        return;
      }

    Connection conn = null;
    try {
      conn = DriverManager.getConnection (
    "jdbc:oracle:thin:@tinman.cs.gsu.edu:1521:sid9ir2","book", "book");
    } catch (SQLException e1) {
        System.out.println("Error");
        return;
      }
    if (conn == null) {
      System.out.println("Null Connection");
      return;
    }
    Statement stmt = conn.createStatement();

    try {
      if (argv.length != 1) {
        // Must pass in the name of the XML file.
        System.err.println("Usage: java CreateGeoData xmlfilename");
        System.exit(1);
      }

      // Get an instance of the parser
      DOMParser parser = new DOMParser();

 	  // Generate a URL from the filename.
  	  URL url = createURL(argv[0]);

      // Set various parser options: validation on,
      // warnings shown, error stream set to stderr.
      parser.setErrorStream(System.err);
      parser.setValidationMode(true);
      parser.showWarnings(true);

	  // Parse the document.
      parser.parse(url);

      // Obtain the document.
      XMLDocument doc = parser.getDocument();
 
      NodeList sl = doc.getElementsByTagName("state");
      NodeList cl = doc.getElementsByTagName("city");
      int len = sl.getLength();
      //System.out.println("number of states = " + len);

      String scode = null, sname = null, nickname = null;
      long population = 0;
      for (int j=0; j < len; j++) {
        XMLNode e = (XMLNode) sl.item(j);
        //e.print(System.out);
        scode = e.valueOf("scode"); 
        sname = e.valueOf("sname"); 
        nickname = e.valueOf("nickname"); 
        population = Long.parseLong(e.valueOf("population")); 

        XMLNode child = (XMLNode) e.getFirstChild();
        while (child != null) {
          if (child.getNodeName().equals("capital"))
            break;
          child = (XMLNode) child.getNextSibling();
        }
        //XMLNode gchild = (XMLNode) child.getFirstChild();
        //gchild.print(System.out);
        NamedNodeMap nnm = child.getAttributes();
        XMLNode n = (XMLNode) nnm.item(0);
        //System.out.println("capital = " + n.getNodeValue());
        String cname=null, ccode=null;
        //System.out.println("n.getNodeValue() = " + n.getNodeValue());
        String x = GetCityNameAndCode(cl,n.getNodeValue());
        int kk = x.indexOf(":");
         String capName = x.substring(0,kk);
         String capCode = x.substring(kk+1);

        child = (XMLNode) e.getFirstChild();
        String cCode[] = new String[10];
        String cName[] = new String[10];
        int count = 0;
        while (child != null) {
          if (child.getNodeName().equals("citiesin")) {
            nnm = child.getAttributes();
            n = (XMLNode) nnm.item(0);
            x = GetCityNameAndCode(cl,n.getNodeValue());
            kk = x.indexOf(":");
            cName[count] = x.substring(0,kk);
            cCode[count++] = x.substring(kk+1);
          }
          child = (XMLNode) child.getNextSibling();
        }
        String insert = "insert into state values ('" +
                         scode +"', '" + sname + "', '" +
                         nickname + "', " + population + ", " +
                         "city_type('"+ capCode + "', '" + capName + "'), ";
                         
        String addInsert = "cities_in_table("; 
        for (int i = 0; i < count; i++) {
          if ( i == count-1) { 
            addInsert += "city_type('" + cCode[i] + "', '" + cName[i] + "')))";
            break;
          }
          addInsert += "city_type('"+ cCode[i] + "', '" + cName[i] + "'), "; 
        }
                           
        insert += addInsert;
        int nrows = 0;
        try {
System.out.println(insert);
          nrows = stmt.executeUpdate(insert); 
        } catch (SQLException e2) {
            System.out.println("Could not insert row");
            System.out.println(e2.getMessage());
          }
      }
   } catch (Exception e) {
       System.out.println(e.toString());
     }
  }


  static String GetCityNameAndCode(NodeList cl, String cid) {
    try {
      int len = cl.getLength();
      for (int j=0; j < len; j++) {
        XMLNode e = (XMLNode) cl.item(j);
        NamedNodeMap nnm = e.getAttributes();
        XMLNode n = (XMLNode) nnm.item(0);
        if (n.getNodeValue().equals(cid)) {
          return (e.valueOf("cname") + ":" + e.valueOf("ccode"));
        }
      }
      return null;
    } catch (Exception e) {
       System.out.println(e.toString());
       return null;
     }
  }

   static URL createURL(String fileName) {
      URL url = null;
      try {
         url = new URL(fileName);
      } catch (MalformedURLException ex) {
         File f = new File(fileName);
         try {
            String path = f.getAbsolutePath();
            String fs = System.getProperty("file.separator");
            if (fs.length() == 1) {
               char sep = fs.charAt(0);
               if (sep != '/')
                  path = path.replace(sep, '/');
               if (path.charAt(0) != '/')
                  path = '/' + path;
            }
            path = "file://" + path;
            url = new URL(path);
         } catch (MalformedURLException e) {
            System.out.println("Cannot create url for: " + fileName);
            System.exit(0);
         }
      }
      return url;
   }
}