diff options
Diffstat (limited to 'db/migrations/0240_far_chat.sql')
| -rw-r--r-- | db/migrations/0240_far_chat.sql | 95 |
1 files changed, 95 insertions, 0 deletions
diff --git a/db/migrations/0240_far_chat.sql b/db/migrations/0240_far_chat.sql new file mode 100644 index 00000000..c0ccb2da --- /dev/null +++ b/db/migrations/0240_far_chat.sql @@ -0,0 +1,95 @@ +DROP VIEW "public"."gtc_documents_view";--> statement-breakpoint +CREATE VIEW "public"."gtc_documents_view" AS (select "gtc_documents"."id", "gtc_documents"."type", "gtc_documents"."project_id", "gtc_documents"."revision", "gtc_documents"."file_name", "gtc_documents"."file_name", "gtc_documents"."file_path", "gtc_documents"."file_size", "gtc_documents"."created_at", "gtc_documents"."created_by_id", "gtc_documents"."updated_at", "gtc_documents"."updated_by_id", "gtc_documents"."edit_reason", "gtc_documents"."is_active", "projects"."code", "projects"."name", created_by_user.name as "created_by_name", created_by_user.email as "created_by_email", updated_by_user.name as "updated_by_name", updated_by_user.email as "updated_by_email", + ( + SELECT count(*) + FROM gtc_documents gd2 + WHERE gd2.type = "gtc_documents"."type" + AND gd2.is_active = true + AND ( + ("gtc_documents"."type" = 'project' AND gd2.project_id = "gtc_documents"."project_id") OR + ("gtc_documents"."type" = 'standard' AND gd2.project_id IS NULL) + ) + ) + as "total_documents_in_group", + ( + SELECT max(revision) + FROM gtc_documents gd3 + WHERE gd3.type = "gtc_documents"."type" + AND gd3.is_active = true + AND ( + ("gtc_documents"."type" = 'project' AND gd3.project_id = "gtc_documents"."project_id") OR + ("gtc_documents"."type" = 'standard' AND gd3.project_id IS NULL) + ) + ) + as "latest_revision", + "gtc_documents"."revision" = ( + SELECT max(revision) + FROM gtc_documents gd4 + WHERE gd4.type = "gtc_documents"."type" + AND gd4.is_active = true + AND ( + ("gtc_documents"."type" = 'project' AND gd4.project_id = "gtc_documents"."project_id") OR + ("gtc_documents"."type" = 'standard' AND gd4.project_id IS NULL) + ) + ) + as "is_latest_revision", + ( + SELECT id + FROM gtc_documents gd5 + WHERE gd5.type = "gtc_documents"."type" + AND gd5.is_active = true + AND gd5.revision < "gtc_documents"."revision" + AND ( + ("gtc_documents"."type" = 'project' AND gd5.project_id = "gtc_documents"."project_id") OR + ("gtc_documents"."type" = 'standard' AND gd5.project_id IS NULL) + ) + ORDER BY gd5.revision DESC + LIMIT 1 + ) + as "previous_revision_id", + ( + SELECT id + FROM gtc_documents gd6 + WHERE gd6.type = "gtc_documents"."type" + AND gd6.is_active = true + AND gd6.revision > "gtc_documents"."revision" + AND ( + ("gtc_documents"."type" = 'project' AND gd6.project_id = "gtc_documents"."project_id") OR + ("gtc_documents"."type" = 'standard' AND gd6.project_id IS NULL) + ) + ORDER BY gd6.revision ASC + LIMIT 1 + ) + as "next_revision_id", + CASE + WHEN "gtc_documents"."file_size" IS NULL THEN NULL + WHEN "gtc_documents"."file_size" < 1024 THEN "gtc_documents"."file_size" || ' B' + WHEN "gtc_documents"."file_size" < 1024 * 1024 THEN round("gtc_documents"."file_size" / 1024.0, 1) || ' KB' + WHEN "gtc_documents"."file_size" < 1024 * 1024 * 1024 THEN round("gtc_documents"."file_size" / (1024.0 * 1024), 1) || ' MB' + ELSE round("gtc_documents"."file_size" / (1024.0 * 1024 * 1024), 1) || ' GB' + END + as "file_size_formatted", + CASE + WHEN "gtc_documents"."project_id" IS NOT NULL THEN ( + SELECT count(*) + FROM gtc_documents gd7 + WHERE gd7.project_id = "gtc_documents"."project_id" + AND gd7.is_active = true + ) + ELSE NULL + END + as "project_total_documents", + ( + SELECT array_agg(revision ORDER BY revision) + FROM gtc_documents gd8 + WHERE gd8.type = "gtc_documents"."type" + AND gd8.is_active = true + AND ( + ("gtc_documents"."type" = 'project' AND gd8.project_id = "gtc_documents"."project_id") OR + ("gtc_documents"."type" = 'standard' AND gd8.project_id IS NULL) + ) + ) + as "revision_history", + "gtc_documents"."created_by_id" != "gtc_documents"."updated_by_id" OR + "gtc_documents"."created_at" != "gtc_documents"."updated_at" + as "has_edit_history" from "gtc_documents" left join "projects" on "gtc_documents"."project_id" = "projects"."id" left join users created_by_user on "gtc_documents"."created_by_id" = created_by_user.id left join users updated_by_user on "gtc_documents"."updated_by_id" = updated_by_user.id);
\ No newline at end of file |