scripts/analytics/sample_dialogs.R

#!/usr/bin/env Rscript
# Stratified sample of «Отдел по работе с клиентами» dialogs (last N days),
# grouped by already-classified topic, for building the Забота KB.
suppressPackageStartupMessages({ library(RPostgres); library(DBI); library(data.table) })
# Запускать из этой папки: cd scripts/analytics && Rscript sample_dialogs.R
source("db_connect.R")

LOOKBACK <- as.integer(Sys.getenv("LOOKBACK_DAYS", "30"))
PER_TOPIC <- as.integer(Sys.getenv("PER_TOPIC", "14"))
OUT <- Sys.getenv("OUT_DIR", "/tmp/zabota_sample")
dir.create(OUT, recursive = TRUE, showWarnings = FALSE)

con <- zabota_connect()
cat("connected\n")

sql <- sprintf("
WITH d AS (
    SELECT f.dialog_id,
           f.\"chatId\" AS chat_id,
           f.created_at,
           t.topic_name,
           f.msg_count
    FROM mv_chat_dialog_features f
    JOIN chat_dialog_topic t ON t.dialog_id = f.dialog_id
    WHERE f.created_at >= now() - INTERVAL '%d days'
      AND BTRIM(f.first_manager_dept) LIKE 'Отдел по работе с клиентами%%'
      AND f.msg_count > 1
),
msgs AS (
    SELECT dm.dialog_id,
           m.date        AS message_date,
           m.\"senderRole\" AS sender_role,
           m.text        AS message_text
    FROM mv_chat_dialog_messages dm
    JOIN mv_coconut_chat_message m ON m.id = dm.message_id
    WHERE m.type IS DISTINCT FROM 'system'
      AND NULLIF(BTRIM(m.text), '') IS NOT NULL
      AND m.\"senderRole\" IN ('client','support')
),
agg AS (
    SELECT dialog_id,
           COUNT(*) FILTER (WHERE sender_role='support') AS n_support,
           COUNT(*) FILTER (WHERE sender_role='client')  AS n_client
    FROM msgs GROUP BY 1
),
picked AS (
    SELECT d.*, a.n_support, a.n_client,
           row_number() OVER (PARTITION BY d.topic_name
                              ORDER BY a.n_support DESC, d.created_at DESC) AS rn
    FROM d JOIN agg a ON a.dialog_id = d.dialog_id
    WHERE a.n_support >= 1 AND a.n_client >= 1
)
SELECT p.dialog_id, p.chat_id, p.created_at, p.topic_name, p.n_support, p.n_client,
       m.message_date, m.sender_role, m.message_text
FROM picked p
JOIN msgs m ON m.dialog_id = p.dialog_id
WHERE p.rn <= %d
ORDER BY p.topic_name, p.dialog_id, m.message_date
", LOOKBACK, PER_TOPIC)

dt <- as.data.table(dbGetQuery(con, sql))
cat("rows:", nrow(dt), " dialogs:", uniqueN(dt$dialog_id), "\n")
dbDisconnect(con)

# --- light PII masking: emails and RU mobile numbers only ---
# Deliberately narrow: dates, times and prices must survive intact, they are the
# substance of the answers we are mining.
mask <- function(x) {
  x <- as.character(x)
  x <- gsub("[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}", "<email>", x, perl = TRUE)
  x <- gsub("(?<![\\d.,])(?:\\+7|\\b8|\\b7)[\\s\\-()]*9\\d{2}[\\s\\-()]*\\d{3}[\\s\\-()]*\\d{2}[\\s\\-()]*\\d{2}(?![\\d])",
            "<phone>", x, perl = TRUE)
  x <- gsub("(?<![\\d.,+])9\\d{9}(?![\\d])", "<phone>", x, perl = TRUE)
  x
}
dt[, message_text := mask(message_text)]
dt[, message_text := substr(message_text, 1, 900)]

fwrite(dt, file.path(OUT, "dialogs_raw.csv"))

label <- function(r) ifelse(r == "client", "КЛИЕНТ", "МЕНЕДЖЕР")
slug <- function(s) {
  s <- gsub("[^\\p{L}\\p{N}]+", "_", s, perl = TRUE)
  substr(tolower(s), 1, 48)
}

setorder(dt, topic_name, dialog_id, message_date)
topics <- dt[, .(n_dialogs = uniqueN(dialog_id)), by = topic_name][order(-n_dialogs)]
print(topics)
fwrite(topics, file.path(OUT, "topics_sampled.csv"))

for (tp in topics$topic_name) {
  sub <- dt[topic_name == tp]
  lines <- c(sprintf("# Тема: %s", tp),
             sprintf("_Диалогов в выборке: %d_", uniqueN(sub$dialog_id)), "")
  for (did in unique(sub$dialog_id)) {
    d <- sub[dialog_id == did]
    lines <- c(lines,
               sprintf("## dialog %s — %s", did, format(d$created_at[1], "%Y-%m-%d")),
               sprintf("[%s] %s", label(d$sender_role), gsub("[\r\n]+", " / ", d$message_text)),
               "")
  }
  writeLines(lines, file.path(OUT, paste0("topic_", slug(tp), ".md")))
}
cat("written to", OUT, "\n")