1 files changed, 87 insertions, 12 deletions
diff --git a/lib/contact-possible-items/service.ts b/lib/contact-possible-items/service.ts
index f4b89368..960df17e 100644
--- a/lib/contact-possible-items/service.ts
+++ b/lib/contact-possible-items/service.ts
@@ -3,6 +3,7 @@
 import db from "@/db/db"
 import { techSalesContactPossibleItems } from "@/db/schema/techSales"
 import { techVendors, techVendorContacts, techVendorPossibleItems } from "@/db/schema/techVendors"
+import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items"
 import { eq, desc, ilike, count, or } from "drizzle-orm"
 import { revalidatePath } from "next/cache"
 import { unstable_noStore } from "next/cache"
@@ -29,15 +30,16 @@ export interface ContactPossibleItemDetail {
   // 연락처 정보
   contactName: string | null
   contactPosition: string | null
+  contactTitle: string | null
   contactEmail: string | null
   contactPhone: string | null
   contactCountry: string | null
   isPrimary: boolean | null
   
   // 아이템 정보
-  itemCode: string | null
   workType: string | null
   shipTypes: string | null
+  itemCode: string | null
   itemList: string | null
   subItemList: string | null
 }
@@ -55,13 +57,11 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
     console.log("Input:", input)
     console.log("Offset:", offset)
     
-    // 검색 조건
+    // 검색 조건 (벤더명, 연락처명으로만 검색)
     let whereCondition
     if (input.search) {
       const searchTerm = `%${input.search}%`
       whereCondition = or(
-        ilike(techVendorPossibleItems.itemCode, searchTerm),
-        ilike(techVendorPossibleItems.itemList, searchTerm),
         ilike(techVendors.vendorName, searchTerm),
         ilike(techVendorContacts.contactName, searchTerm)
       )
@@ -70,9 +70,11 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
       console.log("No search condition")
     }
 
-    // 원본 테이블들을 직접 조인해서 데이터 조회
+    // 새로운 스키마에 맞게 수정 - 아이템 정보를 별도로 조회해야 함
     console.log("Executing data query...")
-    const items = await db
+    
+    // 1단계: 기본 매핑 정보 조회
+    const basicItems = await db
       .select({
         // 기본 매핑 정보
         id: techSalesContactPossibleItems.id,
@@ -94,17 +96,16 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
         // 연락처 정보
         contactName: techVendorContacts.contactName,
         contactPosition: techVendorContacts.contactPosition,
+        contactTitle: techVendorContacts.contactTitle,
         contactEmail: techVendorContacts.contactEmail,
         contactPhone: techVendorContacts.contactPhone,
         contactCountry: techVendorContacts.contactCountry,
         isPrimary: techVendorContacts.isPrimary,
         
-        // 벤더 가능 아이템 정보
-        itemCode: techVendorPossibleItems.itemCode,
-        workType: techVendorPossibleItems.workType,
-        shipTypes: techVendorPossibleItems.shipTypes,
-        itemList: techVendorPossibleItems.itemList,
-        subItemList: techVendorPossibleItems.subItemList,
+        // 벤더 가능 아이템 ID 정보
+        shipbuildingItemId: techVendorPossibleItems.shipbuildingItemId,
+        offshoreTopItemId: techVendorPossibleItems.offshoreTopItemId,
+        offshoreHullItemId: techVendorPossibleItems.offshoreHullItemId,
       })
       .from(techSalesContactPossibleItems)
       .leftJoin(techVendorContacts, eq(techSalesContactPossibleItems.contactId, techVendorContacts.id))
@@ -115,6 +116,80 @@ export async function getContactPossibleItems(input: GetContactPossibleItemsSche
       .offset(offset)
       .limit(input.per_page)
 
+    // 2단계: 각 아이템의 상세 정보를 별도로 조회하여 합치기
+    const items = await Promise.all(basicItems.map(async (item) => {
+      let itemCode = null;
+      let workType = null;
+      let shipTypes = null;
+      let itemList = null;
+      let subItemList = null;
+
+      if (item.shipbuildingItemId) {
+        const shipItem = await db
+          .select({
+            itemCode: itemShipbuilding.itemCode,
+            workType: itemShipbuilding.workType,
+            shipTypes: itemShipbuilding.shipTypes,
+            itemList: itemShipbuilding.itemList,
+          })
+          .from(itemShipbuilding)
+          .where(eq(itemShipbuilding.id, item.shipbuildingItemId))
+          .limit(1);
+        
+        if (shipItem.length > 0) {
+          itemCode = shipItem[0].itemCode;
+          workType = shipItem[0].workType;
+          shipTypes = shipItem[0].shipTypes;
+          itemList = shipItem[0].itemList;
+        }
+      } else if (item.offshoreTopItemId) {
+        const topItem = await db
+          .select({
+            itemCode: itemOffshoreTop.itemCode,
+            workType: itemOffshoreTop.workType,
+            itemList: itemOffshoreTop.itemList,
+            subItemList: itemOffshoreTop.subItemList,
+          })
+          .from(itemOffshoreTop)
+          .where(eq(itemOffshoreTop.id, item.offshoreTopItemId))
+          .limit(1);
+        
+        if (topItem.length > 0) {
+          itemCode = topItem[0].itemCode;
+          workType = topItem[0].workType;
+          itemList = topItem[0].itemList;
+          subItemList = topItem[0].subItemList;
+        }
+      } else if (item.offshoreHullItemId) {
+        const hullItem = await db
+          .select({
+            itemCode: itemOffshoreHull.itemCode,
+            workType: itemOffshoreHull.workType,
+            itemList: itemOffshoreHull.itemList,
+            subItemList: itemOffshoreHull.subItemList,
+          })
+          .from(itemOffshoreHull)
+          .where(eq(itemOffshoreHull.id, item.offshoreHullItemId))
+          .limit(1);
+        
+        if (hullItem.length > 0) {
+          itemCode = hullItem[0].itemCode;
+          workType = hullItem[0].workType;
+          itemList = hullItem[0].itemList;
+          subItemList = hullItem[0].subItemList;
+        }
+      }
+
+      return {
+        ...item,
+        itemCode,
+        workType,
+        shipTypes,
+        itemList,
+        subItemList,
+      };
+    }))
+
     console.log("Items found:", items.length)
     console.log("First 3 items:", items.slice(0, 3))