package com.product.server.report.util;

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
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.data.config.ErrorCode;
import com.product.datasource.config.DataBaseType;
import com.product.datasource.dao.Dao;
import com.product.server.report.config.CmnConst;
import com.product.server.report.config.SystemCode;
import com.product.util.BaseUtil;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

public class QuerySqlParseUtil {

	/**
	 * 获取与表相关的年表 SQL过滤条件
	 *
	 * @param tableNames 表名数组
	 * @return
	 */
	public static String getTableOfYearSQLFilter(Object[] tableNames) {
		StringBuilder tableFilter = new StringBuilder();
		for (int i = 0; i < tableNames.length; i++) {
			String tableName = (String) tableNames[i];
			if (!BaseUtil.strIsNull(tableName)) {
				if (i > 0) {
					tableFilter.append(" AND ");
				} else {
					tableFilter.append(" WHERE ");
				}
				tableFilter.append("table_name LIKE ?");
			} else {
				throw new BaseException("", "");
			}
		}
		return tableFilter.toString();
	}

	/**
	 * 生成需求SQL
	 * 处理：将所有年表联合，生成临时查询SQL
	 * 案例：WITH tableName1 AS (SELECT field1,field2... FROM tableName1_2022 UNION ALL SELECT field1,field2... FROM tableName1_2023),
	 * tableName2 AS (SELECT field3,field4... FROM tableName2_2022 UNION ALL SELECT field3,field4... FROM tableName2_2023)
	 *
	 * @param tableNames  表名数组
	 * @param dtYearTable 表名与年表名集
	 * @return
	 */
	public static String createFinalQuerySQL(Object[] tableNames, DataTableEntity dtYearTable) {
		StringBuilder tableFilter = new StringBuilder();
		JSONObject jsonTableAndYear = convertJson(dtYearTable);
		StringBuilder withSql = new StringBuilder();
		for (int i = 0; i < tableNames.length; i++) {
			String tableName = (String) tableNames[i];
			String tableYear = (String) jsonTableAndYear.get(tableName);
			if (!BaseUtil.strIsNull(tableYear)) {
				if (i == 0) {
					withSql.append(" WITH ");
				}
				withSql.append(tableName);
				withSql.append(" AS(");
				String[] tableYears = tableYear.split(",");
				if (tableYear.length() > 1) {
					for (int j = 0; j < tableYears.length; j++) {
						withSql.append(" SELECT * FROM ");
						withSql.append(tableYears[j]);
						if (j < tableYears.length - 1) {
							withSql.append(" UNION ALL ");
						}
					}
				} else {
					withSql.append(" SELECT * FROM ");
					withSql.append(tableYears[0]);
				}
				withSql.append("),");
			}
		}
		if (tableFilter.toString().endsWith(",")) {
			tableFilter.toString().substring(0, tableFilter.toString().length() - 1);
		}

		return tableFilter.toString();
	}

	/**
	 * DataTableEntity 转 JSONObject
	 * JSONObject<表名,"表名_2022,表名_2023,表名_2024...">
	 *
	 * @param dtYearTable
	 * @return
	 */
	public static JSONObject convertJson(DataTableEntity dtYearTable) {
		JSONObject jsonTableAndYear = new JSONObject();
		for (int i = 0; i < dtYearTable.getRows(); i++) {
			FieldSetEntity fseYearTable = dtYearTable.getFieldSetEntity(i);
			String tableName = fseYearTable.getString(CmnConst.TABLE_NAME);
			String tableYear = fseYearTable.getString("table_year");
			jsonTableAndYear.put(tableName, tableYear);
		}
		return jsonTableAndYear;
	}

	/**
	 * 获取mysql库所有表名，例如，给表名test，获取数据库中所有的test年份分表，test_2019，test_2020...
	 *
	 * @param dao
	 * @param dbName    数据库名称
	 * @param tableName 表名
	 * @return
	 */
	public static Set<String> getAllTableName(Dao dao, String dbName, String tableName) {
		return getAllTableName(dao, null, dbName, tableName);
	}

	public static Set<String> getAllTableName(BaseDao baseDao, String dbName, String tableName) {
		return getAllTableName(null, baseDao, dbName, tableName);
	}

