(대표님) 20250601까지 작업사항

4 files changed, 108 insertions, 24 deletions

diff --git a/db/schema/items.ts b/db/schema/items.ts
index 4fd34506..61643759 100644
--- a/db/schema/items.ts
+++ b/db/schema/items.ts
@@ -4,8 +4,11 @@ import { pgTable, varchar, text, timestamp ,serial, integer, pgEnum} from "drizz
 // 자재 아이템 정보 테이블 (items) - 기존 CMCTB_MAT_CLAS 테이블 매핑 (SOAP 연결 시 컬럼이 추가/삭제될 수 있음)
 export const items = pgTable("items", {
   id: serial("id").primaryKey(), // 고유 식별자 (신규 추가)
+  ProjectNo: varchar("project_no", { length: 100 }).notNull(), // CLAS_CD - 아이템 코드 (자재 클래스 코드)
   itemCode: varchar("item_code", { length: 100 }).unique(), // CLAS_CD - 아이템 코드 (자재 클래스 코드)
   itemName: varchar("item_name", { length: 255 }).notNull(), // CLAS_NM - 아이템명 (자재 클래스명)
+  packageCode: varchar("package_code", { length: 255 }).notNull(), // CLAS_NM - 아이템명 (자재 클래스명)
+  smCode: varchar("sm_code", { length: 255 }), // CLAS_NM - 아이템명 (자재 클래스명)
   description: text("description"), // CLAS_DTL - 아이템 상세 설명 (클래스 내역)
   parentItemCode: varchar("parent_item_code", { length: 18 }), // PRNT_CLAS_CD - 부모 아이템 코드 (부모 클래스 코드)
   itemLevel: integer("item_level"), // CLAS_LVL - 아이템 레벨 (클래스 레벨)
@@ -19,7 +22,26 @@ export const items = pgTable("items", {
   updatedAt: timestamp("updated_at").defaultNow().notNull(), // 수정일시 (신규 추가)
 });
 
+export const materials = pgTable("materials", {
+  id: serial("id").primaryKey(), // 고유 식별자 (신규 추가)
+  itemCode: varchar("item_code", { length: 100 }).unique(), // CLAS_CD - 아이템 코드 (자재 클래스 코드)
+  itemName: varchar("item_name", { length: 255 }).notNull(), // CLAS_NM - 아이템명 (자재 클래스명)
+  description: text("description"), // CLAS_DTL - 아이템 상세 설명 (클래스 내역)
+  parentItemCode: varchar("parent_item_code", { length: 18 }), // PRNT_CLAS_CD - 부모 아이템 코드 (부모 클래스 코드)
+  itemLevel: integer("item_level"), // CLAS_LVL - 아이템 레벨 (클래스 레벨)
+  deleteFlag: varchar("delete_flag", { length: 1 }), // DEL_ORDR - 삭제 지시자 (Y/N)
+  unitOfMeasure: varchar("unit_of_measure", { length: 3 }), // UOM - 단위 (UOM)
+  steelType: varchar("steel_type", { length: 2 }), // STYPE - 강종 (Steel Type)
+  gradeMaterial: varchar("grade_material", { length: 50 }), // GRD_MATL - 등급 재질 (Grade Material)
+  changeDate: varchar("change_date", { length: 8 }), // CHG_DT - 수정일자 (YYYYMMDD)
+  baseUnitOfMeasure: varchar("base_unit_of_measure", { length: 3 }), // BSE_UOM - 기준 단위 (Base UOM)
+  createdAt: timestamp("created_at").defaultNow().notNull(), // 생성일시 (신규 추가)
+  updatedAt: timestamp("updated_at").defaultNow().notNull(), // 수정일시 (신규 추가)
+});
+
+
 export type Item = typeof items.$inferSelect
+export type Materials = typeof materials.$inferSelect
 
 export const itemsRelations = relations(items, ({ many }) => ({
   shipbuilding: many(itemShipbuilding),
diff --git a/db/schema/procurementRFQ.ts b/db/schema/procurementRFQ.ts
index 598bcd95..a69e8fba 100644
--- a/db/schema/procurementRFQ.ts
+++ b/db/schema/procurementRFQ.ts
@@ -2,7 +2,6 @@ import { foreignKey, pgTable, pgView, serial, varchar, text, timestamp, boolean,
 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(
@@ -19,9 +18,12 @@ export const procurementRfqs = pgTable(
 
     series: varchar("series", { length: 50 }),
 
-    itemId: integer("item_id")
-      .notNull()
-      .references(() => items.id, { onDelete: "cascade" }),
+    // itemId: integer("item_id")
+    //   .notNull()
+    //   .references(() => items.id, { onDelete: "cascade" }),
+
+      itemCode: varchar("item_code", { length: 100 }),
+      itemName: varchar("item_name", { length: 255 }),
 
     dueDate: date("due_date", { mode: "date" })
       .$type<Date>()
@@ -126,8 +128,8 @@ export const prItems = pgTable(
     prItem: varchar("pr_item", { length: 50 }), // 단위
     prNo: varchar("pr_no", { length: 50 }), // 단위
 
-    itemId: integer("item_id")
-      .references(() => items.id, { onDelete: "set null" }),
+    // itemId: integer("item_id")
+    //   .references(() => items.id, { onDelete: "set null" }),
 
     materialCode: varchar("material_code", { length: 255 }),
     materialCategory: varchar("material_category", { length: 255 }),
@@ -182,8 +184,8 @@ export const procurementRfqsView = pgView("procurement_rfqs_view").as((qb) => {
       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"),
+      itemCode: sql<string | null>`${procurementRfqs.itemCode}`.as("item_code"),
+      itemName: sql<string | null>`${procurementRfqs.itemName}`.as("item_name"),
       
       // Status and dates
       status: sql<string>`${procurementRfqs.status}`.as("status"),
@@ -231,7 +233,7 @@ export const procurementRfqsView = pgView("procurement_rfqs_view").as((qb) => {
     })
     .from(procurementRfqs)
     .leftJoin(projects, eq(procurementRfqs.projectId, projects.id))
-    .leftJoin(items, eq(procurementRfqs.itemId, items.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));
@@ -246,7 +248,7 @@ export const prItemsView = pgView("pr_items_view").as((qb) => {
       rfqItem: prItems.rfqItem,
       prItem: prItems.prItem,
       prNo: prItems.prNo,
-      itemId: prItems.itemId,
+      // itemId: prItems.itemId,
       materialCode: prItems.materialCode,
       materialCategory: prItems.materialCategory,
       acc: prItems.acc,
@@ -268,12 +270,11 @@ export const prItemsView = pgView("pr_items_view").as((qb) => {
       projectDl: prItems.projectDl,
       remark: prItems.remark,
       rfqCode: procurementRfqs.rfqCode,
-      itemCode: items.itemCode,
-      itemName: items.itemName
+      itemCode: procurementRfqs.itemCode,
+      itemName: procurementRfqs.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) => {
@@ -281,7 +282,6 @@ export const procurementRfqDetailsView = pgView("procurement_rfq_details_view").
   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");
@@ -299,8 +299,8 @@ export const procurementRfqDetailsView = pgView("procurement_rfq_details_view").
       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"),
+      itemCode: sql<string | null>`${rfqsTable.itemCode}`.as("item_code"),
+      itemName: sql<string | null>`${rfqsTable.itemName}`.as("item_name"),
 
       // 벤더 관련 필드
       vendorName: sql<string | null>`${vendorsTable.vendorName}`.as("vendor_name"),
@@ -420,7 +420,7 @@ export const procurementRfqDetailsView = pgView("procurement_rfq_details_view").
     // 기존 조인 유지
     .leftJoin(rfqsTable, eq(rfqDetailsTable.procurementRfqsId, rfqsTable.id))
     .leftJoin(projectsTable, eq(rfqsTable.projectId, projectsTable.id))
-    .leftJoin(itemsTable, eq(rfqsTable.itemId, itemsTable.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))
@@ -669,10 +669,10 @@ export const procurementRfqsRelations = relations(
       fields: [procurementRfqs.projectId],
       references: [projects.id],
     }),
-    item: one(items, {
-      fields: [procurementRfqs.itemId],
-      references: [items.id],
-    }),
+    // item: one(items, {
+    //   fields: [procurementRfqs.itemId],
+    //   references: [items.id],
+    // }),
     createdByUser: one(users, {
       fields: [procurementRfqs.createdBy],
       references: [users.id],
@@ -760,7 +760,7 @@ export const procurementVendorQuotationsRelations = relations(
       relationName: "quotationVendor" // 명시적 관계명 추가
     }),
     // Add this missing relation
-    items: many(procurementQuotationItems),
+    // items: many(procurementQuotationItems),
     // These are optional but good to have
     paymentTerms: one(paymentTerms, {
       fields: [procurementVendorQuotations.paymentTermsCode],
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index ad49f031..8c144f35 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -16,6 +16,8 @@ export const documents = pgTable(
       .notNull()
       .references(() => contracts.id, { onDelete: "cascade" }),
     docNumber: varchar("doc_number", { length: 100 }).notNull(),
+    // ✅ 벤더용 문서 번호 추가 (옵셔널)
+    vendorDocNumber: varchar("vendor_doc_number", { length: 100 }),
     title: varchar("title", { length: 255 }).notNull(),
     status: varchar("status", { length: 50 })
       .notNull()
@@ -31,10 +33,14 @@ export const documents = pgTable(
         table.docNumber,
         table.status
       ),
+      // ✅ 벤더 문서 번호가 있는 경우 유니크 제약 조건 (옵셔널이므로 별도 인덱스)
+      uniqueContractVendorDoc: uniqueIndex("unique_contract_vendor_doc").on(
+        table.contractId,
+        table.vendorDocNumber
+      ).where(sql`${table.vendorDocNumber} IS NOT NULL`),
     }
   }
 )
-
 // 확장된 issueStages 테이블
 export const issueStages = pgTable(
   "issue_stages",
@@ -100,6 +106,7 @@ export const revisions = pgTable(
     
     // 상세 날짜 추적
     submittedDate: date("submitted_date"),
+    uploadedAt: date("uploaded_at"),
     reviewStartDate: date("review_start_date"),
     approvedDate: date("approved_date"),
     rejectedDate: date("rejected_date"),
@@ -145,12 +152,18 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
   // 기본 문서 정보
   documentId: integer("document_id").notNull(),
   docNumber: varchar("doc_number", { length: 100 }).notNull(),
+  vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), // ✅ 벤더 문서 번호 추가
   title: varchar("title", { length: 255 }).notNull(),
   pic: varchar("pic", { length: 50 }),
   status: varchar("status", { length: 50 }).notNull(),
   issuedDate: date("issued_date"),
   contractId: integer("contract_id").notNull(),
   
+  // ✅ 프로젝트 및 벤더 정보 추가
+  projectCode: varchar("project_code", { length: 50 }),
+  vendorName: varchar("vendor_name", { length: 255 }),
+  vendorCode: varchar("vendor_code", { length: 50 }),
+  
   // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지)
   currentStageId: integer("current_stage_id"),
   currentStageName: varchar("current_stage_name", { length: 100 }),
@@ -199,6 +212,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
       revisionStatus: string;
       submittedDate: string | null;
       approvedDate: string | null;
+      uploadedAt: string | null;
       reviewStartDate: string | null;
       rejectedDate: string | null;
       reviewerId: number | null;
@@ -321,6 +335,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
             'comment', r.comment,
             'revisionStatus', r.revision_status,
             'submittedDate', r.submitted_date,
+            'uploadedAt', r.uploaded_at,
             'approvedDate', r.approved_date,
             'reviewStartDate', r.review_start_date,
             'rejectedDate', r.rejected_date,
@@ -373,12 +388,18 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
   SELECT 
     d.id as document_id,
     d.doc_number,
+    d.vendor_doc_number, -- ✅ 벤더 문서 번호 추가
     d.title,
     d.pic,
     d.status,
     d.issued_date,
     d.contract_id,
     
+    -- ✅ 프로젝트 및 벤더 정보 추가
+    p.code as project_code,
+    v.vendor_name as vendor_name,
+    v.vendor_code as vendor_code,
+    
     -- 현재 스테이지 정보
     csi.current_stage_id,
     csi.current_stage_name,
@@ -415,6 +436,11 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
     d.updated_at
     
   FROM documents d
+  -- ✅ contracts, projects, vendors 테이블 JOIN 추가
+  LEFT JOIN contracts c ON d.contract_id = c.id
+  LEFT JOIN projects p ON c.project_id = p.id
+  LEFT JOIN vendors v ON c.vendor_id = v.id
+  
   LEFT JOIN document_stats ds ON d.id = ds.document_id
   LEFT JOIN current_stage_info csi ON d.id = csi.document_id
   LEFT JOIN latest_revision_info lri ON d.id = lri.document_id
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