From 95bbe9c583ff841220da1267630e7b2025fc36dc Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Thu, 19 Jun 2025 09:44:28 +0000
Subject: (대표님) 20250619 1844 KST 작업사항
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations/0150_harsh_gateway.sql | 10 ++++++++++
 1 file changed, 10 insertions(+)
 create mode 100644 db/migrations/0150_harsh_gateway.sql

(limited to 'db/migrations/0150_harsh_gateway.sql')

diff --git a/db/migrations/0150_harsh_gateway.sql b/db/migrations/0150_harsh_gateway.sql
new file mode 100644
index 00000000..a392ccfb
--- /dev/null
+++ b/db/migrations/0150_harsh_gateway.sql
@@ -0,0 +1,10 @@
+DROP VIEW "public"."esg_evaluations_view";--> statement-breakpoint
+CREATE VIEW "public"."esg_evaluations_view" AS (select "esg_evaluations"."id", "esg_evaluations"."serial_number", "esg_evaluations"."category", "esg_evaluations"."inspection_item", "esg_evaluations"."is_active", "esg_evaluations"."created_at", "esg_evaluations"."updated_at", count(distinct "esg_evaluation_items"."id") as "total_evaluation_items", count("esg_answer_options"."id") as "total_answer_options", coalesce(sum("esg_answer_options"."score"), 0) as "max_possible_score", 
+        (
+          SELECT array_agg(evaluation_item order by order_index) 
+          FROM esg_evaluation_items 
+          WHERE esg_evaluation_id = "esg_evaluations"."id" 
+          AND is_active = true 
+          AND evaluation_item is not null
+        )
+       as "evaluation_items_list" from "esg_evaluations" left join "esg_evaluation_items" on "esg_evaluations"."id" = "esg_evaluation_items"."esg_evaluation_id" AND "esg_evaluation_items"."is_active" = true left join "esg_answer_options" on "esg_evaluation_items"."id" = "esg_answer_options"."esg_evaluation_item_id" AND "esg_answer_options"."is_active" = true group by "esg_evaluations"."id", "esg_evaluations"."serial_number", "esg_evaluations"."category", "esg_evaluations"."inspection_item", "esg_evaluations"."is_active", "esg_evaluations"."created_at", "esg_evaluations"."updated_at");
\ No newline at end of file
-- 
cgit v1.2.3

