/**
 * Recent calls from the FreePBX CDR (MariaDB `asteriskcdrdb.cdr` on the PBX,
 * read-only user `logship_cdr`, runtimeConfig.pbxCdr).
 *
 * FreePBX writes MANY cdr rows per call (every Local/queue/follow-me leg), so
 * rows are grouped by `linkedid` (shared by all legs of one call) and reduced
 * to ONE entry per call:
 *   inbound  = a leg whose `channel` is a trunk channel (PJSIP/<trunk>-…)
 *   outbound = a leg whose `dstchannel` is a trunk channel
 *   answered = an ANSWERED leg with billsec > 0 on the far side (for inbound:
 *              a Dial leg to a non-trunk dstchannel — voicemail/announcement
 *              legs don't count; for outbound: the trunk leg itself)
 *   viaQueue = any leg has lastapp 'Queue' (app_queue) — the support line
 * The dashboard card shows: missed direct-dial inbound (needs a callback),
 * ALL Queue-routed inbound (answered or not — it's the main support line,
 * worth seeing either way), and successful outbound.
 */
import mysql from 'mysql2/promise'

export interface RecentCall {
  linkedid: string
  direction: 'in' | 'out'
  answered: boolean
  viaQueue: boolean      // any leg routed through a FreePBX Queue (app_queue) — the support line
  at: string            // ISO of the earliest leg
  number: string        // caller (in) / dialed (out)
  name: string          // caller-id name (in) / dial label (out)
  extension: string     // our side (agent extension) when known
  did: string
  duration: number      // talk seconds (billsec of the far leg)
  legs: number
}

const g = globalThis as any
let pool: mysql.Pool | null = g.__pbxCdrPool ?? null
const cache: { rows: RecentCall[]; expires: number } = g.__pbxCdrCache ?? (g.__pbxCdrCache = { rows: [], expires: 0 })

export const getPool = (): mysql.Pool | null => {
  if (pool) return pool
  const cfg: any = (useRuntimeConfig() as any).pbxCdr || {}
  if (!cfg.host || !cfg.user || !cfg.password) return null
  pool = mysql.createPool({ host: cfg.host, port: Number(cfg.port || 3306), user: cfg.user, password: cfg.password, database: cfg.database || 'asteriskcdrdb', connectionLimit: 2, connectTimeout: 5000, waitForConnections: true, timezone: 'Z' })
  g.__pbxCdrPool = pool
  return pool
}

const trunkPatterns = (): string[] => {
  const cfg: any = (useRuntimeConfig() as any).pbxCdr || {}
  return String(cfg.trunkPatterns || 'easybell_trunk').split(',').map((s: string) => s.trim()).filter(Boolean)
}
const isTrunk = (chan: string, pats: string[]) => { const c = String(chan || ''); return pats.some((p) => c.includes(p)) }
export const extFromChannel = (chan: string): string => {
  const m = /^(?:PJSIP|SIP)\/(\d{2,6})-/.exec(String(chan || '')) || /^Local\/(\d{2,6})@/.exec(String(chan || ''))
  return m?.[1] || ''
}
// Caller-id NAME without our own decorations: the click-to-dial "Anruf: …"
// prefix, the inbound-route "LogYou: …" prefix, and names that only repeat
// the number (FreePBX fills the name with the number when CNAM is empty).
const cleanName = (clid: string, number = ''): string => {
  const m = /^"([^"]*)"/.exec(String(clid || ''))
  let n = (m?.[1] || '').trim()
  n = n.replace(/^(Anruf|LogYou|CID)\s*:\s*/i, '').trim()
  if (!n || /^[\d\s+()\-]+$/.test(n) || n.replace(/\D/g, '') === String(number || '').replace(/\D/g, '')) return ''
  return n
}

