package com.artfess.uc.manager.impl;

import com.artfess.base.constants.SQLConst;
import com.artfess.base.datasource.DatabaseContext;
import com.artfess.base.manager.CommonManager;
import com.artfess.base.manager.impl.BaseManagerImpl;
import com.artfess.base.query.PageBean;
import com.artfess.base.query.PageList;
import com.artfess.base.util.Base64;
import com.artfess.base.util.*;
import com.artfess.table.meta.impl.BaseTableMeta;
import com.artfess.table.model.Column;
import com.artfess.table.model.Table;
import com.artfess.table.model.impl.DefaultColumn;
import com.artfess.table.model.impl.DefaultTable;
import com.artfess.table.operator.ITableOperator;
import com.artfess.table.util.MetaDataUtil;
import com.artfess.uc.dao.MatrixDao;
import com.artfess.uc.manager.MatrixColDefManager;
import com.artfess.uc.manager.MatrixManager;
import com.artfess.uc.model.Matrix;
import com.artfess.uc.model.MatrixColDef;
import com.artfess.uc.model.MatrixColField;
import com.artfess.uc.model.User;
import com.artfess.uc.util.ContextUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.IOException;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 
 * <pre> 
 * 描述：矩阵管理 处理实现类
 * 构建组：x7
 * 作者:pangq
 * 邮箱:pangq@jee-soft.cn
 * 日期:2020-06-05 10:24:44
 * 版权：广州宏天软件股份有限公司
 * </pre>
 */
@Service("matrixManager")
public class MatrixManagerImpl extends BaseManagerImpl<MatrixDao, Matrix> implements MatrixManager{
	@Resource
	ITableOperator tableOperator;
	@Resource
	MatrixColDefManager matrixColDefManager;
	@Resource
	CommonManager commonManager;
	@Resource
	DatabaseContext databaseContext;
	@Resource
	JdbcTemplate jdbcTemplate;

	/**
	 * 获取矩阵详情，包含未删除的列数据
	 * @param id
	 * @return
	 */
	@Override
	public Matrix getDetail(String id) {
		Matrix matrix = this.get(id);
		if(BeanUtils.isNotEmpty(matrix)){
			List<MatrixColDef> condList = matrixColDefManager.getCondList(matrix.getId());
			List<MatrixColDef> roleList = matrixColDefManager.getRoleList(matrix.getId());
			matrix.setCondList(condList);
			matrix.setRoleList(roleList);
		}
		return matrix;
	}
	
	@Override
	public Matrix getByCode(String code) {
		return baseMapper.getByCode(code);
	}

	@Override
	public Boolean isCodeExist(String code, String id) {
		return baseMapper.isCodeExist(code,id);
	}

