DROP VIEW "public"."attachment_revision_history";--> statement-breakpoint
CREATE VIEW "public"."attachment_revision_history" AS (
    SELECT 
        br.id as rfq_id,
        br.rfq_code,
        ba.id as attachment_id,
        ba.attachment_type,
        ba.serial_no,
        
        -- 발주처 리비전 정보
        rev.id as client_revision_id,
        rev.revision_no as client_revision_no,
        rev.original_file_name as client_file_name,
        rev.file_size as client_file_size,
        rev.file_path as client_file_path,
        rev.revision_comment as client_revision_comment,
        rev.created_at as client_revision_created_at,
        rev.is_latest as is_latest_client_revision,
        
        -- 벤더 응답 통계
        COALESCE(response_stats.total_responses, 0) as total_vendor_responses,
        COALESCE(response_stats.responded_count, 0) as responded_vendors,
        COALESCE(response_stats.pending_count, 0) as pending_vendors,
        COALESCE(response_stats.total_files, 0) as total_response_files
        
    FROM b_rfqs br
    JOIN b_rfq_attachments ba ON br.id = ba.rfq_id
    JOIN b_rfq_attachment_revisions rev ON ba.id = rev.attachment_id
    LEFT JOIN (
        SELECT 
            var.attachment_id,
            COUNT(*) as total_responses,
            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
            COUNT(CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 1 END) as pending_count,
            COUNT(vra.id) as total_files
        FROM vendor_attachment_responses var
        LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id
        GROUP BY var.attachment_id
    ) response_stats ON ba.id = response_stats.attachment_id
    
    ORDER BY ba.id, rev.created_at DESC
);