/*
 * Copyright (c) 2006-2009 OrangeSignal.com All rights reserved.
 * 
 * これは Apache ライセンス Version 2.0 (以下、このライセンスと記述) に
 * 従っています。このライセンスに準拠する場合以外、このファイルを使用
 * してはなりません。このライセンスのコピーは以下から入手できます。
 * 
 * http://www.apache.org/licenses/LICENSE-2.0.txt
 * 
 * 適用可能な法律がある、あるいは文書によって明記されている場合を除き、
 * このライセンスの下で配布されているソフトウェアは、明示的であるか暗黙の
 * うちであるかを問わず、「保証やあらゆる種類の条件を含んでおらず」、
 * 「あるがまま」の状態で提供されるものとします。
 * このライセンスが適用される特定の許諾と制限については、このライセンス
 * を参照してください。
 */

package jp.sf.orangesignal.trading.data;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import jp.sf.orangesignal.ta.data.model.Price;

/**
 * データベースからデータセットをロードするデータセットローダークラスを提供します。
 * 
 * @author 杉澤 浩二
 * @since 1.1
 */
public class DbDatasetLoader implements DatasetLoader {

	/**
	 * デフォルトコンストラクタです。
	 */
	public DbDatasetLoader() {}

	/**
	 * データソースを保持します。
	 */
	private DataSource dataSource;

	/**
	 * データソースを返します。
	 * 
	 * @return データソース
	 */
	public DataSource getDataSource() { return dataSource; }

	/**
	 * データソースを設定します。
	 * 
	 * @param dataSource データソース
	 */
	public void setDataSource(final DataSource dataSource) { this.dataSource = dataSource; }

	/**
	 * SQL文を保持します。
	 */
	private String sql;

	/**
	 * SQL文を返します。
	 * 
	 * @return SQL文
	 */
	public String getSql() { return sql; }

	/**
	 * SQL文を設定します。
	 * 
	 * @param sql SQL文
	 */
	public void setSql(final String sql) { this.sql = sql; }

	/**
	 * データの足の単位を保持します。
	 */
	private IntervalType interval = IntervalType.DAILY;

	/**
	 * データの足の単位を返します。
	 * 
	 * @return データの足の単位
	 */
	public IntervalType getInterval() { return interval; }

	/**
	 * データの足の単位を設定します。
	 * 
	 * @param interval データの足の単位
	 */
	public void setInterval(final IntervalType interval) { this.interval = interval; }

	/**
	 * <p>データセットをロードして返します。</p>
	 * <p>データセットロードリスナーが登録されている場合は、リスナーの実装クラスへイベント通知も行います。</p>
	 * 
	 * @return データセット
	 * @throws LoadException データセットのロードに失敗した場合
	 */
	@SuppressWarnings({ "unchecked" })
	@Override
	public Dataset load(final String symbol) throws LoadException {
		if (symbol == null || symbol.isEmpty())
			throw new LoadException("symbol is empty.");
		if (this.dataSource == null)
			throw new LoadException("DbDatasetLoader requires a DataSource to be invoked in this way, or a Connection should be passed in");
		try {
			final Connection conn = this.getDataSource().getConnection();
			try {
				final PreparedStatement stmt = conn.prepareStatement(this.sql);
				try {
					stmt.setObject(1, symbol);
					final ResultSet rs = stmt.executeQuery();
					try {
						final List<Price> list = (List<Price>) new BeanProcessor().toBeanList(rs, Price.class);
						return Dataset.newInstance(list.toArray(new Price[0]), interval, symbol, null);
					} finally {
						try {
							if (rs != null)
								rs.close();
						} catch (SQLException e) {}
					}
				} finally {
					try {
						if (stmt != null)
							stmt.close();
					} catch (SQLException e) {}
				}
			} finally {
				try {
					if (conn != null)
						conn.close();
				} catch (SQLException e) {}
			}
		} catch (SQLException e) {
			throw new LoadException(e.getMessage(), e);
		}
	}

	/**
	 * <p>
	 * <code>BeanProcessor</code> matches column names to bean property names 
	 * and converts <code>ResultSet</code> columns into objects for those bean 
	 * properties.  Subclasses should override the methods in the processing chain
	 * to customize behavior.
	 * </p>
	 * 
	 * <p>
	 * This class is thread-safe.
	 * </p>
	 */
	private static class BeanProcessor {

		/**
		 * Special array value used by <code>mapColumnsToProperties</code> that
		 * indicates there is no bean property that matches a column from a
		 * <code>ResultSet</code>.
		 */
		protected static final int PROPERTY_NOT_FOUND = -1;

