1 files changed, 113 insertions, 45 deletions
diff --git a/lib/tech-vendor-possible-items/repository.ts b/lib/tech-vendor-possible-items/repository.ts
index 5c1487b5..4d876643 100644
--- a/lib/tech-vendor-possible-items/repository.ts
+++ b/lib/tech-vendor-possible-items/repository.ts
@@ -1,12 +1,14 @@
-import { eq, desc, count, SQL, sql, and, or, ilike } from "drizzle-orm";
+import { eq, desc, count, SQL, sql } from "drizzle-orm";
 import { 
   techVendors, 
   techVendorPossibleItems
 } from "@/db/schema/techVendors";
+import { itemShipbuilding, itemOffshoreTop, itemOffshoreHull } from "@/db/schema/items";
 import type { PgTransaction } from "drizzle-orm/pg-core";
 
 /**
- * 기술영업 벤더 가능 아이템 목록 조회 (조인 포함)
+ * 새로운 스키마에 맞는 기술영업 벤더 가능 아이템 목록 조회 (조인 포함)
+ * techVendorPossibleItems는 shipbuildingItemId, offshoreTopItemId, offshoreHullItemId 중 하나만 가짐
  */
 export async function selectTechVendorPossibleItemsWithJoin(
   tx: PgTransaction<any, any, any>,
@@ -19,22 +21,38 @@ export async function selectTechVendorPossibleItemsWithJoin(
     .select({
       id: techVendorPossibleItems.id,
       vendorId: techVendorPossibleItems.vendorId,
-      vendorCode: techVendorPossibleItems.vendorCode, // 테이블에서 직접 조회
-      vendorName: techVendors.vendorName,
-      vendorEmail: techVendorPossibleItems.vendorEmail, // 테이블에서 직접 조회
-      techVendorType: techVendors.techVendorType,
-      vendorStatus: techVendors.status,
-      itemCode: techVendorPossibleItems.itemCode,
-      // 새로운 스키마: 테이블에서 직접 조회
-      workType: techVendorPossibleItems.workType,
-      shipTypes: techVendorPossibleItems.shipTypes,
-      itemList: techVendorPossibleItems.itemList,
-      subItemList: techVendorPossibleItems.subItemList,
+      shipbuildingItemId: techVendorPossibleItems.shipbuildingItemId,
+      offshoreTopItemId: techVendorPossibleItems.offshoreTopItemId,
+      offshoreHullItemId: techVendorPossibleItems.offshoreHullItemId,
       createdAt: techVendorPossibleItems.createdAt,
       updatedAt: techVendorPossibleItems.updatedAt,
+      // 벤더 정보
+      vendorCode: techVendors.vendorCode,
+      vendorName: techVendors.vendorName,
+      vendorEmail: techVendors.email,
+      vendorStatus: techVendors.status,
+      techVendorType: techVendors.techVendorType,
+      // 조선 아이템 정보 (shipbuildingItemId가 있을 때만)
+      shipItemCode: itemShipbuilding.itemCode,
+      shipWorkType: itemShipbuilding.workType,
+      shipItemList: itemShipbuilding.itemList,
+      shipTypes: itemShipbuilding.shipTypes,
+      // 해양 TOP 아이템 정보 (offshoreTopItemId가 있을 때만)
+      topItemCode: itemOffshoreTop.itemCode,
+      topWorkType: itemOffshoreTop.workType,
+      topItemList: itemOffshoreTop.itemList,
+      topSubItemList: itemOffshoreTop.subItemList,
+      // 해양 HULL 아이템 정보 (offshoreHullItemId가 있을 때만)
+      hullItemCode: itemOffshoreHull.itemCode,
+      hullWorkType: itemOffshoreHull.workType,
+      hullItemList: itemOffshoreHull.itemList,
+      hullSubItemList: itemOffshoreHull.subItemList,
     })
     .from(techVendorPossibleItems)
     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
+    .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
+    .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
     .where(where)
     .orderBy(...(orderBy || [desc(techVendorPossibleItems.createdAt)]))
     .limit(limit)
@@ -52,58 +70,70 @@ export async function countTechVendorPossibleItemsWithJoin(
     .select({ count: count() })
     .from(techVendorPossibleItems)
     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
+    .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
+    .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
     .where(where);
   
   return result.count;
 }
 
 /**
- * 새로운 필드들을 위한 그룹별 통계 조회
+ * 공종별 통계 조회 (새로운 스키마 적용)
  */
-export async function getTechVendorPossibleItemsGroupStats(
+export async function getWorkTypeStats(
   tx: PgTransaction<any, any, any>,
-  groupBy: 'workType' | 'shipTypes' | 'vendorCode' | 'vendorEmail',
   where?: SQL | undefined
 ) {
-  const groupField = techVendorPossibleItems[groupBy];
-  
-  return await tx
+  // 각 아이템 타입별로 별도 쿼리 실행 후 통합
+  const shipStats = await tx
     .select({
-      groupValue: groupField,
+      workType: itemShipbuilding.workType,
       count: count(),
       vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
-      itemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('itemCount'),
+      itemCount: sql<number>`COUNT(DISTINCT ${itemShipbuilding.itemCode})`.as('itemCount'),
     })
     .from(techVendorPossibleItems)
     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .innerJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
     .where(where)
-    .groupBy(groupField)
+    .groupBy(itemShipbuilding.workType)
     .orderBy(desc(count()));
-}
 
-/**
- * 공종별 통계 조회
- */
-export async function getWorkTypeStats(
-  tx: PgTransaction<any, any, any>,
-  where?: SQL | undefined
-) {
-  return await tx
+  const topStats = await tx
+    .select({
+      workType: itemOffshoreTop.workType,
+      count: count(),
+      vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
+      itemCount: sql<number>`COUNT(DISTINCT ${itemOffshoreTop.itemCode})`.as('itemCount'),
+    })
+    .from(techVendorPossibleItems)
+    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .innerJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
+    .where(where)
+    .groupBy(itemOffshoreTop.workType)
+    .orderBy(desc(count()));
+
+  const hullStats = await tx
     .select({
-      workType: techVendorPossibleItems.workType,
+      workType: itemOffshoreHull.workType,
       count: count(),
       vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
-      itemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('itemCount'),
+      itemCount: sql<number>`COUNT(DISTINCT ${itemOffshoreHull.itemCode})`.as('itemCount'),
     })
     .from(techVendorPossibleItems)
     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .innerJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
     .where(where)
-    .groupBy(techVendorPossibleItems.workType)
+    .groupBy(itemOffshoreHull.workType)
     .orderBy(desc(count()));
+
+  // 결과 통합
+  return [...shipStats, ...topStats, ...hullStats];
 }
 
 /**
- * 선종별 통계 조회
+ * 선종별 통계 조회 (조선 아이템만 해당)
  */
 export async function getShipTypeStats(
   tx: PgTransaction<any, any, any>,
@@ -111,15 +141,16 @@ export async function getShipTypeStats(
 ) {
   return await tx
     .select({
-      shipTypes: techVendorPossibleItems.shipTypes,
+      shipTypes: itemShipbuilding.shipTypes,
       count: count(),
       vendorCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.vendorId})`.as('vendorCount'),
-      itemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('itemCount'),
+      itemCount: sql<number>`COUNT(DISTINCT ${itemShipbuilding.itemCode})`.as('itemCount'),
     })
     .from(techVendorPossibleItems)
     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .innerJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
     .where(where)
-    .groupBy(techVendorPossibleItems.shipTypes)
+    .groupBy(itemShipbuilding.shipTypes)
     .orderBy(desc(count()));
 }
 
@@ -133,24 +164,61 @@ export async function getVendorStats(
   return await tx
     .select({
       vendorId: techVendorPossibleItems.vendorId,
-      vendorCode: techVendorPossibleItems.vendorCode,
+      vendorCode: techVendors.vendorCode,
       vendorName: techVendors.vendorName,
-      vendorEmail: techVendorPossibleItems.vendorEmail,
+      vendorEmail: techVendors.email,
+      vendorStatus: techVendors.status,
       itemCount: count(),
-      distinctItemCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.itemCode})`.as('distinctItemCount'),
-      workTypeCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.workType})`.as('workTypeCount'),
-      shipTypeCount: sql<number>`COUNT(DISTINCT ${techVendorPossibleItems.shipTypes})`.as('shipTypeCount'),
       latestUpdate: sql<Date>`MAX(${techVendorPossibleItems.updatedAt})`.as('latestUpdate'),
     })
     .from(techVendorPossibleItems)
     .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .leftJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
+    .leftJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
+    .leftJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
     .where(where)
     .groupBy(
       techVendorPossibleItems.vendorId,
-      techVendorPossibleItems.vendorCode,
+      techVendors.vendorCode,
       techVendors.vendorName,
-      techVendorPossibleItems.vendorEmail
+      techVendors.email,
+      techVendors.status
     )
     .orderBy(desc(count()));
 }
 
+/**
+ * 아이템 타입별 통계 조회 (조선, 해양TOP, 해양HULL)
+ */
+export async function getItemTypeStats(
+  tx: PgTransaction<any, any, any>,
+  where?: SQL | undefined
+) {
+  const [shipCount] = await tx
+    .select({ count: count() })
+    .from(techVendorPossibleItems)
+    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .innerJoin(itemShipbuilding, eq(techVendorPossibleItems.shipbuildingItemId, itemShipbuilding.id))
+    .where(where);
+
+  const [topCount] = await tx
+    .select({ count: count() })
+    .from(techVendorPossibleItems)
+    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .innerJoin(itemOffshoreTop, eq(techVendorPossibleItems.offshoreTopItemId, itemOffshoreTop.id))
+    .where(where);
+
+  const [hullCount] = await tx
+    .select({ count: count() })
+    .from(techVendorPossibleItems)
+    .innerJoin(techVendors, eq(techVendorPossibleItems.vendorId, techVendors.id))
+    .innerJoin(itemOffshoreHull, eq(techVendorPossibleItems.offshoreHullItemId, itemOffshoreHull.id))
+    .where(where);
+
+  return [
+    { itemType: "조선", count: shipCount.count },
+    { itemType: "해양TOP", count: topCount.count },
+    { itemType: "해양HULL", count: hullCount.count },
+  ];
+}
+