DashDescomplicar/api/scripts/sessions-patterns.ts

#!/usr/bin/env tsx
/**
 * Detector semanal de padrões sobre a BD Observabilidade (Fase 6A).
 *
 * Uso:
 *   sessions-patterns.ts [--week YYYY-Www] [--publish] [--force]
 *   sessions-patterns.ts --backfill
 *
 * Backfill:
 *   Itera todas as semanas ISO desde a primeira sessão na BD até (excluindo)
 *   a semana corrente, detecta padrões e faz upsert. Nunca publica nem abre
 *   tickets. Output: linha JSON por semana + sumário total no fim.
 *
 * Fluxo:
 *   1. Resolver intervalo da semana (segunda 00:00 UTC → domingo 23:59 UTC)
 *   2. Correr detectPatterns (6 detectores heurísticos)
 *   3. Persistir com upsertPattern + consecutive_weeks
 *   4. Se --publish: chamar gateway MCP (desk-crm) para publicar comentário HTML
 *      na discussão #32, e para padrões com consecutive_weeks>=3 e
 *      severity∈(warning,action) abrir Ticket no Desk.
 *   5. Output JSON-line final com contagens.
 *
 * Env obrigatório com --publish:
 *   MCP_GATEWAY_TOKEN  Bearer token do gateway MCP
 *   MCP_GATEWAY_URL    URL do MCP desk-crm (default https://gateway.descomplicar.pt/v1/desk-crm/mcp)
 */
import { readFileSync, writeFileSync, existsSync } from 'node:fs'
import { openSessionsDb, type PatternRecord, type SessionsDb } from '../services/sessions/db.js'
import { DEFAULT_DB_PATH } from '../services/sessions/indexer.js'
import {
  detectPatterns,
  toPatternRecord,
  weekIso,
  weekRange,
  type Pattern,
} from '../services/sessions/patterns.js'

const CARL_JSON_PATH = '/media/ealmeida/Dados/.carl/carl.json'

/**
 * Propor padrão persistente como staging entry no carl.json.
 * Idempotente: não duplica por pattern_key.
 * Só dispara para severity action ou warning com ≥3 semanas consecutivas.
 */
function proposeCarlStagingEntry(p: PatternRecord): void {
  if (!existsSync(CARL_JSON_PATH)) return
  try {
    const raw = readFileSync(CARL_JSON_PATH, 'utf-8')
    const carl = JSON.parse(raw) as { staging?: unknown[] }
    const staging = (carl.staging ??= [])
    const stagingId = `pattern-${p.pattern_key}`
    const exists = staging.some((e) => typeof e === 'object' && e !== null && (e as { id?: string }).id === stagingId)
    if (exists) return
    staging.push({
      id: stagingId,
      type: 'pattern-proposal',
      source: 'observabilidade-patterns',
      name: `Padrão recorrente: ${p.title}`,
      description: p.description,
      severity: p.severity,
      consecutive_weeks: p.consecutive_weeks,
      affected_count: p.affected_count,
      sample_session_ids: p.sample_session_ids,
      proposed_at: new Date().toISOString(),
      status: 'pending-review',
    })
    writeFileSync(CARL_JSON_PATH, JSON.stringify(carl, null, 2), 'utf-8')
    console.error(`[patterns] proposto em CARL staging: ${stagingId}`)
  } catch (err) {
    console.error(`[patterns] falha ao propor em CARL staging: ${(err as Error).message}`)
  }
}

interface Args {
  week?: string
  publish: boolean
  force: boolean
  backfill: boolean
}

interface MCPToolCallResult {
  content?: Array<{ type: string; text: string }>
  isError?: boolean
}

/** Staff ID usado para publicar comentários automáticos (Observabilidade) */
const OBSERVABILIDADE_STAFF_ID = 25
/** Discussão Desk onde os resumos semanais são publicados */
const OBSERVABILIDADE_DISCUSSION_ID = 32
/** Departamento "Geral" no Desk CRM (id=1) */
const OBSERVABILIDADE_DEPARTMENT_ID = 1

function parseArgs(argv: string[]): Args {
  const a: Args = { publish: false, force: false, backfill: false }
  for (let i = 0; i < argv.length; i++) {
    if (argv[i] === '--week') a.week = argv[++i]
    else if (argv[i] === '--publish') a.publish = true
    else if (argv[i] === '--force') a.force = true
    else if (argv[i] === '--backfill') a.backfill = true
  }
  return a
}

