package com.ozs.mdm.service.impl;

import java.io.File;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import com.ozs.mdm.mapper.StorageManageMapper;
import com.ozs.mdm.service.KpiConfigApi;
import com.ozs.mdm.service.StorageManageService;
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 org.springframework.web.multipart.MultipartFile;

import com.ozs.mdm.dto.CreateTableCopyDTO;
import com.ozs.mdm.dto.MdmCreateTableDTO;
import com.ozs.mdm.dto.MdmDimColumnType;
import com.ozs.mdm.dto.MdmDimCreateOption;
import com.ozs.mdm.dto.MdmInfoColumn;
import com.ozs.mdm.dto.MdmInfoDatabase;
import com.ozs.mdm.dto.MdmInfoTable;
import com.ozs.mdm.dto.UpdateTableDTO;
import com.ozs.mdm.service.util.StorageManageServiceUtil;
import com.ozs.mdm.util.FileTool;
import com.ozs.mdm.util.JDBCUtil;
import com.ozs.mdm.util.R;
import com.ozs.mdm.util.S;

@Service
public class StorageManageServiceImpl extends StorageManageServiceUtil implements StorageManageService {
	private final Logger log = LoggerFactory.getLogger(this.getClass());

	@Autowired
	private StorageManageMapper storageManageMapper;

	// KPI配置服务的接口调用
	@Autowired
	private KpiConfigApi kpiConfigApi;

	@Override
	public R<List<MdmInfoDatabase>> listDatabaseOption(String resourceName) {
		return new R<List<MdmInfoDatabase>>(storageManageMapper.listDatabaseOption(resourceName));
	}

	@Override
	public R<List<MdmInfoDatabase>> listDatabaseOptionAll(String resourceName) {
		return new R<List<MdmInfoDatabase>>(storageManageMapper.listDatabaseOptionAll(resourceName));
	}

	@Override
	public R<List<MdmDimCreateOption>> listCreateTableOption() {
		return new R<List<MdmDimCreateOption>>(storageManageMapper.listCreateTableOption());
	}

