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

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map.Entry;
import java.util.Properties;

import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.springblade.bi.client.DynamicDataSourceClient;
import org.springblade.bi.client.form.BaseDsForm;
import org.springblade.bi.client.form.PageDsForm;
import org.springblade.bi.client.model.DataSourceModel;
import org.springblade.bi.client.model.PageModel;
import org.springblade.cube.enums.ViewColumnTypeEnum;
import org.springblade.cube.utils.CubeConstants;
import org.springblade.flow.visual.onlinedev.common.enums.DsType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.druid.util.JdbcUtils;
import com.alibaba.fastjson2.JSONObject;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.metadata.IPage;

import lombok.extern.slf4j.Slf4j;

/**
 * 动态创建表和插入数据
 * 
 * @author william
 * @date 2022年9月16日 下午4:22:58
 */
@Slf4j
@Service
public class DynamicDataService {
	@Autowired
	private DynamicDataSourceClient dynamicDataClient;

	/**
	 * 执行SQL
	 * @param ds
	 * @param sql
	 * @throws Exception 
	 */
	@DS("#ds.poolName")
	public void executeSql(DataSourceModel ds, String sql) {
		dynamicDataClient.execute(buildBaseDsForm(ds, sql));
	}

	private BaseDsForm buildBaseDsForm(DataSourceModel ds, String sql) {
		BaseDsForm form = new BaseDsForm();
		form.setPoolName(ds.getPoolName());
		form.setSql(sql);
		return form;
	}

	/**
	 * 
	 * @param ds
	 * @param sql
	 * @return
	 */
	@DS("#ds.poolName")
	public List<JSONObject> selectList(DataSourceModel ds, String sql) {
		return dynamicDataClient.list(buildBaseDsForm(ds, sql));
	}

	/**
	 * 
	 * @param ds
	 * @param sql
	 * @return
	 */
	@DS("#ds.poolName")
	public JSONObject selectOne(DataSourceModel ds, String sql) {
		return dynamicDataClient.getOne(buildBaseDsForm(ds, sql));
	}

	/**
	 * 
	 * @param page
	 * @param sql
	 * @return
	 */
	@DS("#ds.poolName")
	public PageModel<JSONObject> selectPage(IPage<?> page, DataSourceModel ds, String sql) {
		PageDsForm form = new PageDsForm();
		form.setPoolName(ds.getPoolName());
		form.setSql(sql);
		form.setSize(page.getSize());
		form.setCurrent(page.getCurrent());
		return dynamicDataClient.page(form);
	}

	/**
	 * 创建连接
	 * @param ds
	 * @return
	 * @throws SQLException
	 */
	public Connection createConnection(DataSourceModel ds) throws Exception {
		Properties props = new Properties();
		props.put("user", ds.getDsUser());
		props.put("password", ds.getDsPassword());
		// 用于显示字段注释
		props.put("remarks", "true");
//		props.put("useInformationSchema", "true");
		props.put("remarksReporting", "true");
		if (ds.getDsType().intValue() == DsType.Oracle.getValue()) {
			props.put("oracle.net.CONNECT_TIMEOUT", "1000");
		} else if (ds.getDsType().intValue() == DsType.MySQL.getValue()
				|| ds.getDsType().intValue() == DsType.Clickhouse.getValue()) {
			props.put("connectTimeout", "1000");
			props.put("nullCatalogMeansCurrent", "true");
		} else if (ds.getDsType().intValue() == DsType.Postgresql.getValue()) {
			props.put("connectTimeout", "1");
		} else if (ds.getDsType().intValue() == DsType.SqlServer.getValue()) {
			props.put("loginTimeout", "1");
		}
		return DriverManager.getConnection(ds.getUrl(), props);
	}

