package pwv.spring.edbutil;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.dao.InvalidDataAccessResourceUsageException;
import org.springframework.dao.TypeMismatchDataAccessException;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;

public class EDbutilTemplate extends JdbcTemplate 
implements InitializingBean {
	private Object[]			mappingObjects;
	private IEProperties[]		supportProperties;
	private Map		helperMap = new HashMap();
	private Map		propertyMap = new HashMap();
	
	public void afterPropertiesSet() {
		super.afterPropertiesSet();
		if (mappingObjects != null) {
			for (int i = 0; i < mappingObjects.length; i++) {
				if (mappingObjects[i] instanceof IEBase) {
					helperMap.put(
						mappingObjects[i].getClass().getName(),
						new EDbHelper(mappingObjects[i])
					);
				}
			}
		}
		if (supportProperties != null) {
			for (int i = 0; i < supportProperties.length; i++) {
				if (supportProperties[i] instanceof IEProperties) {
					propertyMap.put(
						supportProperties[i].getSupportClsName(),
						supportProperties[i]
					);
				}
			}
		}
		tryToCreateTables();	
		setupIds();	
	}
	
	public int insertObject(Object obj) 
	throws TypeMismatchDataAccessException {
		// 既にＤＢに登録されている場合には、挿入しないで0を返す
		if (((IEBase)obj).getId() != null)
			return (0);
		EDbHelper helper = (EDbHelper)helperMap.get(obj.getClass().getName());
		if (helper != null) {
			String sql = helper.getInsertStmt(obj);
			Object[]	params = helper.getObjectArray(obj);
			int[]		types = helper.getTypeArray(obj);
			int ret = -1;
			ret = update(sql, params, types);
			IEProperties prop = (IEProperties)propertyMap.get(obj.getClass().getName());
			if (prop != null) {
				prop.insertProperties(this, obj);
				// 属性をセットし、オブジェクトの参照IDが更新されているので、
				// オブジェクトを更新する
				updateObject(obj);
			}
			return (ret);
		}
		else {
			throw new TypeMismatchDataAccessException("helper not found.");
		}
	}
	
	public int updateObject(Object obj) 
	throws TypeMismatchDataAccessException {
		// ＤＢに登録されていない場合には、挿入する
		if (((IEBase)obj).getId() == null)
			return (insertObject(obj));
		EDbHelper helper = (EDbHelper)helperMap.get(obj.getClass().getName());
		if (helper != null) {
			String sql = helper.getUpdateStmt(obj);
			Object[]	params = helper.getObjectArray(obj);
			int[]		types = helper.getTypeArray(obj);
			int ret = update(sql, params, types);
			return (ret);
			
		}
		else {
			throw new TypeMismatchDataAccessException("helper not found.");
		}
	}

	public void deleteObject(Object obj) 
	throws TypeMismatchDataAccessException {
		EDbHelper helper = (EDbHelper)helperMap.get(obj.getClass().getName());
		if (helper != null) {
			String sql = helper.getDeleteStmt(obj);
			execute(sql);
		}
		else {
			throw new TypeMismatchDataAccessException("helper not found.");
		}
	}

	public String printObject(Object obj) {
		EDbHelper helper = (EDbHelper)helperMap.get(obj.getClass().getName());
		if (helper != null) {
			IEProperties prop = (IEProperties)propertyMap.get(obj.getClass().getName());
			if (prop != null) {
				StringBuffer buf = new StringBuffer();
				buf.append(helper.toString(obj));
				buf.append(prop.printProperties(this, obj));
				return (buf.toString());
			}
			else {
				return (helper.toString(obj));
			}
		}
		else {
			return ("");
		}		
	}
	
	public List selectObjects(Class cls, String where) 
	throws InvalidDataAccessResourceUsageException, 
			TypeMismatchDataAccessException 
	{
		try {
			Connection conToUse = DataSourceUtils.getConnection(getDataSource());
			QueryRunner runner = new QueryRunner();
			ResultSetHandler handler = new BeanListHandler(cls);
			EDbHelper helper = (EDbHelper)helperMap.get(cls.getName());
			if (helper != null) {
				try {
					List list = (List)runner.query(conToUse, "SELECT * from " + helper.getTableName() + " " + where, handler);
					if (list != null && list.size() > 0) {
						for (int i = 0; i < list.size(); i++) {
							Object obj = list.get(i);
							IEProperties prop = (IEProperties)propertyMap.get(obj.getClass().getName());
							if (prop != null) {
								prop.loadProperties(this, obj);
							}
						}
						return (list);
					}
					else {
						return (list);
					}
				}
				catch (SQLException se) {
					throw(new InvalidDataAccessResourceUsageException(se.toString()));
				}
			}
			else {
				throw new TypeMismatchDataAccessException("helper not found.");
			}
			
		}
		catch (CannotGetJdbcConnectionException ex) {
			throw(new DataAccessResourceFailureException(ex.toString()));
		}
	}
	
	public Object loadObject(Class cls, Integer id) 	
	throws InvalidDataAccessResourceUsageException, 
			DataRetrievalFailureException, 
			TypeMismatchDataAccessException
	{
		List list = selectObjects(cls, "WHERE ID = " + id);
		if (list.size() == 0) {
			throw(new DataRetrievalFailureException("object not found."));
		}
		Object obj = list.get(0);
		IEProperties prop = (IEProperties)propertyMap.get(obj.getClass().getName());
		if (prop != null) {
			prop.loadProperties(this, obj);
		}
		return (list.get(0));				
	}

	public Object selectScalar(String sql) {	
		try {
			Connection	conn = DataSourceUtils.getConnection(getDataSource());
			QueryRunner runner = new QueryRunner();
			Object	ret = null;
			try {
				ResultSetHandler handler = new ScalarHandler(1);
				ret = runner.query(conn, sql, handler);
			}
			catch (SQLException ex) {
				throw(new InvalidDataAccessResourceUsageException(ex.toString()));
			}
			return (ret);
		}
		catch (CannotGetJdbcConnectionException e) {
			throw(new DataAccessResourceFailureException(e.toString()));
		}
	}
	
	private void tryToCreateTables() {
		Iterator itr = helperMap.values().iterator();
		Connection conn = DataSourceUtils.getConnection(getDataSource());
		QueryRunner runner = new QueryRunner();
		while (itr.hasNext()) {
			EDbHelper helper = (EDbHelper) itr.next();

			String sql = helper.getCreateTableStmt();
			try {
				runner.update(conn, sql);
			}
			catch (Exception ex) {
				logger.info("crateTables" + ex.toString());
				// エラーが発生しても他のテーブルの作成を試みる
			}
		}
		try {
			conn.commit();
		}
		catch (Exception e) {}
	}

	public void setupIds() {	
		Iterator itr = helperMap.values().iterator();
		Connection conn = DataSourceUtils.getConnection(getDataSource());
		QueryRunner runner = new QueryRunner();
		while (itr.hasNext()) {
			EDbHelper helper = (EDbHelper) itr.next();
			String sql = "SELECT max(id) from " + helper.getTableName();
			try {
				ResultSetHandler handler = new ScalarHandler(1);
				Object ret = runner.query(conn, sql, handler);
				helper.setId(((Integer)ret).intValue()+1);
			}
			catch (Exception ex) {
				logger.info("setupIds" + ex.toString());
				// エラーが発生しても他のテーブルのIDセットを試みる
			}
		}
		try {
			conn.commit();
		}
		catch (Exception e) {}
		
	}
	
	public void setMappingObjects(Object[] objects) {
		mappingObjects = objects;
	}

	public void setSupportProperties(IEProperties[] propertieses) {
		supportProperties = propertieses;
	}

}