		/**
		 * Set a bean's primitive properties to these defaults when SQL NULL is
		 * returned. These are the same as the defaults that ResultSet get*
		 * methods return in the event of a NULL column.
		 */
		private static final Map<Object, Object> primitiveDefaults = new HashMap<Object, Object>();

		static {
			primitiveDefaults.put(Integer.TYPE, Integer.valueOf(0));
			primitiveDefaults.put(Short.TYPE, Short.valueOf((short) 0));
			primitiveDefaults.put(Byte.TYPE, Byte.valueOf((byte) 0));
			primitiveDefaults.put(Float.TYPE, Float.valueOf(0));
			primitiveDefaults.put(Double.TYPE, Double.valueOf(0));
			primitiveDefaults.put(Long.TYPE, Long.valueOf(0));
			primitiveDefaults.put(Boolean.TYPE, Boolean.FALSE);
			primitiveDefaults.put(Character.TYPE, Character.valueOf('\u0000'));
		}

		/**
		 * Constructor for BeanProcessor.
		 */
		public BeanProcessor() {
			super();
		}

		/**
	     * Convert a <code>ResultSet</code> row into a JavaBean.  This 
	     * implementation uses reflection and <code>BeanInfo</code> classes to 
	     * match column names to bean property names.  Properties are matched to 
	     * columns based on several factors:
	     * <br/>
	     * <ol>
	     *     <li>
	     *     The class has a writable property with the same name as a column.
	     *     The name comparison is case insensitive.
	     *     </li>
	     * 
	     *     <li>
	     *     The column type can be converted to the property's set method 
	     *     parameter type with a ResultSet.get* method.  If the conversion fails
	     *     (ie. the property was an int and the column was a Timestamp) an
	     *     SQLException is thrown.
	     *     </li>
	     * </ol>
	     * 
	     * <p>
	     * Primitive bean properties are set to their defaults when SQL NULL is
	     * returned from the <code>ResultSet</code>.  Numeric fields are set to 0
	     * and booleans are set to false.  Object bean properties are set to 
	     * <code>null</code> when SQL NULL is returned.  This is the same behavior
	     * as the <code>ResultSet</code> get* methods.
	     * </p>
	     *
	     * @param rs ResultSet that supplies the bean data
	     * @param type Class from which to create the bean instance
	     * @throws SQLException if a database access error occurs
	     * @return the newly created bean
	     */
		public Object toBean(final ResultSet rs, final Class<?> type) throws SQLException {
			final PropertyDescriptor[] props = this.propertyDescriptors(type);
			final ResultSetMetaData rsmd = rs.getMetaData();
			final int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);
			return this.createBean(rs, type, props, columnToProperty);
		}

	    /**
	     * Convert a <code>ResultSet</code> into a <code>List</code> of JavaBeans.  
	     * This implementation uses reflection and <code>BeanInfo</code> classes to 
	     * match column names to bean property names. Properties are matched to 
	     * columns based on several factors:
	     * <br/>
	     * <ol>
	     *     <li>
	     *     The class has a writable property with the same name as a column.
	     *     The name comparison is case insensitive.
	     *     </li>
	     * 
	     *     <li>
	     *     The column type can be converted to the property's set method 
	     *     parameter type with a ResultSet.get* method.  If the conversion fails
	     *     (ie. the property was an int and the column was a Timestamp) an
	     *     SQLException is thrown.
	     *     </li>
	     * </ol>
	     * 
	     * <p>
	     * Primitive bean properties are set to their defaults when SQL NULL is
	     * returned from the <code>ResultSet</code>.  Numeric fields are set to 0
	     * and booleans are set to false.  Object bean properties are set to 
	     * <code>null</code> when SQL NULL is returned.  This is the same behavior
	     * as the <code>ResultSet</code> get* methods.
	     * </p>
	     *
	     * @param rs ResultSet that supplies the bean data
	     * @param type Class from which to create the bean instance
	     * @throws SQLException if a database access error occurs
	     * @return the newly created List of beans
	     */
		public List<?> toBeanList(final ResultSet rs, final Class<?> type) throws SQLException {
			final List<Object> results = new ArrayList<Object>();

			if (!rs.next())
				return results;

			final PropertyDescriptor[] props = this.propertyDescriptors(type);
			final ResultSetMetaData rsmd = rs.getMetaData();
			final int[] columnToProperty = this.mapColumnsToProperties(rsmd, props);

			do {
				results.add(this.createBean(rs, type, props, columnToProperty));
			} while (rs.next());

			return results;
		}

