DROP VIEW "public"."vendor_response_detail";--> statement-breakpoint
CREATE VIEW "public"."vendor_response_detail" AS (
    SELECT 
        var.id as response_id,
        ba.rfq_id,
        br.rfq_code,
        var.rfq_type,
        var.rfq_record_id,
        
        -- 첨부파일 정보
        ba.id as attachment_id,
        ba.attachment_type,
        ba.serial_no,
        ba.description as attachment_description,
        
        -- 벤더 정보
        v.id as vendor_id,
        v.vendor_code,
        v.vendor_name,
        v.country as vendor_country,
        
        -- 응답 상태
        var.response_status,
        var.current_revision,
        var.responded_revision,
        
        -- 코멘트 (새로 추가된 필드 포함)
        var.response_comment,
        var.vendor_comment,
        var.revision_request_comment,
        
        -- 날짜 (새로 추가된 필드 포함)
        var.requested_at,
        var.responded_at,
        var.revision_requested_at,
        
        -- 발주처 최신 리비전
        latest_rev.revision_no as latest_client_revision_no,
        latest_rev.original_file_name as latest_client_file_name,
        latest_rev.file_size as latest_client_file_size,
        latest_rev.revision_comment as latest_client_revision_comment,
        
        -- 리비전 분석
        CASE 
            WHEN var.responded_revision = ba.current_revision THEN true 
            ELSE false 
        END as is_version_matched,
        
        CASE 
            WHEN var.responded_revision IS NULL OR ba.current_revision IS NULL THEN NULL
            ELSE CAST(SUBSTRING(ba.current_revision FROM '[0-9]+') AS INTEGER) - 
                 CAST(SUBSTRING(var.responded_revision FROM '[0-9]+') AS INTEGER)
        END as version_lag,
        
        CASE 
            WHEN var.response_status = 'RESPONDED' 
                 AND var.responded_revision != ba.current_revision THEN true 
            ELSE false 
        END as needs_update,
        
        CASE 
            WHEN revision_count.total_revisions > 1 THEN true 
            ELSE false 
        END as has_multiple_revisions,
        
        -- 응답 파일 정보
        COALESCE(file_stats.total_files, 0) as total_response_files,
        file_stats.latest_file_name as latest_response_file_name,
        file_stats.latest_file_size as latest_response_file_size,
        file_stats.latest_uploaded_at as latest_response_uploaded_at,
        
        -- 효과적인 상태
        CASE 
            WHEN var.response_status = 'NOT_RESPONDED' THEN 'NOT_RESPONDED'
            WHEN var.response_status = 'WAIVED' THEN 'WAIVED'
            WHEN var.response_status = 'REVISION_REQUESTED' THEN 'REVISION_REQUESTED'
            WHEN var.response_status = 'RESPONDED' AND var.responded_revision = ba.current_revision THEN 'UP_TO_DATE'
            WHEN var.response_status = 'RESPONDED' AND var.responded_revision != ba.current_revision THEN 'VERSION_MISMATCH'
            ELSE var.response_status
        END as effective_status
        
    FROM vendor_attachment_responses var
    JOIN b_rfq_attachments ba ON var.attachment_id = ba.id
    JOIN b_rfqs br ON ba.rfq_id = br.id
    LEFT JOIN vendors v ON var.vendor_id = v.id
    LEFT JOIN b_rfq_attachment_revisions latest_rev ON ba.latest_revision_id = latest_rev.id
    LEFT JOIN (
        SELECT 
            attachment_id,
            COUNT(*) as total_revisions
        FROM b_rfq_attachment_revisions
        GROUP BY attachment_id
    ) revision_count ON ba.id = revision_count.attachment_id
    LEFT JOIN (
        SELECT 
            vendor_response_id,
            COUNT(*) as total_files,
            MAX(original_file_name) as latest_file_name,
            MAX(file_size) as latest_file_size,
            MAX(uploaded_at) as latest_uploaded_at
        FROM vendor_response_attachments_b
        GROUP BY vendor_response_id
    ) file_stats ON var.id = file_stats.vendor_response_id
);