package com.ffii.core.support;

import java.math.BigDecimal;
import java.sql.Blob;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import javax.sql.DataSource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.BadSqlGrammarException;
import org.springframework.jdbc.IncorrectResultSetColumnCountException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/** @author Terence */
public class JdbcDao {

	private NamedParameterJdbcTemplate template;

	public JdbcDao(DataSource dataSource) {
		this.template = new NamedParameterJdbcTemplate(dataSource);
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public String queryForString(String sql) {
		return this.queryForString(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public String queryForString(String sql, Map<String, ?> paramMap) {
		try {
			return this.template.queryForObject(sql, paramMap, String.class);
		} catch (EmptyResultDataAccessException e) {
			return StringUtils.EMPTY;
		}
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public String queryForString(String sql, Object paramObj) {
		try {
			return this.template.queryForObject(sql, new BeanPropertySqlParameterSource(paramObj), String.class);
		} catch (EmptyResultDataAccessException e) {
			return StringUtils.EMPTY;
		}
	}

	/**
	 * @return {@code true} if non-zero, {@code false} if zero
	 * @throws BadSqlGrammarException                  sql error
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public boolean queryForBoolean(String sql) {
		return this.queryForBoolean(sql, (Map<String, ?>) null);
	}

	/**
	 * @return {@code true} if non-zero, {@code false} if zero
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public boolean queryForBoolean(String sql, Map<String, ?> paramMap) {
		try {
			var rs = this.template.queryForObject(sql, paramMap, Boolean.class);
			return rs == null ? false : rs;
		} catch (EmptyResultDataAccessException e) {
			return false;
		}
	}

	/**
	 * @return {@code true} if non-zero, {@code false} if zero
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public boolean queryForBoolean(String sql, Object paramObj) {
		try {
			var rs = this.template.queryForObject(sql, new BeanPropertySqlParameterSource(paramObj), Boolean.class);
			return rs == null ? false : rs;
		} catch (EmptyResultDataAccessException e) {
			return false;
		}
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public int queryForInt(String sql) {
		return this.queryForInt(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public int queryForInt(String sql, Map<String, ?> paramMap) {
		try {
			var rs = this.template.queryForObject(sql, paramMap, Integer.class);
			return rs == null ? 0 : rs;
		} catch (EmptyResultDataAccessException e) {
			return 0;
		}
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public int queryForInt(String sql, Object paramObj) {
		try {
			var rs = this.template.queryForObject(sql,
					new BeanPropertySqlParameterSource(paramObj), Integer.class);
			return rs == null ? 0 : rs;
		} catch (EmptyResultDataAccessException e) {
			return 0;
		}
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public BigDecimal queryForDecimal(String sql) {
		return this.queryForDecimal(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public BigDecimal queryForDecimal(String sql, Map<String, ?> paramMap) {
		try {
			return this.template.queryForObject(sql, paramMap, BigDecimal.class);
		} catch (EmptyResultDataAccessException e) {
			return BigDecimal.ZERO;
		}
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSetColumnCountException  Incorrect column count
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public BigDecimal queryForDecimal(String sql, Object paramObj) {
		try {
			return this.template.queryForObject(sql,
					new BeanPropertySqlParameterSource(paramObj), BigDecimal.class);
		} catch (EmptyResultDataAccessException e) {
			return BigDecimal.ZERO;
		}
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public <T> Optional<T> queryForEntity(String sql, Class<T> entity) {
		return this.queryForEntity(sql, (Map<String, ?>) null, entity);
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public <T> Optional<T> queryForEntity(String sql, Map<String, ?> paramMap, Class<T> entity) {
		try {
			return Optional.of(this.template.queryForObject(sql, paramMap,
					new BeanPropertyRowMapper<T>(entity)));
		} catch (EmptyResultDataAccessException e) {
			return Optional.empty();
		}
	}

	/**
	 * @throws BadSqlGrammarException                  sql error
	 * @throws InvalidDataAccessApiUsageException      params missing when needed
	 * @throws IncorrectResultSizeDataAccessException: Incorrect result size
	 */
	public <T> Optional<T> queryForEntity(String sql, Object paramObj, Class<T> entity) {
		try {
			return Optional.of(this.template.queryForObject(sql,
					new BeanPropertySqlParameterSource(paramObj), new BeanPropertyRowMapper<T>(entity)));
		} catch (EmptyResultDataAccessException e) {
			return Optional.empty();
		}
	}

	/**
	 * @throws BadSqlGrammarException sql error
	 */
	public <T> List<T> queryForList(String sql, Class<T> entity) {
		return this.queryForList(sql, (Map<String, ?>) null, entity);
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public <T> List<T> queryForList(String sql, Map<String, ?> paramMap, Class<T> entity) {
		return this.template.query(sql, paramMap, new BeanPropertyRowMapper<T>(entity));
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public <T> List<T> queryForList(String sql, Object paramObj, Class<T> entity) {
		return this.template.query(sql, new BeanPropertySqlParameterSource(paramObj),
				new BeanPropertyRowMapper<T>(entity));
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<Integer> queryForInts(String sql) {
		return this.queryForInts(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws InvalidDataAccessApiUsageException     params missing when needed
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<Integer> queryForInts(String sql, Map<String, ?> paramMap) {
		return this.template.queryForList(sql, paramMap, Integer.class);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws InvalidDataAccessApiUsageException     params missing when needed
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<Integer> queryForInts(String sql, Object paramObj) {
		return this.template.queryForList(sql, new BeanPropertySqlParameterSource(paramObj), Integer.class);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<LocalDate> queryForDates(String sql) {
		return this.queryForDates(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws InvalidDataAccessApiUsageException     params missing when needed
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<LocalDate> queryForDates(String sql, Map<String, ?> paramMap) {
		return this.template.queryForList(sql, paramMap, LocalDate.class);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws InvalidDataAccessApiUsageException     params missing when needed
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<LocalDate> queryForDates(String sql, Object paramObj) {
		return this.template.queryForList(sql, new BeanPropertySqlParameterSource(paramObj), LocalDate.class);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<LocalDateTime> queryForDatetimes(String sql) {
		return this.queryForDatetimes(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws InvalidDataAccessApiUsageException     params missing when needed
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<LocalDateTime> queryForDatetimes(String sql, Map<String, ?> paramMap) {
		return this.template.queryForList(sql, paramMap, LocalDateTime.class);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws InvalidDataAccessApiUsageException     params missing when needed
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<LocalDateTime> queryForDatetimes(String sql, Object paramObj) {
		return this.template.queryForList(sql, new BeanPropertySqlParameterSource(paramObj), LocalDateTime.class);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<String> queryForStrings(String sql) {
		return this.queryForStrings(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws InvalidDataAccessApiUsageException     params missing when needed
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<String> queryForStrings(String sql, Map<String, ?> paramMap) {
		return this.template.queryForList(sql, paramMap, String.class);
	}

	/**
	 * @throws BadSqlGrammarException                 sql error
	 * @throws InvalidDataAccessApiUsageException     params missing when needed
	 * @throws IncorrectResultSetColumnCountException Incorrect column count
	 */
	public List<String> queryForStrings(String sql, Object paramObj) {
		return this.template.queryForList(sql, new BeanPropertySqlParameterSource(paramObj), String.class);
	}

	/**
	 * @throws BadSqlGrammarException sql error
	 */
	public List<Map<String, Object>> queryForList(String sql) {
		return this.queryForList(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public List<Map<String, Object>> queryForList(String sql, Map<String, ?> paramMap) {
		return this.template.queryForList(sql, paramMap);
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public List<Map<String, Object>> queryForList(String sql, Object paramObj) {
		return this.template.queryForList(sql, new BeanPropertySqlParameterSource(paramObj));
	}

	/**
	 * @throws BadSqlGrammarException sql error
	 */
	public Optional<Map<String, Object>> queryForMap(String sql) {
		return this.queryForMap(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public Optional<Map<String, Object>> queryForMap(String sql, Map<String, ?> paramMap) {
		try {
			return Optional.of(this.template.queryForMap(sql, paramMap));
		} catch (EmptyResultDataAccessException e) {
			return Optional.empty();
		}
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public Optional<Map<String, Object>> queryForMap(String sql, Object paramObj) {
		try {
			return Optional.of(this.template.queryForMap(sql, new BeanPropertySqlParameterSource(paramObj)));
		} catch (EmptyResultDataAccessException e) {
			return Optional.empty();
		}
	}

	/**
	 * @throws BadSqlGrammarException sql error
	 */
	public int executeUpdate(String sql) {
		return this.executeUpdate(sql, (Map<String, ?>) null);
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public int executeUpdate(String sql, Map<String, ?> paramMap) {
		return this.template.update(sql, paramMap);
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public long executeUpdateAndReturnId(String sql, Map<String, ?> paramMap) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.template.update(sql, new MapSqlParameterSource(paramMap), keyHolder);
		Number generatedId = keyHolder.getKey();
		if (generatedId != null) {
			return generatedId.longValue();
		}
		return 0;
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public int executeUpdate(String sql, Object paramObj) {
		return this.template.update(sql, new BeanPropertySqlParameterSource(paramObj));
	}

	/**
	 * @throws BadSqlGrammarException             sql error
	 * @throws InvalidDataAccessApiUsageException params missing when needed
	 */
	public int[] batchUpdate(String sql, List<?> paramsMapOrObject) {
		return this.template.batchUpdate(sql, SqlParameterSourceUtils.createBatch(paramsMapOrObject));
	}

	public Blob queryForBlob(String sql, Map<String, ?> paramMap) {
		try {
			return this.template.queryForObject(sql, paramMap, Blob.class);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
}
