(김준회) dolce: 이유란프로 B4 스테이지(일정) 관련 요구사항 반영

1 files changed, 37 insertions, 0 deletions

diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index 304fe5ae..bfa43849 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -873,6 +873,12 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
   secondStagePlanDate: date("second_stage_plan_date"),
   secondStageActualDate: date("second_stage_actual_date"),
 
+  // 세 번째 스테이지 날짜 정보 (B4 문서의 SHI → GTT)
+  thirdStageId: integer("third_stage_id"),
+  thirdStageName: varchar("third_stage_name", { length: 100 }),
+  thirdStagePlanDate: date("third_stage_plan_date"),
+  thirdStageActualDate: date("third_stage_actual_date"),
+
   // 전체 스테이지 목록
   allStages: json("all_stages").$type<Array<{
     id: number;
@@ -1063,6 +1069,30 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
     WHERE rn = 1
   ),
   
+  -- 세 번째 스테이지 정보 (B4 문서의 SHI → GTT)
+  third_stage_info AS (
+    SELECT 
+      document_id,
+      third_stage_id,
+      third_stage_name,
+      third_stage_plan_date,
+      third_stage_actual_date
+    FROM (
+      SELECT 
+        ist.document_id,
+        ist.id as third_stage_id,
+        ist.stage_name as third_stage_name,
+        ist.plan_date as third_stage_plan_date,
+        ist.actual_date as third_stage_actual_date,
+        ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn
+      FROM issue_stages ist
+      JOIN documents d ON ist.document_id = d.id
+      WHERE 
+        d.drawing_kind = 'B4' AND ist.stage_name = 'SHI → GTT'
+    ) ranked
+    WHERE rn = 1
+  ),
+  
   -- 첨부파일 수 집계
   attachment_counts AS (
     SELECT 
@@ -1133,6 +1163,12 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
     ssi.second_stage_plan_date,
     ssi.second_stage_actual_date,
     
+    -- 세 번째 스테이지 정보
+    tsi.third_stage_id,
+    tsi.third_stage_name,
+    tsi.third_stage_plan_date,
+    tsi.third_stage_actual_date,
+    
     -- 전체 스테이지 (리비전 및 첨부파일 포함)
     COALESCE(sa.all_stages, '[]'::json) as all_stages,
     
@@ -1150,6 +1186,7 @@ export const simplifiedDocumentsView = pgView("simplified_documents_view", {
   -- 스테이지 정보 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 third_stage_info tsi ON d.id = tsi.document_id
   LEFT JOIN stage_aggregation sa ON d.id = sa.document_id
   LEFT JOIN attachment_counts ac ON d.id = ac.document_id