	    /**
	     * Creates a new object and initializes its fields from the ResultSet.
	     *
	     * @param rs The result set.
	     * @param type The bean type (the return type of the object).
	     * @param props The property descriptors.
	     * @param columnToProperty The column indices in the result set.
	     * @return An initialized object.
	     * @throws SQLException if a database error occurs.
	     */
		private Object createBean(final ResultSet rs, final Class<?> type, final PropertyDescriptor[] props, final int[] columnToProperty) throws SQLException {
			final Object bean = this.newInstance(type);

			for (int i = 1; i < columnToProperty.length; i++) {
				if (columnToProperty[i] == PROPERTY_NOT_FOUND)
					continue;

				final PropertyDescriptor prop = props[columnToProperty[i]];
				final  Class<?> propType = prop.getPropertyType();

				Object value = this.processColumn(rs, i, propType);

				if (propType != null && value == null && propType.isPrimitive())
					value = primitiveDefaults.get(propType);

				this.callSetter(bean, prop, value);
			}

			return bean;
		}

	    /**
	     * Calls the setter method on the target object for the given property.
	     * If no setter method exists for the property, this method does nothing.
	     * @param target The object to set the property on.
	     * @param prop The property to set.
	     * @param value The value to pass into the setter.
	     * @throws SQLException if an error occurs setting the property.
	     */
		private void callSetter(final Object target, final PropertyDescriptor prop, Object value) throws SQLException {
			final Method setter = prop.getWriteMethod();

			if (setter == null)
				return;

			final Class<?>[] params = setter.getParameterTypes();
			try {
				// convert types for some popular ones
				if (value != null) {
					if (value instanceof java.util.Date) {
						if (params[0].getName().equals("java.sql.Date")) {
							value = new java.sql.Date(((java.util.Date) value).getTime());
						} else if (params[0].getName().equals("java.sql.Time")) {
							value = new java.sql.Time(((java.util.Date) value).getTime());
						} else if (params[0].getName().equals("java.sql.Timestamp")) {
							value = new java.sql.Timestamp(((java.util.Date) value).getTime());
						}
					}
				}

				// Don't call setter if the value object isn't the right type 
				if (this.isCompatibleType(value, params[0])) {
					setter.invoke(target, new Object[] { value });
				} else {
					throw new SQLException("Cannot set " + prop.getName() + ": incompatible types.");
				}
			} catch (IllegalArgumentException e) {
				throw new SQLException("Cannot set " + prop.getName() + ": " + e.getMessage());
			} catch (IllegalAccessException e) {
				throw new SQLException("Cannot set " + prop.getName() + ": " + e.getMessage());
			} catch (InvocationTargetException e) {
				throw new SQLException("Cannot set " + prop.getName() + ": " + e.getMessage());
			}
		}

	    /**
	     * ResultSet.getObject() returns an Integer object for an INT column.  The
	     * setter method for the property might take an Integer or a primitive int.
	     * This method returns true if the value can be successfully passed into
	     * the setter method.  Remember, Method.invoke() handles the unwrapping
	     * of Integer into an int.
	     * 
	     * @param value The value to be passed into the setter method.
	     * @param type The setter's parameter type.
	     * @return boolean True if the value is compatible.
	     */
		private boolean isCompatibleType(final Object value, final Class<?> type) {
			// Do object check first, then primitives
			if (value == null || type.isInstance(value)) {
				return true;
			} else if (type.equals(Integer.TYPE) && Integer.class.isInstance(value)) {
				return true;
			} else if (type.equals(Long.TYPE) && Long.class.isInstance(value)) {
				return true;
			} else if (type.equals(Double.TYPE) && Double.class.isInstance(value)) {
				return true;
			} else if (type.equals(Float.TYPE) && Float.class.isInstance(value)) {
				return true;
			} else if (type.equals(Short.TYPE) && Short.class.isInstance(value)) {
				return true;
			} else if (type.equals(Byte.TYPE) && Byte.class.isInstance(value)) {
				return true;
			} else if (type.equals(Character.TYPE) && Character.class.isInstance(value)) {
				return true;
			} else if (type.equals(Boolean.TYPE) && Boolean.class.isInstance(value)) {
				return true;
			} else {
				return false;
			}
		}

	    /**
	     * Factory method that returns a new instance of the given Class.  This
	     * is called at the start of the bean creation process and may be 
	     * overridden to provide custom behavior like returning a cached bean
	     * instance.
	     *
	     * @param c The Class to create an object from.
	     * @return A newly created object of the Class.
	     * @throws SQLException if creation failed.
	     */
		protected Object newInstance(final Class<?> c) throws SQLException {
			try {
				return c.newInstance();
			} catch (InstantiationException e) {
				throw new SQLException("Cannot create " + c.getName() + ": " + e.getMessage());
			} catch (IllegalAccessException e) {
				throw new SQLException("Cannot create " + c.getName() + ": " + e.getMessage());
			}
		}

