/*
 * $Id: JGraphSQLBackend.java,v 1.1.1.1 2005/08/06 05:26:45 gaudenz Exp $
 * 
 * Copyright (c) 2001-2005, Gaudenz Alder
 * 
 * See LICENSE file in distribution for licensing details of this source file
 */
package com.jgraph.example.adapter;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.jgraph.graph.DefaultGraphCell;
import org.jgraph.graph.DefaultGraphModel;
import org.jgraph.graph.GraphConstants;

/**
 * Maps changes to the graph model to a custom backend, eg. an SQL database.
 * (This should use prepared statements.)
 */
public class JGraphSQLBackend implements JGraphAdapterBackend {

	protected static final String NO_ID = "0";

	protected int lastId = 0;

	protected boolean trxInProgress = false;

	protected Object vertexPrototype, edgePrototype;

	protected Connection conn;

	public JGraphSQLBackend(Connection conn, Object vertexPrototype,
			Object edgePrototype) throws ClassNotFoundException, SQLException {
		this.conn = conn;
		this.vertexPrototype = vertexPrototype;
		this.edgePrototype = edgePrototype;
		try {
			update("SET AUTOCOMMIT FALSE");
			update("CREATE TABLE entity ( id INTEGER, parent_id INTEGER)");
			update("CREATE TABLE relation ( entity_id INTEGER, source_id INTEGER, target_id INTEGER)");
			update("CREATE TABLE property ( entity_id INTEGER, key VARCHAR(256), value VARCHAR(256))");
			update("CREATE INDEX prp_entity_idx ON property (entity_id)");
			update("CREATE INDEX rel_entity_idx ON relation (entity_id)");
			update("CREATE INDEX rel_source_idx ON relation (source_id)");
			update("CREATE INDEX rel_target_idx ON relation (target_id)");
			// Triggers to update the model if the db changes...
			// update("CREATE TRIGGER test_trigger AFTER UPDATE ON property CALL
			// org.jgraph.studio.business.JGraphStudioSQLBackend.TestTrigger");
			update("COMMIT");
		} catch (SQLException ex2) {
			// this will have no effect on the db
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void commit() throws Exception {
		if (trxInProgress) {
			update("COMMIT");
			trxInProgress = false;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void rollback() throws Exception {
		if (trxInProgress) {
			update("ROLLBACK");
			trxInProgress = false;
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#vertexAdded(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object)
	 */
	public void vertexAdded(JGraphAdapterModel sender, Object vertex,
			boolean validate) throws Exception {
		if (!validate)
			objectAdded(sender.getValue(vertex));
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void edgeAdded(JGraphAdapterModel sender, Object object,
			Object source, Object target, boolean validate) throws Exception {
		if (!validate) {
			Object id = objectAdded(sender.getValue(object));
			if (id != null)
				update("INSERT INTO relation(entity_id, source_id, target_id) VALUES ("
						+ id + ", 0, 0)");
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	protected Object objectAdded(Object object) throws SQLException {
		if (object instanceof JGraphSQLEntity) {
			JGraphSQLEntity entity = (JGraphSQLEntity) object;
			if (entity.getID() == null) {
				entity.setID(getNextEntityId());
				update("INSERT INTO entity(id) VALUES (" + entity.getID() + ")");
				Iterator it = entity.getProperties().entrySet().iterator();
				while (it.hasNext()) {
					Map.Entry entry = (Map.Entry) it.next();
					update("INSERT INTO property(entity_id, key, value) VALUES ("
							+ entity.getID()
							+ ", '"
							+ entry.getKey()
							+ "', '"
							+ entry.getValue() + "')");
				}
			}
			return entity.getID();
		}
		return null;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void cellRemoved(JGraphAdapterModel sender, Object cell,
			boolean validate) throws Exception {
		Object userObj = sender.getValue(cell);
		if (!validate && userObj instanceof JGraphSQLEntity) {
			JGraphSQLEntity entity = (JGraphSQLEntity) userObj;
			Object entityId = entity.getID();
			update("DELETE FROM entity WHERE id = " + entityId);
			update("DELETE FROM relation WHERE entity_id = " + entityId);
			update("DELETE FROM property WHERE entity_id = " + entityId);
			update("UPDATE entity SET parent_id = " + NO_ID
					+ " WHERE parent_id = " + entityId);
			entity.setID(null);
		}
	}

	public void parentChanged(JGraphAdapterModel sender, Object child,
			Object parent, boolean validate) throws Exception {
		Object childObj = sender.getValue(child);
		Object parentObj = sender.getValue(parent);
		if (!validate && childObj instanceof JGraphSQLEntity
				&& parentObj instanceof JGraphSQLEntity) {
			JGraphSQLEntity childEntity = (JGraphSQLEntity) childObj;
			JGraphSQLEntity parentEntity = (JGraphSQLEntity) parentObj;
			update("UPDATE entity SET parent_id = " + parentEntity.getID()
					+ " where id = " + childEntity.getID());
			childEntity.setParent(parentEntity);
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void sourceChanged(JGraphAdapterModel sender, Object edge,
			Object source, boolean validate) throws Exception {
		Object object = sender.getValue(edge);
		source = sender.getParentUserObject(source);
		if (!validate && object instanceof JGraphSQLRelation) {
			JGraphSQLRelation relation = (JGraphSQLRelation) object;
			Object id = NO_ID;
			if (source instanceof JGraphSQLEntity) {
				relation.setSource((JGraphSQLEntity) source);
				id = relation.getSource().getID();
			} else
				relation.setSource(null);
			update("UPDATE relation SET source_id = " + id
					+ " where entity_id = " + relation.getID());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void targetChanged(JGraphAdapterModel sender, Object edge,
			Object target, boolean validate) throws Exception {
		Object object = sender.getValue(edge);
		target = sender.getParentUserObject(target);
		if (!validate && object instanceof JGraphSQLRelation) {
			JGraphSQLRelation relation = (JGraphSQLRelation) object;
			Object id = NO_ID;
			if (target instanceof JGraphSQLEntity) {
				relation.setTarget((JGraphSQLEntity) target);
				id = relation.getTarget().getID();
			} else
				relation.setTarget(null);
			update("UPDATE relation SET target_id = " + id
					+ " where entity_id = " + relation.getID());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void attributesChanged(JGraphAdapterModel sender, Object cell,
			Map attributes, boolean validate) throws Exception {
		if (!validate) {
			if (cell instanceof JGraphSQLEntity) {
				propertiesChanged(cell, attributes);
			} else {
				Object value = GraphConstants.getValue(attributes);
				if (value != null) {
					Map properties = new Hashtable();
					properties.put("value", value);
					propertiesChanged(sender.getValue(cell),
							properties);
				}
			}
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void propertiesChanged(Object object, Map properties)
			throws Exception {
		if (object instanceof JGraphSQLEntity) {
			JGraphSQLEntity entity = (JGraphSQLEntity) object;
			Iterator it = properties.entrySet().iterator();
			while (it.hasNext()) {
				Map.Entry entry = (Map.Entry) it.next();
				// TODO: Handle new attributes here?
				propertyChanged(entity, entry.getKey(), entry.getValue());
			}
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void propertyChanged(JGraphSQLEntity entity, Object key, Object value)
			throws Exception {
		update("UPDATE property SET value = '" + value + "' where key='" + key
				+ "' AND entity_id = " + entity.getID());
	}

	/*
	 * This method is called from the Studio UI. (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void propertyAdded(JGraphSQLEntity entity, Object key, Object value)
			throws Exception {
		update("INSERT INTO property(entity_id, key, value) VALUES ("
				+ entity.getID() + ", '" + key + "', '" + value + "')");
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public Collection findVertices(JGraphAdapterModel sender, Object query,
			Object parent) throws SQLException {
		Object object = sender.getValue(parent);
		String parentClause = (object instanceof JGraphSQLEntity) ? " and parent_id = "
				+ ((JGraphSQLEntity) object).getID()
				: "";
		String whereClause = "where "
				+ createFilter(query)
				+ parentClause
				+ " and not exists (select 1 from relation where relation.entity_id = property.entity_id)";
		return findCells(sender, whereClause);
	}

	protected Collection findCells(JGraphAdapterModel context,
			String whereClause) throws SQLException {
		Collection objs = find(whereClause);
		List result = new ArrayList(objs.size());
		Iterator it = objs.iterator();
		while (it.hasNext()) {
			JGraphSQLEntity entity = (JGraphSQLEntity) it.next();
			result.add(createCell(context, entity));
		}
		return result;
	}

	public Object getParent(Object object) {
		if (object instanceof JGraphSQLEntity)
			return ((JGraphSQLEntity) object).getParent();
		return null;
	}

	public Object getSource(Object object) {
		if (object instanceof JGraphSQLRelation)
			return ((JGraphSQLRelation) object).getSource();
		return null;
	}

	public Object getTarget(Object object) {
		if (object instanceof JGraphSQLRelation)
			return ((JGraphSQLRelation) object).getTarget();
		return null;
	}

	// All relations with cells (parent, source, target) must be
	// set when inserting the cells. the backend only sets the relations
	// on the business-object level.
	public Object createCell(JGraphAdapterModel context, JGraphSQLEntity entity)
			throws SQLException {
		Object prototype = (entity instanceof JGraphSQLRelation) ? edgePrototype
				: vertexPrototype;

		// TODO: Do not create if mapped?
		Object vertex = DefaultGraphModel.cloneCell(context, prototype);
		if (vertex instanceof DefaultGraphCell) {
			DefaultGraphCell cell = (DefaultGraphCell) vertex;
			cell.setUserObject(entity);
		}
		return vertex;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public Collection findEdges(JGraphAdapterModel sender, Object query,
			Object parent, Object source, Object target, boolean directed)
			throws SQLException {
		Object object = sender.getValue(parent);
		source = sender.getValue(source);
		target = sender.getValue(target);
		String parentClause = (object instanceof JGraphSQLEntity) ? " and parent_id = "
				+ ((JGraphSQLEntity) object).getID()
				: "";
		String edgeWhereClause = "1=1 ";
		if (source instanceof JGraphSQLEntity) {
			JGraphSQLEntity entity = (JGraphSQLEntity) source;
			edgeWhereClause += "and source_id = " + entity.getID() + " ";
		}
		if (target instanceof JGraphSQLEntity) {
			JGraphSQLEntity entity = (JGraphSQLEntity) target;
			edgeWhereClause += "and target_id = " + entity.getID() + " ";
		} else if (!directed && source != null) { // TODO: Shortcut for now...
			JGraphSQLEntity entity = (JGraphSQLEntity) source;
			edgeWhereClause += "or target_id = " + entity.getID() + " ";
		}
		String whereClause = "where "
				+ createFilter(query)
				+ parentClause
				+ " and exists (select 1 from relation where relation.entity_id = property.entity_id and ("
				+ edgeWhereClause + "))";
		return findCells(sender, whereClause);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	protected String createFilter(Object query) {
		if (query != null && query.toString().length() > 0) {
			String[] tokens = query.toString().split(" ");
			if (tokens.length > 0) {
				String filter = "soundex(value) IN (soundex('" + tokens[0]
						+ "')";
				for (int i = 1; i < tokens.length; i++)
					filter += ", soundex('" + tokens[i] + "')";
				return filter + ")";
			}
		}
		return "1=1";
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	protected Collection find(String whereClause) throws SQLException {
		Statement st = (conn != null) ? conn.createStatement() : null;
		String sql = "select entity_id, count(entity_id) as ranking from property "
				+ whereClause + " group by entity_id order by ranking desc";
		println("SQL: " + sql);
		ResultSet rs = (st != null) ? st.executeQuery(sql) : null;
		List objects = new LinkedList();
		while (rs != null && rs.next()) {
			Object object = get(rs.getObject(1));
			if (object != null)
				objects.add(object);
		}
		if (st != null)
			st.close();
		return objects;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	protected synchronized Object getNextEntityId() {
		int id = lastId;
		try {
			Statement st = (conn != null) ? conn.createStatement() : null;
			ResultSet rs = (st != null) ? st
					.executeQuery("select max(id) from entity") : null;
			if (st != null)
				st.close();
			if (rs != null && rs.next())
				id = rs.getInt(1) + 1;
			else
				// use local var for ids
				lastId++;
		} catch (SQLException e) {
			// ignore and return lastId
		}
		return new Integer(id);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public JGraphSQLEntity get(Object id) throws SQLException {
		Statement st = (conn != null) ? conn.createStatement() : null;
		JGraphSQLEntity entity = null;
		ResultSet rs = st
				.executeQuery("select id, parent_id from entity where id = "
						+ id);

		Object entityId = null;
		JGraphSQLEntity parent = null;
		if (rs.next()) {
			entityId = rs.getObject(1);
			parent = get(rs.getObject(2));
		}

		// Assumes that the entity exists
		rs = st
				.executeQuery("select source_id, target_id from relation where entity_id = "
						+ id);

		// Fetch the source and target, or create a vertex
		if (rs.next()) {
			JGraphSQLEntity source = get(rs.getObject(1));
			JGraphSQLEntity target = get(rs.getObject(2));
			entity = new JGraphSQLRelation(entityId, parent, source, target); // TODO.
			// Add
			// Parent
		} else {
			entity = new JGraphSQLEntity(entityId, parent);
		}

		// Fill-in the properties
		rs = st
				.executeQuery("select key, value from property where entity_id = "
						+ id);
		Hashtable properties = new Hashtable();
		while (rs.next()) {
			String key = rs.getString(1);
			String value = rs.getString(2);
			if (key != null) {
				properties.put(key, value);
			}
		}
		st.close();
		entity.setProperties(properties);

		return entity;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public synchronized void query(String expression) throws SQLException {
		Statement st = (conn != null) ? conn.createStatement() : null;
		ResultSet rs = (st != null) ? st.executeQuery(expression) : null;
		dump(rs);
		st.close();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public void dump(ResultSet rs) throws SQLException {
		ResultSetMetaData meta = rs.getMetaData();
		int colmax = meta.getColumnCount();
		while (rs.next()) {
			for (int i = 0; i < colmax; ++i) {
				Object o = rs.getObject(i + 1);
				if (o != null)
					System.out.print(o.toString() + " ");
			}
			println(" ");
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.jgraph.editor.business.JGraphBusinessBackend#valueForCellChanged(org.jgraph.editor.business.JGraphBusinessModel,
	 *      java.lang.Object, java.lang.Object)
	 */
	public synchronized void update(String expression) throws SQLException {
		trxInProgress = true;
		Statement st = (conn != null) ? conn.createStatement() : null;
		int i = (st != null) ? st.executeUpdate(expression) : 0;
		if (i == -1) {
			println("db error : " + expression);
		} else {
			println("SQL: " + expression);
		}
		if (st != null)
			st.close();
	}

	protected static void println(String msg) {
		JGraphAdapterExample.println(msg);
	}

}