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

72 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 }
    );
  }

  const { searchParams } = new URL(request.url);
  const collection = searchParams.get("collection");
  const search = searchParams.get("q");

  let where = "WHERE 1=1";
  const params: any[] = [];
  let idx = 1;

  if (collection) {
    where += ` AND wr.collection = $${idx++}`;
    params.push(collection);
  }
  if (search) {
    where += ` AND (wr.reference_number ILIKE $${idx} OR wr.model_name ILIKE $${idx} OR wr.collection ILIKE $${idx})`;
    params.push(`%${search}%`);
    idx++;
  }

  const references = await query(
    `SELECT
      wr.*,
      (SELECT COUNT(*) FROM market_event me WHERE me.reference_id = wr.id) AS event_count,
      (SELECT COUNT(*) FROM msrp_snapshot ms WHERE ms.reference_id = wr.id) AS msrp_count,
      (SELECT ms.msrp_amount FROM msrp_snapshot ms
       WHERE ms.reference_id = wr.id AND ms.region_code = 'US'
       ORDER BY ms.captured_date DESC LIMIT 1) AS latest_msrp,
      (SELECT AVG(me.price_usd) FROM market_event me
       WHERE me.reference_id = wr.id AND me.price_usd IS NOT NULL
       AND me.event_type IN ('auction_realized', 'marketplace_sold')) AS avg_market_price,
      (SELECT me.price_usd FROM market_event me
       WHERE me.reference_id = wr.id AND me.price_usd IS NOT NULL
       ORDER BY me.sale_date DESC LIMIT 1) AS latest_sale_price,
      (SELECT me.sale_date FROM market_event me
       WHERE me.reference_id = wr.id AND me.sale_date IS NOT NULL
       ORDER BY me.sale_date DESC LIMIT 1) AS latest_sale_date
    FROM watch_reference wr
    ${where}
    ORDER BY wr.collection, wr.model_name`,
    params
  );

  const summary = await query(
    `SELECT
      (SELECT COUNT(*) FROM watch_reference) AS total_references,
      (SELECT COUNT(*) FROM market_event) AS total_events,
      (SELECT COUNT(*) FROM msrp_snapshot) AS total_snapshots,
      (SELECT COUNT(DISTINCT collection) FROM watch_reference) AS collection_count`
  );

  return NextResponse.json({
    success: true,
    data: references,
    summary: summary[0],
    meta: { timestamp: new Date().toISOString() },
  });
}