/*
 * This file is part of Nuts Framework.
 * Copyright(C) 2009-2012 Nuts Develop Team.
 *
 * Nuts Framework is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License any later version.
 * 
 * Nuts Framework 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 General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Nuts Framework. If not, see <http://www.gnu.org/licenses/>.
 */
package nuts.core.sql;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import nuts.core.beans.PropertyUtils;
import nuts.core.lang.StringEscapeUtils;

/**
 * utility class for sql
 */
public class SqlUtils {

	/**
	 * escapeSql
	 * @param str string
	 * @return escaped string
	 */
	public static String escapeSql(String str) {
		return StringEscapeUtils.escapeSql(str);
	}

	/**
	 * escapeSqlLike
	 * @param str string
	 * @return escaped string
	 */
	public static String escapeSqlLike(String str) {
		return StringEscapeUtils.escapeSqlLike(str);
	}


	/**
	 * like string 
	 * @param str string
	 * @return %str%
	 */
	public static String stringLike(String str) {
		return '%' + escapeSqlLike(str) + '%';
	}
	
	/**
	 * starts like string 
	 * @param str string
	 * @return str%
	 */
	public static String startsLike(String str) {
		return escapeSqlLike(str) + '%';
	}
	
	/**
	 * ends like string 
	 * @param str string
	 * @return %str
	 */
	public static String endsLike(String str) {
		return '%' + escapeSqlLike(str);
	}
	
	private static Pattern illegalFieldNamePattern = Pattern.compile(".*[^a-zA-Z_0-9\\.].*");

	/**
	 * isIllegalFieldName
	 * @param fieldName fieldName
	 * @return true if fieldName is illegal
	 */
	public static boolean isIllegalFieldName(String fieldName) {
		Matcher m = illegalFieldNamePattern.matcher(fieldName);
		return m.matches();
	}

	/**
	 * <pre>
	 * parse sql
	 * 
	 * ex)
	 * 	sql: INSERT INTO ACCOUNT VALUES($name$, #password#)
	 * 	tag: $
	 *  parsed sql: { 'INSERT INTO ACCOUNT VALUES(?,?)', 'name', 'pasword' }
	 * 
	 * @param sql sql
	 * @param tag tag char
	 * @return parsed sql & parameters list
	 */
	public static List<String> parseSql(String sql, char tag) {
		LinkedList<String> sqls = new LinkedList<String>();
		
		StringBuilder sb = new StringBuilder(sql.length());
		
		boolean instr = false;
		for (int i = 0; i < sql.length(); i++) {
			char c = sql.charAt(i);
			if (c == '\'') {
				sb.append(c);
				if (instr) {
					if (i + 1 >= sql.length()) {
						throw new IllegalArgumentException("Illegal sql statement: unexpected end of string reached.");
					}

					char cn = sql.charAt(i + 1); 
					if (cn == '\'') {
						i++;
						sb.append(cn);
					}
					else {
						instr = false;
					}
				}
				else {
					instr = true;
				}
			}
			else if (c == tag) {
				if (instr) {
					sb.append(c);
				}
				else {
					String v = null;
					int j = i + 1;
					for (; j < sql.length(); j++) {
						if (sql.charAt(j) == tag) {
							v = sql.substring(i + 1, j);
							break;
						}
					}
					if (v == null) {
						throw new IllegalArgumentException("Illegal sql statement (" + i + "): unexpected end of tag reached.");
					}
					else if (v.length() < 1) {
						throw new IllegalArgumentException("Illegal sql statement (" + i + "): the paramenter can not be empty.");
					}
					sqls.add(v);
					sb.append('?');
					i = j;
				}
			}
			else {
				sb.append(c);
			}
		}
		sqls.addFirst(sb.toString());
		
		return sqls;
	}
	
	/**
	 * create PreparedStatement and set parameters
	 * @param con connection
	 * @param sql sql
	 * @param params parameter object
	 * @return PreparedStatement
	 * @throws SQLException if a SQL error occurs 
	 * @throws NoSuchMethodException cause by PropertyUtils.getProperty(params, name)
	 * @throws InvocationTargetException cause by PropertyUtils.getProperty(params, name)
	 * @throws IllegalAccessException cause by PropertyUtils.getProperty(params, name)
	 */
	public static PreparedStatement prepareStatement(Connection con, String sql, Object params)
			throws SQLException, IllegalAccessException, InvocationTargetException,
			NoSuchMethodException {
		PreparedStatement ps = null;
		
		List<String> sqls = parseSql(sql, '#');
		if (sqls != null && sqls.size() > 0) {
			ps = con.prepareStatement(sqls.get(0));
			for (int i = 1; i < sqls.size(); i++) {
				Object v = PropertyUtils.getProperty(params, sqls.get(i));
				ps.setObject(i - 1, v);
			}
		}
		return ps;
	}

	/**
	 * close ResultSet with out throw exception
	 * @param resultSet result set
	 */
	public static void closeQuietly(ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
		} catch (SQLException e) {
		}
	}
	
	/**
	 * close statement with out throw exception
	 * @param statement statement
	 */
	public static void closeQuietly(Statement statement) {
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
		}
	}
	
	/**
	 * close connection with out throw exception
	 * @param connection connection
	 */
	public static void closeQuietly(Connection connection) {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
		}
	}
	
	/**
	 * rollback with out throw exception
	 * @param connection connection
	 */
	public static void rollbackQuietly(Connection connection) {
		try {
			if (connection != null) {
				connection.rollback();
			}
		}
		catch (SQLException e) {
		}
	}

	/**
	 * skip result
	 * @param resultSet result set
	 * @param skip            The number of results to ignore.
	 * @throws SQLException if a SQL exception occurs
	 */
	public static void skipResultSet(ResultSet resultSet, int skip) throws SQLException {
		if (skip > 0) {
			if (resultSet.getType() != ResultSet.TYPE_FORWARD_ONLY) {
				resultSet.absolute(skip);
			}
			else {
				for (; skip > 0; skip--) {
					if (!resultSet.next()) {
						return;
					}
				}
			}
		}
	}

	/**
	 * @param type SQL type from java.sql.Types
	 * @return true if the type is a binary type
     * @see Types
	 */
	public static boolean isBinaryType(int type) {
		return (type == Types.BINARY 
				|| type == Types.BLOB
				|| type == Types.LONGVARBINARY
				|| type == Types.VARBINARY);
	}
}
