(공통) 0919 DB Migrations

1 files changed, 18 insertions, 0 deletions

diff --git a/db/migrations/0382_quick_kulan_gath.sql b/db/migrations/0382_quick_kulan_gath.sql
new file mode 100644
index 00000000..381a1d5c
--- /dev/null
+++ b/db/migrations/0382_quick_kulan_gath.sql
@@ -0,0 +1,18 @@
+DROP VIEW "public"."document_stages_view";--> statement-breakpoint
+CREATE VIEW "public"."document_stages_view" AS (
+  SELECT
+    d.id AS document_id,
+    d.doc_number,
+    d.title,
+    d.status,
+    d.issued_date,
+    d.contract_id,
+    (SELECT COUNT(*) FROM issue_stages WHERE document_id = d.id) AS stage_count,
+    COALESCE( 
+      (SELECT json_agg(i.stage_name) FROM issue_stages i WHERE i.document_id = d.id), 
+      '[]'
+    ) AS stage_list,
+    d.created_at,
+    d.updated_at
+  FROM documents d
+);
\ No newline at end of file