package cn.autoform.fw.service;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import cn.autoform.db.entity.DBConnectionManagerEntity;
import cn.autoform.fw.mapper.DbConnectionMapper;
import cn.autoform.web.mapper.datasource.DatasourceMapper;

/**
 * 获取数据库元数据信息
 * 
 * @author liumy
 *
 */
@Service
public class DatabaseMetaDateService {

	@Autowired
	DbConnectionMapper dbConnectionMapper = null;
	
	@Autowired
	private DatasourceMapper datasourceMapper = null;

	private static final Map<String, String> DBTYPEMAP = new HashMap<>();
	
	private static final String REPLACEURL = "||replaceURL||";
	
	private static final Logger logger = LoggerFactory.getLogger(DatabaseMetaDateService.class);
	
	static {
		DBTYPEMAP.put("001", "com.mysql.jdbc.Driver");
		DBTYPEMAP.put("002", "oracle.jdbc.driver.OracleDriver");
		DBTYPEMAP.put("003", "com.microsoft.sqlserver.jdbc.SQLServerDriver");
		DBTYPEMAP.put("001-URL", "jdbc:mysql://||replaceURL||?characterEncoding=utf8&useSSL=true");
		DBTYPEMAP.put("002-URL", "jdbc:oracle:thin:@||replaceURL||");
		DBTYPEMAP.put("003-URL", "jdbc:sqlserver://||replaceURL||");
	}

	/**
	 * 获取数据库表名
	 * 
	 * @param tenantID
	 * @param dataConnectionName
	 * @return
	 * @throws ClassNotFoundException 
	 */
	public List<String> getDatabaseTableName(String tenantID, String dataConnectionName) {
		List<String> listTableNames = new ArrayList<>();

		DatabaseMetaData dbMetaData = null;

		Connection con = null;

		try {

			DBConnectionManagerEntity dbConnectionObj = dbConnectionMapper.queryDBconnectionManager(tenantID,
					dataConnectionName);

			Class.forName(DBTYPEMAP.get(dbConnectionObj.getdBType()));

			String url = DBTYPEMAP.get(dbConnectionObj.getdBType() + "-URL").replace(REPLACEURL,
					dbConnectionObj.getdBconnectionString());
			System.out.println(url);
			String user = dbConnectionObj.getdBUserID();
			String password = dbConnectionObj.getdBPassword();

			con = DriverManager.getConnection(url, user, password);
			String schema = "";
			if(dbConnectionObj.getdBType().equals("003")){
				schema = con.getSchema();
			} else {
				schema = user.toUpperCase();
			}
			
			dbMetaData = con.getMetaData();
			
			String[] types = { "TABLE" };
			
			ResultSet rs = dbMetaData.getTables(con.getCatalog(), schema, "%", types);
			
			while (rs.next()) {
				listTableNames.add(rs.getString("TABLE_NAME"));
			}
		} catch (ClassNotFoundException e) {
			logger.error("SYSTEM ERROR!", e);
		} catch (SQLException e) {
			logger.error("SYSTEM ERROR!", e);
		} finally {
			try {
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				logger.error("SYSTEM ERROR!", e);
			}
		}
		return listTableNames;
	}

