package com.changhong.autoform.mapper.table.impl;

import com.alibaba.fastjson.JSON;
import com.changhong.autoform.constant.ConstMap;
import com.changhong.autoform.core.exception.RowOverLengthException;
import com.changhong.autoform.core.mapper.BaseMapper;
import com.changhong.autoform.entity.ColunmsFieldEntity;
import com.changhong.autoform.entity.SubTableStructure;
import com.changhong.autoform.entity.TableStructure;
import com.changhong.autoform.entity.sql.create.CreateTable;
import com.changhong.autoform.entity.sql.insert.Insert;
import com.changhong.autoform.entity.sql.update.UpdateTable;
import com.changhong.autoform.mapper.table.TableMapper;
import com.iemsoft.framework.cloud.core.tools.ObjectUtil;
import org.springframework.stereotype.Component;
import org.springframework.ui.ModelMap;

import java.util.*;
import java.util.stream.Collectors;

@Component("simpleTableMapper")
public class TableMapperImpl extends BaseMapper implements TableMapper{

	private static final Map<String, String> DBTYPE = new HashMap<>();

	private static final List<String> FIELDLIST = new ArrayList<>();

	private static final Map<String, Integer> DBTYPELENGTH = new HashMap<>();


	static {
		// 单行文本
		DBTYPE.put("text", ConstMap.VARCHAR150);
		// 单选按钮
		DBTYPE.put("radio", ConstMap.VARCHAR50);
		// 下拉列表框
		DBTYPE.put("dropdownlist", ConstMap.LONGTEXT);
		// 日期控件
		DBTYPE.put("calendar", ConstMap.VARCHAR30);
		// 复选框
		DBTYPE.put("checkbox", ConstMap.LONGTEXT);
		// 子表单
		DBTYPE.put("subform", ConstMap.VARCHAR30);
		// 数字
		DBTYPE.put("number", ConstMap.VARCHAR30);
		// 多行文本
		DBTYPE.put("textarea", ConstMap.LONGTEXT);
		// HTML
		DBTYPE.put("htmlediter", ConstMap.LONGTEXT);
		// 条码
		DBTYPE.put("barcode", ConstMap.VARCHAR30);
		// 二维码
		DBTYPE.put("qrcode", ConstMap.VARCHAR200);
		// 图片
		DBTYPE.put("image", ConstMap.LONGTEXT);
		// 文件上传
		DBTYPE.put("fileupload", ConstMap.LONGTEXT);
		// 数型控件
		DBTYPE.put("tree", ConstMap.LONGTEXT);
		// 金额组件
		DBTYPE.put("amount", ConstMap.LONGTEXT);
		//自定义控件
		DBTYPE.put("mytext",ConstMap.VARCHAR150);
		//自动生成编号控件
		DBTYPE.put("auto",ConstMap.VARCHAR150);

		
		// 单行文本
		DBTYPELENGTH.put("text", 150);
		// 单选按钮
		DBTYPELENGTH.put("radio", 50);
		// 日期控件
		DBTYPELENGTH.put("calendar", 30);
		// 复选框
		// 子表单
		DBTYPELENGTH.put("subform", 30);
		// 数字
		DBTYPELENGTH.put("number", 30);
		// 条码
		DBTYPELENGTH.put("barcode", 30);
		// 二维码
		DBTYPELENGTH.put("qrcode", 200);

		// 表单固定字段
		FIELDLIST.add("DATAROWNUM");
		FIELDLIST.add("TENANTID");
		FIELDLIST.add("PARENTDATAROWNUM");
		FIELDLIST.add("DELETEFLG");
		FIELDLIST.add("CREATEUSER");
		FIELDLIST.add("CREATEDATETIME");
		FIELDLIST.add("UPDATEUSER");
		FIELDLIST.add("UPDATEDATETIME");
		FIELDLIST.add("PROCESSINSTID");
		
	}

