DROP VIEW "public"."stage_submission_view";--> statement-breakpoint
ALTER TABLE "stage_submissions" ADD COLUMN "revision_code" varchar(50) NOT NULL;--> statement-breakpoint
CREATE VIEW "public"."stage_submission_view" AS (
  WITH eligible_documents AS (
    -- buyerSystemStatus가 '승인(DC)'인 문서만 선택
    SELECT 
      sd.id as document_id,
      sd.doc_number,
      sd.vendor_doc_number,
      sd.title as document_title,
      sd.project_id,
      sd.vendor_id,
      p.code as project_code,
      v.vendor_name,
      v.vendor_code
    FROM stage_documents sd
    LEFT JOIN projects p ON sd.project_id = p.id
    LEFT JOIN vendors v ON sd.vendor_id = v.id
    WHERE sd.buyer_system_status = '승인(DC)'
      AND sd.status = 'ACTIVE'
  ),
  
  submission_stats AS (
    SELECT 
      ss.stage_id,
      COUNT(*) as total_submissions,
      COUNT(CASE WHEN ss.review_status = 'APPROVED' THEN 1 END) as approved_submissions,
      COUNT(CASE WHEN ss.review_status = 'REJECTED' THEN 1 END) as rejected_submissions,
      COUNT(CASE WHEN ss.review_status = 'PENDING' OR ss.review_status IS NULL THEN 1 END) as pending_submissions,
      SUM(ss.total_files) as total_files,
      SUM(ss.total_file_size) as total_file_size,
      MAX(ss.revision_number) as latest_revision_number
    FROM stage_submissions ss
    GROUP BY ss.stage_id
  ),
  
  latest_submission AS (
    SELECT DISTINCT ON (stage_id)
      stage_id,
      id as submission_id,
      revision_number,
      revision_code, -- ⭐ 추가
      submission_status,
      submitted_at,
      submitted_by,
      review_status,
      sync_status,
      buyer_system_status,
      last_synced_at,
      total_files_to_sync,
      synced_files_count,
      CASE 
        WHEN total_files_to_sync > 0 
        THEN ROUND((synced_files_count * 100.0) / total_files_to_sync)
        ELSE 0 
      END as sync_progress
    FROM stage_submissions
    ORDER BY stage_id, revision_number DESC
  ),
  
  submission_history_agg AS (
    SELECT 
      ss.stage_id,
      json_agg(
        json_build_object(
          'submissionId', ss.id,
          'revisionNumber', ss.revision_number,
          'revisionCode', ss.revision_code, -- ⭐ 추가
          'status', ss.submission_status,
          'submittedAt', ss.submitted_at,
          'submittedBy', ss.submitted_by,
          'reviewStatus', ss.review_status,
          'syncStatus', ss.sync_status,
          'fileCount', ss.total_files
        ) ORDER BY ss.revision_number DESC
      ) as submission_history
    FROM stage_submissions ss
    GROUP BY ss.stage_id
  )
  
  SELECT 
    ed.document_id,
    ed.doc_number,
    ed.vendor_doc_number,
    ed.document_title,
    ed.project_id,
    ed.project_code,
    ed.vendor_id,
    ed.vendor_name,
    ed.vendor_code,
    
    ist.id as stage_id,
    ist.stage_name,
    ist.stage_order,
    ist.stage_status,
    ist.plan_date as stage_plan_date,
    
    ls.submission_id as latest_submission_id,
    ls.revision_number as latest_revision_number,
    ls.revision_code as latest_revision_code, -- ⭐ 추가
    ls.submission_status as latest_submission_status,
    ls.submitted_at as latest_submitted_at,
    ls.submitted_by as latest_submitted_by,
    ls.review_status as latest_review_status,
    
    -- 동기화 상태
    ls.sync_status as latest_sync_status,
    ls.buyer_system_status as latest_buyer_system_status,
    ls.last_synced_at,
    ls.total_files_to_sync,
    ls.synced_files_count,
    ls.sync_progress,
    
    COALESCE(ss.total_submissions, 0) as total_submissions,
    COALESCE(ss.approved_submissions, 0) as approved_submissions,
    COALESCE(ss.rejected_submissions, 0) as rejected_submissions,
    COALESCE(ss.pending_submissions, 0) as pending_submissions,
    COALESCE(ss.total_files, 0) as total_files,
    COALESCE(ss.total_file_size, 0) as total_file_size,
    
    -- 제출이 필요한지 판단
    CASE 
      WHEN ist.stage_status IN ('PLANNED', 'IN_PROGRESS', 'REJECTED') 
        AND (ls.review_status IS NULL OR ls.review_status != 'APPROVED')
      THEN true 
      ELSE false 
    END as requires_submission,
    
    -- 동기화가 필요한지 판단
    CASE 
      WHEN ls.sync_status IN ('pending', 'failed') 
        OR ls.synced_files_count < ls.total_files_to_sync
      THEN true 
      ELSE false 
    END as requires_sync,
    
    -- 기한 관련
    CASE 
      WHEN ist.plan_date < CURRENT_DATE 
        AND ist.stage_status NOT IN ('COMPLETED', 'APPROVED')
      THEN true 
      ELSE false 
    END as is_overdue,
    
    CASE 
      WHEN ist.plan_date IS NOT NULL 
      THEN ist.plan_date - CURRENT_DATE
      ELSE NULL 
    END as days_until_due,
    
    COALESCE(sha.submission_history, '[]'::json) as submission_history
    
  FROM eligible_documents ed
  INNER JOIN stage_issue_stages ist ON ed.document_id = ist.document_id
  LEFT JOIN submission_stats ss ON ist.id = ss.stage_id
  LEFT JOIN latest_submission ls ON ist.id = ls.stage_id
  LEFT JOIN submission_history_agg sha ON ist.id = sha.stage_id
  
  ORDER BY ed.document_id, ist.stage_order
);