	@Override
	@Transactional
	public void saveMatrix(Matrix matrix) {
		User user = ContextUtil.getCurrentUser();
		String matrixId = BeanUtils.isNotEmpty(matrix.getId()) ? matrix.getId() : UniqueIdUtil.getSuid();
		
		//是否将矩阵设置为待发布
		boolean setMatrixUnpublish = false;
		
		/** 以下是列定义的保存 
		 * 	列的保存主要注意3个问题：
		 * 1、删除列，修改列的isDele属性，并不是物理删除
		 * 2、新增列，数据库不存在同别名的
		 * 3、新增列，数据库存在同别名的列，将原同名列做更新
		 * ***/
		
		/**
		 * 保存-条件列
		 */
		List<MatrixColDef> oldCondList = matrixColDefManager.getColAllList(matrixId, MatrixColDef.COND_COL_TYPE);
		//数据库原有的条件列别名集合
		Map<String,MatrixColDef> condOldCodeMap = new HashMap<String,MatrixColDef>();
		for(MatrixColDef colDef : oldCondList){
			condOldCodeMap.put(colDef.getCode(), colDef);
		}
		
		List<MatrixColDef> condList = matrix.getCondList();
		if(BeanUtils.isNotEmpty(condList)){
			for(int i=0; i< condList.size(); i++){
				MatrixColDef col = condList.get(i);
				boolean isExist = false;
				if(condOldCodeMap.containsKey(col.getCode())){
					isExist = true;
					condOldCodeMap.remove(col.getCode());
				}
				col.setSn(i+1);
				boolean isAddCol = matrixColDefManager.saveCol(matrixId,col,MatrixColDef.COND_COL_TYPE,isExist);
				if(isAddCol){
					setMatrixUnpublish = true;
				}
			}
		}
		//处理删除的列
		for(String code : condOldCodeMap.keySet()){
			MatrixColDef willDele = condOldCodeMap.get(code);
			willDele.setIsDele(1);
			matrixColDefManager.update(willDele);
		}
		
		/**
		 * 保存-角色列
		 */
		List<MatrixColDef> oldRoleList = matrixColDefManager.getColAllList(matrixId, MatrixColDef.ROLE_COL_TYPE);
		//数据库原有的条件列别名集合
		Map<String,MatrixColDef> roleOldCodeMap = new HashMap<String,MatrixColDef>();
		for(MatrixColDef colDef : oldRoleList){
			roleOldCodeMap.put(colDef.getCode(), colDef);
		}
		List<MatrixColDef> roleList = matrix.getRoleList();
		if(BeanUtils.isNotEmpty(roleList)){
			for(int i=0; i< roleList.size(); i++){
				MatrixColDef col = roleList.get(i);
				boolean isExist = false;
				if(roleOldCodeMap.containsKey(col.getCode())){
					isExist = true;
					roleOldCodeMap.remove(col.getCode());
				}
				col.setSn(i+1);
				boolean isAddCol = matrixColDefManager.saveCol(matrixId,col,MatrixColDef.ROLE_COL_TYPE,isExist);
				if(isAddCol){
					setMatrixUnpublish = true;
				}
			}
		}
		//处理删除的列
		for(String code : roleOldCodeMap.keySet()){
			MatrixColDef willDele = roleOldCodeMap.get(code);
			willDele.setIsDele(1);
			matrixColDefManager.update(willDele);
		}
		
		if(StringUtil.isEmpty(matrix.getId())){
			matrix.setId(matrixId);
			matrix.setCreateBy(user.getId());
			matrix.setCreateTime(LocalDateTime.now());
			this.create(matrix);
			
		}else{
			matrix.setUpdateBy(user.getId());
			matrix.setUpdateTime(LocalDateTime.now());
			if(setMatrixUnpublish){
				matrix.setStatus(0);
			}
			this.update(matrix);
		}
	}

	@Override
	@Transactional
	public void deleLogical(String[] ids) {
		baseMapper.deleLogical(ids);
	}

	@Override
	public void publish(String id) throws SQLException {
		
		List<String> pulishedColIds = new ArrayList<String>();
		
		Matrix matrix = this.getDetail(id);
		String tableName = Matrix.TABLE_NAME_PRE+matrix.getCode();
		//判断是否已经创建了数据库表
		boolean isCreatedTable = tableOperator.isTableExist(tableName.toLowerCase());
		//如果未创建表，则创建表和列
		if(!isCreatedTable){
			DefaultTable table = new DefaultTable();
			table.setTableName(tableName.toLowerCase());
			table.setComment(matrix.getName());
			
			DefaultColumn pk = new DefaultColumn();
			pk.setFieldName("ID_");
			pk.setComment("主键");
			pk.setColumnType(Column.COLUMN_TYPE_VARCHAR);
			pk.setCharLen(64);
			pk.setIsPk(true);
			pk.setIsNull(false);
			table.addColumn(pk);
			
			List<MatrixColDef> condList = matrix.getCondList();
			for(MatrixColDef col: condList){
				DefaultColumn columnK = this.buildCondK(col);
				table.addColumn(columnK);
				
				DefaultColumn columnV = this.buildCondV(col);
				table.addColumn(columnV);
				
				pulishedColIds.add(col.getId());
			}
			
			List<MatrixColDef> roleList = matrix.getRoleList();
			for(MatrixColDef col : roleList){
				DefaultColumn roleColumn = this.buildRole(col);
				table.addColumn(roleColumn);
				
				pulishedColIds.add(col.getId());
			}
			
			tableOperator.createTable(table);
		}else{

			BaseTableMeta baseTableMeta = MetaDataUtil.getBaseTableMetaAfterSetDT(databaseContext.getDbType());
			Table table = baseTableMeta.getTableByName(tableName);
			//查询出已经物理创建的列
			List<Column> existColumnList = table.getColumnList();
			//小写的列名集合
			Set<String> existFieldNames = getFieldNameSet(existColumnList);

			List<MatrixColDef> condList = matrix.getCondList();
			for(MatrixColDef col: condList){
				if(col.getStatus() == 0){
					DefaultColumn columnK = this.buildCondK(col);
					if(!existFieldNames.contains(columnK.getFieldName().toLowerCase())){
						tableOperator.addColumn(tableName, columnK);
					}
					DefaultColumn columnV = this.buildCondV(col);
					if(!existFieldNames.contains(columnV.getFieldName().toLowerCase())){
						tableOperator.addColumn(tableName, columnV);
					}
					pulishedColIds.add(col.getId());
				}
			}
			
			List<MatrixColDef> roleList = matrix.getRoleList();
			for(MatrixColDef col : roleList){
				if(col.getStatus() == 0){
					DefaultColumn roleColumn = this.buildRole(col);
					if(!existFieldNames.contains(roleColumn.getFieldName().toLowerCase())){
						tableOperator.addColumn(tableName, roleColumn);
					}
					pulishedColIds.add(col.getId());
				}
			}
		}
		
		//更新发布状态
		matrix.setStatus(1);
		//更新列的发布状态
		this.update(matrix);
		
		if(pulishedColIds.size()>0){
			this.matrixColDefManager.updateStatus(pulishedColIds,1);
		}
	}

