(대표님) rfq 히스토리, swp 등

2 files changed, 474 insertions, 11 deletions

diff --git a/db/schema/generalContract.ts b/db/schema/generalContract.ts
index 4d1de750..29479b20 100644
--- a/db/schema/generalContract.ts
+++ b/db/schema/generalContract.ts
@@ -146,6 +146,8 @@ export const generalContractItems = pgTable('general_contract_items', {
   // ═══════════════════════════════════════════════════════════════

   quantity: decimal('quantity', { precision: 15, scale: 3 }), // 수량

   quantityUnit: varchar('quantity_unit', { length: 50 }), // 수량단위

+  totalWeight: decimal('total_weight', { precision: 15, scale: 3 }), // 총 중량

+  weightUnit: varchar('weight_unit', { length: 50 }), // 중량단위

   contractDeliveryDate: date('contract_delivery_date'), // 계약납기일

   contractUnitPrice: decimal('contract_unit_price', { precision: 15, scale: 2 }), // 계약단가

   contractAmount: decimal('contract_amount', { precision: 15, scale: 2 }), // 계약금액

diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index c37bc2ec..3d9ad46c 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -1,7 +1,7 @@
 // enhanced-schema.ts
 import {
   pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb, boolean, serial,json,
-  index, text
+  index, text, bigint
 } from "drizzle-orm/pg-core"
 import { eq, sql } from "drizzle-orm";
 import { projects } from "./projects";
@@ -1362,6 +1362,19 @@ export const stageDocuments = pgTable(
       .references(() => projects.id, { onDelete: "cascade" }),
     vendorId: integer("vendor_id").notNull(), // vendors 테이블의 vendor_id 직접 저장
     contractId: integer("contract_id").notNull(),
+
+
+     // 구매자 시스템 동기화 필드 추가
+     buyerSystemStatus: varchar("buyer_system_status", { length: 50 }), // 승인(DC), 검토중, 반려 등
+     buyerSystemComment: text("buyer_system_comment"),
+     lastSyncedAt: timestamp("last_synced_at", { withTimezone: true }),
+     syncStatus: varchar("sync_status", { length: 20 }).default("pending"), // pending, synced, error, syncing
+     syncError: text("sync_error"),
+     
+     // 실시간 동기화를 위한 버전 관리
+     syncVersion: integer("sync_version").default(0), // 변경 추적용
+     lastModifiedBy: varchar("last_modified_by", { length: 100 }), // EVCP or BUYER_SYSTEM
+
     // 메타 정보
     createdAt: timestamp("created_at").defaultNow().notNull(),
     updatedAt: timestamp("updated_at").defaultNow().notNull(),
@@ -1384,11 +1397,16 @@ export const stageDocuments = pgTable(
       // 검색용 인덱스
       vendorIdIndex: index("stage_doc_vendor_id_idx").on(table.vendorId),
       statusIndex: index("stage_doc_status_idx").on(table.status),
+
+      // 동기화 관련 인덱스 추가
+      syncStatusIndex: index("stage_doc_sync_status_idx").on(table.syncStatus),
+      buyerStatusIndex: index("stage_doc_buyer_status_idx").on(table.buyerSystemStatus),
     }
   }
 )
 
 // 🎯 간소화된 스테이지 전용 뷰
+// db/schema/stage-documents.ts
 export const stageDocumentsView = pgView("stage_documents_view", {
   // 기본 문서 정보
   documentId: integer("document_id").notNull(),
@@ -1398,7 +1416,7 @@ export const stageDocumentsView = pgView("stage_documents_view", {
   status: varchar("status", { length: 50 }).notNull(),
   issuedDate: date("issued_date"),
   
-  // 프로젝트 및 벤더 정보 (직접 참조로 간소화)
+  // 프로젝트 및 벤더 정보
   projectId: integer("project_id").notNull(),
   contractId: integer("contract_id").notNull(),
   projectCode: varchar("project_code", { length: 50 }),
@@ -1406,7 +1424,16 @@ export const stageDocumentsView = pgView("stage_documents_view", {
   vendorName: varchar("vendor_name", { length: 255 }),
   vendorCode: varchar("vendor_code", { length: 50 }),
 
-  // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지)
+  // 동기화 상태 필드 추가
+  buyerSystemStatus: varchar("buyer_system_status", { length: 50 }),
+  buyerSystemComment: text("buyer_system_comment"),
+  lastSyncedAt: timestamp("last_synced_at", { withTimezone: true }),
+  syncStatus: varchar("sync_status", { length: 20 }),
+  syncError: text("sync_error"),
+  syncVersion: integer("sync_version"),
+  lastModifiedBy: varchar("last_modified_by", { length: 100 }),
+
+  // 현재 스테이지 정보
   currentStageId: integer("current_stage_id"),
   currentStageName: varchar("current_stage_name", { length: 100 }),
   currentStageStatus: varchar("current_stage_status", { length: 50 }),
@@ -1426,7 +1453,7 @@ export const stageDocumentsView = pgView("stage_documents_view", {
   completedStages: integer("completed_stages"),
   progressPercentage: integer("progress_percentage"),
 
-  // 전체 스테이지 목록 (리비전 및 첨부파일 제외)
+  // 전체 스테이지 목록
   allStages: jsonb("all_stages").$type<Array<{
     id: number;
     stageName: string;
@@ -1491,7 +1518,6 @@ export const stageDocumentsView = pgView("stage_documents_view", {
     WHERE stage_status NOT IN ('COMPLETED', 'APPROVED')
     ORDER BY document_id, stage_order ASC, priority DESC
   ),
-  -- 문서별 스테이지 집계 (리비전 제외)
   stage_aggregation AS (
     SELECT 
       ist.document_id,
@@ -1522,7 +1548,7 @@ export const stageDocumentsView = pgView("stage_documents_view", {
     sd.status,
     sd.issued_date,
     
-    -- 프로젝트 및 벤더 정보 (직접 참조로 간소화)
+    -- 프로젝트 및 벤더 정보
     sd.project_id,
     sd.contract_id,
     p.code as project_code,
@@ -1530,6 +1556,15 @@ export const stageDocumentsView = pgView("stage_documents_view", {
     v.vendor_name,
     v.vendor_code,
     
+    -- 동기화 상태 필드 추가
+    sd.buyer_system_status,
+    sd.buyer_system_comment,
+    sd.last_synced_at,
+    sd.sync_status,
+    sd.sync_error,
+    sd.sync_version,
+    sd.last_modified_by,
+    
     -- 현재 스테이지 정보
     csi.current_stage_id,
     csi.current_stage_name,
@@ -1550,7 +1585,7 @@ export const stageDocumentsView = pgView("stage_documents_view", {
     ds.completed_stages,
     ds.progress_percentage,
     
-    -- 전체 스테이지 (리비전 제외)
+    -- 전체 스테이지
     COALESCE(sa.all_stages, '[]'::json) as all_stages,
     
     -- 메타 정보
@@ -1558,11 +1593,8 @@ export const stageDocumentsView = pgView("stage_documents_view", {
     sd.updated_at
     
   FROM stage_documents sd
-  -- 간소화된 JOIN (vendors는 vendor_id로 직접 조인)
   LEFT JOIN projects p ON sd.project_id = p.id
   LEFT JOIN vendors v ON sd.vendor_id = v.id
-  
-  -- 스테이지 관련 정보 JOIN
   LEFT JOIN document_stats ds ON sd.id = ds.document_id
   LEFT JOIN current_stage_info csi ON sd.id = csi.document_id
   LEFT JOIN stage_aggregation sa ON sd.id = sa.document_id
@@ -1570,7 +1602,6 @@ export const stageDocumentsView = pgView("stage_documents_view", {
   ORDER BY sd.created_at DESC
 `);
 
-
 // 🎯 issue_stages 테이블도 stage_documents를 참조하도록 수정
 export const stageIssueStages = pgTable(
   "stage_issue_stages", // 또는 기존 issue_stages 테이블을 수정
@@ -1624,3 +1655,433 @@ export type StageDocumentInsert = typeof stageDocuments.$inferInsert
 export type StageDocumentsView = typeof stageDocumentsView.$inferSelect
 export type StageIssueStage = typeof stageIssueStages.$inferSelect
 export type StageIssueStageInsert = typeof stageIssueStages.$inferInsert
+
+
+
+// 📄 스테이지 제출 테이블 - 구매자 시스템 동기화 필드 추가
+export const stageSubmissions = pgTable(
+  "stage_submissions",
+  {
+    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+    
+    // 참조 관계
+    stageId: integer("stage_id")
+      .notNull()
+      .references(() => stageIssueStages.id, { onDelete: "cascade" }),
+    documentId: integer("document_id")
+      .notNull()
+      .references(() => stageDocuments.id, { onDelete: "cascade" }),
+    
+    // 리비전 관리
+    revisionNumber: integer("revision_number").notNull().default(1),
+    revisionCode: varchar("revision_code", { length: 50 }).notNull(), // 새 필드: "Rev0", "A", "1" 등
+    revisionType: varchar("revision_type", { length: 50 })
+      .default("INITIAL"), // INITIAL, RESUBMISSION, CORRECTION
+    
+    // 제출 정보
+    submissionStatus: varchar("submission_status", { length: 50 })
+      .notNull()
+      .default("DRAFT"), // DRAFT, SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED, WITHDRAWN
+    
+    // 제출자 정보
+    submittedBy: varchar("submitted_by", { length: 100 }).notNull(),
+    submittedByEmail: varchar("submitted_by_email", { length: 255 }),
+    submittedAt: timestamp("submitted_at", { withTimezone: true }).notNull().defaultNow(),
+    
+    // 검토자 정보
+    reviewedBy: varchar("reviewed_by", { length: 100 }),
+    reviewedByEmail: varchar("reviewed_by_email", { length: 255 }),
+    reviewedAt: timestamp("reviewed_at", { withTimezone: true }),
+    
+    // 제출 내용
+    submissionTitle: varchar("submission_title", { length: 500 }),
+    submissionDescription: text("submission_description"),
+    submissionNotes: text("submission_notes"),
+    
+    // 반려/승인 정보
+    reviewStatus: varchar("review_status", { length: 50 }), // PENDING, APPROVED, REJECTED, CONDITIONAL
+    reviewComments: text("review_comments"),
+    rejectionReason: text("rejection_reason"),
+    
+    // 벤더 정보 (denormalized for quick access)
+    vendorId: integer("vendor_id").notNull(),
+    vendorCode: varchar("vendor_code", { length: 50 }),
+    
+    // 메타데이터
+    totalFiles: integer("total_files").default(0),
+    totalFileSize: bigint("total_file_size", { mode: "number" }).default(0), // bytes
+    
+    // ✨ 구매자 시스템 동기화 정보 (제출 레벨)
+    buyerSystemStatus: varchar("buyer_system_status", { length: 50 }), // PENDING, SYNCING, SYNCED, FAILED, PARTIAL
+    buyerSystemComment: text("buyer_system_comment"),
+    buyerSystemSubmissionId: varchar("buyer_system_submission_id", { length: 255 }), // 구매자 시스템의 제출 ID
+    lastSyncedAt: timestamp("last_synced_at", { withTimezone: true }),
+    syncStatus: varchar("sync_status", { length: 20 }).default("pending"), // pending, syncing, synced, failed, partial
+    syncError: text("sync_error"),
+    syncVersion: integer("sync_version").default(0),
+    lastModifiedBy: varchar("last_modified_by", { length: 100 }), // EVCP, BUYER_SYSTEM
+    syncRetryCount: integer("sync_retry_count").default(0),
+    nextRetryAt: timestamp("next_retry_at", { withTimezone: true }),
+    
+    // 동기화 통계
+    totalFilesToSync: integer("total_files_to_sync").default(0),
+    syncedFilesCount: integer("synced_files_count").default(0),
+    failedFilesCount: integer("failed_files_count").default(0),
+    
+    // 타임스탬프
+    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+  },
+  (table) => {
+    return {
+      // 한 스테이지에서 리비전 번호는 유니크
+      uniqueStageRevision: uniqueIndex("unique_stage_revision").on(
+        table.stageId,
+        table.revisionNumber
+      ),
+      
+      // 검색용 인덱스
+      documentIdIndex: index("submission_document_id_idx").on(table.documentId),
+      vendorIdIndex: index("submission_vendor_id_idx").on(table.vendorId),
+      statusIndex: index("submission_status_idx").on(table.submissionStatus),
+      submittedAtIndex: index("submission_submitted_at_idx").on(table.submittedAt),
+      
+      // ✨ 동기화 관련 인덱스
+      syncStatusIndex: index("submission_sync_status_idx").on(table.syncStatus),
+      buyerSystemStatusIndex: index("submission_buyer_status_idx").on(table.buyerSystemStatus),
+      nextRetryIndex: index("submission_next_retry_idx").on(table.nextRetryAt),
+    }
+  }
+);
+
+// 📎 제출 첨부파일 테이블 - 구매자 시스템 동기화 필드 추가
+export const stageSubmissionAttachments = pgTable(
+  "stage_submission_attachments",
+  {
+    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+    
+    // 참조 관계
+    submissionId: integer("submission_id")
+      .notNull()
+      .references(() => stageSubmissions.id, { onDelete: "cascade" }),
+    
+    // 파일 정보
+    fileName: varchar("file_name", { length: 255 }).notNull(),
+    originalFileName: varchar("original_file_name", { length: 255 }).notNull(),
+    fileType: varchar("file_type", { length: 100 }), // application/pdf, image/jpeg, etc.
+    fileExtension: varchar("file_extension", { length: 20 }),
+    fileSize: bigint("file_size", { mode: "number" }).notNull(), // bytes
+    
+    // 저장 정보
+    storageType: varchar("storage_type", { length: 50 })
+      .default("S3"), // S3, LOCAL, AZURE, GCS
+    storagePath: varchar("storage_path", { length: 1000 }).notNull(),
+    storageUrl: varchar("storage_url", { length: 2000 }),
+    bucketName: varchar("bucket_name", { length: 255 }),
+    
+    // 파일 메타데이터
+    mimeType: varchar("mime_type", { length: 100 }),
+    checksum: varchar("checksum", { length: 255 }), // MD5 or SHA256
+    
+    // 문서 분류 (옵션)
+    documentType: varchar("document_type", { length: 100 }), // DRAWING, SPEC, REPORT, CERTIFICATE, etc.
+    documentCategory: varchar("document_category", { length: 100 }),
+    
+    // 버전 관리
+    fileVersion: varchar("file_version", { length: 50 }),
+    isLatest: boolean("is_latest").default(true),
+    
+    // 업로드 정보
+    uploadedBy: varchar("uploaded_by", { length: 100 }).notNull(),
+    uploadedAt: timestamp("uploaded_at", { withTimezone: true }).defaultNow().notNull(),
+    
+    // 상태
+    status: varchar("status", { length: 50 })
+      .default("ACTIVE"), // ACTIVE, DELETED, ARCHIVED
+    
+    // ✨ 구매자 시스템 동기화 정보 (파일 레벨)
+    buyerSystemFileId: varchar("buyer_system_file_id", { length: 255 }), // 구매자 시스템의 파일 ID
+    buyerSystemStatus: varchar("buyer_system_status", { length: 50 }), // PENDING, UPLOADING, UPLOADED, FAILED
+    buyerSystemComment: text("buyer_system_comment"),
+    buyerSystemUrl: varchar("buyer_system_url", { length: 2000 }), // 구매자 시스템에서의 파일 URL
+    
+    // 동기화 상태
+    syncStatus: varchar("sync_status", { length: 20 }).default("pending"), // pending, syncing, synced, failed
+    syncStartedAt: timestamp("sync_started_at", { withTimezone: true }),
+    syncCompletedAt: timestamp("sync_completed_at", { withTimezone: true }),
+    syncError: text("sync_error"),
+    syncRetryCount: integer("sync_retry_count").default(0),
+    lastSyncAttempt: timestamp("last_sync_attempt", { withTimezone: true }),
+    
+    // 전송 정보
+    transferMethod: varchar("transfer_method", { length: 50 }), // API, FTP, SFTP, EMAIL
+    transferredBytes: bigint("transferred_bytes", { mode: "number" }), // 실제 전송된 바이트
+    transferProgress: integer("transfer_progress"), // 0-100 퍼센트
+    
+    // 동기화 메타데이터
+    syncVersion: integer("sync_version").default(0),
+    lastModifiedBy: varchar("last_modified_by", { length: 100 }), // EVCP, BUYER_SYSTEM
+    
+    // 보안/접근 제어
+    isPublic: boolean("is_public").default(false),
+    expiresAt: timestamp("expires_at", { withTimezone: true }),
+    
+    // 추가 메타데이터
+    metadata: jsonb("metadata").$type<{
+      pageCount?: number;
+      dimensions?: { width: number; height: number };
+      duration?: number; // for videos
+      buyerSystemMetadata?: any; // 구매자 시스템에서 받은 추가 정보
+      [key: string]: any;
+    }>(),
+    
+    // 타임스탬프
+    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+  },
+  (table) => {
+    return {
+      // 검색용 인덱스
+      submissionIdIndex: index("attachment_submission_id_idx").on(table.submissionId),
+      fileTypeIndex: index("attachment_file_type_idx").on(table.fileType),
+      statusIndex: index("attachment_status_idx").on(table.status),
+      uploadedAtIndex: index("attachment_uploaded_at_idx").on(table.uploadedAt),
+      
+      // ✨ 동기화 관련 인덱스
+      syncStatusIndex: index("attachment_sync_status_idx").on(table.syncStatus),
+      buyerSystemFileIdIndex: index("attachment_buyer_file_id_idx").on(table.buyerSystemFileId),
+      buyerSystemStatusIndex: index("attachment_buyer_status_idx").on(table.buyerSystemStatus),
+    }
+  }
+);
+
+
+
+// 🔍 제출 현황 뷰 수정 - 동기화 상태 포함
+export const stageSubmissionView = pgView("stage_submission_view", {
+  // 문서 정보
+  documentId: integer("document_id").notNull(),
+  docNumber: varchar("doc_number", { length: 100 }).notNull(),
+  vendorDocNumber: varchar("vendor_doc_number", { length: 100 }),
+  documentTitle: varchar("document_title", { length: 255 }).notNull(),
+  
+  // 프로젝트/벤더 정보
+  projectId: integer("project_id").notNull(),
+  projectCode: varchar("project_code", { length: 50 }),
+  vendorId: integer("vendor_id").notNull(),
+  vendorName: varchar("vendor_name", { length: 255 }),
+  vendorCode: varchar("vendor_code", { length: 50 }),
+  
+  // 스테이지 정보
+  stageId: integer("stage_id"),
+  stageName: varchar("stage_name", { length: 100 }),
+  stageOrder: integer("stage_order"),
+  stageStatus: varchar("stage_status", { length: 50 }),
+  stagePlanDate: date("stage_plan_date"),
+  
+  // 최신 제출 정보
+  latestSubmissionId: integer("latest_submission_id"),
+  latestRevisionNumber: integer("latest_revision_number"),
+  latestRevisionCode: varchar("latest_revision_code", { length: 50 }), // ⭐ 추가
+  latestSubmissionStatus: varchar("latest_submission_status", { length: 50 }),
+  latestSubmittedAt: timestamp("latest_submitted_at", { withTimezone: true }),
+  latestSubmittedBy: varchar("latest_submitted_by", { length: 100 }),
+  latestReviewStatus: varchar("latest_review_status", { length: 50 }),
+  
+  // ✨ 동기화 상태
+  latestSyncStatus: varchar("latest_sync_status", { length: 20 }),
+  latestBuyerSystemStatus: varchar("latest_buyer_system_status", { length: 50 }),
+  lastSyncedAt: timestamp("last_synced_at", { withTimezone: true }),
+  totalFilesToSync: integer("total_files_to_sync"),
+  syncedFilesCount: integer("synced_files_count"),
+  syncProgress: integer("sync_progress"), // 0-100 퍼센트
+  
+  // 제출 통계
+  totalSubmissions: integer("total_submissions"),
+  approvedSubmissions: integer("approved_submissions"),
+  rejectedSubmissions: integer("rejected_submissions"),
+  pendingSubmissions: integer("pending_submissions"),
+  
+  // 파일 정보
+  totalFiles: integer("total_files"),
+  totalFileSize: bigint("total_file_size", { mode: "number" }),
+  
+  // 제출 필요 여부
+  requiresSubmission: boolean("requires_submission"),
+  requiresSync: boolean("requires_sync"),
+  isOverdue: boolean("is_overdue"),
+  daysUntilDue: integer("days_until_due"),
+  
+  // 제출 이력 (revisionCode 추가)
+  submissionHistory: jsonb("submission_history").$type<Array<{
+    submissionId: number;
+    revisionNumber: number;
+    revisionCode: string | null; // ⭐ 추가
+    status: string;
+    submittedAt: string;
+    submittedBy: string;
+    reviewStatus: string | null;
+    syncStatus: string | null;
+    fileCount: number;
+  }>>(),
+  
+}).as(sql`
+  WITH eligible_documents AS (
+    -- buyerSystemStatus가 '승인(DC)'인 문서만 선택
+    SELECT 
+      sd.id as document_id,
+      sd.doc_number,
+      sd.vendor_doc_number,
+      sd.title as document_title,
+      sd.project_id,
+      sd.vendor_id,
+      p.code as project_code,
+      v.vendor_name,
+      v.vendor_code
+    FROM stage_documents sd
+    LEFT JOIN projects p ON sd.project_id = p.id
+    LEFT JOIN vendors v ON sd.vendor_id = v.id
+    WHERE sd.buyer_system_status = '승인(DC)'
+      AND sd.status = 'ACTIVE'
+  ),
+  
+  submission_stats AS (
+    SELECT 
+      ss.stage_id,
+      COUNT(*) as total_submissions,
+      COUNT(CASE WHEN ss.review_status = 'APPROVED' THEN 1 END) as approved_submissions,
+      COUNT(CASE WHEN ss.review_status = 'REJECTED' THEN 1 END) as rejected_submissions,
+      COUNT(CASE WHEN ss.review_status = 'PENDING' OR ss.review_status IS NULL THEN 1 END) as pending_submissions,
+      SUM(ss.total_files) as total_files,
+      SUM(ss.total_file_size) as total_file_size,
+      MAX(ss.revision_number) as latest_revision_number
+    FROM stage_submissions ss
+    GROUP BY ss.stage_id
+  ),
+  
+  latest_submission AS (
+    SELECT DISTINCT ON (stage_id)
+      stage_id,
+      id as submission_id,
+      revision_number,
+      revision_code, -- ⭐ 추가
+      submission_status,
+      submitted_at,
+      submitted_by,
+      review_status,
+      sync_status,
+      buyer_system_status,
+      last_synced_at,
+      total_files_to_sync,
+      synced_files_count,
+      CASE 
+        WHEN total_files_to_sync > 0 
+        THEN ROUND((synced_files_count * 100.0) / total_files_to_sync)
+        ELSE 0 
+      END as sync_progress
+    FROM stage_submissions
+    ORDER BY stage_id, revision_number DESC
+  ),
+  
+  submission_history_agg AS (
+    SELECT 
+      ss.stage_id,
+      json_agg(
+        json_build_object(
+          'submissionId', ss.id,
+          'revisionNumber', ss.revision_number,
+          'revisionCode', ss.revision_code, -- ⭐ 추가
+          'status', ss.submission_status,
+          'submittedAt', ss.submitted_at,
+          'submittedBy', ss.submitted_by,
+          'reviewStatus', ss.review_status,
+          'syncStatus', ss.sync_status,
+          'fileCount', ss.total_files
+        ) ORDER BY ss.revision_number DESC
+      ) as submission_history
+    FROM stage_submissions ss
+    GROUP BY ss.stage_id
+  )
+  
+  SELECT 
+    ed.document_id,
+    ed.doc_number,
+    ed.vendor_doc_number,
+    ed.document_title,
+    ed.project_id,
+    ed.project_code,
+    ed.vendor_id,
+    ed.vendor_name,
+    ed.vendor_code,
+    
+    ist.id as stage_id,
+    ist.stage_name,
+    ist.stage_order,
+    ist.stage_status,
+    ist.plan_date as stage_plan_date,
+    
+    ls.submission_id as latest_submission_id,
+    ls.revision_number as latest_revision_number,
+    ls.revision_code as latest_revision_code, -- ⭐ 추가
+    ls.submission_status as latest_submission_status,
+    ls.submitted_at as latest_submitted_at,
+    ls.submitted_by as latest_submitted_by,
+    ls.review_status as latest_review_status,
+    
+    -- 동기화 상태
+    ls.sync_status as latest_sync_status,
+    ls.buyer_system_status as latest_buyer_system_status,
+    ls.last_synced_at,
+    ls.total_files_to_sync,
+    ls.synced_files_count,
+    ls.sync_progress,
+    
+    COALESCE(ss.total_submissions, 0) as total_submissions,
+    COALESCE(ss.approved_submissions, 0) as approved_submissions,
+    COALESCE(ss.rejected_submissions, 0) as rejected_submissions,
+    COALESCE(ss.pending_submissions, 0) as pending_submissions,
+    COALESCE(ss.total_files, 0) as total_files,
+    COALESCE(ss.total_file_size, 0) as total_file_size,
+    
+    -- 제출이 필요한지 판단
+    CASE 
+      WHEN ist.stage_status IN ('PLANNED', 'IN_PROGRESS', 'REJECTED') 
+        AND (ls.review_status IS NULL OR ls.review_status != 'APPROVED')
+      THEN true 
+      ELSE false 
+    END as requires_submission,
+    
+    -- 동기화가 필요한지 판단
+    CASE 
+      WHEN ls.sync_status IN ('pending', 'failed') 
+        OR ls.synced_files_count < ls.total_files_to_sync
+      THEN true 
+      ELSE false 
+    END as requires_sync,
+    
+    -- 기한 관련
+    CASE 
+      WHEN ist.plan_date < CURRENT_DATE 
+        AND ist.stage_status NOT IN ('COMPLETED', 'APPROVED')
+      THEN true 
+      ELSE false 
+    END as is_overdue,
+    
+    CASE 
+      WHEN ist.plan_date IS NOT NULL 
+      THEN ist.plan_date - CURRENT_DATE
+      ELSE NULL 
+    END as days_until_due,
+    
+    COALESCE(sha.submission_history, '[]'::json) as submission_history
+    
+  FROM eligible_documents ed
+  INNER JOIN stage_issue_stages ist ON ed.document_id = ist.document_id
+  LEFT JOIN submission_stats ss ON ist.id = ss.stage_id
+  LEFT JOIN latest_submission ls ON ist.id = ls.stage_id
+  LEFT JOIN submission_history_agg sha ON ist.id = sha.stage_id
+  
+  ORDER BY ed.document_id, ist.stage_order
+`);
+
+export type StageSubmissionView = typeof stageSubmissionView.$inferSelect
\ No newline at end of file