`agents/price-agent/skills/report.js`

233 lines
/**
 * report.js — Report skill
 *
 * Generates a comprehensive summary report of tuition data,
 * alerts, federal rates, and crawl status. Pushes status
 * to Pulse agent.
 */

const { query } = require('../../shared/db');
const { logAction } = require('../../shared/audit-logger');
const { reportAgentStatus } = require('../../shared/pulse-reporter');

const AGENT_NAME = 'price-agent';

module.exports = async function report(body = {}) {
  const startTime = Date.now();

  try {
    // 1. Total CA schools in tuition_history
    const totalSchoolsRes = await query(
      `SELECT COUNT(DISTINCT unit_id) as total
       FROM tuition_history
       WHERE state = 'CA'`
    );
    const totalSchools = parseInt(totalSchoolsRes.rows[0].total, 10) || 0;

    // 2. Average tuition by institution_type
    const avgTuitionRes = await query(
      `SELECT
         institution_type,
         COUNT(DISTINCT unit_id) as school_count,
         ROUND(AVG(tuition_in_state)) as avg_tuition_in_state,
         ROUND(AVG(tuition_out_state)) as avg_tuition_out_state,
         ROUND(AVG(room_board_on_campus)) as avg_room_board,
         ROUND(AVG(coa_on_campus)) as avg_coa_on_campus,
         ROUND(AVG(net_price_overall)) as avg_net_price,
         MIN(academic_year) as earliest_year,
         MAX(academic_year) as latest_year
       FROM tuition_history
       WHERE state = 'CA'
         AND academic_year = (
           SELECT MAX(academic_year)
           FROM tuition_history
           WHERE state = 'CA'
         )
       GROUP BY institution_type
       ORDER BY avg_tuition_in_state DESC NULLS LAST`
    );
    const avgTuitionByType = avgTuitionRes.rows.map((r) => ({
      institution_type: r.institution_type,
      school_count: parseInt(r.school_count, 10),
      avg_tuition_in_state: parseInt(r.avg_tuition_in_state, 10) || null,
      avg_tuition_out_state: parseInt(r.avg_tuition_out_state, 10) || null,
      avg_room_board: parseInt(r.avg_room_board, 10) || null,
      avg_coa_on_campus: parseInt(r.avg_coa_on_campus, 10) || null,
      avg_net_price: parseInt(r.avg_net_price, 10) || null,
      earliest_year: r.earliest_year,
      latest_year: r.latest_year,
    }));

    // 3. Active (unread) alerts by severity
    const alertsRes = await query(
      `SELECT
         severity,
         COUNT(*)::int as count,
         MAX(created_at) as latest_at
       FROM cost_alerts
       WHERE is_read = false
       GROUP BY severity
       ORDER BY
         CASE severity
           WHEN 'critical' THEN 1
           WHEN 'warning' THEN 2
           WHEN 'info' THEN 3
           ELSE 4
         END`
    );
    const activeAlerts = alertsRes.rows.map((r) => ({
      severity: r.severity,
      count: r.count,
      latest_at: r.latest_at,
    }));

    const totalActiveAlerts = activeAlerts.reduce((sum, a) => sum + a.count, 0);

    // 4. Latest federal rates
    const federalRatesRes = await query(
      `SELECT rate_type, interest_rate, origination_fee, academic_year, data_source
       FROM federal_rates
       WHERE academic_year = (SELECT MAX(academic_year) FROM federal_rates)
       ORDER BY rate_type`
    );
    const latestFederalRates = federalRatesRes.rows.map((r) => ({
      rate_type: r.rate_type,
      interest_rate: parseFloat(r.interest_rate),
      origination_fee: r.origination_fee ? parseFloat(r.origination_fee) : null,
      academic_year: r.academic_year,
      data_source: r.data_source,
    }));

    // 5. Last crawl timestamp per crawl_type
    const crawlStatusRes = await query(
      `SELECT DISTINCT ON (crawl_type)
         crawl_type, status, schools_found, schools_updated,
         duration_ms, started_at, completed_at, error_message
       FROM price_crawl_log
       ORDER BY crawl_type, started_at DESC`
    );
    const crawlStatus = crawlStatusRes.rows.map((r) => ({
      crawl_type: r.crawl_type,
      status: r.status,
      schools_found: r.schools_found,
      schools_updated: r.schools_updated,
      duration_ms: r.duration_ms,
      started_at: r.started_at,
      completed_at: r.completed_at,
      error_message: r.error_message,
    }));

    // Get data coverage stats
    const coverageRes = await query(
      `SELECT
         COUNT(DISTINCT unit_id) as schools_with_data,
         COUNT(DISTINCT academic_year) as years_of_data,
         MIN(academic_year) as earliest_year,
         MAX(academic_year) as latest_year,
         COUNT(*) as total_records,
         COUNT(DISTINCT data_source) as source_count
       FROM tuition_history
       WHERE state = 'CA'`
    );
    const coverage = coverageRes.rows[0] || {};

    // Top 10 most expensive schools (latest year)
    const topExpensiveRes = await query(
      `SELECT school_name, institution_type, tuition_in_state, tuition_out_state,
              coa_on_campus, academic_year
       FROM tuition_history
       WHERE state = 'CA'
         AND academic_year = (SELECT MAX(academic_year) FROM tuition_history WHERE state = 'CA')
         AND tuition_in_state IS NOT NULL
       ORDER BY tuition_in_state DESC
       LIMIT 10`
    );
    const topExpensive = topExpensiveRes.rows;

    // Top 10 largest year-over-year increases (from cost_alerts)
    const topIncreasesRes = await query(
      `SELECT school_name, metric, previous_value, new_value, change_pct,
              academic_year, severity, created_at
       FROM cost_alerts
       WHERE change_pct > 0
       ORDER BY change_pct DESC
       LIMIT 10`
    );
    const topIncreases = topIncreasesRes.rows;

    // Build the full report
    const durationMs = Date.now() - startTime;

    const reportData = {
      generated_at: new Date().toISOString(),
      duration_ms: durationMs,

      summary: {
        total_ca_schools: totalSchools,
        total_active_alerts: totalActiveAlerts,
        data_coverage: {
          schools_with_data: parseInt(coverage.schools_with_data, 10) || 0,
          years_of_data: parseInt(coverage.years_of_data, 10) || 0,
          earliest_year: coverage.earliest_year,
          latest_year: coverage.latest_year,
          total_records: parseInt(coverage.total_records, 10) || 0,
          source_count: parseInt(coverage.source_count, 10) || 0,
        },
      },

      avg_tuition_by_type: avgTuitionByType,
      active_alerts: activeAlerts,
      latest_federal_rates: latestFederalRates,
      crawl_status: crawlStatus,
      top_expensive_schools: topExpensive,
      top_increases: topIncreases,
    };

    // 6. Push status to Pulse
    await reportAgentStatus(AGENT_NAME, {
      state: 'running',
      lastAction: {
        type: 'report',
        timestamp: new Date().toISOString(),
        summary: `${totalSchools} schools tracked, ${totalActiveAlerts} active alerts`,
      },
      metrics: {
        total_schools: totalSchools,
        active_alerts: totalActiveAlerts,
        federal_rates_count: latestFederalRates.length,
        crawl_types: crawlStatus.length,
      },
    }).catch((pulseErr) => {
      console.warn('[report] Pulse status push failed:', pulseErr.message);
    });

    await logAction({
      agent: AGENT_NAME,
      actionType: 'report',
      platform: 'price_monitor',
      content: `Generated report: ${totalSchools} schools, ${totalActiveAlerts} active alerts, ${latestFederalRates.length} federal rate types`,
      responseData: {
        total_schools: totalSchools,
        total_alerts: totalActiveAlerts,
      },
      status: 'success',
    });

    console.log(`[report] Generated in ${durationMs}ms: ${totalSchools} schools, ${totalActiveAlerts} alerts`);

    return reportData;
  } catch (err) {
    await logAction({
      agent: AGENT_NAME,
      actionType: 'report',
      platform: 'price_monitor',
      content: 'Report generation failed',
      status: 'error',
      errorMessage: err.message,
    }).catch(() => {});

    console.error('[report] Fatal error:', err);
    throw err;
  }
};