///////////////////////////////////////////////////////////////////////////////

// COMP4317 - Assignment 3
// Andrew Clayphan
// z3131666, ajc
// Last Edit: 2:06am Wednesday May 27 2009

///////////////////////////////////////////////////////////////////////////////

import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;
import org.xml.sax.Attributes;
import org.xml.sax.helpers.DefaultHandler;

import java.sql.*;

import java.util.*;
import java.lang.*;

import java.io.*;

import java.util.regex.*;

///////////////////////////////////////////////////////////////////////////////

// Options
class Options {
  public static boolean t = false; // implemented
  public static boolean p = false; // implemented
  public static boolean d = false; // implemented
  public static boolean q = false; // implemented
  public static boolean c = false; // implemented

  public static String filename = "";
  public static String table_prefix = "";

  public static String student_prefix = "z3131666";

  public static String database_server = "";
  public static String database_name = "";
  public static String database_username = "";
  public static String database_password = "";
}

///////////////////////////////////////////////////////////////////////////////

class Pair {
  public int level = -1;
  public String tag = null;

  Pair(int _level, String _tag) {
    level = _level;
    tag = _tag;
  }
}

///////////////////////////////////////////////////////////////////////////////

class TableRow {
  public int pre = 0;
  public int post = 0;
  public int level = 0;
  public String tag = null;
  public String text = null;

  TableRow(int _pre, int _post, int _level, String _tag, String _text) {
    pre   = _pre;
    post  = _post;
    level = _level;
    tag   = _tag;
    text  = _text;
  }
}

///////////////////////////////////////////////////////////////////////////////

class AttrRow {
  public int pre = 0;
  public String attr = null;
  public String value = null;

  AttrRow(int _pre, String _attr, String _value) {
    pre   = _pre;
    attr  = _attr;
    value = _value;
  }
}

///////////////////////////////////////////////////////////////////////////////

class SAXHandlers extends DefaultHandler {

  // internal representation of database tables
  public ArrayList<TableRow> table = new ArrayList<TableRow>();
  public ArrayList<AttrRow>  attr  = new ArrayList<AttrRow>();

  // internal counter variables
  private ArrayList<Integer>  stack = new ArrayList<Integer>();
  private int preCount = 1;
  private int postCount = 1;

  // to handle the SAX Handler problem as documented in the tutorial
  private String buffer = "";

  public void processCharacters() {
    if(buffer.length() != 0) {
      // Add to the internal table
      stack.add(preCount);
      table.add(new TableRow(preCount,0,0,null,buffer));
      ++preCount;
      // Update the internal table
      int level    = stack.size();
      int index    = stack.remove(stack.size()-1);
      TableRow ref = table.get(index-1);
      ref.post     = postCount;
      ref.level    = level;
      ++postCount;
      // Clear the buffer
      buffer = "";
    }
  }

  public void characters(char ch[], int start, int length) {
    String textNodeContent = new String(ch, start, length);
    textNodeContent = textNodeContent.trim().replaceAll("<", "&lt;").replaceAll(">", "&gt;").replaceAll("&", "&amp;").replaceAll("\"", "&quot;").replaceAll("'", "&apos;");
    if(!textNodeContent.equals("")) {
      buffer += textNodeContent;
    }
  }

  public void endDocument() {
    processCharacters();
  }

  public void startDocument() {
    processCharacters();
  }

  public void startElement(String uri, String localName, String qName, Attributes attrs) throws SAXException {
    processCharacters();

    // Add to the internal table
    stack.add(preCount);
    table.add(new TableRow(preCount,0,0,qName,null));

    // Add to the internal attr
    for(int i = 0; i < attrs.getLength(); ++i) {
      attr.add(new AttrRow(preCount,attrs.getQName(i),attrs.getValue(i)));
    }

    ++preCount;
  }

  public void endElement(String uri, String localName, String qName) throws SAXException {
    processCharacters();

    // Update the internal table
    int level    = stack.size();
    int index    = stack.remove(stack.size()-1);
    TableRow ref = table.get(index-1);
    ref.post     = postCount;
    ref.level    = level;
    ++postCount;
  }
}

///////////////////////////////////////////////////////////////////////////////

public class ass3 {

