package com.walker.dbmeta.support;

import com.walker.connector.Address;
import com.walker.connector.support.DatabaseConnector;
import com.walker.connector.util.ConnectorUtils;
import com.walker.db.DatabaseException;
import com.walker.db.TableInfo;
import com.walker.db.page.GenericPager;
import com.walker.dbmeta.AbstractDatabaseMetaEngine;
import com.walker.dbmeta.FieldInfo;
import com.walker.dbmeta.util.DatabaseMetaEngineUtils;
import com.walker.dbmeta.util.DatabaseUtils;
import com.walker.infrastructure.utils.StringUtils;
import com.walker.jdbc.util.StringSqlUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * MySQL元数据引擎实现
 * @author shikeying
 * @date 2015年12月18日
 *
 */
public class MySQLMetaEngine extends AbstractDatabaseMetaEngine {

	public static final String SQL_GET_TBL_COUNT = "SELECT COUNT(*) cnt FROM information_schema.`TABLES`"
			+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";

	public final String QUERY_TABLE_FIELDS = "SELECT COLUMN_NAME from information_schema.COLUMNS "
			+ "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";

	public final String QUERY_COLUMNS_LIST = "select table_name,column_name,data_type,NUMERIC_PRECISION data_precision,NUMERIC_SCALE data_scale, column_key, column_comment"
			+ " from INFORMATION_SCHEMA.Columns"
			+ " where table_schema=(SELECT DATABASE()) and table_name like ? order by table_name,ordinal_position";

	public final String SQL_TABLES_ROW = "SELECT TABLE_NAME tname, TABLE_COMMENT summary, TABLE_ROWS trows"
			+ " FROM information_schema.`TABLES` "
			+ " WHERE TABLE_SCHEMA = :schema AND TABLE_TYPE = 'BASE TABLE'"
			+ " AND TABLE_NAME in (:ids)";

	public final String SQL_ONE_TABLE_ROW = "SELECT TABLE_ROWS trows"
			+ " FROM information_schema.`TABLES` "
			+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'"
			+ " AND TABLE_NAME = ?";
	public final String SQL_TABLE_LIKE = "SELECT TABLE_NAME "
			+ " FROM information_schema.`TABLES` "
			+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'"
			+ " AND TABLE_NAME like ?";

	// 模糊分页查询用户表集合
	public final String SQL_TABLE_LIKE_PAGE = "SELECT TABLE_NAME tname, TABLE_ROWS trows, TABLE_COMMENT summary, "
			+ "AVG_ROW_LENGTH avg_row_len, DATA_LENGTH blocks, UPDATE_TIME last_analyzed, ENGINE tablespace_name "
			+ " FROM information_schema.`TABLES` "
			+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'"
			+ " AND TABLE_NAME like ?";
	public final String SQL_TABLE_PAGE = "SELECT TABLE_NAME tname, TABLE_ROWS trows, TABLE_COMMENT summary, "
			+ "AVG_ROW_LENGTH avg_row_len, DATA_LENGTH blocks, UPDATE_TIME last_analyzed, ENGINE tablespace_name "
			+ " FROM information_schema.`TABLES` "
			+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";

	private final TableInfoMapper tableInfoMapper = new TableInfoMapper();
	private final FieldInfoMapper fieldInfoMapper = new FieldInfoMapper();

	@Override
	protected DatabaseConnector createDbConnector(Address address) {
		DatabaseConnector connector = ConnectorUtils.createMySQLConnector(address);
		if(connector == null){
			throw new IllegalArgumentException("connector create failed!");
		}
		return ConnectorUtils.acquireTransactionProxyConnector(connector);
	}

	@SuppressWarnings("unchecked")
	protected List<Map<String, Object>> loadDatas(DatabaseConnector connector
			, String tableName, String sql) throws Exception{
		List<Map<String, Object>> list = (List<Map<String, Object>>)connector.invoke(sql, (Object[])null);
		if(list == null || list.size() == 0){
			return null;
		}
		return list;
	}

	@Override
	protected List<String> loadFields(DatabaseConnector connector, String tableName) {
		List<Map<String, Object>> list = connector.queryForList(QUERY_TABLE_FIELDS, new Object[]{StringSqlUtils.getMySQLSchemaName(connector.getServiceName()), tableName});
		if(!StringUtils.isEmptyList(list)){
			List<String> result = new ArrayList<String>(list.size());
			for(Map<String, Object> map : list){
				result.add(map.get("COLUMN_NAME").toString().toLowerCase());
			}
			return result;
		}
		return null;
	}

	@Override
	protected int loadSchemaTableSize(DatabaseConnector connector) {
		return connector.queryForInt(SQL_GET_TBL_COUNT, new Object[]{connector.getServiceName()});
	}