export const classifyCalls = (rows: any[], pats: string[]): RecentCall[] => {
  const groups = new Map<string, any[]>()
  for (const r of rows) {
    const key = String(r.linkedid || r.uniqueid)
    if (!groups.has(key)) groups.set(key, [])
    groups.get(key)!.push(r)
  }
  const out: RecentCall[] = []
  for (const [linkedid, legs] of groups) {
    legs.sort((a, b) => new Date(a.calldate).getTime() - new Date(b.calldate).getTime())
    const inboundLeg = legs.find((l) => isTrunk(l.channel, pats))
    const outboundLegs = legs.filter((l) => isTrunk(l.dstchannel, pats))
    const at = new Date(legs[0].calldate).toISOString()
    if (inboundLeg && outboundLegs.length === 0) {
      const answeredLeg = legs.find((l) => String(l.disposition) === 'ANSWERED' && Number(l.billsec) > 0 && String(l.lastapp) === 'Dial' && l.dstchannel && !isTrunk(l.dstchannel, pats))
      out.push({
        linkedid, direction: 'in', answered: !!answeredLeg, viaQueue: legs.some((l) => String(l.lastapp) === 'Queue'), at,
        number: String(inboundLeg.src || '').trim(),
        name: cleanName(inboundLeg.clid, inboundLeg.src),
        extension: answeredLeg ? extFromChannel(answeredLeg.dstchannel) : '',
        did: String(inboundLeg.did || inboundLeg.dst || ''),
        duration: answeredLeg ? Number(answeredLeg.billsec) : 0,
        legs: legs.length
      })
      continue
    }
    if (outboundLegs.length) {
      const answeredLeg = outboundLegs.find((l) => String(l.disposition) === 'ANSWERED' && Number(l.billsec) > 0)
      const last = answeredLeg || outboundLegs[outboundLegs.length - 1]
      const agentLeg = legs.find((l) => extFromChannel(l.channel) && !isTrunk(l.channel, pats))
      out.push({
        linkedid, direction: 'out', answered: !!answeredLeg, viaQueue: false, at,
        number: String(last.dst || '').trim(),
        name: cleanName(legs.find((l) => /Anruf:/i.test(String(l.clid || '')))?.clid || '', last.dst),
        extension: extFromChannel(agentLeg?.channel || '') || extFromChannel(legs.find((l) => /^Local\/\d+@/.test(String(l.channel)))?.channel || ''),
        did: '',
        duration: answeredLeg ? Number(answeredLeg.billsec) : 0,
        legs: legs.length
      })
    }
  }
  out.sort((a, b) => b.at.localeCompare(a.at))
  return out
}

const loadShown = async (): Promise<RecentCall[] | null> => {
  if (cache.expires > Date.now()) return cache.rows
  const p = getPool()
  if (!p) return null
  const [rows] = await p.query(
    'SELECT calldate, clid, src, dst, dcontext, channel, dstchannel, lastapp, disposition, duration, billsec, uniqueid, linkedid, did FROM cdr WHERE calldate > NOW() - INTERVAL 30 DAY ORDER BY calldate DESC LIMIT 1500'
  ) as any
  const all = classifyCalls(rows as any[], trunkPatterns())
  // Direct-dial inbound: missed only (an answered one was picked up personally,
  // nothing to follow up on). Queue-routed inbound (the support line): always
  // shown, answered or not — worth seeing regardless of outcome.
  const shown = all.filter((c) => (c.direction === 'in' && (!c.answered || c.viaQueue)) || (c.direction === 'out' && c.answered))
  cache.rows = shown.slice(0, 150)
  cache.expires = Date.now() + 30 * 1000
  return cache.rows
}

/**
 * Missed direct-dial inbound + all Queue-routed inbound + answered outbound,
 * newest first, one row per call. Cached 30 s per process (last 150 calls
 * over the 30-day CDR window).
 * `direction` narrows to 'in' | 'out' (default: both); `search` matches the
 * number (digits only, so formatting/spacing in the query doesn't matter) or
 * the caller-id name; `offset`/`limit` page through the filtered result.
 * `total` is the count after filtering (for page count); `missedTotal` is the
 * missed-inbound count within the search filter but ACROSS both directions
 * (independent of the `direction` tab), so the header badge stays accurate
 * no matter which tab is selected.
 */
export const recentCalls = async (
  limit = 20,
  offset = 0,
  search = '',
  direction: 'all' | 'in' | 'out' = 'all'
): Promise<{ available: boolean; rows: RecentCall[]; total: number; missedTotal: number }> => {
  const shown = await loadShown()
  if (shown === null) return { available: false, rows: [], total: 0, missedTotal: 0 }
  const q = String(search || '').trim()
  const qDigits = q.replace(/\D+/g, '')
  const bySearch = shown.filter((c) => !q ||
    (qDigits && String(c.number || '').replace(/\D+/g, '').includes(qDigits)) ||
    String(c.name || '').toLowerCase().includes(q.toLowerCase())
  )
  const filtered = bySearch.filter((c) => direction === 'all' || c.direction === direction)
  const missedTotal = bySearch.filter((c) => c.direction === 'in' && !c.answered).length
  return { available: true, rows: filtered.slice(offset, offset + limit), total: filtered.length, missedTotal }
}