package com.wgcloud.util.jdbc;

import cn.hutool.core.collection.CollectionUtil;
import com.wgcloud.config.CommonConfig;
import com.wgcloud.entity.DbInfo;
import com.wgcloud.entity.DbTable;
import com.wgcloud.service.DbInfoService;
import com.wgcloud.service.LogInfoService;
import com.wgcloud.util.DateUtil;
import com.wgcloud.util.FormatUtil;
import com.wgcloud.util.ThreadPoolUtil;
import com.wgcloud.util.msg.WarnMailUtil;
import com.wgcloud.util.msg.WarnPools;
import com.wgcloud.util.staticvar.StaticKeys;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * @version v3.3
 * @ClassName:ConnectionUtil.java
 * @author: http://www.wgstart.com
 * @date: 2021年1月16日
 * @Description: ConnectionUtil.java
 * @Copyright: 2019-2021 wgcloud. All rights reserved.
 */
@Component
public class ConnectionUtil {
    private static final Logger logger = LoggerFactory.getLogger(ConnectionUtil.class);
    @Resource
    private LogInfoService logInfoService;
    @Resource
    private DbInfoService dbInfoService;
    @Autowired
    CommonConfig commonConfig;

    public JdbcTemplate getJdbcTemplate(DbInfo dbInfo) throws Exception {
        JdbcTemplate jdbcTemplate = null;
        String driver = "";
        String url = dbInfo.getDbUrl();
        if (RDSConnection.KEY_MYSQL.equals(dbInfo.getDbType())) {
            driver = RDSConnection.DRIVER_MYSQL;
        } else if (RDSConnection.KEY_MARIADB.equals(dbInfo.getDbType())) {
            driver = RDSConnection.DRIVER_MARIADB;
        } else if (RDSConnection.KEY_POSTGRESQL.equals(dbInfo.getDbType())) {
            driver = RDSConnection.DRIVER_POSTGRESQL;
        } else if (RDSConnection.KEY_SQLSERVER.equals(dbInfo.getDbType())) {
            driver = RDSConnection.DRIVER_SQLSERVER;
        } else if (RDSConnection.KEY_DB2.equals(dbInfo.getDbType())) {
            driver = RDSConnection.DRIVER_DB2;
        } else {
            driver = RDSConnection.DRIVER_ORACLE;
        }
        try {
            //创建连接池
            DriverManagerDataSource dataSource = new DriverManagerDataSource();
            dataSource.setDriverClassName(driver);
            dataSource.setUrl(url);
            dataSource.setUsername(dbInfo.getUserName());
            dataSource.setPassword(dbInfo.getPasswd());
            jdbcTemplate = new JdbcTemplate(dataSource);
            jdbcTemplate.setQueryTimeout(120);//超时120s
            if (RDSConnection.KEY_MYSQL.equals(dbInfo.getDbType())) {
                jdbcTemplate.queryForRowSet(RDSConnection.MYSQL_VERSION);
            } else if (RDSConnection.KEY_MARIADB.equals(dbInfo.getDbType())) {
                jdbcTemplate.queryForRowSet(RDSConnection.MYSQL_VERSION);
            } else if (RDSConnection.KEY_POSTGRESQL.equals(dbInfo.getDbType())) {
                jdbcTemplate.queryForRowSet(RDSConnection.MYSQL_VERSION);
            } else if (RDSConnection.KEY_SQLSERVER.equals(dbInfo.getDbType())) {
                jdbcTemplate.queryForRowSet(RDSConnection.SQLSERVER_VERSION);
            } else if (RDSConnection.KEY_DB2.equals(dbInfo.getDbType())) {
                jdbcTemplate.queryForRowSet(RDSConnection.DB2_VERSION);
            } else {
                jdbcTemplate.queryForRowSet(RDSConnection.ORACLE_VERSION);
            }
            dbInfo.setDbState(StaticKeys.ON_STATE);
            if (null != WarnPools.MEM_WARN_MAP && null != WarnPools.MEM_WARN_MAP.get(dbInfo.getId())) {
                Runnable runnable = () -> {
                    WarnMailUtil.sendDbDown(dbInfo, false);
                };
                ThreadPoolUtil.executor.execute(runnable);
            }
            //只有数据源在线时候，才更新时间，连接失败就不更新时间
            dbInfo.setCreateTime(DateUtil.getNowTime());
            dbInfoService.updateById(dbInfo);
            return jdbcTemplate;
        } catch (Exception e) {
            jdbcTemplate = null;
            logger.error("连接数据库错误", e);
            dbInfo.setDbState(StaticKeys.DOWN_STATE);
            dbInfoService.updateById(dbInfo);
            Runnable runnable = () -> {
                WarnMailUtil.sendDbDown(dbInfo, true);
            };
            ThreadPoolUtil.executor.execute(runnable);
        }
        return null;
    }

    public long queryTableCount(DbInfo dbInfo, JdbcTemplate jdbcTemplate, DbTable dbTable) {
        try {
            dbTable.setState(StaticKeys.ON_STATE);
            String sqlinkey = FormatUtil.haveSqlDanger(dbTable.getWhereVal(), commonConfig.getSqlInKeys());
            if (!StringUtils.isEmpty(sqlinkey)) {
                String errinfo = "统计SQL语句含有sql敏感字符" + sqlinkey;
                logger.error("统计数据表错误：" + errinfo);
                logInfoService.save(errinfo + "：" + dbInfo.getAliasName(), "数据库别名：" + dbInfo.getAliasName() + "，数据表别名：" + dbTable.getRemark()
                        + "，错误信息：" + errinfo, StaticKeys.LOG_XTCZ);
                return 0;
            }

            if (null == jdbcTemplate) {
                dbTable.setState(StaticKeys.DOWN_STATE);
                return 0;
            }
            List<Map<String, Object>> list = jdbcTemplate.queryForList(dbTable.getWhereVal());
            if (CollectionUtil.isEmpty(list)) {
                return 0;
            } else {
                //取第一行
                Map<String, Object> mapResult = list.get(0);
                String resultStr = "0";
                if (StringUtils.isEmpty(dbTable.getTableName())) {
                    //如果没有设置取值列名，默认取值
                    for (String key : mapResult.keySet()) {
                        resultStr = mapResult.get(key).toString();
                    }
                } else {
                    resultStr = mapResult.get(dbTable.getTableName()).toString();
                }
                if (resultStr.indexOf(".") > -1) {
                    return Double.valueOf(resultStr).longValue();
                } else {
                    return Long.valueOf(resultStr);
                }
            }
        } catch (Exception e) {
            dbTable.setState(StaticKeys.DOWN_STATE);
            logger.error("统计数据表错误：", e);
            logInfoService.save("统计数据表sql执行错误：" + dbInfo.getAliasName(), "数据库别名：" + dbInfo.getAliasName() + "，数据表别名：" + dbTable.getRemark() +
                    "，错误信息：" + e.toString(), StaticKeys.LOG_XTCZ);
            return 0;
        }
    }

}