`src/app/api/dashboard/route.ts`

92 lines
import { NextRequest, NextResponse } from "next/server";
import { query } from "@/lib/db";
import { getSession } from "@/lib/auth";

export const dynamic = "force-dynamic";

export async function GET(request: NextRequest) {
  const session = await getSession(request);
  if (!session) {
    return NextResponse.json(
      { success: false, error: { code: "UNAUTHORIZED", message: "Login required" } },
      { status: 401 }
    );
  }

  // Recent collector runs (last 10)
  const recentRuns = await query(`
    SELECT cr.run_id, ds.display_name as source_name, cr.job_type, cr.status,
           cr.records_fetched, cr.records_inserted, cr.records_quarantined,
           cr.duration_ms, cr.started_at, cr.error_message
    FROM collector_run cr
    JOIN data_source ds ON cr.source_id = ds.id
    ORDER BY cr.started_at DESC
    LIMIT 10
  `);

  // Collection-level price summary
  const collectionSummary = await query(`
    SELECT wr.collection,
           COUNT(DISTINCT wr.id) as ref_count,
           COUNT(me.event_id) as event_count,
           AVG(me.price_usd)::NUMERIC(14,2) as avg_market_price,
           MIN(me.price_usd)::NUMERIC(14,2) as min_price,
           MAX(me.price_usd)::NUMERIC(14,2) as max_price
    FROM watch_reference wr
    LEFT JOIN market_event me ON me.reference_id = wr.id AND NOT me.is_quarantined AND me.price_usd > 0
    GROUP BY wr.collection
    ORDER BY event_count DESC
  `);

  // Recent quarantine alerts (unresolved, last 5)
  const recentAlerts = await query(`
    SELECT qr.id, qr.severity, qr.reason as field_name, qr.details,
           me.reference_number_raw, me.source_name, qr.created_at
    FROM quarantine_record qr
    JOIN market_event me ON qr.event_id = me.event_id
    WHERE qr.resolved = false
    ORDER BY qr.created_at DESC
    LIMIT 5
  `);

  // 24-hour collection stats
  const stats24h = await query(`
    SELECT
      COUNT(*) as runs_24h,
      COUNT(*) FILTER (WHERE status = 'completed') as success_24h,
      COUNT(*) FILTER (WHERE status = 'failed') as failed_24h,
      COALESCE(SUM(records_inserted), 0) as inserted_24h,
      COALESCE(SUM(records_quarantined), 0) as quarantined_24h
    FROM collector_run
    WHERE started_at > NOW() - INTERVAL '24 hours'
  `);

  // Top movers — references with highest price variance in last 30 days
  const topMovers = await query(`
    SELECT wr.reference_number, wr.collection, wr.model_name,
           COUNT(*) as events_30d,
           AVG(me.price_usd)::NUMERIC(14,2) as avg_price,
           STDDEV(me.price_usd)::NUMERIC(14,2) as price_stddev
    FROM market_event me
    JOIN watch_reference wr ON me.reference_id = wr.id
    WHERE me.sale_date > CURRENT_DATE - 30
      AND NOT me.is_quarantined
      AND me.price_usd > 0
    GROUP BY wr.id, wr.reference_number, wr.collection, wr.model_name
    HAVING COUNT(*) >= 2
    ORDER BY STDDEV(me.price_usd) DESC NULLS LAST
    LIMIT 5
  `);

  return NextResponse.json({
    success: true,
    data: {
      recent_runs: recentRuns,
      collection_summary: collectionSummary,
      recent_alerts: recentAlerts,
      stats_24h: stats24h[0],
      top_movers: topMovers,
    },
  });
}