From 7a1524ba54f43d0f2a19e4bca2c6a2e0b01c5ef1 Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Tue, 17 Jun 2025 09:02:32 +0000
Subject: (대표님) 20250617 18시 작업사항
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations/0143_pink_morlocks.sql | 88 ++++++++++++++++++++++++++++++++++++
 1 file changed, 88 insertions(+)
 create mode 100644 db/migrations/0143_pink_morlocks.sql

(limited to 'db/migrations/0143_pink_morlocks.sql')

diff --git a/db/migrations/0143_pink_morlocks.sql b/db/migrations/0143_pink_morlocks.sql
new file mode 100644
index 00000000..44597407
--- /dev/null
+++ b/db/migrations/0143_pink_morlocks.sql
@@ -0,0 +1,88 @@
+DROP VIEW "public"."vendor_response_attachments_enhanced";--> statement-breakpoint
+CREATE VIEW "public"."vendor_response_attachments_enhanced" AS (
+    SELECT 
+        vra.id as response_attachment_id,
+        vra.vendor_response_id,
+        vra.file_name,
+        vra.original_file_name,
+        vra.file_path,
+        vra.file_size,
+        vra.file_type,
+        vra.description,
+        vra.uploaded_at,
+        
+        -- 응답 기본 정보
+        var.attachment_id,
+        var.vendor_id,
+        var.rfq_type,
+        var.rfq_record_id,
+        var.response_status,
+        var.current_revision,
+        var.responded_revision,
+        
+        -- 코멘트 (새로 추가된 필드 포함)
+        var.response_comment,
+        var.vendor_comment,
+        var.revision_request_comment,
+        
+        -- 날짜 (새로 추가된 필드 포함)
+        var.requested_at,
+        var.responded_at,
+        var.revision_requested_at,
+        
+        -- 첨부파일 정보
+        ba.attachment_type,
+        ba.serial_no,
+        ba.rfq_id,
+        
+        -- 벤더 정보
+        v.vendor_code,
+        v.vendor_name,
+        v.country as vendor_country,
+        
+        -- 발주처 현재 리비전 정보
+        latest_rev.id as latest_client_revision_id,
+        latest_rev.revision_no as latest_client_revision_no,
+        latest_rev.original_file_name as latest_client_file_name,
+        
+        -- 리비전 비교
+        CASE 
+            WHEN var.responded_revision = ba.current_revision THEN true 
+            ELSE false 
+        END as is_version_matched,
+        
+        -- 버전 차이 계산 (Rev.0, Rev.1 형태 가정)
+        CASE 
+            WHEN var.responded_revision IS NULL THEN NULL
+            WHEN ba.current_revision IS NULL THEN NULL
+            ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - 
+                 CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER)
+        END as version_lag,
+        
+        CASE 
+            WHEN var.response_status = 'RESPONDED' 
+                 AND var.responded_revision != ba.current_revision THEN true 
+            ELSE false 
+        END as needs_update,
+        
+        -- 파일 순서
+        ROW_NUMBER() OVER (
+            PARTITION BY var.id 
+            ORDER BY vra.uploaded_at DESC
+        ) as file_sequence,
+        
+        -- 최신 응답 파일 여부
+        CASE 
+            WHEN ROW_NUMBER() OVER (
+                PARTITION BY var.id 
+                ORDER BY vra.uploaded_at DESC
+            ) = 1 THEN true 
+            ELSE false 
+        END as is_latest_response_file
+        
+    FROM vendor_response_attachments_b vra
+    JOIN vendor_attachment_responses var ON vra.vendor_response_id = var.id
+    JOIN b_rfq_attachments ba ON var.attachment_id = ba.id
+    LEFT JOIN vendors v ON var.vendor_id = v.id
+    LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id
+);
\ No newline at end of file
-- 
cgit v1.2.3