package com.ozs.mdm.service.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import com.ozs.mdm.mapper.DatabaseManageMapper;
import com.ozs.mdm.mapper.StorageManageMapper;
import com.ozs.mdm.service.DatabaseManageService;
import com.ozs.mdm.service.KpiConfigApi;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.ozs.mdm.dto.MdmCreateTableDTO;
import com.ozs.mdm.dto.MdmDimColumnType;
import com.ozs.mdm.dto.MdmDimDatabaseType;
import com.ozs.mdm.dto.MdmDimResourceCharset;
import com.ozs.mdm.dto.MdmDimResourceType;
import com.ozs.mdm.dto.MdmInfoColumn;
import com.ozs.mdm.dto.MdmInfoDatabase;
import com.ozs.mdm.util.JDBCUtil;
import com.ozs.mdm.util.ParamUtil;
import com.ozs.mdm.util.R;

@Service
public class DatabaseManageServiceImpl implements DatabaseManageService {
	private final Logger log = LoggerFactory.getLogger(this.getClass());
	// KPI配置服务的接口调用
	@Autowired
	private KpiConfigApi kpiConfigApi;

	@Autowired
	private DatabaseManageMapper databaseManageMapper;

	@Autowired
	private StorageManageMapper storageManageMapper;

	/**
	 * 新建数据库资源
	 */
	@Transactional
	@Override
	public R<String> saveDatabase(MdmInfoDatabase database) throws Exception {

		R<String> r = new R<>();
		database.setId(UUID.randomUUID().toString());
		// 判断资源名称是否重复
		if (checkResourceName(database.getResourceName(), null, databaseManageMapper)) {
			r.setCode("-1");
			r.setMsg("资源名称已存在！");
			return r;
		}
		// 判断资源是否已经存在，ip port和databaseName
		if (databaseManageMapper.countDatabase(database.getIp(), database.getResourcePort(),
				database.getDatabaseName()) != 0) {
			r.setCode("-1");
			r.setMsg("数据资源已存在！" + database.getIp() + ":" + database.getResourcePort() + "/"
					+ database.getDatabaseName());
			return r;
		}
		// 如果是原始库，进行原始库表信息字段信息拉取
		// if (database.getDatabaseType() == 1) {
		MdmDimResourceType resorceType = databaseManageMapper.getResorceTypeById(database.getResourceType());
		database.setUrl(resorceType.getUrl());
		database.setDriverName(resorceType.getDriverName());
		database.setResourceTypeName(resorceType.getTypeName());
		saveTableAndColumn(database, false);

		// }
		databaseManageMapper.saveDatabase(database);
		r.setMsg("数据库新建成功");
		return r;
	}

