`agents/price-agent/skills/crawl-uc.js`

264 lines
/**
 * crawl-uc.js — Post skill
 *
 * Scrapes UC system tuition/fee pages for each campus.
 * Parses HTML with cheerio-based parser, falls back to Gemini AI enrichment
 * when parsing returns incomplete data.
 */

const fetch = require('node-fetch');
const { query } = require('../../shared/db');
const { logAction } = require('../../shared/audit-logger');
const { UC_CAMPUSES } = require('../lib/ca-schools-registry');
const { parseUCCostsPage } = require('../lib/html-parser');

const AGENT_NAME = 'price-agent';
const FETCH_DELAY_MS = 2000;
const MIN_FIELDS_THRESHOLD = 3;

function sleep(ms) {
  return new Promise((resolve) => setTimeout(resolve, ms));
}

/**
 * Determine current academic year (fall-term year).
 */
function currentAcademicYear() {
  const now = new Date();
  return now.getMonth() >= 6 ? now.getFullYear() : now.getFullYear() - 1;
}

/**
 * Count non-null cost fields in a parsed result.
 */
function countCostFields(parsed) {
  if (!parsed || typeof parsed !== 'object') return 0;
  const costKeys = [
    'tuition_in_state', 'tuition_out_state', 'mandatory_fees',
    'room_board_on_campus', 'room_board_off_campus',
    'books_supplies', 'other_expenses', 'transportation',
    'coa_on_campus', 'coa_off_campus',
  ];
  return costKeys.filter((k) => parsed[k] != null).length;
}

module.exports = async function crawlUC(body = {}) {
  const startTime = Date.now();
  const academicYear = (body && body.academic_year) || currentAcademicYear();

  // 1. Log crawl start
  const crawlLogRes = await query(
    `INSERT INTO price_crawl_log (crawl_type, status, metadata)
     VALUES ('uc_scrape', 'running', $1)
     RETURNING id`,
    [JSON.stringify({ academic_year: academicYear })]
  );
  const crawlLogId = crawlLogRes.rows[0].id;

  let campusesCrawled = 0;
  let campusesWithData = 0;
  const errors = [];

  try {
    // Lazy-load enrich skill for fallback
    let enrichSkill = null;
    try {
      enrichSkill = require('./enrich');
    } catch (e) {
      console.warn('[crawl-uc] Enrich skill not available for fallback:', e.message);
    }

    for (const campus of UC_CAMPUSES) {
      const campusName = campus.name || campus.school_name;
      console.log(`[crawl-uc] Crawling ${campusName}...`);

      try {
        if (!campus.costsUrl) {
          errors.push({ campus: campusName, error: 'No costsUrl configured' });
          continue;
        }

        // 3. Fetch the costs page
        const resp = await fetch(campus.costsUrl, {
          headers: {
            'User-Agent': 'Mozilla/5.0 (compatible; NormaPriceAgent/1.0; education-research)',
            'Accept': 'text/html,application/xhtml+xml',
            'Accept-Language': 'en-US,en;q=0.9',
          },
          timeout: 20000,
          redirect: 'follow',
        });

        if (!resp.ok) {
          errors.push({ campus: campusName, error: `HTTP ${resp.status}` });
          console.warn(`[crawl-uc] ${campusName}: HTTP ${resp.status}`);
          campusesCrawled++;
          await sleep(FETCH_DELAY_MS);
          continue;
        }

        const html = await resp.text();
        campusesCrawled++;

        // 4. Parse HTML
        let parsed = null;
        try {
          parsed = parseUCCostsPage(html, campusName);
        } catch (parseErr) {
          console.warn(`[crawl-uc] ${campusName}: Parse error:`, parseErr.message);
        }

        // 5. If parsing returned incomplete data, try enrich as fallback
        if (countCostFields(parsed) < MIN_FIELDS_THRESHOLD && enrichSkill) {
          console.log(`[crawl-uc] ${campusName}: Only ${countCostFields(parsed)} fields, trying Gemini fallback...`);
          try {
            const enriched = await enrichSkill({
              html: html,
              school_name: campusName,
              campus_system: 'UC',
            });
            if (enriched && countCostFields(enriched) > countCostFields(parsed)) {
              parsed = enriched;
              console.log(`[crawl-uc] ${campusName}: Gemini returned ${countCostFields(parsed)} fields`);
            }
          } catch (enrichErr) {
            console.warn(`[crawl-uc] ${campusName}: Gemini fallback failed:`, enrichErr.message);
          }
        }

        if (!parsed || countCostFields(parsed) === 0) {
          errors.push({ campus: campusName, error: 'No cost data extracted' });
          await sleep(FETCH_DELAY_MS);
          continue;
        }

        // 6. Upsert into tuition_history
        const unitId = campus.unit_id || campus.unitId;
        const resolvedYear = parsed.academic_year || academicYear;

        await query(
          `INSERT INTO tuition_history (
            unit_id, school_name, state, institution_type, academic_year, data_source,
            tuition_in_state, tuition_out_state, mandatory_fees,
            tuition_and_fees_in, tuition_and_fees_out,
            room_board_on_campus, room_board_off_campus,
            books_supplies, other_expenses, transportation,
            coa_on_campus, coa_off_campus,
            raw_data
          ) VALUES (
            $1,$2,'CA','4year_public',$3,'uc_scrape',
            $4,$5,$6,
            $7,$8,
            $9,$10,
            $11,$12,$13,
            $14,$15,
            $16
          )
          ON CONFLICT (unit_id, academic_year, data_source) DO UPDATE SET
            school_name = EXCLUDED.school_name,
            tuition_in_state = COALESCE(EXCLUDED.tuition_in_state, tuition_history.tuition_in_state),
            tuition_out_state = COALESCE(EXCLUDED.tuition_out_state, tuition_history.tuition_out_state),
            mandatory_fees = COALESCE(EXCLUDED.mandatory_fees, tuition_history.mandatory_fees),
            tuition_and_fees_in = COALESCE(EXCLUDED.tuition_and_fees_in, tuition_history.tuition_and_fees_in),
            tuition_and_fees_out = COALESCE(EXCLUDED.tuition_and_fees_out, tuition_history.tuition_and_fees_out),
            room_board_on_campus = COALESCE(EXCLUDED.room_board_on_campus, tuition_history.room_board_on_campus),
            room_board_off_campus = COALESCE(EXCLUDED.room_board_off_campus, tuition_history.room_board_off_campus),
            books_supplies = COALESCE(EXCLUDED.books_supplies, tuition_history.books_supplies),
            other_expenses = COALESCE(EXCLUDED.other_expenses, tuition_history.other_expenses),
            transportation = COALESCE(EXCLUDED.transportation, tuition_history.transportation),
            coa_on_campus = COALESCE(EXCLUDED.coa_on_campus, tuition_history.coa_on_campus),
            coa_off_campus = COALESCE(EXCLUDED.coa_off_campus, tuition_history.coa_off_campus),
            raw_data = EXCLUDED.raw_data`,
          [
            unitId,
            campusName,
            resolvedYear,
            parsed.tuition_in_state || null,
            parsed.tuition_out_state || null,
            parsed.mandatory_fees || null,
            parsed.tuition_and_fees_in || null,
            parsed.tuition_and_fees_out || null,
            parsed.room_board_on_campus || null,
            parsed.room_board_off_campus || null,
            parsed.books_supplies || null,
            parsed.other_expenses || null,
            parsed.transportation || null,
            parsed.coa_on_campus || null,
            parsed.coa_off_campus || null,
            JSON.stringify({ source_url: campus.costsUrl, parsed }),
          ]
        );

        campusesWithData++;
        console.log(`[crawl-uc] ${campusName}: ${countCostFields(parsed)} cost fields saved`);
      } catch (campusErr) {
        errors.push({ campus: campusName, error: campusErr.message });
        console.error(`[crawl-uc] ${campusName}: Error:`, campusErr.message);
      }

      // Polite delay between campuses
      await sleep(FETCH_DELAY_MS);
    }

    // 7. Update crawl log
    const durationMs = Date.now() - startTime;
    await query(
      `UPDATE price_crawl_log
       SET status = 'completed',
           schools_found = $1,
           schools_updated = $2,
           duration_ms = $3,
           metadata = metadata || $4,
           completed_at = NOW()
       WHERE id = $5`,
      [
        campusesCrawled,
        campusesWithData,
        durationMs,
        JSON.stringify({ errors: errors.slice(0, 20) }),
        crawlLogId,
      ]
    );

    await logAction({
      agent: AGENT_NAME,
      actionType: 'crawl',
      platform: 'uc_system',
      content: `Crawled ${campusesCrawled} UC campuses, ${campusesWithData} with data`,
      responseData: { campuses_crawled: campusesCrawled, campuses_with_data: campusesWithData, errors: errors.length },
      status: errors.length > 0 ? 'partial' : 'success',
      errorMessage: errors.length > 0 ? `${errors.length} campus errors` : null,
    });

    console.log(`[crawl-uc] Complete: ${campusesWithData}/${campusesCrawled} campuses with data in ${durationMs}ms`);

    return {
      campuses_crawled: campusesCrawled,
      campuses_with_data: campusesWithData,
      errors,
      crawl_log_id: crawlLogId,
      duration_ms: durationMs,
    };
  } catch (err) {
    const durationMs = Date.now() - startTime;
    await query(
      `UPDATE price_crawl_log
       SET status = 'error', error_message = $1, duration_ms = $2, completed_at = NOW()
       WHERE id = $3`,
      [err.message, durationMs, crawlLogId]
    ).catch(() => {});

    await logAction({
      agent: AGENT_NAME,
      actionType: 'crawl',
      platform: 'uc_system',
      content: 'UC crawl failed',
      status: 'error',
      errorMessage: err.message,
    }).catch(() => {});

    console.error('[crawl-uc] Fatal error:', err);
    throw err;
  }
};