wulf-pulse/lib/services/ticket-digest-service.ts

/**
 * Ticket Digest Report Service
 * Aggregates ticket data for daily/weekly/monthly periods, sends it to an LLM
 * for noise analysis and insights, then delivers an Adaptive Card to Teams.
 */

import { postgresClient } from './postgres-client';

export type DigestPeriod = 'daily' | 'weekly' | 'monthly';

export interface DigestConfig {
  daily_enabled: boolean;
  weekly_enabled: boolean;
  monthly_enabled: boolean;
  daily_cron: string;
  weekly_cron: string;
  monthly_cron: string;
  llm_provider: string;
  llm_model: string;
  include_noise_analysis: boolean;
  include_sla_analysis: boolean;
  include_resource_analysis: boolean;
  include_client_analysis: boolean;
  include_recommendations: boolean;
  channel_ids: number[];
}

export interface NotificationChannel {
  id: number;
  name: string;
  channel_type: 'teams' | 'telegram' | 'ntfy' | 'webhook';
  config: Record<string, any>;
  is_active: boolean;
}

export interface DeliveryResult {
  channelId: number;
  label: string;
  success: boolean;
  httpStatus?: number;
  error?: string;
}

export interface TicketDigestStats {
  period: { type: DigestPeriod; start: string; end: string; label: string };
  overview: {
    total_created: number;
    total_resolved: number;
    total_open_end: number;
    avg_resolution_hours: number | null;
    avg_first_response_hours: number | null;
    total_hours_worked: number;
  };
  by_source: Array<{ source: number | null; source_label: string; count: number; pct: number }>;
  by_queue: Array<{ queue_id: number | null; queue_label: string; count: number; resolved: number; avg_resolve_hrs: number | null }>;
  by_priority: Array<{ priority: number | null; priority_label: string; count: number }>;
  by_issue_type: Array<{ issue_type: number | null; issue_label: string; count: number }>;
  top_clients: Array<{ company_id: number; company_name: string; ticket_count: number; hours_worked: number }>;
  top_resources: Array<{ resource_id: number; resource_name: string; tickets_touched: number; hours_worked: number }>;
  noise_candidates: Array<{ title: string; count: number; source: number | null; source_label: string; avg_resolve_min: number | null; sample_id: number }>;
  monitor_tickets: { total: number; auto_resolved: number; pct_of_all: number };
  sla: { first_response_met: number; first_response_missed: number; resolution_met: number; resolution_missed: number };
  comparison: {
    prev_total_created: number;
    prev_total_resolved: number;
    prev_avg_resolution_hours: number | null;
    prev_total_hours_worked: number;
    created_delta_pct: number | null;
    resolved_delta_pct: number | null;
  } | null;
}

const SOURCE_LABELS: Record<number, string> = {
  '-2': 'RMM Alert (Resolved)',
  '-1': 'RMM Alert',
  1: 'Phone',
  2: 'Chat/Portal',
  4: 'Email',
  6: 'Internal',
  8: 'Monitoring Alert',
  17: 'Auto-ticket',
  21: 'Voice',
  27: 'Feedback',
  30: 'Web Portal',
  35: 'Phish Alert',
  38: 'Teams',
  39: 'API',
};

const PRIORITY_LABELS: Record<number, string> = {
  1: 'Critical',
  2: 'High',
  3: 'Medium',
  4: 'Low',
  6: 'Informational',
};

function getPeriodBounds(period: DigestPeriod, now: Date): { start: Date; end: Date; prevStart: Date; prevEnd: Date; label: string } {
  const end = new Date(now);
  end.setHours(0, 0, 0, 0);

  if (period === 'daily') {
    const start = new Date(end);
    start.setDate(start.getDate() - 1);
    const prevEnd = new Date(start);
    const prevStart = new Date(prevEnd);
    prevStart.setDate(prevStart.getDate() - 1);
    return { start, end, prevStart, prevEnd, label: start.toLocaleDateString('en-US', { weekday: 'long', month: 'short', day: 'numeric' }) };
  }
  if (period === 'weekly') {
    const start = new Date(end);
    start.setDate(start.getDate() - 7);
    const prevEnd = new Date(start);
    const prevStart = new Date(prevEnd);
    prevStart.setDate(prevStart.getDate() - 7);
    const label = `${start.toLocaleDateString('en-US', { month: 'short', day: 'numeric' })} – ${new Date(end.getTime() - 86400000).toLocaleDateString('en-US', { month: 'short', day: 'numeric' })}`;
    return { start, end, prevStart, prevEnd, label };
  }
  // monthly
  const start = new Date(end.getFullYear(), end.getMonth() - 1, 1);
  const monthEnd = new Date(end.getFullYear(), end.getMonth(), 1);
  const prevStart = new Date(start.getFullYear(), start.getMonth() - 1, 1);
  const prevEnd = new Date(start);
  const label = start.toLocaleDateString('en-US', { month: 'long', year: 'numeric' });
  return { start, end: monthEnd, prevStart, prevEnd, label };
}

