1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
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
);
|
