package com.citygis.web.service.impl;

import cn.hutool.core.lang.Snowflake;
import cn.hutool.core.util.IdUtil;
import com.alibaba.druid.stat.DruidStatService;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.citygis.web.domain.TabSqlExecute;
import com.citygis.web.mapper.TabSqlExecuteMapper;
import com.citygis.web.service.ITabSqlExecuteService;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author citygis
 * @since 2025-01-08
 */
@Service
public class TabSqlExecuteServiceImpl extends ServiceImpl<TabSqlExecuteMapper, TabSqlExecute> implements ITabSqlExecuteService {

    private DruidStatService druidStatService = DruidStatService.getInstance();

    @Resource
    TabSqlExecuteMapper tabSqlExecuteMapper;

    @Override
    @Transactional
    @Scheduled(cron = "*/5 * * * * *")
    public void executeSqlList() {
        String sqlJson = druidStatService.service("/sql.json?orderBy=SQL&orderType=desc&page=1&perPageCount=1000000&");
        JSONArray content = JSONArray.parseArray(JSONObject.parseObject(sqlJson).get("Content").toString());
        for (Object o : content) {
            TabSqlExecute tabSqlExecute = new TabSqlExecute();
            Snowflake snowflake = IdUtil.getSnowflake(1, 1);
            long id = snowflake.nextId();

            JSONObject jsonObject = JSONObject.parseObject(o.toString());
            Integer sqlId = Integer.valueOf(jsonObject.get("ID").toString());

            String dbType = jsonObject.get("DbType").toString();

            //执行的sql
            String sql = jsonObject.get("SQL").toString();

            //匹配sql类型
            String sqlType = null;
            if (sql.toUpperCase().startsWith("SELECT")) {
                sqlType = "SELECT";
            } else if (sql.toUpperCase().startsWith("UPDATE")) {
                sqlType = "UPDATE";
            } else if (sql.toUpperCase().startsWith("INSERT")) {
                sqlType = "INSERT";
            } else if (sql.toUpperCase().startsWith("DELETE")) {
                sqlType = "DELETE";
            } else if (sql.toUpperCase().startsWith("CREATE")) {
                sqlType = "CREATE";
            } else if (sql.toUpperCase().startsWith("DROP")) {
                sqlType = "DROP";
            }

            //执行次数
            String executeCount = jsonObject.get("ExecuteCount").toString();

            //执行时间
            String totalTime = jsonObject.get("TotalTime").toString();

            //执行最慢
            String maxTimespan = jsonObject.get("MaxTimespan").toString();

            //事务执行
            String inTransactionCount = jsonObject.get("InTransactionCount").toString();

            //错误数
            String errorCount = jsonObject.get("ErrorCount").toString();

            //更新行数
            String effectedRowCount = jsonObject.get("EffectedRowCount").toString();

            //读取行数
            String fetchRowCount = jsonObject.get("FetchRowCount").toString();

            //执行中
            String runningCount = jsonObject.get("RunningCount").toString();

            //最大并发数
            String concurrentMax = jsonObject.get("ConcurrentMax").toString();
            tabSqlExecute.setId(String.valueOf(id));
            tabSqlExecute.setDbtype(dbType);
            tabSqlExecute.setSql(sql);

            //获取表名
            String tableName = extractTableName(sql);

            //分离模式名和表名
            String[] split = tableName.split("\\.");

            if (split.length > 1) {
                tabSqlExecute.setSchemaname(split[0]);
                tabSqlExecute.setTablename(split[1]);
            } else {
                tabSqlExecute.setTablename(tableName);
            }

            tabSqlExecute.setSqlid(sqlId);
            tabSqlExecute.setExecutecount(executeCount);
            tabSqlExecute.setTotaltime(totalTime);
            tabSqlExecute.setMaxtimespan(maxTimespan);
            tabSqlExecute.setIntransactioncount(inTransactionCount);
            tabSqlExecute.setErrorcount(errorCount);
            tabSqlExecute.setErrorcount(effectedRowCount);
            tabSqlExecute.setFetchrowcount(fetchRowCount);
            tabSqlExecute.setRunningcount(runningCount);
            tabSqlExecute.setConcurrentmax(concurrentMax);
            tabSqlExecute.setSqltype(sqlType);

            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String formatDate = simpleDateFormat.format(new Date());
            tabSqlExecute.setCreateTime(formatDate);

            LambdaQueryWrapper<TabSqlExecute> queryWrapper = new LambdaQueryWrapper<>();
            queryWrapper.eq(TabSqlExecute::getSql, tabSqlExecute.getSql());

            TabSqlExecute one = this.getOne(queryWrapper);

            // 不存储自身
            if (!"TAB_SQL_EXECUTE".equals(tabSqlExecute.getTablename())) {
                if (one == null) {
                    this.save(tabSqlExecute);
                } else {
                    this.updateById(tabSqlExecute);
                }
            }
        }
    }

    @Override
    public List<TabSqlExecute> getSqlResult(TabSqlExecute tabSqlExecute) {
        return tabSqlExecuteMapper.getTabSqlExecuteList(tabSqlExecute);
    }

    /**
     * 使用正则匹配不同的sql中的表名
     *
     * @param sql
     * @return
     */
    public static String extractTableName(String sql) {
        String upperCase = sql.toUpperCase().replaceAll("\"", "");

        Pattern pattern = null;
        Matcher matcher = null;

        // 匹配 SELECT 语句中的表名
        pattern = Pattern.compile("(?i)\\bFROM\\s+([\\w.]+)");
        matcher = pattern.matcher(upperCase);
        if (matcher.find()) {
            return matcher.group(1);
        }

        // 匹配 INSERT 语句中的表名
        pattern = Pattern.compile("(?i)\\bINTO\\s+([\\w.]+)");
        matcher = pattern.matcher(upperCase);
        if (matcher.find()) {
            return matcher.group(1);
        }

        // 匹配 UPDATE 语句中的表名
        pattern = Pattern.compile("(?i)\\bUPDATE\\s+([\\w.]+)");
        matcher = pattern.matcher(upperCase);
        if (matcher.find()) {
            return matcher.group(1);
        }

        // 匹配 DELETE 语句中的表名
        pattern = Pattern.compile("(?i)\\bDELETE\\s+FROM\\s+([\\w.]+)");
        matcher = pattern.matcher(upperCase);
        if (matcher.find()) {
            return matcher.group(1);
        }

        // 匹配 CREATE 语句中的表名
        pattern = Pattern.compile("(?i)\\bCREATE\\s+TABLE\\s+([\\w.]+)");
        matcher = pattern.matcher(upperCase);
        if (matcher.find()) {
            return matcher.group(1);
        }

        // 匹配 DROP 语句中的表名
        pattern = Pattern.compile("(?i)\\bDROP\\s+TABLE\\s+([\\w.]+)");
        matcher = pattern.matcher(upperCase);
        if (matcher.find()) {
            return matcher.group(1);
        }
        return null;
    }
}
