package org.quickbundle.base.dao;

import java.io.ByteArrayInputStream;
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.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.quickbundle.ICoreConstants;
import org.quickbundle.config.RmBaseConfig;
import org.quickbundle.tools.helper.RmSqlHelper;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterDisposer;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlProvider;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.jdbc.core.StatementCreatorUtils;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;

public class RmJdbcTemplate extends JdbcTemplate {
	static enum EnumBoolean {
		NULL,
		TRUE,
		FALSE
	}
	private EnumBoolean absolutePage = EnumBoolean.NULL;
	public EnumBoolean getAbsolutePage() {
		return absolutePage;
	}
	/**
	 * is query page by ResultSet.absolute() mode? not spelling new sql
	 * @param absolutePage
	 */
	public void setAbsolutePage(boolean absolutePage) {
		if(absolutePage) {
			this.absolutePage = EnumBoolean.TRUE;
		} else {
			this.absolutePage = EnumBoolean.FALSE;
		}
	}

	// -------------------------------------------------------------------------
	// Methods dealing with static SQL (java.sql.Statement)
	// -------------------------------------------------------------------------
	public Object execute(StatementCallback action) throws DataAccessException {
		Assert.notNull(action, "Callback object must not be null");

		Connection con = DataSourceUtils.getConnection(getDataSource());
		Statement stmt = null;
		try {
			Connection conToUse = con;
			if (getNativeJdbcExtractor() != null &&
					getNativeJdbcExtractor().isNativeConnectionNecessaryForNativeStatements()) {
				conToUse = getNativeJdbcExtractor().getNativeConnection(con);
			}
			stmt = conToUse.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			applyStatementSettings(stmt);
			Statement stmtToUse = stmt;
			if (getNativeJdbcExtractor() != null) {
				stmtToUse = getNativeJdbcExtractor().getNativeStatement(stmt);
			}
			Object result = action.doInStatement(stmtToUse);
			handleWarnings(stmt);
			return result;
		}
		catch (SQLException ex) {
			// Release Connection early, to avoid potential connection pool deadlock
			// in the case when the exception translator hasn't been initialized yet.
			JdbcUtils.closeStatement(stmt);
			stmt = null;
			DataSourceUtils.releaseConnection(con, getDataSource());
			con = null;
			throw getExceptionTranslator().translate("StatementCallback", getSql(action), ex);
		}
		finally {
			JdbcUtils.closeStatement(stmt);
			DataSourceUtils.releaseConnection(con, getDataSource());
		}
	}

	/**
	 * Determine SQL from potential provider object.
	 * @param sqlProvider object that's potentially a SqlProvider
	 * @return the SQL string, or <code>null</code>
	 * @see SqlProvider
	 */
	private static String getSql(Object sqlProvider) {
		if (sqlProvider instanceof SqlProvider) {
			return ((SqlProvider) sqlProvider).getSql();
		}
		else {
			return null;
		}
	}

	public int update(final String sql) throws DataAccessException {
		return super.update(appendTs(sql));
	}

	public int update(String sql, Object[] args) throws DataAccessException {
		return super.update(appendTs(sql), args, getSqlTypeFromArgs(args));
	}
	
	/**
	 * parse byte[] to Clob 对byte[]类型的字段,直接以流方式写入Blob
	 * @param sql
	 * @param args
	 * @return
	 * @throws DataAccessException
	 */
	public int updateWithBlob(String sql, final Object[] args) throws DataAccessException {
		return super.update(sql, new RmArgTypePreparedStatementSetter(args, getSqlTypeFromArgs(args)));
	}

	private static class RmArgTypePreparedStatementSetter implements PreparedStatementSetter, ParameterDisposer {
		private final Object[] args;
		private final int[] argTypes;

