package com.code2roc.fastface.db;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Component;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Component
public class JDBCDTO {
    private Logger logger = LoggerFactory.getLogger(JDBCDTO.class);
    @Autowired
    private MasterJdbcTemplate jdbcTemplate;

    public List<LinkedHashMap<String, Object>> findListByParam(String sqlText, Map<String, Object> map) {

        List<LinkedHashMap<String, Object>> result = new ArrayList<>();
        List<Object> paramList = new ArrayList<>();
        //解析sqlText中的占位符#{xxxx}
        String regex = "\\#\\{(?<RegxName>[\\w.]*)\\}";
        String sqlTextCopy = sqlText;
        Pattern pattern = Pattern.compile(regex);

        Matcher matcher = pattern.matcher(sqlTextCopy);
        while (matcher.find()) {
            String paramNameSymbol = matcher.group(0);
            sqlText = sqlText.replace(paramNameSymbol, " ? ");
        }
        logger.debug("【sqlText】：" + sqlText);

        //参数赋值
        matcher = pattern.matcher(sqlTextCopy);
        while (matcher.find()) {
            String paramNameSymbol = matcher.group(0);
            String paramName = paramNameSymbol.replace("#", "").replace("{", "").replace("}", "");
            Object paramValue = map.get(paramName);
            logger.debug("【paramName】：" + paramName);
            logger.debug("【paramValue】：" + paramValue);
            paramList.add(paramValue);
        }

        jdbcTemplate.query(sqlText, paramList.toArray(), new ResultSetExtractor<Object>() {
            @Override
            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                try {
                    ResultSetMetaData rsMetaData = rs.getMetaData();
                    while (rs.next()) {
                        LinkedHashMap<String, Object> resultitem = new LinkedHashMap<>();
                        for (int i = 1; i <= rsMetaData.getColumnCount(); i++) {
                            String columnName = "";
                            columnName = rsMetaData.getColumnName(i);
                            Object columnValue = rs.getObject(columnName);
                            resultitem.put(columnName, columnValue);
                        }
                        result.add(resultitem);
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    return null;
                }
            }
        });
        return result;
    }

    public int executeSQL(String sqlText, Map<String, Object> map){
        List<Object> paramList = new ArrayList<>();
        //解析sqlText中的占位符#{xxxx}
        String regex = "\\#\\{(?<RegxName>[\\w.]*)\\}";
        String sqlTextCopy = sqlText;
        Pattern pattern = Pattern.compile(regex);

        Matcher matcher = pattern.matcher(sqlTextCopy);
        while (matcher.find()) {
            String paramNameSymbol = matcher.group(0);
            sqlText = sqlText.replace(paramNameSymbol, " ? ");
        }

        logger.debug("【sqlText】：" + sqlText);

        //参数赋值
        matcher = pattern.matcher(sqlTextCopy);
        while (matcher.find()) {
            String paramNameSymbol = matcher.group(0);
            String paramName = paramNameSymbol.replace("#", "").replace("{", "").replace("}", "");
            Object paramValue = map.get(paramName);
            logger.debug("【paramName】：" + paramName);
            logger.debug("【paramValue】：" + paramValue);
            paramList.add(paramValue);
        }

        return jdbcTemplate.update(sqlText,paramList.toArray());
    }

    public Object executeScar(String sqlText, Map<String, Object> map){
        List<Object> paramList = new ArrayList<>();
        //解析sqlText中的占位符#{xxxx}
        String regex = "\\#\\{(?<RegxName>[\\w.]*)\\}";
        String sqlTextCopy = sqlText;
        Pattern pattern = Pattern.compile(regex);

        Matcher matcher = pattern.matcher(sqlTextCopy);
        while (matcher.find()) {
            String paramNameSymbol = matcher.group(0);
            sqlText = sqlText.replace(paramNameSymbol, " ? ");
        }

        logger.debug("【sqlText】：" + sqlText);

        //参数赋值
        matcher = pattern.matcher(sqlTextCopy);
        while (matcher.find()) {
            String paramNameSymbol = matcher.group(0);
            String paramName = paramNameSymbol.replace("#", "").replace("{", "").replace("}", "");
            Object paramValue = map.get(paramName);
            logger.debug("【paramName】：" + paramName);
            logger.debug("【paramValue】：" + paramValue);
            paramList.add(paramValue);
        }

        Object result;
        try {
            result = jdbcTemplate.queryForObject(sqlText, Object.class, paramList.toArray());
        } catch (EmptyResultDataAccessException e) {
            result = null;
        }
        return result;
    }

    public void batchExecuteSQL(String sqlText, List<Map<String, Object>> mapList){

        List<Object[]> paramList = new ArrayList<>();
        //解析sqlText中的占位符#{xxxx}
        String regex = "\\#\\{(?<RegxName>[\\w.]*)\\}";
        String sqlTextCopy = sqlText;
        Pattern pattern = Pattern.compile(regex);

        Matcher matcher = pattern.matcher(sqlTextCopy);
        while (matcher.find()) {
            String paramNameSymbol = matcher.group(0);
            sqlText = sqlText.replace(paramNameSymbol, " ? ");
        }

        logger.debug("【sqlText】：" + sqlText);

        //参数赋值
        for (Map<String, Object> map:mapList) {
            matcher = pattern.matcher(sqlTextCopy);
            List<Object> singleParamList = new ArrayList<>();
            while (matcher.find()) {
                String paramNameSymbol = matcher.group(0);
                String paramName = paramNameSymbol.replace("#", "").replace("{", "").replace("}", "");
                Object paramValue = map.get(paramName);
                logger.debug("【paramName】：" + paramName);
                logger.debug("【paramValue】：" + paramValue);
                singleParamList.add(paramValue);
            }
            paramList.add(singleParamList.toArray());
        }
        jdbcTemplate.batchUpdate(sqlText,paramList);
    }
}