DROP VIEW "public"."vendor_response_attachments_enhanced";--> statement-breakpoint
CREATE VIEW "public"."vendor_response_attachments_enhanced" AS (
    SELECT 
        vra.id as response_attachment_id,
        vra.vendor_response_id,
        vra.file_name,
        vra.original_file_name,
        vra.file_path,
        vra.file_size,
        vra.file_type,
        vra.description,
        vra.uploaded_at,
        
        -- 응답 기본 정보
        var.attachment_id,
        var.vendor_id,
        var.rfq_type,
        var.rfq_record_id,
        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,
        
        -- 첨부파일 정보
        ba.attachment_type,
        ba.serial_no,
        ba.rfq_id,
        
        -- 벤더 정보
        v.vendor_code,
        v.vendor_name,
        v.country as vendor_country,
        
        -- 발주처 현재 리비전 정보
        latest_rev.id as latest_client_revision_id,
        latest_rev.revision_no as latest_client_revision_no,
        latest_rev.original_file_name as latest_client_file_name,
        
        -- 리비전 비교
        CASE 
            WHEN var.responded_revision = ba.current_revision THEN true 
            ELSE false 
        END as is_version_matched,
        
        -- 버전 차이 계산 (Rev.0, Rev.1 형태 가정)
        CASE 
            WHEN var.responded_revision IS NULL THEN NULL
            WHEN 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,
        
        -- 파일 순서
        ROW_NUMBER() OVER (
            PARTITION BY var.id 
            ORDER BY vra.uploaded_at DESC
        ) as file_sequence,
        
        -- 최신 응답 파일 여부
        CASE 
            WHEN ROW_NUMBER() OVER (
                PARTITION BY var.id 
                ORDER BY vra.uploaded_at DESC
            ) = 1 THEN true 
            ELSE false 
        END as is_latest_response_file
        
    FROM vendor_response_attachments_b vra
    JOIN vendor_attachment_responses var ON vra.vendor_response_id = var.id
    JOIN b_rfq_attachments ba ON var.attachment_id = ba.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
);