	@Override
	@Transactional
	public R<String> createTable(MdmCreateTableDTO tableDto) throws Exception {
		R<String> r = new R<>();
		// 判断 名称是否重复
		List<String> tableNames = new ArrayList<>();
		tableNames.add(tableDto.getTableName());
		String tableName = checkTableNameByDatabase(tableNames, tableDto.getDatabaseId(), storageManageMapper);
		if (tableName != null) {
			r.setCode("-1");
			r.setMsg("数据表【" + tableName + "】已经存在！");
			return r;
		}
		JDBCUtil jdbcUtil = new JDBCUtil();
		tableDto.setTableId(UUID.randomUUID().toString());
		MdmInfoDatabase database = null;
		database = createTableForDatabase(tableDto, jdbcUtil, storageManageMapper);
		jdbcUtil.closeJDBC();
		r.setData("建表完成");
		try {
			// 数据库建表
			saveColums(tableDto, storageManageMapper);
			storageManageMapper.saveTable(tableDto);
		} catch (Exception e) {
			// 手动删表，实现回滚
			jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
					database.getResourcePort(), database.getUsername(), database.getPassword());
			jdbcUtil.update("DROP TABLE  " + tableDto.getTableName());
			log.error("数据库建表信息保存异常");
			jdbcUtil.closeJDBC();
			throw new RuntimeException("数据库建表信息保存异常");
		}
		r.setData("建表完成");
		return r;
	}

	@Override
	public String getTemplateColums(String substring) throws Exception {
		return super.getTemplateColums(substring, storageManageMapper);
	}

	@Override
	public R<List<Map<String, Object>>> loadTemplateColums(MultipartFile multipartFile) throws Exception {
		List<Map<String, Object>> resultList = new ArrayList<>();
		// 载入报表展示字段
		List<String> paramList = REPORT_COLUMNS;
		StringBuffer fileSb = new StringBuffer(multipartFile.getOriginalFilename());
		fileSb.insert(fileSb.indexOf("."), "_" + System.currentTimeMillis());
		FileTool.uploadFile(multipartFile.getBytes(), "", fileSb.toString());
		File file = new File(fileSb.toString());
		List<List<String>> columnList = null;
		// 进行后缀为xlsx的Excel操作
		columnList = xlsx(file, paramList);
		if (columnList == null) {
			return new R<>(S.FILE_DATA_ERROR);
		}
		Map<String, Object> dataMap = null;
		for (int i = 0; i < paramList.size(); i++) {
			dataMap = new HashMap<>();
			dataMap.put("header", REPORT_COLUMNS_MAP.get(paramList.get(i)));
			dataMap.put("body", columnList.get(i));
			resultList.add(dataMap);
		}
		List<MdmDimColumnType> types = storageManageMapper.listColumnTypeAll();

		// 以横向为单位输出数组数据
		resultList = xlsx2(file, paramList);
		for (Map<String, Object> map : resultList) {
			for (MdmDimColumnType type : types) {
				if (map.get("columnType") != null && map.get("columnType").equals(type.getTypeName())) {
					map.put("columnType", type.getId());
					break;
				}
			}
			if (map.get("ifPrimary") != null && map.get("ifPrimary").equals("是")) {
				map.put("ifPrimary", 1);
			}
			if (map.get("ifPrimary") != null && map.get("ifPrimary").equals("否")) {
				map.put("ifPrimary", 0);
			}
			if (map.get("ifNull") != null && map.get("ifNull").equals("可空")) {
				map.put("ifNull", 1);
			}
			if (map.get("ifNull") != null && map.get("ifNull").equals("不可空")) {
				map.put("ifNull", 0);
			}
		}
		file.delete();
		return new R<>(resultList);
	}

	@Transactional
	@Override
	public R<String> createTableByDDL(MdmCreateTableDTO tableDto) throws Exception {
		R<String> r = new R<>();
		// 解析ddl,获取表名
		tableDto.setTableName(getTabelNameByDDL(tableDto.getDdl()));
		// 判断 名称是否重复
		List<String> tableNames = new ArrayList<>();
		tableNames.add(tableDto.getTableName());
		String tableName = checkTableNameByDatabase(tableNames, tableDto.getDatabaseId(), storageManageMapper);
		if (tableName != null) {
			r.setCode("-1");
			r.setMsg("数据表【" + tableName + "】已经存在！");
			return r;
		}
		JDBCUtil jdbcUtil = new JDBCUtil();
		tableDto.setTableId(UUID.randomUUID().toString());
		MdmInfoDatabase database = null;
		database = createTableForDatabaseByDDL(tableDto, jdbcUtil, storageManageMapper);
		tableDto.setCharsetId(database.getCharset());
		try {
			// 数据库建表
			storageManageMapper.saveTable(tableDto);
			saveColumsByDDL(tableDto, database, storageManageMapper);
		} catch (Exception e) {
			e.printStackTrace();
			// 手动删表，实现回滚
			jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
					database.getResourcePort(), database.getUsername(), database.getPassword());
			jdbcUtil.update("DROP TABLE " + tableDto.getTableName());
			jdbcUtil.commit();
			jdbcUtil.closeJDBC();
			log.error("数据库建表信息保存异常");
			throw new RuntimeException("数据库建表信息保存异常");
		}
		r.setData("建表完成");
		return r;
	}

	/**
	 * 建表语句DDL中获取表名
	 * 
	 * @param ddl
	 * @return
	 */
	private String getTabelNameByDDL(String ddl) {
		// 去除所有空格和`符号
		ddl = ddl.replace(" ", "");
		ddl = ddl.replace("`", "");
		// 转小写
		ddl = ddl.toLowerCase();
		String tableName = ddl.substring(ddl.indexOf("createtable"), ddl.indexOf("("));
		tableName = tableName.replace("createtable", "");
		return tableName;
	}

	@Override
	public R<List<MdmInfoTable>> listTable(String tableName, String tableComment, String databaseId, String sort,
			String order, Integer page, Integer pageSize) {
		R<MdmInfoTable> r = new R<>();
		sort = sort + " "+order+",id ";
		r.pageStart(sort, order, page, pageSize);
		List<MdmInfoTable> resultList = storageManageMapper.listTable(tableName, tableComment, databaseId);
		return r.page(resultList);
	}

	@Override
	public R<List<MdmInfoTable>> listTableScan(String tableName, String tableComment, String databaseId, String sort,
			String order, Integer page, Integer pageSize) {
		R<MdmInfoTable> r = new R<>();
		r.pageStart(sort, order, page, pageSize);
		List<MdmInfoTable> resultList = storageManageMapper.listTableScan(tableName, tableComment, databaseId);
		return r.page(resultList);
	}

	/**
	 * 删信息表操作
	 * 
	 * @throws SQLException
	 */
	@Transactional
	@Override
	public R<String> removeTableById(String tableId) throws Exception {
		R<String> r = new R<>();
		List<String> columnIds = storageManageMapper.listColumnIdByTableId(tableId);
		if (ifColumnsInUse(columnIds, kpiConfigApi)) {
			// 获取数据库信息
			MdmCreateTableDTO tableDto = storageManageMapper.getTableInfoById(tableId);
			MdmInfoDatabase database = storageManageMapper.getDatabaseById(tableDto.getDatabaseId());
			// 删除表信息和字段信息
			storageManageMapper.removeTableById(tableId);
			storageManageMapper.removeColumnByTableId(tableId);
			JDBCUtil jdbcUtil = new JDBCUtil();
			try {
				// 数据库中删表，无法回滚
				jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
						database.getResourcePort(), database.getUsername(), database.getPassword());
				jdbcUtil.update("DROP TABLE " + tableDto.getTableName());
			} catch (RuntimeException e) {
				// 找不到删除表的情况下，保证删除接口执行成功
				log.error("删表操作异常" + e.getMessage());
				// 如果不是找不到表的异常，另外抛出异常
				if (!e.getMessage().contains("Unknown table")) {
					e.printStackTrace();
					throw new RuntimeException(e.getMessage());
				}
			} finally {
				jdbcUtil.commit();
				jdbcUtil.closeJDBC();
			}
			r.setData("删除成功");
		} else {
			r.setCode("-1");
			r.setMsg("该表正在使用，不可删除");
		}
		return r;
	}

	/**
	 * 表信息修改
	 * 
	 * @throws Exception
	 */
	@Transactional
	@Override
	public R<String> updateTable(UpdateTableDTO uTableDto) throws Exception {
		R<String> r = new R<>();
		// 判断 名称是否重复
		List<String> tableNames = new ArrayList<>();
		tableNames.add(uTableDto.getTableName());
		MdmCreateTableDTO table = storageManageMapper.getTableInfoById(uTableDto.getId());
		String tableName = checkTableNameByDatabase(tableNames, uTableDto.getDatabaseId(), storageManageMapper);
		if (tableName != null && !table.getTableName().equals(tableName)) {
			r.setCode("-1");
			r.setMsg("数据表【" + tableName + "】已经存在！");
			return r;
		}
		JDBCUtil jdbcUtil = new JDBCUtil();
		List<String> columnIds = storageManageMapper.listColumnIdByTableId(uTableDto.getId());
		if (ifColumnsInUse(columnIds, kpiConfigApi)) {
			try {
				// 获取数据库信息
				MdmCreateTableDTO tableDto = storageManageMapper.getTableInfoById(uTableDto.getId());
				MdmInfoDatabase database = storageManageMapper.getDatabaseById(tableDto.getDatabaseId());
				// 获取表原名，用作表字段修改等操作
				uTableDto.setNewTableName(uTableDto.getTableName());
				uTableDto.setTableName(tableDto.getTableName());
				jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
						database.getResourcePort(), database.getUsername(), database.getPassword());
				switch (database.getResourceTypeName()) {
				case "mysql":
					updateColumnsOfDatabase(uTableDto, database, jdbcUtil, storageManageMapper);
					updateTableOfDatabase(uTableDto, database, jdbcUtil, storageManageMapper);
					break;
				case "hive":
					updateColumnsOfDatabase(uTableDto, database, jdbcUtil, storageManageMapper);
					updateTableOfDatabase(uTableDto, database, jdbcUtil, storageManageMapper);
					break;
				default:
					log.info("未知的数据资源类型");
					throw new RuntimeException("未知的数据资源类型");
				}
			} catch (Exception e) {
				e.printStackTrace();
				jdbcUtil.rollback();
				log.error("数据库表信息修改异常：" + e.getMessage());
				throw new RuntimeException("数据库建表信息保存异常");
			} finally {
				jdbcUtil.commit();
				jdbcUtil.closeJDBC();
			}
		} else {
			r.setCode("-1");
			r.setMsg("该表正在使用，不可修改");
		}
		return r;
	}

	@Override
	public R<MdmCreateTableDTO> getTableInfoById(String tableId) {
		return new R<MdmCreateTableDTO>(storageManageMapper.getTableInfoById(tableId));
	}

	@Override
	public R<List<MdmInfoColumn>> listColumsByTableId(String tableId) {
		return new R<List<MdmInfoColumn>>(storageManageMapper.listColumsByTableId(tableId));

	}

	@Override
	public R<List<MdmDimColumnType>> listColumTypeByDatabaseType(Integer resourceType) {
		return new R<List<MdmDimColumnType>>(storageManageMapper.listColumTypeByDatabaseType(resourceType));

	}

	/**
	 * 检查表名称在数据库是否存在，返回重复名称
	 * 
	 * @param tableNames
	 * @param databaseId
	 * @param storageManageMapper
	 * @return 返回重复名称
	 */
	protected String checkTableNameByDatabase(List<String> tableNames, String databaseId,
			StorageManageMapper storageManageMapper) {
		// 获取数据库所有表名
		List<String> names = storageManageMapper.listTableNameByDatabaseId(databaseId);
		for (String tableName : tableNames) {
			for (String name : names) {
				if (name.equals(tableName)) {
					return tableName;
				}
			}
		}
		return null;
	}

	/**
	 * 检查字段名称在表里是否存在,true 存在 false 不存在
	 * 
	 * @param columnName
	 * @param tableId
	 * @param storageManageMapper
	 * @return
	 */
	protected boolean checkColumnNameByTable(List<String> columnName, String tableId,
			StorageManageMapper storageManageMapper) {
		// TODO 字段重复判断
		return false;

	}

	@Transactional
	@Override
	public R<String> createTableByCopy(CreateTableCopyDTO tableCopyDto) throws Exception {
		R<String> r = new R<>();
		List<MdmCreateTableDTO> tableDtos = storageManageMapper.listCreateTableByIds(tableCopyDto.getTableIds());
		// 判断 名称是否重复
		List<String> tableNames = new ArrayList<>();
		for (MdmCreateTableDTO tableDto : tableDtos) {
			tableNames.add(tableDto.getTableName());
		}
		String tableName = checkTableNameByDatabase(tableNames, tableCopyDto.getTargetDatabaseId(),
				storageManageMapper);
		if (tableName != null) {
			r.setCode("-1");
			r.setMsg("数据表【" + tableName + "】已经存在！");
			return r;
		}
		JDBCUtil jdbcUtil = new JDBCUtil();
		List<MdmInfoColumn> columns = storageManageMapper.listColumnByTableIds(tableCopyDto.getTableIds());
		List<String> sqls = new ArrayList<>();
		// 获取最大code字段编码
		int maxCode = storageManageMapper.getMaxColumnCode();
		for (MdmCreateTableDTO tableDto : tableDtos) {
			String tableId = UUID.randomUUID().toString();
			List<MdmInfoColumn> columnsU = new ArrayList<>();
			tableDto.setDatabaseId(tableCopyDto.getTargetDatabaseId());
			tableDto.setUpdateUserId(tableCopyDto.getUpdateUserId());
			for (MdmInfoColumn column : columns) {
				if (tableDto.getTableId().equals(column.getTableId())) {
					// 修改用户ID
					column.setUpdateUserId(tableCopyDto.getUpdateUserId());
					// 设置字段code 值
					maxCode++;
					column.setColumnCode(maxCode);
					// 修改用户ID
					column.setTableId(tableId);
					columnsU.add(column);
				}
			}
			tableDto.setTableId(tableId);
			tableDto.setColumns(columnsU);
			String sql = getMysqlSql(tableDto);
			sqls.add(sql);
		}
		storageManageMapper.saveCopyTables(tableDtos);
		storageManageMapper.saveColumns(columns);
		try {
			MdmInfoDatabase database = storageManageMapper.getDatabaseById(tableCopyDto.getTargetDatabaseId());
			// 手动删表，实现回滚
			jdbcUtil.init(database.getDriverName(), database.getUrl(), database.getIp(), database.getDatabaseName(),
					database.getResourcePort(), database.getUsername(), database.getPassword());
			for (String s : sqls) {
				jdbcUtil.update(s);
			}
		} catch (Exception e) {
			for (String s : sqls) {
				jdbcUtil.update(s);
			}
			jdbcUtil.closeJDBC();
			log.error("数据库建表信息保存异常");
			throw new RuntimeException("数据库建表信息保存异常");
		} finally {
			jdbcUtil.commit();
			jdbcUtil.closeJDBC();

		}
		r.setData("建表完成");
		r.setMsg("建表完成");
		return r;
	}
}