	/**
	 * 返回小写的列名集合
	 * @param existColumnList
	 * @return
	 */
	private static Set<String> getFieldNameSet(List<Column> existColumnList) {
		Set<String> set = new HashSet<String>();
		if(BeanUtils.isNotEmpty(existColumnList)){
			for (Column col : existColumnList) {
				String colName = col.getFieldName();
				set.add(colName.toLowerCase());
			}
		}
		return set;
	}


	public DefaultColumn buildRole(MatrixColDef col) {
		DefaultColumn roleColumn = new DefaultColumn();
		roleColumn.setFieldName(MatrixColDef.ROLE_ + col.getCode());
		roleColumn.setComment(col.getName());
		roleColumn.setColumnType(Column.COLUMN_TYPE_CLOB);
		return roleColumn;
	}

	public DefaultColumn buildCondK(MatrixColDef col) {
		DefaultColumn columnK = new DefaultColumn();
		columnK.setFieldName(MatrixColDef.CONDK_ + col.getCode());
		columnK.setComment(col.getName()+"key");
		columnK.setColumnType(Column.COLUMN_TYPE_VARCHAR);
		columnK.setCharLen(2000);
		return columnK;
	}
	
	public DefaultColumn buildCondV(MatrixColDef col) {
		DefaultColumn columnV = new DefaultColumn();
		columnV.setFieldName(MatrixColDef.CONDV_ + col.getCode());
		columnV.setComment(col.getName()+"value");
		columnV.setColumnType(Column.COLUMN_TYPE_VARCHAR);
		columnV.setCharLen(2000);
		return columnV;
	}

	/**
	 * 构建返回字段列
	 * @param matrix
	 * @return
	 */
	private List<MatrixColField> buildSelectFields(Matrix matrix) {
		List<MatrixColField> colFields = new ArrayList<MatrixColField>();
		// 加入主键
		colFields.add(new MatrixColField(MatrixColDef.ID_, MatrixColDef.ID_, "主键", "id", null));

		for (MatrixColDef col : matrix.getCondList()) {
			if (col.getStatus() == 1) {
				colFields.add(new MatrixColField(MatrixColDef.CONDK_ + col.getCode(), col.getCode(), col.getName(),
						"ck", col));
				colFields.add(new MatrixColField(MatrixColDef.CONDV_ + col.getCode(), col.getCode(), col.getName(),
						"cv", col));
			}
		}
		for (MatrixColDef col : matrix.getRoleList()) {
			if (col.getStatus() == 1) {
				colFields.add(new MatrixColField(MatrixColDef.ROLE_ + col.getCode(), col.getCode(), col.getName(), "ro", col));
			}
		}
		return colFields;
	}

	@Override
	public Map<String, Object> getData(String matrixId, PageBean pageBean) throws Exception {
		Matrix matrix = this.getDetail(matrixId);

		// 判断是否已经生成物理表
		boolean tableExist = tableOperator.isTableExist(Matrix.TABLE_NAME_PRE + matrix.getCode());
		if (!tableExist) {
			throw new RuntimeException("该矩阵还未发布！");
		}

		List<MatrixColField> colFields = this.buildSelectFields(matrix);

		String sql = constructSelectListSql(matrix.getCode(), colFields);

		PageList<Map<String, Object>> dataList = commonManager.query(sql, pageBean);

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("colFields", colFields);
		map.put("matrix", matrix);
		map.put("data", dataList);
		return map;
	}

