From 9ceed79cf32c896f8a998399bf1b296506b2cd4a Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Tue, 8 Apr 2025 03:08:19 +0000
Subject: 로그인 및 미들웨어 처리. 구조 변경
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations_backup/0054_polite_darwin.sql | 81 +++++++++++++++++++++++++++++
 1 file changed, 81 insertions(+)
 create mode 100644 db/migrations_backup/0054_polite_darwin.sql

(limited to 'db/migrations_backup/0054_polite_darwin.sql')

diff --git a/db/migrations_backup/0054_polite_darwin.sql b/db/migrations_backup/0054_polite_darwin.sql
new file mode 100644
index 00000000..ead16a74
--- /dev/null
+++ b/db/migrations_backup/0054_polite_darwin.sql
@@ -0,0 +1,81 @@
+DROP TABLE "vendor_documents" CASCADE;--> statement-breakpoint
+CREATE VIEW "public"."vendor_documents_view" AS (
+    SELECT 
+      d.id, 
+      d.doc_number,
+      d.title,
+      d.status,
+      d.issued_date,
+  
+      d.contract_id,
+      c.contract_no,
+      c.name AS contract_name,
+  
+      c.vendor_id,
+      v.name AS vendor_name,
+  
+      c.project_id,
+      p.code AS project_code,
+      p.name AS project_name,
+      p.type AS project_type,
+  
+      (
+        SELECT id FROM issue_stages
+        WHERE document_id = d.id
+        ORDER BY created_at DESC LIMIT 1
+      ) AS latest_stage_id,
+      (
+        SELECT stage_name FROM issue_stages
+        WHERE document_id = d.id
+        ORDER BY created_at DESC LIMIT 1
+      ) AS latest_stage_name,
+      (
+        SELECT plan_date FROM issue_stages
+        WHERE document_id = d.id
+        ORDER BY created_at DESC LIMIT 1
+      ) AS latest_stage_plan_date,
+      (
+        SELECT actual_date FROM issue_stages
+        WHERE document_id = d.id
+        ORDER BY created_at DESC LIMIT 1
+      ) AS latest_stage_actual_date,
+  
+      (
+        SELECT r.id FROM revisions r
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+        ORDER BY r.created_at DESC LIMIT 1
+      ) AS latest_revision_id,
+      (
+        SELECT r.revision FROM revisions r
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+        ORDER BY r.created_at DESC LIMIT 1
+      ) AS latest_revision,
+      (
+        SELECT r.uploader_type FROM revisions r
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+        ORDER BY r.created_at DESC LIMIT 1
+      ) AS latest_revision_uploader_type,
+      (
+        SELECT r.uploader_name FROM revisions r
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+        ORDER BY r.created_at DESC LIMIT 1
+      ) AS latest_revision_uploader_name,
+  
+      (
+        SELECT COUNT(*) FROM document_attachments a
+        JOIN revisions r ON a.revision_id = r.id
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+      ) AS attachment_count,
+  
+      d.created_at,
+      d.updated_at
+    FROM documents d
+    JOIN contracts c ON d.contract_id = c.id
+    JOIN vendors v ON c.vendor_id = v.id
+    JOIN projects p ON c.project_id = p.id
+  );
\ No newline at end of file
-- 
cgit v1.2.3