	/**
	 * 原始库类型表和字段信息需要建表时提取保存
	 * 
	 * @param database
	 * @throws Exception
	 */
	private void saveTableAndColumn(MdmInfoDatabase database, boolean refresh) throws Exception {
		List<MdmInfoColumn> columns = new ArrayList<>();
		List<MdmCreateTableDTO> tables = new ArrayList<>();
		switch (database.getResourceTypeName()) {
		case "mysql":
			columns = getMysqlColumns(database);
			tables = getMysqlTables(database);
			break;
		case "hive":
			columns = getMysqlColumns(database);
			tables = getMysqlTables(database);
			break;
		default:
			log.info("未知的数据资源类型");
			throw new Exception("未知的数据资源类型");
		}

		// 判断数据库是否返回值
		if (tables != null && tables.size() != 0 && columns != null && columns.size() != 0) {
			// 是否是刷新操作，刷新操作对已存在表不错新建操作，并删除没有查到的表
			if (refresh) {
				log.info("刷新库信息操作...");
				List<MdmCreateTableDTO> ts = storageManageMapper.listTableByDatabaseId(database.getId());
				List<MdmInfoColumn> cs = storageManageMapper.listColumnByDatabaseId(database.getId());
				List<MdmCreateTableDTO> removeT = new ArrayList<>();
				List<MdmInfoColumn> removeC = new ArrayList<>();
				log.info("检验库中表和字段信息是否于系统中存在...");

				// 1.库中表和字段信息不存在与系统中时操作
				for (MdmCreateTableDTO table : tables) {
					for (MdmCreateTableDTO t : ts) {
						// 1.1已存在表不进行新建操作
						if (t.getTableName().equals(table.getTableName())) {
							// 判断表中字段是否修改
							for (MdmInfoColumn column : columns) {
								if (column.getTableName().equals(table.getTableName())) {
									// 表存在，而字段不存在需要新增字段
									column.setTableId(t.getTableId());
									column.setUpdateUserId(database.getUpdateUserId());
									for (MdmInfoColumn c : cs) {
										// 1.2表中已存在字段不进行新建操作
										if (column.getColumnName().equals(c.getColumnName())) {
											removeC.add(column);
										}
									}
								}
							}
							removeT.add(table);
						}
					}
				}
				log.info("检验系统中表和字段信息是否于数据库中存在...");
				// 2.系统中表和字段信息不存在于库中时操作
				for (MdmCreateTableDTO t : ts) {
					// 表存在状态
					boolean tContain = false;
					for (MdmCreateTableDTO table : tables) {
						if (t.getTableName().equals(table.getTableName())) {
							for (MdmInfoColumn c : cs) {
								// 字段存在状态
								boolean cContain = false;
								if (table.getTableName().equals(c.getTableName())) {
									for (MdmInfoColumn column : columns) {
										if (column.getColumnName().equals(c.getColumnName())) {
											cContain = true;
										}
									}
								} else {
									cContain = true;
								}
								// 2.1 不存在，删除系统中表中字段信息
								if (!cContain) {
									log.info("已不存在表中字段：" + t.getTableName() + "." + c.getColumnName());
									storageManageMapper.removeColumnById(c.getId());
								}
							}
							tContain = true;
						}

					}
					// 2.2不存在，删除系统中表信息和表字段信息
					if (!tContain) {
						log.info("已不存在表：" + t.getTableName());
						storageManageMapper.removeTableById(t.getTableId());
						storageManageMapper.removeColumnByTableId(t.getTableId());
					}
				}
				// 已存在表和字段不参与新建操作
				tables.removeAll(removeT);
				columns.removeAll(removeC);
			}
			if (columns != null && columns.size() != 0) {
				List<MdmDimColumnType> types = storageManageMapper.listColumnTypeByNames(columns);
				int maxCode = storageManageMapper.getMaxColumnCode();
				for (MdmInfoColumn c : columns) {
					maxCode++;
					c.setColumnCode(maxCode);
					for (MdmCreateTableDTO t : tables) {
						if (c.getTableName().equals(t.getTableName())) {
							c.setTableId(t.getTableId());
							c.setUpdateUserId(t.getUpdateUserId());
						}
					}
					for (MdmDimColumnType type : types) {
						if (c.getColumnTypeName().equals(type.getTypeName())) {
							c.setColumnType(type.getId());
						}
					}
				}
			}

			if (tables != null && tables.size() != 0) {
				storageManageMapper.saveCopyTables(tables);
			}
			if (columns != null && columns.size() != 0) {
				storageManageMapper.saveColumns(columns);
			}
		}

	}