	@Override
	public Map<String, Object> getOneData(String matrixId, String pk) throws Exception {
		Matrix matrix = this.getDetail(matrixId);

		// 判断是否已经生成物理表
		boolean tableExist = tableOperator.isTableExist(Matrix.TABLE_NAME_PRE + matrix.getCode());
		if (!tableExist) {
			throw new RuntimeException("该矩阵还未发布！");
		}

		List<MatrixColField> colFields = this.buildSelectFields(matrix);

		String sql = constructSelectOneSql(matrix.getCode(), colFields,pk);

		List<Map<String, Object>> dataList = commonManager.query(sql);
		Map<String, Object> data = (dataList!=null&&dataList.size()>0) ? dataList.get(0) : null;
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("colFields", colFields);
		map.put("matrix", matrix);
		map.put("data", data);
		return map;
	}
	@Override
	public Map<String, Object> getBlankOneData(String matrixId) {
		Matrix matrix = this.getDetail(matrixId);

		// 判断是否已经生成物理表
		boolean tableExist = tableOperator.isTableExist(Matrix.TABLE_NAME_PRE + matrix.getCode());
		if (!tableExist) {
			throw new RuntimeException("该矩阵还未发布！");
		}
		Map<String,String> data = new HashMap<String,String>();
		for (MatrixColDef col : matrix.getCondList()) {
			if (col.getStatus() == 1) {
				data.put(MatrixColDef.CONDK_ + col.getCode(), "");
				data.put(MatrixColDef.CONDV_ + col.getCode(), "");
			}
		}
		for (MatrixColDef col : matrix.getRoleList()) {
			if (col.getStatus() == 1) {
				data.put(MatrixColDef.ROLE_ + col.getCode(), "");
			}
		}

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("data", data);
		return map;
	}

	@Override
	@Transactional
	@SuppressWarnings("unchecked")
	public void saveData(String matrixId, String data) throws Exception {
		Matrix matrix = this.get(matrixId);
		Map<String, Object> bean = JsonUtil.toBean(data, Map.class);
		if (BeanUtils.isNotEmpty(bean)) {
			String existSql = this.constructIsCondKeyExistSql(matrix.getCode(), bean);
			List<Map<String, Object>> query = commonManager.query(existSql);
			if (query != null && query.size() > 0) {
				throw new RuntimeException("条件组合已存在，无需重复添加。");
			}
			String ID_ = (String) bean.get(MatrixColDef.ID_);
			String sql = "";
			if (BeanUtils.isNotEmpty(ID_)) {
				this.updateData(matrix.getCode(), bean);
			} else {
				this.insertData(matrix.getCode(), bean);
			}
		}
	}

	@Override
	@Transactional
	public void dataRemove(String matrixId, String[] ids) {
		Matrix matrix = this.get(matrixId);
		String sql = this.cunstructDeleteSql(matrix.getCode(), ids);
		jdbcTemplate.execute(sql);
	}

	/**
	 *
	 * @param matrixCode
	 * @param colFields
	 *            返回的字段
	 * @return
	 */
	public String constructSelectListSql(String matrixCode, List<MatrixColField> colFields) {
		StringBuffer sql = new StringBuffer("select ");
		List<String> fields = new ArrayList<String>();
		// 为了严谨性，必须要传入返回字段colFields来查询数据库，而不是*号，否则可能会引发字段大小写问题而导致数据对应不上。
		for (MatrixColField field : colFields) {
			fields.add(field.getField());
		}
		//获取数据时由于pgsql默认将字段转换为小写，因此这里要将查询结果转换为原字段
		String dbType = SQLUtil.getDbType();
		if (dbType.equals(SQLConst.DB_POSTGRESQL) || dbType.equals(SQLConst.DB_ORACLE) || dbType.equals(SQLConst.DB_DM)) {
			for (int i = 0; i < fields.size(); i++) {
				sql.append(fields.get(i) + " as \"" + fields.get(i) + "\"");
				if(i != fields.size() -1 ){
					sql.append(",");
				}
			}
		}else{
			sql.append(String.join(",", fields));
		}
		sql.append(" from " + Matrix.TABLE_NAME_PRE + matrixCode + " ");

		return sql.toString();
	}
	/**
	 *
	 * @param matrixCode
	 * @param colFields 返回的字段
	 * @param pk 主键
	 * @return
	 */
	public String constructSelectOneSql(String matrixCode, List<MatrixColField> colFields,String pk) {
		StringBuffer sql = new StringBuffer("select ");
		List<String> fields = new ArrayList<String>();
		// 为了严谨性，必须要传入返回字段colFields来查询数据库，而不是*号，否则可能会引发字段大小写问题而导致数据对应不上。
		for (MatrixColField field : colFields) {
			fields.add(field.getField());
		}
		String dbType = SQLUtil.getDbType();
		if (dbType.equals(SQLConst.DB_POSTGRESQL) || dbType.equals(SQLConst.DB_ORACLE) || dbType.equals(SQLConst.DB_DM)) {
			for (int i = 0; i < fields.size(); i++) {
				sql.append(fields.get(i) + " as \"" + fields.get(i) + "\"");
				if(i != fields.size() -1 ){
					sql.append(",");
				}
			}
		}else{
			sql.append(String.join(",", fields));
		}
		sql.append(" from " + Matrix.TABLE_NAME_PRE + matrixCode + " ");
		sql.append(" where ");
		sql.append(MatrixColDef.ID_ + " = '" + pk+"'");

		return sql.toString();
	}

