(대표님) lib 파트 커밋

1 files changed, 445 insertions, 78 deletions

diff --git a/lib/vendor-investigation/service.ts b/lib/vendor-investigation/service.ts
index e3d03cd4..bcf9efd4 100644
--- a/lib/vendor-investigation/service.ts
+++ b/lib/vendor-investigation/service.ts
@@ -1,6 +1,6 @@
 "use server"; // Next.js 서버 액션에서 직접 import하려면 (선택)
 
-import { vendorInvestigationAttachments, vendorInvestigations, vendorInvestigationsView } from "@/db/schema/vendors"
+import { items, vendorInvestigationAttachments, vendorInvestigations, vendorInvestigationsView, vendorPossibleItems, vendors } from "@/db/schema/"
 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";
@@ -13,54 +13,62 @@ import fs from "fs"
 import path from "path"
 import { v4 as uuid } from "uuid"
 import { vendorsLogs } from "@/db/schema";
+import { cache } from "react"
 
 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.requesterName, s),
+            ilike(vendorInvestigationsView.qmManagerName, s),
+            
+            // 실사 정보
             ilike(vendorInvestigationsView.investigationNotes, s),
-            ilike(vendorInvestigationsView.vendorEmail, s)
-            // etc.
+            ilike(vendorInvestigationsView.investigationStatus, s),
+            ilike(vendorInvestigationsView.evaluationType, s),
+            ilike(vendorInvestigationsView.investigationAddress, s),
+            ilike(vendorInvestigationsView.investigationMethod, s),
+            
+            // 평가 결과
+            ilike(vendorInvestigationsView.evaluationResult, s)
           )
         }
-
         // 3) Combine finalWhere
-        // Example: Only show vendorStatus = "PQ_SUBMITTED"
         const finalWhere = and(
           advancedWhere,
-          globalWhere,
-          // eq(vendorInvestigationsView.vendorStatus, "PQ_APPROVED")
+          globalWhere
         )
-
-
-
-        // 5) Sorting
+        
+        // 4) 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
+                item.desc
+                  ? desc(vendorInvestigationsView[item.id])
+                  : asc(vendorInvestigationsView[item.id])
+              )
+            : [desc(vendorInvestigationsView.createdAt)]
+        
+        // 5) Query & count
         const { data, total } = await db.transaction(async (tx) => {
           // a) Select from the view
           const investigationsData = await tx
@@ -70,7 +78,7 @@ export async function getVendorsInvestigation(input: GetVendorsInvestigationSche
             .orderBy(...orderBy)
             .offset(offset)
             .limit(input.perPage)
-
+          
           // b) Count total
           const resCount = await tx
             .select({ count: count() })
@@ -79,14 +87,11 @@ export async function getVendorsInvestigation(input: GetVendorsInvestigationSche
 
           return { data: investigationsData, total: resCount[0]?.count }
         })
-
-        // 7) Calculate pageCount
+        
+        // 6) Calculate pageCount
         const pageCount = Math.ceil(total / input.perPage)
-
-        console.log(data,"data")
-
-        // Now 'data' already contains JSON arrays of contacts & items
-        // thanks to the subqueries in the view definition!
+        
+        // Data is already in the correct format from the simplified view
         return { data, pageCount }
       } catch (err) {
         console.error(err)
@@ -101,8 +106,6 @@ export async function getVendorsInvestigation(input: GetVendorsInvestigationSche
     }
   )()
 }
