package cc.smtweb.framework.core.db.jdbc;

import cc.smtweb.framework.core.common.SwEnum;
import cc.smtweb.framework.core.common.SwMap;
import cc.smtweb.framework.core.db.impl.BaseBean;
import cc.smtweb.framework.core.exception.DbException;
import cc.smtweb.framework.core.exception.SwException;
import cc.smtweb.framework.core.util.JsonUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.function.Function;

/**
 * JDBC访问类，包装了spring jdbcTemplate对象
 */
public class JdbcEngine {

    private Map<Thread, JdbcTrans> mapThreadTrans = new ConcurrentHashMap<>();
    private JdbcTemplate jdbcTemplate;
    private NamedParameterJdbcTemplate namedJdbcTemplate;
    private DataSourceTransactionManager dataSourceTransactionManager;
    private IdGenerator idGenerator;

    protected String type;

    public JdbcEngine(NamedParameterJdbcTemplate namedJdbcTemplate, IdGenerator idGenerator, String type) {
        this.namedJdbcTemplate = namedJdbcTemplate;
        this.jdbcTemplate = namedJdbcTemplate.getJdbcTemplate();
        this.dataSourceTransactionManager = new DataSourceTransactionManager(jdbcTemplate.getDataSource());
        this.idGenerator = idGenerator;
        this.type = type;
    }

    public boolean isMysql() {
        return SwEnum.DbType.MYSQL.value.equalsIgnoreCase(type);
    }

    public String getDbType() {
        return type;
    }

    /**
     * 获取数据库唯一id
     *
     * @return 返回ID值
     */
    public long nextId() {
        return this.idGenerator.nextId();
    }

    /**
     * 查询单行数据
     *
     * @param sql        查询SQL
     * @param rowHandler ResultSet处理器
     * @param <T>        返回得对象类型
     * @return 返回单个对象数据
     */
    public <T> T queryEntity(String sql, final ResultSetExtractor<T> rowHandler) {
        return jdbcTemplate.query(sql, rowHandler);
    }

    /**
     * 查询单行数据
     *
     * @param sql        查询SQL
     * @param rowHandler ResultSet处理器
     * @param params     SQL参数
     * @param <T>        返回得对象类型
     * @return 返回单个对象数据
     */
    public <T> T queryEntity(String sql, final ResultSetExtractor<T> rowHandler, Object... params) {
        return jdbcTemplate.query(sql, rowHandler, params);
    }

    /**
     * 通过回调函数查询SQL，返回列表或者对象数据
     */
    public <T> T query(String sql, ResultSetExtractor<T> rse, Object... params) {
        return jdbcTemplate.query(sql, rse, params);
    }

    /**
     * 查询SQL，返回列表数据
     */
    public <T> List<T> query(String sql, RowMapper<T> rowMapper) {
        return jdbcTemplate.query(sql, rowMapper);
    }

