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

193 lines
/**
 * crawl-ccc.js — Custom route skill
 *
 * California Community Colleges have a statewide per-unit fee.
 * This skill calculates the annual cost and inserts a tuition_history
 * row for every CA community college in the scorecard table.
 */

const { query } = require('../../shared/db');
const { logAction } = require('../../shared/audit-logger');
const { CCC_CONFIG } = require('../lib/ca-schools-registry');

const AGENT_NAME = 'price-agent';

function currentAcademicYear() {
  const now = new Date();
  return now.getMonth() >= 6 ? now.getFullYear() : now.getFullYear() - 1;
}

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

  // 1. Get CCC config from registry
  const config = CCC_CONFIG;
  const feePerUnit = config.statewideFeePerUnit || config.fee_per_unit || 46; // $46/unit as of 2025-2026
  const fullTimeUnits = config.fullTimeUnitsPerYear || config.full_time_units || 30; // 30 units/year typical full-time

  // 2. Calculate annual tuition
  const annualTuition = Math.round(feePerUnit * fullTimeUnits);

  console.log(`[crawl-ccc] Fee per unit: $${feePerUnit}, Full-time units: ${fullTimeUnits}, Annual: $${annualTuition}`);

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

  let collegesUpdated = 0;
  const errors = [];

  try {
    // 4. Query all CA community colleges from college_scorecard
    const { rows: colleges } = await query(
      `SELECT unit_id, school_name
       FROM college_scorecard
       WHERE state = 'CA'
         AND institution_type = 'community_college'
       ORDER BY school_name`
    );

    console.log(`[crawl-ccc] Found ${colleges.length} CA community colleges in scorecard`);

    if (colleges.length === 0) {
      // If no scorecard data yet, log and return
      const durationMs = Date.now() - startTime;
      await query(
        `UPDATE price_crawl_log
         SET status = 'completed',
             schools_found = 0, schools_updated = 0,
             duration_ms = $1, error_message = 'No community colleges found in scorecard — run scorecard crawl first',
             completed_at = NOW()
         WHERE id = $2`,
        [durationMs, crawlLogId]
      );

      return {
        colleges_updated: 0,
        annual_fee_calculated: annualTuition,
        crawl_log_id: crawlLogId,
        message: 'No community colleges in scorecard. Run scorecard crawl first.',
      };
    }

    // 5. For each community college, insert tuition_history
    for (const college of colleges) {
      try {
        await query(
          `INSERT INTO tuition_history (
            unit_id, school_name, state, institution_type, academic_year, data_source,
            tuition_in_state, per_unit_fee,
            raw_data
          ) VALUES (
            $1, $2, 'CA', 'community_college', $3, 'ccc_calc',
            $4, $5,
            $6
          )
          ON CONFLICT (unit_id, academic_year, data_source) DO UPDATE SET
            school_name = EXCLUDED.school_name,
            tuition_in_state = EXCLUDED.tuition_in_state,
            per_unit_fee = EXCLUDED.per_unit_fee,
            raw_data = EXCLUDED.raw_data`,
          [
            college.unit_id,
            college.school_name,
            academicYear,
            annualTuition,
            feePerUnit,
            JSON.stringify({
              calculation: 'statewide_fee',
              fee_per_unit: feePerUnit,
              full_time_units: fullTimeUnits,
              annual_tuition: annualTuition,
              enrollment_fee_only: true,
              note: 'CCC statewide enrollment fee; does not include student activity, health, or campus-specific fees',
            }),
          ]
        );

        collegesUpdated++;
      } catch (err) {
        errors.push({ unit_id: college.unit_id, school: college.school_name, error: err.message });
        console.error(`[crawl-ccc] Error for ${college.school_name}:`, err.message);
      }
    }

    // 6. 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`,
      [
        colleges.length,
        collegesUpdated,
        durationMs,
        JSON.stringify({ errors: errors.slice(0, 20) }),
        crawlLogId,
      ]
    );

    await logAction({
      agent: AGENT_NAME,
      actionType: 'crawl',
      platform: 'ccc_system',
      content: `Calculated CCC tuition for ${collegesUpdated} colleges ($${annualTuition}/year at $${feePerUnit}/unit)`,
      responseData: {
        colleges_updated: collegesUpdated,
        annual_tuition: annualTuition,
        fee_per_unit: feePerUnit,
      },
      status: errors.length > 0 ? 'partial' : 'success',
      errorMessage: errors.length > 0 ? `${errors.length} colleges had errors` : null,
    });

    console.log(`[crawl-ccc] Complete: ${collegesUpdated} colleges updated with $${annualTuition}/year in ${durationMs}ms`);

    return {
      colleges_updated: collegesUpdated,
      annual_fee_calculated: annualTuition,
      fee_per_unit: feePerUnit,
      full_time_units: fullTimeUnits,
      crawl_log_id: crawlLogId,
      errors_count: errors.length,
      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: 'ccc_system',
      content: 'CCC calculation failed',
      status: 'error',
      errorMessage: err.message,
    }).catch(() => {});

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