From 9da494b0e3bbe7b513521d0915510fe9ee376b8b Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Mon, 21 Jul 2025 07:19:52 +0000
Subject: (대표님, 최겸) 작업사항 - 이메일 템플릿, 메일링, 기술영업 요구사항
 반영
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations/0219_dashing_maggott.sql | 83 ++++++++++++++++++++++++++++++++++
 1 file changed, 83 insertions(+)
 create mode 100644 db/migrations/0219_dashing_maggott.sql

(limited to 'db/migrations/0219_dashing_maggott.sql')

diff --git a/db/migrations/0219_dashing_maggott.sql b/db/migrations/0219_dashing_maggott.sql
new file mode 100644
index 00000000..cff88957
--- /dev/null
+++ b/db/migrations/0219_dashing_maggott.sql
@@ -0,0 +1,83 @@
+DROP VIEW "public"."template_detail_view";--> statement-breakpoint
+DROP VIEW "public"."template_list_view";--> statement-breakpoint
+ALTER TABLE "template_history" ADD COLUMN "subject" text NOT NULL;--> statement-breakpoint
+ALTER TABLE "templates" ADD COLUMN "subject" text NOT NULL;--> statement-breakpoint
+CREATE VIEW "public"."template_detail_view" AS (
+  SELECT
+    t.id,
+    t.name,
+    t.slug,
+    t.subject,
+    t.content,
+    t.description,
+    t.category,
+    t.sample_data,
+    t.is_active,
+    t.version,
+    t.created_by,
+    u.name  AS created_by_name,
+    u.email AS created_by_email,
+    t.created_at,
+    t.updated_at,
+    COALESCE(
+      json_agg(
+        json_build_object(
+          'id', v.id,
+          'variableName', v.variable_name,
+          'variableType', v.variable_type,
+          'defaultValue', v.default_value,
+          'isRequired', v.is_required,
+          'description', v.description,
+          'displayOrder', v.display_order
+        ) ORDER BY v.display_order
+      ) FILTER (WHERE v.id IS NOT NULL),
+      '[]'::json
+    ) AS variables
+  FROM "templates" t
+  LEFT JOIN "users" u ON t.created_by = u.id
+  LEFT JOIN "template_variables" v ON t.id = v.template_id
+  GROUP BY
+    t.id,
+    t.name,
+    t.slug,
+    t.subject,
+    t.content,
+    t.description,
+    t.category,
+    t.sample_data,
+    t.is_active,
+    t.version,
+    t.created_by,
+    u.name,
+    u.email,
+    t.created_at,
+    t.updated_at
+);--> statement-breakpoint
+CREATE VIEW "public"."template_list_view" AS (
+  SELECT
+    t.id,
+    t.name,
+    t.slug,
+    t.subject,
+    t.description,
+    t.category,
+    t.is_active,
+    t.version,
+    t.created_by,
+    u.name  AS created_by_name,
+    u.email AS created_by_email,
+    t.created_at,
+    t.updated_at,
+    COALESCE(v.variable_count, 0) AS variable_count,
+    COALESCE(v.required_variable_count, 0) AS required_variable_count
+  FROM "templates" t
+  LEFT JOIN "users" u ON t.created_by = u.id
+  LEFT JOIN (
+    SELECT
+      template_id,
+      COUNT(*) AS variable_count,
+      COUNT(*) FILTER (WHERE is_required) AS required_variable_count
+    FROM "template_variables"
+    GROUP BY template_id
+  ) v ON t.id = v.template_id
+);
\ No newline at end of file
-- 
cgit v1.2.3