    /**
     * 查询SQL，返回列表数据
     */
    public <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... params) {
        return jdbcTemplate.query(sql, rowMapper, params);
    }

    /**
     * 执行更新SQL
     */
    public int update(String sql) {
        return jdbcTemplate.update(sql);
    }

    /**
     * 执行更新SQL
     */
    public int update(String sql, Object... params) {
        return jdbcTemplate.update(sql, params);
    }

    /**
     * 查询字符list
     *
     * @param sql    查询SQL
     * @param params 查询SQL参数
     * @return 字符list
     */
    public List<String> queryStringList(String sql, Object... params) {
        return jdbcTemplate.query(sql, (resultSet, i) -> resultSet.getString(1), params);
    }

    /**
     * 查询字符set
     *
     * @param sql    查询SQL
     * @param params 查询SQL参数
     * @return 字符set
     */
    public Set<String> queryStringSet(String sql, Object... params) {
        return jdbcTemplate.query(sql, (resultSet) -> {
            Set<String> result = new HashSet<>();
            while (resultSet.next()) {
                String value = resultSet.getString(1);
                if (value != null) {
                    result.add(value);
                }
            }

            return result;
        }, params);
    }

    /**
     * 查询字符
     *
     * @param sql    查询SQL
     * @param params 查询SQL参数
     * @return 字符
     */
    public String queryString(String sql, Object... params) {
        List<String> list = jdbcTemplate.query(sql, (resultSet, i) -> resultSet.getString(1), params);
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        }

        return null;
    }

    /**
     * 查询值为JSON格式，转换为对象返回
     *
     * @param sql    查询SQL
     * @param clazz  对象类
     * @param params 查询SQL参数
     * @param <T>    对象类型
     * @return JSON格式，转换为对象返回
     */
    public <T> T queryJson(String sql, Class<T> clazz, Object... params) {
        List<String> list = jdbcTemplate.query(sql, (resultSet, i) -> resultSet.getString(1), params);
        if (list != null && !list.isEmpty()) {
            String s = list.get(0);
            if (StringUtils.isNotBlank(s)) {
                return JsonUtil.parse(s, clazz);
            } else {
                try {
                    return clazz.newInstance();
                } catch (InstantiationException | IllegalAccessException e) {
                    throw new DbException(e);
                }
            }
        }

        return null;
    }

    /**
     * 查询Long对象
     *
     * @param sql    查询SQL
     * @param params 查询SQL参数
     * @return Long对象
     */
    public Long queryLong(String sql, Object... params) {
        List<Long> list = jdbcTemplate.query(sql, (resultSet, i) -> resultSet.getLong(1), params);
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        }

        return null;
    }

    /**
     * 查询List<Long>对象
     *
     * @param sql    查询SQL
     * @param params 查询SQL参数
     * @return List<Long>对象
     */
    public List<Long> queryLongList(String sql, Object... params) {
        return jdbcTemplate.query(sql, (resultSet, i) -> resultSet.getLong(1), params);
    }

    /**
     * 查询Set<Long>对象
     *
     * @param sql    查询SQL
     * @param params 查询SQL参数
     * @return Set<Long>对象
     */
    public Set<Long> queryLongSet(String sql, Object... params) {
        return jdbcTemplate.query(sql, (resultSet) -> {
            Set<Long> result = new HashSet<>();
            while (resultSet.next()) {
                long value = resultSet.getLong(1);
                if (!resultSet.wasNull()) {
                    result.add(value);
                }
            }

            return result;
        }, params);
    }

    /**
     * 查询Integer对象
     *
     * @param sql    查询SQL
     * @param params 查询SQL参数
     * @return Integer对象
     */
    public Integer queryInt(String sql, Object... params) {
        List<Integer> list = jdbcTemplate.query(sql, (resultSet, i) -> resultSet.getInt(1), params);
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        }

        return null;
    }

    /**
     * 执行批量更新语句
     *
     * @param sql    SQL语句
     * @param params 数组参数列表
     * @return 执行结果数组
     */
    public int[] batchUpdate(String sql, List<Object[]> params) {
        return jdbcTemplate.batchUpdate(sql, params);
    }

    /**
     * 启动事务,需要用try(DbTrans dbTrans=dbEngine.openTrans())来处理异常后自动回滚事务，如果手工提交事务DbTaans.commit()
     *
     * @return 事务对象
     */
    public JdbcTrans openTrans() {
        return new JdbcTrans(this.dataSourceTransactionManager);
    }

    /**
     * 在回调函数中执行事务，返回的true就提交事务，否则回滚事务
     *
     * @param extractor 回调方法
     * @return 事务是否成功，如果异常会也会事务回滚后再抛出异常
     *//*
    public boolean doTrans(Supplier<Boolean> extractor) {
        try (JdbcTrans jdbcTrans = openTrans()) {
            boolean result = extractor.get();
            if (result) {
                jdbcTrans.commit();
                return true;
            }
        }

        return false;
    }*/
    public void beginTrans() {
        Thread t = Thread.currentThread();
        JdbcTrans jdbcTrans = mapThreadTrans.get(t);
        if (jdbcTrans != null) {
            jdbcTrans.inc();
            return;
        }
        mapThreadTrans.put(t, openTrans());
    }

    public void commit() throws SwException {
        Thread t = Thread.currentThread();
        JdbcTrans tm = this.mapThreadTrans.get(t);
        if (tm == null) throw new SwException("当前没有开启事务。");
        if (tm.canCommit()) {
            tm.doEnd("commit");
            tm.commit();
            this.mapThreadTrans.remove(t);
        }
    }

    public void recTransLog() {
        for (JdbcTrans tm : mapThreadTrans.values()) {
            tm.doEnd("process");
        }
    }

    public void rollback(Exception e) throws SwException {
        Thread t = Thread.currentThread();
        JdbcTrans tm = mapThreadTrans.get(t);
        if (tm == null) throw new SwException("当前没有开启事务。");
        if (tm.canCommit()) {
            tm.doEnd("rollback");
            mapThreadTrans.remove(t);
            tm.rollback();
        }
        if (e != null) throw new SwException(e);
    }

    public void doTrans(IDbWorker dbWorker) throws SwException {
        beginTrans();
        try {
            dbWorker.work();
            commit();
        } catch (Exception e) {
            rollback(e);
            dbWorker.doAfterDbRollback();
            throw new SwException(e);
        }
        dbWorker.doAfterDbCommit();
    }

    public void doTrans(ISimpleWorker dbWorker) throws SwException {
        beginTrans();
        try {
            dbWorker.work();
            commit();
        } catch (Exception e) {
            rollback(e);
            throw new SwException(e);
        }
    }

    //独立事务
    public void doTransSingle(IDbWorker worker) throws SwException {
        JdbcTrans jdbcTrans = openTrans();
        try {
            worker.work();
            jdbcTrans.commit();
        } catch (Exception e) {
            jdbcTrans.rollback();
            worker.doAfterDbRollback();
            throw new SwException(e);
        }
        worker.doAfterDbCommit();
    }

    public void doTransSingle(ISimpleWorker worker) throws SwException {
        JdbcTrans jdbcTrans = openTrans();
        try {
            worker.work();
            jdbcTrans.commit();
        } catch (Exception e) {
            jdbcTrans.rollback();
            throw new SwException(e);
        }
    }


    /**
     * 获取原始数据库连接执行命令
     *
     * @return 数据库连接
     */
    public <T> T doConn(Function<Connection, T> extractor) {
        DataSource ds = Objects.requireNonNull(this.jdbcTemplate.getDataSource());

        try (Connection conn = ds.getConnection()) {
            return extractor.apply(conn);
        } catch (SQLException e) {
            throw new DbException(e);
        }
    }

    /**
     * 查询单行数据，返回bean
     */
    public <T> T queryEntity(String sql, Class<T> type) {
        List<T> list = jdbcTemplate.query(sql, createRowMapper(type));

        if (list != null && !list.isEmpty()) {
            return list.get(0);
        }

        return null;
    }

    /**
     * 查询单行数据，返回bean
     */
    public <T> T queryEntity(String sql, Class<T> type, Object... params) {
        List<T> list = query(sql, createRowMapper(type), params);

        if (list != null && !list.isEmpty()) {
            return list.get(0);
        }

        return null;
    }

    /**
     * 通过回调函数查询SQL，返回列表或者对象数据
     */
    public <T> T query(String sql, ResultSetExtractor<T> rse) {
        return jdbcTemplate.query(sql, rse);
    }

    /**
     * 查询SQL，返回列表数据
     */
    public <T> List<T> query(String sql, Class<T> type) {
        return jdbcTemplate.query(sql, createRowMapper(type));
    }

    /**
     * 查询SQL，返回列表数据
     */
    public <T> List<T> query(String sql, Class<T> type, Object... params) {
        return jdbcTemplate.query(sql, createRowMapper(type), params);
    }


    /*================以下为具名参数方法================================================*/
    public int queryIntN(String sql, Map<String, ?> params) {
        List<Integer> list = namedJdbcTemplate.query(sql, params, (resultSet, i) -> resultSet.getInt(1));
        if (list != null && !list.isEmpty()) {
            return list.get(0);
        }

        return 0;
    }

    public <T> T queryEntityN(String sql, Map<String, ?> params, Class<T> type) {
        List<T> list = queryN(sql, params, type);

        if (list != null && !list.isEmpty()) {
            if (list.size() > 1) throw new SwException("您调用的查询单个对象的方法，结果却查出了多条！");
            return list.get(0);
        }

        return null;
    }

    public <T> List<T> queryN(String sql, Map<String, ?> params, Class<T> type) {
        return namedJdbcTemplate.query(sql, params, createRowMapper(type));
    }

    /**
     * 执行更新SQL
     */
    public int updateN(String sql, Map<String, ?> params) {
        return namedJdbcTemplate.update(sql, params);
    }

    public int[] batchUpdateN(String sql, List<Map<String, Object>> params) {
        return namedJdbcTemplate.batchUpdate(sql, (Map<String, Object>[]) params.toArray());
    }

    /**
     * 翻页查询
     */
    public <T> List<T> pagedQueryN(String sql, Class<T> type, int start, int limit, Map<String, ?> params) {
        return queryN(sql + " LIMIT " + start + "," + limit, params, type);
    }

    /*=================以上为具名参数方法==================================================*/
    public boolean isExists(String sql, Object... params) {
        try {
            Object v = jdbcTemplate.queryForObject(sql + " LIMIT 0, 1", Object.class, params);
            return (v != null);
        } catch (EmptyResultDataAccessException e) {
            return false;
        }
    }

    private <T> RowMapper<T> createRowMapper(Class<T> type) {
        RowMapper<T> rowMapper;
        if (BaseBean.class.isAssignableFrom(type)) {
            rowMapper = new BaseBeanPropertyRowMapper<>(type);
        } else if (java.util.Map.class.isAssignableFrom(type)) {
            if (SwMap.class.equals(type)) {
                rowMapper = new SwMapPropertyRowMapper<>(type);
            } else {
                rowMapper = new MapPropertyRowMapper<>(type);
            }
        } else {
            rowMapper = new BeanPropertyRowMapper<>(type);
        }
        return rowMapper;
    }

    /**
     * 翻页查询
     */
    public <T> List<T> pagedQuery(String sql, Class<T> type, int start, int limit, Object... params) {
        return jdbcTemplate.query(sql + " LIMIT " + start + "," + limit, createRowMapper(type), params);
    }

    /**
     * 翻页查询
     */
    public <T> List<T> pagedQuery(String sql, RowMapper<T> rowMapper, int start, int limit, Object... params) {
        return jdbcTemplate.query(sql + " LIMIT " + start + "," + limit, rowMapper, params);
    }
}