/** Detecta e persiste padrões para uma única semana (sem publicar). */
function processWeek(db: SessionsDb, monday: Date): {
  week_iso: string
  detected: number
  by_severity: { action: number; warning: number; info: number }
} {
  const range = weekRange(monday)
  const weekIsoStr = range.iso
  const detected: Pattern[] = detectPatterns(db, range.start, range.end)
  const records: PatternRecord[] = []
  for (const p of detected) {
    const tmpRec = toPatternRecord(p, weekIsoStr, 1)
    db.upsertPattern(tmpRec)
    const consecutive = db.getConsecutiveWeeks(p.pattern_key, weekIsoStr)
    const rec = toPatternRecord(p, weekIsoStr, consecutive)
    db.upsertPattern(rec)
    records.push(rec)
  }
  return {
    week_iso: weekIsoStr,
    detected: records.length,
    by_severity: {
      action: records.filter((r) => r.severity === 'action').length,
      warning: records.filter((r) => r.severity === 'warning').length,
      info: records.filter((r) => r.severity === 'info').length,
    },
  }
}

/** Executa backfill desde a primeira sessão até (excl.) semana corrente. */
function runBackfill(db: SessionsDb, dbPath: string): void {
  const raw = db.rawDb()
  const row = raw.prepare('SELECT MIN(started_at) as min_started FROM sessions').get() as
    | { min_started: string | null }
    | undefined
  if (!row || !row.min_started) {
    console.error('[patterns] backfill: BD sem sessões. Nada a fazer.')
    console.log(JSON.stringify({ backfill: true, weeks_processed: 0, total_detected: 0 }))
    return
  }
  const firstStart = new Date(row.min_started)
  const firstRange = weekRange(firstStart)
  const currentRange = weekRange(new Date())
  const currentIso = currentRange.iso

  console.error(
    `[patterns] backfill: primeira sessão ${row.min_started}, semana inicial ${firstRange.iso}, ` +
      `semana corrente ${currentIso} (excluída) db=${dbPath}`,
  )

  let cursor = new Date(firstRange.start)
  let weeksProcessed = 0
  let totalDetected = 0
  const bySev = { action: 0, warning: 0, info: 0 }
  const perWeek: Array<{ week_iso: string; detected: number }> = []

  // Iteração semanal: cursor é sempre segunda 00:00 UTC
  while (weekIso(cursor) !== currentIso) {
    const summary = processWeek(db, cursor)
    console.log(JSON.stringify({ backfill: true, ...summary }))
    weeksProcessed++
    totalDetected += summary.detected
    bySev.action += summary.by_severity.action
    bySev.warning += summary.by_severity.warning
    bySev.info += summary.by_severity.info
    perWeek.push({ week_iso: summary.week_iso, detected: summary.detected })
    // Avançar 7 dias
    cursor = new Date(cursor)
    cursor.setUTCDate(cursor.getUTCDate() + 7)
    // Safety: evitar loop infinito em caso de bug
    if (weeksProcessed > 520) {
      console.error('[patterns] backfill: safety break após 520 semanas')
      break
    }
  }

  console.log(
    JSON.stringify({
      backfill_summary: true,
      weeks_processed: weeksProcessed,
      total_detected: totalDetected,
      by_severity: bySev,
      first_week: perWeek[0]?.week_iso ?? null,
      last_week: perWeek[perWeek.length - 1]?.week_iso ?? null,
    }),
  )
}

/** Converte YYYY-Www em intervalo {start,end} UTC. */
function weekIsoToRange(weekIsoStr: string): { start: Date; end: Date; iso: string } {
  const m = weekIsoStr.match(/^(\d{4})-W(\d{2})$/)
  if (!m) throw new Error(`Formato --week inválido: ${weekIsoStr}`)
  const year = parseInt(m[1], 10)
  const week = parseInt(m[2], 10)
  // ISO week: quinta da semana 1 está sempre em 4 de Janeiro
  const jan4 = new Date(Date.UTC(year, 0, 4))
  const jan4Dow = jan4.getUTCDay() || 7 // 1..7 (seg..dom)
  const mondayWeek1 = new Date(jan4)
  mondayWeek1.setUTCDate(jan4.getUTCDate() - (jan4Dow - 1))
  const monday = new Date(mondayWeek1)
  monday.setUTCDate(mondayWeek1.getUTCDate() + (week - 1) * 7)
  return weekRange(monday)
}

function escapeHtml(s: string): string {
  return s.replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/"/g, '&quot;')
}

