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

1 files changed, 27 insertions, 1 deletions

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