package org.jeecg.modules.iot.depository;

import com.baomidou.dynamic.datasource.annotation.DS;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.List;

/**
 * @author cuikaidong
 * @date 2025/5/6
 */
@Service
public class MysqlDataWriter {
    // 创建MysqlTable表
    public static void createMysqlTable(String tableName, String... columns) {
        if (DbConfig.DATABASE.equals("sqlserver")) {
            // 调用mysql创建表方法
            createSqlserverTable(tableName, columns);
        } else if (DbConfig.DATABASE.equals("mysql")) {
            String sql = String.format("CREATE TABLE IF NOT EXISTS %s (%s)", tableName, String.join(", ", columns));

            try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
                 Statement stmt = conn.createStatement()) {
                stmt.executeUpdate(sql);
                System.out.println("表创建成功: " + tableName);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    // 写入数据
    public static void insertMysqlData(String tableName, String[] columns, Object[] values) {
        if (columns.length != values.length) {
            throw new IllegalArgumentException("字段与值的数量不匹配");
        }
        String placeholders = String.join(", ", java.util.Collections.nCopies(columns.length, "?"));
        String columnNames = String.join(", ", columns);
        String sql = String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, columnNames, placeholders);

        try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            for (int i = 0; i < values.length; i++) {
                pstmt.setObject(i + 1, values[i]);
            }
            pstmt.executeUpdate();
//            System.out.println("数据写入成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 增加字段  在  类中添加以下方法
    public static void addMysqlColumn(String tableName, String columnDefinition, String columnName) {
        if (isColumnExists(tableName, columnName)) {
            System.out.println("字段已存在，无需添加: " + columnName);
            return;
        }
        String sql = String.format("ALTER TABLE %s ADD COLUMN %s", tableName, columnDefinition);
        try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            System.out.println("字段添加成功: " + columnDefinition);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 辅助方法：检查表中是否已存在指定字段
    private static boolean isColumnExists(String tableName, String columnName) {
        String sql = String.format(
                "SELECT COUNT(*) FROM information_schema.columns " +
                        "WHERE table_name = '%s' AND column_name = '%s'",
                tableName, columnName
        );

        try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    // 批量导入字段
    public static void addMysqlColumns(String tableName, List<String> columnDefinitions, List<String> columnNames) {
        if (columnDefinitions.size() != columnNames.size()) {
            throw new IllegalArgumentException("字段定义和字段名的数量必须一致");
        }

        StringBuilder sqlBuilder = new StringBuilder("ALTER TABLE ").append(tableName).append(" ADD COLUMN ( ");
        for (int i = 0; i < columnDefinitions.size(); i++) {
            String columnName = columnNames.get(i);
            if (batchIsColumnExists(tableName, columnName)) {
                System.out.println("字段已存在，无需添加: " + columnName);
                continue;
            }
            String columnDefinition = columnDefinitions.get(i);
            if (i > 0) {
                sqlBuilder.append(", ");
            }
            sqlBuilder.append(columnDefinition);
        }
        sqlBuilder.append(" ) ");
        String sql = sqlBuilder.toString();
        if (sql.endsWith(" ADD COLUMN ( )")) {
            System.out.println("没有需要添加的新字段");
            return;
        }
        try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            System.out.println("字段批量添加成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 验证字段是否重复
    private static boolean batchIsColumnExists(String tableName, String columnName) {
        String sql = String.format(
                "SELECT COUNT(*) FROM information_schema.columns " +
                        "WHERE TABLE_SCHEMA = 'lx_iot' AND table_name = '%s' AND column_name = '%s'",
                tableName, columnName
        );

        try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


    /************************************************************ sqlserver版本 ***************************************/
    // 创建sqlserver表
    public static void createSqlserverTable(String tableName, String... columns) {
        String schemaName = "dbo";
        // 创建表的 SQL（如果不存在）
        String tableSql = String.format(
                "IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = '%s' AND schema_id = SCHEMA_ID('%s')) " +
                        "CREATE TABLE [%s].[%s] (%s)",
                tableName, schemaName, schemaName, tableName, String.join(", ", columns)
        );
//        String sql = String.format("CREATE TABLE IF NOT EXISTS %s (%s)", tableName, String.join(", ", columns));
        try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(tableSql);
            System.out.println("表创建成功: " + tableName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 批量导入字段（SQL Server 版本）
    public static void addSqlserverColumns(String schemaName, String tableName, List<String> columnDefinitions, List<String> columnNames) {
        if (columnDefinitions.size() != columnNames.size()) {
            throw new IllegalArgumentException("字段定义和字段名的数量必须一致");
        }

        // 转义表名中的方括号，防止SQL注入
        tableName = tableName.replace("[", "[[").replace("]", "]]");

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("ALTER TABLE [").append(schemaName).append("].[")
                .append(tableName).append("] ADD ");

        boolean hasNewColumns = false;
        for (int i = 0; i < columnDefinitions.size(); i++) {
            String columnName = columnNames.get(i);
            if (sqlServeBatchIsColumnExists(schemaName, tableName, columnName)) {
                System.out.println("字段已存在，无需添加: " + columnName);
                continue;
            }

            String columnDefinition = columnDefinitions.get(i);
            if (hasNewColumns) {
                sqlBuilder.append(", ");
            }
            sqlBuilder.append("[").append(columnName.replace("[", "[[").replace("]", "]]")).append("] ")
                    .append(columnDefinition);
            hasNewColumns = true;
        }

        String sql = sqlBuilder.toString();
        if (!hasNewColumns) {
            System.out.println("没有需要添加的新字段");
            return;
        }

        try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
             Statement stmt = conn.createStatement()) {
            System.out.println("执行SQL: " + sql); // 调试用
            stmt.executeUpdate(sql);
            System.out.println("字段批量添加成功");
        } catch (SQLException e) {
            System.err.println("批量添加字段失败: " + sql);
            e.printStackTrace();
        }
    }

    /**
     * 验证字段是否存在（SQL Server 版本）
     */
    private static boolean sqlServeBatchIsColumnExists(String schemaName, String tableName, String columnName) {
        String sql = "SELECT COUNT(*) FROM sys.columns " +
                "WHERE object_id = OBJECT_ID('[" + schemaName + "].[" + tableName + "]') " +
                "AND name = @columnName";

        try (Connection conn = DriverManager.getConnection(DbConfig.URL, DbConfig.USER, DbConfig.PASSWORD);
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, columnName);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        } catch (SQLException e) {
            System.err.println("检查字段存在性失败: " + sql);
            e.printStackTrace();
        }
        return false;
    }
}