function buildDiscussionHtml(weekIso: string, patterns: PatternRecord[]): string {
  const dateStr = new Date().toISOString().slice(0, 10)
  const lines: string[] = []
  lines.push(`<h4>Semana ${weekIso} — Padrões Detectados Automaticamente</h4>`)
  lines.push(`<p><em>Detector automático Observabilidade — ${patterns.length} padrões analisados.</em></p>`)
  if (patterns.length === 0) {
    lines.push('<p>Nenhum padrão accionável detectado esta semana.</p>')
  } else {
    lines.push('<h4>Padrões (severity)</h4>')
    lines.push('<ul>')
    for (const p of patterns) {
      const samples = p.sample_session_ids
        .slice(0, 5)
        .map((id) => `<a href="/sessions/${encodeURIComponent(id)}"><code>${escapeHtml(id.slice(0, 8))}</code></a>`)
        .join(', ')
      lines.push(
        `<li><strong>${escapeHtml(p.title)}</strong> [${p.severity}] — ` +
          `${p.affected_count} sessões` +
          (p.metric_value != null ? `, métrica ${p.metric_value}` : '') +
          `<br><em>Descrição:</em> ${escapeHtml(p.description)}` +
          `<br><em>Sample:</em> ${samples}` +
          `<br><em>Semanas consecutivas:</em> ${p.consecutive_weeks}</li>`,
      )
    }
    lines.push('</ul>')
  }
  lines.push('<hr>')
  lines.push(`<p><strong>Skill:</strong> observabilidade-patterns | <strong>Data:</strong> ${dateStr}</p>`)
  return lines.join('\n')
}

/**
 * Chama uma ferramenta do gateway MCP (JSON-RPC 2.0 sobre HTTP).
 * O gateway pode responder em SSE (text/event-stream) ou JSON — tratamos ambos.
 */
async function callMcpTool(tool: string, args: Record<string, unknown>): Promise<MCPToolCallResult> {
  const url = process.env.MCP_GATEWAY_URL ?? 'https://gateway.descomplicar.pt/v1/desk-crm/mcp'
  const token = process.env.MCP_GATEWAY_TOKEN
  if (!token) throw new Error('MCP_GATEWAY_TOKEN não definido')
  const body = {
    jsonrpc: '2.0',
    id: Date.now(),
    method: 'tools/call',
    params: { name: tool, arguments: args },
  }
  const res = await fetch(url, {
    method: 'POST',
    headers: {
      Authorization: `Bearer ${token}`,
      'Content-Type': 'application/json',
      Accept: 'application/json, text/event-stream',
    },
    body: JSON.stringify(body),
  })
  if (!res.ok) {
    const txt = await res.text().catch(() => '')
    throw new Error(`MCP gateway ${res.status}: ${txt.slice(0, 300)}`)
  }
  const raw = await res.text()
  // Responde JSON puro ou SSE (linhas "data: {...}")
  let payload: string | null = null
  for (const line of raw.split(/\r?\n/)) {
    const trimmed = line.trim()
    if (!trimmed) continue
    if (trimmed.startsWith('data: ')) {
      payload = trimmed.slice(6)
      break
    }
    if (trimmed.startsWith('{')) {
      payload = trimmed
      break
    }
  }
  if (!payload) throw new Error(`MCP resposta sem payload JSON: ${raw.slice(0, 200)}`)
  const parsed = JSON.parse(payload) as { error?: unknown; result?: MCPToolCallResult }
  if (parsed.error) throw new Error(`MCP error: ${JSON.stringify(parsed.error)}`)
  const result = parsed.result as MCPToolCallResult | undefined
  if (result?.isError) {
    const txt = result.content?.map((c) => c.text).join('\n') ?? ''
    throw new Error(`MCP tool ${tool} devolveu isError: ${txt.slice(0, 300)}`)
  }
  return result ?? {}
}

async function postDeskDiscussionComment(html: string): Promise<unknown> {
  return callMcpTool('add_discussion_comment', {
    discussion_id: OBSERVABILIDADE_DISCUSSION_ID,
    content: html,
    staff_id: OBSERVABILIDADE_STAFF_ID,
  })
}

async function createDeskTicket(p: PatternRecord): Promise<unknown> {
  const priority = p.severity === 'action' ? 4 : 3 // 4=alta, 3=média
  const message = [
    `<p><strong>Padrão recorrente detectado automaticamente — ${p.consecutive_weeks} semanas consecutivas.</strong></p>`,
    `<p>${escapeHtml(p.description)}</p>`,
    `<ul>`,
    `<li>Pattern key: <code>${escapeHtml(p.pattern_key)}</code></li>`,
    `<li>Severity: ${p.severity}</li>`,
    `<li>Affected: ${p.affected_count} sessões</li>`,
    `<li>Metric: ${p.metric_value ?? 'n/a'}</li>`,
    `<li>Week: ${p.week_iso}</li>`,
    `</ul>`,
    `<p><em>Sample sessions:</em> ${p.sample_session_ids.map((s) => `<code>${escapeHtml(s)}</code>`).join(', ')}</p>`,
  ].join('\n')
  return callMcpTool('create_ticket', {
    subject: `Padrão recorrente: ${p.title}`,
    message,
    priority,
    department: OBSERVABILIDADE_DEPARTMENT_ID,
  })
}

