package org.jeecg.codegenerate;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: Lius
 * @CreateTime: 2025-06-23
 * @Description: SQL Server 触发器生成工具
 */
public class CreateTrigger {

    // 数据库连接信息
    private static final String JDBC_URL = "jdbc:sqlserver://192.168.1.118:1433;databaseName=LXZN_TEST_HANGYU_GK";
    private static final String USERNAME = "sa";
    private static final String PASSWORD = "123";

    public static void main(String[] args) {
        String schemaName = "dbo"; // SQL Server 默认架构
        String logTableName = "log_table"; // 日志表名

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
            // 获取指定架构下的所有表
            List<String> tables = getTables(connection, schemaName);

            // 生成触发器 SQL 并保存到文件
            generateTriggersForTables(connection, schemaName, logTableName, tables);

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取指定架构下的所有表
     */
    private static List<String> getTables(Connection connection, String schemaName) throws SQLException {
        List<String> tables = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();

        try (ResultSet resultSet = metaData.getTables(null, schemaName, null, new String[]{"TABLE"})) {
            while (resultSet.next()) {
                String tableName = resultSet.getString("TABLE_NAME");
                tables.add(tableName);
            }
        }

        return tables;
    }

    /**
     * 获取表的字段列表
     */
    private static List<String> getTableColumns(Connection connection, String schemaName, String tableName) throws SQLException {
        List<String> columns = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();

        try (ResultSet resultSet = metaData.getColumns(null, schemaName, tableName, null)) {
            while (resultSet.next()) {
                String columnName = resultSet.getString("COLUMN_NAME");
                columns.add(columnName);
            }
        }

        return columns;
    }

    /**
     * 获取表的主键列
     */
    private static String getPrimaryKeyColumn(Connection connection, String schemaName, String tableName) throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        try (ResultSet resultSet = metaData.getPrimaryKeys(null, schemaName, tableName)) {
            if (resultSet.next()) {
                return resultSet.getString("COLUMN_NAME");
            }
        }
        return "id"; // 默认使用id作为主键，如果没有找到主键
    }

    /**
     * 为所有表生成触发器
     */
    private static void generateTriggersForTables(Connection connection, String schemaName, String logTableName, List<String> tables) throws SQLException, IOException {
        try (FileWriter writer = new FileWriter("triggers.sql")) {
            for (String tableName : tables) {
                // 获取表结构信息
                List<String> columns = getTableColumns(connection, schemaName, tableName);
                String primaryKey = getPrimaryKeyColumn(connection, schemaName, tableName);

                // 生成触发器 SQL
                String triggerSQL = generateTriggerSQL(schemaName, tableName, logTableName, columns, primaryKey);

                // 将生成的触发器 SQL 写入文件
                writer.write("-- 触发器表: " + tableName + "\n");
                writer.write(triggerSQL);
                writer.write("\n\n");

                // 如果需要，可以直接在数据库中创建触发器
                // createTrigger(connection, triggerSQL);
            }
            System.out.println("成功生成触发器并保存到Triggers.sql中!");
        }
    }

    /**
     * 生成 SQL Server 触发器 SQL
     */
    private static String generateTriggerSQL(String schemaName, String tableName, String logTableName, List<String> columns, String primaryKey) {
        StringBuilder triggerSQL = new StringBuilder();

        // 触发器头部
        triggerSQL.append("CREATE TRIGGER [")
                .append(tableName)
                .append("_trigger]\n")
                .append("ON [")
                .append(schemaName)
                .append("].[").append(tableName).append("]\n")
                .append("AFTER INSERT, UPDATE, DELETE\n")
                .append("AS\n")
                .append("BEGIN\n")
                .append("    SET NOCOUNT ON;\n\n");

        // 插入操作
        triggerSQL.append("    -- 插入操作\n")
                .append("    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted)\n")
                .append("    BEGIN\n")
                .append("        INSERT INTO [").append(logTableName).append("] (action, create_time, sql_log)\n")
                .append("        SELECT 'INSERT', GETDATE(), 'INSERT INTO [").append(tableName).append("] (");

        // 拼接字段名
        for (int i = 0; i < columns.size(); i++) {
            triggerSQL.append("[").append(columns.get(i)).append("]");
            if (i < columns.size() - 1) {
                triggerSQL.append(", ");
            }
        }

        triggerSQL.append(") VALUES (''' + ");

        // 拼接字段值
        for (int i = 0; i < columns.size(); i++) {
            triggerSQL.append("ISNULL(CAST(i.").append(columns.get(i)).append(" AS NVARCHAR(MAX)), '') + '''");
            if (i < columns.size() - 1) {
                triggerSQL.append(", ''' + ");
            }
        }

        triggerSQL.append(");'\n")
                .append("        FROM inserted i;\n")
                .append("    END\n\n");

        // 更新操作
        triggerSQL.append("    -- 更新操作\n")
                .append("    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)\n")
                .append("    BEGIN\n")
                .append("        INSERT INTO [").append(logTableName).append("] (action, create_time, sql_log)\n")
                .append("        SELECT 'UPDATE', GETDATE(), 'UPDATE [").append(tableName).append("] SET ");

        // 拼接更新字段
        for (int i = 0; i < columns.size(); i++) {
            triggerSQL.append("[").append(columns.get(i)).append("] = ''' + ISNULL(CAST(i.").append(columns.get(i)).append(" AS NVARCHAR(MAX)), '') + '''");
            if (i < columns.size() - 1) {
                triggerSQL.append(", ");
            }
        }

        triggerSQL.append(" WHERE [").append(primaryKey).append("] = ''' + ISNULL(CAST(d.").append(primaryKey).append(" AS NVARCHAR(MAX)), '') + ''';'\n")
                .append("        FROM inserted i\n")
                .append("        INNER JOIN deleted d ON i.").append(primaryKey).append(" = d.").append(primaryKey).append(";\n")
                .append("    END\n\n");

        // 删除操作
        triggerSQL.append("    -- 删除操作\n")
                .append("    IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)\n")
                .append("    BEGIN\n")
                .append("        INSERT INTO [").append(logTableName).append("] (action, create_time, sql_log)\n")
                .append("        SELECT 'DELETE', GETDATE(), 'DELETE FROM [").append(tableName).append("] WHERE [").append(primaryKey).append("] = ''' + ISNULL(CAST(d.").append(primaryKey).append(" AS NVARCHAR(MAX)), '') + ''';'\n")
                .append("        FROM deleted d;\n")
                .append("    END\n\n");

        triggerSQL.append("END;\n");

        return triggerSQL.toString();
    }

//    /**
//     * 在数据库中创建触发器
//     */
//    private static void createTrigger(Connection connection, String triggerSQL) throws SQLException {
//        try (Statement statement = connection.createStatement()) {
//            statement.execute(triggerSQL);
//            System.out.println("Trigger created successfully!");
//        }
//    }
}