package com.oying.modules.maint.util;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.util.StringUtils;
import com.oying.modules.maint.domain.enums.DataTypeEnum;
import lombok.extern.slf4j.Slf4j;
import com.oying.utils.CloseUtil;
import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.File;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Z
 */
@Slf4j
public class SqlUtils {

	/**
	 * 获取数据源
	 *
	 * @param jdbcUrl /
	 * @param userName /
	 * @param password /
	 * @return DataSource
	 */
	private static DataSource getDataSource(String jdbcUrl, String userName, String password) {
		DruidDataSource druidDataSource = new DruidDataSource();
		String className;
		try {
			className = DriverManager.getDriver(jdbcUrl.trim()).getClass().getName();
		} catch (SQLException e) {
			throw new RuntimeException("Get class name error: =" + jdbcUrl);
		}
		if (StringUtils.isEmpty(className)) {
			DataTypeEnum dataTypeEnum = DataTypeEnum.urlOf(jdbcUrl);
			if (null == dataTypeEnum) {
				throw new RuntimeException("Not supported data type: jdbcUrl=" + jdbcUrl);
			}
			druidDataSource.setDriverClassName(dataTypeEnum.getDriver());
		} else {
			druidDataSource.setDriverClassName(className);
		}

		// 去掉不安全的参数
		jdbcUrl = sanitizeJdbcUrl(jdbcUrl);

		druidDataSource.setUrl(jdbcUrl);
		druidDataSource.setUsername(userName);
		druidDataSource.setPassword(password);
		// 配置获取连接等待超时的时间
		druidDataSource.setMaxWait(3000);
		// 配置初始化大小、最小、最大
		druidDataSource.setInitialSize(1);
		druidDataSource.setMinIdle(1);
		druidDataSource.setMaxActive(1);

		// 如果链接出现异常则直接判定为失败而不是一直重试
		druidDataSource.setBreakAfterAcquireFailure(true);
		try {
			druidDataSource.init();
		} catch (SQLException e) {
			log.error("Exception during pool initialization", e);
			throw new RuntimeException(e.getMessage());
		}

		return druidDataSource;
	}

	private static Connection getConnection(String jdbcUrl, String userName, String password) {
		DataSource dataSource = getDataSource(jdbcUrl, userName, password);
		Connection connection = null;
		try {
			connection = dataSource.getConnection();
		} catch (Exception ignored) {}
		try {
			int timeOut = 5;
			if (null == connection || connection.isClosed() || !connection.isValid(timeOut)) {
				log.info("connection is closed or invalid, retry get connection!");
				connection = dataSource.getConnection();
			}
		} catch (Exception e) {
			log.error("create connection error, jdbcUrl: {}", jdbcUrl);
			throw new RuntimeException("create connection error, jdbcUrl: " + jdbcUrl);
		} finally {
			CloseUtil.close(connection);
		}
		return connection;
	}

	private static void releaseConnection(Connection connection) {
		if (null != connection) {
			try {
				connection.close();
			} catch (Exception e) {
				log.error(e.getMessage(),e);
			}
		}
	}

	public static boolean testConnection(String jdbcUrl, String userName, String password) {
		Connection connection = null;
		try {
			connection = getConnection(jdbcUrl, userName, password);
			if (null != connection) {
				return true;
			}
		} catch (Exception e) {
            log.error("Get connection failed:{}", e.getMessage());
		} finally {
			releaseConnection(connection);
		}
		return false;
	}

	public static String executeFile(String jdbcUrl, String userName, String password, File sqlFile) {
		Connection connection = getConnection(jdbcUrl, userName, password);
		try {
			batchExecute(connection, readSqlList(sqlFile));
		} catch (Exception e) {
			log.error("sql脚本执行发生异常:{}",e.getMessage());
			return e.getMessage();
		}finally {
			releaseConnection(connection);
		}
		return "success";
	}

	/**
	 * 批量执行sql
	 * @param connection /
	 * @param sqlList /
	 */
	public static void batchExecute(Connection connection, List<String> sqlList) {
		try (Statement st = connection.createStatement()) {
			for (String sql : sqlList) {
				// 去除末尾的分号
				if (sql.endsWith(";")) {
					sql = sql.substring(0, sql.length() - 1);
				}
				// 检查 SQL 语句是否为空
				if (!sql.trim().isEmpty()) {
					st.addBatch(sql);
				}
			}
			st.executeBatch();
		} catch (SQLException e) {
			log.error("SQL脚本批量执行发生异常: {}，错误代码: {}", e.getMessage(), e.getErrorCode());
		}
	}

	/**
	 * 将文件中的sql语句以；为单位读取到列表中
	 * @param sqlFile /
	 * @return /
     */
	private static List<String> readSqlList(File sqlFile) {
		List<String> sqlList = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		try (BufferedReader reader = Files.newBufferedReader(sqlFile.toPath(), StandardCharsets.UTF_8)) {
			String line;
			while ((line = reader.readLine()) != null) {
				log.info("line: {}", line);
				sb.append(line.trim());

				if (line.trim().endsWith(";")) {
					sqlList.add(sb.toString());
					// 清空 StringBuilder
					sb.setLength(0);
				} else {
					// 在行之间加一个空格
					sb.append(" ");
				}
			}
			if (sb.length() > 0) {
				sqlList.add(sb.toString().trim());
			}
		} catch (Exception e) {
			log.error("读取SQL文件时发生异常: {}", e.getMessage());
		}
		return sqlList;
	}

	/**
	 * 去除不安全的参数
	 * @param jdbcUrl /
	 * @return /
	 */
	private static String sanitizeJdbcUrl(String jdbcUrl) {
		// 定义不安全参数和其安全替代值
		String[][] unsafeParams = {
				// allowLoadLocalInfile：允许使用 LOAD DATA LOCAL INFILE，可能导致文件泄露
				{"allowLoadLocalInfile", "false"},
				// allowUrlInLocalInfile：允许在 LOAD DATA LOCAL INFILE 中使用 URL，可能导致未经授权的文件访问
				{"allowUrlInLocalInfile", "false"},
				// autoDeserialize：允许自动反序列化对象，可能导致反序列化漏洞
				{"autoDeserialize", "false"},
				// allowNanAndInf：允许使用 NaN 和 Infinity 作为数字值，可能导致不一致的数据处理
				{"allowNanAndInf", "false"},
				// allowMultiQueries：允许在一个语句中执行多个查询，可能导致 SQL 注入攻击
				{"allowMultiQueries", "false"},
				// allowPublicKeyRetrieval：允许从服务器检索公钥，可能导致中间人攻击
				{"allowPublicKeyRetrieval", "false"}
		};

		// 替换不安全的参数
		for (String[] param : unsafeParams) {
			jdbcUrl = jdbcUrl.replaceAll("(?i)" + param[0] + "=true", param[0] + "=" + param[1]);
		}
		return jdbcUrl;
	}
}