← back to Norma

agents/datasource-agent/skills/report.js

162 lines

/**
 * Report Skill
 *
 * Returns statistics across all data source tables:
 * - discovered_apis by status
 * - newspaper_frontpages by country for today
 * - nonprofit_statements from last 24h
 */

const { logAction } = require('../../shared/audit-logger');

const AGENT = 'datasource-agent';
const PLATFORM = 'report';

const DB_URL = process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/sdcc';

let _pool;
function getPool() {
  if (!_pool) {
    const { Pool } = require('pg');
    _pool = new Pool({ connectionString: DB_URL, max: 5 });
  }
  return _pool;
}

/**
 * Main report skill handler.
 *
 * @param {Object} body - Request body
 * @param {number} [body.hours_back=24] - How many hours back for statement count
 * @returns {Promise<Object>} Report data
 */
module.exports = async function report(body = {}) {
  const hoursBack = body.hours_back || 24;
  const pool = getPool();

  // 1. discovered_apis by status
  let apiStats = {};
  try {
    const { rows } = await pool.query(
      `SELECT status, COUNT(*)::int as count, ROUND(AVG(relevance_score)::numeric, 1) as avg_score
       FROM discovered_apis
       GROUP BY status
       ORDER BY count DESC`
    );
    apiStats = {
      by_status: rows.reduce((acc, r) => {
        acc[r.status] = { count: r.count, avg_score: parseFloat(r.avg_score) || 0 };
        return acc;
      }, {}),
      total: rows.reduce((sum, r) => sum + r.count, 0),
    };
  } catch (err) {
    console.error(`[${AGENT}] Error getting API stats:`, err.message);
    apiStats = { error: err.message, total: 0 };
  }

  // 2. newspaper_frontpages by country for today
  let frontpageStats = {};
  try {
    const { rows } = await pool.query(
      `SELECT country, source_type, COUNT(*)::int as count,
              ROUND(AVG(relevance_score)::numeric, 1) as avg_score
       FROM newspaper_frontpages
       WHERE scraped_date = CURRENT_DATE
       GROUP BY country, source_type
       ORDER BY count DESC`
    );
    frontpageStats = {
      by_country: rows.reduce((acc, r) => {
        acc[r.country] = { count: r.count, avg_score: parseFloat(r.avg_score) || 0, source_type: r.source_type };
        return acc;
      }, {}),
      total_today: rows.reduce((sum, r) => sum + r.count, 0),
    };
  } catch (err) {
    console.error(`[${AGENT}] Error getting frontpage stats:`, err.message);
    frontpageStats = { error: err.message, total_today: 0 };
  }

  // 3. nonprofit_statements from last N hours
  let statementStats = {};
  try {
    const { rows } = await pool.query(
      `SELECT org_name, COUNT(*)::int as count, sentiment,
              ROUND(AVG(relevance_score)::numeric, 1) as avg_score
       FROM nonprofit_statements
       WHERE created_at > NOW() - INTERVAL '1 hour' * $1
       GROUP BY org_name, sentiment
       ORDER BY count DESC`,
      [hoursBack]
    );

    const totalRecent = rows.reduce((sum, r) => sum + r.count, 0);
    statementStats = {
      by_org: rows.reduce((acc, r) => {
        if (!acc[r.org_name]) acc[r.org_name] = { count: 0, sentiments: {} };
        acc[r.org_name].count += r.count;
        acc[r.org_name].sentiments[r.sentiment || 'unknown'] = r.count;
        acc[r.org_name].avg_score = parseFloat(r.avg_score) || 0;
        return acc;
      }, {}),
      total_recent: totalRecent,
      hours_back: hoursBack,
    };
  } catch (err) {
    console.error(`[${AGENT}] Error getting statement stats:`, err.message);
    statementStats = { error: err.message, total_recent: 0 };
  }

  // 4. Top-rated items across all sources
  let topRated = [];
  try {
    const { rows } = await pool.query(
      `(SELECT 'api' as source, name as title, relevance_score, match_topics, created_at
        FROM discovered_apis WHERE relevance_score > 50 ORDER BY relevance_score DESC LIMIT 5)
       UNION ALL
       (SELECT 'frontpage' as source, newspaper_name || ': ' || COALESCE(top_headline, '') as title,
               relevance_score, match_topics, created_at
        FROM newspaper_frontpages WHERE relevance_score > 30 AND scraped_date = CURRENT_DATE
        ORDER BY relevance_score DESC LIMIT 5)
       UNION ALL
       (SELECT 'statement' as source, org_name || ': ' || statement_title as title,
               relevance_score, match_topics, created_at
        FROM nonprofit_statements WHERE relevance_score > 30
        AND created_at > NOW() - INTERVAL '48 hours'
        ORDER BY relevance_score DESC LIMIT 5)
       ORDER BY relevance_score DESC
       LIMIT 10`
    );
    topRated = rows.map((r) => ({
      source: r.source,
      title: (r.title || '').substring(0, 200),
      score: r.relevance_score,
      topics: r.match_topics || [],
    }));
  } catch (err) {
    console.error(`[${AGENT}] Error getting top rated:`, err.message);
  }

  await logAction({
    agent: AGENT,
    actionType: 'report',
    platform: PLATFORM,
    content: 'Generated data source report',
    responseData: {
      apis_total: apiStats.total || 0,
      frontpages_today: frontpageStats.total_today || 0,
      statements_recent: statementStats.total_recent || 0,
    },
    status: 'success',
  });

  return {
    apis: apiStats,
    frontpages: frontpageStats,
    statements: statementStats,
    top_rated: topRated,
    generated_at: new Date().toISOString(),
  };
};