	/**
	 * mysql 原始库获取表字段信息
	 * 
	 * @param database
	 * @return
	 */
	private List<MdmInfoColumn> getMysqlColumns(MdmInfoDatabase database) throws SQLException {
		List<MdmInfoColumn> columns = new ArrayList<>();
		String sql = "SELECT 	table_name,column_name column_name,data_type as column_type,column_comment column_comment,NUMERIC_SCALE numeric_scale,if( NUMERIC_SCALE = 0 or NUMERIC_SCALE is null, substring_index(substring_index(COLUMN_TYPE, '(', -1), ')', 1), substring_index(substring_index(COLUMN_TYPE, '(', -1), ',', 1) ) length, if(IS_NULLABLE = 'NO','0','1')	if_null, if(COLUMN_KEY = 'PRI','1','0') if_primary FROM information_schema.COLUMNS "
				+ "WHERE TABLE_SCHEMA = '" + database.getDatabaseName() + "'";
		JDBCUtil jdbcUtil = new JDBCUtil();
		jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
				database.getResourcePort(), database.getUsername(), database.getPassword());
		List<Map<String, Object>> result = jdbcUtil.getData(sql);
		jdbcUtil.closeJDBC();
		for (Map<String, Object> m : result) {
			MdmInfoColumn c = new MdmInfoColumn();
			if (m.get("column_comment") != null) {
				c.setColumnComment(m.get("column_comment").toString());
			}
			if (m.get("tableName") != null) {
				c.setTableName(m.get("tableName").toString());
			}
			if (m.get("column_name") != null) {
				c.setColumnName(m.get("column_name").toString());
			}
			if (m.get("column_type") != null) {
				c.setColumnTypeName(m.get("column_type").toString().toUpperCase());
			}
			if (m.get("numeric_scale") != null) {
				c.setNumericScale(Integer.parseInt((m.get("numeric_scale").toString())));
			}
			if (m.get("length") != null && ParamUtil.isNumeric(m.get("length").toString())) {
				c.setLength(Integer.parseInt((m.get("length").toString())));
			} else {
				c.setLength(0);
			}
			if (m.get("if_null") != null) {
				c.setIfNull(Integer.parseInt((m.get("if_null").toString())));
			}
			if (m.get("if_primary") != null) {
				c.setIfPrimary(Integer.parseInt((m.get("if_primary").toString())));
			}
			if (m.get("table_name") != null) {
				c.setTableName(m.get("table_name").toString());
			}
			columns.add(c);
		}
		return columns;
	}

	/**
	 * mysql 原始库获取表信息
	 * 
	 * @param database
	 * @return
	 * @throws Exception
	 */
	private List<MdmCreateTableDTO> getMysqlTables(MdmInfoDatabase database) throws SQLException {
		List<MdmCreateTableDTO> tables = new ArrayList<>();
		String sql = "SELECT table_name,	table_comment FROM	information_schema. TABLES " + "WHERE TABLE_SCHEMA = '"
				+ database.getDatabaseName() + "'";
		JDBCUtil jdbcUtil = new JDBCUtil();
		jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
				database.getResourcePort(), database.getUsername(), database.getPassword());
		List<Map<String, Object>> result = jdbcUtil.getData(sql);
		jdbcUtil.closeJDBC();
		for (Map<String, Object> m : result) {
			MdmCreateTableDTO t = new MdmCreateTableDTO();
			// 赋值属性
			t.setTableId(UUID.randomUUID().toString());
			t.setDatabaseId(database.getId());
			t.setUpdateUserId(database.getUpdateUserId());
			t.setCharsetId(database.getCharset());
			t.setTableDesc("原始库表默认描述");
			if (m.get("table_name") != null) {
				t.setTableName(m.get("table_name").toString());
			}
			if (m.get("table_comment") != null) {
				t.setTableComment(m.get("table_comment").toString());
			}
			tables.add(t);
		}
		return tables;
	}

	/**
	 * 获取数据库类型列表
	 */
	@Override
	public R<List<MdmDimDatabaseType>> listDatabaseType() {
		return new R<List<MdmDimDatabaseType>>(databaseManageMapper.listDatabaseType());
	}

	/**
	 * 获取数据资源类型列表
	 */
	@Override
	public R<List<MdmDimResourceType>> listResourceType() {
		return new R<List<MdmDimResourceType>>(databaseManageMapper.listResourceType());
	}

	/**
	 * 获取编码集列表
	 */
	@Override
	public R<List<MdmDimResourceCharset>> listCharsetType() {
		return new R<List<MdmDimResourceCharset>>(databaseManageMapper.listCharsetType());
	}

	/**
	 * 获取数据库资源列表
	 */
	@Override
	public R<List<MdmInfoDatabase>> listDatabase(String resourceName, Integer resourceType, String sort, String order,
			Integer page, Integer pageSize) {
		R<MdmInfoDatabase> r = new R<>();
		r.pageStart(sort, order, page, pageSize);
		List<MdmInfoDatabase> resultList = databaseManageMapper.listDatabase(resourceName, resourceType);
		return r.page(resultList);
	}

	/**
	 * 测试数据库资源连接
	 */
	@Override
	public R<String> getTestConnection(String ip, String resourcePort, String username, String password,
			String databaseName, Integer resourceType) {
		R<String> r = new R<>();
		// 判断不同数据库资源连接
		MdmDimResourceType resorceType = databaseManageMapper.getResorceTypeById(resourceType);
		JDBCUtil jdbcUtil = new JDBCUtil();
		if (jdbcUtil.init(resorceType.getDriverName(), resorceType.getUrl(), ip, databaseName, resourcePort, username,
				password)) {
			log.info("连接成功");
			r.setData("连接成功");
			r.setMsg("连接成功");

		} else {
			log.info("连接失败");
			r.setMsg("连接失败!");
			r.setData("连接失败!");
			r.setCode("-1");
		}
		try {
			jdbcUtil.closeJDBC();
		} catch (SQLException e) {
			r.setData("关闭连接出现异常");
			e.printStackTrace();
		}
		return r;
	}

	/**
	 * 通过ID获取数据库信息
	 */
	@Override
	public R<MdmInfoDatabase> getDatabaseById(String id) {
		return new R<MdmInfoDatabase>(databaseManageMapper.getDatabaseById(id));
	}

	@Override
	public R<String> updateDatabase(MdmInfoDatabase database) {
		R<String> r = new R<>();
		// 判断资源名称是否重复
		if (checkResourceName(database.getResourceName(), database.getId(), databaseManageMapper)) {
			r.setCode("-1");
			r.setData("资源名称已存在！");
			return r;
		}
		List<String> columnIds = databaseManageMapper.listColumnIdByDatabaseId(database.getId());
		// TODO 判断数据库汇总是否存在在api中使用的字段
		if (ifColumnsInUse(columnIds, kpiConfigApi)) {
			databaseManageMapper.updateDatabase(database);
			r.setData("更新成功");
		} else {
			r.setCode("-1");
			r.setMsg("该资源正在使用，不可修改");
		}
		return r;
	}

	@Transactional
	@Override
	public R<String> removeDatabase(String id) {
		R<String> r = new R<>();
		List<String> columnIds = databaseManageMapper.listColumnIdByDatabaseId(id);
		// 判断数据库汇总是否存在在api中使用的字段
		if (ifColumnsInUse(columnIds, kpiConfigApi)) {
			// 删除字段信息
			databaseManageMapper.removeColumnsByDatabaseId(id);
			// 删除表信息
			databaseManageMapper.removeTablesByDatabaseId(id);
			// 删除数据库信息
			databaseManageMapper.removeDatabase(id);
			r.setData("删除成功");
		} else {
			r.setCode("-1");
			r.setMsg("该资源正在使用，不可删除");
		}
		return r;
	}

	/**
	 * 检查数据库资源名称是否存在,true 存在 false 不存在
	 * 
	 * @param resourceName
	 * @param databaseId
	 *            原数据库ID（用于修改操作，判断名称是否非原名称下重复）
	 * @param storageManageMapper
	 * 
	 * @return
	 */
	protected boolean checkResourceName(String resourceName, String databaseId,
			DatabaseManageMapper databaseManageMapper) {
		// 返回值 1 存在， 0 不存在
		if (databaseManageMapper.checkDatabaseName(resourceName, databaseId) == 1) {
			return true;
		}
		return false;

	}

	/**
	 * 调用kpi配置服务接口，批量判断字段是否在使用
	 * 
	 * @param storageManageMapper
	 * @param tableId
	 * @return
	 */
	protected boolean ifColumnsInUse(List<String> columnIds, KpiConfigApi kpiConfigApi) {
		if (columnIds == null || columnIds.size() == 0) {
			return true;
		}
		R<Map<String, Object>> result = kpiConfigApi.ifColumnsInUse(columnIds);
		Map<String, Object> m = result.getData();
		return !m.containsValue("true");
	}

	@Transactional
	@Override
	public R<String> updateOriginalDatabase(String databaseId) throws Exception {
		R<String> r = new R<>();
		MdmInfoDatabase database = databaseManageMapper.getDatabaseById(databaseId);
		// 如果不是原始库
//		if (database.getDatabaseType() != 1) {
//			r.setCode("-1");
//			r.setMsg("更新失败，非原始类型库！");
//			return r;
//		}
		MdmDimResourceType resorceType = databaseManageMapper.getResorceTypeById(database.getResourceType());
		database.setUrl(resorceType.getUrl());
		database.setDriverName(resorceType.getDriverName());
		database.setResourceTypeName(resorceType.getTypeName());
		saveTableAndColumn(database, true);
		r.setData("更新成功");
		return r;
	}
}
