(대표님) 20250613 16시 08분 b-rfq, document 등

2 files changed, 370 insertions, 35 deletions

diff --git a/db/schema/bRfq.ts b/db/schema/bRfq.ts
index 5e211448..115d2c6e 100644
--- a/db/schema/bRfq.ts
+++ b/db/schema/bRfq.ts
@@ -168,52 +168,114 @@ export const bRfqsAttachments = pgTable(
     "b_rfq_attachments",
     {
       id: serial("id").primaryKey(),
-      attachmentType: varchar("attachment_type", { length: 50 }).notNull(), // '구매', '설계'
+      attachmentType: varchar("attachment_type", { length: 50 }).notNull(),
       serialNo: varchar("serial_no", { length: 50 }).notNull(),
       rfqId: integer("rfq_id")
-      .notNull()
-      .references(() => bRfqs.id),
+        .notNull()
+        .references(() => bRfqs.id),
+      
+      // 현재 리비전 정보 (빠른 접근용)
+      currentRevision: varchar("current_revision", { length: 10 }).notNull().default("Rev.0"),
+      latestRevisionId: integer("latest_revision_id"), // self-reference to bRfqAttachmentRevisions
+      
+      // 메타 정보
+      description: varchar("description", { length: 500 }),
+      createdBy: integer("created_by")
+        .references(() => users.id, { onDelete: "set null" })
+        .notNull(),
+      createdAt: timestamp("created_at").defaultNow().notNull(),
+      updatedAt: timestamp("updated_at").defaultNow().notNull(),
+    }
+  )
+  
+  // 리비전 테이블 (모든 파일 버전 관리)
+  export const bRfqAttachmentRevisions = pgTable(
+    "b_rfq_attachment_revisions",
+    {
+      id: serial("id").primaryKey(),
+      attachmentId: integer("attachment_id")
+        .notNull()
+        .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),
+        
+      // 리비전 정보
+      revisionNo: varchar("revision_no", { length: 10 }).notNull(), // "Rev.0", "Rev.1", "Rev.2"
+      revisionComment: text("revision_comment"),
+      isLatest: boolean("is_latest").notNull().default(true),
+      
+      // 파일 정보
       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(),
-    },
-  );
-// 1. 첨부파일 리비전 관리 테이블
-export const bRfqAttachmentRevisions = pgTable(
-    "b_rfq_attachment_revisions",
-    {
-        id: serial("id").primaryKey(),
-        attachmentId: integer("attachment_id")
-            .notNull()
-            .references(() => bRfqsAttachments.id, { onDelete: "cascade" }),
-        
-        revisionNo: varchar("revision_no", { length: 10 }).notNull(), // "Rev.0", "Rev.1", "Rev.2"
-        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 }),
-        
-        isLatest: boolean("is_latest").notNull().default(true),
-        revisionComment: text("revision_comment"),
-        
-        createdBy: integer("created_by")
-            .references(() => users.id, { onDelete: "set null" })
-            .notNull(),
-        createdAt: timestamp("created_at").defaultNow().notNull(),
-    }, (t) => ({
-        latestRevisionIdx: uniqueIndex('latest_revision_idx').on(
-          t.attachmentId.asc(),
-          t.isLatest.asc(),
-        ),
-      }));
+    }, 
+    (t) => ({
+      // 첨부파일당 하나의 최신 리비전만 허용
+      latestRevisionIdx: uniqueIndex('latest_revision_idx')
+        .on(t.attachmentId, t.isLatest)
+        .where(eq(t.isLatest, true)),
+      
+      // 첨부파일 + 리비전 번호 유니크
+      attachmentRevisionIdx: uniqueIndex('attachment_revision_idx')
+        .on(t.attachmentId, t.revisionNo),
+    })
+  )
+  
+  // 첨부파일 + 최신 리비전 뷰
+  export const attachmentsWithLatestRevisionView = pgView("attachments_with_latest_revision", {
+    // 메인 첨부파일 정보
+    attachmentId: integer("attachment_id"),
+    attachmentType: varchar("attachment_type", { length: 50 }),
+    serialNo: varchar("serial_no", { length: 50 }),
+    rfqId: integer("rfq_id"),
+    description: varchar("description", { length: 500 }),
+    currentRevision: varchar("current_revision", { length: 10 }),
+    
+    // 최신 리비전 파일 정보
+    revisionId: integer("revision_id"),
+    fileName: varchar("file_name", { length: 255 }),
+    originalFileName: varchar("original_file_name", { length: 255 }),
+    filePath: varchar("file_path", { length: 512 }),
+    fileSize: integer("file_size"),
+    fileType: varchar("file_type", { length: 100 }),
+    revisionComment: text("revision_comment"),
+    
+    // 생성/수정 정보
+    createdBy: integer("created_by"),
+    createdByName: varchar("created_by_name", { length: 255 }),
+    createdAt: timestamp("created_at"),
+    updatedAt: timestamp("updated_at"),
+  }).as(sql`
+    SELECT 
+      a.id as attachment_id,
+      a.attachment_type,
+      a.serial_no,
+      a.rfq_id,
+      a.description,
+      a.current_revision,
+      
+      r.id as revision_id,
+      r.file_name,
+      r.original_file_name,
+      r.file_path,
+      r.file_size,
+      r.file_type,
+      r.revision_comment,
+      
+      a.created_by,
+      u.name as created_by_name,
+      a.created_at,
+      a.updated_at
+    FROM b_rfq_attachments a
+    LEFT JOIN b_rfq_attachment_revisions r ON a.latest_revision_id = r.id
+    LEFT JOIN users u ON a.created_by = u.id
+  `)
 
 // 2. 벤더별 첨부파일 응답 현황 관리
 export const vendorAttachmentResponses = pgTable(
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index a3138fd5..af48d74b 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -467,6 +467,7 @@ export const enhancedDocumentsView = pgView("enhanced_documents_view", {
     d.degree_gbn,
     d.dept_gbn,
     d.s_gbn,
+    d.j_gbn,
 
 
     
@@ -758,3 +759,275 @@ export type ChangeLogInsert = typeof changeLogs.$inferInsert
 export type SyncBatch = typeof syncBatches.$inferSelect
 export type SyncBatchInsert = typeof syncBatches.$inferInsert
 export type SyncStatusView = typeof syncStatusView.$inferSelect
+
+
+export const simplifiedDocumentsView = pgView("simplified_documents_view", {
+  // 기본 문서 정보
+  documentId: integer("document_id").notNull(),
+  docNumber: varchar("doc_number", { length: 100 }).notNull(),
+  drawingKind: varchar("drawing_kind", { length: 50 }),
+  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 }),
+
+  // B4 전용 필드들
+  cGbn: varchar("c_gbn", { length: 50 }),
+  dGbn: varchar("d_gbn", { length: 50 }),
+  degreeGbn: varchar("degree_gbn", { length: 50 }),
+  deptGbn: varchar("dept_gbn", { length: 50 }),
+  jGbn: varchar("j_gbn", { length: 50 }),
+  sGbn: varchar("s_gbn", { length: 50 }),
+  
+  // 첫 번째 스테이지 날짜 정보 (drawingKind에 따라 다름)
+  firstStageId: integer("first_stage_id"),
+  firstStageName: varchar("first_stage_name", { length: 100 }),
+  firstStagePlanDate: date("first_stage_plan_date"),
+  firstStageActualDate: date("first_stage_actual_date"),
+  
+  // 두 번째 스테이지 날짜 정보 (drawingKind에 따라 다름)
+  secondStageId: integer("second_stage_id"),
+  secondStageName: varchar("second_stage_name", { length: 100 }),
+  secondStagePlanDate: date("second_stage_plan_date"),
+  secondStageActualDate: date("second_stage_actual_date"),
+  
+  // 전체 스테이지 목록 (기존과 동일)
+  allStages: jsonb("all_stages").$type<Array<{
+    id: number;
+    stageName: string;
+    stageStatus: string;
+    stageOrder: number;
+    planDate: string | null;
+    actualDate: string | null;
+    assigneeName: string | null;
+    priority: string;
+    revisions: Array<{
+      id: number;
+      issueStageId: number;
+      revision: string;
+      uploaderType: string;
+      uploaderId: number | null;
+      uploaderName: string | null;
+      comment: string | null;
+      usage: string | null;
+      revisionStatus: string;
+      submittedDate: string | null;
+      approvedDate: string | null;
+      uploadedAt: string | null;
+      reviewStartDate: string | null;
+      rejectedDate: string | null;
+      reviewerId: number | null;
+      reviewerName: string | null;
+      reviewComments: string | null;
+      createdAt: Date;
+      updatedAt: Date;
+      attachments: Array<{
+        id: number;
+        revisionId: number;
+        fileName: string;
+        filePath: string;
+        fileSize: number | null;
+        fileType: string | null;
+        createdAt: Date;
+        updatedAt: Date;
+      }>;
+    }>;
+  }>>(),
+  
+  // 메타 정보
+  attachmentCount: integer("attachment_count"),
+  createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
+  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
+}).as(sql`
+  WITH 
+  -- 리비전별 첨부파일 집계
+  revision_attachments AS (
+    SELECT 
+      r.id as revision_id,
+      COALESCE(
+        json_agg(
+          json_build_object(
+            'id', da.id,
+            'revisionId', da.revision_id,
+            'fileName', da.file_name,
+            'filePath', da.file_path,
+            'fileSize', da.file_size,
+            'fileType', da.file_type,
+            'createdAt', da.created_at,
+            'updatedAt', da.updated_at
+          ) ORDER BY da.created_at
+        ) FILTER (WHERE da.id IS NOT NULL),
+        '[]'::json
+      ) as attachments
+    FROM revisions r
+    LEFT JOIN document_attachments da ON r.id = da.revision_id
+    GROUP BY r.id
+  ),
+  -- 스테이지별 리비전 집계 (첨부파일 포함)
+  stage_revisions AS (
+    SELECT 
+      ist.id as stage_id,
+      COALESCE(
+        json_agg(
+          json_build_object(
+            'id', r.id,
+            'issueStageId', r.issue_stage_id,
+            'revision', r.revision,
+            'uploaderType', r.uploader_type,
+            'uploaderId', r.uploader_id,
+            'uploaderName', r.uploader_name,
+            'comment', r.comment,
+            'usage', r.usage,
+            '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,
+            'reviewerId', r.reviewer_id,
+            'reviewerName', r.reviewer_name,
+            'reviewComments', r.review_comments,
+            'createdAt', r.created_at,
+            'updatedAt', r.updated_at,
+            'attachments', ra.attachments
+          ) ORDER BY r.created_at
+        ) FILTER (WHERE r.id IS NOT NULL),
+        '[]'::json
+      ) as revisions
+    FROM issue_stages ist
+    LEFT JOIN revisions r ON ist.id = r.issue_stage_id
+    LEFT JOIN revision_attachments ra ON r.id = ra.revision_id
+    GROUP BY ist.id
+  ),
+  -- 문서별 스테이지 집계 (리비전 포함)
+  stage_aggregation AS (
+    SELECT 
+      ist.document_id,
+      json_agg(
+        json_build_object(
+          'id', ist.id,
+          'stageName', ist.stage_name,
+          'stageStatus', ist.stage_status,
+          'stageOrder', ist.stage_order,
+          'planDate', ist.plan_date,
+          'actualDate', ist.actual_date,
+          'assigneeName', ist.assignee_name,
+          'priority', ist.priority,
+          'revisions', sr.revisions
+        ) ORDER BY ist.stage_order
+      ) as all_stages
+    FROM issue_stages ist
+    LEFT JOIN stage_revisions sr ON ist.id = sr.stage_id
+    GROUP BY ist.document_id
+  ),
+  -- 첫 번째 스테이지 정보 (drawingKind에 따라 다른 조건)
+  first_stage_info AS (
+    SELECT DISTINCT ON (ist.document_id)
+      ist.document_id,
+      ist.id as first_stage_id,
+      ist.stage_name as first_stage_name,
+      ist.plan_date as first_stage_plan_date,
+      ist.actual_date as first_stage_actual_date
+    FROM issue_stages ist
+    JOIN documents d ON ist.document_id = d.id
+    WHERE 
+      (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%pre%') OR
+      (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%approval%') OR
+      (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%first%')
+    ORDER BY ist.document_id, ist.stage_order ASC
+  ),
+  -- 두 번째 스테이지 정보 (drawingKind에 따라 다른 조건)
+  second_stage_info AS (
+    SELECT DISTINCT ON (ist.document_id)
+      ist.document_id,
+      ist.id as second_stage_id,
+      ist.stage_name as second_stage_name,
+      ist.plan_date as second_stage_plan_date,
+      ist.actual_date as second_stage_actual_date
+    FROM issue_stages ist
+    JOIN documents d ON ist.document_id = d.id
+    WHERE 
+      (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%work%') OR
+      (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%work%') OR
+      (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%second%')
+    ORDER BY ist.document_id, ist.stage_order ASC
+  ),
+  -- 첨부파일 수 집계
+  attachment_counts AS (
+    SELECT 
+      ist.document_id,
+      COUNT(da.id) as attachment_count
+    FROM issue_stages ist
+    LEFT JOIN revisions r ON ist.id = r.issue_stage_id
+    LEFT JOIN document_attachments da ON r.id = da.revision_id
+    GROUP BY ist.document_id
+  )
+  
+  SELECT 
+    d.id as document_id,
+    d.doc_number,
+    d.drawing_kind,
+    d.vendor_doc_number,
+    d.title,
+    d.pic,
+    d.status,
+    d.issued_date,
+    d.contract_id,
+    
+    -- B4 전용 필드들
+    d.c_gbn,
+    d.d_gbn,
+    d.degree_gbn,
+    d.dept_gbn,
+    d.s_gbn,
+    d.j_gbn,
+    
+    -- 프로젝트 및 벤더 정보
+    p.code as project_code,
+    v.vendor_name as vendor_name,
+    v.vendor_code as vendor_code,
+    
+    -- 첫 번째 스테이지 정보
+    fsi.first_stage_id,
+    fsi.first_stage_name,
+    fsi.first_stage_plan_date,
+    fsi.first_stage_actual_date,
+    
+    -- 두 번째 스테이지 정보
+    ssi.second_stage_id,
+    ssi.second_stage_name,
+    ssi.second_stage_plan_date,
+    ssi.second_stage_actual_date,
+    
+    -- 전체 스테이지 (리비전 및 첨부파일 포함)
+    COALESCE(sa.all_stages, '[]'::json) as all_stages,
+    
+    -- 기타
+    COALESCE(ac.attachment_count, 0) as attachment_count,
+    d.created_at,
+    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
+  
+  -- 스테이지 정보 JOIN
+  LEFT JOIN first_stage_info fsi ON d.id = fsi.document_id
+  LEFT JOIN second_stage_info ssi ON d.id = ssi.document_id
+  LEFT JOIN stage_aggregation sa ON d.id = sa.document_id
+  LEFT JOIN attachment_counts ac ON d.id = ac.document_id
+  
+  ORDER BY d.created_at DESC
+`);
+
+// 타입 추출
+export type SimplifiedDocumentsView = typeof simplifiedDocumentsView.$inferSelect