src/app/api/cv-profile/route.ts

import { PrismaClient } from "@/generated/prisma";
import { prisma } from "@/lib/prisma";
import { NextRequest, NextResponse } from "next/server";

const UUID_REGEX = /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i;

// ─── Helpers ──────────────────────────────────────────────────────────────────

async function getMatchingArrayValues(
  column: string,
  search: string,
  client: PrismaClient
): Promise<string[]> {
  const allowedColumns = ["hardskills", "softskills", "certifications", "business_domain"];
  if (!allowedColumns.includes(column)) throw new Error(`Invalid column: ${column}`);

  const result = await client.$queryRawUnsafe<{ val: string }[]>(
    `SELECT DISTINCT unnest("${column}") AS val
     FROM cv_profile
     WHERE EXISTS (
       SELECT 1 FROM unnest("${column}") AS elem
       WHERE elem ILIKE '%' || $1 || '%'
     )`,
    search
  );

  const lower = search.toLowerCase();
  return result.map((r) => r.val).filter((v) => v.toLowerCase().includes(lower));
}

// One query joining both tables instead of two round-trips
async function getProfileIdsByUserId(user_id: string): Promise<string[]> {
  const rows = await prisma.$queryRaw<{ profile_id: string }[]>`
    SELECT p.profile_id
    FROM cv_profile p
    INNER JOIN cv_file f ON f.file_id = p.file_id
    WHERE f.user_id = ${user_id}::uuid AND f.is_deleted = false
  `;
  return rows.map((r) => r.profile_id);
}

// Fetch all score data in 2 queries instead of 3
async function buildScoreMap(criteria_id: string): Promise<Map<string, number | null>> {
  const scoreMap = new Map<string, number | null>();

  // Single query: join cv_weight → cv_matching → cv_score
  const rows = await prisma.$queryRaw<{ profile_id: string; score: number | null }[]>`
    SELECT
      COALESCE(s.profile_id, m.profile_id) AS profile_id,
      s.score
    FROM cv_weight   w
    JOIN cv_matching m ON m.weight_id = w.weight_id
    JOIN cv_score    s ON s.matching_id = m.matching_id
    WHERE w.criteria_id = ${criteria_id}::uuid
  `;

  for (const row of rows) {
    if (row.profile_id) scoreMap.set(row.profile_id, row.score ?? null);
  }

  return scoreMap;
}

// ─── GET /api/cv-profiles ─────────────────────────────────────────────────────

