import java.io.*;
import java.sql.*;
import java.text.*;

/** object wrapper for the database.
 * @author anders
 *
 * basically just isolates all the database specific code so we could,
 * if we wanted to, change the entire database backend simply by
 * changing this one class.
 */

public class DBWrapper {
    
    Connection db = null;	// The connection to the database
    Statement  st = null;	// Our statement to run queries with
    //    PrintWriter out = res.getWriter();  
	
    String url = "jdbc:postgresql:network"; 
    String usr = "anders";
    String pwd = "foo";

    /** default constructor.
     * @author anders
     */
    public DBWrapper(){
	super();
    }

    /** establishes connection to database.
     * @author anders
     * @return true if connection established, false if problems
     */
    public boolean connect(){
	try {
	    // Load the driver
	    Class.forName("org.postgresql.Driver");
	    
	    // Connect to database
	    db = DriverManager.getConnection(url, usr, pwd);
	    return true;
	} 
	catch(ClassNotFoundException e) {
	    System.out.println("Couldn't load database driver: " + e.getMessage());
	    return false;
	}
	catch(SQLException e) {
	    System.out.println("SQLException caught: " + e.getMessage());
	    return false;
	}
		
    }

    /** gets an xml formatted list of edge data.
     * @author anders
     * @return xml formatted list of edge data
     */
    public String getEdgeList(){
	String temp = "";
	try {
	    st = db.createStatement();

	    ResultSet rs = st.executeQuery("select source,dest,weight,class from example");

	    while(rs.next()) {
		String source = "<source>" + rs.getString("source") + "</source>";
		String dest   = "<dest>" + rs.getString("dest") + "</dest>";
		String weight = rs.getString("weight");
		weight = (weight == null)
		    ? "<weight>50</weight>" : "<weight>" + weight + "</weight>";
		String e_class = rs.getString("class");
		e_class = (e_class == null)
		    ? "<class>default</class>" : "<class>" + e_class + "</class>";
		temp += "<edge>" + source + dest + weight + e_class + "</edge>\t";
	    }
	}
    
	catch(SQLException e) {
	    return "SQLException caught: " + e.getMessage();
	}
	
	return "<edge-list>" + temp + "</edge-list>";
    }

    /** gets a list of the node data.
     * @author anders
     * @return xml formatted list of node data
     */
    public String getNodeList(){
	String temp = "";
	try {
	    st = db.createStatement();

	    ResultSet rs = st.executeQuery("select name,class from nodes");

	    while(rs.next()) {
		String name = rs.getString("name");
		String n_class = rs.getString("class");
		
		temp += "<node><name>"
		    + name +
		    "</name>"
		    + "<class>"
		    + n_class
		    + "</class></node>\t";
	    }
	}
    
	catch(SQLException e) {
	    return "<error type='SQLException'>" + e.getMessage() + "</error>";
	}
	
	return "<node-list>" + temp + "</node-list>";
    }

    /** removes an edge from the database.
     * @param source source of edge to remove
     * @param dest destination of edge
     * @param e_class class of edge (in case there are multiple edges between the same two nodes)
     */
    public void removeEdge(String source, String dest, String e_class){
	System.out.println("removing edge");
	try {
	    st = db.createStatement();
	    String query = "delete from example where source='"
		+ source + "' AND dest='" + dest + "' AND class='" + e_class + "'";
	    st.executeUpdate(query);
	} catch(SQLException e) {
	    System.out.println("SQL error: " + e);
	    return;
	}
    }

    /** adds an edge to the database.
     * @param source source node of edge
     * @param dest destination node of edge
     * @param e_class class of edge
     */
    public void addEdge(String source, String dest, String e_class){
	try {
	    st = db.createStatement();
	    st.executeUpdate("insert into example (source,dest,class) VALUES ('"
					   + source + "','"
					   + dest   + "','"
					   + e_class + "')");
	} catch(SQLException e) {
	    return;
	}
    }

    /** adds a node to the database.
     * @param name label of node
     * @param n_class class of node
     */
    public void addNode (String name, String n_class) {
	try {
	    st = db.createStatement();
	    String query = "insert into nodes (name,class)"
		+ " VALUES ('" + name + "','" + n_class + "')";
	    st.executeUpdate(query);
	} catch (SQLException e) {
	    System.out.println("SQL exception: " + e);
	    return;
	}
    }

    /** removes a node from the database.
     * @param name name of node to remove.
     *
     * also removes any edges that were attached to the node.
     */
    
    public void removeNode(String name){
	try {
	    st = db.createStatement();
	    st.executeUpdate("delete from nodes where name='" +
					   name + "'");
	    st.executeUpdate("delete from example where source='" +
				 name + "'");
	    st.executeUpdate("delete from example where dest='" +
				 name + "'");
	} catch (SQLException e){
	    return;
	}
    }
    
    /** disconnects from the database.
     * @author anders
     */
    public void disconnect() {
	try {
	    if (db != null) db.close();
	}
	catch (SQLException ignored) {}
    }
}