async function main(): Promise<void> {
  const args = parseArgs(process.argv.slice(2))
  if (args.publish && !process.env.MCP_GATEWAY_TOKEN) {
    console.error('[patterns] --publish requer MCP_GATEWAY_TOKEN. Aborta.')
    process.exit(1)
  }
  const dbPath = process.env.OBSERVABILIDADE_DB ?? DEFAULT_DB_PATH
  const db = openSessionsDb(dbPath)

  if (args.backfill) {
    if (args.publish) {
      console.error('[patterns] --backfill é incompatível com --publish. Aborta.')
      process.exit(1)
    }
    runBackfill(db, dbPath)
    db.close()
    return
  }

  const range = args.week ? weekIsoToRange(args.week) : weekRange(new Date())
  const weekIso = args.week ?? range.iso
  console.error(`[patterns] semana ${weekIso} range=${range.start.toISOString()}..${range.end.toISOString()} db=${dbPath} publish=${args.publish}`)

  const detected: Pattern[] = detectPatterns(db, range.start, range.end)
  console.error(`[patterns] detectados ${detected.length} padrões`)

  const records: PatternRecord[] = []
  for (const p of detected) {
    // Primeiro upsert com consecutive=1 (placeholder); depois recalcula.
    const tmpRec = toPatternRecord(p, weekIso, 1)
    db.upsertPattern(tmpRec)
    const consecutive = db.getConsecutiveWeeks(p.pattern_key, weekIso)
    const rec = toPatternRecord(p, weekIso, consecutive)
    db.upsertPattern(rec)
    records.push(rec)
  }

  // Ordenar por severity (action > warning > info) depois affected_count
  const sevRank: Record<string, number> = { action: 3, warning: 2, info: 1 }
  records.sort((a, b) => (sevRank[b.severity] - sevRank[a.severity]) || (b.affected_count - a.affected_count))

  let commentPosted = false
  let ticketsCreated = 0
  let publishError: string | null = null

  if (args.publish) {
    try {
      const html = buildDiscussionHtml(weekIso, records)
      await postDeskDiscussionComment(html)
      commentPosted = true
      for (const rec of records) {
        if (rec.consecutive_weeks >= 3 && (rec.severity === 'warning' || rec.severity === 'action')) {
          try {
            await createDeskTicket(rec)
            ticketsCreated++
          } catch (e) {
            console.error(`[patterns] falha ao criar ticket para ${rec.pattern_key}:`, (e as Error).message)
          }
          // Propor como staging no CARL — idempotente por pattern_key
          proposeCarlStagingEntry(rec)
        }
      }
    } catch (e) {
      publishError = (e as Error).message
    }
  }

  // Dry-run: render HTML para stderr para verificação manual
  if (!args.publish) {
    const html = buildDiscussionHtml(weekIso, records)
    console.error('\n--- HTML (dry-run) ---')
    console.error(html)
    console.error('--- /HTML ---\n')
    for (const rec of records) {
      if (rec.consecutive_weeks >= 3 && (rec.severity === 'warning' || rec.severity === 'action')) {
        console.error(`[patterns] (dry-run) Ticket seria criado: ${rec.title} — ${rec.consecutive_weeks} sem.`)
        console.error(`[patterns] (dry-run) CARL staging seria proposto: pattern-${rec.pattern_key}`)
      }
    }
  }

  const summary = {
    week_iso: weekIso,
    range: { start: range.start.toISOString(), end: range.end.toISOString() },
    detected: records.length,
    by_severity: {
      action: records.filter((r) => r.severity === 'action').length,
      warning: records.filter((r) => r.severity === 'warning').length,
      info: records.filter((r) => r.severity === 'info').length,
    },
    published: commentPosted,
    tickets_created: ticketsCreated,
    publish_error: publishError,
    dry_run: !args.publish,
  }
  console.log(JSON.stringify(summary))
  db.close()
}

main().catch((err) => {
  console.error('[patterns] falha fatal:', err)
  process.exit(2)
})