(김준회) AVL 피드백 반영 (이진용 프로 건)

1 files changed, 25 insertions, 2 deletions

diff --git a/db/schema/avl/avl.ts b/db/schema/avl/avl.ts
index 0b983168..30c82ced 100644
--- a/db/schema/avl/avl.ts
+++ b/db/schema/avl/avl.ts
@@ -1,4 +1,4 @@
-import { pgTable, boolean, integer, timestamp, varchar, decimal, json, pgView } from "drizzle-orm/pg-core";
+import { pgTable, boolean, integer, timestamp, varchar, decimal, json, pgView, uniqueIndex } from "drizzle-orm/pg-core";
 import { createInsertSchema, createSelectSchema } from "drizzle-zod";
 import { sql } from "drizzle-orm";
 
@@ -118,7 +118,30 @@ export const avlVendorInfo = pgTable("avl_vendor_info", {
   // 타임스탬프
   createdAt: timestamp("created_at").defaultNow(),
   updatedAt: timestamp("updated_at").defaultNow(),
-});
+}, (table) => ({
+  // 표준 AVL용 unique 제약조건 (isTemplate = true)
+  // 표준AVL용 필드들 + 자재그룹코드 + 협력업체명이 unique해야 함
+  uniqueStandardAvl: uniqueIndex("unique_standard_avl_material_vendor")
+    .on(
+      table.constructionSector,
+      table.shipType,
+      table.avlKind,
+      table.htDivision,
+      table.materialGroupCode,
+      table.vendorName
+    )
+    .where(sql`${table.isTemplate} = true`),
+  
+  // 프로젝트 AVL용 unique 제약조건 (isTemplate = false)
+  // 같은 projectCode 내에서 자재그룹코드 + 협력업체명이 unique해야 함
+  uniqueProjectAvl: uniqueIndex("unique_project_avl_material_vendor")
+    .on(
+      table.projectCode,
+      table.materialGroupCode,
+      table.vendorName
+    )
+    .where(sql`${table.isTemplate} = false`),
+}));
 
 // Zod 스키마 생성
 export const insertAvlListSchema = createInsertSchema(avlList);