export class TicketDigestService {

  // ──────────────────────────────────────────────────────────────
  // Config & Webhook CRUD
  // ──────────────────────────────────────────────────────────────

  async getConfig(): Promise<DigestConfig> {
    const r = await postgresClient.query('SELECT * FROM ticket_digest_config WHERE id = 1');
    return r.rows[0] as DigestConfig;
  }

  async updateConfig(updates: Partial<DigestConfig>): Promise<DigestConfig> {
    const fields: string[] = [];
    const values: unknown[] = [];
    let idx = 1;
    for (const [key, val] of Object.entries(updates)) {
      fields.push(`${key} = $${idx++}`);
      values.push(val);
    }
    if (fields.length === 0) return this.getConfig();
    fields.push('updated_at = NOW()');
    values.push(1);
    const r = await postgresClient.query(
      `UPDATE ticket_digest_config SET ${fields.join(', ')} WHERE id = $${idx} RETURNING *`,
      values
    );
    return r.rows[0] as DigestConfig;
  }

  async getAvailableChannels(): Promise<NotificationChannel[]> {
    const r = await postgresClient.query(
      'SELECT id, name, channel_type, config, is_active, owner_user_id FROM notification_channels ORDER BY name'
    );
    return r.rows as NotificationChannel[];
  }

  // ──────────────────────────────────────────────────────────────
  // Data Aggregation
  // ──────────────────────────────────────────────────────────────

