path: root/db/migrations/0139_real_warlock.sql

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.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
);--> statement-breakpoint
CREATE VIEW "public"."rfq_progress_summary" AS (
    SELECT 
        br.id as rfq_id,
        br.rfq_code,
        br.status as rfq_status,
        br.due_date,
        (br.due_date - CURRENT_DATE) as days_to_deadline,
        
        -- 첨부파일 통계
        attachment_stats.total_attachments,
        attachment_stats.attachments_with_multiple_revisions,
        attachment_stats.total_client_revisions,
        
        -- Initial RFQ 통계
        COALESCE(initial_stats.vendor_count, 0) as initial_vendor_count,
        COALESCE(initial_stats.total_responses, 0) as initial_total_responses,
        COALESCE(initial_stats.responded_count, 0) as initial_responded_count,
        COALESCE(initial_stats.up_to_date_count, 0) as initial_up_to_date_count,
        COALESCE(initial_stats.version_mismatch_count, 0) as initial_version_mismatch_count,
        COALESCE(initial_stats.response_rate, 0) as initial_response_rate,
        COALESCE(initial_stats.version_match_rate, 0) as initial_version_match_rate,
        
        -- Final RFQ 통계
        COALESCE(final_stats.vendor_count, 0) as final_vendor_count,
        COALESCE(final_stats.total_responses, 0) as final_total_responses,
        COALESCE(final_stats.responded_count, 0) as final_responded_count,
        COALESCE(final_stats.up_to_date_count, 0) as final_up_to_date_count,
        COALESCE(final_stats.version_mismatch_count, 0) as final_version_mismatch_count,
        COALESCE(final_stats.response_rate, 0) as final_response_rate,
        COALESCE(final_stats.version_match_rate, 0) as final_version_match_rate,
        
        COALESCE(file_stats.total_files, 0) as total_response_files
        
    FROM b_rfqs br
    LEFT JOIN (
        SELECT 
            ba.rfq_id,
            COUNT(*) as total_attachments,
            COUNT(CASE WHEN rev_count.total_revisions > 1 THEN 1 END) as attachments_with_multiple_revisions,
            SUM(rev_count.total_revisions) as total_client_revisions
        FROM b_rfq_attachments ba
        LEFT JOIN (
            SELECT 
                attachment_id,
                COUNT(*) as total_revisions
            FROM b_rfq_attachment_revisions
            GROUP BY attachment_id
        ) rev_count ON ba.id = rev_count.attachment_id
        GROUP BY ba.rfq_id
    ) attachment_stats ON br.id = attachment_stats.rfq_id
    
    LEFT JOIN (
        SELECT 
            br.id as rfq_id,
            COUNT(DISTINCT var.vendor_id) as vendor_count,
            COUNT(*) as total_responses,
            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
            COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count,
            COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count,
            ROUND(
                COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(*), 0), 2
            ) as response_rate,
            ROUND(
                COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2
            ) as version_match_rate
        FROM b_rfqs br
        JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id
        JOIN vendor_attachment_responses var ON vrd.response_id = var.id
        WHERE var.rfq_type = 'INITIAL'
        GROUP BY br.id
    ) initial_stats ON br.id = initial_stats.rfq_id
    
    LEFT JOIN (
        SELECT 
            br.id as rfq_id,
            COUNT(DISTINCT var.vendor_id) as vendor_count,
            COUNT(*) as total_responses,
            COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count,
            COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) as up_to_date_count,
            COUNT(CASE WHEN vrd.effective_status = 'VERSION_MISMATCH' THEN 1 END) as version_mismatch_count,
            ROUND(
                COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(*), 0), 2
            ) as response_rate,
            ROUND(
                COUNT(CASE WHEN vrd.effective_status = 'UP_TO_DATE' THEN 1 END) * 100.0 / 
                NULLIF(COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END), 0), 2
            ) as version_match_rate
        FROM b_rfqs br
        JOIN vendor_response_detail vrd ON br.id = vrd.rfq_id
        JOIN vendor_attachment_responses var ON vrd.response_id = var.id
        WHERE var.rfq_type = 'FINAL'
        GROUP BY br.id
    ) final_stats ON br.id = final_stats.rfq_id
    
    LEFT JOIN (
        SELECT 
            br.id as rfq_id,
            COUNT(vra.id) as total_files
        FROM b_rfqs br
        JOIN b_rfq_attachments ba ON br.id = ba.rfq_id
        JOIN vendor_attachment_responses var ON ba.id = var.attachment_id
        LEFT JOIN vendor_response_attachments_b vra ON var.id = vra.vendor_response_id
        GROUP BY br.id
    ) file_stats ON br.id = file_stats.rfq_id
);--> 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.requested_at,
        var.responded_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
);--> 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.requested_at,
        var.responded_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
);