diff options
Diffstat (limited to 'db/migrations/0219_dashing_maggott.sql')
| -rw-r--r-- | db/migrations/0219_dashing_maggott.sql | 83 |
1 files changed, 83 insertions, 0 deletions
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 |