	/**
	 * 批量入库
	 * @param ds
	 * @throws Exception
	 */
	public void batchSave(DataSourceModel ds, String insertSql, int batchSize,
			LinkedHashMap<Integer, ViewColumnTypeEnum> headerMap, List<LinkedHashMap<Integer, String>> dataList) {
		PreparedStatement psStatement = null;
		Connection connection = null;
		try {
			connection = createConnection(ds);// dynamicRoutingDataSource.getConnection();
			psStatement = connection.prepareStatement(insertSql);
			connection.setAutoCommit(false);
			long startTime = System.currentTimeMillis();
			int size = dataList.size();
			int i = 1;
			for (LinkedHashMap<Integer, String> fileData : dataList) {
				// sql占位符索引，从1开始
				int sortOfSql = 1;
				for (Entry<Integer, String> entry : fileData.entrySet()) {
					// excel下标索引，从0开始
					Integer indexKey = entry.getKey();
					String value = fileData.get(indexKey);
					ViewColumnTypeEnum columnType = headerMap.get(indexKey);

					if (columnType == ViewColumnTypeEnum.DATETIME) {
						java.sql.Timestamp dateValue = null;
						try {
							Date date = DateUtils.parseDate(value, Locale.getDefault(),
									CubeConstants.DATE_FORMAT_PATTERNS);
							dateValue = new Timestamp(date.getTime());
						} catch (Exception e) {
						}
						psStatement.setTimestamp(sortOfSql, dateValue);
					} else if (columnType == ViewColumnTypeEnum.NUMBER) {
						try {
							value = value.replaceAll(CubeConstants.NUMBER_FORAMT_PATTERNS, "");
						} catch (Exception e) {
						}
						psStatement.setDouble(sortOfSql, NumberUtils.toDouble(value, 0D));
					} else {
						psStatement.setString(sortOfSql, value);
					}

					sortOfSql += 1;
				}

				psStatement.addBatch();
				// 达到批量入库的条数,开始入库操作
				if (i % batchSize == 0 || i == size) {
					log.info("数据建批完成|dataSize:{}, cost:{}ms", dataList.size(), System.currentTimeMillis() - startTime);
					psStatement.executeBatch();
					connection.commit();
					psStatement.clearBatch();
				}
				i++;
			}
			long endTime = System.currentTimeMillis();
			log.info("批量入库成功|dataSize:{}, cost:{}ms", dataList.size(), endTime - startTime);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcUtils.close(psStatement);
			JdbcUtils.close(connection);
		}
	}

	/**
	 * 针对多表的批量导入
	 * 
	 * @param ds
	 * @param insertSql
	 * @param batchSize
	 * @param headerMap
	 * @param rowList
	 */
	public void batchSaveForMultipleTable(DataSourceModel ds, String insertSql, int batchSize,
			LinkedHashMap<String, ViewColumnTypeEnum> headerMap, List<TableRowData> rowList) {
		PreparedStatement psStatement = null;
		Connection connection = null;
		try {
			connection = createConnection(ds);// dynamicRoutingDataSource.getConnection();
			psStatement = connection.prepareStatement(insertSql);
			connection.setAutoCommit(false);
			long startTime = System.currentTimeMillis();
			int size = rowList.size();
			int i = 1;
			for (TableRowData row : rowList) {
				// sql占位符索引，从1开始
				int sortOfSql = 1;
				for (Entry<String, ViewColumnTypeEnum> entry : headerMap.entrySet()) {
					// excel下标索引，从0开始
					String field = entry.getKey();
					String value = row.getData(field).getValue();
					ViewColumnTypeEnum columnType = headerMap.get(field);

					if (columnType == ViewColumnTypeEnum.DATETIME) {
						java.sql.Timestamp dateValue = null;
						try {
							Date date = DateUtils.parseDate(value, Locale.getDefault(),
									CubeConstants.DATE_FORMAT_PATTERNS);
							dateValue = new Timestamp(date.getTime());
						} catch (Exception e) {
						}
						psStatement.setTimestamp(sortOfSql, dateValue);
					} else if (columnType == ViewColumnTypeEnum.NUMBER) {
						try {
							value = value.replaceAll(CubeConstants.NUMBER_FORAMT_PATTERNS, "");
						} catch (Exception e) {
						}
						psStatement.setDouble(sortOfSql, NumberUtils.toDouble(value, 0D));
					} else {
						psStatement.setString(sortOfSql, value);
					}

					sortOfSql += 1;
				}

				psStatement.addBatch();
				// 达到批量入库的条数,开始入库操作
				if (i % batchSize == 0 || i == size) {
					log.info("多表数据建批完成|dataSize:{}, cost:{}ms", rowList.size(), System.currentTimeMillis() - startTime);
					psStatement.executeBatch();
					connection.commit();
					psStatement.clearBatch();
				}
				i++;
			}
			long endTime = System.currentTimeMillis();
			log.info("多表批量入库成功|dataSize:{}, cost:{}ms", rowList.size(), endTime - startTime);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			JdbcUtils.close(psStatement);
			JdbcUtils.close(connection);
		}
	}

}
