package com.czg.service.system.service.impl;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.czg.service.system.mapper.SqlScriptMapper;
import com.czg.system.entity.SqlScript;
import com.czg.system.service.SqlScriptService;
import com.mybatisflex.spring.service.impl.ServiceImpl;
import jakarta.annotation.PostConstruct;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 服务层实现。
 *
 * @author ww
 * @since 2025-03-27
 */
@Slf4j
@Service
//@Profile({"prod"})
public class SqlScriptServiceImpl extends ServiceImpl<SqlScriptMapper, SqlScript> implements SqlScriptService {


    @Autowired
    private JdbcTemplate jdbcTemplate;

    @PostConstruct
    public void init() {
        // 读取数据库表 sql_script 最后一条 日期
        String lastTradeDay = queryChain().select(SqlScript::getTradeDay).orderBy(SqlScript::getTradeDay).desc().oneAs(String.class);
        if (StrUtil.isBlank(lastTradeDay)) {
            lastTradeDay = "250301";
        }
        // 读取目录 sqls/  其中是以日期为目录的 多个目录  获取 日期 大于 最后一条 日期的所有目录
        File sqlDirectory = new File("sqls");
        if (sqlDirectory.exists() && sqlDirectory.isDirectory()) {
            File[] dateDirectories = sqlDirectory.listFiles();
            if (dateDirectories != null) {
                for (File dateDir : dateDirectories) {
                    if (dateDir.isDirectory()) {
                        String dirDate = dateDir.getName();
                        if (isDateGreater(dirDate, lastTradeDay)) {
                            // 遍历执行 sql 文件
                            executeSqlFilesInDirectory(dateDir);
                        }
                    }
                }
            }
        }
        log.info("初始化执行SQL初始化完成");
    }

    // 判断日期大小
    private boolean isDateGreater(String date1, String date2) {
        return date1.compareTo(date2) > 0;
    }

    // 执行指定目录下的 SQL 文件
    private void executeSqlFilesInDirectory(File directory) {
        File[] sqlFiles = directory.listFiles((dir, name) -> name.endsWith(".sql"));
        if (sqlFiles != null) {
            for (File sqlFile : sqlFiles) {
                try {
                    if (!isSqlExecuted(directory.getName(), sqlFile.getName())) {
                        String sql = readSqlStatementsFromFile(sqlFile);
                        if (StrUtil.isNotBlank(sql)) {
                            executeSql(sql);
                        }
                        markSqlAsExecuted(directory.getName(), sqlFile.getName(), sql);
                    }
                } catch (Exception e) {
                    log.error("执行SQL文件异常:{}", sqlFile.getName());
                }
            }
        }
    }

    // 从文件中读取 SQL 语句
    private String readSqlStatementsFromFile(File file) throws IOException {
        try (BufferedReader reader = new BufferedReader(new FileReader(file))) {
            StringBuilder statement = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null) {
                statement.append(line).append("\n");
            }
            return statement.toString();
        }
    }

    // 判断 SQL 是否已经执行过
    private boolean isSqlExecuted(String date, String sqlFileName) {
        return queryChain().eq(SqlScript::getTradeDay, date).eq(SqlScript::getTableName, sqlFileName).eq(SqlScript::getIsUp, 1).exists();
    }

    // 执行 SQL 语句
    private void executeSql(String sqls) {
        // 这里应该是实际的数据库执行逻辑，暂时打印 SQL 语句
        List<String> sqlList = Arrays.asList(sqls.split(";"));
        for (String sql : sqlList) {
            if (!sql.trim().isEmpty()) {
                jdbcTemplate.execute(sql);
            }
        }
        log.info("初始化执行SQL:{}", sqls);
    }

    // 标记 SQL 为已执行
    private void markSqlAsExecuted(String date, String sqlFileName, String sqls) {
        // 这里应该是实际的数据库更新逻辑，暂时打印标记信息
        SqlScript sqlScript = queryChain().eq(SqlScript::getTradeDay, date).eq(SqlScript::getTableName, sqlFileName).eq(SqlScript::getIsUp, 1).one();
        if (sqlScript == null) {
            sqlScript = new SqlScript();
            sqlScript.setTableName(sqlFileName);
            sqlScript.setIsUp(1);
            sqlScript.setSqls(sqls);
            sqlScript.setTradeDay(DateUtil.format(new Date(), "yyMMdd"));
            save(sqlScript);
        } else {
            updateChain().set(SqlScript::getSqls, sqls).set(SqlScript::getIsUp, 1).eq(SqlScript::getId, sqlScript.getId()).update();
        }
    }
}