	@Override
	protected Map<String, TableInfo> loadTablesRow(Address address, DatabaseConnector connector, List<String> tableNameList) {
		try {
//			StringBuilder tnames = new StringBuilder();
//			if(tableNameList != null){
//				int i = 0;
//				for(String s : tableNameList){
//					if(i > 0){
//						tnames.append(",");
//					}
//					tnames.append("'");
//					tnames.append(s).append("'");
//				}
//			}
			MapSqlParameterSource nameSet = new MapSqlParameterSource();
			nameSet.addValue("schema", DatabaseUtils.getMySQLSchemaName(connector.getServiceName()));
			nameSet.addValue("ids", tableNameList);

			List<TableInfo> list = (List<TableInfo>)connector.queryForRowMapper(SQL_TABLES_ROW
					, tableInfoMapper, nameSet);
			if(!StringUtils.isEmptyList(list)){
				Map<String, TableInfo> result = new HashMap<String, TableInfo>();
				for(TableInfo ti : list){
					result.put(ti.getName(), ti);
				}
				return result;
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	protected long loadTableRow(DatabaseConnector connector, String tableName) {
//		MapSqlParameterSource nameSet = new MapSqlParameterSource();
//		nameSet.addValue("schema", DatabaseUtils.getMySQLSchemaName(connector.getServiceName()));
//		nameSet.addValue("tableName", tableName);
		return connector.queryForLong(SQL_ONE_TABLE_ROW, new Object[]{DatabaseUtils.getMySQLSchemaName(connector.getServiceName()), tableName});
	}

	/**
	 * 查找给定库中的表名集合
	 * @param address
	 * @param tableNameLike 要模糊查找的表名，如: base_custdept_
	 * @return
	 */
	@Override
	public List<String> getTableNamesByLike(Address address, String tableNameLike){
		DatabaseConnector conn = getConnector(address);
		List<Map<String, Object>> list = conn.queryForList(SQL_TABLE_LIKE
				, new Object[]{DatabaseUtils.getMySQLSchemaName(conn.getServiceName()), DatabaseMetaEngineUtils.getLikeConditionArg(tableNameLike)});
		List<String> result = new ArrayList<>();
		if(list != null){
			for(Map<String, Object> m : list){
				result.add(m.get("table_name").toString());
			}
		}
		return result;
	}

	private class TableInfoMapper implements RowMapper<TableInfo>{
		@Override
		public TableInfo mapRow(ResultSet rs, int arg1) throws SQLException {
			ResultSetMetaData rsMetaData = rs.getMetaData();
			TableInfo ti = new TableInfo();
			ti.setName(rs.getString("tname"));
			ti.setSummary(rs.getString("summary"));
			ti.setRows(rs.getInt("trows"));
			if(this.containColumn("avg_row_len", rsMetaData)){
				ti.setAvgRowLen(rs.getLong("avg_row_len"));
			}
			if(this.containColumn("blocks", rsMetaData)){
				ti.setBlocks(rs.getLong("blocks"));
			}
			if(this.containColumn("last_analyzed", rsMetaData)){
				ti.setLastAnalyzed(rs.getString("last_analyzed"));
			}
			if(this.containColumn("tablespace_name", rsMetaData)){
				ti.setTableSpace(rs.getString("tablespace_name"));
			}
			ti.setStatus(StringUtils.EMPTY_STRING);
			return ti;
		}

		private boolean containColumn(String columnName, ResultSetMetaData rsMetaData) throws SQLException{
//			int numberOfColumns = rsMetaData.getColumnCount();
//			String column = null;
//			for (int i = 1; i < numberOfColumns + 1; i++) {
//				column = rsMetaData.getColumnName(i);
//				if (column.equals(columnName)) {
//					return true;
//				}
//			}
//			return false;
			return DatabaseMetaEngineUtils.isContainColumnName(columnName, rsMetaData);
		}
	}

	private class FieldInfoMapper implements RowMapper<FieldInfo>{
		@Override
		public FieldInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ResultSetMetaData rsMetaData = rs.getMetaData();
			FieldInfo fieldInfo = new FieldInfo();
			fieldInfo.setFieldName(rs.getString("column_name"));
			fieldInfo.setDataType(rs.getString("data_type"));
//			fieldInfo.setLength();
			if(DatabaseMetaEngineUtils.isContainColumnName("column_comment", rsMetaData)){
				// 字段备注
//				logger.debug("+++++++ " + rs.getString("column_comment"));
				fieldInfo.setComments(rs.getString("column_comment"));
			}
			if(DatabaseMetaEngineUtils.isContainColumnName("data_precision", rsMetaData)){
				fieldInfo.setPrecision(rs.getInt("data_precision"));
			}
			if(DatabaseMetaEngineUtils.isContainColumnName("data_scale", rsMetaData)){
				fieldInfo.setScale(rs.getInt("data_scale"));
			}
			if(DatabaseMetaEngineUtils.isContainColumnName("column_key", rsMetaData)){
				fieldInfo.setColumnKey(rs.getString("column_key"));
			}
			fieldInfo.setTableName(rs.getString("table_name"));
			return fieldInfo;
		}
	}

	@Override
	public GenericPager<TableInfo> queryPageTableNamesByLike(Address address, String tableNameLike
//			, int pageIndex, int pageSize
			) {
		DatabaseConnector conn = getConnector(address);
		// 查询总记录
		// 查询数据集合
		String query = null;
		String pageSql = null;
		Object[] args = null;
//		args[0] = address.getServiceName();
//		args[1] = tableNameLike;

		if(StringUtils.isNotEmpty(tableNameLike)){
			query = SQL_TABLE_LIKE_PAGE;
			pageSql = SQL_TABLE_LIKE_PAGE + " limit ? offset ?";
			args = new Object[]{address.getServiceName(), DatabaseMetaEngineUtils.getLikeConditionArg(tableNameLike)};
		} else {
			query = SQL_TABLE_PAGE;
			pageSql = SQL_TABLE_PAGE + " limit ? offset ?";
			args = new Object[]{address.getServiceName()};
		}
//		Object[] pageArgs = new Object[4];
//		pageArgs[0] = address.getServiceName();
//		pageArgs[1] = tableNameLike;
//		pageArgs[2] = ListPageContext.getCurrentPageSize();
//		pageArgs[3] = ListPageContext.getCurrentPageIndex();
		return conn.sqlSimpleQueryPager(query, args, tableInfoMapper
//				, pageIndex, pageSize
				, pageSql);
	}

	@Override
	protected List<FieldInfo> loadFieldsObject(DatabaseConnector connector, String tableName) {
		return connector.queryForRowMapper(QUERY_COLUMNS_LIST, new Object[]{"%" + tableName}, this.fieldInfoMapper);
	}

	@Override
	protected void doCreateTableAction(Address address
			, List<FieldInfo> fieldList, String dataVersionField, String tableName) throws DatabaseException {
		DatabaseConnector conn = getConnector(address);
		if(conn == null){
			throw new DatabaseException("数据库连接器不存在，无法执行ddl。table = " + tableName);
		}

		StringBuilder tableSql = new StringBuilder();
		tableSql.append("CREATE TABLE ").append(tableName).append("(");

		int i = 0;
		for(FieldInfo fi : fieldList){
			tableSql.append(fi.getFieldName()).append(" ");
			if(fi.getDataType().equals(FieldInfo.TYPE_DOUBLE)){
				tableSql.append("double(10,2) not null default 0");
			} else if(fi.getDataType().equals(FieldInfo.TYPE_LONG)){
				tableSql.append("bigint not null default 0");
			} else if(fi.getDataType().equals(FieldInfo.TYPE_STRING)){
				tableSql.append("varchar(255)");
			} else {
				throw new UnsupportedOperationException("未实现字段类型：" + fi.getDataType());
			}
			i++;
			if(i == fieldList.size()){
				// 最后一个字段
				tableSql.append(");");
			} else {
				tableSql.append(StringUtils.DEFAULT_SPLIT_SEPARATOR);
			}
		}
		// 1-创建表
		conn.exeCreateTable(tableSql.toString());

		// 2-创建数据版本索引（如果存在）
		if(StringUtils.isNotEmpty(dataVersionField)){
			StringBuilder dataVersionIndexSql = new StringBuilder("create index inx_");
			dataVersionIndexSql.append(tableName).append(StringUtils.UNDERLINE).append(StringUtils.generateRandomNumber(3));
			dataVersionIndexSql.append(" on ").append(tableName);
			dataVersionIndexSql.append("(").append(dataVersionField).append(")");
			logger.debug("添加索引：" + dataVersionIndexSql);
			conn.execute(dataVersionIndexSql.toString());
		}
	}

	public static void main(String[] args){
		Address address = new Address();
//		address.setUrl("127.0.0.1");
		address.setUrl("116.198.40.76");
		address.setPort(3306);
//		address.setService("enterprise-platform");
		address.setService("iplatform");
		address.setAuthentication("root");
//		address.setCertification("root");
		address.setCertification("Bjjmy_2020");

		MySQLMetaEngine metaEngine = new MySQLMetaEngine();

		// 测试查询
//		ListPageContext.setCurrentPageIndex(1);
//		ListPageContext.setCurrentPageSize(10);
//
//		GenericPager<TableInfo> pager = metaEngine.queryPageTableNamesByLike(address, "b2");
//		System.out.println(pager.getDatas());

		// 测试创建表
//		List<Map<String, Object>> datas = metaEngine.loadTableDatas(address
//				, "s_user_core", "select id, create_time, name, type, status, org_name, mobile, find_pass_time from s_user_core limit 20");
//		List<Map<String, Object>> datas = metaEngine.loadTableDatas(address
//				, "", "select u.*, d.name dept from s_user_core u, s_department d where u.DEPARTMENT_ID = d.ID limit 30");
		try {
//			metaEngine.createTableDynamic(address, datas, "id", "test_user_dept");
			List<FieldInfo> list = metaEngine.getFieldsObject(address, "s_user_core");
			if(list != null){
				for(FieldInfo fieldInfo : list){
					System.out.println(fieldInfo);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}