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
|
DROP VIEW "public"."template_detail_view";--> statement-breakpoint
DROP VIEW "public"."template_list_view";--> statement-breakpoint
ALTER TABLE "template_history" ALTER COLUMN "changed_by" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "templates" ALTER COLUMN "created_by" SET DATA TYPE integer;--> statement-breakpoint
ALTER TABLE "template_history" ADD CONSTRAINT "template_history_changed_by_users_id_fk" FOREIGN KEY ("changed_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "templates" ADD CONSTRAINT "templates_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."template_detail_view" AS (
SELECT
t.id,
t.name,
t.slug,
t.content,
t.description,
t.category,
t.sample_data,
t.is_active,
t.version,
t.created_by,
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 "template_variables" v ON t.id = v.template_id
GROUP BY t.id, t.name, t.slug, t.content, t.description, t.category,
t.sample_data, t.is_active, t.version, t.created_by, t.created_at, t.updated_at
);--> statement-breakpoint
CREATE VIEW "public"."template_list_view" AS (
SELECT
t.id,
t.name,
t.slug,
t.description,
t.category,
t.is_active,
t.version,
t.created_by,
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 (
SELECT
template_id,
COUNT(*) AS variable_count,
COUNT(*) FILTER (WHERE is_required = true) AS required_variable_count
FROM "template_variables"
GROUP BY template_id
) v ON t.id = v.template_id
);
|
