package ws.prova.sql;

/*
 * Copyright (C) 2002-2007 <a href="mailto:a.kozlenkov@city.ac.uk">Alex Kozlenkov</a>
 * Copyright (C) 1998-2002 <a href="mailto:jens.dietrich@unforgettable.com">Jens Dietrich</a>
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

import org.mandarax.kernel.*;
import org.mandarax.sql.SQLClauseSet;
import org.mandarax.sql.SQLPredicate;
import org.mandarax.util.logging.LogCategories;
import ws.prova.RListUtils;
import ws.prova.reference.ExceptionHandler;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.*;

/**
 * Wrapper around extended SQL result sets used in Prova
 * <p>Title: Prova</p>
 * <p>Description: Wrapper around extended SQL result sets used in Prova</p>
 * <p>Copyright: Copyright (C) 2002-2007</p>
 * <p>Company: City University, London, United Kingdom</p>
 *
 * @author Alex Kozlenkov
 * @author <A HREF="mailto:mandarax@jbdietrich.com">Jens Dietrich</A>
 * @version 2.0 <25 March 2007>
 */
public class SQLExtendedClauseSet extends AbstractPropertiesSupport implements ClauseSet, LogCategories {

	private String whereClause = "";
	private SQLPredicate predicate = null;
	private String query = null;
	private List cache = null;
	private long cacheTimeout = NO_CACHE;
	private long cacheTimestamp = 0;
	public static final int NO_CACHE = -1;
	private boolean closeConnection = false;
//	private boolean allFields = false;

	/**
	 * Constructor.
	 */
	public SQLExtendedClauseSet() {
		super();
	}

	/**
	 * Constructor.
	 *
	 * @param p SQLPredicate a predicate
	 */
	public SQLExtendedClauseSet(SQLPredicate p) {
		this(p, NO_CACHE);
	}

	/**
	 * Constructor
	 *
	 * @param p     SQLPredicate a predicate
	 * @param where String a where clause
	 */
	public SQLExtendedClauseSet(SQLPredicate p, String where) {
		this(p, where, NO_CACHE);
	}

	/**
	 * Constructor.
	 *
	 * @param p       a predicate
	 * @param timeout the timeout after which the cache expires, if NO_CACHE , no caching is done
	 */
	public SQLExtendedClauseSet(SQLPredicate p, long timeout) {
		this(p, "", timeout);
	}

	/**
	 * Constructor.
	 *
	 * @param p       a predicate
	 * @param where   a where clause
	 * @param timeout the timeout after which the cache expires, if NO_CACHE , no caching is done
	 */
	public SQLExtendedClauseSet(SQLPredicate p, String where, long timeout) {
		super();
		predicate = p;
		whereClause = where;
		cacheTimeout = timeout;
	}

	/**
	 * Add a clause set listener.
	 *
	 * @param l the listener
	 */
	public void addClauseSetChangeListener(ClauseSetChangeListener l) {
	}

	/**
	 * Should not be called
	 *
	 * @return
	 * @throws ClauseSetException
	 */
	public org.mandarax.util.ClauseIterator clauses() throws ClauseSetException {
		throw new ClauseSetException("This method should not have been called");
	}

