src/app/api/cv-profile/export/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

// ---------------------------------------------
// SAFE ARRAY SEARCH (same as main endpoint)
// ---------------------------------------------
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(
    `SELECT DISTINCT unnest("${column}") as val
     FROM cv_profile
     WHERE EXISTS (
       SELECT 1 FROM unnest("${column}") AS elem
       WHERE elem ILIKE '%' || $1 || '%'
     )`,
    search
  ) as { val: string }[]

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

// ---------------------------------------------
// Resolve user_id → profile_ids
// ---------------------------------------------
// In getProfileIdsByUserId, return null if no profiles found
// so it doesn't filter to empty set
async function getProfileIdsByUserId(user_id: string): Promise<string[] | null> {
  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
  `
  if (rows.length === 0) return null // <-- treat as "no filter" instead of "match nothing"
  return rows.map((r) => r.profile_id)
}

// ---------------------------------------------
// EXPORT ENDPOINT
// ---------------------------------------------
export async function GET(request: NextRequest) {
  const { searchParams } = new URL(request.url)

  // -------------------------
  // VALIDATION
  // -------------------------
  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 }
    )
  }

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

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

  // -------------------------
  // 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"

  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",
    "score",
  ]

  const isScoreSort = sortBy === "score" && !!criteria_id

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

  try {
    // -------------------------
    // PARALLEL RESOLUTION
    // -------------------------
    const [
      matchingHardskills,
      matchingSoftskills,
      matchingCertifications,
      matchingBusinessDomain,
      userProfileIds,
    ] = await Promise.all([
      search
        ? getMatchingArrayValues("hardskills", search, prisma)
        : Promise.resolve([]),
      search
        ? getMatchingArrayValues("softskills", search, prisma)
        : Promise.resolve([]),
      search
        ? getMatchingArrayValues("certifications", search, prisma)
        : Promise.resolve([]),
      search
        ? getMatchingArrayValues("business_domain", search, prisma)
        : Promise.resolve([]),
      user_id
        ? getProfileIdsByUserId(user_id)
        : Promise.resolve(null),
    ])

    // -------------------------
    // BUILD WHERE
    // -------------------------
    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(Number.parseFloat(search))
                ? []
                : [
                    { gpa_edu_1: { equals: Number.parseFloat(search) } },
                    { gpa_edu_2: { equals: Number.parseFloat(search) } },
                    { gpa_edu_3: { equals: Number.parseFloat(search) } },
                  ]),

              ...(Number.isNaN(Number.parseInt(search))
                ? []
                : [{ yoe: { equals: Number.parseInt(search) } }]),
            ],
          }
        : {}),

      ...(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) } }),
    }

    // -------------------------
    // SCORE MAP
    // -------------------------
    const scoreMap = new Map<string, number | null>()

    if (criteria_id) {
      const weight = await prisma.cv_weight.findFirst({
        where: { criteria_id },
        select: { weight_id: true },
      })

      if (weight) {
        const matchings = await prisma.cv_matching.findMany({
          where: { weight_id: weight.weight_id },
          select: { matching_id: true, profile_id: true },
        })

        if (matchings.length > 0) {
          const matchingIds = matchings.map((m) => m.matching_id)

          const scores = await prisma.cv_score.findMany({
            where: { matching_id: { in: matchingIds } },
            select: { matching_id: true, profile_id: true, score: true },
          })

          const matchingToProfile = new Map(
            matchings.map((m) => [m.matching_id, m.profile_id])
          )

          for (const s of scores) {
            const profileId =
              s.profile_id ??
              matchingToProfile.get(s.matching_id ?? "")
            if (profileId) {
              scoreMap.set(profileId, s.score ?? null)
            }
          }
        }
      }
    }

    // -------------------------
    // FETCH ALL (NO PAGINATION)
    // -------------------------
    const profiles = await prisma.cv_profile.findMany({
      where,
      orderBy: isScoreSort ? { created_at: "desc" } : orderBy,
    })

    // In-memory score sort
    if (isScoreSort) {
      profiles.sort((a, b) => {
        const aScore = scoreMap.get(a.profile_id) ?? -Infinity
        const bScore = scoreMap.get(b.profile_id) ?? -Infinity
        return sortOrder === "asc"
          ? aScore - bScore
          : bScore - aScore
      })
    }

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

    const csv = generateCSV(profilesWithScore)

    if (!csv) {
      return NextResponse.json(
        { error: "No data to export" },
        { status: 400 }
      )
    }

    const filename = `candidates-${new Date()
      .toISOString()
      .slice(0, 10)}.csv`

    return new NextResponse(csv, {
      status: 200,
      headers: {
        "Content-Type": "text/csv;charset=utf-8",
        "Content-Disposition": `attachment; filename="${filename}"`,
        "Cache-Control": "no-cache, no-store, must-revalidate",
      },
    })
  } catch (error) {
    console.error("Export error:", error)
    return NextResponse.json(
      { error: "Failed to export candidates" },
      { status: 500 }
    )
  }
}

// ---------------------------------------------
// CSV GENERATOR
// ---------------------------------------------
function generateCSV(profiles: any[]): string {
  if (profiles.length === 0) return ""

  const columns = [
    "profile_id",
    "fullname",
    "domicile",
    "yoe",
    "univ_edu_1",
    "major_edu_1",
    "gpa_edu_1",
    "univ_edu_2",
    "major_edu_2",
    "gpa_edu_2",
    "univ_edu_3",
    "major_edu_3",
    "gpa_edu_3",
    "hardskills",
    "softskills",
    "certifications",
    "business_domain",
    "score",
  ]

  const header = columns.join(",")

  const rows = profiles.map((profile) =>
    columns
      .map((col) => {
        const value = profile[col]

        if (Array.isArray(value)) {
          return `"${value.join("; ")}"`
        }

        if (value === null || value === undefined) {
          return ""
        }

        const stringValue = String(value)

        if (
          stringValue.includes(",") ||
          stringValue.includes('"') ||
          stringValue.includes("\n")
        ) {
          return `"${stringValue.replace(/"/g, '""')}"`
        }

        return stringValue
      })
      .join(",")
  )

  return [header, ...rows].join("\n")
}