1 files changed, 37 insertions, 1 deletions
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts
index 90900517..60b40f21 100644
--- a/db/schema/vendors.ts
+++ b/db/schema/vendors.ts
@@ -1,6 +1,6 @@
 // db/schema/vendors.ts
 import {  pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView} from "drizzle-orm/pg-core";
-import { items } from "./items";
+import { items, materials } from "./items";
 import { sql, eq, relations } from "drizzle-orm";
 import { users } from "./users";
 import { vendorPQSubmissions } from "./pq";
@@ -384,6 +384,18 @@ export const vendorPossibleItems = pgTable("vendor_possible_items", {
   updatedAt: timestamp("updated_at").defaultNow().notNull(),
 });
 
+
+export const vendorPossibleMateirals = pgTable("vendor_possible_materials", {
+  id: serial("id").primaryKey(),
+  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
+  // itemId: integer("item_id"), // 별도 item 테이블 연동시
+  itemCode: varchar("item_code", { length: 100 })
+    .notNull()
+    .references(() => materials.itemCode, { onDelete: "cascade" }),
+  createdAt: timestamp("created_at").defaultNow().notNull(),
+  updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
 export const vendorItemsView = pgView("vendor_items_view").as((qb) => {
   return qb
     .select({
@@ -404,6 +416,29 @@ export const vendorItemsView = pgView("vendor_items_view").as((qb) => {
     .leftJoin(items, eq(vendorPossibleItems.itemCode, items.itemCode))
 })
 
+export const vendorMaterialsView = pgView("vendor_materials_view").as((qb) => {
+  return qb
+    .select({
+      // vendorPossibleItems의 "id" -> "vendorItemId"
+      vendorItemId: vendorPossibleMateirals.id,
+      vendorId: vendorPossibleMateirals.vendorId,
+
+      // items의 "id" -> "itemId"
+      // itemId: items.id,
+      itemName: materials.itemName,
+      itemCode: materials.itemCode,
+      description: materials.description,
+      unitOfMeasure: materials.unitOfMeasure,
+      steelType: materials.steelType,
+      gradeMaterial: materials.gradeMaterial,
+
+      createdAt: vendorPossibleMateirals.createdAt,
+      updatedAt: vendorPossibleMateirals.updatedAt,
+    })
+    .from(vendorPossibleMateirals)
+    .leftJoin(materials, eq(vendorPossibleMateirals.itemCode, materials.itemCode))
+})
+
 export const vendorAttachments = pgTable("vendor_attachments", {
   id: serial("id").primaryKey(),
   vendorId: integer("vendor_id").references(() => vendors.id),
@@ -429,6 +464,7 @@ export type VendorWithAttachments = Vendor & {
 }
 
 export type VendorItemsView = typeof vendorItemsView.$inferSelect
+export type VendorMaterialsView = typeof vendorMaterialsView.$inferSelect
 
 // ------- [시작] MDG 대응을 위한 새로운 테이블 타입 정의 -------------
 export type VendorBusinessGroup = typeof vendorBusinessGroups.$inferSelect