/**
 * 
 */
package org.springblade.etl.service;

import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.springblade.datasource.support.DynamicDataSourceSupport;
import org.springblade.etl.dto.SqlScriptBrowserResultDto;
import org.springblade.etl.entity.SqlJobScript;
import org.springblade.etl.entity.bi.ReportDatasource;
import org.springblade.etl.mapper.SqlJobScriptMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import java.util.stream.Stream;

/**
 *	SQL任务脚本 服务处理
 *
 * @author aokunsang
 * @date   2023-12-03
 */
@Slf4j
@Service
public class SqlJobScriptService extends ServiceImpl<SqlJobScriptMapper, SqlJobScript>{

	final static Pattern SQL_ANNOTATION_PATTERN = Pattern.compile("(?:/\\*.*?\\*/)|(?:--[^\r\n]*$)", Pattern.CASE_INSENSITIVE | Pattern.MULTILINE);
	
	@Autowired
    private QueryWrapperService queryWrapperService;
	
	/**
	 * 
	 * @param JobId
	 * @return
	 */
	public List<SqlJobScript> listByJobId(Integer jobId){
		return baseMapper.selectByJobId(jobId);
	}
	
	/**
	 * 执行脚本
	 * @param ds
	 * @param sqlScript
	 * @param bgRun  - 是否后台运行
	 */
	public List<SqlScriptBrowserResultDto> runScript(ReportDatasource ds, String sqlScript, boolean bgRun) throws Exception{
		List<String> sqlList = new ArrayList<>();
		Stream.of(sqlScript.split(";|；")).map(String::trim).forEach(sqlList::add);
		
		List<SqlScriptBrowserResultDto> resultList = new ArrayList<>();
		try(Connection connection = DynamicDataSourceSupport.createConnection(ds, true);
				Statement statment = connection.createStatement();){
			//不做事务处理，自动提交事务
//			connection.setAutoCommit(false);
			long startTime = System.currentTimeMillis();
			log.info("======开始执行sql======");
			for(String sql : sqlList) {
				String _sql = StrUtil.removeAll(sql, ';', '；');
				if(StrUtil.isNotBlank(_sql)) {
					//去除空格、换行、sql注释
					String formatSql = StrUtil.removeAllLineBreaks(SQL_ANNOTATION_PATTERN.matcher(_sql).replaceAll("").trim());
					//判断是否 select语句
					boolean isSelect = formatSql.toLowerCase().startsWith("select");
					log.info("executeSql:{},formatSql:{},bgRun:{}", _sql, formatSql, bgRun);
					if(isSelect) { //SQL查询
						if(!bgRun) { //非后台查询，需要查询出来数据返回
							log.info(">>>>>>>开始数据查询<<<<<<<");
							long _startTime = System.currentTimeMillis();
							IPage<Map<String, Object>> dataMap = queryWrapperService.tempSelectPageList(new Page<>(1, 20), ds, formatSql);
							resultList.add(new SqlScriptBrowserResultDto(ds.getId(), formatSql, dataMap));
							log.info(">>>>>>>结束数据查询|cost:{}ms<<<<<<<", (System.currentTimeMillis()-_startTime));
						}
						continue;
					}
					//非查询SQL，执行一下
					statment.execute(_sql);
				}
			}
			log.info("======结束执行sql｜cost:{}ms====", (System.currentTimeMillis()-startTime));
//			connection.commit();
		} 
//		catch (Exception e) {
//			connection.rollback();
//			throw e;
//		}
		finally {  //nothing
//			JdbcUtils.closeConnection(connection);
//			JdbcUtils.closeStatement(statment);
		}
		return resultList;
	}
}