벤더 코멘트 처리

1 files changed, 91 insertions, 12 deletions

diff --git a/db/schema/pq.ts b/db/schema/pq.ts
index 59ec8f07..76d15d5d 100644
--- a/db/schema/pq.ts
+++ b/db/schema/pq.ts
@@ -3,6 +3,7 @@ import {
     timestamp, uniqueIndex
 } from "drizzle-orm/pg-core";
 import { vendors } from "./vendors";
+import { projects } from "./projects";
 
 export const pqCriterias = pgTable("pq_criterias", {
     id: serial("id").primaryKey(),
@@ -17,21 +18,34 @@ export const pqCriterias = pgTable("pq_criterias", {
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
 
-export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", {
+export const pqCriteriasExtension = pgTable("pq_criterias_extension", {
     id: serial("id").primaryKey(),
-    vendorId: integer("vendor_id")
-    .notNull()
-    .references(() => vendors.id, { onDelete: "cascade" }),
-
-    criteriaId: integer("criteria_id")
-        .notNull()
-        .references(() => pqCriterias.id, { onDelete: "cascade" }),
-
-    answer: text("answer"),
-
+  
+    // pq_criterias와 연결 (1:1 or 1:N 중 필요에 맞춰 사용)
+    pqCriteriaId: integer("pq_criteria_id")
+      .notNull()
+      .references(() => pqCriterias.id, {
+        onDelete: "cascade",
+        onUpdate: "cascade",
+      }),
+  
+    // projects 테이블에 대한 FK
+    projectId: integer("project_id")
+      .notNull()
+      .references(() => projects.id, {
+        onDelete: "cascade",
+        onUpdate: "cascade",
+      }),
+  
+    // 프로젝트별 PQ 시 필요한 추가 정보
+    contractInfo: text("contract_info"),
+    additionalRequirement: text("additional_requirement"),
+  
     createdAt: timestamp("created_at").defaultNow().notNull(),
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
-});
+  });
+
+
 
 export const vendorCriteriaAttachments = pgTable("vendor_criteria_attachments", {
     id: serial("id").primaryKey(),
@@ -65,3 +79,68 @@ export const vendorPqReviewLogs = pgTable("vendor_pq_review_logs", {
     createdAt: timestamp("created_at").defaultNow().notNull(),
   })
 export type PqCriterias = typeof pqCriterias.$inferSelect
+
+
+// 벤더와 프로젝트 PQ 요청 연결 테이블
+export const vendorProjectPQs = pgTable("vendor_project_pqs", {
+  id: serial("id").primaryKey(),
+  
+  // vendors 테이블 FK
+  vendorId: integer("vendor_id")
+    .notNull()
+    .references(() => vendors.id, {
+      onDelete: "cascade",
+      onUpdate: "cascade",
+    }),
+  
+  // projects 테이블 FK
+  projectId: integer("project_id")
+    .notNull()
+    .references(() => projects.id, {
+      onDelete: "cascade",
+      onUpdate: "cascade",
+    }),
+  
+  // 상태: REQUESTED(요청됨), IN_PROGRESS(진행중), SUBMITTED(제출됨), APPROVED(승인됨), REJECTED(거부됨)
+  status: varchar("status", { length: 20 }).notNull().default("REQUESTED"),
+  
+  // 메타데이터
+  submittedAt: timestamp("submitted_at"),
+  approvedAt: timestamp("approved_at"),
+  rejectedAt: timestamp("rejected_at"),
+  rejectReason: text("reject_reason"),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+// 기존 vendorPqCriteriaAnswers 테이블에 projectId 필드 추가
+export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", {
+  id: serial("id").primaryKey(),
+  
+  vendorId: integer("vendor_id")
+    .notNull()
+    .references(() => vendors.id, {
+      onDelete: "cascade",
+      onUpdate: "cascade",
+    }),
+  
+  criteriaId: integer("criteria_id")
+    .notNull()
+    .references(() => pqCriterias.id, {
+      onDelete: "cascade",
+      onUpdate: "cascade",
+    }),
+  
+  // 추가: 프로젝트 ID (null은 일반 PQ를 의미)
+  projectId: integer("project_id")
+    .references(() => projects.id, {
+      onDelete: "cascade",
+      onUpdate: "cascade",
+    }),
+  
+  answer: text("answer"),
+  
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});