path: root/lib/vendor-investigation/service.ts

"use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)

import { vendorInvestigationAttachments, vendorInvestigations, vendorInvestigationsView } from "@/db/schema/vendors"
import { GetVendorsInvestigationSchema, updateVendorInvestigationSchema } from "./validations"
import { asc, desc, ilike, inArray, and, or, gte, lte, eq, isNull, count } from "drizzle-orm";
import { revalidateTag, unstable_noStore } from "next/cache";
import { filterColumns } from "@/lib/filter-columns";
import { unstable_cache } from "@/lib/unstable-cache";
import { getErrorMessage } from "@/lib/handle-error";
import db from "@/db/db";
import { sendEmail } from "../mail/sendEmail";
import fs from "fs"
import path from "path"
import { v4 as uuid } from "uuid"

export async function getVendorsInvestigation(input: GetVendorsInvestigationSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage

        // 1) Advanced filters
        const advancedWhere = filterColumns({
          table: vendorInvestigationsView,
          filters: input.filters,
          joinOperator: input.joinOperator,
        })

        // 2) Global search
        let globalWhere
        if (input.search) {
          const s = `%${input.search}%`
          globalWhere = or(
            ilike(vendorInvestigationsView.vendorName, s),
            ilike(vendorInvestigationsView.vendorCode, s),
            ilike(vendorInvestigationsView.investigationNotes, s),
            ilike(vendorInvestigationsView.vendorEmail, s)
            // etc.
          )
        }

        // 3) Combine finalWhere
        // Example: Only show vendorStatus = "PQ_SUBMITTED"
        const finalWhere = and(
          advancedWhere,
          globalWhere,
          eq(vendorInvestigationsView.vendorStatus, "PQ_SUBMITTED")
        )



        // 5) Sorting
        const orderBy =
          input.sort && input.sort.length > 0
            ? input.sort.map((item) =>
              item.desc
                ? desc(vendorInvestigationsView[item.id])
                : asc(vendorInvestigationsView[item.id])
            )
            : [desc(vendorInvestigationsView.investigationCreatedAt)]

        // 6) Query & count
        const { data, total } = await db.transaction(async (tx) => {
          // a) Select from the view
          const investigationsData = await tx
            .select()
            .from(vendorInvestigationsView)
            .where(finalWhere)
            .orderBy(...orderBy)
            .offset(offset)
            .limit(input.perPage)

          // b) Count total
          const resCount = await tx
            .select({ count: count() })
            .from(vendorInvestigationsView)
            .where(finalWhere)

          return { data: investigationsData, total: resCount[0]?.count }
        })

        // 7) Calculate pageCount
        const pageCount = Math.ceil(total / input.perPage)

        // Now 'data' already contains JSON arrays of contacts & items
        // thanks to the subqueries in the view definition!
        return { data, pageCount }
      } catch (err) {
        console.error(err)
        return { data: [], pageCount: 0 }
      }
    },
    // Cache key
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: ["vendors-in-investigation"],
    }
  )()
}


interface RequestInvestigateVendorsInput {
  ids: number[]
}

export async function requestInvestigateVendors({
  ids,
}: RequestInvestigateVendorsInput) {
  try {
    if (!ids || ids.length === 0) {
      return { error: "No vendor IDs provided." }
    }

    // 1. Create a new investigation row for each vendor
    // You could also check if an investigation already exists for each vendor
    // before inserting. For now, we’ll assume we always insert new ones.
    const newRecords = await db
      .insert(vendorInvestigations)
      .values(
        ids.map((vendorId) => ({
          vendorId
        }))
      )
      .returning()

    // 2. Optionally, send an email notification
    //    Adjust recipient, subject, and body as needed.
    await sendEmail({
      to: "dujin.kim@dtsolution.io",
      subject: "New Vendor Investigation(s) Requested",
      // This template name could match a Handlebars file like: `investigation-request.hbs`
      template: "investigation-request",
      context: {
        // For example, if you're translating in Korean:
        language: "ko",
        // Add any data you want to use within the template
        vendorIds: ids,
        notes: "Please initiate the planned investigations soon."
      },
    })

    // 3. Optionally, revalidate any pages that might show updated data
    // revalidatePath("/your-vendors-page") // or wherever you list the vendors

    return { data: newRecords, error: null }
  } catch (err: unknown) {
    const errorMessage = err instanceof Error ? err.message : String(err)
    return { error: errorMessage }
  }
}


export async function updateVendorInvestigationAction(formData: FormData) {
  try {
    // 1) Separate text fields from file fields
    const textEntries: Record<string, string> = {}
    for (const [key, value] of formData.entries()) {
      if (typeof value === "string") {
        textEntries[key] = value
      }
    }

    // 2) Convert text-based "investigationId" to a number
    if (textEntries.investigationId) {
      textEntries.investigationId = String(Number(textEntries.investigationId))
    }

    // 3) Parse/validate with Zod
    const parsed = updateVendorInvestigationSchema.parse(textEntries)
    // parsed is type UpdateVendorInvestigationSchema

    // 4) Update the vendor_investigations table
    await db
      .update(vendorInvestigations)
      .set({
        investigationStatus: parsed.investigationStatus,
        scheduledStartAt: parsed.scheduledStartAt
          ? new Date(parsed.scheduledStartAt)
          : null,
        scheduledEndAt: parsed.scheduledEndAt ? new Date(parsed.scheduledEndAt) : null,
        completedAt: parsed.completedAt ? new Date(parsed.completedAt) : null,
        investigationNotes: parsed.investigationNotes ?? "",
        updatedAt: new Date(),
      })
      .where(eq(vendorInvestigations.id, parsed.investigationId))

    // 5) Handle file attachments
    //    formData.getAll("attachments") can contain multiple files
    const files = formData.getAll("attachments") as File[]

    // Make sure the folder exists
    const uploadDir = path.join(process.cwd(), "public", "vendor-investigation")
    if (!fs.existsSync(uploadDir)) {
      fs.mkdirSync(uploadDir, { recursive: true })
    }

    for (const file of files) {
      if (file && file.size > 0) {
        // Create a unique filename
        const ext = path.extname(file.name) // e.g. ".pdf"
        const newFileName = `${uuid()}${ext}`

        const filePath = path.join(uploadDir, newFileName)

        // 6) Write file to disk
        const arrayBuffer = await file.arrayBuffer()
        const buffer = Buffer.from(arrayBuffer)
        fs.writeFileSync(filePath, buffer)

        // 7) Insert a record in vendor_investigation_attachments
        await db.insert(vendorInvestigationAttachments).values({
          investigationId: parsed.investigationId,
          fileName: file.name, // original name
          filePath: `/vendor-investigation/${newFileName}`, // relative path in public/
          attachmentType: "REPORT", // or user-specified
        })
      }
    }

    // Revalidate anything if needed
    revalidateTag("vendors-in-investigation")

    return { data: "OK", error: null }
  } catch (err: unknown) {
    const message = err instanceof Error ? err.message : String(err)
    return { error: message }
  }
}