package com.topdraw.platform.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Component
@Slf4j
public class DbUtil {
    @Resource
    DataSource dataSource;

    public Connection getConnection() throws SQLException {
        Connection connection = null;
        if (dataSource != null) {
            connection = dataSource.getConnection();
            connection.setAutoCommit(true);
        }
        return connection;
    }

    public void close(Connection conn) {
        if (conn != null) {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 查询唯一结果集
     *
     * @param conn
     * @param strSqlCommand
     * @param parameters
     * @return
     * @throws SQLException
     */
    public Map<String, Object> queryUniqueResult(Connection conn, String strSqlCommand, Object... parameters)
            throws SQLException {
        QueryRunner runner = new QueryRunner();
        MapHandler handler = new MapHandler();
        return runner.query(conn, strSqlCommand, handler, parameters);
    }

    /**
     * 列表查询
     *
     * @param conn
     * @param strSqlCommand
     * @param parameters
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryList(Connection conn, String strSqlCommand, Object... parameters)
            throws SQLException {
        QueryRunner runner = new QueryRunner();
        MapListHandler handler = new MapListHandler();
        return runner.query(conn, strSqlCommand, handler, parameters);
    }

    /***
     * 记录保存
     *
     * @param conn
     * @param mapData
     * @param objectName
     * @return
     * @throws SQLException
     */
    public <T> T save(Connection conn, Map<String, Object> mapData, String objectName) throws SQLException {
        if (!mapData.containsKey("update_time") || mapData.get("update_time") == null) {
            mapData.put("update_time", new Date());
        }
        if (!mapData.containsKey("create_time") || mapData.get("create_time") == null) {
            mapData.put("create_time", new Date());
        }
        Iterator<String> it = mapData.keySet().iterator();

        String key;
        StringBuilder sbInsert = new StringBuilder();
        sbInsert.append("INSERT INTO ").append(objectName);

        List<Object> valueList = new ArrayList<>();
        List<String> filedNameList = new ArrayList<>();
        List<String> preValueList = new ArrayList<>();

        while (it.hasNext()) {
            key = it.next();
            filedNameList.add(key);
            preValueList.add("?");
            valueList.add(mapData.get(key));
        }
        sbInsert.append(" ( `").append(StringUtils.collectionToDelimitedString(filedNameList, "`,`"))
                .append("` ) VALUES ( ").append(StringUtils.collectionToDelimitedString(preValueList, ",")).append(")");
        QueryRunner run = new QueryRunner();

        return run.insert(conn, sbInsert.toString(), new InsertResultHandler<T>(), valueList.toArray());
    }

    private class InsertResultHandler<T> implements ResultSetHandler<T> {
        @SuppressWarnings("unchecked")
        @Override
        public T handle(ResultSet rs) throws SQLException {
            T t;
            if (rs.next()) {
                t = (T) rs.getObject(1);
            } else {
                // throw new NoIdGeneratedException("No id generated from database...");
                return null;
            }
            return t;
        }
    }

    /**
     * 记录更新
     *
     * @param conn
     * @param mapData
     * @param objectName
     * @param strPrimaryKey
     * @return
     * @throws SQLException
     */
    public long update(Connection conn, Map<String, Object> mapData, String objectName, String strPrimaryKey)
            throws SQLException {
        if (!mapData.containsKey("update_time") || mapData.get("update_time") == null) {
            mapData.put("update_time", new Date());
        }

        Iterator<String> it = mapData.keySet().iterator();
        String key;
        List<Object> valList = new ArrayList<>();
        StringBuffer updateSql = new StringBuffer(128);
        updateSql.append("UPDATE ").append(objectName).append(" SET ");
        while (it.hasNext()) {
            key = it.next();
            if (key.equalsIgnoreCase(strPrimaryKey)) {
                continue;
            }
            updateSql.append("`" + key + "`").append("=?,");
            valList.add(mapData.get(key));
        }
        updateSql.deleteCharAt(updateSql.length() - 1);
        updateSql.append(" WHERE `" + strPrimaryKey + "`= ? ");
        valList.add(mapData.get(strPrimaryKey));
        return doExecute(conn, updateSql.toString(), valList.toArray());
    }

    /**
     * sql执行
     *
     * @param conn
     * @param sql
     * @param parameters
     * @return
     * @throws SQLException
     */
    public long doExecute(Connection conn, String sql, Object... parameters) throws SQLException {
        QueryRunner runner = new QueryRunner();
        return runner.update(conn, sql, parameters);
    }

    /**
     * 按主键删除记录
     *
     * @param conn
     * @param objectName
     * @param id
     * @return
     */
    public int deleteById(Connection conn, String objectName, String id) throws SQLException {
        String sql = "DELETE FROM " + objectName + " WHERE id = ? ";
        QueryRunner runner = new QueryRunner();
        return runner.update(conn, sql, id);
    }

    public void beginTransaction(Connection connection) throws Exception {
        connection.setAutoCommit(false);
        connection.setReadOnly(false);
    }

    public void commitTransaction(Connection connection) throws SQLException {
        if (!connection.getAutoCommit()) {
            connection.commit();
        }
    }

    public void rollbackTransaction(Connection connection) throws SQLException {
        if (!connection.getAutoCommit()) {
            connection.rollback();
        }
    }
}