package org.springblade.flow.visual.onlinedev.common.excel;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springblade.bi.client.model.CubeDataModel;
import org.springblade.bi.client.model.DataSourceModel;
import org.springblade.cube.dto.TableLinkJoin;
import org.springblade.cube.dto.TableLinkJoin.JoinKey;
import org.springblade.cube.enums.ViewColumnTypeEnum;
import org.springblade.flow.database.model.DbLinkEntity;
import org.springblade.flow.util.JsonUtil;
import org.springblade.flow.visual.base.entity.VisualdevEntity;
import org.springblade.flow.visual.onlinedev.common.dynamic.DataSourceService;
import org.springblade.flow.visual.onlinedev.common.dynamic.DynamicDataService;
import org.springblade.flow.visual.onlinedev.common.dynamic.TableData;
import org.springblade.flow.visual.onlinedev.common.dynamic.TableFieldData;
import org.springblade.flow.visual.onlinedev.common.dynamic.TableRowData;
import org.springblade.flow.visual.onlinedev.common.excel.ExcelFormParam.ExcelFieldIndex;
import org.springblade.flow.visual.onlinedev.common.formdata.FormDataWithTableService;
import org.springblade.flow.visual.onlinedev.common.relation.RelationBean;
import org.springblade.flow.visual.onlinedev.common.relation.RelationField;
import org.springblade.flow.visual.onlinedev.common.relation.RelationUtil;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.github.xiaoymin.knife4j.core.util.StrUtil;

import cn.hutool.core.collection.CollUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * 多表excel导入
 * 
 * @author william
 * @date 2022年9月15日 下午3:39:36
 */
@Slf4j
public class ExcelRead4MultipleTableListener extends AnalysisEventListener<LinkedHashMap<Integer, String>> {
	private static final int BATCH_COUNT = 10000;

	private long dataTotal;
	private long tableTotal;

	private DataSourceModel ds;
	private DynamicDataService dynamicDataService;

	private List<RelationField> fieldList;
	private List<TableLinkJoin> joinList;
	private List<RelationBean> relationTree;

	private Map<String, String> keyMap = new HashMap<>();// 每个表对应的主键

	private TableData tableData = new TableData();

	public ExcelRead4MultipleTableListener(FormDataWithTableService formDataWithTableService,
			DataSourceService dataSourceService, CubeDataModel cubeData, DynamicDataService dynamicDataService,
			DataSourceModel ds, VisualdevEntity formInfo, List<ExcelFieldIndex> sourceIndexList) throws SQLException {
		this.ds = ds;
		this.dynamicDataService = dynamicDataService;
		this.joinList = JsonUtil.getJsonToList(cubeData.getLinksJoin(), TableLinkJoin.class);

//		// 按照excel索引升序排列
//		List<ExcelFieldIndex> indexList = sourceIndexList.stream()
//				.sorted(Comparator.comparing(ExcelFieldIndex::getIndex)).collect(Collectors.toList());

		// 按照excel索引升序排列
//		this.fieldList = fieldList.stream().sorted(Comparator.comparing(RelationField::getIndex))
//				.collect(Collectors.toList());

		this.fieldList = new ArrayList<>();
		DbLinkEntity linkEntity = dataSourceService.getInfo(formInfo.getDbLinkId());
		String tables = formInfo.getTables();
		List<RelationField> sourceFieldList = JSON.parseArray(tables, RelationField.class);
		for (RelationField rf : sourceFieldList) {
			for (ExcelFieldIndex excel : sourceIndexList) {
				if (rf.getFieldId() == excel.getFieldId()) {
					// 给RelationField实体类对象赋值excel下表索引
					rf.setIndex(excel.getIndex());
					this.fieldList.add(rf);
					continue;
				}
			}

			String tableName = rf.getTableName();
			if (!keyMap.containsKey(tableName)) {
				String key = formDataWithTableService.getKey(ds, rf.getTableName());
				keyMap.put(tableName, key);
			}

		}

		tables = JSON.toJSONString(sourceFieldList);
		this.relationTree = dataSourceService.getRelationTree(tables, formInfo.getDbLinkId());
	}

	/**
	 * 根据excel下表找到对应的RelationField实体类
	 * 
	 * @param excelIndex
	 * @return
	 */
	private RelationField getFieldByIndex(int excelIndex) {
		for (RelationField field : fieldList) {
			if (field.getIndex() == excelIndex) {
				return field;
			}
		}

		return null;
	}

	/**
	 * 生成所有表对应的行数据结构,key是表名，value是行数据
	 * 
	 * @return
	 */
	private Map<String, TableRowData> buildAllTableRow() {
		Map<String, TableRowData> map = new HashMap<>();
		for (RelationField field : fieldList) {
			map.put(field.getTableName(), new TableRowData());
		}
		return map;
	}

