package com.lesingle.edu.util;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库迁移工具类
 */
@Slf4j
@Component
public class DatabaseMigrationUtil {

    private static final String URL = "jdbc:mysql://8.148.151.56:3306/reading_platform?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "reading_platform_pwd";

    /**
     * 执行 SQL 脚本
     */
    public static void executeMigration(String[] sqlStatements) {
        Connection conn = null;
        Statement stmt = null;

        try {
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 连接数据库
            log.info("连接到数据库: {}", URL);
            conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            stmt = conn.createStatement();

            // 执行 SQL 语句
            for (int i = 0; i < sqlStatements.length; i++) {
                String sql = sqlStatements[i].trim();
                if (sql.isEmpty() || sql.startsWith("--")) {
                    continue;
                }

                try {
                    log.info("执行 SQL [{}/{}]: {}", i + 1, sqlStatements.length, sql.substring(0, Math.min(sql.length(), 100)));
                    stmt.execute(sql);
                    log.info("✓ SQL 执行成功");
                } catch (Exception e) {
                    if (e.getMessage() != null && e.getMessage().contains("already exists")) {
                        log.warn("⚠ 表已存在，跳过: {}", e.getMessage());
                    } else {
                        log.error("✗ SQL 执行失败: {}", e.getMessage());
                    }
                }
            }

            log.info("数据库迁移完成！");

        } catch (Exception e) {
            log.error("数据库迁移失败: {}", e.getMessage(), e);
        } finally {
            try {
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                log.error("关闭连接失败: {}", e.getMessage());
            }
        }
    }

    /**
     * 解析 SQL 脚本文件
     */
    public static String[] parseSqlScript(String script) {
        List<String> statements = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        String[] lines = script.split("\n");

        for (String line : lines) {
            line = line.trim();

            // 跳过注释和空行
            if (line.isEmpty() || line.startsWith("--")) {
                continue;
            }

            sb.append(line).append(" ");

            // 检查是否是语句结束
            if (line.endsWith(";")) {
                String sql = sb.toString();
                statements.add(sql.substring(0, sql.length() - 1).trim()); // 移除分号
                sb = new StringBuilder();
            }
        }

        // 添加最后一个语句
        if (sb.length() > 0) {
            String sql = sb.toString().trim();
            if (sql.endsWith(";")) {
                sql = sql.substring(0, sql.length() - 1).trim();
            }
            if (!sql.isEmpty()) {
                statements.add(sql);
            }
        }

        return statements.toArray(new String[0]);
    }
}