-
-
 /**
  * Get existing investigations for a list of vendor IDs
  * 
@@ -116,7 +119,7 @@ export async function getExistingInvestigationsForVendors(vendorIds: number[]) {
     // Query the vendorInvestigationsView using the vendorIds
     const investigations = await db.query.vendorInvestigations.findMany({
       where: inArray(vendorInvestigationsView.vendorId, vendorIds),
-      orderBy: [desc(vendorInvestigationsView.investigationCreatedAt)],
+      orderBy: [desc(vendorInvestigationsView.createdAt)],
     })
 
     return investigations
@@ -188,9 +191,10 @@ export async function requestInvestigateVendors({
 }
 
 
+// 개선된 서버 액션 - 텍스트 데이터만 처리
 export async function updateVendorInvestigationAction(formData: FormData) {
   try {
-    // 1) Separate text fields from file fields
+    // 1) 텍스트 필드만 추출
     const textEntries: Record<string, string> = {}
     for (const [key, value] of formData.entries()) {
       if (typeof value === "string") {
@@ -198,69 +202,432 @@ export async function updateVendorInvestigationAction(formData: FormData) {
       }
     }
 
-    // 2) Convert text-based "investigationId" to a number
+    // 2) 적절한 타입으로 변환
+    const processedEntries: any = {}
+    
+    // 필수 필드
     if (textEntries.investigationId) {
-      textEntries.investigationId = String(Number(textEntries.investigationId))
+      processedEntries.investigationId = Number(textEntries.investigationId)
+    }
+    if (textEntries.investigationStatus) {
+      processedEntries.investigationStatus = textEntries.investigationStatus
     }
 
-    // 3) Parse/validate with Zod
-    const parsed = updateVendorInvestigationSchema.parse(textEntries)
-    // parsed is type UpdateVendorInvestigationSchema
+    // 선택적 enum 필드
+    if (textEntries.evaluationType) {
+      processedEntries.evaluationType = textEntries.evaluationType
+    }
 
-    // 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))
+    // 선택적 문자열 필드
+    if (textEntries.investigationAddress) {
+      processedEntries.investigationAddress = textEntries.investigationAddress
+    }
+    if (textEntries.investigationMethod) {
+      processedEntries.investigationMethod = textEntries.investigationMethod
+    }
+    if (textEntries.investigationNotes) {
+      processedEntries.investigationNotes = textEntries.investigationNotes
+    }
 
-    // 5) Handle file attachments
-    //    formData.getAll("attachments") can contain multiple files
-    const files = formData.getAll("attachments") as File[]
+    // 선택적 날짜 필드
+    if (textEntries.forecastedAt) {
+      processedEntries.forecastedAt = new Date(textEntries.forecastedAt)
+    }
+    if (textEntries.requestedAt) {
+      processedEntries.requestedAt = new Date(textEntries.requestedAt)
+    }
+    if (textEntries.confirmedAt) {
+      processedEntries.confirmedAt = new Date(textEntries.confirmedAt)
+    }
+    if (textEntries.completedAt) {
+      processedEntries.completedAt = new Date(textEntries.completedAt)
+    }
 
-    // Make sure the folder exists
-    const uploadDir = path.join(process.cwd(), "public", "vendor-investigation")
-    if (!fs.existsSync(uploadDir)) {
-      fs.mkdirSync(uploadDir, { recursive: true })
+    // 선택적 숫자 필드
+    if (textEntries.evaluationScore) {
+      processedEntries.evaluationScore = Number(textEntries.evaluationScore)
     }
 
-    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}`
+    // 선택적 평가 결과
+    if (textEntries.evaluationResult) {
+      processedEntries.evaluationResult = textEntries.evaluationResult
+    }
 
-        const filePath = path.join(uploadDir, newFileName)
+    // 3) Zod로 파싱/검증
+    const parsed = updateVendorInvestigationSchema.parse(processedEntries)
 
-        // 6) Write file to disk
-        const arrayBuffer = await file.arrayBuffer()
-        const buffer = Buffer.from(arrayBuffer)
-        fs.writeFileSync(filePath, buffer)
+    // 4) 업데이트 데이터 준비 - 실제로 제공된 필드만 포함
+    const updateData: any = {
+      investigationStatus: parsed.investigationStatus,
+      updatedAt: new Date(),
+    }
 
-        // 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
-        })
-      }
+    // 선택적 필드들은 존재할 때만 추가
+    if (parsed.evaluationType !== undefined) {
+      updateData.evaluationType = parsed.evaluationType
+    }
+    if (parsed.investigationAddress !== undefined) {
+      updateData.investigationAddress = parsed.investigationAddress
+    }
+    if (parsed.investigationMethod !== undefined) {
+      updateData.investigationMethod = parsed.investigationMethod
+    }
+    if (parsed.forecastedAt !== undefined) {
+      updateData.forecastedAt = parsed.forecastedAt
+    }
+    if (parsed.requestedAt !== undefined) {
+      updateData.requestedAt = parsed.requestedAt
+    }
+    if (parsed.confirmedAt !== undefined) {
+      updateData.confirmedAt = parsed.confirmedAt
+    }
+    if (parsed.completedAt !== undefined) {
+      updateData.completedAt = parsed.completedAt
+    }
+    if (parsed.evaluationScore !== undefined) {
+      updateData.evaluationScore = parsed.evaluationScore
+    }
+    if (parsed.evaluationResult !== undefined) {
+      updateData.evaluationResult = parsed.evaluationResult
+    }
+    if (parsed.investigationNotes !== undefined) {
+      updateData.investigationNotes = parsed.investigationNotes
     }
 
-    // Revalidate anything if needed
+    // 5) vendor_investigations 테이블 업데이트
+    await db
+      .update(vendorInvestigations)
+      .set(updateData)
+      .where(eq(vendorInvestigations.id, parsed.investigationId))
+
+    // 6) 캐시 무효화
     revalidateTag("vendors-in-investigation")
 
     return { data: "OK", error: null }
   } catch (err: unknown) {
+    console.error("Investigation update error:", err)
     const message = err instanceof Error ? err.message : String(err)
     return { error: message }
   }
-}
\ No newline at end of file
+}
+// 실사 첨부파일 조회 함수
+export async function getInvestigationAttachments(investigationId: number) {
+  try {
+    const attachments = await db
+      .select()
+      .from(vendorInvestigationAttachments)
+      .where(eq(vendorInvestigationAttachments.investigationId, investigationId))
+      .orderBy(vendorInvestigationAttachments.createdAt)
+
+    return { success: true, attachments }
+  } catch (error) {
+    console.error("첨부파일 조회 실패:", error)
+    return { success: false, error: "첨부파일 조회에 실패했습니다.", attachments: [] }
+  }
+}
+
+// 첨부파일 삭제 함수
+export async function deleteInvestigationAttachment(attachmentId: number) {
+  try {
+    // 파일 정보 조회
+    const [attachment] = await db
+      .select()
+      .from(vendorInvestigationAttachments)
+      .where(eq(vendorInvestigationAttachments.id, attachmentId))
+      .limit(1)
+
+    if (!attachment) {
+      return { success: false, error: "첨부파일을 찾을 수 없습니다." }
+    }
+
+    // 실제 파일 삭제
+    const fullFilePath = path.join(process.cwd(), "public", attachment.filePath)
+    if (fs.existsSync(fullFilePath)) {
+      fs.unlinkSync(fullFilePath)
+    }
+
+    // 데이터베이스에서 레코드 삭제
+    await db
+      .delete(vendorInvestigationAttachments)
+      .where(eq(vendorInvestigationAttachments.id, attachmentId))
+
+    // 캐시 무효화
+    revalidateTag("vendors-in-investigation")
+    
+    return { success: true }
+  } catch (error) {
+    console.error("첨부파일 삭제 실패:", error)
+    return { success: false, error: "첨부파일 삭제에 실패했습니다." }
+  }
+}
+
+// 첨부파일 다운로드 정보 조회
+export async function getAttachmentDownloadInfo(attachmentId: number) {
+  try {
+    const [attachment] = await db
+      .select({
+        fileName: vendorInvestigationAttachments.fileName,
+        filePath: vendorInvestigationAttachments.filePath,
+        mimeType: vendorInvestigationAttachments.mimeType,
+        fileSize: vendorInvestigationAttachments.fileSize,
+      })
+      .from(vendorInvestigationAttachments)
+      .where(eq(vendorInvestigationAttachments.id, attachmentId))
+      .limit(1)
+
+    if (!attachment) {
+      return { success: false, error: "첨부파일을 찾을 수 없습니다." }
+    }
+
+    const fullFilePath = path.join(process.cwd(), "public", attachment.filePath)
+    if (!fs.existsSync(fullFilePath)) {
+      return { success: false, error: "파일이 존재하지 않습니다." }
+    }
+
+    return { 
+      success: true, 
+      downloadInfo: {
+        fileName: attachment.fileName,
+        filePath: attachment.filePath,
+        mimeType: attachment.mimeType,
+        fileSize: attachment.fileSize,
+      }
+    }
+  } catch (error) {
+    console.error("첨부파일 정보 조회 실패:", error)
+    return { success: false, error: "첨부파일 정보 조회에 실패했습니다." }
+  }
+}
+/**
+ * Get vendor details by ID
+ */
+export const getVendorById = cache(async (vendorId: number) => {
+  try {
+    const [vendorData] = await db
+      .select({
+        id: vendors.id,
+        name: vendors.vendorName,
+        code: vendors.vendorCode,
+        taxId: vendors.taxId,
+        email: vendors.email,
+        phone: vendors.phone,
+        website: vendors.website,
+        address: vendors.address,
+        country: vendors.country,
+        status: vendors.status,
+        description: vendors.items, // Using items field as description for now
+        vendorTypeId: vendors.vendorTypeId,
+        representativeName: vendors.representativeName,
+        representativeBirth: vendors.representativeBirth,
+        representativeEmail: vendors.representativeEmail,
+        representativePhone: vendors.representativePhone,
+        corporateRegistrationNumber: vendors.corporateRegistrationNumber,
+        creditAgency: vendors.creditAgency,
+        creditRating: vendors.creditRating,
+        cashFlowRating: vendors.cashFlowRating,
+        businessSize: vendors.businessSize,
+        createdAt: vendors.createdAt,
+        updatedAt: vendors.updatedAt,
+      })
+      .from(vendors)
+      .where(eq(vendors.id, vendorId))
+      .limit(1)
+
+    if (!vendorData) {
+      throw new Error(`Vendor with ID ${vendorId} not found`)
+    }
+
+    return vendorData
+  } catch (error) {
+    console.error("Error fetching vendor:", error)
+    throw new Error("Failed to fetch vendor details")
+  }
+})
+
+/**
+ * Get vendor items by vendor ID with caching
+ */
+export async function getVendorItemsByVendorId(vendorId: number) {
+  return unstable_cache(
+    async () => {
+      try {
+        // Join vendorPossibleItems with items table to get complete item information
+        const vendorItems = await db
+          .select({
+            id: vendorPossibleItems.id,
+            vendorId: vendorPossibleItems.vendorId,
+            itemCode: vendorPossibleItems.itemCode,
+            itemName: items.itemName,
+            description: items.description,
+            createdAt: vendorPossibleItems.createdAt,
+            updatedAt: vendorPossibleItems.updatedAt,
+          })
+          .from(vendorPossibleItems)
+          .leftJoin(
+            items,
+            eq(vendorPossibleItems.itemCode, items.itemCode)
+          )
+          .where(eq(vendorPossibleItems.vendorId, vendorId))
+          .orderBy(vendorPossibleItems.createdAt)
+
+        return vendorItems
+      } catch (error) {
+        console.error("Error fetching vendor items:", error)
+        throw new Error("Failed to fetch vendor items")
+      }
+    },
+    // Cache key
+    [`vendor-items-${vendorId}`],
+    {
+      revalidate: 3600, // Cache for 1 hour
+      tags: [`vendor-items-${vendorId}`, "vendor-items"],
+    }
+  )()
+}
+
+/**
+ * Get all items for a vendor (alternative function name for clarity)
+ */
+export const getVendorPossibleItems = cache(async (vendorId: number) => {
+  return getVendorItemsByVendorId(vendorId)
+})
+
+/**
+ * Get vendor contacts by vendor ID
+ * This function assumes you have a vendorContacts table
+ */
+export const getVendorContacts = cache(async (vendorId: number) => {
+  try {
+    // Note: This assumes you have a vendorContacts table
+    // If you don't have this table yet, you can return an empty array
+    // or implement based on your actual contacts storage structure
+    
+    // For now, returning empty array since vendorContacts table wasn't provided
+    return []
+    
+    /* 
+    // Uncomment and modify when you have vendorContacts table:
+    const contacts = await db
+      .select({
+        id: vendorContacts.id,
+        contactName: vendorContacts.name,
+        contactEmail: vendorContacts.email,
+        contactPhone: vendorContacts.phone,
+        contactPosition: vendorContacts.position,
+        isPrimary: vendorContacts.isPrimary,
+        isActive: vendorContacts.isActive,
+        createdAt: vendorContacts.createdAt,
+        updatedAt: vendorContacts.updatedAt,
+      })
+      .from(vendorContacts)
+      .where(
+        and(
+          eq(vendorContacts.vendorId, vendorId),
+          eq(vendorContacts.isActive, true)
+        )
+      )
+
+    return contacts
+    */
+  } catch (error) {
+    console.error("Error fetching vendor contacts:", error)
+    return []
+  }
+})
+
+/**
+ * Add an item to a vendor
+ */
+export async function addVendorItem(vendorId: number, itemCode: string) {
+  try {
+    // Check if the item exists
+    const [item] = await db
+      .select()
+      .from(items)
+      .where(eq(items.itemCode, itemCode))
+      .limit(1)
+
+    if (!item) {
+      throw new Error(`Item with code ${itemCode} not found`)
+    }
+
+    // Check if the vendor-item relationship already exists
+    const [existingRelation] = await db
+      .select()
+      .from(vendorPossibleItems)
+      .where(
+        eq(vendorPossibleItems.vendorId, vendorId) &&
+        eq(vendorPossibleItems.itemCode, itemCode)
+      )
+      .limit(1)
+
+    if (existingRelation) {
+      throw new Error("This item is already associated with the vendor")
+    }
+
+    // Add the item to the vendor
+    const [newVendorItem] = await db
+      .insert(vendorPossibleItems)
+      .values({
+        vendorId,
+        itemCode,
+      })
+      .returning()
+
+    // Revalidate cache
+    revalidateTag(`vendor-items-${vendorId}`)
+    revalidateTag("vendor-items")
+
+    return newVendorItem
+  } catch (error) {
+    console.error("Error adding vendor item:", error)
+    throw new Error("Failed to add item to vendor")
+  }
+}
+
+/**
+ * Remove an item from a vendor
+ */
+export async function removeVendorItem(vendorId: number, itemCode: string) {
+  try {
+    await db
+      .delete(vendorPossibleItems)
+      .where(
+        eq(vendorPossibleItems.vendorId, vendorId) &&
+        eq(vendorPossibleItems.itemCode, itemCode)
+      )
+
+    // Revalidate cache
+    revalidateTag(`vendor-items-${vendorId}`)
+    revalidateTag("vendor-items")
+
+    return { success: true }
+  } catch (error) {
+    console.error("Error removing vendor item:", error)
+    throw new Error("Failed to remove item from vendor")
+  }
+}
+
+/**
+ * Get all available items (for adding to vendors)
+ */
+export const getAllItems = cache(async () => {
+  try {
+    const allItems = await db
+      .select({
+        id: items.id,
+        itemCode: items.itemCode,
+        itemName: items.itemName,
+        description: items.description,
+        createdAt: items.createdAt,
+        updatedAt: items.updatedAt,
+      })
+      .from(items)
+      .orderBy(items.itemName)
+
+    return allItems
+  } catch (error) {
+    console.error("Error fetching all items:", error)
+    throw new Error("Failed to fetch items")
+  }
+})
\ No newline at end of file