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