/*******************************************************************************
 * Copyright 2Fi Business Solutions Ltd.
 *
 * This code is copyrighted. Under no circumstances should any party, people,
 * or organization should redistribute any portions of this code in any form,
 * either verbatim or through electronic media, to any third parties, unless
 * under explicit written permission by 2Fi Business Solutions Ltd.
 ******************************************************************************/
package com.ffii.core.dao;

import java.io.Serializable;
import java.util.List;
import java.util.Map;

import javax.persistence.Table;

import com.ffii.core.BaseEntity;
import com.ffii.core.utils.MapUtils;
import com.ffii.core.utils.Params;

import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcDaoSupport;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

/**
 * JDBC DAO implementation which extends Spring {@link NamedParameterJdbcDaoSupport}
 *
 * @author Patrick
 */
public class JdbcDao extends NamedParameterJdbcDaoSupport {

	/**
	 * Return an instance of SimpleJdbcInsert with the specified table name
	 */
	private SimpleJdbcInsert getJdbcInsert(String tableName, String idName) {
		SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(getJdbcTemplate());
		if (idName != null)
			return jdbcInsert.withTableName(tableName).usingGeneratedKeyColumns(idName);
		else
			return jdbcInsert.withTableName(tableName);
	}

	/**
	 * Query for a {@code List} of {@code Object}s of type {@code T} using the supplied {@code class} to map the query results. Uses SQL with the named
	 * parameter support provided by the {@code NamedParameterJdbcTemplate}.
	 *
	 * @param sql
	 *            the SQL query to run
	 * @param mappedClass
	 *            the class to use for result mapping
	 * @param args
	 *            the map containing the arguments for the query
	 * @param maxRows
	 *            the maximum results to return
	 */
	public <T> List<T> query(String sql, Class<T> mappedClass, Map<String, ?> args, int maxRows) {
		((JdbcTemplate) getNamedParameterJdbcTemplate().getJdbcOperations()).setMaxRows(maxRows);
		return getNamedParameterJdbcTemplate().query(sql, args, BeanPropertyRowMapper.newInstance(mappedClass));
	}

	/**
	 * Execute the supplied query with the supplied arguments.
	 * <p>
	 * The query is expected to be a single row query (otherwise, {@code null} will be returned); the result row will be mapped to a Map<String, Object> (one
	 * entry for each column, using the column name as the key). Uses SQL with the named parameter support provided by the {@link NamedParameterJdbcTemplate}
	 * </p>
	 *
	 * @param sql
	 *            the SQL query to run
	 * @param args
	 *            the map containing the arguments for the query
	 */
	public Map<String, Object> queryForMap(String sql, Map<String, ?> args) {
		try {
			return getNamedParameterJdbcTemplate().queryForMap(sql, args);
		} catch (IncorrectResultSizeDataAccessException e) {
			logger.debug("Unexpected row count for queryForMap()", e);
			return null;
		}
	}

	/**
	 * Query for a {@code List} of {@code Map}s, each element in the returned {@code List} is constructed as a {@code Map} as described in
	 * {@link JdbcOperations#queryForMap}. Uses SQL with the named parameter support provided by the {@code NamedParameterJdbcTemplate}.
	 *
	 * @param sql
	 *            the SQL query to run
	 * @param args
	 *            the map containing the arguments for the query
	 * @param maxRows
	 *            the maximum results to return
	 */
	public List<Map<String, Object>> queryForList(String sql, Map<String, ?> args, int maxRows) {
		((JdbcTemplate) getNamedParameterJdbcTemplate().getJdbcOperations()).setMaxRows(maxRows);
		return getNamedParameterJdbcTemplate().queryForList(sql, args);
	}

	/**
	 * Query for a {@code List} of {@code Map}s, each element in the returned {@code List} is constructed as a {@code Map} as described in
	 * {@link JdbcOperations#queryForMap}. Uses SQL with the named parameter support provided by the {@code NamedParameterJdbcTemplate}.
	 *
	 * @param sql
	 *            the SQL query to run
	 * @param args
	 *            the map containing the arguments for the query
	 */
	public List<Map<String, Object>> queryForList(String sql, Map<String, ?> args) {
		return queryForList(sql, args, 0);
	}

