package com.product.admin.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.product.admin.config.CmnConst;
import com.product.admin.config.SystemCode;
import com.product.admin.service.idel.ISystemPromptService;
import com.product.common.lang.StringUtils;
import com.product.core.cache.DataPoolCacheImpl;
import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldSetEntity;
import com.product.core.exception.BaseException;
import com.product.core.service.support.AbstractBaseService;
import com.product.core.service.support.QueryFilterService;

import com.product.core.spring.context.SpringMVCContextHolder;
import com.product.core.transfer.Transactional;
import com.product.util.BaseUtil;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Copyright © 2019
 *
 * @Title: SystemPromptService
 * @Project: LX
 * @Date: 2019-05-10 11:07
 * @Author: Dave.Luo
 * @Description: 高级参照Service层
 */

@Component
public class SystemPromptService extends AbstractBaseService implements ISystemPromptService {

	@Autowired
	public BaseDao baseDao;
	@Autowired
	public QueryFilterService queryFilterService;

	public QueryFilterService getQueryFilterService() {
		return queryFilterService;
	}

	public void setQueryFilterService(QueryFilterService queryFilterService) {
		this.queryFilterService = queryFilterService;
	}

	public FieldSetEntity getPromptFind(String uuid) throws BaseException {

		return baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_PROMPT, uuid, false);
	}

	/**
	 * 获取高级参照数据源表 别名
	 *
	 * @return
	 * @throws BaseException
	 */
	public DataTableEntity getPromptSourceTables(Integer is_multiple) throws BaseException {
		DataTableEntity dataTableEntity = new DataTableEntity();
		if (1 == is_multiple) {
			getPromptSourceTableAilas(dataTableEntity);
		} else {
			dataTableEntity = DataPoolCacheImpl.getInstance().getCacheData("所有表信息");
		}
		return dataTableEntity;
	}

	/**
	 * 获取高级参照数据源表别名 参数不为空返回list集合 参数为空返回空集合 使用指针方式将表别名加到传入参数中
	 *
	 * @return List集合 value 表名 +别名
	 * @throws BaseException
	 */
	public List<String> getPromptSourceTableAilas(DataTableEntity dt) throws BaseException {
		DataPoolCacheImpl instance = DataPoolCacheImpl.getInstance();
		// 获取缓存中所有数据表
		DataTableEntity cacheTableInfo = instance.getCacheData("所有表信息");
		List<String> aliasList = null;
		if (dt == null) {
			aliasList = new ArrayList<>();
		}
		if (!BaseUtil.dataTableIsEmpty(cacheTableInfo)) {
			for (int i = 0; i < cacheTableInfo.getRows(); i++) {
				// 表名
				String table_name = cacheTableInfo.getString(i, CmnConst.TABLE_NAME);
				// 表id
				String table_id = cacheTableInfo.getString(i, CmnConst.TABLE_ID);
				// 别名
				String alias = "a" + table_id;
				// 获取需要重复关联的表
				FieldSetEntity repetitionJoinTable = getRepetitionJoinTable(table_name, alias);
				if (dt != null) {
					FieldSetEntity fs = new FieldSetEntity();
					fs.setTableName("product_sys_datamodel_field");
					// 表名和表id组成表别名
					fs.setValue(CmnConst.TABLE_NAME, table_name + " " + alias);
					dt.addFieldSetEntity(fs);
					if (repetitionJoinTable != null) {
						dt.addFieldSetEntity(repetitionJoinTable);
					}
					// 参数不等于空则不在list 集合中条件参数
					continue;
				}
				// 表id组成别名
				aliasList.add(alias);
				if (repetitionJoinTable != null) {
					aliasList.add(table_name + " " + repetitionJoinTable.getString("alias"));
				}
			}
		}
		return aliasList;
	}

	/**
	 * 定义高级参照数据源重复表
	 *
	 * @return
	 */
	private List<String> getPromptRepetitionJoinTable() {
		List<String> tableName = new ArrayList<>();
		// 需要重复关联的表
		tableName.add(CmnConst.PRODUCT_SYS_ORG_LEVELS);
		tableName.add("product_sys_staffs");

		return tableName;
	}

	/**
	 * 根据表名别名获取重复关联表
	 *
	 * @param tableName
	 * @param alias
	 * @return
	 * @throws BaseException
	 */
	private FieldSetEntity getRepetitionJoinTable(String tableName, String alias) throws BaseException {
		if (StringUtils.isEmpty(tableName) || StringUtils.isEmpty(alias)) {
			return null;
		}
		// 特殊加表操作 如.PRODUCT_SYS_org_levels 关联公司 且也同时关联
		// 部门就需要在参照数据源中显示2.PRODUCT_SYS_org_levels表
		// 别名后再加多_t 成为另一个关联表
		String repetitionAlias = "_t";
		List<String> promptRepetitionJoinTable = getPromptRepetitionJoinTable();
		if (!promptRepetitionJoinTable.contains(tableName)) {
			return null;
		}
		FieldSetEntity fs = new FieldSetEntity();
		fs.setTableName(CmnConst.PRODUCT_SYS_DATAMODEL_TABLE);
		fs.setValue(CmnConst.TABLE_NAME, tableName + " " + alias + repetitionAlias);
		fs.setValue(CmnConst.UUID, tableName + " " + alias + repetitionAlias);
		fs.setValue("alias", tableName + " " + alias + repetitionAlias);
		return fs;
	}

	/**
	 * 根据表名获取字段名称
	 *
	 * @return
	 * @throws BaseException
	 */
	public JSONArray getPromptSourceTableFields(String table_name, Integer is_multiple) throws BaseException {
		if (StringUtils.isEmpty(table_name) || table_name.toLowerCase().indexOf("join") != -1) {
			return new JSONArray();
		}
		JSONArray fieldsArray = new JSONArray();
		DataPoolCacheImpl instance = DataPoolCacheImpl.getInstance();
		if (1 == is_multiple) {
			String[] tableNames = table_name.split(",");
			// 在缓存中取出 表信息
			DataTableEntity cacheTableInfo = instance.getCacheData("所有表信息");
			if (!BaseUtil.dataTableIsEmpty(cacheTableInfo)) {
				Map<Object, List<FieldSetEntity>> fieldSets = cacheTableInfo.getFieldSets();
				if (fieldSets != null) {
					forTableNames(tableNames, fieldSets, instance, fieldsArray);
				}
			}
		} else {
			return returnFieldDt(instance, table_name, fieldsArray);
		}
		return fieldsArray;
	}

	private void forTableNames(String[] tableNames, Map<Object, List<FieldSetEntity>> fieldSets,
			DataPoolCacheImpl instance, JSONArray fieldsArray) {
		for (int i = 0; i < tableNames.length && tableNames[i].split(" ").length > 1; i++) {
			String tableName = tableNames[i].split(" ")[0];
			String alias = tableNames[i].split(" ")[1];
			List<FieldSetEntity> tableList = fieldSets.get(tableName);
			if (tableList != null && tableList.get(0) != null) {
				FieldSetEntity fs = tableList.get(0);
				String table_uuid = fs.getString(CmnConst.UUID);
				if (!StringUtils.isEmpty(table_uuid)) {
					DataTableEntity fieldDt = instance.getCacheData("所有字段信息并按表分组", new String[] { table_uuid });
					if (!BaseUtil.dataTableIsEmpty(fieldDt)) {
						forFieldDt(fieldDt, tableNames, i, alias, fieldsArray);
					}
				}
			}
		}
	}

	private void forFieldDt(DataTableEntity fieldDt, String[] tableNames, int i, String alias, JSONArray fieldsArray) {
		for (int k = 0; k < fieldDt.getRows(); k++) {
			String field_name = fieldDt.getString(k, CmnConst.FIELD_NAME);
			JSONObject json = new JSONObject();
			json.put(CmnConst.TABLE_NAME, tableNames[i]);
			json.put(CmnConst.FIELD_NAME, alias + "." + field_name);
			fieldsArray.add(json);
		}
	}

	private JSONArray returnFieldDt(DataPoolCacheImpl instance, String table_name, JSONArray fieldsArray) {
		DataTableEntity cacheTableInfo = instance.getCacheData("所有表信息", new String[] { table_name });
		if (!BaseUtil.dataTableIsEmpty(cacheTableInfo)) {
			String table_uuid = cacheTableInfo.getString(0, CmnConst.UUID);
			DataTableEntity fieldDt = instance.getCacheData("所有字段信息并按表分组", new String[] { table_uuid });
			return BaseUtil.dataTableEntityToJson(fieldDt);
		}
		return fieldsArray;
	}

	/**
	 * 验证多表关联参照
	 *
	 * @param fse
	 * @throws BaseException
	 */
	public void verifyPromptFormTables(FieldSetEntity fse) throws BaseException {
		verifyPromptError(fse);
		// 关联类型
		String relate_filter = fse.getString(CmnConst.RELATE_FILTER);
		String source_table = fse.getString(CmnConst.SOURCE_TABLE);
		// 多个表拆分
		String[] tables = source_table.split(",");
		String[] filters = relate_filter.split(",");
		forVerifyisRelate(tables, filters);
		// 多表关联个数与关联条件个数验证
		verifyPromptErrors(tables, filters);
	}

	private void forVerifyisRelate(String[] tables, String[] filters) {
		for (int i = 0; i < tables.length; i++) {
			boolean is_relate = false;
			// 用空格拆分别名
			String[] str = tables[i].split(" ");
			if ((filters.length > 0 && i <= 1) || (i > 1 && filters.length >= i)) {
				String filter = filters[i <= 1 ? 0 : i - 1];
				if (!StringUtils.isEmpty(filter)) {
					is_relate = verifyPromptFor(str, filter);
				}
			}
			if (!is_relate) {
				throw new BaseException(SystemCode.SYSTEM_PROMPT_ASSOCIATION_FILTER_INCOMPLETE.getValue(),
						SystemCode.SYSTEM_PROMPT_ASSOCIATION_FILTER_INCOMPLETE.getText(), this.getClass(),
						"public static void verifyPromptFormTables(FieldSetEntity fse) ");
			}
		}
	}

	private boolean verifyPromptFor(String[] str, String filter) {
		boolean is_relate = false;
		for (int j = 1; j < str.length; j++) {
			String field = str[j];
			if (!StringUtils.isEmpty(field) && filter.indexOf(field + ".") != -1) {
				// 验证条件中是否有表"别名. "的字符
				is_relate = true;
				break;
			}
		}
		return is_relate;
	}

	private void verifyPromptErrors(String[] tables, String[] filters) {
		if ((tables.length - 1) != filters.length) {
			throw new BaseException(
					SystemCode.SYSTEM_PROMPT_NUMBER_OF_TABLE_AND_NUMBER_OF_FILTER_MISMATCHING.getValue(),
					SystemCode.SYSTEM_PROMPT_NUMBER_OF_TABLE_AND_NUMBER_OF_FILTER_MISMATCHING.getText(),
					this.getClass(), "public static void verifyPromptFormTables(FieldSetEntity fse)");
		}
	}

	private void verifyPromptError(FieldSetEntity fse) {
		String relate_type = fse.getString(CmnConst.RELATE_TYPE);
		if (StringUtils.isEmpty(relate_type)) {
			throw new BaseException(SystemCode.SYSTEM_PROMPT_ASSOCIATION_TYPE_REQUIRED.getValue(),
					SystemCode.SYSTEM_PROMPT_ASSOCIATION_TYPE_REQUIRED.getText(), this.getClass(),
					"public void verifyPromptFormTables(FieldSetEntity fse)");
		}
		// 数据源
		// 关联条件
		String relate_filter = fse.getString(CmnConst.RELATE_FILTER);
		if (StringUtils.isEmpty(relate_filter)) {
			throw new BaseException(SystemCode.SYSTEM_PROMPT_ASSOCIATION_FILTER_REQUIRED.getValue(),
					SystemCode.SYSTEM_PROMPT_ASSOCIATION_FILTER_REQUIRED.getText(), this.getClass(),
					"public void verifyPromptFormTables(FieldSetEntity fse)");
		}
	}

	/**
	 * 新增高级参照配置
	 *
	 * @param fse
	 * @return
	 * @throws BaseException
	 */
	@Transactional
	public String addPrompt(FieldSetEntity fse) throws BaseException {
		fse.setValue("created_by", SpringMVCContextHolder.getCurrentUser().getUser_id());// 获取登录帐号
		fse.setValue("created_utc_datetime", new Date());// 创建时间
		fse.setValue("select_fields", selectFields(fse.getString("value_field"), fse.getString("view_fields"),
				fse.getString("prompt_field")));
		if ("1".equals(fse.getString(CmnConst.IS_MULTIPLE))) {
			verifyPromptFormTables(fse);
		}
		String uuid = baseDao.add(fse);
		if (!StringUtils.isEmpty(uuid)) {
			DataPoolCacheImpl.getInstance().cacheDataByTable(CmnConst.PRODUCT_SYS_PROMPT);
		}
		return uuid;
	}

	/**
	 * 更新高级参照配置
	 *
	 * @param fse
	 * @return
	 * @throws BaseException
	 */
	@Transactional
	public boolean updatePrompt(FieldSetEntity fse) throws BaseException {
		fse.setValue("updated_by", SpringMVCContextHolder.getCurrentUser().getUser_id());// 获取登录帐号
		fse.setValue("updated_utc_datetime", new Date());// 创建时间
		fse.setValue("select_fields", selectFields(fse.getString("value_field"), fse.getString("view_fields"),
				fse.getString("prompt_field")));
		if ("1".equals(fse.getString(CmnConst.IS_MULTIPLE))) {
			verifyPromptFormTables(fse);
		}
		boolean update = baseDao.update(fse);
		if (update) {
			DataPoolCacheImpl.getInstance().cacheDataByTable(CmnConst.PRODUCT_SYS_PROMPT);
		}
		return update;
	}

	/***
	 * 高级参照列表查询
	 *
	 * @param cpage    页数
	 * @param pagesize 每页条数
	 * @return
	 * @throws BaseException
	 */
	public DataTableEntity getPromptList(FieldSetEntity fse) throws BaseException {
		// 郑盟 12-18 12：02
		String queryFilter = "";
		if (!BaseUtil.dataTableIsEmpty(fse.getSubDataTable("systemSeniorQueryString"))) {
			queryFilter = queryFilterService.getQueryFilter(fse);
		}
		DataTableEntity dt = baseDao.listTable(CmnConst.PRODUCT_SYS_PROMPT, queryFilter, new Object[] {}, null, null,
				fse.getInteger("pagesize"), fse.getInteger("cpage"));
		baseDao.loadPromptData(dt);
		return dt;
	}

	/***
	 * 组装selectFields字段
	 *
	 * @param valueField  取列值
	 * @param viewFields  选择后显示列对应的名称
	 * @param promptField 弹出选择框时提示列
	 * @return
	 */
	public String selectFields(String valueField, String viewFields, String promptField) {
		StringBuilder selectFields = new StringBuilder();
		selectFields.append(valueField);
		selectFields.append(" value_field,");
		selectFields.append(viewFields);
		selectFields.append(" view_fields,");
		if (promptField != null && promptField.length() > 0) {
			String[] ar = promptField.split("\\,");
			for (int i = 0; i < ar.length; i++) {
				selectFields.append(ar[i]);
				selectFields.append(" f");
				selectFields.append((i + 1));
				selectFields.append(",");

			}
		}
		return selectFields.substring(0, selectFields.length() - 1);
	}

	/***
	 * 数据建模数据源表列表查询
	 *
	 * @param cpage      页数
	 * @param pagesize   每页条数
	 * @param table_name 表名
	 * @param table_type 表类型
	 * @return
	 * @throws BaseException
	 */
	public DataTableEntity getDatamodelTableList(Integer cpage, Integer pagesize, String table_name, String table_type)
			throws BaseException {
		// 郑盟 2020年12月17日 下午16:05
		StringBuilder sql = new StringBuilder();
		sql.append(
				"select uuid,table_name,table_description,table_primary_key,table_type,table_base_view,created_by,created_utc_datetime FROM product_sys_datamodel_table where 1 = 1 ");
		Object[] object = null;
		// 周杰 2020年12月17日 上午11:16
		if (!StringUtils.isEmpty(table_name)) {
			sql.append(" and table_name = ? ");
			object = new Object[] { table_name };
		}
		// 周杰 2020年12月17日 上午11:16
		if (!StringUtils.isEmpty(table_type)) {
			sql.append(" and table_type = ? ");
			if (object != null) {
				object = new Object[] { table_name, table_type };
			} else {
				object = new Object[] { table_type };
			}
		}

		return baseDao.listTable(sql.toString(), object, pagesize, cpage);
	}

	/***
	 * 数据建模数据源字段表列表查询
	 *
	 * @param cpage      页数
	 * @param pagesize   每页条数
	 * @param table_uuid.PRODUCT_SYS_datamodel_table表中的uuid字段值
	 * @return
	 * @throws BaseException
	 */
	public DataTableEntity getDatamodelFieldList(Integer cpage, Integer pagesize, String table_uuid)
			throws BaseException {
		String sql = "select uuid,table_uuid,field_name,field_show_name,field_type,field_length,field_unit,created_by,updated_utc_datetime FROM product_sys_datamodel_field where table_uuid = ? ";

		return baseDao.listTable(sql, new Object[] { table_uuid }, pagesize, cpage);
	}

}