package com.ruoyi.imports.util;

import java.math.BigDecimal;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;



public class DbUtils {
	
	private static final Logger logger=LoggerFactory.getLogger(DbUtils.class);
	
	private DbConfig conf;
	
	private DbUtils(){
	}
	
	
	public static DbUtils newDbUtil(DbConfig conf)throws Exception{
		if(conf==null ){
			throw new Exception("无法实例化DbUtils,原因:末知的数据库配置");
		}
		String err=conf.validate();
		if(StringUtils.isNotBlank(err)){
			throw new Exception("无法实例化DbUtils,原因:"+err);	
		}
		
		DbUtils util=new DbUtils();
		
		util.conf=conf;
		
		return util;
		
	}
	
	public static DbUtils newDbUtil(String driver,String username,String pwd,String url)throws Exception{
		if(StringUtils.isBlank(driver) || StringUtils.isBlank(username) 
				|| StringUtils.isBlank(pwd) || StringUtils.isBlank(url)  ){
			throw new Exception("无法实例化DbUtils,缺少必要信息");
		}
		DbUtils util=new DbUtils();
		DbConfig conf=new DbConfig();
		conf.setDriver(driver);
		conf.setUsername(username);
		conf.setPwd(pwd);
		conf.setUrl(url);
		util.conf=conf;

		return util;
	}
	
	
//	public static void main(String[] args) throws Exception{
//		
//		DbUtils util=new DbUtils();
//		util.conf=new DbConfig();
//		
//		String sql="select * from t_member where userid=?";
//		List param=new ArrayList();
//		param.add(777777);
//		List<Map<String,Object>>list= util.executeQuery(sql, param);
//		Map<String,Object>map=list.get(0);
////		MyLogUtils.print(DbUtils.class, "查询结果:"+map.get("email"));
//		
//		sql="update t_member set email=? where userid=?";
//		param.clear();
//		param.add("fdfdfsdf");
//		param.add(777777);
//		
//		int c=util.executeUpdate(sql,param);
////		MyLogUtils.print(DbUtils.class, "更新行数:"+c);
//		
//		
//		sql="select * from t_member where userid=?";
//		param=new ArrayList();
//		param.add(777777);
//		list= util.executeQuery(sql, param);
//		map=list.get(0);
////		MyLogUtils.print(DbUtils.class, "查询结果:"+map.get("email"));
//		
//		
//	}
	
	
	public void executeSqlFile(String filePath)throws Exception {
//			   String url = "jdbc:mysql://localhost:3306/jeesite?useUnicode=true&characterEncoding=utf-8";
//			   String driver = "com.mysql.jdbc.Driver";
//			   String username = "root";
//			   String password ="root";
	  
	   String url = conf.getUrl();
	   String driver = conf.getDriver();
	   String username = conf.getUsername();
	   String password =conf.getPwd();
	   
			   
	   Class.forName(driver).newInstance();
	   Connection conn = (Connection) DriverManager.getConnection(url, username, password);
	   ScriptRunner runner = new ScriptRunner(conn);
//			   runner.setAutoCommit(false);
//			   runner.setStopOnError(true);
	   
	   runner.setAutoCommit(conf.isAutoCommit());
	   runner.setStopOnError(conf.isStopOnError());
	    
	   runner.setErrorLogWriter(null);
	   runner.setLogWriter(null);
	   runner.runScript(Resources.getResourceAsReader(filePath));
//			   runner.runScript(Resources.getResourceAsReader("ddl/mysql/jpetstore-mysql-dataload.sql"));
			    
			   
	}
	
	public Connection getConnections()throws Exception{
		Connection connection = null;
		Class.forName(conf.getDriver());
		connection= DriverManager.getConnection(conf.getUrl(),conf.getUsername(),conf.getPwd());
		return connection;
	}
	
	
	@SuppressWarnings("rawtypes")
	public int executeUpdate(String sql,List param)throws Exception {
		Connection conn=getConnections();
		
		PreparedStatement ps = conn.prepareStatement(sql);
		if(param!=null){
            for(int i=0;i<param.size();i++){
                Object obj = param.get(i);  
                this.setParameterValue(ps, i+1, obj);  
            }  
        }
		
		int count = ps.executeUpdate();
		close(ps);
		close(conn);
		return count;
	}
	
	@SuppressWarnings("rawtypes")
	public int executeUpdate(Connection conn,String sql,List param)throws Exception {
		
		PreparedStatement ps = conn.prepareStatement(sql);
		
		if(param!=null){  
            for(int i=0;i<param.size();i++){
                Object obj = param.get(i);  
                this.setParameterValue(ps, i+1, obj);  
            }  
        }
		
		int count = ps.executeUpdate();
		close(ps);
		
		return count;
	}
	
	@SuppressWarnings("rawtypes")
	public List<Map<String,Object>> executeQuery(String sql,List param)throws Exception {
		Connection conn=getConnections();
		PreparedStatement ps = conn.prepareStatement(sql);
		
        if(param!=null){  
            for(int i=0;i<param.size();i++){
                Object obj = param.get(i);  
                this.setParameterValue(ps, i+1, obj);  
            }  
        }
          
        ResultSet rs = ps.executeQuery();  
        List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();  
        Map<String,Integer> metaDataMap = null;  
        while(rs.next()){  
            if(rs.isFirst()){  
                metaDataMap = this.getMetaData(rs);  
            }  
            dataList.add(this.setData(rs,metaDataMap));  
        }  
        
        dataList=upperCaseKey(dataList);
        
        close(rs);
        close(ps);
        close(conn);
        
		return dataList;
	}
	