	/**
	 * Query for an {@code int} passing in a SQL query using the named parameter support provided by the {@code NamedParameterJdbcTemplate} and a map containing
	 * the arguments.
	 * <p>
	 * The query is expected to be a single row/single column query; the returned result will be directly mapped to an {@code int}.
	 *
	 * @param sql
	 *            the SQL query to run
	 * @param args
	 *            the map containing the arguments for the query
	 */
	public int queryForInt(String sql, Map<String, ?> args) {
		Integer value = getNamedParameterJdbcTemplate().queryForObject(sql, args, Integer.class);
		return (value != null ? value.intValue() : 0);
	}

	/**
	 * Query for a {@code String} passing in a SQL query using the named parameter support provided by the {@code NamedParameterJdbcTemplate} and a map
	 * containing the arguments.
	 * <p>
	 * The query is expected to be a single row/single column query; the returned result will be directly mapped to a {@code String}.
	 *
	 * @param sql
	 *            the SQL query to run
	 * @param args
	 *            the map containing the arguments for the query
	 */
	public String queryForString(String sql, Map<String, ?> args) {
		return getNamedParameterJdbcTemplate().queryForObject(sql, args, String.class);
	}

	/**
	 * Executes a batch using the supplied SQL statement with the batch of supplied arguments using the named parameter support provided by the
	 * {@code NamedParameterJdbcTemplate}
	 *
	 * @param sql
	 *            the SQL statement to execute
	 * @param batchValues
	 *            the array of Maps containing the batch of arguments for the query
	 *
	 * @return an array containing the numbers of rows affected by each update in the batch
	 */
	public int[] executeBatchUpdate(String sql, Map<String, ?>[] batchValues) {
		return getNamedParameterJdbcTemplate().batchUpdate(sql, batchValues);
	}

	/**
	 * Execute the supplied SQL statement with (optional) supplied arguments using the named parameter support provided by the
	 * {@code NamedParameterJdbcTemplate}
	 *
	 * @param sql
	 *            the SQL statement to execute
	 * @param args
	 *            the map containing the arguments for the query
	 *
	 * @return the number of rows affected by the update
	 */
	public int executeUpdate(String sql, Map<String, ?> args) {
		return getNamedParameterJdbcTemplate().update(sql, args);
	}

	/**
	 * Execute the supplied SQL statement with no arguments
	 *
	 * @param sql
	 *            the SQL statement to execute
	 *
	 * @return the number of rows affected by the update
	 */
	public int executeUpdate(String sql) {
		return getJdbcTemplate().update(sql);
	}

	/**
	 * Executes SQL UPDATE statement with the supplied class and its ID and keyValuePairs
	 *
	 * @param clazz
	 *            any class that extends BaseEntity
	 * @param keyValuePairs
	 *            key value pairs, must include the ID field
	 *
	 * @return the number of rows affected by the update
	 */
	public int executeUpdateByTable(String tableName, Map<String, ?> args) {

		StringBuilder sql = new StringBuilder("UPDATE `" + tableName + "` SET ");
		int i = 0;
		for (String key : args.keySet()) {
			if (i > 0) sql.append(", ");
			sql.append(key + " = :" + key);
			i++;
		}
		sql.append(" WHERE id = :id");

		return executeUpdate(sql.toString(), args);
	}

	/**
	 * Execute SQL Insert statement with the supplied arguments
	 *
	 * @param tableName
	 *            the database table name
	 * @param args
	 *            the map containing the arguments for the insert statement in the form of column name and its value
	 *
	 * @return the number of rows affected as returned by the JDBC driver
	 */
	public int executeInsert(String tableName, Map<String, Object> args) {
		return getJdbcInsert(tableName, null).execute(args);
	}

	/**
	 * Execute SQL Insert statement with the supplied arguments and return the generated key value
	 *
	 * @param tableName
	 *            the database table name
	 * @param idName
	 *            the name of ID column that has auto generated key
	 * @param args
	 *            the map containing the arguments for the insert statement in the form of column name and its value
	 *
	 * @return the generated key value
	 */
	public Number executeInsertAndReturnKey(String tableName, String idName, Map<String, Object> args) {
		return getJdbcInsert(tableName, idName).executeAndReturnKey(args);
	}

	/**
	 * Executes SQL Delete statement with the supplied class and its ID
	 *
	 * @param clazz
	 *            any class that extends BaseEntity
	 * @param id
	 *            the entity's ID
	 *
	 * @return the number of rows affected by the delete
	 */
	public int executeDelete(Class<? extends BaseEntity<Serializable>> clazz, Serializable id) {
		Table table = clazz.getAnnotation(Table.class);

		String sql = "DELETE FROM " + table.name() + " WHERE id = :id";

		return executeUpdate(sql, MapUtils.toHashMap(Params.ID, id));
	}

}
