`src/app/api/models/[reference]/distributions/route.ts`

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

export const dynamic = "force-dynamic";

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

  const ref = await queryOne(
    "SELECT * FROM watch_reference WHERE reference_number = $1",
    [params.reference]
  );

  if (!ref) {
    return NextResponse.json({ success: false, error: { code: "NOT_FOUND", message: "Reference not found" } }, { status: 404 });
  }

  // Price distribution histogram (20 buckets)
  const distribution = await query(
    `WITH stats AS (
      SELECT
        MIN(price_usd) AS min_p,
        MAX(price_usd) AS max_p
      FROM market_event
      WHERE reference_id = $1 AND NOT is_quarantined AND price_usd > 0
    ),
    buckets AS (
      SELECT
        width_bucket(me.price_usd, s.min_p, s.max_p + 1, 20) AS bucket,
        s.min_p,
        s.max_p
      FROM market_event me, stats s
      WHERE me.reference_id = $1 AND NOT me.is_quarantined AND me.price_usd > 0
    )
    SELECT
      bucket,
      min_p + (bucket - 1) * (max_p - min_p + 1) / 20.0 AS bucket_low,
      min_p + bucket * (max_p - min_p + 1) / 20.0 AS bucket_high,
      COUNT(*) AS count
    FROM buckets
    GROUP BY bucket, min_p, max_p
    ORDER BY bucket`,
    [ref.id]
  );

  // Condition breakdown
  const conditionBreakdown = await query(
    `SELECT
      condition_normalized AS condition,
      COUNT(*) AS count,
      AVG(price_usd)::NUMERIC(14,2) AS avg_price,
      MIN(price_usd) AS min_price,
      MAX(price_usd) AS max_price
    FROM market_event
    WHERE reference_id = $1 AND NOT is_quarantined AND price_usd > 0
    GROUP BY condition_normalized
    ORDER BY avg_price DESC`,
    [ref.id]
  );

  // Source breakdown
  const sourceBreakdown = await query(
    `SELECT
      source_name,
      event_type,
      COUNT(*) AS count,
      AVG(price_usd)::NUMERIC(14,2) AS avg_price
    FROM market_event
    WHERE reference_id = $1 AND NOT is_quarantined AND price_usd > 0
    GROUP BY source_name, event_type
    ORDER BY count DESC`,
    [ref.id]
  );

  return NextResponse.json({
    success: true,
    data: {
      reference: ref,
      price_distribution: distribution,
      condition_breakdown: conditionBreakdown,
      source_breakdown: sourceBreakdown,
    },
  });
}