package org.openslx.bwlp.sat.database;

import java.io.Closeable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * Class for creating {@link PreparedStatement}s with named parameters. Based on
 * <a href=
 * "http://www.javaworld.com/article/2077706/core-java/named-parameters-for-preparedstatement.html?page=2"
 * >Named Parameters for PreparedStatement</a>
 */
public class MysqlStatement implements Closeable {

	private static final QueryCache cache = new QueryCache();

	private final PreparsedQuery query;

	private final PreparedStatement statement;

	private final List<ResultSet> openResultSets = new ArrayList<>();

	MysqlStatement(Connection con, String sql) throws SQLException {
		PreparsedQuery query;
		synchronized (cache) {
			query = cache.get(sql);
		}
		if (query == null) {
			query = parse(sql);
			synchronized (cache) {
				cache.put(sql, query);
			}
		}
		this.query = query;
		this.statement = con.prepareStatement(query.sql);
	}
	
	public String getQuery()
	{
	   return query.sql;
	}

	/**
	 * Returns the indexes for a parameter.
	 * 
	 * @param name parameter name
	 * @return parameter indexes
	 * @throws IllegalArgumentException if the parameter does not exist
	 */
	private List<Integer> getIndexes(String name) {
		List<Integer> indexes = query.indexMap.get(name);
		if (indexes == null) {
			throw new IllegalArgumentException("Parameter not found: " + name);
		}
		return indexes;
	}

	/**
	 * Sets a parameter.
	 * 
	 * @param name parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setObject(int, java.lang.Object)
	 */
	public void setObject(String name, Object value) throws SQLException {
		List<Integer> indexes = getIndexes(name);
		for (Integer index : indexes) {
			statement.setObject(index, value);
		}
	}

	/**
	 * Sets a parameter.
	 * 
	 * @param name parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setString(int, java.lang.String)
	 */
	public void setString(String name, String value) throws SQLException {
		List<Integer> indexes = getIndexes(name);
		for (Integer index : indexes) {
			statement.setString(index, value);
		}
	}

	/**
	 * Sets a parameter.
	 * 
	 * @param name parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setInt(int, int)
	 */
	public void setInt(String name, int value) throws SQLException {
		List<Integer> indexes = getIndexes(name);
		for (Integer index : indexes) {
			statement.setInt(index, value);
		}
	}

	/**
	 * Sets a parameter.
	 * 
	 * @param name parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setLong(int, long)
	 */
	public void setLong(String name, long value) throws SQLException {
		List<Integer> indexes = getIndexes(name);
		for (Integer index : indexes) {
			statement.setLong(index, value);
		}
	}

	/**
	 * Sets a parameter.
	 * 
	 * @param name parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setBoolean(int, boolean)
	 */
	public void setBoolean(String name, boolean value) throws SQLException {
		List<Integer> indexes = getIndexes(name);
		for (Integer index : indexes) {
			statement.setBoolean(index, value);
		}
	}

	/**
	 * Sets a parameter.
	 * 
	 * @param name parameter name
	 * @param value parameter value
	 * @throws SQLException if an error occurred
	 * @throws IllegalArgumentException if the parameter does not exist
	 * @see PreparedStatement#setBoolean(int, boolean)
	 */
	public void setBinary(String name, byte[] value) throws SQLException {
		List<Integer> indexes = getIndexes(name);
		for (Integer index : indexes) {
			statement.setBytes(index, value);
		}
	}

	/**
	 * Executes the statement.
	 * 
	 * @return true if the first result is a {@link ResultSet}
	 * @throws SQLException if an error occurred
	 * @see PreparedStatement#execute()
	 */
	public boolean execute() throws SQLException {
		return statement.execute();
	}

	/**
	 * Executes the statement, which must be a query.
	 * 
	 * @return the query results
	 * @throws SQLException if an error occurred
	 * @see PreparedStatement#executeQuery()
	 */
	public ResultSet executeQuery() throws SQLException {
		ResultSet rs = statement.executeQuery();
		openResultSets.add(rs);
		return rs;
	}

	/**
	 * Executes the statement, which must be an SQL INSERT, UPDATE or DELETE
	 * statement; or an SQL statement that returns nothing, such as a DDL
	 * statement.
	 * 
	 * @return number of rows affected
	 * @throws SQLException if an error occurred
	 * @see PreparedStatement#executeUpdate()
	 */
	public int executeUpdate() throws SQLException {
		return statement.executeUpdate();
	}
	