  async aggregate(period: DigestPeriod, now?: Date): Promise<TicketDigestStats> {
    const { start, end, prevStart, prevEnd, label } = getPeriodBounds(period, now ?? new Date());
    const s = start.toISOString();
    const e = end.toISOString();
    const ps = prevStart.toISOString();
    const pe = prevEnd.toISOString();

    const [
      overviewR,
      bySourceR,
      byQueueR,
      byPriorityR,
      byIssueTypeR,
      topClientsR,
      topResourcesR,
      noiseR,
      monitorR,
      slaR,
      prevOverviewR,
    ] = await Promise.all([
      // Overview
      postgresClient.query(`
        SELECT
          COUNT(*) FILTER (WHERE t.create_date >= $1 AND t.create_date < $2) as total_created,
          COUNT(*) FILTER (WHERE t.resolved_date_time >= $1 AND t.resolved_date_time < $2) as total_resolved,
          COUNT(*) FILTER (WHERE t.create_date < $2 AND (t.resolved_date_time IS NULL OR t.resolved_date_time >= $2) AND t.status NOT IN (5)) as total_open_end,
          ROUND(AVG(EXTRACT(EPOCH FROM (t.resolved_date_time - t.create_date))/3600) FILTER (WHERE t.resolved_date_time >= $1 AND t.resolved_date_time < $2)::numeric, 1) as avg_resolution_hours,
          ROUND(AVG(EXTRACT(EPOCH FROM (t.first_response_date_time - t.create_date))/3600) FILTER (WHERE t.first_response_date_time IS NOT NULL AND t.create_date >= $1 AND t.create_date < $2)::numeric, 1) as avg_first_response_hours,
          COALESCE(SUM(te.hours_worked), 0) as total_hours_worked
        FROM tickets t
        LEFT JOIN time_entries te ON te.ticket_id = t.id AND (te.is_deleted = false) AND te.entry_date >= $1::date AND te.entry_date < $2::date
        WHERE t.is_deleted = false AND (t.create_date >= $1 AND t.create_date < $2 OR t.resolved_date_time >= $1 AND t.resolved_date_time < $2)
      `, [s, e]),

      // By source
      postgresClient.query(`
        SELECT t.source, COUNT(*) as count
        FROM tickets t WHERE t.is_deleted = false AND t.create_date >= $1 AND t.create_date < $2
        GROUP BY t.source ORDER BY count DESC
      `, [s, e]),

      // By queue
      postgresClient.query(`
        SELECT t.queue_id, q.label as queue_label, COUNT(*) as count,
          COUNT(*) FILTER (WHERE t.resolved_date_time >= $1 AND t.resolved_date_time < $2) as resolved,
          ROUND(AVG(EXTRACT(EPOCH FROM (t.resolved_date_time - t.create_date))/3600) FILTER (WHERE t.resolved_date_time IS NOT NULL)::numeric, 1) as avg_resolve_hrs
        FROM tickets t
        LEFT JOIN queues q ON q.value = t.queue_id
        WHERE t.is_deleted = false AND t.create_date >= $1 AND t.create_date < $2
        GROUP BY t.queue_id, q.label ORDER BY count DESC LIMIT 15
      `, [s, e]),

      // By priority
      postgresClient.query(`
        SELECT t.priority, COUNT(*) as count
        FROM tickets t WHERE t.is_deleted = false AND t.create_date >= $1 AND t.create_date < $2
        GROUP BY t.priority ORDER BY t.priority
      `, [s, e]),

      // By issue type
      postgresClient.query(`
        SELECT t.issue_type, it.label as issue_label, COUNT(*) as count
        FROM tickets t
        LEFT JOIN issue_types it ON it.value = t.issue_type
        WHERE t.is_deleted = false AND t.create_date >= $1 AND t.create_date < $2
        GROUP BY t.issue_type, it.label ORDER BY count DESC LIMIT 15
      `, [s, e]),

      // Top clients
      postgresClient.query(`
        SELECT t.company_id, c.company_name, COUNT(DISTINCT t.id) as ticket_count,
          COALESCE(SUM(te.hours_worked), 0)::float as hours_worked
        FROM tickets t
        JOIN companies c ON c.id = t.company_id
        LEFT JOIN time_entries te ON te.ticket_id = t.id AND (te.is_deleted = false) AND te.entry_date >= $1::date AND te.entry_date < $2::date
        WHERE t.is_deleted = false AND t.create_date >= $1 AND t.create_date < $2
        GROUP BY t.company_id, c.company_name ORDER BY ticket_count DESC LIMIT 10
      `, [s, e]),

      // Top resources
      postgresClient.query(`
        SELECT te.resource_id, r.first_name || ' ' || r.last_name as resource_name,
          COUNT(DISTINCT te.ticket_id) as tickets_touched,
          COALESCE(SUM(te.hours_worked), 0)::float as hours_worked
        FROM time_entries te
        JOIN resources r ON r.id = te.resource_id
        WHERE te.is_deleted = false AND te.entry_date >= $1::date AND te.entry_date < $2::date AND te.ticket_id IS NOT NULL
        GROUP BY te.resource_id, r.first_name, r.last_name ORDER BY hours_worked DESC LIMIT 10
      `, [s, e]),

      // Noise candidates — repeated titles (grouping by first 60 chars of title)
      postgresClient.query(`
        SELECT LEFT(t.title, 60) as title, COUNT(*) as count, t.source,
          ROUND(AVG(EXTRACT(EPOCH FROM (t.resolved_date_time - t.create_date))/60) FILTER (WHERE t.resolved_date_time IS NOT NULL)::numeric, 0) as avg_resolve_min,
          MIN(t.id) as sample_id
        FROM tickets t
        WHERE t.is_deleted = false AND t.create_date >= $1 AND t.create_date < $2
        GROUP BY LEFT(t.title, 60), t.source
        HAVING COUNT(*) >= 3
        ORDER BY count DESC LIMIT 20
      `, [s, e]),

      // Monitor-generated tickets
      postgresClient.query(`
        SELECT
          COUNT(*) as total,
          COUNT(*) FILTER (WHERE t.resolved_date_time IS NOT NULL AND EXTRACT(EPOCH FROM (t.resolved_date_time - t.create_date)) < 1800) as auto_resolved
        FROM tickets t
        WHERE t.is_deleted = false AND t.create_date >= $1 AND t.create_date < $2 AND t.monitor_id IS NOT NULL
      `, [s, e]),

      // SLA (using 1hr first response / 24hr resolution as baseline)
      postgresClient.query(`
        SELECT
          COUNT(*) FILTER (WHERE t.first_response_date_time IS NOT NULL AND EXTRACT(EPOCH FROM (t.first_response_date_time - t.create_date))/3600 <= 1) as fr_met,
          COUNT(*) FILTER (WHERE t.first_response_date_time IS NOT NULL AND EXTRACT(EPOCH FROM (t.first_response_date_time - t.create_date))/3600 > 1) as fr_missed,
          COUNT(*) FILTER (WHERE t.resolved_date_time IS NOT NULL AND EXTRACT(EPOCH FROM (t.resolved_date_time - t.create_date))/3600 <= 24) as res_met,
          COUNT(*) FILTER (WHERE t.resolved_date_time IS NOT NULL AND EXTRACT(EPOCH FROM (t.resolved_date_time - t.create_date))/3600 > 24) as res_missed
        FROM tickets t
        WHERE t.is_deleted = false AND t.create_date >= $1 AND t.create_date < $2
      `, [s, e]),

      // Previous period overview for comparison
      postgresClient.query(`
        SELECT
          COUNT(*) FILTER (WHERE t.create_date >= $1 AND t.create_date < $2) as total_created,
          COUNT(*) FILTER (WHERE t.resolved_date_time >= $1 AND t.resolved_date_time < $2) as total_resolved,
          ROUND(AVG(EXTRACT(EPOCH FROM (t.resolved_date_time - t.create_date))/3600) FILTER (WHERE t.resolved_date_time >= $1 AND t.resolved_date_time < $2)::numeric, 1) as avg_resolution_hours,
          COALESCE(SUM(te.hours_worked), 0) as total_hours_worked
        FROM tickets t
        LEFT JOIN time_entries te ON te.ticket_id = t.id AND (te.is_deleted = false) AND te.entry_date >= $1::date AND te.entry_date < $2::date
        WHERE t.is_deleted = false AND (t.create_date >= $1 AND t.create_date < $2 OR t.resolved_date_time >= $1 AND t.resolved_date_time < $2)
      `, [ps, pe]),
    ]);

    const ov = overviewR.rows[0];
    const prevOv = prevOverviewR.rows[0];
    const monRow = monitorR.rows[0];
    const slaRow = slaR.rows[0];
    const totalCreated = parseInt(ov.total_created) || 0;
    const prevCreated = parseInt(prevOv.total_created) || 0;
    const prevResolved = parseInt(prevOv.total_resolved) || 0;

    const deltaPct = (cur: number, prev: number): number | null => prev === 0 ? null : Math.round(((cur - prev) / prev) * 100);

    return {
      period: { type: period, start: s, end: e, label },
      overview: {
        total_created: totalCreated,
        total_resolved: parseInt(ov.total_resolved) || 0,
        total_open_end: parseInt(ov.total_open_end) || 0,
        avg_resolution_hours: ov.avg_resolution_hours ? parseFloat(ov.avg_resolution_hours) : null,
        avg_first_response_hours: ov.avg_first_response_hours ? parseFloat(ov.avg_first_response_hours) : null,
        total_hours_worked: parseFloat(ov.total_hours_worked) || 0,
      },
      by_source: bySourceR.rows.map(r => ({
        source: r.source,
        source_label: SOURCE_LABELS[r.source] ?? `Source ${r.source ?? 'Unknown'}`,
        count: parseInt(r.count),
        pct: totalCreated > 0 ? Math.round((parseInt(r.count) / totalCreated) * 100) : 0,
      })),
      by_queue: byQueueR.rows.map(r => ({
        queue_id: r.queue_id,
        queue_label: r.queue_label || `Queue ${r.queue_id}`,
        count: parseInt(r.count),
        resolved: parseInt(r.resolved) || 0,
        avg_resolve_hrs: r.avg_resolve_hrs ? parseFloat(r.avg_resolve_hrs) : null,
      })),
      by_priority: byPriorityR.rows.map(r => ({
        priority: r.priority,
        priority_label: PRIORITY_LABELS[r.priority] ?? `Priority ${r.priority ?? 'Unknown'}`,
        count: parseInt(r.count),
      })),
      by_issue_type: byIssueTypeR.rows.map(r => ({
        issue_type: r.issue_type,
        issue_label: r.issue_label || `Type ${r.issue_type}`,
        count: parseInt(r.count),
      })),
      top_clients: topClientsR.rows.map(r => ({
        company_id: r.company_id,
        company_name: r.company_name,
        ticket_count: parseInt(r.ticket_count),
        hours_worked: parseFloat(r.hours_worked) || 0,
      })),
      top_resources: topResourcesR.rows.map(r => ({
        resource_id: r.resource_id,
        resource_name: r.resource_name,
        tickets_touched: parseInt(r.tickets_touched),
        hours_worked: parseFloat(r.hours_worked) || 0,
      })),
      noise_candidates: noiseR.rows.map(r => ({
        title: r.title,
        count: parseInt(r.count),
        source: r.source,
        source_label: SOURCE_LABELS[r.source] ?? `Source ${r.source}`,
        avg_resolve_min: r.avg_resolve_min ? parseFloat(r.avg_resolve_min) : null,
        sample_id: parseInt(r.sample_id),
      })),
      monitor_tickets: {
        total: parseInt(monRow.total) || 0,
        auto_resolved: parseInt(monRow.auto_resolved) || 0,
        pct_of_all: totalCreated > 0 ? Math.round((parseInt(monRow.total) / totalCreated) * 100) : 0,
      },
      sla: {
        first_response_met: parseInt(slaRow.fr_met) || 0,
        first_response_missed: parseInt(slaRow.fr_missed) || 0,
        resolution_met: parseInt(slaRow.res_met) || 0,
        resolution_missed: parseInt(slaRow.res_missed) || 0,
      },
      comparison: {
        prev_total_created: prevCreated,
        prev_total_resolved: prevResolved,
        prev_avg_resolution_hours: prevOv.avg_resolution_hours ? parseFloat(prevOv.avg_resolution_hours) : null,
        prev_total_hours_worked: parseFloat(prevOv.total_hours_worked) || 0,
        created_delta_pct: deltaPct(totalCreated, prevCreated),
        resolved_delta_pct: deltaPct(parseInt(ov.total_resolved) || 0, prevResolved),
      },
    };
  }

