/**
 * WorthDoing.ai
 * Author: Simon-Pierre Boucher
 * Contact: contact@spboucher.ai
 * File: src/app/api/opportunities/route.ts
 * Description: List opportunities across investigations for the Explore page.
 */
import { NextResponse } from "next/server";
import { desc, eq } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { opportunities, investigations } from "@/lib/db/schema";

export const dynamic = "force-dynamic";

export async function GET() {
  const rows = await db
    .select({
      id: opportunities.id,
      title: opportunities.title,
      summary: opportunities.summary,
      status: opportunities.status,
      worthScore: opportunities.worthScore,
      evidenceConfidence: opportunities.evidenceConfidence,
      createdAt: opportunities.createdAt,
      investigationId: opportunities.investigationId,
      objective: investigations.objective,
    })
    .from(opportunities)
    .innerJoin(investigations, eq(investigations.id, opportunities.investigationId))
    .orderBy(desc(opportunities.worthScore))
    .limit(100);
  return NextResponse.json({ opportunities: rows });
}