(대표님) 20250617 18시 작업사항

1 files changed, 42 insertions, 0 deletions

diff --git a/db/migrations/0140_careless_agent_zero.sql b/db/migrations/0140_careless_agent_zero.sql
new file mode 100644
index 00000000..53bd8724
--- /dev/null
+++ b/db/migrations/0140_careless_agent_zero.sql
@@ -0,0 +1,42 @@
+DROP VIEW "public"."attachment_revision_history";--> statement-breakpoint
+CREATE VIEW "public"."attachment_revision_history" AS (
+    SELECT 
+        br.id as rfq_id,
+        br.rfq_code,
+        ba.id as attachment_id,
+        ba.attachment_type,
+        ba.serial_no,
+        
+        -- 발주처 리비전 정보
+        rev.id as client_revision_id,
+        rev.revision_no as client_revision_no,
+        rev.original_file_name as client_file_name,
+        rev.file_size as client_file_size,
+        rev.file_path as client_file_path,
+        rev.revision_comment as client_revision_comment,
+        rev.created_at as client_revision_created_at,
+        rev.is_latest as is_latest_client_revision,
+        
+        -- 벤더 응답 통계
+        COALESCE(response_stats.total_responses, 0) as total_vendor_responses,
+        COALESCE(response_stats.responded_count, 0) as responded_vendors,
+        COALESCE(response_stats.pending_count, 0) as pending_vendors,
+        COALESCE(response_stats.total_files, 0) as total_response_files
+        
+    FROM b_rfqs br
+    JOIN b_rfq_attachments ba ON br.id = ba.rfq_id
+    JOIN b_rfq_attachment_revisions rev ON ba.id = rev.attachment_id
+    LEFT JOIN (
+        SELECT 
+            var.attachment_id,
+            COUNT(*) as total_responses,
+            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
+            COUNT(CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 1 END) as pending_count,
+            COUNT(vra.id) as total_files
+        FROM vendor_attachment_responses var
+        LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id
+        GROUP BY var.attachment_id
+    ) response_stats ON ba.id = response_stats.attachment_id
+    
+    ORDER BY ba.id, rev.created_at DESC
+);
\ No newline at end of file