  // ──────────────────────────────────────────────────────────────
  // LLM Analysis
  // ──────────────────────────────────────────────────────────────

  async analyzeWithLLM(stats: TicketDigestStats, config: DigestConfig): Promise<{ analysis: string; tokensUsed: number }> {
    const apiKey = config.llm_provider === 'anthropic'
      ? process.env.ANTHROPIC_API_KEY || ''
      : process.env.OPENAI_API_KEY || '';

    if (!apiKey) {
      // Also check workflow_settings table
      const keyRow = await postgresClient.query(
        `SELECT value FROM workflow_settings WHERE key = $1`,
        [config.llm_provider === 'anthropic' ? 'anthropic_api_key' : 'openai_api_key']
      );
      const dbKey = keyRow.rows[0]?.value?.replace(/"/g, '') || '';
      if (!dbKey) {
        return { analysis: 'LLM API key not configured. Configure it in Admin → Workflow Settings.', tokensUsed: 0 };
      }
      return this.callLLM(stats, config, dbKey);
    }
    return this.callLLM(stats, config, apiKey);
  }

  private async callLLM(stats: TicketDigestStats, config: DigestConfig, apiKey: string): Promise<{ analysis: string; tokensUsed: number }> {
    const systemPrompt = `You are an IT service desk analyst for a managed service provider (MSP). You produce concise, actionable digest reports for management.

Your analysis should be structured with these sections (use markdown headers):
${config.include_noise_analysis ? '- **Noise & Automation**: Identify repetitive/auto-generated tickets that could be suppressed or auto-resolved. Quantify the noise.' : ''}
${config.include_sla_analysis ? '- **SLA & Response Times**: Analyze first response and resolution times. Call out any concerning trends.' : ''}
${config.include_resource_analysis ? '- **Team Workload**: Analyze resource utilization. Flag overloaded or underutilized engineers.' : ''}
${config.include_client_analysis ? '- **Client Spotlight**: Highlight clients with unusual ticket volume or patterns worth attention.' : ''}
${config.include_recommendations ? '- **Recommendations**: 3-5 specific, actionable items to reduce noise, improve response times, or optimize workflows.' : ''}

Rules:
- Be direct and data-driven. Reference specific numbers from the data.
- Keep the total response under 800 words.
- Focus on anomalies and actionable findings, not restating obvious stats.
- If noise candidates repeat 10+ times, strongly recommend automation or suppression.
- Compare with previous period where relevant.`;

    const dataPayload = JSON.stringify({
      period: stats.period,
      overview: stats.overview,
      comparison: stats.comparison,
      by_source: stats.by_source.slice(0, 8),
      by_queue: stats.by_queue.slice(0, 10),
      by_priority: stats.by_priority,
      top_clients: stats.top_clients.slice(0, 8),
      top_resources: stats.top_resources.slice(0, 8),
      noise_candidates: stats.noise_candidates.slice(0, 15),
      monitor_tickets: stats.monitor_tickets,
      sla: stats.sla,
    }, null, 2);

    const userPrompt = `Analyze this ${stats.period.type} ticket digest for ${stats.period.label}:\n\n${dataPayload}`;

    if (config.llm_provider === 'anthropic') {
      const response = await fetch('https://api.anthropic.com/v1/messages', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'x-api-key': apiKey,
          'anthropic-version': '2023-06-01',
        },
        body: JSON.stringify({
          model: config.llm_model || 'claude-sonnet-4-20250514',
          max_tokens: 2000,
          temperature: 0.3,
          system: systemPrompt,
          messages: [{ role: 'user', content: userPrompt }],
        }),
      });

      if (!response.ok) {
        const err = await response.text();
        throw new Error(`Anthropic API error (${response.status}): ${err}`);
      }

      const data = await response.json();
      const text = data.content?.find((b: any) => b.type === 'text')?.text || '';
      const tokensUsed = (data.usage?.input_tokens || 0) + (data.usage?.output_tokens || 0);
      return { analysis: text, tokensUsed };
    } else {
      const response = await fetch('https://api.openai.com/v1/chat/completions', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'Authorization': `Bearer ${apiKey}`,
        },
        body: JSON.stringify({
          model: config.llm_model || 'gpt-4o',
          messages: [
            { role: 'system', content: systemPrompt },
            { role: 'user', content: userPrompt },
          ],
          temperature: 0.3,
          max_tokens: 2000,
        }),
      });

      if (!response.ok) {
        const err = await response.text();
        throw new Error(`OpenAI API error (${response.status}): ${err}`);
      }

      const data = await response.json();
      const text = data.choices?.[0]?.message?.content || '';
      const tokensUsed = (data.usage?.total_tokens) || 0;
      return { analysis: text, tokensUsed };
    }
  }

  // ──────────────────────────────────────────────────────────────
  // Adaptive Card Builder
  // ──────────────────────────────────────────────────────────────

  buildAdaptiveCard(stats: TicketDigestStats, analysis: string): object {
    const ov = stats.overview;
    const cmp = stats.comparison;
    const periodTitle = stats.period.type.charAt(0).toUpperCase() + stats.period.type.slice(1);
    const headerText = `📊 ${periodTitle} Ticket Digest — ${stats.period.label}`;

    const delta = (cur: number, prev: number | null | undefined): string => {
      if (prev == null || prev === 0) return '';
      const pct = Math.round(((cur - prev) / prev) * 100);
      return pct > 0 ? ` ↑${pct}%` : pct < 0 ? ` ↓${Math.abs(pct)}%` : '';
    };

    const bodyItems: object[] = [
      { type: 'TextBlock', text: headerText, weight: 'Bolder', size: 'Large', wrap: true },
      {
        type: 'ColumnSet',
        columns: [
          { type: 'Column', width: 'stretch', items: [{ type: 'TextBlock', text: `**${ov.total_created}** Created${cmp ? delta(ov.total_created, cmp.prev_total_created) : ''}`, wrap: true }] },
          { type: 'Column', width: 'stretch', items: [{ type: 'TextBlock', text: `**${ov.total_resolved}** Resolved${cmp ? delta(ov.total_resolved, cmp.prev_total_resolved) : ''}`, wrap: true }] },
          { type: 'Column', width: 'stretch', items: [{ type: 'TextBlock', text: `**${ov.avg_resolution_hours ?? '—'}h** Avg Resolve`, wrap: true }] },
          { type: 'Column', width: 'stretch', items: [{ type: 'TextBlock', text: `**${ov.total_hours_worked.toFixed(1)}h** Worked`, wrap: true }] },
        ],
      },
    ];

    // Noise highlight
    if (stats.noise_candidates.length > 0) {
      const topNoise = stats.noise_candidates.slice(0, 5);
      const totalNoise = topNoise.reduce((s, n) => s + n.count, 0);
      const noiseFacts = topNoise.map(n => ({
        title: `${n.count}×`,
        value: `${n.title} (${n.source_label})`,
      }));
      bodyItems.push(
        { type: 'TextBlock', text: `🔁 Top Noise — ${totalNoise} repetitive tickets`, weight: 'Bolder', spacing: 'Medium', wrap: true },
        { type: 'FactSet', facts: noiseFacts },
      );
    }

    // Monitor tickets
    if (stats.monitor_tickets.total > 0) {
      bodyItems.push({
        type: 'TextBlock',
        text: `🤖 Monitor-generated: **${stats.monitor_tickets.total}** (${stats.monitor_tickets.pct_of_all}% of all) · ${stats.monitor_tickets.auto_resolved} auto-resolved (<30m)`,
        spacing: 'Medium', wrap: true,
      });
    }

    // SLA summary
    const totalFR = stats.sla.first_response_met + stats.sla.first_response_missed;
    const totalRes = stats.sla.resolution_met + stats.sla.resolution_missed;
    if (totalFR > 0 || totalRes > 0) {
      const frPct = totalFR > 0 ? Math.round((stats.sla.first_response_met / totalFR) * 100) : 0;
      const resPct = totalRes > 0 ? Math.round((stats.sla.resolution_met / totalRes) * 100) : 0;
      bodyItems.push({
        type: 'TextBlock',
        text: `⏱️ SLA: First Response **${frPct}%** met (≤1h) · Resolution **${resPct}%** met (≤24h)`,
        spacing: 'Small', wrap: true,
      });
    }

    // Top clients
    if (stats.top_clients.length > 0) {
      const clientFacts = stats.top_clients.slice(0, 5).map(c => ({
        title: `${c.ticket_count} tickets`,
        value: `${c.company_name} (${c.hours_worked.toFixed(1)}h)`,
      }));
      bodyItems.push(
        { type: 'TextBlock', text: '🏢 Top Clients', weight: 'Bolder', spacing: 'Medium', wrap: true },
        { type: 'FactSet', facts: clientFacts },
      );
    }

    // LLM analysis section (split into paragraphs for readability)
    if (analysis && analysis.length > 20) {
      bodyItems.push(
        { type: 'TextBlock', text: '🧠 AI Analysis', weight: 'Bolder', size: 'Medium', spacing: 'Large', wrap: true },
      );
      // Truncate for Adaptive Card limits (~28KB) and split on headers
      const truncated = analysis.substring(0, 3500);
      const sections = truncated.split(/(?=^##?\s)/m).filter(s => s.trim());
      for (const section of sections.slice(0, 6)) {
        bodyItems.push({ type: 'TextBlock', text: section.trim(), wrap: true, spacing: 'Small' });
      }
    }

    return {
      $schema: 'http://adaptivecards.io/schemas/adaptive-card.json',
      type: 'AdaptiveCard',
      version: '1.4',
      body: bodyItems,
      actions: [
        { type: 'Action.OpenUrl', title: 'Open Pulse', url: 'https://pulse.wulfconsulting.cloud' },
      ],
    };
  }

  // ──────────────────────────────────────────────────────────────
  // Delivery
  // ──────────────────────────────────────────────────────────────

  async deliver(card: object, stats: TicketDigestStats, channelIds?: number[]): Promise<DeliveryResult[]> {
    const config = await this.getConfig();
    const ids = channelIds ?? config.channel_ids ?? [];
    if (ids.length === 0) return [];

    const channelRows = await postgresClient.query(
      'SELECT id, name, channel_type, config, is_active, owner_user_id FROM notification_channels WHERE id = ANY($1)',
      [ids]
    );
    const channels = channelRows.rows as NotificationChannel[];

    const teamsEnvelope = {
      type: 'message',
      attachments: [{
        contentType: 'application/vnd.microsoft.card.adaptive',
        contentUrl: null,
        content: card,
      }],
    };

    const plainText = this.buildPlainTextSummary(stats);

    const results: DeliveryResult[] = await Promise.all(
      channels.map(async (ch): Promise<DeliveryResult> => {
        try {
          let res: Response;
          if (ch.channel_type === 'teams') {
            const url = ch.config.webhook_url;
            if (!url) throw new Error('Teams channel missing webhook_url');
            res = await fetch(url, {
              method: 'POST',
              headers: { 'Content-Type': 'application/json' },
              body: JSON.stringify(teamsEnvelope),
            });
          } else if (ch.channel_type === 'telegram') {
            const { bot_token, chat_id, parse_mode } = ch.config;
            if (!bot_token || !chat_id) throw new Error('Telegram missing bot_token or chat_id');
            res = await fetch(`https://api.telegram.org/bot${bot_token}/sendMessage`, {
              method: 'POST',
              headers: { 'Content-Type': 'application/json' },
              body: JSON.stringify({ chat_id, text: plainText, parse_mode: parse_mode || 'HTML' }),
            });
          } else if (ch.channel_type === 'ntfy') {
            // Personal channels forced to company server + token (UAT-FIX-01).
            // Global rows retain config-driven behavior so admin-configured
            // digest channels keep working.
            const isPersonal = !!(ch as NotificationChannel & { owner_user_id?: string | null }).owner_user_id;
            const server = isPersonal
              ? (process.env.NTFY_BASE_URL || 'https://ntfy.wulfconsulting.cloud')
              : (ch.config.server_url || 'https://ntfy.sh');
            const topic = ch.config.topic;
            if (!topic) throw new Error('ntfy missing topic');
            const headers: Record<string, string> = { 'Content-Type': 'text/plain', 'Title': `Ticket Digest — ${stats.period.label}` };
            if (isPersonal) {
              const token = process.env.NTFY_PULSE_TOKEN;
              if (token) headers['Authorization'] = `Bearer ${token}`;
            } else if (ch.config.auth_token) {
              headers['Authorization'] = `Bearer ${ch.config.auth_token}`;
            }
            if (ch.config.default_priority) headers['Priority'] = ch.config.default_priority;
            res = await fetch(`${server}/${topic}`, { method: 'POST', headers, body: plainText });
          } else {
            const url = ch.config.url;
            if (!url) throw new Error('Webhook channel missing url');
            res = await fetch(url, {
              method: ch.config.method || 'POST',
              headers: { 'Content-Type': 'application/json', ...(ch.config.headers || {}) },
              body: JSON.stringify({ title: `Ticket Digest — ${stats.period.label}`, text: plainText, stats: stats.overview }),
            });
          }
          return { channelId: ch.id, label: ch.name, success: res.ok, httpStatus: res.status };
        } catch (err) {
          const error = err instanceof Error ? err.message : String(err);
          return { channelId: ch.id, label: ch.name, success: false, error };
        }
      })
    );

    return results;
  }

  private buildPlainTextSummary(stats: TicketDigestStats): string {
    const ov = stats.overview;
    const lines = [
      `📊 Ticket Digest — ${stats.period.label}`,
      `Created: ${ov.total_created} | Resolved: ${ov.total_resolved} | Open: ${ov.total_open_end}`,
      `Avg Resolution: ${ov.avg_resolution_hours ?? '—'}h | Hours Worked: ${ov.total_hours_worked.toFixed(1)}h`,
    ];
    if (stats.monitor_tickets.total > 0) {
      lines.push(`Monitor alerts: ${stats.monitor_tickets.total} (${stats.monitor_tickets.pct_of_all}% of all, ${stats.monitor_tickets.auto_resolved} auto-resolved)`);
    }
    if (stats.noise_candidates.length > 0) {
      lines.push(`Top noise: ${stats.noise_candidates.slice(0, 3).map(n => `${n.title} (${n.count}×)`).join(', ')}`);
    }
    return lines.join('\n');
  }

  // ──────────────────────────────────────────────────────────────
  // Full Run
  // ──────────────────────────────────────────────────────────────

  async run(period: DigestPeriod, channelIds?: number[]): Promise<{
    stats: TicketDigestStats;
    analysis: string;
    deliveryResults: DeliveryResult[];
    processingTimeMs: number;
  }> {
    const startTime = Date.now();
    const config = await this.getConfig();

    console.log(`[TICKET-DIGEST] Generating ${period} report...`);

    // 1. Aggregate data
    const stats = await this.aggregate(period);
    console.log(`[TICKET-DIGEST] Aggregated: ${stats.overview.total_created} created, ${stats.overview.total_resolved} resolved`);

    // 2. LLM analysis
    let analysis = '';
    let tokensUsed = 0;
    try {
      const llmResult = await this.analyzeWithLLM(stats, config);
      analysis = llmResult.analysis;
      tokensUsed = llmResult.tokensUsed;
      console.log(`[TICKET-DIGEST] LLM analysis complete (${tokensUsed} tokens)`);
    } catch (err) {
      const msg = err instanceof Error ? err.message : String(err);
      console.error(`[TICKET-DIGEST] LLM analysis failed: ${msg}`);
      analysis = `LLM analysis unavailable: ${msg}`;
    }

    // 3. Build card
    const card = this.buildAdaptiveCard(stats, analysis);

    // 4. Persist
    const processingTimeMs = Date.now() - startTime;
    await postgresClient.query(
      `INSERT INTO ticket_digest_reports (period_type, period_start, period_end, stats, llm_analysis, card_payload, tokens_used, processing_time_ms)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
      [period, stats.period.start, stats.period.end, JSON.stringify(stats), analysis, JSON.stringify(card), tokensUsed, processingTimeMs]
    );

    // 5. Deliver
    const deliveryResults = await this.deliver(card, stats, channelIds);
    console.log(`[TICKET-DIGEST] Delivered to ${deliveryResults.filter(r => r.success).length}/${deliveryResults.length} channels`);

    // Update delivery status
    const statusMap: Record<number, object> = {};
    for (const r of deliveryResults) {
      statusMap[r.channelId] = { success: r.success, httpStatus: r.httpStatus, error: r.error };
    }
    await postgresClient.query(
      `UPDATE ticket_digest_reports SET delivery_status = $1
       WHERE id = (SELECT id FROM ticket_digest_reports ORDER BY generated_at DESC LIMIT 1)`,
      [JSON.stringify(statusMap)]
    );

    return { stats, analysis, deliveryResults, processingTimeMs };
  }

  // ──────────────────────────────────────────────────────────────
  // History
  // ──────────────────────────────────────────────────────────────

  async getHistory(limit = 20): Promise<Array<{
    id: number;
    period_type: string;
    period_start: string;
    period_end: string;
    generated_at: string;
    stats: TicketDigestStats;
    llm_analysis: string | null;
    delivery_status: object;
    tokens_used: number | null;
    processing_time_ms: number | null;
  }>> {
    const r = await postgresClient.query(
      'SELECT * FROM ticket_digest_reports ORDER BY generated_at DESC LIMIT $1',
      [limit]
    );
    return r.rows;
  }
}

let _instance: TicketDigestService | null = null;
export function getTicketDigestService(): TicketDigestService {
  if (!_instance) _instance = new TicketDigestService();
  return _instance;
}