← back to Norma

app/api/price-tracker/route.ts

396 lines

import { NextRequest, NextResponse } from 'next/server';
import { query } from '@/lib/db';
import { requireRole } from '@/lib/require-role';

/**
 * GET /api/price-tracker
 * Main data endpoint for Price Tracker tab.
 *
 * ?view=overview|schools|school|alerts|federal|comparison|history
 * ?unit_id=       — for school-specific views
 * ?institution_type= — filter by type
 * ?search=        — text search on school name
 * ?severity=      — filter alerts by severity
 * ?limit=         — pagination (default 50, max 200)
 * ?offset=        — pagination offset
 * ?sort=          — column to sort by
 * ?sort_dir=      — asc|desc
 * ?unit_ids=      — comma-separated for comparison view
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

  try {
    const { searchParams } = new URL(request.url);
    const view = searchParams.get('view') || 'overview';

    switch (view) {
      case 'overview':
        return getOverview();
      case 'schools':
        return getSchools(searchParams);
      case 'school':
        return getSchoolDetail(searchParams);
      case 'alerts':
        return getAlerts(searchParams);
      case 'federal':
        return getFederalRates(searchParams);
      case 'comparison':
        return getComparison(searchParams);
      case 'history':
        return getHistory(searchParams);
      default:
        return NextResponse.json({ error: `Unknown view: ${view}` }, { status: 400 });
    }
  } catch (err: unknown) {
    const message = err instanceof Error ? err.message : 'Unknown error';
    console.error('[api/price-tracker] error:', message);
    return NextResponse.json({ error: 'Failed to fetch price data' }, { status: 500 });
  }
}

async function getOverview() {
  // Average tuition by institution type
  const avgTuition = await query(`
    SELECT institution_type,
           COUNT(DISTINCT unit_id) AS school_count,
           ROUND(AVG(tuition_in_state)) AS avg_tuition_in,
           ROUND(AVG(tuition_out_state)) AS avg_tuition_out,
           ROUND(AVG(net_price_overall)) AS avg_net_price,
           ROUND(AVG(room_board_on_campus)) AS avg_room_board,
           ROUND(AVG(coa_on_campus)) AS avg_coa
    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 DESC NULLS LAST
  `);

  // Active alerts by severity
  const alerts = await query(`
    SELECT severity, COUNT(*) AS count
    FROM cost_alerts
    WHERE NOT is_read
    GROUP BY severity
  `);

  // Latest federal rates
  const fedRates = await query(`
    SELECT rate_type, interest_rate, origination_fee, academic_year
    FROM federal_rates
    WHERE academic_year = (SELECT MAX(academic_year) FROM federal_rates)
    ORDER BY rate_type
  `);

  // Last crawl per type
  const crawls = await query(`
    SELECT DISTINCT ON (crawl_type)
      crawl_type, status, schools_found, schools_updated, started_at, completed_at
    FROM price_crawl_log
    ORDER BY crawl_type, started_at DESC
  `);

  // Total schools tracked
  const totalSchools = await query(`
    SELECT COUNT(DISTINCT unit_id) AS count FROM tuition_history WHERE state = 'CA'
  `);

  return NextResponse.json({
    schools_tracked: parseInt(totalSchools.rows[0]?.count || '0'),
    avg_tuition_by_type: avgTuition.rows,
    active_alerts: alerts.rows,
    federal_rates: fedRates.rows,
    last_crawls: crawls.rows,
  });
}

async function getSchools(params: URLSearchParams) {
  let limit = Math.min(parseInt(params.get('limit') || '50'), 200);
  let offset = parseInt(params.get('offset') || '0');
  if (isNaN(limit) || limit < 1) limit = 50;
  if (isNaN(offset) || offset < 0) offset = 0;

  const search = params.get('search');
  const institutionType = params.get('institution_type');
  const sort = params.get('sort') || 'school_name';
  const sortDir = params.get('sort_dir') === 'desc' ? 'DESC' : 'ASC';

  const allowedSorts = ['school_name', 'tuition_in_state', 'tuition_out_state', 'net_price_overall',
    'room_board_on_campus', 'coa_on_campus', 'median_debt', 'enrollment', 'institution_type'];
  const safeSort = allowedSorts.includes(sort) ? sort : 'school_name';

  const conditions: string[] = [`state = 'CA'`];
  const values: unknown[] = [];
  let idx = 1;

  // Only latest year per school
  conditions.push(`academic_year = (SELECT MAX(academic_year) FROM tuition_history WHERE state = 'CA')`);

  if (search) {
    conditions.push(`school_name ILIKE $${idx}`);
    values.push(`%${search}%`);
    idx++;
  }
  if (institutionType) {
    conditions.push(`institution_type = $${idx}`);
    values.push(institutionType);
    idx++;
  }

  const where = conditions.join(' AND ');

  const countResult = await query(
    `SELECT COUNT(DISTINCT unit_id) AS total FROM tuition_history WHERE ${where}`,
    values,
  );

  const result = await query(
    `SELECT DISTINCT ON (unit_id)
       unit_id, school_name, institution_type,
       tuition_in_state, tuition_out_state, net_price_overall,
       room_board_on_campus, books_supplies, coa_on_campus,
       median_debt, pell_grant_rate, default_rate,
       academic_year, data_source
     FROM tuition_history
     WHERE ${where}
     ORDER BY unit_id, crawled_at DESC`,
    values,
  );

  // Sort in JS since DISTINCT ON requires matching ORDER BY
  const rows = result.rows.sort((a: Record<string, unknown>, b: Record<string, unknown>) => {
    const aVal = a[safeSort] ?? '';
    const bVal = b[safeSort] ?? '';
    if (typeof aVal === 'number' && typeof bVal === 'number') {
      return sortDir === 'ASC' ? aVal - bVal : bVal - aVal;
    }
    return sortDir === 'ASC'
      ? String(aVal).localeCompare(String(bVal))
      : String(bVal).localeCompare(String(aVal));
  });

  // Apply pagination in JS after sort
  const paged = rows.slice(offset, offset + limit);

  return NextResponse.json({
    total: parseInt(countResult.rows[0]?.total || '0'),
    limit,
    offset,
    schools: paged,
  });
}

async function getSchoolDetail(params: URLSearchParams) {
  const unitId = params.get('unit_id');
  if (!unitId) {
    return NextResponse.json({ error: 'unit_id required' }, { status: 400 });
  }

  // Latest data
  const latest = await query(
    `SELECT * FROM tuition_history
     WHERE unit_id = $1
     ORDER BY academic_year DESC, crawled_at DESC
     LIMIT 1`,
    [unitId],
  );

  // All historical data
  const history = await query(
    `SELECT DISTINCT ON (academic_year)
       academic_year, tuition_in_state, tuition_out_state,
       room_board_on_campus, books_supplies, net_price_overall,
       coa_on_campus, median_debt, pell_grant_rate,
       net_price_0_30k, net_price_30_48k, net_price_48_75k,
       net_price_75_110k, net_price_110k_plus,
       data_source, crawled_at
     FROM tuition_history
     WHERE unit_id = $1
     ORDER BY academic_year DESC, crawled_at DESC`,
    [unitId],
  );

  // Alerts for this school
  const alerts = await query(
    `SELECT * FROM cost_alerts
     WHERE unit_id = $1
     ORDER BY created_at DESC
     LIMIT 20`,
    [unitId],
  );

  // College scorecard base data
  const scorecard = await query(
    `SELECT * FROM college_scorecard WHERE unit_id = $1`,
    [unitId],
  );

  return NextResponse.json({
    school: latest.rows[0] || null,
    scorecard: scorecard.rows[0] || null,
    history: history.rows,
    alerts: alerts.rows,
  });
}

async function getAlerts(params: URLSearchParams) {
  let limit = Math.min(parseInt(params.get('limit') || '50'), 200);
  let offset = parseInt(params.get('offset') || '0');
  if (isNaN(limit) || limit < 1) limit = 50;
  if (isNaN(offset) || offset < 0) offset = 0;

  const severity = params.get('severity');
  const conditions: string[] = [];
  const values: unknown[] = [];
  let idx = 1;

  if (severity) {
    conditions.push(`severity = $${idx}`);
    values.push(severity);
    idx++;
  }

  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';

  const countResult = await query(`SELECT COUNT(*) AS total FROM cost_alerts ${where}`, values);

  values.push(limit, offset);
  const result = await query(
    `SELECT * FROM cost_alerts ${where}
     ORDER BY created_at DESC
     LIMIT $${idx} OFFSET $${idx + 1}`,
    values,
  );

  return NextResponse.json({
    total: parseInt(countResult.rows[0]?.total || '0'),
    limit,
    offset,
    alerts: result.rows,
  });
}

async function getFederalRates(params: URLSearchParams) {
  const year = params.get('year');
  const conditions: string[] = [];
  const values: unknown[] = [];

  if (year) {
    conditions.push('academic_year = $1');
    values.push(parseInt(year));
  }

  const where = conditions.length ? `WHERE ${conditions.join(' AND ')}` : '';

  const result = await query(
    `SELECT * FROM federal_rates ${where} ORDER BY academic_year DESC, rate_type`,
    values,
  );

  return NextResponse.json({ rates: result.rows });
}

async function getComparison(params: URLSearchParams) {
  const unitIdsStr = params.get('unit_ids');
  if (!unitIdsStr) {
    return NextResponse.json({ error: 'unit_ids required (comma-separated)' }, { status: 400 });
  }

  const unitIds = unitIdsStr.split(',').map(Number).filter(n => !isNaN(n)).slice(0, 5);

  if (unitIds.length < 2) {
    return NextResponse.json({ error: 'At least 2 unit_ids required' }, { status: 400 });
  }

  const placeholders = unitIds.map((_, i) => `$${i + 1}`).join(',');

  const result = await query(
    `SELECT DISTINCT ON (unit_id)
       unit_id, school_name, institution_type,
       tuition_in_state, tuition_out_state, net_price_overall,
       room_board_on_campus, books_supplies, coa_on_campus, coa_off_campus,
       net_price_0_30k, net_price_30_48k, net_price_48_75k,
       net_price_75_110k, net_price_110k_plus,
       median_debt, pell_grant_rate, default_rate,
       academic_year
     FROM tuition_history
     WHERE unit_id IN (${placeholders})
     ORDER BY unit_id, academic_year DESC, crawled_at DESC`,
    unitIds,
  );

  return NextResponse.json({ schools: result.rows });
}

async function getHistory(params: URLSearchParams) {
  const unitId = params.get('unit_id');
  const institutionType = params.get('institution_type');

  if (unitId) {
    // Single school time series
    const result = await query(
      `SELECT DISTINCT ON (academic_year)
         academic_year, tuition_in_state, tuition_out_state,
         net_price_overall, room_board_on_campus, coa_on_campus, median_debt
       FROM tuition_history
       WHERE unit_id = $1
       ORDER BY academic_year ASC, crawled_at DESC`,
      [unitId],
    );
    return NextResponse.json({ history: result.rows });
  }

  // Aggregated by institution type
  const typeFilter = institutionType ? `AND institution_type = $1` : '';
  const typeValues = institutionType ? [institutionType] : [];

  const result = await query(
    `SELECT academic_year, institution_type,
            ROUND(AVG(tuition_in_state)) AS avg_tuition_in,
            ROUND(AVG(net_price_overall)) AS avg_net_price,
            ROUND(AVG(coa_on_campus)) AS avg_coa,
            COUNT(DISTINCT unit_id) AS school_count
     FROM tuition_history
     WHERE state = 'CA' ${typeFilter}
     GROUP BY academic_year, institution_type
     ORDER BY academic_year ASC, institution_type`,
    typeValues,
  );

  return NextResponse.json({ history: result.rows });
}

/**
 * POST /api/price-tracker — mark alerts as read
 */
export async function POST(request: NextRequest) {
  const auth = requireRole(request, 'admin');
  if (auth instanceof NextResponse) return auth;

  try {
    const body = await request.json();
    const { action, alert_ids } = body;

    if (action === 'mark_read' && Array.isArray(alert_ids)) {
      const placeholders = alert_ids.map((_: string, i: number) => `$${i + 1}`).join(',');
      await query(
        `UPDATE cost_alerts SET is_read = true WHERE id IN (${placeholders})`,
        alert_ids,
      );
      return NextResponse.json({ success: true, marked: alert_ids.length });
    }

    if (action === 'mark_all_read') {
      const result = await query(`UPDATE cost_alerts SET is_read = true WHERE NOT is_read`);
      return NextResponse.json({ success: true, marked: result.rowCount });
    }

    return NextResponse.json({ error: 'Unknown action' }, { status: 400 });
  } catch (err: unknown) {
    const message = err instanceof Error ? err.message : 'Unknown error';
    console.error('[api/price-tracker] POST error:', message);
    return NextResponse.json({ error: 'Failed' }, { status: 500 });
  }
}