	/**
	 * Retrieves any auto-generated keys created as a result of executing this
	 * <code>Statement</code> object. If this <code>Statement</code> object did
	 * not generate any keys, an empty <code>ResultSet</code>
	 * object is returned.
	 *
	 * <p>
	 * <B>Note:</B>If the columns which represent the auto-generated keys were not specified,
	 * the JDBC driver implementation will determine the columns which best represent the
	 * auto-generated keys.
	 *
	 * @return a <code>ResultSet</code> object containing the auto-generated key(s)
	 *         generated by the execution of this <code>Statement</code> object
	 * @exception SQLException if a database access error occurs or
	 *               this method is called on a closed <code>Statement</code>
	 * @throws SQLFeatureNotSupportedException if the JDBC driver does not support this method
	 */
	public ResultSet getGeneratedKeys() throws SQLException {
		ResultSet rs = statement.getGeneratedKeys();
		openResultSets.add(rs);
		return rs;
	}

	public int lastInsertId() throws SQLException {
		int result = -1;
		try (ResultSet rs = statement.getGeneratedKeys()) {
			if (rs.next()) {
				result = rs.getInt(1);
			}
		}
		return result;
	}

	/**
	 * Closes the statement.
	 * 
	 * @see Statement#close()
	 */
	@Override
	public void close() {
		for (ResultSet rs : openResultSets) {
			try {
				rs.close();
			} catch (SQLException e) {
				//
			}
		}
		try {
			statement.cancel();
		} catch (SQLException e) {
			// Nothing to do
		}
		try {
			statement.close();
		} catch (SQLException e) {
			// Nothing to do
		}
	}

	/**
	 * Adds the current set of parameters as a batch entry.
	 * 
	 * @throws SQLException if something went wrong
	 */
	public void addBatch() throws SQLException {
		statement.addBatch();
	}

	/**
	 * Executes all of the batched statements.
	 * 
	 * See {@link Statement#executeBatch()} for details.
	 * 
	 * @return update counts for each statement
	 * @throws SQLException if something went wrong
	 */
	public int[] executeBatch() throws SQLException {
		return statement.executeBatch();
	}

	// static methods

	private static PreparsedQuery parse(String query) {
		int length = query.length();
		StringBuffer parsedQuery = new StringBuffer(length);
		Map<String, List<Integer>> paramMap = new HashMap<>();
		boolean inSingleQuote = false;
		boolean inDoubleQuote = false;
		boolean hasBackslash = false;
		int index = 1;

		for (int i = 0; i < length; i++) {
			char c = query.charAt(i);
			if (hasBackslash) {
				// Last char was a backslash, so we ignore the current char
				hasBackslash = false;
			} else if (c == '\\') {
				// This is a backslash, next char will be escaped
				hasBackslash = true;
			} else if (inSingleQuote) {
				// End of quoted string
				if (c == '\'') {
					inSingleQuote = false;
				}
			} else if (inDoubleQuote) {
				// End of quoted string
				if (c == '"') {
					inDoubleQuote = false;
				}
			} else {
				// Not in string, look for named params
				if (c == '\'') {
					inSingleQuote = true;
				} else if (c == '"') {
					inDoubleQuote = true;
				} else if (c == ':' && i + 1 < length && Character.isJavaIdentifierStart(query.charAt(i + 1))) {
					int j = i + 2;
					while (j < length && Character.isJavaIdentifierPart(query.charAt(j))) {
						j++;
					}
					String name = query.substring(i + 1, j);
					c = '?'; // replace the parameter with a question mark
					i += name.length(); // skip past the end of the parameter

					List<Integer> indexList = paramMap.get(name);
					if (indexList == null) {
						indexList = new ArrayList<>();
						paramMap.put(name, indexList);
					}
					indexList.add(Integer.valueOf(index));

					index++;
				}
			}
			parsedQuery.append(c);
		}

		return new PreparsedQuery(parsedQuery.toString(), paramMap);
	}

	// private helper classes

	private static class PreparsedQuery {
		private final Map<String, List<Integer>> indexMap;
		private final String sql;

		public PreparsedQuery(String sql, Map<String, List<Integer>> indexMap) {
			this.sql = sql;
			this.indexMap = indexMap;
		}
	}

	private static class QueryCache extends LinkedHashMap<String, PreparsedQuery> {
		private static final long serialVersionUID = 1L;

		public QueryCache() {
			super(30, (float) 0.75, true);
		}

		@Override
		protected boolean removeEldestEntry(Map.Entry<String, PreparsedQuery> eldest) {
			return size() > 40;
		}
	}

}