package jp.sf.amateras.mirage;

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
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.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import jp.sf.amateras.mirage.annotation.PrimaryKey;
import jp.sf.amateras.mirage.annotation.Transient;
import jp.sf.amateras.mirage.bean.BeanDesc;
import jp.sf.amateras.mirage.bean.BeanDescFactory;
import jp.sf.amateras.mirage.bean.PropertyDesc;
import jp.sf.amateras.mirage.naming.DefaultNameConverter;
import jp.sf.amateras.mirage.naming.NameConverter;
import jp.sf.amateras.mirage.parser.Node;
import jp.sf.amateras.mirage.parser.SqlContext;
import jp.sf.amateras.mirage.parser.SqlContextImpl;
import jp.sf.amateras.mirage.parser.SqlParserImpl;
import jp.sf.amateras.mirage.provider.ConnectionProvider;
import jp.sf.amateras.mirage.util.JdbcUtil;

public class SqlManagerImpl implements SqlManager {

	private static final Logger logger = LoggerFactory.getLogger(SqlManagerImpl.class);

	private NameConverter nameConverter = new DefaultNameConverter();

	private ConnectionProvider connectionProvider;

	@Override
	public void setNameConverter(NameConverter nameConverter) {
		this.nameConverter = nameConverter;
	}

	@Override
	public void setConnectionProvider(ConnectionProvider connectionProvider) {
		this.connectionProvider = connectionProvider;
	}

	protected Node prepareNode(String sqlPath) {
		ClassLoader cl = Thread.currentThread().getContextClassLoader();
		InputStream in = cl.getResourceAsStream(sqlPath);
		if (in == null) {
			throw new RuntimeException(String.format(
					"resource: %s is not found.", sqlPath));
		}

		String sql = null;
		try {
			byte[] buf = new byte[1024 * 8];
			int length = 0;
			ByteArrayOutputStream out = new ByteArrayOutputStream();
			while((length = in.read(buf)) != -1){
				out.write(buf, 0, length);
			}

			sql = new String(out.toByteArray(), "UTF-8");

		} catch(Exception ex){
			throw new RuntimeException(String.format("Failed to load SQL from: %s", sqlPath));
		}

		Node node = new SqlParserImpl(sql).parse();
		return node;
	}

	protected SqlContext prepareSqlContext(Object param){
		SqlContext context = new SqlContextImpl();

		if (param != null) {
			BeanDesc beanDesc = BeanDescFactory.getBeanDesc(param);
			for (int i = 0; i < beanDesc.getPropertyDescSize(); i++) {
				PropertyDesc pd = beanDesc.getPropertyDesc(i);
				context.addArg(pd.getPropertyName(), pd.getValue(param), pd
						.getPropertyType());
			}
		}

		return context;
	}

	@Override
	public int executeUpdate(String sqlPath) {
		return executeUpdate(sqlPath, null);
	}

	@Override
	public int executeUpdate(String sqlPath, Object param) {
		Node node = prepareNode(sqlPath);
		SqlContext context = prepareSqlContext(param);

		node.accept(context);

		PreparedStatement stmt = null;
		try {
			stmt = connectionProvider.getConnection().prepareStatement(context.getSql());

			Object[] vars = context.getBindVariables();
			for (int i = 0; i < vars.length; i++) {
				stmt.setObject(i, vars[i]);
			}

			if(logger.isDebugEnabled()){
				logger.debug(context.getSql());
			}

			int result = stmt.executeUpdate();

			return result;

		} catch (Exception ex) {
			throw new RuntimeException(ex);

		} finally {
			JdbcUtil.close(stmt);
		}
	}

	@Override
	public <T> List<T> getResultList(Class<T> clazz, String sqlPath) {
		return getResultList(clazz, sqlPath, null);
	}