	private List<Map<String,Object>>upperCaseKey(List<Map<String,Object>>list){
		List<Map<String,Object>>newList=new ArrayList<Map<String,Object>>();
		if(list!=null && !list.isEmpty()){
			for(Map<String,Object>map:list){
				newList.add(upperCaseKey(map));
			}
		}
		
		return newList;
	}
	private Map<String,Object> upperCaseKey(Map<String,Object> map){
		Map<String,Object>newMap=new HashMap<String,Object>();
		if(map!=null && !map.isEmpty()){
			for(Map.Entry<String, Object>ent:map.entrySet()){
				newMap.put(ent.getKey().toUpperCase(), ent.getValue());
			}
		}
		return newMap;
	}
	
	
	private void setParameterValue(PreparedStatement pStatement, int piIndex,Object pValueObject) throws Exception {  
        if (pValueObject instanceof String) {  
            pStatement.setString(piIndex, (String) pValueObject);  
        } else if (pValueObject instanceof Boolean) {  
            pStatement.setBoolean(piIndex, ((Boolean) pValueObject).booleanValue());  
        } else if (pValueObject instanceof Byte) {  
            pStatement.setByte(piIndex, ((Byte) pValueObject).byteValue());  
        } else if (pValueObject instanceof Short) {  
            pStatement.setShort(piIndex, ((Short) pValueObject).shortValue());  
        } else if (pValueObject instanceof Integer) {  
            pStatement.setInt(piIndex, ((Integer) pValueObject).intValue());  
        } else if (pValueObject instanceof Long) {  
            pStatement.setLong(piIndex, ((Long) pValueObject).longValue());  
        } else if (pValueObject instanceof Float) {  
            pStatement.setFloat(piIndex, ((Float) pValueObject).floatValue());  
        } else if (pValueObject instanceof Double) {  
            pStatement.setDouble(piIndex, ((Double) pValueObject).doubleValue());  
        } else if (pValueObject instanceof BigDecimal) {  
            pStatement.setBigDecimal(piIndex, (BigDecimal) pValueObject);  
        } else if (pValueObject instanceof java.sql.Date) {  
            pStatement.setDate(piIndex, (java.sql.Date) pValueObject);  
        } else if (pValueObject instanceof Time) {  
            pStatement.setTime(piIndex, (Time) pValueObject);  
        } else if (pValueObject instanceof Timestamp) {  
            pStatement.setTimestamp(piIndex, (Timestamp) pValueObject);  
        } else {  
            pStatement.setObject(piIndex, pValueObject);  
        }  
    }  
  
    /** 
     * 根据传入的结果集返回结果集的元数据，以列名为键以列类型为值的map对象 
     * @param rs 
     * @return  
     * @throws SQLException 
     */  
    private Map<String,Integer> getMetaData(ResultSet rs) throws SQLException{  
        Map<String,Integer> map = new HashMap<String,Integer>();  
        ResultSetMetaData metaData = rs.getMetaData();  
        int numberOfColumns =  metaData.getColumnCount();  
        for(int column = 0; column < numberOfColumns; column++) {  
            String columnName = metaData.getColumnLabel(column+1);  
            int colunmType = metaData.getColumnType(column+1);  
            columnName = columnName.toLowerCase();  
            map.put(columnName, colunmType);  
        }  
        return map;  
    }  
      
    /** 
     * 将结果集封装为以列名存储的map对象 
     * @param rs 
     * @param metaDataMap元数据集合 
     * @return 
     * @throws Exception 
     */  
    private Map<String,Object> setData(ResultSet rs,Map<String,Integer> metaDataMap) throws Exception {  
        Map<String,Object> map = new HashMap<String,Object>();  
        for (String columnName : metaDataMap.keySet()) {  
            int columnType = metaDataMap.get(columnName);  
            Object object = rs.getObject(columnName);  
            if(object==null){  
                map.put(columnName, null);  
                continue;  
            }  
            switch (columnType) {  
            case java.sql.Types.VARCHAR:  
                map.put(columnName, object);  
                break;  
            case java.sql.Types.DATE:  
                map.put(columnName, DateUtils.formatDateTime( (java.sql.Date) object));  
                break;  
            case java.sql.Types.TIMESTAMP:  
                map.put(columnName, DateUtils.formatDateTime( (java.sql.Timestamp) object));  
                break;  
            case java.sql.Types.TIME:  
                map.put(columnName, DateUtils.formatDateTime( (java.sql.Time) object));  
                break;  
            case java.sql.Types.CLOB:  
                try{  
                    if(object!=null){  
                        Clob clob = (Clob)object;  
                        long length = clob.length();  
                        map.put(columnName, clob.getSubString(1L, (int)length));  
                    }  
                }catch(Exception e){  
                	logger.error( e.getMessage(),e);
                }  
                break;  
            case java.sql.Types.BLOB:  
                map.put(columnName, "");  
                break;  
            default:  
                map.put(columnName, object);  
                break;  
            }  
        }  
        return map;  
    }  
	
	public void close(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {

			logger.error( e.getMessage(),e);
		}
	}
	
	public void close(PreparedStatement ps) {
		try {
			if (ps != null) {
				ps.close();
				ps = null;
			}
		} catch (SQLException e) {

			logger.error( e.getMessage(),e);
		}
	}
	
	public void close(ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
				resultSet = null;
			}
		} catch (SQLException e) {

			logger.error( e.getMessage(), e);
		}
	}
	
	
	
	
	
}