	//创建表结构
	@Override
	public int createTable(TableStructure tableStructure) {
		int count = 0;
		count = update(new CreateTable(String.format(tableStructure.getTableName())
				, new ModelMap().addAllAttributes(tableStructure.getFields())));
		//添加记录到formDataManager表中
		Insert insert = new Insert("FORMDATAMANAGER"
				,new ModelMap("FORMDATATABLE", tableStructure.getTableName())
				.addAttribute("FORMID", tableStructure.getFormId())
				.addAttribute("SUBFORMFLG", 0)
				.addAttribute("CREATEUSER", tableStructure.getCreatUser())
				.addAttribute("UPDATEUSER", tableStructure.getCreatUser())
				.addAttribute("FORMDATANUM", 0)
				.addAttribute("DELETEFLG", 0)
				.addAttribute("TENANTID", tableStructure.getTenantId()));
		execute(insert);
		List<SubTableStructure> subTableList = tableStructure.getSubTableList();
		if(subTableList != null && subTableList.size()>0){
			for (SubTableStructure subTableStructure : subTableList) {
				String subTableName = subTableStructure.getTableName();
				Map<String, Integer> subFormNameAndFormDataNum = subTableStructure.getSubFormNameAndFormDataNum();
				count += update(new CreateTable(subTableName
						,new ModelMap().addAllAttributes(subTableStructure.getFields())));

				Insert subInsert = new Insert("FORMDATAMANAGER"
						,new ModelMap("FORMDATATABLE", subTableStructure.getTableName())
						.addAttribute("FORMID", tableStructure.getFormId())
						.addAttribute("SUBFORMFLG", 1)
						.addAttribute("CREATEUSER", subTableStructure.getCreatUser())
						.addAttribute("UPDATEUSER", subTableStructure.getCreatUser())
						.addAttribute("FORMDATANUM", subFormNameAndFormDataNum.get(subTableName))
						.addAttribute("DELETEFLG", 0)
						.addAttribute("TENANTID", tableStructure.getTenantId()));
				execute(subInsert);
			}
		}
		return count;
	}

	//修改表结构
	@Override
	public int updateTable(TableStructure tableStructure) {
		System.out.println("updateTable请求参数:"+JSON.toJSONString(tableStructure));
		// 获取现有主表字段
		alterTableCols(tableStructure.getTableName(), tableStructure.getAliasValuesMap());
		/*
			处理子表单
		*/
		if(ObjectUtil.empty(tableStructure.getSubTableList())){
			return 1;
		}
		tableStructure.getSubTableList().stream().forEach(subTableStructure ->{
			if(ObjectUtil.notEmpty(subTableStructure.getFields())){
				update(new CreateTable(subTableStructure.getTableName()
						,new ModelMap().addAllAttributes(subTableStructure.getFields())));
				return;
			}
			alterTableCols(subTableStructure.getTableName()
					, Optional.ofNullable(subTableStructure.getAliasSubValuesMap().get(subTableStructure.getSubFormNameAndFormDataNum().values().stream().findFirst().orElse(0)))
							.orElse(Collections.EMPTY_MAP)
			);
		});
		return 1;
	}