	@Override
	public void invoke(LinkedHashMap<Integer, String> row, AnalysisContext context) {
		// key是表名，value是行数据
		Map<String, TableRowData> tableRowMap = buildAllTableRow();
		List<Integer> excelIndexList = new ArrayList<>(row.keySet());
		// 循环组装行原始数据
		excelIndexList.forEach(index -> {
			RelationField rf = getFieldByIndex(index);
			if (rf != null) {
				// 表名
				String table = rf.getTableName();
				// 字段
				String field = rf.getField();
				// 值
				String value = row.get(index);
				// 字段类型
				ViewColumnTypeEnum type = ViewColumnTypeEnum.valueOf(rf.getDataTypeOrigin());

				TableRowData tableRow = tableRowMap.get(table);
				TableFieldData fieldData = new TableFieldData(type, field, value);
				tableRow.add(field, fieldData);
			}
		});

		// 判断每个表的主键，存在主键的话并赋值，并且加入到行数据
		List<String> tableKeys = new ArrayList<>(tableRowMap.keySet());
		tableKeys.forEach(table -> {
			String key = keyMap.get(table);
			if (StrUtil.isNotBlank(key)) {
				// 如果该表有主键
				TableRowData tableRow = tableRowMap.get(table);
				String keyValue = IdWorker.getIdStr();
				TableFieldData keyData = new TableFieldData(ViewColumnTypeEnum.NUMBER, key, keyValue);
				tableRow.add(key, keyData);
			}
		});

		// 判断关联字段，如果存在，则给关联子表字段赋值对应的父表相应字段的值,并加入到行数据
		if (CollUtil.isNotEmpty(joinList)) {
			joinList.forEach(join -> {
				String childTable = join.getTarget();
				List<JoinKey> keyList = join.getJoinKeys();
				keyList.forEach(key -> {
					String childField = key.getTargetKey();
					RelationField parentRelation = RelationUtil.getParentRelationField(childTable, childField, joinList,
							fieldList);
					if (parentRelation != null) {
						// 该字段存在关联字段
						TableRowData childRow = tableRowMap.get(childTable);
						TableFieldData childFieldData = childRow.getData(childField);
						// 取父表对应字段的值，并且赋值给该表字段
						TableRowData parentRow = tableRowMap.get(parentRelation.getTableName());
						String parentField = parentRelation.getField();
						String parentValue = parentRow.get(parentField).getValue();
						childFieldData.setValue(parentValue);
						childRow.add(childField, childFieldData);
					}
				});
			});
		}

		// 每个表的行数据组装完成
		// 循环放入每个表的行数据
		tableKeys.forEach(table -> {
			TableRowData tableRow = tableRowMap.get(table);
			tableData.add(table, tableRow);
		});

		if (tableData.size() >= BATCH_COUNT) {
			saveData();
			tableData.clear();
		}
	}

	@Override
	public void doAfterAllAnalysed(AnalysisContext context) {
		saveData();
		log.info("所有数据解析完成！");
	}

	/**
	 * 将数据存储数据库
	 */
	private void saveData() {
		recursionInsert(relationTree);
		tableTotal += tableData.getTableSize();
		dataTotal += tableData.getDataSize();
		log.info("存储数据库成功！");
	}

	/**
	 * 递归从主表到次表，依次批量插入数据
	 * 
	 * @param tree 表关联关系的树形结构，从主表到次表
	 */
	private void recursionInsert(List<RelationBean> tree) {
		for (RelationBean relation : tree) {
			String tableName = relation.getTable();
			List<TableRowData> rowList = tableData.getRowList(tableName);
			if (CollUtil.isEmpty(rowList)) {
				continue;
			}

			TableRowData first = rowList.get(0);
			List<String> fieldSet = new ArrayList<>(first.keySet());

			if (CollUtil.isEmpty(fieldSet)) {
				continue;
			}

			// insert into t1(field1,field2) values(?,?);
			StringBuffer fieldBuffer = new StringBuffer();
			fieldBuffer.append("insert into " + tableName + "(");

			StringBuffer valueBuffer = new StringBuffer();
			valueBuffer.append(" values(");

			LinkedHashMap<String, ViewColumnTypeEnum> headerMap = new LinkedHashMap<>();

			for (int i = 0, size = fieldSet.size(); i < size; i++) {
				String field = fieldSet.get(i);
				fieldBuffer.append(field);
				valueBuffer.append("?");
				if (i < size - 1) {
					fieldBuffer.append(",");
					valueBuffer.append(",");
				}
				ViewColumnTypeEnum type = first.get(field).getType();
				headerMap.put(field, type);
			}

			fieldBuffer.append(")");
			valueBuffer.append(")");

			String insertSql = fieldBuffer.toString() + valueBuffer.toString();

			dynamicDataService.batchSaveForMultipleTable(ds, insertSql, BATCH_COUNT, headerMap, rowList);

			if (CollUtil.isNotEmpty(relation.getChildren())) {
				// 插入子表数据
				recursionInsert(relation.getChildren());
			}
		}

//		log.info("insertSql is ->" + insertSql);
//		log.info("headerMap is ->" + JSON.toJSONString(header));
	}

	public long getDataTotal() {
		return dataTotal;
	}

	public long getTableTotal() {
		return tableTotal;
	}
}