export async function GET(request: NextRequest) {
  const { searchParams } = new URL(request.url);

  // --- PAGINATION ---
  const page  = Math.max(1, Number.parseInt(searchParams.get("page")  ?? "1")  || 1);
  const limit = Math.min(100, Math.max(1, Number.parseInt(searchParams.get("limit") ?? "10") || 10));
  const skip  = (page - 1) * limit;

  // --- SEARCH ---
  const search = searchParams.get("search");

  // --- CRITERIA ---
  const criteria_id = searchParams.get("criteria_id");
  if (criteria_id && !UUID_REGEX.test(criteria_id)) {
    return NextResponse.json({ error: "Invalid criteria_id format" }, { status: 400 });
  }

  // --- USER FILTER ---
  const user_id = searchParams.get("user_id");
  if (user_id && !UUID_REGEX.test(user_id)) {
    return NextResponse.json({ error: "Invalid user_id format" }, { status: 400 });
  }

  // --- FILTERS ---
  const domicile        = searchParams.get("domicile");
  const yoe             = searchParams.get("yoe");
  const softskills      = searchParams.getAll("softskills");
  const hardskills      = searchParams.getAll("hardskills");
  const certifications  = searchParams.getAll("certifications");
  const business_domain = searchParams.getAll("business_domain");

  const univ_edu_1  = searchParams.getAll("univ_edu_1");
  const univ_edu_2  = searchParams.getAll("univ_edu_2");
  const univ_edu_3  = searchParams.getAll("univ_edu_3");
  const major_edu_1 = searchParams.getAll("major_edu_1");
  const major_edu_2 = searchParams.getAll("major_edu_2");
  const major_edu_3 = searchParams.getAll("major_edu_3");
  const gpa_1 = searchParams.get("gpa_1");
  const gpa_2 = searchParams.get("gpa_2");
  const gpa_3 = searchParams.get("gpa_3");

  // --- SORT ---
  const sortBy    = searchParams.get("sortBy") ?? "created_at";
  const sortOrder = searchParams.get("sortOrder") === "asc" ? "asc" : "desc";

  // "score" is intentionally excluded — it's not a DB column, handled in-memory
  const allowedSortFields = [
    "fullname", "domicile", "yoe", "gpa_edu_1", "gpa_edu_2", "gpa_edu_3",
    "univ_edu_1", "univ_edu_2", "univ_edu_3", "major_edu_1", "major_edu_2",
    "major_edu_3", "created_at",
  ];

  const isScoreSort = sortBy === "score" && !!criteria_id;
  const orderBy = allowedSortFields.includes(sortBy)
    ? { [sortBy]: sortOrder }
    : { created_at: "desc" as const };

  try {
    // --- ALL INDEPENDENT LOOKUPS IN ONE PARALLEL BATCH ---
    const [
      matchingHardskills,
      matchingSoftskills,
      matchingCertifications,
      matchingBusinessDomain,
      userProfileIds,
      scoreMap,
    ] = await Promise.all([
      search      ? getMatchingArrayValues("hardskills",      search, prisma) : Promise.resolve([] as string[]),
      search      ? getMatchingArrayValues("softskills",      search, prisma) : Promise.resolve([] as string[]),
      search      ? getMatchingArrayValues("certifications",  search, prisma) : Promise.resolve([] as string[]),
      search      ? getMatchingArrayValues("business_domain", search, prisma) : Promise.resolve([] as string[]),
      user_id     ? getProfileIdsByUserId(user_id)                            : Promise.resolve(null as string[] | null),
      criteria_id ? buildScoreMap(criteria_id)                                : Promise.resolve(new Map<string, number | null>()),
    ]);

    // --- BUILD WHERE ---
    const searchFloat = search ? Number.parseFloat(search) : NaN;
    const searchInt   = search ? Number.parseInt(search)   : NaN;

    const where: any = {
      ...(userProfileIds !== null && { profile_id: { in: userProfileIds } }),

      ...(search && {
        OR: [
          { fullname:   { contains: search, mode: "insensitive" } },
          { domicile:   { contains: search, mode: "insensitive" } },
          { univ_edu_1: { contains: search, mode: "insensitive" } },
          { univ_edu_2: { contains: search, mode: "insensitive" } },
          { univ_edu_3: { contains: search, mode: "insensitive" } },
          { major_edu_1:{ contains: search, mode: "insensitive" } },
          { major_edu_2:{ contains: search, mode: "insensitive" } },
          { major_edu_3:{ contains: search, mode: "insensitive" } },
          { filename:   { contains: search, mode: "insensitive" } },
          ...(matchingHardskills.length    > 0 ? [{ hardskills:      { hasSome: matchingHardskills    } }] : []),
          ...(matchingSoftskills.length    > 0 ? [{ softskills:      { hasSome: matchingSoftskills    } }] : []),
          ...(matchingCertifications.length> 0 ? [{ certifications:  { hasSome: matchingCertifications} }] : []),
          ...(matchingBusinessDomain.length> 0 ? [{ business_domain: { hasSome: matchingBusinessDomain} }] : []),
          ...(!Number.isNaN(searchFloat) ? [
            { gpa_edu_1: { equals: searchFloat } },
            { gpa_edu_2: { equals: searchFloat } },
            { gpa_edu_3: { equals: searchFloat } },
          ] : []),
          ...(!Number.isNaN(searchInt) ? [{ yoe: { equals: searchInt } }] : []),
        ],
      }),

      ...(domicile        && { domicile }),
      ...(yoe             && { yoe:             { gte: Number.parseInt(yoe) } }),
      ...(softskills.length      > 0 && { softskills:      { hasSome: softskills      } }),
      ...(hardskills.length      > 0 && { hardskills:      { hasSome: hardskills      } }),
      ...(certifications.length  > 0 && { certifications:  { hasSome: certifications  } }),
      ...(business_domain.length > 0 && { business_domain: { hasSome: business_domain } }),

      ...(univ_edu_1.length  > 0 && { univ_edu_1:  { in: univ_edu_1  } }),
      ...(major_edu_1.length > 0 && { major_edu_1: { in: major_edu_1 } }),
      ...(gpa_1              && { gpa_edu_1: { gte: Number.parseFloat(gpa_1) } }),

      ...(univ_edu_2.length  > 0 && { univ_edu_2:  { in: univ_edu_2  } }),
      ...(major_edu_2.length > 0 && { major_edu_2: { in: major_edu_2 } }),
      ...(gpa_2              && { gpa_edu_2: { gte: Number.parseFloat(gpa_2) } }),

      ...(univ_edu_3.length  > 0 && { univ_edu_3:  { in: univ_edu_3  } }),
      ...(major_edu_3.length > 0 && { major_edu_3: { in: major_edu_3 } }),
      ...(gpa_3              && { gpa_edu_3: { gte: Number.parseFloat(gpa_3) } }),
    };

    // --- FETCH PROFILES + COUNT IN PARALLEL ---
    const [profiles, total] = await Promise.all([
      prisma.cv_profile.findMany({
        where,
        orderBy,
        // Skip DB-level pagination when we need to sort by score in-memory
        ...(isScoreSort ? {} : { skip, take: limit }),
      }),
      prisma.cv_profile.count({ where }),
    ]);

    const pagination = {
      total,
      page,
      limit,
      totalPages: Math.ceil(total / limit),
      hasNext: page < Math.ceil(total / limit),
      hasPrev: page > 1,
    };

    // --- NO criteria_id → return early ---
    if (!criteria_id) {
      return NextResponse.json({ data: profiles, pagination });
    }

    // --- ATTACH SCORES ---
    const profilesWithScore = profiles.map((profile) => ({
      ...profile,
      score: scoreMap.get(profile.profile_id) ?? null,
    }));

    // --- IN-MEMORY SORT + PAGINATE (score sort only) ---
    if (isScoreSort) {
      profilesWithScore.sort((a, b) => {
        const aScore = a.score ?? -Infinity;
        const bScore = b.score ?? -Infinity;
        return sortOrder === "asc" ? aScore - bScore : bScore - aScore;
      });

      return NextResponse.json({
        data: profilesWithScore.slice(skip, skip + limit),
        pagination,
      });
    }

    return NextResponse.json({ data: profilesWithScore, pagination });

  } catch (error) {
    const isDev = process.env.NODE_ENV !== "production";

    const message = error instanceof Error ? error.message : String(error);
    const stack   = error instanceof Error ? error.stack   : undefined;

    console.error("[GET /cv-profiles]", error);

    return NextResponse.json(
      {
        error: "Failed to fetch profiles",
        // Full detail in dev; safe summary in prod so you can still read logs
        ...(isDev
          ? { detail: message, stack }
          : { detail: message }          // remove this line if you want prod fully silent
        ),
      },
      { status: 500 }
    );
  }
}