	protected void alterTableCols(String tableName, Map<String, String> aliasValues){
		List<ColunmsFieldEntity> mainCols = getColunmsField(tableName);
		Set<String> cols = mainCols.stream().map(ColunmsFieldEntity::getField).collect(Collectors.toSet());
		Map<String, Object> alterFields = new HashMap<>();
		aliasValues.entrySet().stream()
				.filter(val->!cols.contains(val.getKey()))
				.forEach(val->alterFields.put(val.getKey()
						, Optional.ofNullable(DBTYPE.get(aliasValues.get(val.getValue())))
								.orElse(ConstMap.VARCHAR50)
				));

		if(ObjectUtil.notEmpty(alterFields)) {
			// 添加字段
			update(new UpdateTable(tableName, alterFields));
		}

	}
	
//	public static void main(String... args){
//	    String v = "DATAROWNUM\tint(11)\n" +
//				"TENANTID\tchar(50)\n" +
//				"PARENTDATAROWNUM\tint(11)\n" +
//				"journey\tvarchar(30)\n" +
//				"单行文本56\tvarchar(150)\n" +
//				"子表单49\tvarchar(30)\n" +
//				"DELETEFLG\tchar(1)\n" +
//				"CREATEUSER\tvarchar(50)\n" +
//				"CREATEDATETIME\ttimestamp\n" +
//				"UPDATEUSER\tvarchar(50)\n" +
//				"UPDATEDATETIME\ttimestamp\n" +
//				"PROCESSINSTID\tdecimal(20,0)\n" +
//				"orderCode\tvarchar(50)\n" +
//				"orderStatus\tvarchar(2)\n" +
//				"processState\tvarchar(2)\n" +
//				"processLog\tvarchar(255)\n" +
//				"processTask\tvarchar(255)\n" +
//				"processUser\tvarchar(20)\n" +
//				"processFlag\tvarchar(20)\n" +
//				"solidification\tlongtext\n" +
//				"voucherCode\tvarchar(20)\n" +
////				"C_Type\tlongtext\n" +
//				"Amount\tvarchar(30)\n" +
//				"toWriteBudget\tlongtext\n" +
//				"payinfo\tvarchar(30)\n" +
//				"budgetStartDate\tvarchar(30)\n" +
//				"Remark\tvarchar(150)\n" +
//				"budget_itemBalance\tvarchar(30)\n" +
//				"reimburseMan\tlongtext\n" +
//				"expend_department\tlongtext\n" +
//				"expendUseMoney\tvarchar(30)\n" +
//				"netMoneyAll\tvarchar(30)\n" +
//				"debitItem\tvarchar(150)\n" +
//				"expenDesc\tvarchar(150)\n" +
//				"currency\tlongtext\n" +
//				"单行文本38\tvarchar(150)\n" +
//				"number_business\tvarchar(50)";
//		List<ColunmsFieldEntity> mainCols = new ArrayList<>();
//		for(String col : v.split("\n")){
//			ColunmsFieldEntity c = new ColunmsFieldEntity();
//			c.setField(col.split("\t")[0]);
//			c.setType(col.split("\t")[1]);
//			mainCols.add(c);
//		}
//		Set<String> cols = mainCols.stream().map(ColunmsFieldEntity::getField).collect(Collectors.toSet());
//		Map<String, Object> alterFields = new HashMap<>();
//		Map<String, Object> aliasValues = JSONTool.toObj("{\n" +
//				"        \"C_Type\":\"tree\",\n" +
//				"        \"journey\":\"subform\",\n" +
//				"        \"toWriteBudget\":\"mytext\",\n" +
//				"        \"payinfo\":\"subform\",\n" +
//				"        \"budgetStartDate\":\"calendar\",\n" +
//				"        \"Remark\":\"text\",\n" +
//				"        \"budget_itemBalance\":\"number\",\n" +
//				"        \"reimburseMan\":\"dropdownlist\",\n" +
//				"        \"expend_department\":\"tree\",\n" +
//				"        \"expendUseMoney\":\"number\",\n" +
//				"        \"netMoneyAll\":\"number\",\n" +
//				"        \"debitItem\":\"text\",\n" +
//				"        \"expenDesc\":\"text\",\n" +
//				"        \"currency\":\"dropdownlist\",\n" +
//				"        \"单行文本38\":\"text\",\n" +
//				"        \"number_business\":\"auto\"\n" +
//				"    }", Map.class);
//		aliasValues.entrySet().stream()
//				.filter(val->!cols.contains(val.getKey()))
//				.forEach(val->alterFields.put(val.getKey()
//						, Optional.ofNullable(DBTYPE.get(aliasValues.get(val.getValue())))
//								.orElse(ConstMap.VARCHAR50)
//				));
//		System.out.println(alterFields);
//	}

	protected List<ColunmsFieldEntity> getColunmsField(String tableName) {
		return showColumn(" show columns from "+tableName);
	}

	@SuppressWarnings("unused")
	private Map<String,Object> alterTable(String tableName, 
			List<String> fieldtypeList, Map<String, String> fieldsLength, List<String> aliasValues,
			List<String> oldField, Map<String,String> aliasValuesMap) throws RowOverLengthException {

		Map<String, List<String>> optionColumns = genAddAndDelColumns(aliasValues, fieldtypeList, oldField);
		List<String> deleteList = optionColumns.get("DELETE");
		List<String> addList = optionColumns.get("ADD"); 

		int maxLengthDel = 0;
		for (String delObj : deleteList) {
			if (fieldsLength.get(delObj) != null) {
				String length = fieldsLength.get(delObj).replaceAll("[^0-9]", "");
				if (!fieldsLength.get(delObj).equals("longtext")) {
					maxLengthDel += Integer.parseInt(length);  //数据类型大小150  varchar(150)
				}
			}
		}

		if (!checkRowLength(fieldtypeList, maxLengthDel)) {
			throw new RowOverLengthException();
		}
		Map<String,Object> data = new HashMap<>();

		for (int i = 0; i < addList.size(); i++) {
			String field = addList.get(i);
			String type = aliasValuesMap.get(field);
			if (DBTYPE.containsKey(type)) {
				data.put(field, DBTYPE.get(type));
			}
		}

		// 表结构没有更改
		if (deleteList.isEmpty() && addList.isEmpty()) {
			return null;
		}
		return data;
	}