	/**
	 * Iterate over all clauses
	 *
	 * @param goal Clause
	 * @return ClauseIterator a clause iterator
	 * @throws ClauseSetException
	 */
	public org.mandarax.util.ClauseIterator clauses(Clause goal, Object additionalParameter) throws ClauseSetException {
		ExceptionHandler exhandler = (ExceptionHandler) additionalParameter;
		ResultSet rs = null;
		Fact f = (Fact) goal.getNegativeLiterals().get(0);
		Term t = null;
		List field2var = new ArrayList();
		Map function2alias = new HashMap();
//		ArrayList tailColumns = new ArrayList();
		String tailVar = null;
		int tailStartsAt = 1000000;
		Statement stmt = null;
		try {
			int functions = 0;
			List fields = new ArrayList();
			if (false/*useCache()*/) {
				return null;//new CachedClauseIterator(cache);
			} else {
				Connection conn = null;
				t = f.getTerms()[0];
				String sFields = "";
				// WHERE or HAVING clause is accumulated in sWH[0] or sWH[1], respectively
				String[] sWH = new String[]{"", ""};
				String sOptions = "";
				String sTable = "";
				if (t instanceof ComplexTerm) {
					ComplexTerm ct = (ComplexTerm) t;
					List objs = new ArrayList();
					String sMsg = RListUtils.decode(ct, objs);
					String[] s0 = sMsg.split(",\\[");
					sTable = s0[0].substring(2);
					boolean isConn = (s0[0].charAt(1) == '%');
					if (isConn) {
						// There is possibly a Connection object passed as an argument
						int i = 1;
						int commapos = sTable.indexOf(',');
						int indexConn = Integer.parseInt(sTable.substring(0, commapos));
						sTable = sTable.substring(commapos + 1);
						if (objs.get(indexConn) instanceof Connection) {
							// Yes, it is a Connection, so the database is known and Connection is already established
							conn = (Connection) objs.get(indexConn);
							objs.remove(indexConn);
							sTable = sTable.replaceAll("\"", "");
						}
					} else {
						throw new ClauseSetException("Exception building clause from SQL");
					}
					String[] s1;
					String s, sa, sb;
					for (int i = 1; i < s0.length; i++) {
						field2var.add(null);
						s1 = s0[i].split(",");
						int iWH = 0;
						int k = 1;
						String alias = null;
						if (s1.length > 2) {
							// An SQL-function used in field name
							String function = s1[0].substring(2, s1[0].length() - 1);
							sa = function + "(";
							for (k = 1; k < s1.length - 2; k++) {
								sa += s1[k].replaceAll("\"", "");
							}
							s1[k] = s1[k].replaceAll("\"", "");
							sa += s1[k].substring(0, s1[k].length() - 1) + ')';
							k++;
							alias = new String("alias" + functions++);
							function2alias.put(sa, alias);
							sa += " as " + alias;
							iWH = 1;
						} else {
							sa = s1[0].substring(1, s1[0].length() - 1);
							if (sa.equalsIgnoreCase("options") || sa.equalsIgnoreCase("where") || sa.equalsIgnoreCase("having")) {
								// Options or WHERE or HAVING string
								sb = s1[k].split("\"]")[0];
								// Strip double quotes and enforce single quotes inside
								sb = sb.substring(1, sb.length()).replaceAll("\"", "'");
								// Replace function calls by aliases if they are those
								for (Iterator it = function2alias.keySet().iterator(); it.hasNext();) {
									String code = (String) it.next();
									String replacement = (String) function2alias.get(code);
									int pos = sb.indexOf(code);
									if (pos >= 0) {
										String tail = sb.substring(pos + code.length());
										sb = sb.substring(0, pos) + replacement + tail;
									}
								}
								if (sa.equalsIgnoreCase("options")) {
									// Append to Options string
									sOptions += " " + sb;
								} else if (sa.equalsIgnoreCase("where")) {
									// Append to WHERE clause
									if (sWH[0].length() != 0) {
										sWH[0] += " and ";
									}
									sWH[0] += " " + sb;
								} else {
									// Append to HAVING clause
									if (sWH[1].length() != 0) {
										sWH[1] += " and ";
									}
									sWH[1] += " " + sb;
								}
								continue;
							}
							String sax = sa;
							int pos_sp = sa.indexOf(" ");
							if (pos_sp >= 0) {
								sax = sa.substring(pos_sp + 1);
							}
							fields.add(sax);
						}
						if (i > 1) {
							sFields += ',';
						}
						sFields += sa;
						sb = s1[k].split("]")[0];
						//this causes infinite loop! sb = sb.replaceAll("\"", "'");
						char start = sb.charAt(0);
						if (start == '%') {
							// It is a constant of some class
							if (sWH[iWH].length() != 0) {
								sWH[iWH] += " and ";
							}
							int nobj = Integer.parseInt(sb.substring(1));
							String sW = objs.get(nobj).toString();
							if (sW.charAt(0) != '\'' && sW.charAt(0) != '\"')
								sW = '\'' + sW + '\'';
							sWH[iWH] += sa + "=" + sW;
						} else if (start == '"') {
							// It is a text constant surrounded by quotes
							if (sWH[iWH].length() != 0) {
								sWH[iWH] += " and ";
							}
							sWH[iWH] += sa + "=" + sb.replaceAll("\"", "'"); // ANSI SQL
						} else {
							int lastDot = sb.lastIndexOf('.');
							if (lastDot >= 0) {
								String scl = sb.substring(0, lastDot);
								try {
									ws.prova.Calc.forName(scl);
									// It is a variable of class cl
								} catch (ClassNotFoundException ex) {
									// It is a text constant
									if (sWH[iWH].length() != 0) {
										sWH[iWH] += " and ";
									}
									// ANSI SQL requires single quotes sWH[iWH] += sa + "=" + '"' + sb + '"';
									sWH[iWH] += sa + "=" + "'" + sb + "'";
								}
								continue;
							}
							if (Character.isLowerCase(start)) {
								// It is a text constant
								if (sWH[iWH].length() != 0) {
									sWH[iWH] += " and ";
								}
								sWH[iWH] += sa + "=" + "'" + sb + "'";
							} else {
								// It is a variable
								if (alias != null) {
									sa = alias;
								}
								int pos_sp = sa.indexOf(" ");
								if (pos_sp >= 0) {
									sa = sa.substring(pos_sp + 1);
								}
								int pos_dot = sa.lastIndexOf(".");
								if (pos_dot >= 0) {
									sa = sa.substring(pos_dot + 1);
								}
								field2var.set(i - 1, sb);
							}
						}
					}
					int posBar = sMsg.indexOf("|");
					if (posBar > 0) {
						// There is a tail to the list of fields
						String ta = sMsg.substring(posBar);
						tailVar = ta.substring(1, ta.indexOf(']'));
						tailStartsAt = fields.size();
						query = "select * from " + sTable + " limit 0";
						stmt = conn.createStatement();
						rs = stmt.executeQuery(query);
						ResultSetMetaData meta = rs.getMetaData();
						int ncols = meta.getColumnCount();
						String colName = "";
						for (int col = 1; col <= ncols; col++) {
							colName = meta.getColumnName(col);
							if (fields.contains(colName)) {
								continue;
							}
							if (sFields.length() != 0) {
								sFields += ",";
							}
							sFields += colName;
							field2var.add(null);
						}
					}
				}
				if (sFields.length() == 0) {
					sFields = "*";
				}
				query = "select " + sFields + " from " + sTable;
				if (sWH[0].length() != 0) {
					query += " where " + sWH[0];
				}
				if (functions > 0 && fields.size() > 0) {
					query += " group by ";
					boolean next = false;
					for (ListIterator fit = fields.listIterator(); fit.hasNext(); next = true) {
						if (next) {
							query += ',';
						}
						query += fit.next();
					}
					query += " ";
				}
				if (sWH[1].length() != 0) {
					query += " having " + sWH[1];
				}
				query += sOptions;
				LOG_SQL.debug("Execute SQL: " + query);
				stmt = conn.createStatement();
				rs = stmt.executeQuery(query);

				// next line bugfix 1.9
				if (cacheTimeout != NO_CACHE) {
					cache = new Vector();
					cacheTimestamp = System.currentTimeMillis();
				}
				return new SQLExtendedClauseIterator(f, field2var, predicate, false, rs, cache, closeConnection, stmt, conn, tailStartsAt, tailVar);
			}
		} catch (Exception x) {
//			LOG_SQL.error("Exception executing SQL", x);
			// Whether an exception has occurred AND there was found a matching handler
			boolean exceptionWithHandler = false;
			if (processException(exhandler, x, goal))
				exceptionWithHandler = true;
			return new SQLExtendedClauseIterator(f, field2var, predicate, exceptionWithHandler, rs, cache, closeConnection, stmt, null, 1000000, "");
		}

	}

