package com.awspaas.user.apps.scienceriskrecord.controller.riskTaking;

import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.sql.*;

/**
 * MySQL 表结构 + 数据导出工具
 * 生成标准可执行的SQL脚本
 */
public class MySQLTableExporter {
    // MySQL 8.0+ 驱动（旧版5.x可改回 com.mysql.jdbc.Driver）
    private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    // 数据库连接地址
    private static final String DB_URL = "jdbc:mysql://localhost:3306/as?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true";
    private static final String USER = "root";
    private static final String PASSWORD = "as@&d1";
    // 导出文件路径（UTF-8编码，无乱码）
    private static final String EXPORT_FILE_PATH = "D:\\mysql_export.sql";

    public static void main(String[] args) {
        System.out.println("============= 开始导出MySQL表结构和数据 ==============");
        // 要导出的表名（可修改）
        String tableName = "bwhitelist11";

        try (
                // 1. 注册驱动+建立连接
                Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
                // 2. 自动关闭的文件写入流（UTF-8编码）
                BufferedWriter writer = new BufferedWriter(
                        new OutputStreamWriter(new FileOutputStream(EXPORT_FILE_PATH), StandardCharsets.UTF_8)
                )
        ) {
            // 导出 建表语句
            exportCreateTable(conn, tableName, writer);
            writer.write("\n\n");
            // 导出 数据插入语句
            exportInsertData(conn, tableName, writer);

            System.out.println("导出成功！文件路径：" + EXPORT_FILE_PATH);
        } catch (Exception e) {
            System.err.println("导出失败！");
            e.printStackTrace();
        }
    }

    /**
     * 导出建表语句（带 IF NOT EXISTS）
     */
    private static void exportCreateTable(Connection conn, String tableName, BufferedWriter writer) throws SQLException, java.io.IOException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SHOW CREATE TABLE " + tableName)) {

            if (rs.next()) {
                // 获取原始建表语句，并添加 IF NOT EXISTS 防止重复创建报错
                String createSql = rs.getString(2).replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS");
                writer.write("-- 表 " + tableName + " 建表语句");
                writer.newLine();
                writer.write(createSql);
                writer.write(";");
                writer.newLine();
            }
        }
    }

    /**
     * 导出数据INSERT语句（处理空值、单引号、字段名）
     */
    private static void exportInsertData(Connection conn, String tableName, BufferedWriter writer) throws SQLException, java.io.IOException {
        String querySql = "SELECT * FROM " + tableName;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(querySql)) {

            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();

            writer.write("-- 表 " + tableName + " 数据");
            writer.newLine();

            // 遍历数据行
            while (rs.next()) {
                StringBuilder columns = new StringBuilder();
                StringBuilder values = new StringBuilder();

                for (int i = 1; i <= columnCount; i++) {
                    // 字段名
                    columns.append(meta.getColumnName(i));
                    // 字段值（处理空值、转义单引号）
                    Object value = rs.getObject(i);
                    if (rs.wasNull()) {
                        values.append("NULL");
                    } else {
                        // 字符串转义单引号：' 替换为 ''
                        String valStr = value.toString().replace("'", "''");
                        values.append("'").append(valStr).append("'");
                    }

                    // 拼接分隔符
                    if (i < columnCount) {
                        columns.append(", ");
                        values.append(", ");
                    }
                }

                // 拼接完整INSERT语句
                String insertSql = String.format("INSERT INTO %s (%s) VALUES (%s);",
                        tableName, columns, values);
                writer.write(insertSql);
                writer.newLine();
            }
        }
    }
}