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_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);