import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Scan all tenant DBs (schemas with lobster_system_prompt) and apply GEPA negative prompt SQL.
 *
 * Usage:
 *   java -cp mysql-connector-j.jar;. ApplyLobsterGepaNegativePrompt
 *   java -cp mysql-connector-j.jar;. ApplyLobsterGepaNegativePrompt fs_tenant_cs1
 */
public class ApplyLobsterGepaNegativePrompt {

    private static final String HOST = "cq-cdb-8fjmemkb.sql.tencentcdb.com:27220";
    private static final String USER = "root";
    private static final String PASS = "Ylrz_1q2w3e4r5t6y";
    private static final String MASTER_DB = "ylrz_saas";
    private static final String SQL_PATH = "d:/ylrz_saas_new/java/sql/lobster_engine_prompt_gepa_negative.sql";

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        byte[] bytes = Files.readAllBytes(Paths.get(SQL_PATH));
        String sql = new String(bytes, StandardCharsets.UTF_8);
        if (sql.startsWith("\uFEFF")) {
            sql = sql.substring(1);
        }

        List<String> targets = new ArrayList<String>();
        if (args.length > 0) {
            for (String arg : args) {
                if (arg != null && !arg.trim().isEmpty()) {
                    targets.add(arg.trim());
                }
            }
        } else {
            targets = discoverTenantDatabases();
        }

        System.out.println("Target databases: " + targets.size());
        int ok = 0;
        int fail = 0;
        for (String db : targets) {
            try {
                applyOne(db, sql);
                ok++;
            } catch (Exception e) {
                fail++;
                System.out.println("[FAIL] " + db + ": " + e.getMessage());
            }
        }
        System.out.println("\nSummary: ok=" + ok + " fail=" + fail + " total=" + targets.size());
    }

    private static List<String> discoverTenantDatabases() throws Exception {
        List<String> dbs = new ArrayList<String>();
        Connection master = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            master = open(MASTER_DB);
            st = master.createStatement();
            rs = st.executeQuery(
                    "SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES "
                            + "WHERE TABLE_NAME = 'lobster_system_prompt' "
                            + "AND TABLE_SCHEMA NOT IN ('information_schema','mysql','performance_schema','sys') "
                            + "ORDER BY TABLE_SCHEMA");
            while (rs.next()) {
                dbs.add(rs.getString(1));
            }
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (master != null) {
                master.close();
            }
        }
        return dbs;
    }

    private static void applyOne(String db, String sql) throws Exception {
        System.out.println("\n=== " + db + " ===");
        Connection c = null;
        Statement st = null;
        try {
            c = open(db);
            st = c.createStatement();
            st.execute(sql);
            verify(st, db);
        } finally {
            if (st != null) {
                st.close();
            }
            if (c != null) {
                c.close();
            }
        }
    }

    private static void verify(Statement st, String db) throws Exception {
        ResultSet rs = st.executeQuery(
                "SELECT prompt_key, "
                        + "(prompt_content LIKE '%{avoidBlock}%' OR prompt_content LIKE '%{negativeBlock}%') AS ok "
                        + "FROM lobster_system_prompt "
                        + "WHERE company_id IS NULL "
                        + "AND prompt_key IN ('gepa_synthesize_avoid', 'gepa_mutate_skill') "
                        + "ORDER BY prompt_key");
        try {
            while (rs.next()) {
                String key = rs.getString("prompt_key");
                boolean ok = rs.getInt("ok") > 0;
                System.out.println((ok ? "[OK] " : "[WARN] ") + db + " " + key);
            }
        } finally {
            rs.close();
        }
    }

    private static Connection open(String db) throws Exception {
        return DriverManager.getConnection(
                "jdbc:mysql://" + HOST + "/" + db
                        + "?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf8&allowMultiQueries=true",
                USER, PASS);
    }
}