Function getRelationshipStats

packages/core/src/query/advanced/relationship.ts:67–208

(
  db: DatabaseAdapter,
  filter?: TimeFilter,
  options?: RelationshipOptions
)

const DEFAULT_PERSEVERANCE_THRESHOLD = 300

export function getRelationshipStats(
  db: DatabaseAdapter,
  filter?: TimeFilter,
  options?: RelationshipOptions
): RelationshipStats {
  const perseveranceThreshold = options?.perseveranceThreshold ?? DEFAULT_PERSEVERANCE_THRESHOLD
  const emptyResult: RelationshipStats = {
    months: [],
    members: [],
    totalSessions: 0,
    hasSessionIndex: false,
    iceBreakers: [],
    totalIceBreaks: 0,
    responseLatency: [],
    perseverance: [],
    totalDoubleTexts: 0,
    monthlyResponseLatency: [],
    monthlyPerseverance: [],
    perseveranceThreshold,
  }

  const sessionCount = db.prepare('SELECT COUNT(*) as count FROM segment').get() as { count: number } | undefined
  if (!sessionCount || sessionCount.count === 0) return emptyResult

  const timeConditions: string[] = []
  const params: (number | string)[] = []

  if (filter?.startTs !== undefined) {
    timeConditions.push('cs.start_ts >= ?')
    params.push(filter.startTs)
  }
  if (filter?.endTs !== undefined) {
    timeConditions.push('cs.start_ts <= ?')
    params.push(filter.endTs)
  }

  const whereClause = timeConditions.length > 0 ? `WHERE ${timeConditions.join(' AND ')}` : ''

  const segmentRows = db
    .prepare(
      `SELECT cs.id AS segment_id, cs.start_ts, cs.end_ts,
              (SELECT m.sender_id FROM message_context mc JOIN message m ON m.id = mc.message_id
               WHERE mc.segment_id = cs.id ORDER BY m.ts ASC, m.id ASC LIMIT 1) AS initiator_id,
              (SELECT m.sender_id FROM message_context mc JOIN message m ON m.id = mc.message_id
               WHERE mc.segment_id = cs.id ORDER BY m.ts DESC, m.id DESC LIMIT 1) AS closer_id
       FROM segment cs ${whereClause} ORDER BY cs.start_ts ASC`
    )
    .all(...params) as Array<{
    segment_id: number
    start_ts: number
    end_ts: number
    initiator_id: number | null
    closer_id: number | null
  }>

  const memberNames = new Map<number, string>()
  const memberRows = db
    .prepare('SELECT id, COALESCE(group_nickname, account_name, platform_id) as name FROM member')