	/**
	 * Process an <code>InvocationTargetException</code> by inserting the matching handler predicate call
	 * after the current goal. Return false if there is no matching handler.
	 * Pre-condition: <code>ex instanceof InvocationTargetException</code>
	 *
	 * @param exhandler collection of handlers and the current exception
	 * @param ex
	 * @param goal
	 * @return <code>true</code> if the matching handler was found and inserted, <code>false</code>, otherwise.
	 */
	protected static boolean processException(ExceptionHandler exhandler, Exception ex, Clause goal) {
		if (exhandler == null || exhandler.isEmpty()) {
			// There are no handlers registered
			return false;
		}
		Throwable extarget;
		// (1) the real exception inside the method OR an invocation problem
		if (ex instanceof InvocationTargetException) {
			extarget = ((InvocationTargetException) ex).getCause();
		} else {
			extarget = ex;
		}
		// (2) set it as the last exception.
		exhandler.setException(extarget);
		// (3) find the predicate handler appropriate for this exception
		ComplexTerm ct = exhandler.handler(extarget);
		if (ct == null) {
			// There is no matching handler for this exception
			return false;
		}
		// (4) insert the handler predicate call as a Prerequisite
		if (((Fact) goal.getNegativeLiterals().get(0)).getPredicate() instanceof SQLExtendedPredicate) {
			goal.getNegativeLiterals().remove(0);
			goal.getNegativeLiterals().add(0, exhandler.prova().ct2prereq(ct));
		} else
			goal.getNegativeLiterals().add(1, exhandler.prova().ct2prereq(ct));
		return true;
	}