	private Map<String, Object> alterSubTable(String subTableName, List<String> subFormObjcloumn,  List<String> fieldtypeList,
			Map<String, String> fieldsLength, List<String> oldField, Map<String, String> subFormNameAndColumnsMap )
					throws RowOverLengthException {

		Map<String, List<String>> optionColumns = genAddAndDelColumns(subFormObjcloumn, fieldtypeList, oldField);
		List<String> deleteList = optionColumns.get("DELETE");
		List<String> addList = optionColumns.get("ADD");

		int maxLengthDel = 0;
		for (String delObj : deleteList) {
			if (fieldsLength.get(delObj) != null) {
				if (!fieldsLength.get(delObj).equals("longtext")) {
					Integer length = Integer.parseInt(fieldsLength.get(delObj).replaceAll("[^0-9]", ""));
					maxLengthDel += length;
				}
			}
		}
		if (!checkRowLength(fieldtypeList, maxLengthDel)) {
			throw new RowOverLengthException();
		}
		Map<String,Object> data = new HashMap<>();
		StringBuilder alterTableSql = new StringBuilder(
				"/*!mycat:sql=select tenantid from FORMDATAMANAGER WHERE TENANTID = '%s' AND FORMID = '%s' */ alter table ");
		alterTableSql.append(subTableName);
		// 表单数据表列增加
		for (int i = 0; i < addList.size(); i++) {
			String field = addList.get(i);
			if("DATAROWNUM".equals(field))
				continue;
			String type = subFormNameAndColumnsMap.get(field);
			if (DBTYPE.containsKey(type)) {
				alterTableSql.append(" ADD COLUMN ");
				alterTableSql.append(field);
				alterTableSql.append(DBTYPE.get(type));
				data.put(field,DBTYPE.get(type));
				alterTableSql.append(" NULL AFTER SUBDATAROWNUM");
				alterTableSql.append(ConstMap.COMMA);
			}
		}
		if (!addList.isEmpty()) {
			alterTableSql.deleteCharAt(alterTableSql.length() - 1);
		}
		// 表结构没有更改
		if (deleteList.isEmpty() && addList.isEmpty()) {
			return null;
		}
		alterTableSql.append(";");
		return data;
	}

	private boolean checkRowLength(List<String> fieldtypeList, Integer oldLength) {
		int rowLength = 0;
		for (String type : fieldtypeList) {
			if (DBTYPELENGTH.containsKey(type)) {
				rowLength += DBTYPELENGTH.get(type);
			}
		}
		if ((rowLength + oldLength) > ConstMap.MAXROWLENGTH) {
			return false;
		}
		return true;
	}

	private Map<String, List<String>> genAddAndDelColumns(List<String> aliasValues,
			List<String> fieldtypeList,List<String> oldField) {

		Map<String, List<String>> map = new HashMap<>();
		List<String> needToDel = new ArrayList<>();
		List<String> needToAdd = new ArrayList<>();
		List<String> needToUpd = new ArrayList<>();
		int count = 0;
		for (String alias : aliasValues) {
			if (DBTYPE.containsKey(fieldtypeList.get(count))) {
				if (!oldField.contains(alias)) {
					needToAdd.add(alias);
				} else {
					needToUpd.add(alias);
				}
			}
			count++;
		}
		for (String old : oldField) {
			if (!aliasValues.contains(old)) {
				needToDel.add(old);
			}
		}
		map.put("DELETE", needToDel);
		map.put("ADD", needToAdd);
		map.put("UPDATE", needToUpd);
		return map;
	}

}