		/**
		 * Create a new ArgTypePreparedStatementSetter for the given arguments.
		 * @param args the arguments to set
		 * @param argTypes the corresponding SQL types of the arguments
		 */
		public RmArgTypePreparedStatementSetter(Object[] args, int[] argTypes) {
			if ((args != null && argTypes == null) || (args == null && argTypes != null) ||
					(args != null && args.length != argTypes.length)) {
				throw new InvalidDataAccessApiUsageException("args and argTypes parameters must match");
			}
			this.args = args;
			this.argTypes = argTypes;
		}


		public void setValues(PreparedStatement ps) throws SQLException {
			int argIndx = 1;
			if (this.args != null) {
				for (int i = 0; i < this.args.length; i++) {
					Object arg = this.args[i];
					if (arg instanceof Collection && this.argTypes[i] != Types.ARRAY) {
						Collection entries = (Collection) arg;
						for (Iterator it = entries.iterator(); it.hasNext();) {
							Object entry = it.next();
							if (entry instanceof Object[]) {
								Object[] valueArray = ((Object[])entry);
								for (int k = 0; k < valueArray.length; k++) {
									Object argValue = valueArray[k];
									StatementCreatorUtils.setParameterValue(ps, argIndx++, this.argTypes[i], argValue);
								}
							}
							else {
								StatementCreatorUtils.setParameterValue(ps, argIndx++, this.argTypes[i], entry);
							}
						}
					}
					else {
						if(this.argTypes[i] == Types.BLOB) {
							byte[] bytes = (byte[])this.args[i];
							ByteArrayInputStream bais = new ByteArrayInputStream(bytes);
							ps.setBinaryStream(argIndx++, bais, bytes.length);
						} else {
							StatementCreatorUtils.setParameterValue(ps, argIndx++, this.argTypes[i], arg);
						}
					}
				}
			}
		}

		public void cleanupParameters() {
			StatementCreatorUtils.cleanupParameters(this.args);
		}
	}
	
	public int[] batchUpdate(final String[] sql) throws DataAccessException {
		for (int i = 0; i < sql.length; i++) {
			sql[i] = appendTs(sql[i]);
		}
		return super.batchUpdate(sql);
	}

