1 files changed, 34 insertions, 0 deletions
diff --git a/db/migrations/0056_majestic_khan.sql b/db/migrations/0056_majestic_khan.sql
new file mode 100644
index 00000000..161e1c3c
--- /dev/null
+++ b/db/migrations/0056_majestic_khan.sql
@@ -0,0 +1,34 @@
+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,
+
+    (
+      SELECT json_agg(
+        json_build_object(
+          'id', i.id,
+          'stageName', i.stage_name,
+          'planDate', i.plan_date,
+          'actualDate', i.actual_date,
+          'createdAt', i.created_at,
+          'updatedAt', i.updated_at
+        )
+      )
+      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