DROP VIEW "public"."gtc_clauses_with_vendor_view";--> statement-breakpoint
CREATE VIEW "public"."gtc_clauses_with_vendor_view" AS (select "gtc_clauses"."id", "gtc_clauses"."document_id", "gtc_clauses"."parent_id", "gtc_clauses"."item_number", "gtc_clauses"."category", "gtc_clauses"."subtitle", "gtc_clauses"."content", "gtc_clauses"."sort_order", "gtc_clauses"."depth", "gtc_clauses"."full_path", "gtc_clauses"."is_active", "gtc_vendor_clauses"."id", "gtc_vendor_clauses"."vendor_document_id", vendors.id as "vendor_id", vendors.vendor_code as "vendor_code", vendors.vendor_name as "vendor_name", gtc_docs.type as "document_type", gtc_docs.title as "document_title", gtc_docs.revision as "document_revision", 
        COALESCE("gtc_vendor_clauses"."modified_item_number", "gtc_clauses"."item_number")
       as "effective_item_number", 
        COALESCE("gtc_vendor_clauses"."modified_category", "gtc_clauses"."category")
       as "effective_category", 
        COALESCE("gtc_vendor_clauses"."modified_subtitle", "gtc_clauses"."subtitle")
       as "effective_subtitle", 
        COALESCE("gtc_vendor_clauses"."modified_content", "gtc_clauses"."content")
       as "effective_content", 
        COALESCE("gtc_vendor_clauses"."sort_order", "gtc_clauses"."sort_order")
       as "effective_sort_order", 
        COALESCE("gtc_vendor_clauses"."depth", "gtc_clauses"."depth")
       as "effective_depth", 
        COALESCE("gtc_vendor_clauses"."full_path", "gtc_clauses"."full_path")
       as "effective_full_path", 
        "gtc_vendor_clauses"."is_number_modified" IS TRUE
       as "is_number_modified", 
        "gtc_vendor_clauses"."is_category_modified" IS TRUE
       as "is_category_modified", 
        "gtc_vendor_clauses"."is_subtitle_modified" IS TRUE
       as "is_subtitle_modified", 
        "gtc_vendor_clauses"."is_content_modified" IS TRUE
       as "is_content_modified", 
        "gtc_vendor_clauses"."id" IS NOT NULL
       as "has_vendor_clause", 
        ("gtc_vendor_clauses"."is_number_modified" IS TRUE) OR 
        ("gtc_vendor_clauses"."is_category_modified" IS TRUE) OR 
        ("gtc_vendor_clauses"."is_subtitle_modified" IS TRUE) OR 
        ("gtc_vendor_clauses"."is_content_modified" IS TRUE)
       as "has_modifications", "gtc_vendor_clauses"."review_status", "gtc_vendor_clauses"."negotiation_note", 
        "gtc_vendor_clauses"."is_excluded" IS TRUE
       as "is_excluded", 
        EXISTS(
          SELECT 1 
          FROM gtc_negotiation_history gnh 
          WHERE gnh.vendor_clause_id = "gtc_vendor_clauses"."id"
          AND gnh.comment IS NOT NULL 
          AND gnh.comment != ''
        )
       as "has_negotiation_history", "gtc_clauses"."created_at", "gtc_clauses"."updated_at", "gtc_vendor_clauses"."created_at", "gtc_vendor_clauses"."updated_at" from "gtc_clauses" left join "gtc_vendor_clauses" on 
      "gtc_clauses"."id" = "gtc_vendor_clauses"."base_clause_id"
     left join "gtc_vendor_documents" on 
      "gtc_vendor_clauses"."vendor_document_id" = "gtc_vendor_documents"."id"
     left join vendors on 
      "gtc_vendor_documents"."vendor_id" = vendors.id
     left join gtc_documents gtc_docs on 
      "gtc_clauses"."document_id" = gtc_docs.id
    );