	@Override
	public <T> List<T> getResultList(Class<T> clazz, String sqlPath, Object param) {
		Node node = prepareNode(sqlPath);
		SqlContext context = prepareSqlContext(param);

		node.accept(context);

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connectionProvider.getConnection().prepareStatement(context.getSql());

			Object[] vars = context.getBindVariables();
			for (int i = 0; i < vars.length; i++) {
				stmt.setObject(i + 1, vars[i]);
			}

			List<T> list = new ArrayList<T>();

			if(logger.isDebugEnabled()){
				logger.debug(context.getSql());
			}

			rs = stmt.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount();

			BeanDesc beanDesc = BeanDescFactory.getBeanDesc(clazz);

			while(rs.next()){
				T entity = createEntity(clazz, rs, meta, columnCount, beanDesc);
				list.add(entity);
			}

			return list;

		} catch (Exception ex) {
			throw new RuntimeException(ex);

		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(stmt);
		}
	}

	@SuppressWarnings("unchecked")
	protected <T> T createEntity(Class<T> clazz, ResultSet rs,
			ResultSetMetaData meta, int columnCount, BeanDesc beanDesc)
			throws InstantiationException, IllegalAccessException, SQLException {

		T entity = null;
		if(clazz == Map.class){
			entity = (T) new HashMap();
		} else {
			entity = clazz.newInstance();
		}

		for(int i = 0; i < columnCount; i++){
			String columnName = meta.getColumnName(i + 1);
			String propertyName = nameConverter.columnToProperty(columnName);

			PropertyDesc pd = beanDesc.getPropertyDesc(propertyName);

			if(pd != null){
				Class<?> fieldType = pd.getPropertyType();

				if(fieldType == String.class){
					pd.setValue(entity, rs.getString(columnName));

				} else if(fieldType == Integer.class || fieldType == Integer.TYPE){
					pd.setValue(entity, rs.getInt(columnName));

				} else if(fieldType == Long.class || fieldType == Long.TYPE){
					pd.setValue(entity, rs.getLong(columnName));

				} else if(fieldType == Double.class || fieldType == Double.TYPE){
					pd.setValue(entity, rs.getDouble(columnName));

				} else if(fieldType == Short.class || fieldType == Short.TYPE){
					pd.setValue(entity, rs.getShort(columnName));

				} else if(fieldType == Float.class || fieldType == Float.TYPE){
					pd.setValue(entity, rs.getFloat(columnName));

				} else if(fieldType == BigDecimal.class){
					pd.setValue(entity, rs.getBigDecimal(columnName));

				} else if(fieldType == Date.class){
					pd.setValue(entity, rs.getTimestamp(columnName));
				}

				// TODO support other types

			}
		}
		return entity;
	}

	@Override
	public <T> T getSingleResult(Class<T> clazz, String sqlPath) {
		return getSingleResult(clazz, sqlPath, null);
	}

	@Override
	public <T> T getSingleResult(Class<T> clazz, String sqlPath, Object param) {
		Node node = prepareNode(sqlPath);
		SqlContext context = prepareSqlContext(param);

		node.accept(context);

		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			stmt = connectionProvider.getConnection().prepareStatement(context.getSql());

			Object[] vars = context.getBindVariables();
			for (int i = 0; i < vars.length; i++) {
				stmt.setObject(i + 1, vars[i]);
			}

			if(logger.isDebugEnabled()){
				logger.debug(context.getSql());
			}

			rs = stmt.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount();

			BeanDesc beanDesc = BeanDescFactory.getBeanDesc(clazz);

			if(rs.next()){
				T entity = createEntity(clazz, rs, meta, columnCount, beanDesc);
				return entity;
			}

			return null;

		} catch (Exception ex) {
			throw new RuntimeException(ex);

		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(stmt);
		}
	}

	@Override
	public int deleteEntity(Object entity) {
		StringBuilder sb = new StringBuilder();
		sb.append("DELETE FROM ").append(nameConverter.entityToTable(entity.getClass().getName()));
		sb.append(" WHERE ");

		List<Object> params = new ArrayList<Object>();
		boolean hasPrimaryKey = false;

		BeanDesc beanDesc = BeanDescFactory.getBeanDesc(entity.getClass());

		for(int i=0;i<beanDesc.getPropertyDescSize();i++){
			PropertyDesc pd = beanDesc.getPropertyDesc(i);
			if(pd.getAnnotation(PrimaryKey.class) != null && pd.isReadable()){
				if(!params.isEmpty()){
					sb.append(" AND ");
				}
				sb.append(nameConverter.propertyToColumn(pd.getPropertyName())).append("=?");
				try {
					params.add(pd.getValue(entity));
				} catch(Exception ex){
					throw new RuntimeException(ex);
				}
				hasPrimaryKey = true;
			}
		}

		if(hasPrimaryKey == false){
			throw new RuntimeException(
					"Primary key is not found: " + entity.getClass());
		}

		return executeUpdateSql(sb.toString(), params.toArray());
	}

	@Override
	public int insertEntity(Object entity) {
		List<Object> params = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder();
		BeanDesc beanDesc = BeanDescFactory.getBeanDesc(entity.getClass());

		sb.append("INSERT INTO ").append(nameConverter.entityToTable(entity.getClass().getName())).append(" (");
		{
			int count = 0;
			for(int i=0;i<beanDesc.getPropertyDescSize();i++){
				PropertyDesc pd = beanDesc.getPropertyDesc(i);
				if(pd.getAnnotation(PrimaryKey.class) == null && pd.getAnnotation(Transient.class) == null && pd.isReadable() ){
					if(count != 0){
						sb.append(", ");
					}
					sb.append(nameConverter.propertyToColumn(pd.getPropertyName()));
					count++;
				}
			}
		}
		sb.append(") VALUES (");
		{
			int count = 0;
			for(int i=0;i<beanDesc.getPropertyDescSize();i++){
				PropertyDesc pd = beanDesc.getPropertyDesc(i);
				if(pd.getAnnotation(PrimaryKey.class) == null && pd.getAnnotation(Transient.class) == null && pd.isReadable() ){
					if(count != 0){
						sb.append(", ");
					}
					sb.append("?");

					try {
						params.add(pd.getValue(entity));
					} catch(Exception ex){
						throw new RuntimeException(ex);
					}

					count++;
				}
			}
		}
		sb.append(")");

		return executeUpdateSql(sb.toString(), params.toArray());
	}

	@Override
	public int updateEntity(Object entity) {
		List<Object> params = new ArrayList<Object>();
		StringBuilder sb = new StringBuilder();

		sb.append("UPDATE ").append(nameConverter.entityToTable(entity.getClass().getName())).append(" SET ");

		BeanDesc beanDesc = BeanDescFactory.getBeanDesc(entity.getClass());
		{
			int count = 0;
			for (int i = 0; i < beanDesc.getPropertyDescSize(); i++) {
				PropertyDesc pd = beanDesc.getPropertyDesc(i);
				if(pd.getAnnotation(PrimaryKey.class) == null && pd.getAnnotation(Transient.class) == null && pd.isReadable() ){
					if (count != 0) {
						sb.append(", ");
					}
					sb.append(nameConverter.propertyToColumn(pd.getPropertyName())).append(" = ?");
					try {
						params.add(pd.getValue(entity));
					} catch (Exception ex) {
						throw new RuntimeException(ex);
					}
					count++;
				}
			}
		}
		sb.append(" WHERE ");
		{
			int count = 0;
			for (int i = 0; i < beanDesc.getPropertyDescSize(); i++) {
				PropertyDesc pd = beanDesc.getPropertyDesc(i);
				if(pd.getAnnotation(PrimaryKey.class) != null && pd.isReadable() ){
					if(count != 0){
						sb.append(" AND ");
					}
					sb.append(nameConverter.propertyToColumn(pd.getPropertyName())).append(" = ? ");
					try {
						params.add(pd.getValue(entity));
					} catch(Exception ex){
						throw new RuntimeException(ex);
					}
					count++;
				}
			}
			if(count == 0){
				throw new RuntimeException(
						"Primary key is not found: " + entity.getClass());
			}
		}

		return executeUpdateSql(sb.toString(), params.toArray());
	}

	private int executeUpdateSql(String sql, Object... params) {
		PreparedStatement stmt = null;
		try {
			Connection conn = connectionProvider.getConnection();

			if(logger.isDebugEnabled()){
				logger.debug(sql);
			}

			stmt = conn.prepareStatement(sql);
			if(params != null){
				for(int i=0; i<params.length; i++){
					if(params[i] instanceof Date){
						stmt.setTimestamp(i + 1, new Timestamp(((Date) params[i]).getTime()));
					} else {
						stmt.setObject(i + 1, params[i]);
					}
				}
			}
			int result = stmt.executeUpdate();
			return result;

		} catch(SQLException ex){
			throw new RuntimeException(ex);
		} finally {
			JdbcUtil.close(stmt);
		}
	}
}