	/**
	 * Get a key for indexing. The knowledge base is supposed to
	 * contain only clauses having the same key object. This method
	 * returns this key object. Note that this behavior is not enforced!
	 *
	 * @return the key object
	 * @see org.mandarax.kernel.Fact#getKey
	 * @see org.mandarax.kernel.Rule#getKey
	 * @see org.mandarax.kernel.KnowledgeBase
	 */
	public Object getKey() {
		return predicate;
	}

	/**
	 * Get the predicate.
	 *
	 * @return a predicate
	 */
	public Predicate getPredicate() {
		return predicate;
	}
	
	/**
	 * Convert the object to a string.
	 *
	 * @return a string
	 */
	public String toString() {
		return predicate.toString(); 
	}

	/**
	 * Get the query build from the SELECT statement from the predicate
	 * plus the additional WHERE clause set here.
	 *
	 * @return a query
	 */
	public String getQuery() {
		if (query == null) {
			String q = predicate.getQuery().trim();
			String w = getWhereClause();
			if (w != null) {
				w = w.trim();

				if (w.length() > 0) {
					if (w.toLowerCase().indexOf("where") == 0) {
						q = q + " " + w;
					} else {
						q = q + " where " + w;
					}
				}
			}
			query = q;
		}
		return query;
	}

	/**
	 * Get the where clause
	 *
	 * @return the where clause
	 */
	public String getWhereClause() {
		return whereClause;
	}

	/**
	 * Remove a clause set listener.
	 *
	 * @param l the listener
	 */
	public void removeClauseSetChangeListener(ClauseSetChangeListener l) {
	}

	/**
	 * Set an SQL predicate.
	 *
	 * @param p a predicate
	 */
	public void setSQLPredicate(SQLPredicate p) {
		predicate = p;
	}

	/**
	 * Set a WHERE clause.
	 *
	 * @param c the clause
	 */
	public void setWhereClause(String c) {
		whereClause = c;
		query = null;
	}

	/**
	 * Indicates whether the cache can be used.
	 *
	 * @return a boolean
	 */
	private boolean useCache() {
		if (cache == null) {
			return false;
		} else {
			if (cacheTimeout == NO_CACHE) {
				cache = null;
				return false;
			} else {
				// check whether cache is expired
				if ((System.currentTimeMillis() - cacheTimestamp) > (cacheTimeout * 1000)) {
					cache = null;
					return false;
				} else {
					return true;
				}
			}
		}
	}

	/**
	 * Set the cache time out. The cache expires after this time.
	 *
	 * @param timeout timeout in milli seconds, use NO_CACHE in order to switch caching off
	 */
	public void setCacheTimeout(long timeout) {
		cacheTimeout = timeout;
	}

	/**
	 * Get the cache time out. The cache expires after this time.
	 *
	 * @return a timeout in milli seconds, NO_CACHE indicates that there is no caching
	 */
	public long getCacheTimeout() {
		return cacheTimeout;
	}

	/**
	 * Compare objects.
	 *
	 * @param obj another object
	 * @return a boolean
	 */
	public boolean equals(Object obj) {
		if ((obj != null) && (obj instanceof SQLClauseSet)) {
			SQLExtendedClauseSet c = (SQLExtendedClauseSet) obj;
			boolean result = true;

			// compare query, name and data source
			// bugfix in 1.8 - thanks to chenjb@gsta.com
			result = (predicate == null) ? (c.predicate == null) : predicate.equals(c.predicate);
			result = result && ((whereClause == null) ? (c.whereClause == null) : whereClause.equals(c.whereClause));
			result = result && ((cacheTimeout == c.cacheTimeout));

			return result;
		} else {
			return false;
		}
	}

	/**
	 * Get the hashcode of the object.
	 *
	 * @return the hash code of the object
	 */
	public int hashCode() {
		return ((predicate == null) ? 0 : predicate.hashCode())
				^ ((whereClause == null) ? 0 : whereClause.hashCode());
	}

	/**
	 * Indicates whether to close the connection at the end.
	 *
	 * @return a boolean
	 */
	public boolean isCloseConnection() {
		return closeConnection;
	}

	/**
	 * Sets whether to close the connection at the end.
	 *
	 * @param closeConnection a boolean
	 */
	public void setCloseConnection(boolean closeConnection) {
		this.closeConnection = closeConnection;
	}

	/**
	 * predicates
	 *
	 * @return Iterator
	 */
	public Iterator predicates() {
		return null;
	}

}