	    /**
	     * Returns a PropertyDescriptor[] for the given Class.
	     *
	     * @param c The Class to retrieve PropertyDescriptors for.
	     * @return A PropertyDescriptor[] describing the Class.
	     * @throws SQLException if introspection failed.
	     */
		private PropertyDescriptor[] propertyDescriptors(final Class<?> c) throws SQLException {
			// Introspector caches BeanInfo classes for better performance
			final BeanInfo beanInfo;
			try {
				beanInfo = Introspector.getBeanInfo(c);
			} catch (IntrospectionException e) {
				throw new SQLException("Bean introspection failed: " + e.getMessage());
			}
			return beanInfo.getPropertyDescriptors();
		}

	    /**
	     * The positions in the returned array represent column numbers.  The 
	     * values stored at each position represent the index in the 
	     * <code>PropertyDescriptor[]</code> for the bean property that matches 
	     * the column name.  If no bean property was found for a column, the 
	     * position is set to <code>PROPERTY_NOT_FOUND</code>.
	     * 
	     * @param rsmd The <code>ResultSetMetaData</code> containing column 
	     * information.
	     * 
	     * @param props The bean property descriptors.
	     * 
	     * @throws SQLException if a database access error occurs
	     *
	     * @return An int[] with column index to property index mappings.  The 0th 
	     * element is meaningless because JDBC column indexing starts at 1.
	     */
		protected int[] mapColumnsToProperties(final ResultSetMetaData rsmd, final PropertyDescriptor[] props) throws SQLException {
			final int cols = rsmd.getColumnCount();
			final int columnToProperty[] = new int[cols + 1];
			Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);

			for (int col = 1; col <= cols; col++) {
				final String columnName = rsmd.getColumnName(col);
				for (int i = 0; i < props.length; i++) {
					if (columnName.equalsIgnoreCase(props[i].getName())) {
						columnToProperty[col] = i;
						break;
					}
				}
			}

			return columnToProperty;
		}

	    /**
	     * Convert a <code>ResultSet</code> column into an object.  Simple 
	     * implementations could just call <code>rs.getObject(index)</code> while
	     * more complex implementations could perform type manipulation to match 
	     * the column's type to the bean property type.
	     * 
	     * <p>
	     * This implementation calls the appropriate <code>ResultSet</code> getter 
	     * method for the given property type to perform the type conversion.  If 
	     * the property type doesn't match one of the supported 
	     * <code>ResultSet</code> types, <code>getObject</code> is called.
	     * </p>
	     * 
	     * @param rs The <code>ResultSet</code> currently being processed.  It is
	     * positioned on a valid row before being passed into this method.
	     * 
	     * @param index The current column index being processed.
	     * 
	     * @param propType The bean property type that this column needs to be
	     * converted into.
	     * 
	     * @throws SQLException if a database access error occurs
	     * 
	     * @return The object from the <code>ResultSet</code> at the given column
	     * index after optional type processing or <code>null</code> if the column
	     * value was SQL NULL.
	     */
		protected Object processColumn(final ResultSet rs, final int index, final Class<?> propType) throws SQLException {
			if (!propType.isPrimitive() && rs.getObject(index) == null)
				return null;

			if (propType.equals(String.class)) {
				return rs.getString(index);
			} else if (propType.equals(Integer.TYPE) || propType.equals(Integer.class)) {
				return Integer.valueOf(rs.getInt(index));
			} else if (propType.equals(Boolean.TYPE) || propType.equals(Boolean.class)) {
				return Boolean.valueOf(rs.getBoolean(index));
			} else if (propType.equals(Long.TYPE) || propType.equals(Long.class)) {
				return Long.valueOf(rs.getLong(index));
			} else if (propType.equals(Double.TYPE) || propType.equals(Double.class)) {
				return Double.valueOf(rs.getDouble(index));
			} else if (propType.equals(Float.TYPE) || propType.equals(Float.class)) {
				return Float.valueOf(rs.getFloat(index));
			} else if (propType.equals(Short.TYPE) || propType.equals(Short.class)) {
				return Short.valueOf(rs.getShort(index));
			} else if (propType.equals(Byte.TYPE) || propType.equals(Byte.class)) {
				return Byte.valueOf(rs.getByte(index));
			} else if (propType.equals(Timestamp.class)) {
				return rs.getTimestamp(index);
			} else {
				return rs.getObject(index);
			}
		}
	}

}
