`src/app/api/entity-resolution/route.ts`

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

  // Find market events whose reference_number_raw doesn't match any watch_reference
  const unmatched = await query(`
    SELECT me.event_id, me.source_name, me.source_listing_id, me.event_type,
           me.reference_number_raw, me.sale_date, me.price_amount, me.currency,
           me.condition_raw, me.condition_normalized, me.source_url,
           me.created_at
    FROM market_event me
    LEFT JOIN watch_reference wr ON me.reference_number_raw = wr.reference_number
    WHERE wr.id IS NULL AND me.reference_number_raw IS NOT NULL
    ORDER BY me.created_at DESC
    LIMIT 100
  `);

  // Get all known references for fuzzy matching suggestions
  const references = await query(`
    SELECT id, reference_number, collection, model_name
    FROM watch_reference
    ORDER BY collection, reference_number
  `);

  // Build match suggestions using simple similarity
  const suggestions: Record<string, any[]> = {};
  for (const event of unmatched) {
    const raw = event.reference_number_raw || "";
    const matches = references
      .map((ref: any) => ({
        ...ref,
        score: similarity(raw, ref.reference_number),
      }))
      .filter((m: any) => m.score > 0.3)
      .sort((a: any, b: any) => b.score - a.score)
      .slice(0, 5);
    suggestions[event.event_id] = matches;
  }

  // Summary stats
  const stats = await query(`
    SELECT
      COUNT(*) FILTER (WHERE wr.id IS NULL AND me.reference_number_raw IS NOT NULL) as unmatched,
      COUNT(*) FILTER (WHERE wr.id IS NOT NULL) as matched,
      COUNT(*) as total
    FROM market_event me
    LEFT JOIN watch_reference wr ON me.reference_number_raw = wr.reference_number
  `);

  return NextResponse.json({
    success: true,
    data: {
      unmatched,
      suggestions,
      references,
      stats: stats[0],
    },
  });
}

// Simple Levenshtein-based similarity score (0-1)
function similarity(a: string, b: string): number {
  if (!a || !b) return 0;
  a = a.toLowerCase().replace(/[^a-z0-9]/g, "");
  b = b.toLowerCase().replace(/[^a-z0-9]/g, "");
  if (a === b) return 1;

  const matrix: number[][] = [];
  for (let i = 0; i <= a.length; i++) {
    matrix[i] = [i];
    for (let j = 1; j <= b.length; j++) {
      if (i === 0) {
        matrix[i][j] = j;
      } else {
        const cost = a[i - 1] === b[j - 1] ? 0 : 1;
        matrix[i][j] = Math.min(
          matrix[i - 1][j] + 1,
          matrix[i][j - 1] + 1,
          matrix[i - 1][j - 1] + cost
        );
      }
    }
  }
  const maxLen = Math.max(a.length, b.length);
  return maxLen > 0 ? 1 - matrix[a.length][b.length] / maxLen : 0;
}

// POST: Link a market event to a reference
export async function POST(request: NextRequest) {
  const session = await getSession(request);
  if (!session) {
    return NextResponse.json({ success: false, error: { code: "UNAUTHORIZED", message: "Login required" } }, { status: 401 });
  }

  const { event_id, reference_number } = await request.json();

  if (!event_id || !reference_number) {
    return NextResponse.json({
      success: false,
      error: { code: "MISSING_PARAMS", message: "event_id and reference_number required" },
    }, { status: 400 });
  }

  // Update the market event's reference_number_raw
  await query(
    `UPDATE market_event SET reference_number_raw = $1 WHERE event_id = $2`,
    [reference_number, event_id]
  );

  return NextResponse.json({ success: true, data: { event_id, reference_number } });
}