ylrz_saas_pharmacy/java/scripts/QueryAggregateChatSession101220.java

import java.sql.*;

/** Analyze aggregate chat session 101220 in tenant DB */
public class QueryAggregateChatSession101220 {
    public static void main(String[] args) throws Exception {
        String host = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/";
        String user = "root";
        String pass = "Ylrz_1q2w3e4r5t6y";
        String db = "fs_tenant_cs1";

        try (Connection c = DriverManager.getConnection(host + db
                + "?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", user, pass)) {
            querySession(c, 101220L);
            queryMsgByContact(c, 755L);
            queryDuplicateOutbound(c);
            querySilentInstances(c);
            queryInstanceForSession(c, 101220L);
            queryPrompts(c);
            queryTasks(c);
        }
    }

    private static void querySession(Connection c, long sessionId) throws SQLException {
        System.out.println("--- chat_session " + sessionId + " ---");
        try (Statement st = c.createStatement()) {
            try (ResultSet rs = st.executeQuery(
                    "SELECT session_id, company_id, contact_id, channel_type, external_user_id, "
                            + "instance_id, last_msg, last_msg_time, status, create_time, update_time "
                            + "FROM chat_session WHERE session_id=" + sessionId)) {
                if (rs.next()) {
                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        System.out.println(rs.getMetaData().getColumnLabel(i) + "=" + rs.getString(i));
                    }
                } else {
                    System.out.println("(not found)");
                }
            }
            System.out.println("--- chat_msg chronological ---");
            try (ResultSet rs = st.executeQuery(
                    "SELECT msg_id, send_type, LEFT(content,160) AS content, create_time "
                            + "FROM chat_msg WHERE session_id=" + sessionId + " ORDER BY create_time ASC")) {
                int n = 0, cust = 0, ai = 0;
                while (rs.next()) {
                    n++;
                    int stype = rs.getInt("send_type");
                    if (stype == 1) cust++;
                    else if (stype == 2) ai++;
                    System.out.printf("%s | send_type=%s | %s%n",
                            rs.getString("create_time"), stype, rs.getString("content"));
                }
                System.out.println("total=" + n + " customer=" + cust + " ai=" + ai);
            }
        }
    }

    private static void queryMsgByContact(Connection c, long contactId) throws SQLException {
        System.out.println("\n--- sessions contact_id=" + contactId + " ---");
        String sql = "SELECT session_id, channel_type, instance_id, last_msg, last_msg_time "
                + "FROM chat_session WHERE contact_id=" + contactId + " ORDER BY last_msg_time DESC LIMIT 5";
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                System.out.printf("sess=%s ch=%s inst=%s time=%s msg=%s%n",
                        rs.getString("session_id"), rs.getString("channel_type"),
                        rs.getString("instance_id"), rs.getString("last_msg_time"),
                        rs.getString("last_msg"));
            }
        }
    }

    private static void queryDuplicateOutbound(Connection c) throws SQLException {
        System.out.println("\n--- duplicate outbound send_type=2 (7d, cnt>=3) ---");
        String sql = "SELECT LEFT(content,90) AS snippet, COUNT(*) AS cnt, "
                + "MIN(create_time) AS first_at, MAX(create_time) AS last_at "
                + "FROM chat_msg WHERE send_type=2 "
                + "AND create_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) "
                + "GROUP BY LEFT(content,90) HAVING cnt >= 3 ORDER BY cnt DESC LIMIT 12";
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            while (rs.next()) {
                System.out.printf("cnt=%s | %s ~ %s | %s%n",
                        rs.getString("cnt"), rs.getString("first_at"),
                        rs.getString("last_at"), rs.getString("snippet"));
            }
        }
    }

    private static void querySilentInstances(Connection c) throws SQLException {
        System.out.println("\n--- running instances: 0 inbound chat, >=2 outbound ---");
        String sql = "SELECT i.id, i.workflow_id, i.contact_id, i.current_node_name, i.start_time, "
                + "(SELECT COUNT(*) FROM chat_msg m JOIN chat_session s ON m.session_id=s.session_id "
                + " WHERE s.instance_id=i.id AND m.send_type=1) AS inbound, "
                + "(SELECT COUNT(*) FROM chat_msg m JOIN chat_session s ON m.session_id=s.session_id "
                + " WHERE s.instance_id=i.id AND m.send_type=2) AS outbound "
                + "FROM lobster_workflow_instance i WHERE i.status='running' "
                + "HAVING inbound=0 AND outbound>=2 ORDER BY outbound DESC LIMIT 8";
        try (Statement st = c.createStatement(); ResultSet rs = st.executeQuery(sql)) {
            int n = 0;
            while (rs.next()) {
                n++;
                System.out.printf("inst=%s wf=%s contact=%s out=%s in=%s node=%s%n",
                        rs.getString("id"), rs.getString("workflow_id"), rs.getString("contact_id"),
                        rs.getString("outbound"), rs.getString("inbound"), rs.getString("current_node_name"));
            }
            if (n == 0) System.out.println("(none)");
        } catch (SQLException e) {
            System.out.println("skip: " + e.getMessage());
        }
    }

    private static void queryInstanceForSession(Connection c, long sessionId) throws SQLException {
        System.out.println("\n--- workflow instance for session ---");
        try (Statement st = c.createStatement()) {
            try (ResultSet rs = st.executeQuery(
                    "SELECT id, workflow_id, contact_id, status, current_node_code, current_node_name, "
                            + "start_time, last_activity_time, context_json "
                            + "FROM lobster_workflow_instance WHERE id=(SELECT instance_id FROM chat_session WHERE session_id="
                            + sessionId + ")")) {
                if (rs.next()) {
                    System.out.println("id=" + rs.getString("id"));
                    System.out.println("workflow_id=" + rs.getString("workflow_id"));
                    System.out.println("status=" + rs.getString("status"));
                    System.out.println("node=" + rs.getString("current_node_code") + " / " + rs.getString("current_node_name"));
                    System.out.println("start=" + rs.getString("start_time"));
                    System.out.println("last_activity=" + rs.getString("last_activity_time"));
                    String ctx = rs.getString("context_json");
                    if (ctx != null) {
                        System.out.println("context_json(len)=" + ctx.length());
                        if (ctx.contains("_proactiveGreetCount")) System.out.println("  has _proactiveGreetCount");
                        if (ctx.contains("_lastProactiveGreetText")) System.out.println("  has _lastProactiveGreetText");
                    }
                } else {
                    System.out.println("(no instance)");
                }
            }
        } catch (SQLException e) {
            System.out.println("skip: " + e.getMessage());
        }
    }

    private static void queryPrompts(Connection c) throws SQLException {
        System.out.println("\n--- lobster_system_prompt (instruction keys) ---");
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT prompt_key, LEFT(prompt_content,120) AS c FROM lobster_system_prompt "
                             + "WHERE prompt_key LIKE '%instruction%' OR prompt_key='system' "
                             + "OR prompt_content LIKE '%Please answer%' LIMIT 15")) {
            while (rs.next()) {
                System.out.printf("%s | %s%n", rs.getString("prompt_key"), rs.getString("c"));
            }
        } catch (SQLException e) {
            System.out.println("skip: " + e.getMessage());
        }
    }

    private static void queryTasks(Connection c) throws SQLException {
        System.out.println("\n--- recent lobster tasks (pending/done) ---");
        try (Statement st = c.createStatement();
             ResultSet rs = st.executeQuery(
                     "SELECT id, workflow_id, task_type, execute_status, cron_expression, "
                             + "last_execute_time, create_time FROM company_workflow_lobster_task "
                             + "ORDER BY id DESC LIMIT 8")) {
            while (rs.next()) {
                System.out.printf("task=%s wf=%s type=%s status=%s cron=%s last=%s%n",
                        rs.getString("id"), rs.getString("workflow_id"), rs.getString("task_type"),
                        rs.getString("execute_status"), rs.getString("cron_expression"),
                        rs.getString("last_execute_time"));
            }
        } catch (SQLException e) {
            System.out.println("skip: " + e.getMessage());
        }
    }
}