	/**
	 * 获取数据库表列名
	 * 
	 * @param tenantID
	 * @param dataConnectionName
	 * @return
	 * @throws Exception 
	 */
	public List<String> getDatabaseColumnsName(String tenantID, String dataConnectionName, String datasource){

		List<String> listColumnsNames = new ArrayList<>();
		
		DatabaseMetaData dbMetaData = null;

		Connection con = null;
		
		try {

			DBConnectionManagerEntity dbConnectionObj = dbConnectionMapper.queryDBconnectionManager(tenantID,
					dataConnectionName);

			Class.forName(DBTYPEMAP.get(dbConnectionObj.getdBType()));

			String url = DBTYPEMAP.get(dbConnectionObj.getdBType() + "-URL").replace(REPLACEURL,
					dbConnectionObj.getdBconnectionString());

			String user = dbConnectionObj.getdBUserID();
			String password = dbConnectionObj.getdBPassword();
			con = DriverManager.getConnection(url, user, password);
			
			dbMetaData = con.getMetaData();
			String schema = "";
			if(dbConnectionObj.getdBType().equals("003")){
				schema = con.getSchema();
			} else {
				schema = user.toUpperCase();
			}
			ResultSet rs = dbMetaData.getColumns(null, schema, datasource, "%");

			while (rs.next()) {
				listColumnsNames.add(rs.getString("COLUMN_NAME"));
			}		
			
		} catch (ClassNotFoundException e) {
			logger.error("SYSTEM ERROR!", e);
		} catch (SQLException e) {
			logger.error("SYSTEM ERROR!", e);
		} finally {
			try {
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				logger.error("SYSTEM ERROR!", e);
			}
		}
		return listColumnsNames;
	}
	
	
	/**
	 * 获取数据库列信息
	 * 
	 * @param tenantID
	 * @param dataConnectionName
	 * @return key:列名 value:列值List
	 * @throws Exception 
	 */
	public Map<String, List<String>> getDatabaseColumnsInfo(String tenantID, String dataConnectionName, String datasource) {

		DatabaseMetaData dbMetaData = null;

		Connection con = null;
		
		PreparedStatement  pstmt = null;
		Map<String, List<String>> rsMap = new HashMap<>();
		try {

			DBConnectionManagerEntity dbConnectionObj = dbConnectionMapper.queryDBconnectionManager(tenantID,
					dataConnectionName);

			Class.forName(DBTYPEMAP.get(dbConnectionObj.getdBType()));

			String url = DBTYPEMAP.get(dbConnectionObj.getdBType() + "-URL").replace(REPLACEURL,
					dbConnectionObj.getdBconnectionString());

			String user = dbConnectionObj.getdBUserID();
			String password = dbConnectionObj.getdBPassword();
			con = DriverManager.getConnection(url, user, password);
			
			dbMetaData = con.getMetaData();
			String schema = "";
			if(dbConnectionObj.getdBType().equals("003")){
				schema = con.getSchema();
			} else {
				schema = user.toUpperCase();
			}
			ResultSet rs = dbMetaData.getColumns(null, schema, datasource, "%");
			while (rs.next()) {
				String columnsName = rs.getString("COLUMN_NAME");
				String sql = "SELECT "+ columnsName + " FROM " + datasource;
				List<String> listValue = new ArrayList<>();
				
				pstmt = con.prepareStatement(sql);
				ResultSet rsValue = pstmt.executeQuery();
				
				pstmt.close();
				pstmt = null;
				
				while (rsValue.next()) {
					listValue.add(rsValue.getString(columnsName));
				}
				
				
				rsMap.put(columnsName, listValue);	
			}
			con.close();
			con = null;
			
		} catch (ClassNotFoundException e) {
			logger.error("SYSTEM ERROR!", e);
		} catch (SQLException e) {
			logger.error("SYSTEM ERROR!", e);
		} finally {
			try {
				if (pstmt !=null) {
					pstmt.close();
				}
			} catch (SQLException e) {
				logger.error("SYSTEM ERROR!", e);
			}
			try {
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				logger.error("SYSTEM ERROR!", e);
			}
		}
		return rsMap;
	}
	
	/**
	 * 获取列值
	 * @param formdatsource
	 * @param columnName
	 * @return
	 * @throws Exception 
	 */
	public List<String> getColumnsInfo(String tenantID, String databaseName,String datasource,String columnName){

		Connection con = null;
		PreparedStatement  pstmt = null;
		System.out.println("++++++++++"+databaseName+"**********"+datasource);
		List<Map<String,Object>> list=datasourceMapper.getDateName(databaseName);
		String dbName=(String)list.get(0).get("DBTYPE");
		if(dbName.equals("001")){
			dbName="mysql";
		}else if(dbName.equals("002")){
			dbName="oracle";
		}else if(dbName.equals("003")){
			dbName="microsoft:sqlserver";
		}
		
		List<String> listValue = new ArrayList<>();
		try {

			DBConnectionManagerEntity dbConnectionObj = dbConnectionMapper.queryDBconnectionManager(tenantID,
					databaseName);
			System.out.println("进入方法提");
			Class.forName(DBTYPEMAP.get(dbConnectionObj.getdBType()));
			System.out.println(DBTYPEMAP.get(dbConnectionObj.getdBType()));
			System.out.println("jdbc:"+dbName+"://"+list.get(0).get("DBCONNECTIONSTRING")+"?characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8");
			String url = "jdbc:"+dbName+"://"+list.get(0).get("DBCONNECTIONSTRING")+"?characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8";
			//String url = "jdbc:mysql://122.114.176.216:3306/epc_tenant_1?characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8";
					//DBTYPEMAP.get(dbConnectionObj.getdBType() + "-URL").replace(REPLACEURL,
					//dbConnectionObj.getdBconnectionString());

			String user = dbConnectionObj.getdBUserID();
			String password = dbConnectionObj.getdBPassword();
			con = DriverManager.getConnection(url, user, password);
			System.out.println(con);
			
			// String sql = "SELECT ? FROM ? ";
			String sql = "SELECT "+ columnName + " FROM " + datasource;
			pstmt = con.prepareStatement(sql);

			// pstmt.setString(1, columnName);
			// pstmt.setString(2, datasource);

			ResultSet rsValue = pstmt.executeQuery(sql);
			while (rsValue.next()) {
				listValue.add(rsValue.getString(columnName));
			}
			pstmt.close();
			pstmt = null;
			
			con.close();
			con = null;
		} catch (ClassNotFoundException e) {
			logger.error("SYSTEM ERROR!", e);
		} catch (SQLException e) {
			logger.error("SYSTEM ERROR!", e);
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}

			} catch (SQLException e) {
				logger.error("SYSTEM ERROR!", e);
			}

			
			try {
				if (con != null) {
					con.close();
				}

			} catch (SQLException e) {
				logger.error("SYSTEM ERROR!", e);
			}
		}
		
		return listValue;
	}
	
	

}