  // as per the tutor's instructions from the tutorial
  private static String extractTableName(String filename) {
    String table_prefix = "";
    for(int i = filename.length()-1; i > -1; i--) {
      if (filename.charAt(i) == '/') {
        break;
      } else {
        table_prefix = filename.charAt(i) + table_prefix;
      }
    }
    return table_prefix.replaceAll(".xml$","");
  }

  public static void main ( String [] args ) {

    // Options handling
    try {
      if (args[0].equals("-t")) {
        Options.t = true;
        Options.filename = args[1];
        Options.table_prefix = extractTableName(Options.filename);
      } else if (args[0].equals("-p")) {
        Options.p = true;
        Options.filename = args[1];
        Options.table_prefix = extractTableName(Options.filename);
      } else if (args[0].equals("-d")) {
        Options.d = true;
        Options.filename = args[1];
        Options.table_prefix = extractTableName(Options.filename);
        Options.database_server = args[2];
        Options.database_name = args[3];
        Options.database_username = args[4];
        Options.database_password =  args[5];
      } else if (args[0].equals("-q")) {
        Options.q = true;
        Options.filename = args[5];
        Options.database_server = args[1];
        Options.database_name = args[2];
        Options.database_username = args[3];
        Options.database_password =  args[4];
      } else if (args[0].equals("-c")) {
        Options.c = true;
        Options.database_server = args[1];
        Options.database_name = args[2];
        Options.database_username = args[3];
        Options.database_password =  args[4];
      } else {
        System.out.println(args[0]+" is not a valid option, go and read the spec");
        System.exit(1);
      }
    } catch (Exception e) {
      System.out.println("Invalid arguments supplied");
      System.exit(1);
    }

    SAXHandlers saxHandler = new SAXHandlers();
    XMLReader myReader;
    String parserName = "org.apache.xerces.parsers.SAXParser";

    // process a file into memory if the option is -t, -p or -d
    if(Options.t == true || Options.p == true || Options.d == true) {
      try {
        myReader = XMLReaderFactory.createXMLReader(parserName);
        myReader.setContentHandler(saxHandler);
        myReader.setErrorHandler(saxHandler);
        // build an internal representation of the database into memory
        myReader.parse(Options.filename);
      } catch (Exception ex) {
        ex.printStackTrace();
        System.exit(1);
      }
    }

    // Option -t
    if (Options.t == true) {
      System.out.println(Options.table_prefix+"_tbl:");
      for(int i = 0; i < saxHandler.table.size(); ++i) {
        TableRow ref = saxHandler.table.get(i);
        System.out.println(ref.pre+"\t"+ref.post+"\t"+ref.level+"\t"+ref.tag+"\t"+ref.text);
      }

      System.out.println(Options.table_prefix+"_attr:");
      for(int i = 0; i < saxHandler.attr.size(); ++i) {
        AttrRow ref = saxHandler.attr.get(i);
        System.out.println(ref.pre+"\t"+ref.attr+"\t"+ref.value);
      }
    }

    // Option -p
    if (Options.p == true) {
      System.out.println("CREATE TABLE "+Options.table_prefix+"_tbl (");
      System.out.println("  pre INTEGER PRIMARY KEY,");
      System.out.println("  post INTEGER,");
      System.out.println("  level INTEGER,");
      System.out.println("  tag VARCHAR(256),");
      System.out.println("  text VARCHAR(1000)");
      System.out.println(");");
      System.out.println();
      System.out.println("CREATE TABLE "+Options.table_prefix+"_attr (");
      System.out.println("  pre INTEGER REFERENCES "+Options.table_prefix+"_tbl (pre),");
      System.out.println("  attr VARCHAR(256),");
      System.out.println("  value VARCHAR(1000)");
      System.out.println(");");
      //System.out.println();
      for(int i = 0; i < saxHandler.table.size(); ++i) {
        if (i==0) { System.out.println(); }
        System.out.println("INSERT INTO "+Options.table_prefix+"_tbl (pre, post, level, tag, text)");
        TableRow ref = saxHandler.table.get(i);
        System.out.print("VALUES ("+ref.pre+", "+ref.post+", "+ref.level+", ");
        if (ref.tag!=null) {
          System.out.print("\""+ref.tag+"\""+", ");
        } else {
          System.out.print(ref.tag+", ");
        }
        if (ref.text!=null) {
          System.out.println("\""+ref.text+"\""+");");
        } else {
          System.out.println(ref.text+");");
        }
        if (i!=saxHandler.table.size()-1) {
          System.out.println();
        }
      }
      for(int i = 0; i < saxHandler.attr.size(); ++i) {
        if (i==0) { System.out.println(); }
        System.out.println("INSERT INTO "+Options.table_prefix+"_attr (pre, attr, value)");
        AttrRow ref = saxHandler.attr.get(i);
        System.out.print("VALUES ("+ref.pre+", ");
        if (ref.attr!=null) {
          System.out.print("\""+ref.attr+"\""+", ");
        } else {
          System.out.print(ref.attr+", ");
        }
        if (ref.value!=null) {
          System.out.println("\""+ref.value+"\""+");");
        } else {
          System.out.println(ref.value+");");
        }
        if (i!=saxHandler.attr.size()-1) {
          System.out.println();
        }
      }
    }

    Connection conn = null;

    if (Options.d == true || Options.q == true || Options.c == true) {
      // make a connection to the database
      try {
        String url = "jdbc:mysql://"+Options.database_server+"/"+Options.database_name;
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection (url, Options.database_username, Options.database_password);
      } catch (Exception e) {
        if (conn != null) {
          try { conn.close(); } catch (Exception ex) {}
        }
      }
    }

    // Option -d
    if (Options.d == true) {
      try {
        Statement stmt = conn.createStatement();
        // create the table
        stmt.executeUpdate("CREATE TABLE "+Options.student_prefix+"_"+Options.table_prefix+
                           "_tbl (pre INTEGER PRIMARY KEY, post INTEGER, level INTEGER, tag VARCHAR(256), text VARCHAR(1000));");
        // create the attributes
        stmt.executeUpdate("CREATE TABLE "+Options.student_prefix+"_"+Options.table_prefix+
                           "_attr (pre INTEGER REFERENCES "+Options.student_prefix+"_"+Options.table_prefix+
                           "_tbl (pre), attr VARCHAR(256), value VARCHAR(1000));");

        // insert the rows into the table
        for(int i = 0; i < saxHandler.table.size(); ++i) {
          String insert_statement = "";
          insert_statement += "INSERT INTO "+Options.student_prefix+"_"+Options.table_prefix+"_tbl (pre, post, level, tag, text)";
          TableRow ref = saxHandler.table.get(i);
          insert_statement += "VALUES ("+ref.pre+", "+ref.post+", "+ref.level+", ";
          if (ref.tag!=null) {
            insert_statement += "'"+ref.tag+"'"+", ";
          } else {
            insert_statement += ref.tag+", ";
          }
          if (ref.text!=null) {
            insert_statement += "'"+ref.text+"'"+");";
          } else {
            insert_statement += ref.text+");";
          }
          stmt.executeUpdate(insert_statement);
        }

        // insert the rows into the attribute table
        for(int i = 0; i < saxHandler.attr.size(); ++i) {
          String insert_statement = "";
          insert_statement += "INSERT INTO "+Options.student_prefix+"_"+Options.table_prefix+"_attr (pre, attr, value)";
          AttrRow ref = saxHandler.attr.get(i);
          insert_statement += "VALUES ("+ref.pre+", ";
          if (ref.attr!=null) {
            insert_statement += "\""+ref.attr+"\""+", ";
          } else {
            insert_statement += ref.attr+", ";
          }
          if (ref.value!=null) {
            insert_statement += "\""+ref.value+"\""+");";
          } else {
            insert_statement += ref.value+");";
          }
          stmt.executeUpdate(insert_statement);
        }

        // Retrieve the table from the database and print it out
        System.out.println(Options.student_prefix+"_"+Options.table_prefix+"_tbl:");
        ResultSet rs = stmt.executeQuery("SELECT * FROM "+Options.student_prefix+"_"+Options.table_prefix+"_tbl ORDER BY tag,pre;");
        while (rs.next()) {
          System.out.println(rs.getInt("pre")+"\t"+rs.getInt("post")+"\t"+rs.getInt("level")+"\t"+rs.getString("tag")+"\t"+rs.getString("text"));
        }

        // Retrieve the attr table from the database and print it out
        System.out.println(Options.student_prefix+"_"+Options.table_prefix+"_attr:");
        rs = stmt.executeQuery("SELECT * FROM "+Options.student_prefix+"_"+Options.table_prefix+"_attr ORDER BY attr,pre;");
        while (rs.next()) {
          System.out.println(rs.getInt("pre")+"\t"+rs.getString("attr")+"\t"+rs.getString("value"));
        }

        // close the result set connector
        rs.close();
        // close the statement connector
        stmt.close();

      } catch (Exception e) {
        e.printStackTrace();
      }
    }

    // Option -q
    if (Options.q == true) {
      try {
        Statement stmt = conn.createStatement();

        FileInputStream filestream = new FileInputStream(Options.filename);
        byte b[]= new byte[filestream.available()];
        filestream.read(b);
        String query = new String(b);

        query = query.trim()+";";
        query = query.replaceAll("\\s+(\\w+)_tbl"," "+Options.student_prefix+"_"+"$1_tbl").replaceAll("\\s+(\\w+)_attr"," "+Options.student_prefix+"_"+"$1_attr");

        Pattern pattern = Pattern.compile("z3131666_\\w+_tbl");
        Matcher matcher = pattern.matcher(query);
        boolean matchFound = matcher.find();
        matcher.group();
        int start = matcher.start();
        int end = matcher.end();
        String table_name = query.substring(start, end);
        String attr_name = table_name.replaceAll("_tbl$","_attr");

        ResultSet rs = stmt.executeQuery(query);

        // spec says i'll get one node, so let's grab the pre and post values from it
        int pre = -1;
        int post = -1;
        while(rs.next()) {
          pre = rs.getInt("pre");
          post = rs.getInt("post");
          break;
        }
        rs.close();

        // get the tree
        ResultSet rs1 = stmt.executeQuery("SELECT * from "+table_name+" WHERE pre >= "
                                          +pre+" AND post <= "+post+" ORDER by pre;");


        ArrayList<Pair> stack = new ArrayList<Pair>();

        int current_level = -1;
        Boolean flag = false;

        // print the tree
        while(rs1.next()) {
          // lets get the lowest level from the tree (root is the node captured from above)
          if (flag == false) {
            current_level = rs1.getInt("level");
            flag = true;
          }
          int object_pre     = rs1.getInt("pre");
          int object_post    = rs1.getInt("post");
          int object_level   = rs1.getInt("level");
          String object_tag  = rs1.getString("tag");
          String object_text = rs1.getString("text");
          if (object_level <= current_level) {
            while(true) {
              if (stack.size() != 0) {
                Pair ref = stack.get(stack.size()-1);
                if (ref.level < object_level) {
                  break;
                }
                Pair actual = stack.remove(stack.size()-1);
                if (actual.tag != null) {
                  System.out.println("</"+actual.tag+">");
                }
              } else {
                break;
              }
            }
          }
          if (object_text == null) {
            System.out.print("<"+object_tag);
            // print the attributes
            Statement stmts = conn.createStatement();
            ResultSet rs5 = stmts.executeQuery("SELECT * from "+attr_name+" WHERE pre = "+object_pre+" ORDER by pre;");
            while(rs5.next()) {
              System.out.print(" "+rs5.getString("attr")+"=\""+rs5.getString("value")+"\"");
            }
            rs5.close();
            stmts.close();
            System.out.println(">");
          } else {
            System.out.println(object_text);
          }
          stack.add(new Pair(object_level, object_tag));
          current_level = object_level;
        }
        for(int i = stack.size()-1; i > -1; i--) {
          Pair actual = stack.remove(i);
          if (actual.tag != null) {
            System.out.println("</"+actual.tag+">");
          }
        }
        rs1.close();
        stmt.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }

    // Option -c
    if (Options.c == true) {
      try {
        Statement stmt = conn.createStatement();
        ResultSet rs7 = stmt.executeQuery("SHOW TABLES LIKE 'z3131666%';");
        while (rs7.next()) {
          String table_to_drop = rs7.getString(1);
          Statement stmts = conn.createStatement();
          stmts.executeUpdate("DROP TABLE IF EXISTS "+table_to_drop+";");
          stmts.close();
        }
        rs7.close();
        stmt.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }

    // close the connection with the database
    if (conn != null) {
      try { conn.close(); } catch (Exception e) {  }
    }


  }
}

///////////////////////////////////////////////////////////////////////////////