ylrz_saas_pharmacy/java/scripts/QueryLobsterNodeTemplates.java

import java.sql.*;

public class QueryLobsterNodeTemplates {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/fs_tenant_cs1"
                + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";
        try (Connection c = DriverManager.getConnection(url, "root", "Ylrz_1q2w3e4r5t6y");
             Statement st = c.createStatement()) {

            System.out.println("=== lobster_system_prompt fallback hits ===");
            try (ResultSet rs = st.executeQuery(
                    "SELECT prompt_key, LEFT(prompt_content,150) c FROM lobster_system_prompt "
                            + "WHERE prompt_key LIKE '%fallback%' "
                            + "OR prompt_content LIKE '%confirm%' "
                            + "OR prompt_content LIKE '%customerName%' "
                            + "OR prompt_content LIKE '%Evaluate customer%' LIMIT 20")) {
                while (rs.next()) {
                    System.out.println(rs.getString("prompt_key"));
                    System.out.println("  " + rs.getString("c"));
                }
            }

            System.out.println("\n=== workflow nodes wf=11 ===");
            try (ResultSet rs = st.executeQuery(
                    "SELECT id, node_code, node_name, node_type, LEFT(message_template,120) mt, LEFT(greeting_config,80) gc "
                            + "FROM company_workflow_lobster_node WHERE workflow_id=11 AND del_flag=0 ORDER BY sort_no")) {
                while (rs.next()) {
                    System.out.printf("%s %s type=%s%n  mt=%s%n  gc=%s%n",
                            rs.getString("node_code"), rs.getString("node_name"),
                            rs.getString("node_type"), rs.getString("mt"), rs.getString("gc"));
                }
            }

            System.out.println("\n=== contact 755 workflow bindings ===");
            try (ResultSet rs = st.executeQuery(
                    "SELECT id, workflow_id, contact_id, status, current_node_name FROM lobster_workflow_instance "
                            + "WHERE contact_id=755 OR contact_id IN (SELECT contact_id FROM chat_session WHERE session_id=101220)")) {
                while (rs.next()) {
                    System.out.printf("inst=%s wf=%s contact=%s status=%s node=%s%n",
                            rs.getString("id"), rs.getString("workflow_id"),
                            rs.getString("contact_id"), rs.getString("status"), rs.getString("current_node_name"));
                }
            }

            System.out.println("\n=== msg content stats session 101220 ===");
            try (ResultSet rs = st.executeQuery(
                    "SELECT LEFT(content,60) snippet, COUNT(*) cnt, MIN(create_time) fa, MAX(create_time) la "
                            + "FROM chat_msg WHERE session_id=101220 AND send_type=2 "
                            + "GROUP BY LEFT(content,60) ORDER BY cnt DESC LIMIT 8")) {
                while (rs.next()) {
                    System.out.printf("cnt=%s %s~%s | %s%n",
                            rs.getString("cnt"), rs.getString("fa"), rs.getString("la"), rs.getString("snippet"));
                }
            }
        }
    }
}