	public int[] batchUpdate(String sql, final Object[] aObj, final CircleVoArray cva) throws DataAccessException {
		if (aObj.length == 0) {
			return new int[0];
		}
		sql = appendTs(sql);
		int[] aCount = super.batchUpdate(sql, new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Object[] args = cva.getArgs(aObj[i]);
				ArgTypePreparedStatementSetter atpss = new ArgTypePreparedStatementSetter(args, getSqlTypeFromArgs(args));
				atpss.setValues(ps);
			}

			public int getBatchSize() {
				return aObj.length;
			}
		});
		return aCount;
	}

	/**
	 * 自动加TS更新戳
	 * 
	 * @param sql
	 * @return
	 */
	private String appendTs(String sql) {
		// if(RmBaseConfig.sqlUpdateAutoAppendTs()) {
		// //检测是否有TS列
		// if(RmTableTsDetector.containTs(sql)) {
		// //sql = CrossDBObject.translate(sql);
		// }
		// }
		return sql;
	}

	/**
	 * @param args
	 * @return
	 */
	public static int[] getSqlTypeFromArgs(Object[] args) {
		int types[] = new int[args.length];
		for (int i = 0; i < args.length; i++) {
			if (args[i] == null) {
				types[i] = Types.VARCHAR;
			} else if (args[i] instanceof java.sql.Timestamp) {
				types[i] = Types.TIMESTAMP;
			} else if (args[i] instanceof java.sql.Date) {
				types[i] = Types.DATE;
			} else if (args[i] instanceof java.sql.Time) {
				types[i] = Types.TIME;
			} else if (args[i] instanceof java.math.BigDecimal) {
				types[i] = Types.DECIMAL;
			} else if (args[i] instanceof Integer) {
				types[i] = Types.INTEGER;
			} else if (args[i] instanceof Long) {
				types[i] = Types.BIGINT;
			} else if (args[i] instanceof Short) {
				types[i] = Types.SMALLINT;
			} else if (args[i] instanceof Float) {
				types[i] = Types.FLOAT;
			} else if (args[i] instanceof Double) {
				types[i] = Types.DOUBLE;
			} else if (args[i] instanceof byte[]) {
				types[i] = Types.BLOB;
			} else {
				types[i] = Types.VARCHAR;
			}
		}
		return types;
	}
	
	/**
	 * 功能:
	 * 
	 * @param strsql
	 * @param rowMapper
	 * @param startIndex 开始位置(第一条是1,第二条是2...)
	 * @param size
	 * @return
	 */
	public List query(String strsql, RowMapper rowMapper, int startIndex, int size) {
		return query(strsql, rowMapper, startIndex, size, false);
	}

	/**
	 * 功能:
	 * 
	 * @param strsql
	 * @param rowMapper
	 * @param startIndex 开始位置(第一条是1,第二条是2...)
	 * @param size
	 * @param absoluteByNext circle ResultSet.next() instead of ResultSet.absolute(), because of JDBC driver not support so, such as DB2's CLOB 
	 * @return
	 */
	public List query(String strsql, RowMapper rowMapper, int startIndex, int size, boolean absoluteByNext) {
		if (RmBaseConfig.getSingleton().getDatabaseProductName() != null &&
				(getAbsolutePage().equals(EnumBoolean.FALSE) || (getAbsolutePage().equals(EnumBoolean.NULL) && !RmBaseConfig.getSingleton().isAbsolutePage()))) {
			if (ICoreConstants.DatabaseProductType.ORACLE.getDatabaseProductName().equalsIgnoreCase(RmBaseConfig.getSingleton().getDatabaseProductName())) {
				return (List) query(RmSqlHelper.getSqlPage4Oracle(strsql, startIndex, size), rowMapper);
			} else if (ICoreConstants.DatabaseProductType.MYSQL.getDatabaseProductName().equalsIgnoreCase(RmBaseConfig.getSingleton().getDatabaseProductName())) {
				return (List) query(RmSqlHelper.getSqlPage4Mysql(strsql, startIndex, size), rowMapper);
			}
		}
		return (List) query(strsql, new RmRowMapperResultSetExtractor(rowMapper, startIndex, size, absoluteByNext));
	}

	private static class RmRowMapperResultSetExtractor implements ResultSetExtractor {
		private final RowMapper rowMapper;
		private final int startIndex;
		private final int size;
		private boolean absoluteByNext;
		/**
		 * Create a new RowMapperResultSetExtractor.
		 * @param rowMapper the RowMapper which creates an object for each row
		 * @param startIndex
		 * @param size
		 * @param absolute
		 */
		public RmRowMapperResultSetExtractor(RowMapper rowMapper, int startIndex, int size, boolean absoluteByNext) {
			this.rowMapper = rowMapper;
			this.startIndex = startIndex;
			this.size = size;
			this.absoluteByNext = absoluteByNext;
		}

		public Object extractData(ResultSet rs) throws SQLException {
			List<Object> results = new ArrayList<Object>();
			int rowProcessed = 0;
			if(absoluteByNext) {
				if(startIndex > 0) {
					int pos = 1;
					while(pos < startIndex) {
						rs.next();
						pos ++;
					}
				}
			} else {
				if(startIndex > 0) {
					//moves to the given row number with respect to the beginning of the result set
					if(rs.getRow() != (startIndex - 1)) {
						rs.absolute(startIndex - 1);
					}
				} else if(startIndex < 0) {
					//moves to an absolute row position with respect to the end of the result set
					rs.absolute(startIndex - 1);
				}
			}
			while (rs.next() && rowProcessed < size) {
				results.add(this.rowMapper.mapRow(rs, rs.getRow()));
				rowProcessed++;
			}
			return results;
		}
	}

	public interface CircleVoArray {
		public Object[] getArgs(Object obj);
	}
}