package com.usoftchina.bi.server.service.dataSource;

import com.usoftchina.bi.core.exception.MyException;
import com.usoftchina.bi.core.jdbc.DynamicDataSourceContextHolder;
import com.usoftchina.bi.server.dao.dataSource.DataColumnMapper;
import com.usoftchina.bi.server.dao.dataSource.DataConnectorMapper;
import com.usoftchina.bi.server.model.bo.ToSql;
import com.usoftchina.bi.server.model.po.Databases;
import com.usoftchina.bi.core.base.RepCode;
import com.usoftchina.bi.core.base.RepEntity;
import com.usoftchina.bi.server.model.vo.dataVo.ColumnTypeInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;

@Service
public class ImplementSqlService {
    @Autowired
    DataColumnMapper dataColumnMapper;
    @Autowired
    DataConnectorMapper dataConnectorMapper;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /*
    执行数据源
     */
    public RepEntity implementSql(ToSql toSql) {
        List<String> checkSql = Arrays.asList(toSql.getStrSql().toLowerCase().split(" "));
        if (checkSql.contains("update") || checkSql.contains("delete") || checkSql.contains("insert") ||
                checkSql.contains("drop") || checkSql.contains("create") || checkSql.contains("comment")){
            return new RepEntity(RepCode.SqlWarn);
        }
        String sqlStr = "select * from (" + toSql.getStrSql() + ") where rownum <=1";
        if ("".equals(sqlStr) || sqlStr == null) {
            return new RepEntity(RepCode.ChartsNameNull);
        }

        SqlRowSet sqlRowSet = null;
        try {
            sqlRowSet = jdbcTemplate.queryForRowSet(sqlStr);
        } catch (DataAccessException e) {
            throw new MyException(e.getCause().getMessage());
        }
        SqlRowSetMetaData metaData = sqlRowSet.getMetaData();
        int length = metaData.getColumnCount();
        List<ColumnTypeInfo> columnTypeInfoList = new ArrayList<>();
        for (int i = 1; i <= length; i++) {
            String typeName = metaData.getColumnClassName(i);
            typeName = metaData.getColumnClassName(i).substring(typeName.lastIndexOf(".") + 1);
            columnTypeInfoList.add(new ColumnTypeInfo(metaData.getColumnName(i), typeName));
        }
        return new RepEntity(RepCode.success,columnTypeInfoList);
    }

    /*
    判断列类型
     */
    public LinkedHashMap<String, String> getColumnType(LinkedHashMap<String, Object> columnData){
        LinkedHashMap<String, String> tarValue = new LinkedHashMap<>();
            LinkedHashMap<String, Object> columnDataMap = columnData;
            Iterator<String> cm = columnDataMap.keySet().iterator();
            while (cm.hasNext()){
                String key = cm.next();
                if (tarValue.containsKey(key)){
                    continue;
                }else {
                    Object value = columnDataMap.get(key);
                    if (value == null) {
                        continue;
                    }else {
                        String values = getType(value);
                        tarValue.put(key, values);
                    }
                    if (tarValue.size() == columnDataMap.size()){
                        break;
                    }
                }
            }
        return tarValue;
    }

    /*
    判断值类型
     */
    public String getType(Object obj){
        if(obj instanceof String){
            return "String";
        }else if (obj instanceof Double){
            return "Double";
        }else if (obj instanceof Integer){
            return "Integer";
        }else if (obj instanceof Date){
            return "Date";
        }else if (obj instanceof Boolean){
            return "Boolean";
        }else if(obj instanceof Byte){
            return "Byte";
        }else if (obj instanceof Short){
            return "Short";
        }else if (obj instanceof BigDecimal){
            return "BigDecimal";
        }else if (obj instanceof Object){
            return "Object";
        }else {
            return "";
        }
    }



    /*
    判断数据库连接
     */
    public RepEntity connectTest(Databases databases) {
        String password = "";
        String passwords = databases.getPassWord();
        int id = databases.getId();
        String pw = dataConnectorMapper.getBasesById(id);
        if (passwords != null && !"".equals(passwords)){
            password = passwords;
        }else {
            password = pw;
        }

        String url = "jdbc:oracle:thin:@" + databases.getAddrass() + ":" + databases.getPort() + ":" + databases
                .getDataName();
        String username = databases.getUserName();
        Connection con = null;
        try {
            DriverManager.setLoginTimeout(6);
            con = DriverManager.getConnection(url, username, password);
            con.close();
        } catch (SQLException se) {
            se.printStackTrace();
            throw new MyException("数据库连接信息有误!");
        }
        return new RepEntity(RepCode.success);
    }
}