	private static Set<String> getAllTableName(Dao dao, BaseDao baseDao, String dbName, String tableName) {
		if (dao != null && !DataBaseType.MYSQL.equals(dao.getDataBaseType())) {
			throw new BaseException(ErrorCode.NONSUPPORT_DATABASE_TYPE);
		}
		String sql = "select table_name from information_schema.`TABLES` where table_schema=? and table_name like concat(?,'%') order by table_name";
		DataTableEntity dte = new DataTableEntity();
		if (dao != null) {
			dte = dao.getList(sql, new Object[]{dbName, tableName});
		}
		if (baseDao != null) {
			dte = baseDao.listTable(sql, new Object[]{dbName, tableName});
		}
		Set<String> set = Sets.newLinkedHashSet();
		FieldSetEntity fse;
		String curtableName;
		for (int i = 0; i < dte.getRows(); i++) {
			fse = dte.getFieldSetEntity(i);
			curtableName = StringUtils.isEmpty(fse.getString("TABLE_NAME")) ? fse.getString("table_name") : fse.getString("TABLE_NAME");
			if (curtableName.matches(tableName + "_?\\d{4,6}")) {
				set.add(curtableName);
			}
		}
		return set;
	}

	/**
	 * mysql-获取with联合表查询sql
	 *
	 * @param tableAlias        表别名，with之后的那个
	 * @param tableCollection   分表集合，例如年份分表，就是多个年份表的集合
	 * @param fieldCollection   查询字段集合，没有可以直接给null，会默认查询所有字段。特殊：若是要查询当前表名，那么字段名为“{#table_name#}”，之后的别名为“_table_name”
	 *                          也就是说假设字段为 name,{#table_name#} 表名为test_table，那么解析的sql为“select name,'test_table' _table_name from test_table”
	 * @param onlyStructureFlag 只查询结构的标识，若为true，则只查询结构，即为with table_alias as (select * from table_name_1 union all select * from table_name_2 limit 1)
	 * @param filter            过滤条件
	 * @param tableAliasAlias   表别名的别名，当with后存在查询相同的表但是条件不同的时候使用表别名的别名作为with后的别名
	 * @return
	 */
	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection, boolean onlyStructureFlag, String filter, String tableAliasAlias) {
		StringBuilder sql = new StringBuilder(128);
		if (StringUtils.isEmpty(tableAlias) || tableCollection == null || tableCollection.isEmpty()) {
			return sql;
		}
		StringBuilder content = new StringBuilder(128);
		tableCollection.forEach(tableName -> {
			if (content.length() > 0) {
				content.append("\n    union all");
			}
			String fieldContent = "*";
			if (fieldCollection != null && !fieldCollection.isEmpty()) {
				fieldContent = BaseUtil.collection2String(fieldCollection);
				fieldContent = fieldContent.replace("{#table_name#}", "'" + tableName + "' _table_name");
			}
			content.append("\n    select ").append(fieldContent).append(" from ").append(tableName);
			if (!StringUtils.isEmpty(filter) && !StringUtils.isEmpty(filter.trim())) {
				content.append(" ").append(filter.replaceAll("\\s*\\n\\s*", " "));
			}
		});
		sql.append(tableAliasAlias).append(" as (").append(content);
		if (onlyStructureFlag) {
			sql.append(" limit 1");
		}
		sql.append("\n)");
		return sql;
	}

	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection, boolean onlyStructureFlag, String filter) {
		return getUnionTableSql(tableAlias, tableCollection, fieldCollection, onlyStructureFlag, filter, tableAlias);
	}

	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection, boolean onlyStructureFlag) {
		return getUnionTableSql(tableAlias, tableCollection, fieldCollection, onlyStructureFlag, null);
	}

	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection) {
		return getUnionTableSql(tableAlias, tableCollection, fieldCollection, false);
	}

	/**
	 * mysql-获取with联合表查询sql
	 *
	 * @param tableAlias      表别名，with之后的那个
	 * @param tableCollection 分表集合，例如年份分表，就是多个年份表的集合
	 * @return
	 */
	public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection) {
		return getUnionTableSql(tableAlias, tableCollection, null);
	}

	/**
	 * 解析sql中的分表，将其转化为with语句，其符号内的内容作为表别名使用
	 *
	 * @param baseDao
	 * @param sql               待解析的sql
	 * @param year              查询最近的年数，0或者负数表示所有，按照当前为第一年，只查询当年，则给1；查询当前和去年，则给2；查询当年、去年、前年，则给3，以此类推...
	 * @param onlyStructureFlag 只查询结构的标识，若为true，则只查询结构，即为with table_alias as (select * from table_name_1 union all select * from table_name_2 limit 1)
	 * @return
	 */
	public static String parseSplitTableSql(BaseDao baseDao, String sql, int year, boolean onlyStructureFlag) {
		boolean allTableFlag = year <= 0;
		StringBuilder parseSql = new StringBuilder(128);
		String regexp = "\\[=((?!\\[=).)*=\\]";
		List<String> suitList = BaseUtil.getSuitContent(sql, regexp, true);
		if (suitList.isEmpty()) {
			return sql;
		} else {
			Set<String> suitSet = Sets.newLinkedHashSet();
			suitSet.addAll(suitList);

			// 获取需要的年份
			List<String> yearList = Lists.newArrayList();
			if (!allTableFlag) {
				Calendar c = Calendar.getInstance();
				for (int i = 0; i < year; i++) {
					yearList.add(String.valueOf(c.get(Calendar.YEAR)));
					c.add(Calendar.YEAR, -1);
				}
			}
			// 获取对应存在表
			String tableInfo;
			String tableAlias;// 表别名，查询用的前缀，通常with后的别名
			String tableAliasAlias;// 表别名的别名，当with后存在查询相同的表但是条件不同的时候使用表别名的别名作为with后的别名
			Set<String> allTableSet;
			Set<String> needTableSet;
			for (String suitContent : suitSet) {
				Set<String> fieldSet = Sets.newLinkedHashSet();
				String filterInfo = null;
				needTableSet = Sets.newLinkedHashSet();
				tableInfo = suitContent.replace("[=", "").replace("=]", "").trim();
				if (tableInfo.contains("{")) {
					tableAlias = tableInfo.substring(tableInfo.indexOf("{") + 1, tableInfo.indexOf("}"));
					if (tableAlias.contains(" ")) {
						tableAliasAlias = tableAlias.substring(tableAlias.lastIndexOf(" ") + 1);
						tableAlias = tableAlias.substring(0, tableAlias.indexOf(" "));
					} else {
						tableAliasAlias = tableAlias;
					}
					int fromIndex = tableInfo.indexOf("}") + 1;
					if (tableInfo.indexOf("{", fromIndex) > -1) {
						String fieldInfo = tableInfo.substring(tableInfo.indexOf("{", fromIndex) + 1, tableInfo.indexOf("}", fromIndex));
						if (!StringUtils.isEmpty(fieldInfo)) {
							fieldSet.addAll(Arrays.asList(fieldInfo.split(",")));
						}
						fromIndex = tableInfo.indexOf("}", fromIndex) + 1;
						if (tableInfo.indexOf("{", fromIndex) > -1) {
							filterInfo = tableInfo.substring(tableInfo.indexOf("{", fromIndex) + 1, tableInfo.lastIndexOf("}"));
						}
					}
				} else {
					tableAlias = tableInfo;
					tableAliasAlias = tableAlias;
				}
				sql = sql.replace(suitContent, tableAliasAlias);
				allTableSet = getAllTableName(baseDao, baseDao.getDataBaseName(), tableAlias);
				if (allTableFlag) {
					needTableSet = allTableSet;
				} else {
					if (!yearList.isEmpty()) {
						StringBuilder regexpSb = new StringBuilder(64);
						for (String yearStr : yearList) {
							if (regexpSb.length() > 0) {
								regexpSb.append("|");
							}
							regexpSb.append(tableAlias).append("_").append(yearStr).append("\\d{0,2}");
						}
						regexp = regexpSb.toString();
						for (String tableName : allTableSet) {
							if (tableName.matches(regexp)) {
								needTableSet.add(tableName);
							}
						}
					}
				}
				// 获取sql
				StringBuilder contentSb = getUnionTableSql(tableAlias, needTableSet, fieldSet, onlyStructureFlag, filterInfo, tableAliasAlias);
				if (contentSb.length() > 0) {
					if (parseSql.length() > 0) {
						parseSql.append(",");
					}
					parseSql.append(contentSb);
				} else {
					throw new BaseException(SystemCode.NO_MATCH_TABLE.getValue(), SystemCode.NO_MATCH_TABLE.getText() + " table_name:" + tableAlias);
				}
			}
			return new StringBuilder().append("with ").append(parseSql).append("\n").append(sql).toString();
		}
	}

	public static String parseSplitTableSql(BaseDao baseDao, String sql, int year) {
		return parseSplitTableSql(baseDao, sql, year, false);
	}

	public static String parseSplitTableSql(BaseDao baseDao, String sql, boolean onlyStructureFlag) {
		return parseSplitTableSql(baseDao, sql, 0, onlyStructureFlag);
	}

	public static String parseSplitTableSql(BaseDao baseDao, String sql) {
		return parseSplitTableSql(baseDao, sql, 0, false);
	}


}