(대표님) 스키마 변경사항항

4 files changed, 793 insertions, 0 deletions

diff --git a/db/schema/index.ts b/db/schema/index.ts
index 189519e8..537cc16c 100644
--- a/db/schema/index.ts
+++ b/db/schema/index.ts
@@ -12,3 +12,4 @@ export * from './vendors';
 export * from './tasks';
 export * from './logs';
 export * from './basicContractDocumnet';
+export * from './procurementRFQ';
diff --git a/db/schema/procurementRFQ.ts b/db/schema/procurementRFQ.ts
new file mode 100644
index 00000000..eb83afa8
--- /dev/null
+++ b/db/schema/procurementRFQ.ts
@@ -0,0 +1,788 @@
+import { foreignKey, pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, alias, check } from "drizzle-orm/pg-core";
+import { eq, sql, and, or, relations } from "drizzle-orm";
+import { projects } from "./projects";
+import { users } from "./users";
+import { items } from "./items";
+import { vendors } from "./vendors";
+
+export const procurementRfqs = pgTable(
+  "procurement_rfqs",
+  {
+    id: serial("id").primaryKey(),
+
+    // RFQ 고유 코드
+    rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001"
+
+    // 프로젝트 참조
+    projectId: integer("project_id")
+      .references(() => projects.id, { onDelete: "set null" }),
+
+    series: varchar("series", { length: 50 }),
+
+    itemId: integer("item_id")
+      .notNull()
+      .references(() => items.id, { onDelete: "cascade" }),
+
+    dueDate: date("due_date", { mode: "date" })
+      .$type<Date>()
+      .notNull(),
+
+    rfqSendDate: date("rfq_send_date", { mode: "date" })
+      .$type<Date | null>(), // notNull() 제약조건 제거, null 허용
+
+    status: varchar("status", { length: 30 })
+      .$type<"RFQ Created" | "RFQ Vendor Assignned" | "RFQ Sent" | "Quotation Analysis" | "PO Transfer" | "PO Create">()
+      .default("RFQ Created")
+      .notNull(),
+
+    rfqSealedYn: boolean("rfq_sealed_yn").default(false),
+    picCode: varchar("pic_code", { length: 50 }),
+
+    remark: text("remark"),
+    // 생성자
+
+    sentBy: integer("sent_by")
+      .references(() => users.id, { onDelete: "set null" }),
+
+
+    createdBy: integer("created_by")
+      .notNull()
+      .references(() => users.id, { onDelete: "set null" }),
+
+    updatedBy: integer("updated_by")
+      .notNull()
+      .references(() => users.id, { onDelete: "set null" }),
+
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+  },
+
+);
+
+export const paymentTerms = pgTable("payment_terms", {
+  code: varchar("code", { length: 50 }).primaryKey(),
+  description: varchar("description", { length: 255 }).notNull(),
+  // days: integer("days").notNull(),
+  isActive: boolean("is_active").default(true).notNull(),
+  createdBy: integer("created_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+});
+
+// 인코텀즈 테이블
+export const incoterms = pgTable("incoterms", {
+  code: varchar("code", { length: 20 }).primaryKey(),
+  description: varchar("description", { length: 255 }).notNull(),
+  isActive: boolean("is_active").default(true).notNull(),
+  createdBy: integer("created_by")
+    .references(() => users.id, { onDelete: "set null" }),
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+});
+
+export const procurementRfqDetails = pgTable(
+  "procurement_rfq_details",
+  {
+    id: serial("id").primaryKey(),
+    procurementRfqsId: integer("procurement_rfqs_id")
+      .references(() => procurementRfqs.id, { onDelete: "set null" }),
+    vendorsId: integer("vendors_id")
+      .references(() => vendors.id, { onDelete: "set null" }),
+    currency: varchar("currency", { length: 10 }).default("USD"),
+
+    // 정규화된 paymentTerms 참조
+    paymentTermsCode: varchar("payment_terms_code", { length: 50 })
+      .references(() => paymentTerms.code, { onDelete: "set null" }),
+    // paymentTerms 필드는 제거 (코드로 조회)
+
+    // 정규화된 incoterms 참조
+    incotermsCode: varchar("incoterms_code", { length: 20 })
+      .references(() => incoterms.code, { onDelete: "set null" }),
+    incotermsDetail: varchar("incoterms_detail", { length: 255 }),
+
+    deliveryDate: date("delivery_date", { mode: "date" })
+      .$type<Date>()
+      .notNull(),
+    taxCode: varchar("tax_code", { length: 255 }).default("VV"),
+    placeOfShipping: varchar("place_of_shipping", { length: 255 }),
+    placeOfDestination: varchar("place_of_destination", { length: 255 }),
+    remark: text("remark"),
+    cancelReason: text("cancel_reason"),
+    updatedBy: integer("updated_by")
+      .notNull()
+      .references(() => users.id, { onDelete: "set null" }),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+    materialPriceRelatedYn: boolean("material_price_related_yn").default(false),
+  }
+);
+
+export const prItems = pgTable(
+  "pr_items",
+  {
+    id: serial("id").primaryKey(),
+    procurementRfqsId: integer("procurement_rfqs_id")
+      .references(() => procurementRfqs.id, { onDelete: "set null" }),
+
+    rfqItem: varchar("rfq_item", { length: 50 }), // 단위
+    prItem: varchar("pr_item", { length: 50 }), // 단위
+    prNo: varchar("pr_no", { length: 50 }), // 단위
+
+    itemId: integer("item_id")
+      .references(() => items.id, { onDelete: "set null" }),
+
+    materialCode: varchar("material_code", { length: 255 }),
+    materialCategory: varchar("material_category", { length: 255 }),
+    acc: varchar("acc", { length: 255 }),
+    materialDescription: varchar("material_description", { length: 255 }),
+    size: varchar("size", { length: 255 }),
+    deliveryDate: date("delivery_date", { mode: "date" })
+      .$type<Date>(),
+    quantity: numeric("quantity", { precision: 12, scale: 2 })
+      .$type<number>()
+      .default(1),
+    uom: varchar("uom", { length: 50 }), // 단위
+    grossWeight: numeric("gross_weight", { precision: 12, scale: 2 })
+      .$type<number>()
+      .default(1),
+    gwUom: varchar("gw_uom", { length: 50 }), // 단위
+
+    specNo: varchar("spec_no", { length: 255 }),
+    specUrl: varchar("spec_url", { length: 255 }),
+    trackingNo: varchar("tracking_no", { length: 255 }),
+
+    majorYn: boolean("major_yn").default(false),
+
+    projectDef: varchar("project_def", { length: 255 }),
+    projectSc: varchar("project_sc", { length: 255 }),
+    projectKl: varchar("project_kl", { length: 255 }),
+    projectLc: varchar("project_lc", { length: 255 }),
+    projectDl: varchar("project_dl", { length: 255 }),
+
+    remark: text("remark"),
+
+  },
+);
+
+
+//view
+export const procurementRfqsView = pgView("procurement_rfqs_view").as((qb) => {
+  const createdByUser = alias(users, "created_by_user");
+  const updatedByUser = alias(users, "updated_by_user");
+  const sentByUser = alias(users, "sent_by_user");
+  
+  return qb
+    .select({
+      // Basic RFQ identification
+      id: sql<number>`${procurementRfqs.id}`.as("id"),
+      rfqCode: sql<string>`${procurementRfqs.rfqCode}`.as("rfq_code"),
+      series: sql<string | null>`${procurementRfqs.series}`.as("series"),
+      rfqSealedYn: sql<string | null>`${procurementRfqs.rfqSealedYn}`.as("rfq_sealed_yn"),
+      
+      // Project information
+      projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+      projectName: sql<string | null>`${projects.name}`.as("project_name"),
+      
+      // Item information
+      itemCode: sql<string | null>`${items.itemCode}`.as("item_code"),
+      itemName: sql<string | null>`${items.itemName}`.as("item_name"),
+      
+      // Status and dates
+      status: sql<string>`${procurementRfqs.status}`.as("status"),
+      picCode: sql<string | null>`${procurementRfqs.picCode}`.as("pic_code"),
+      rfqSendDate: sql<Date | null>`${procurementRfqs.rfqSendDate}`.as("rfq_send_date"),
+      dueDate: sql<Date | null>`${procurementRfqs.dueDate}`.as("due_date"),
+      
+      // 가장 빠른 견적서 제출 날짜 추가
+      earliestQuotationSubmittedAt: sql<Date | null>`(
+        SELECT MIN(submitted_at)
+        FROM procurement_vendor_quotations
+        WHERE rfq_id = ${procurementRfqs.id}
+        AND submitted_at IS NOT NULL
+      )`.as("earliest_quotation_submitted_at"),
+      
+      // Audit information
+      createdByUserName: sql<string | null>`${createdByUser.name}`.as("created_by_user_name"),
+      sentByUserName: sql<string | null>`${sentByUser.name}`.as("sent_by_user_name"),
+      updatedAt: sql<Date>`${procurementRfqs.updatedAt}`.as("updated_at"),
+      updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"),
+      remark: sql<string | null>`${procurementRfqs.remark}`.as("remark"),
+      
+      // Related item information
+      majorItemMaterialCode: sql<string | null>`(
+        SELECT material_code 
+        FROM pr_items 
+        WHERE procurement_rfqs_id = ${procurementRfqs.id}
+        AND major_yn = true
+        LIMIT 1
+      )`.as("major_item_material_code"),
+      
+      poNo: sql<string | null>`(
+        SELECT pr_no 
+        FROM pr_items 
+        WHERE procurement_rfqs_id = ${procurementRfqs.id}
+        AND major_yn = true
+        LIMIT 1
+      )`.as("po_no"),
+      
+      prItemsCount: sql<number>`(
+        SELECT COUNT(*) 
+        FROM pr_items 
+        WHERE procurement_rfqs_id = ${procurementRfqs.id}
+      )`.as("pr_items_count")
+    })
+    .from(procurementRfqs)
+    .leftJoin(projects, eq(procurementRfqs.projectId, projects.id))
+    .leftJoin(items, eq(procurementRfqs.itemId, items.id))
+    .leftJoin(createdByUser, eq(procurementRfqs.createdBy, createdByUser.id))
+    .leftJoin(updatedByUser, eq(procurementRfqs.updatedBy, updatedByUser.id))
+    .leftJoin(sentByUser, eq(procurementRfqs.sentBy, sentByUser.id));
+});
+
+// 수정된 pr_items_view
+export const prItemsView = pgView("pr_items_view").as((qb) => {
+  return qb
+    .select({
+      id: prItems.id,
+      procurementRfqsId: prItems.procurementRfqsId,
+      rfqItem: prItems.rfqItem,
+      prItem: prItems.prItem,
+      prNo: prItems.prNo,
+      itemId: prItems.itemId,
+      materialCode: prItems.materialCode,
+      materialCategory: prItems.materialCategory,
+      acc: prItems.acc,
+      materialDescription: prItems.materialDescription,
+      size: prItems.size,
+      deliveryDate: prItems.deliveryDate,
+      quantity: prItems.quantity,
+      uom: prItems.uom,
+      grossWeight: prItems.grossWeight, // 필드명 수정
+      gwUom: prItems.gwUom,
+      specNo: prItems.specNo,
+      specUrl: prItems.specUrl,
+      trackingNo: prItems.trackingNo,
+      majorYn: prItems.majorYn,
+      projectDef: prItems.projectDef,
+      projectSc: prItems.projectSc,
+      projectKl: prItems.projectKl,
+      projectLc: prItems.projectLc,
+      projectDl: prItems.projectDl,
+      remark: prItems.remark,
+      rfqCode: procurementRfqs.rfqCode,
+      itemCode: items.itemCode,
+      itemName: items.itemName
+    })
+    .from(prItems)
+    .leftJoin(procurementRfqs, eq(prItems.procurementRfqsId, procurementRfqs.id))
+    .leftJoin(items, eq(prItems.itemId, items.id));
+});
+
+export const procurementRfqDetailsView = pgView("procurement_rfq_details_view").as((qb) => {
+  // 기존 별칭 정의 유지
+  const rfqDetailsTable = alias(procurementRfqDetails, "rfq_details");
+  const rfqsTable = alias(procurementRfqs, "rfqs");
+  const projectsTable = alias(projects, "projects");
+  const itemsTable = alias(items, "items");
+  const vendorsTable = alias(vendors, "vendors");
+  const paymentTermsTable = alias(paymentTerms, "payment_terms");
+  const incotermsTable = alias(incoterms, "incoterms");
+  const updatedByUser = alias(users, "updated_by_user");
+
+  return qb
+    .select({
+      // procurementRfqDetails 필드
+      detailId: sql<number>`${rfqDetailsTable.id}`.as("detail_id"),
+      rfqId: sql<number>`${rfqsTable.id}`.as("rfq_id"),
+      rfqCode: sql<string>`${rfqsTable.rfqCode}`.as("rfq_code"),
+
+      // 프로젝트 관련 필드
+      projectCode: sql<string | null>`${projectsTable.code}`.as("project_code"),
+      projectName: sql<string | null>`${projectsTable.name}`.as("project_name"),
+
+      // 아이템 관련 필드
+      itemCode: sql<string | null>`${itemsTable.itemCode}`.as("item_code"),
+      itemName: sql<string | null>`${itemsTable.itemName}`.as("item_name"),
+
+      // 벤더 관련 필드
+      vendorName: sql<string | null>`${vendorsTable.vendorName}`.as("vendor_name"),
+      vendorCode: sql<string | null>`${vendorsTable.vendorCode}`.as("vendor_code"),
+      vendorId: sql<string | null>`${vendorsTable.id}`.as("vendor_id"),
+      vendorCountry: sql<string | null>`${vendorsTable.country}`.as("vendor_country"),
+
+      // RFQ 상세 정보 필드
+      currency: sql<string | null>`${rfqDetailsTable.currency}`.as("currency"),
+      paymentTermsCode: sql<string | null>`${paymentTermsTable.code}`.as("payment_terms_code"),
+      paymentTermsDescription: sql<string | null>`${paymentTermsTable.description}`.as("payment_terms_description"),
+      incotermsCode: sql<string | null>`${incotermsTable.code}`.as("incoterms_code"),
+      incotermsDescription: sql<string | null>`${incotermsTable.description}`.as("incoterms_description"),
+      incotermsDetail: sql<string | null>`${rfqDetailsTable.incotermsDetail}`.as("incoterms_detail"),
+      deliveryDate: sql<Date | null>`${rfqDetailsTable.deliveryDate}`.as("delivery_date"),
+      taxCode: sql<string | null>`${rfqDetailsTable.taxCode}`.as("tax_code"),
+      placeOfShipping: sql<string | null>`${rfqDetailsTable.placeOfShipping}`.as("place_of_shipping"),
+      placeOfDestination: sql<string | null>`${rfqDetailsTable.placeOfDestination}`.as("place_of_destination"),
+      materialPriceRelatedYn: sql<boolean | null>`${rfqDetailsTable.materialPriceRelatedYn}`.as("material_price_related_yn"),
+
+      // 업데이트 관련 필드
+      updatedByUserName: sql<string | null>`${updatedByUser.name}`.as("updated_by_user_name"),
+      updatedAt: sql<Date | null>`${rfqDetailsTable.updatedAt}`.as("updated_at"),
+
+      // 추가적으로 pr_items 개수도 카운트하는 필드 추가
+      prItemsCount: sql<number>`(
+              SELECT COUNT(*) 
+              FROM pr_items 
+              WHERE procurement_rfqs_id = ${rfqsTable.id}
+          )`.as("pr_items_count"),
+
+      // 메이저 아이템 개수도 카운트
+      majorItemsCount: sql<number>`(
+              SELECT COUNT(*) 
+              FROM pr_items 
+              WHERE procurement_rfqs_id = ${rfqsTable.id} 
+              AND major_yn = true
+          )`.as("major_items_count"),
+
+      // 새로운 필드 추가: 코멘트 수 카운트
+      commentCount: sql<number>`(
+                SELECT COUNT(*) 
+                FROM procurement_rfq_comments 
+                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+            )`.as("comment_count"),
+
+      // 새로운 필드 추가: 최근 코멘트 날짜
+      lastCommentDate: sql<Date | null>`(
+                SELECT created_at 
+                FROM procurement_rfq_comments 
+                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+                ORDER BY created_at DESC LIMIT 1
+            )`.as("last_comment_date"),
+
+      // 새로운 필드 추가: 벤더가 마지막으로 코멘트한 날짜
+      lastVendorCommentDate: sql<Date | null>`(
+                SELECT created_at 
+                FROM procurement_rfq_comments 
+                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId} AND is_vendor_comment = true
+                ORDER BY created_at DESC LIMIT 1
+            )`.as("last_vendor_comment_date"),
+
+      // 새로운 필드 추가: 첨부파일 수 카운트
+      attachmentCount: sql<number>`(
+                SELECT COUNT(*) 
+                FROM procurement_rfq_attachments 
+                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+            )`.as("attachment_count"),
+
+      // 새로운 필드 추가: 견적서 제출 여부
+      hasQuotation: sql<boolean>`(
+                SELECT COUNT(*) > 0
+                FROM procurement_vendor_quotations
+                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+            )`.as("has_quotation"),
+
+      // 새로운 필드 추가: 견적서 상태
+      quotationStatus: sql<string | null>`(
+                SELECT status
+                FROM procurement_vendor_quotations
+                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+                ORDER BY created_at DESC LIMIT 1
+            )`.as("quotation_status"),
+
+      // 새로운 필드 추가: 견적서 총액
+      quotationTotalPrice: sql<number | null>`(
+                SELECT total_price
+                FROM procurement_vendor_quotations
+                WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+                ORDER BY created_at DESC LIMIT 1
+            )`.as("quotation_total_price"),
+
+      // 최신 견적서 버전
+      quotationVersion: sql<number | null>`(
+        SELECT quotation_version
+        FROM procurement_vendor_quotations
+        WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+        ORDER BY quotation_version DESC LIMIT 1
+    )`.as("quotation_version"),
+
+      // 총 견적서 버전 수
+      quotationVersionCount: sql<number>`(
+        SELECT COUNT(DISTINCT quotation_version)
+        FROM procurement_vendor_quotations
+        WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+    )`.as("quotation_version_count"),
+
+      // 마지막 견적서 생성 날짜
+      lastQuotationDate: sql<Date | null>`(
+        SELECT created_at
+        FROM procurement_vendor_quotations
+        WHERE rfq_id = ${rfqsTable.id} AND vendor_id = ${rfqDetailsTable.vendorsId}
+        ORDER BY quotation_version DESC LIMIT 1
+    )`.as("last_quotation_date"),
+    })
+    .from(rfqDetailsTable)
+    // 기존 조인 유지
+    .leftJoin(rfqsTable, eq(rfqDetailsTable.procurementRfqsId, rfqsTable.id))
+    .leftJoin(projectsTable, eq(rfqsTable.projectId, projectsTable.id))
+    .leftJoin(itemsTable, eq(rfqsTable.itemId, itemsTable.id))
+    .leftJoin(vendorsTable, eq(rfqDetailsTable.vendorsId, vendorsTable.id))
+    .leftJoin(paymentTermsTable, eq(rfqDetailsTable.paymentTermsCode, paymentTermsTable.code))
+    .leftJoin(incotermsTable, eq(rfqDetailsTable.incotermsCode, incotermsTable.code))
+    .leftJoin(updatedByUser, eq(rfqDetailsTable.updatedBy, updatedByUser.id));
+});
+
+export const procurementAttachments = pgTable(
+  "procurement_attachments",
+  {
+    id: serial("id").primaryKey(),
+    attachmentType: varchar("attachment_type", { length: 50 }).notNull(), // 'RFQ_COMMON', 'VENDOR_SPECIFIC'
+    procurementRfqsId: integer("procurement_rfqs_id")
+      .references(() => procurementRfqs.id, { onDelete: "cascade" }),
+    procurementRfqDetailsId: integer("procurement_rfq_details_id")
+      .references(() => procurementRfqDetails.id, { onDelete: "cascade" }),
+    fileName: varchar("file_name", { length: 255 }).notNull(),
+    originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+    filePath: varchar("file_path", { length: 512 }).notNull(),
+    fileSize: integer("file_size"),
+    fileType: varchar("file_type", { length: 100 }),
+    description: varchar("description", { length: 500 }),
+    createdBy: integer("created_by")
+      .references(() => users.id, { onDelete: "set null" })
+      .notNull(),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+  },
+  (table) => ({
+    attachmentTypeCheck: check(
+      "attachment_type_check",
+      sql`${table.procurementRfqsId} IS NOT NULL OR ${table.procurementRfqDetailsId} IS NOT NULL`
+    )
+  })
+);
+
+export type ProcurementRfqsView = typeof procurementRfqsView.$inferSelect;
+export type PrItemsView = typeof prItemsView.$inferSelect;
+export type ProcurementRfqDetailsView = typeof procurementRfqDetailsView.$inferSelect;
+
+
+//vendor response
+export const procurementVendorQuotations = pgTable(
+  "procurement_vendor_quotations",
+  {
+    id: serial("id").primaryKey(),
+    rfqId: integer("rfq_id")
+      .notNull()
+      .references(() => procurementRfqs.id, { onDelete: "cascade" }),
+    vendorId: integer("vendor_id")
+      .notNull()
+      .references(() => vendors.id, { onDelete: "set null" }),
+
+    // 견적 요약 정보
+    quotationCode: varchar("quotation_code", { length: 50 }),
+    quotationVersion: integer("quotation_version").default(1),
+    totalItemsCount: integer("total_items_count").default(0),
+    subTotal: numeric("sub_total").default("0"),
+    taxTotal: numeric("tax_total").default("0"),
+    discountTotal: numeric("discount_total").default("0"),
+    totalPrice: numeric("total_price").default("0"),
+    currency: varchar("currency", { length: 10 }).default("USD"),
+
+    // 견적 유효성 및 배송 정보
+    validUntil: date("valid_until", { mode: "date" }).$type<Date>(),
+    estimatedDeliveryDate: date("estimated_delivery_date", { mode: "date" }).$type<Date>(),
+
+    // 지불 조건 등 상세 정보
+    paymentTermsCode: varchar("payment_terms_code", { length: 50 })
+      .references(() => paymentTerms.code, { onDelete: "set null" }),
+    incotermsCode: varchar("incoterms_code", { length: 20 })
+      .references(() => incoterms.code, { onDelete: "set null" }),
+    incotermsDetail: varchar("incoterms_detail", { length: 255 }),
+
+    // 상태 관리
+    status: varchar("status", { length: 30 })
+      .$type<"Draft" | "Submitted" | "Revised" | "Rejected" | "Accepted">()
+      .default("Draft")
+      .notNull(),
+
+    // 기타 정보
+    remark: text("remark"),
+    rejectionReason: text("rejection_reason"),
+    submittedAt: timestamp("submitted_at"),
+    acceptedAt: timestamp("accepted_at"),
+
+    // 감사 필드
+    createdBy: integer("created_by"),
+    updatedBy: integer("updated_by"),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+  }
+);
+
+export const procurementRfqComments = pgTable(
+  "procurement_rfq_comments",
+  {
+    id: serial("id").primaryKey(),
+    rfqId: integer("rfq_id")
+      .notNull()
+      .references(() => procurementRfqs.id, { onDelete: "cascade" }),
+    vendorId: integer("vendor_id")
+      .references(() => vendors.id, { onDelete: "set null" }),
+    userId: integer("user_id")
+      .references(() => users.id, { onDelete: "set null" }),
+    content: text("content").notNull(),
+    isVendorComment: boolean("is_vendor_comment").default(false),
+    isRead: boolean("is_read").default(false), // 읽음 상태 추가
+    parentCommentId: integer("parent_comment_id"),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+  },
+  // 자기참조 FK 정의
+  (table) => {
+    return {
+      parentFk: foreignKey({
+        columns: [table.parentCommentId],
+        foreignColumns: [table.id],
+      }).onDelete("set null"),
+    };
+  }
+);
+
+export const procurementRfqAttachments = pgTable(
+  "procurement_rfq_attachments",
+  {
+    id: serial("id").primaryKey(),
+    rfqId: integer("rfq_id")
+      .notNull()
+      .references(() => procurementRfqs.id, { onDelete: "cascade" }),
+    commentId: integer("comment_id")
+      .references(() => procurementRfqComments.id, { onDelete: "cascade" }),
+    quotationId: integer("quotation_id")
+      .references(() => procurementVendorQuotations.id, { onDelete: "cascade" }),
+    fileName: varchar("file_name", { length: 255 }).notNull(),
+    fileSize: integer("file_size").notNull(),
+    fileType: varchar("file_type", { length: 100 }),
+    filePath: varchar("file_path", { length: 500 }).notNull(),
+    isVendorUpload: boolean("is_vendor_upload").default(false),
+    uploadedBy: integer("uploaded_by")
+      .references(() => users.id, { onDelete: "set null" }),
+    vendorId: integer("vendor_id")
+      .references(() => vendors.id, { onDelete: "set null" }),
+    uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
+  }
+);
+
+export const procurementQuotationItems = pgTable(
+  "procurement_quotation_items",
+  {
+    id: serial("id").primaryKey(),
+    quotationId: integer("quotation_id")
+      .notNull()
+      .references(() => procurementVendorQuotations.id, { onDelete: "cascade" }),
+
+    // PR 아이템과의 연결 추가
+    prItemId: integer("pr_item_id")
+      .notNull()
+      .references(() => prItems.id, { onDelete: "cascade" }),
+
+    // 원본 PR 아이템 정보 참조 (읽기 전용)
+    materialCode: varchar("material_code", { length: 50 }),
+    materialDescription: varchar("material_description", { length: 255 }),
+    quantity: numeric("quantity").notNull(),
+    uom: varchar("uom", { length: 20 }),
+
+    // 벤더가 입력하는 정보
+    unitPrice: numeric("unit_price").notNull(),
+    totalPrice: numeric("total_price").notNull(),
+    currency: varchar("currency", { length: 10 }).default("USD"),
+    vendorMaterialCode: varchar("vendor_material_code", { length: 50 }),
+    vendorMaterialDescription: varchar("vendor_material_description", { length: 255 }),
+
+    // 배송 관련 정보
+    deliveryDate: date("delivery_date", { mode: "date" }).$type<Date>(),
+    leadTimeInDays: integer("lead_time_in_days"),
+
+    // 세금 및 기타 정보
+    taxRate: numeric("tax_rate"),
+    taxAmount: numeric("tax_amount"),
+    discountRate: numeric("discount_rate"),
+    discountAmount: numeric("discount_amount"),
+
+    // 기타 정보
+    remark: text("remark"),
+    isAlternative: boolean("is_alternative").default(false),
+    isRecommended: boolean("is_recommended").default(false),
+    createdAt: timestamp("created_at").defaultNow().notNull(),
+    updatedAt: timestamp("updated_at").defaultNow().notNull(),
+  }
+);
+
+
+// procurementRfqComments 테이블의 관계 정의 - 타입 명시
+export const procurementRfqCommentsRelations = relations(
+  procurementRfqComments,
+  ({ one, many }) => ({
+    user: one(users, {
+      fields: [procurementRfqComments.userId],
+      references: [users.id],
+    }),
+    vendor: one(vendors, {
+      fields: [procurementRfqComments.vendorId],
+      references: [vendors.id],
+    }),
+    rfq: one(procurementRfqs, {
+      fields: [procurementRfqComments.rfqId],
+      references: [procurementRfqs.id],
+    }),
+    parentComment: one(procurementRfqComments, {
+      fields: [procurementRfqComments.parentCommentId],
+      references: [procurementRfqComments.id],
+      relationName: "commentHierarchy",
+    }),
+    childComments: many(procurementRfqComments, { relationName: "commentHierarchy" }),
+    attachments: many(procurementRfqAttachments, { relationName: "commentAttachments" }),
+  })
+);
+
+export const procurementRfqAttachmentsRelations = relations(
+  procurementRfqAttachments,
+  ({ one }) => ({
+    comment: one(procurementRfqComments, {
+      fields: [procurementRfqAttachments.commentId],
+      references: [procurementRfqComments.id],
+      relationName: "commentAttachments",
+    }),
+    rfq: one(procurementRfqs, {
+      fields: [procurementRfqAttachments.rfqId],
+      references: [procurementRfqs.id],
+    }),
+    uploader: one(users, {
+      fields: [procurementRfqAttachments.uploadedBy],
+      references: [users.id],
+    }),
+    vendor: one(vendors, {
+      fields: [procurementRfqAttachments.vendorId],
+      references: [vendors.id],
+    }),
+  })
+);
+
+
+export const procurementRfqsRelations = relations(
+  procurementRfqs,
+  ({ one, many }) => ({
+    project: one(projects, {
+      fields: [procurementRfqs.projectId],
+      references: [projects.id],
+    }),
+    item: one(items, {
+      fields: [procurementRfqs.itemId],
+      references: [items.id],
+    }),
+    createdByUser: one(users, {
+      fields: [procurementRfqs.createdBy],
+      references: [users.id],
+      relationName: "rfqCreator",
+    }),
+    updatedByUser: one(users, {
+      fields: [procurementRfqs.updatedBy],
+      references: [users.id],
+      relationName: "rfqUpdater",
+    }),
+    rfqDetails: many(procurementRfqDetails),
+    prItems: many(prItems, { relationName: "rfqPrItems" }),
+  })
+);
+
+export const prItemsRelations = relations(
+  prItems,
+  ({ one }) => ({
+    rfq: one(procurementRfqs, {
+      fields: [prItems.procurementRfqsId],
+      references: [procurementRfqs.id],
+      relationName: "rfqPrItems"
+    }),
+  })
+);
+
+// procurementRfqDetails 테이블의 관계 정의
+export const procurementRfqDetailsRelations = relations(
+  procurementRfqDetails,
+  ({ one }) => ({
+    rfq: one(procurementRfqs, {
+      fields: [procurementRfqDetails.procurementRfqsId],
+      references: [procurementRfqs.id],
+    }),
+    vendor: one(vendors, {
+      fields: [procurementRfqDetails.vendorsId],
+      references: [vendors.id],
+    }),
+    paymentTerms: one(paymentTerms, {
+      fields: [procurementRfqDetails.paymentTermsCode],
+      references: [paymentTerms.code],
+    }),
+    incoterms: one(incoterms, {
+      fields: [procurementRfqDetails.incotermsCode],
+      references: [incoterms.code],
+    }),
+    updatedByUser: one(users, {
+      fields: [procurementRfqDetails.updatedBy],
+      references: [users.id],
+    }),
+  })
+);
+
+export const vendorsRelations = relations(
+  vendors,
+  ({ many }) => ({
+    users: many(users, {
+      relationName: "vendorUsers"
+    }),
+  })
+);
+
+export const usersRelations = relations(
+  users,
+  ({ one }) => ({
+    vendor: one(vendors, {
+      fields: [users.companyId],
+      references: [vendors.id],
+      relationName: "vendorUsers"
+    }),
+  })
+);
+
+export const procurementVendorQuotationsRelations = relations(
+  procurementVendorQuotations,
+  ({ one, many }) => ({
+    rfq: one(procurementRfqs, {
+      fields: [procurementVendorQuotations.rfqId],
+      references: [procurementRfqs.id],
+    }),
+    vendor: one(vendors, {
+      fields: [procurementVendorQuotations.vendorId],
+      references: [vendors.id],
+    }),
+    // Add this missing relation
+    items: many(procurementQuotationItems),
+    // These are optional but good to have
+    paymentTerms: one(paymentTerms, {
+      fields: [procurementVendorQuotations.paymentTermsCode],
+      references: [paymentTerms.code],
+    }),
+    incoterms: one(incoterms, {
+      fields: [procurementVendorQuotations.incotermsCode],
+      references: [incoterms.code],
+    }),
+  })
+);
+
+export const procurementQuotationItemsRelations = relations(
+  procurementQuotationItems,
+  ({ one }) => ({
+    quotation: one(procurementVendorQuotations, {
+      fields: [procurementQuotationItems.quotationId],
+      references: [procurementVendorQuotations.id],
+    }),
+    prItem: one(prItems, {
+      fields: [procurementQuotationItems.prItemId],
+      references: [prItems.id],
+    }),
+  })
+);
+
+export type ProcurementVendorQuotations = typeof procurementVendorQuotations.$inferSelect;
diff --git a/db/schema/users.ts b/db/schema/users.ts
index 843ee2f3..8346c24b 100644
--- a/db/schema/users.ts
+++ b/db/schema/users.ts
@@ -16,6 +16,8 @@ export const users = pgTable("users", {
     .defaultNow()
     .notNull(),
   imageUrl: varchar("image_url", { length: 1024 }),
+  language: varchar("language", { length: 10 }).default("en"), // 언어 필드 추가 (기본값: 영어)
+
 
 });
 
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts
index f56a1288..182b7037 100644
--- a/db/schema/vendors.ts
+++ b/db/schema/vendors.ts
@@ -59,6 +59,8 @@ export const vendors = pgTable("vendors", {
   creditRating: varchar("credit_rating", { length: 50 }),
   cashFlowRating: varchar("cash_flow_rating", { length: 50 }),
 
+  businessSize: varchar("business_size", { length: 255 }),
+
   createdAt: timestamp("created_at").defaultNow().notNull(),
   updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });