import { db } from "@/app/db/db";
import { tasks } from "@/app/db/schema/tasks/tasks";
import { subTasks } from "@/app/db/schema/tasks/subtask";
import { eq, desc, count, sql } from "drizzle-orm";
import { NextRequest, NextResponse } from "next/server";

export async function GET(
  request: NextRequest,
  { params }: { params: Promise<{ portfolioId: string }> }
) {
  try {
    const { portfolioId } = await params;
    const { searchParams } = new URL(request.url);
    const limit = parseInt(searchParams.get("limit") || "20");
    const offset = parseInt(searchParams.get("offset") || "0");

    const rows = await db
      .select({
        id: tasks.id,
        taskSource: tasks.taskSource,
        jobStarted: tasks.jobStarted,
        jobCompleted: tasks.jobCompleted,
        status: tasks.status,
        service: tasks.service,
        updatedAt: tasks.updatedAt,
        totalSubtasks: count(subTasks.id),
        completedSubtasks: sql<number>`count(case when lower(${subTasks.status}) in ('completed', 'complete') then 1 end)::int`,
        failedSubtasks: sql<number>`count(case when lower(${subTasks.status}) in ('failed', 'failure', 'error') then 1 end)::int`,
      })
      .from(tasks)
      .leftJoin(subTasks, eq(subTasks.taskId, tasks.id))
      .where(eq(tasks.sourceId, portfolioId))
      .groupBy(tasks.id)
      .orderBy(desc(tasks.jobStarted))
      .limit(limit)
      .offset(offset);

    const countResult = await db
      .select({ count: count() })
      .from(tasks)
      .where(eq(tasks.sourceId, portfolioId));

    const total = countResult[0].count;

    return NextResponse.json({ tasks: rows, total, limit, offset });
  } catch (error) {
    console.error("Error fetching portfolio tasks:", error);
    return NextResponse.json(
      { error: "Failed to fetch portfolio tasks" },
      { status: 500 }
    );
  }
}
