`app/api/price-tracker/stats/route.ts`

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

/**
 * GET /api/price-tracker/stats
 * Dashboard widget stats for Price Tracker overview cards.
 */
export async function GET(request: NextRequest) {
  const auth = requireRole(request, 'admin', 'staff');
  if (auth instanceof NextResponse) return auth;

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

    // Active unread alerts
    const alertsResult = await query(
      `SELECT COUNT(*) AS count FROM cost_alerts WHERE NOT is_read`,
    );

    // Latest crawl timestamp
    const crawlResult = await query(
      `SELECT MAX(completed_at) AS latest FROM price_crawl_log WHERE status = 'success'`,
    );

    // Average tuition by type (latest year)
    const avgResult = await query(`
      SELECT institution_type,
             ROUND(AVG(tuition_in_state)) AS avg_tuition
      FROM tuition_history
      WHERE state = 'CA'
        AND academic_year = (SELECT MAX(academic_year) FROM tuition_history WHERE state = 'CA')
      GROUP BY institution_type
    `);

    // YoY average tuition change
    const yoyResult = await query(`
      WITH years AS (
        SELECT DISTINCT academic_year FROM tuition_history WHERE state = 'CA'
        ORDER BY academic_year DESC LIMIT 2
      ),
      curr AS (
        SELECT AVG(tuition_in_state) AS avg_t
        FROM tuition_history
        WHERE state = 'CA' AND academic_year = (SELECT MAX(academic_year) FROM years)
      ),
      prev AS (
        SELECT AVG(tuition_in_state) AS avg_t
        FROM tuition_history
        WHERE state = 'CA' AND academic_year = (SELECT MIN(academic_year) FROM years)
      )
      SELECT
        CASE WHEN prev.avg_t > 0
          THEN ROUND(((curr.avg_t - prev.avg_t) / prev.avg_t * 100)::numeric, 1)
          ELSE NULL
        END AS yoy_change_pct
      FROM curr, prev
    `);

    // Current federal rate (Direct Subsidized undergrad)
    const fedResult = await query(`
      SELECT interest_rate FROM federal_rates
      WHERE academic_year = (SELECT MAX(academic_year) FROM federal_rates)
        AND rate_type LIKE '%sub%'
      ORDER BY rate_type LIMIT 1
    `);

    // Data source count
    const sourceResult = await query(
      `SELECT COUNT(DISTINCT data_source) AS count FROM tuition_history`,
    );

    const avgByType: Record<string, number> = {};
    for (const row of avgResult.rows) {
      avgByType[row.institution_type] = parseInt(row.avg_tuition || '0');
    }

    return NextResponse.json({
      schools_tracked: parseInt(schoolsResult.rows[0]?.count || '0'),
      data_sources: parseInt(sourceResult.rows[0]?.count || '0'),
      active_alerts: parseInt(alertsResult.rows[0]?.count || '0'),
      latest_crawl: crawlResult.rows[0]?.latest || null,
      avg_tuition_by_type: avgByType,
      yoy_change_pct: parseFloat(yoyResult.rows[0]?.yoy_change_pct || '0'),
      current_fed_rate: parseFloat(fedResult.rows[0]?.interest_rate || '0'),
    });
  } catch (err: unknown) {
    const message = err instanceof Error ? err.message : 'Unknown error';
    console.error('[api/price-tracker/stats] error:', message);
    return NextResponse.json({ error: 'Failed to fetch stats' }, { status: 500 });
  }
}