	private String cunstructDeleteSql(String matrixCode, String[] ids) {
		StringBuffer sb = new StringBuffer("delete from ");
		sb.append(Matrix.TABLE_NAME_PRE + matrixCode + " where ");
		sb.append(MatrixColDef.ID_ + " in ('" + String.join("','", ids) + "')");
		return sb.toString();
	}

	private void insertData(String matrixCode, Map<String, Object> bean) throws Exception {
		StringBuffer sb = new StringBuffer("insert into ");
		sb.append(Matrix.TABLE_NAME_PRE + matrixCode);

		List<String> setFields = new ArrayList<String>();
		List<String> values = new ArrayList<String>();
		// 主键赋值
		String ID_ = UniqueIdUtil.getSuid();
		setFields.add(MatrixColDef.ID_);
		values.add(ID_);

		for (String key : bean.keySet()) {
			setFields.add(key);
			Object value = bean.get(key);
			if (value instanceof List) {
				values.add(JsonUtil.toJson(value));
			} else {
				if(BeanUtils.isEmpty(value)){
					values.add("");
				}else{
					values.add(value.toString());
				}
			}
		}
		sb.append("(" + String.join(",", setFields) + ")");
		sb.append(" values ");
		
		sb.append("(" + String.join(",", values.stream().map(e->{return "?";}).collect(Collectors.toList())) + ")");

		commonManager.execute(sb.toString(),values.toArray());
	}

	private void updateData(String matrixCode, Map<String, Object> bean) throws Exception {
		StringBuffer sb = new StringBuffer("update ");
		sb.append(Matrix.TABLE_NAME_PRE + matrixCode + " set ");

		List<String> setFields = new ArrayList<String>();
		List<String> values = new ArrayList<String>();
		
		String ID_ = (String) bean.remove(MatrixColDef.ID_);
		
		for (String key : bean.keySet()) {
			Object value = bean.get(key);
			setFields.add(key+"=?");
			if (value instanceof List) {
				values.add(JsonUtil.toJson(value));
			}else{
				if(BeanUtils.isEmpty(value)){
					values.add("");
				}else{
					values.add(value.toString());
				}
			}
		}
		sb.append(String.join(",", setFields));
		sb.append(" where " + MatrixColDef.ID_ + String.format("='%s'", ID_));
		
		commonManager.execute(sb.toString(),values.toArray());
	}

	/**
	 * 构建判断条件列组合是否已经存在
	 *
	 * @param matrixCode
	 * @param bean
	 * @return
	 * @throws IOException
	 */
	private String constructIsCondKeyExistSql(String matrixCode, Map<String, Object> bean) throws IOException {
		StringBuffer sb = new StringBuffer("select * from ");
		sb.append(Matrix.TABLE_NAME_PRE + matrixCode);
		String dbType = SQLUtil.getDbType();
		List<String> setFields = new ArrayList<String>();
		for (String key : bean.keySet()) {
			if (key.indexOf(MatrixColDef.CONDK_) == 0) {
				Object value = bean.get(key);
				if(BeanUtils.isNotEmpty(value)){
					if(SQLConst.DB_ORACLE.equals(dbType)){
						setFields.add("to_char(" + key + ") = '" + value + "'");
					}else{
						setFields.add(key + " = '" + value + "'");
					}
				}else{
					String empty = "("+key+" is null or "+key+"='')";
					setFields.add(empty);
				}
			}
		}

		if (BeanUtils.isNotEmpty(bean.get(MatrixColDef.ID_))) {
			setFields.add(MatrixColDef.ID_ +" != '" + bean.get(MatrixColDef.ID_)+"'");
		}
		if(setFields.size()>0){
			sb.append(" where ");
		}
		sb.append(String.join(" and ", setFields));

		return sb.toString();
	}
	
	@Override
	public List<Map<String, Object>> getUsers(Map<String,Object> params) throws Exception {
		String sql = (String) params.get("sql");
		sql = Base64.getFromBase64(sql);
		List<Map<String, Object>> query = null;
		query = commonManager.query(sql);
		return query;
	}
}