DROP VIEW "public"."simplified_documents_view";--> statement-breakpoint
ALTER TABLE "documents" ADD COLUMN "vendor_id" integer;--> statement-breakpoint
ALTER TABLE "documents" ADD CONSTRAINT "documents_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."simplified_documents_view" AS (
  WITH 
  -- 리비전별 첨부파일 집계
  revision_attachments AS (
    SELECT 
      r.id as revision_id,
      COALESCE(
        json_agg(
          json_build_object(
            'id', da.id,
            'revisionId', da.revision_id,
            'fileName', da.file_name,
            'filePath', da.file_path,
            'fileSize', da.file_size,
            'fileType', da.file_type,
            'createdAt', da.created_at,
            'updatedAt', da.updated_at
          ) ORDER BY da.created_at
        ) FILTER (WHERE da.id IS NOT NULL),
        '[]'::json
      ) as attachments
    FROM revisions r
    LEFT JOIN document_attachments da ON r.id = da.revision_id
    GROUP BY r.id
  ),
  
  -- 스테이지별 리비전 집계 (첨부파일 포함)
  stage_revisions AS (
    SELECT 
      ist.id as stage_id,
      COALESCE(
        json_agg(
          json_build_object(
            'id', r.id,
            'issueStageId', r.issue_stage_id,
            'revision', r.revision,
            'uploaderType', r.uploader_type,
            'uploaderId', r.uploader_id,
            'uploaderName', r.uploader_name,
            'comment', r.comment,
            'usage', r.usage,
            'usageType', r.usage_type,
            'revisionStatus', r.revision_status,
            'submittedDate', r.submitted_date,
            'uploadedAt', r.uploaded_at,
            'approvedDate', r.approved_date,
            'reviewStartDate', r.review_start_date,
            'rejectedDate', r.rejected_date,
            'reviewerId', r.reviewer_id,
            'reviewerName', r.reviewer_name,
            'reviewComments', r.review_comments,
            'createdAt', r.created_at,
            'updatedAt', r.updated_at,
            'attachments', COALESCE(ra.attachments, '[]'::json)
          ) ORDER BY r.created_at
        ) FILTER (WHERE r.id IS NOT NULL),
        '[]'::json
      ) as revisions
    FROM issue_stages ist
    LEFT JOIN revisions r ON ist.id = r.issue_stage_id
    LEFT JOIN revision_attachments ra ON r.id = ra.revision_id
    GROUP BY ist.id
  ),
  
  -- 문서별 스테이지 집계 (리비전 포함)
  stage_aggregation AS (
    SELECT 
      ist.document_id,
      json_agg(
        json_build_object(
          'id', ist.id,
          'stageName', ist.stage_name,
          'stageStatus', ist.stage_status,
          'stageOrder', ist.stage_order,
          'planDate', ist.plan_date,
          'actualDate', ist.actual_date,
          'assigneeName', ist.assignee_name,
          'priority', ist.priority,
          'revisions', COALESCE(sr.revisions, '[]'::json)
        ) ORDER BY ist.stage_order
      ) as all_stages
    FROM issue_stages ist
    LEFT JOIN stage_revisions sr ON ist.id = sr.stage_id
    GROUP BY ist.document_id
  ),
  
  -- 첫 번째 스테이지 정보 (drawingKind에 따라 다른 조건)
  first_stage_info AS (
    SELECT 
      document_id,
      first_stage_id,
      first_stage_name,
      first_stage_plan_date,
      first_stage_actual_date
    FROM (
      SELECT 
        ist.document_id,
        ist.id as first_stage_id,
        ist.stage_name as first_stage_name,
        ist.plan_date as first_stage_plan_date,
        ist.actual_date as first_stage_actual_date,
        ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn
      FROM issue_stages ist
      JOIN documents d ON ist.document_id = d.id
      WHERE 
        (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%pre%') OR
        (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%approval%') OR
        (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%first%')
    ) ranked
    WHERE rn = 1
  ),
  
  -- 두 번째 스테이지 정보 (drawingKind에 따라 다른 조건)
  second_stage_info AS (
    SELECT 
      document_id,
      second_stage_id,
      second_stage_name,
      second_stage_plan_date,
      second_stage_actual_date
    FROM (
      SELECT 
        ist.document_id,
        ist.id as second_stage_id,
        ist.stage_name as second_stage_name,
        ist.plan_date as second_stage_plan_date,
        ist.actual_date as second_stage_actual_date,
        ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn
      FROM issue_stages ist
      JOIN documents d ON ist.document_id = d.id
      WHERE 
        (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%work%') OR
        (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%work%') OR
        (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%second%')
    ) ranked
    WHERE rn = 1
  ),
  
  -- 첨부파일 수 집계
  attachment_counts AS (
    SELECT 
      ist.document_id,
      COUNT(da.id) as attachment_count
    FROM issue_stages ist
    LEFT JOIN revisions r ON ist.id = r.issue_stage_id
    LEFT JOIN document_attachments da ON r.id = da.revision_id
    GROUP BY ist.document_id
  )
  
  SELECT 
    d.id as document_id,
    d.project_id,
    d.vendor_id,
    d.doc_number,
    d.drawing_kind,
    d.drawing_move_gbn,
    d.discipline,
    d.vendor_doc_number,
    d.title,
    d.pic,
    d.status,
    d.issued_date,
    d.contract_id,
    
    -- 외부 시스템 연동 정보
    d.external_document_id,
    d.external_system_type,
    d.external_synced_at,
    
    -- DOLCE 응답의 추가 정보들
    d.shi_drawing_no,
    d.manager,
    d.manager_enm,
    d.manager_no,
    d.register_group,
    d.register_group_id,
    
    -- 생성자 정보
    d.create_user_no,
    d.create_user_id,
    d.create_user_enm,
    
    -- 프로젝트 및 벤더 정보
    p.code as project_code,
    v.vendor_name,
    v.vendor_code,
    
    -- B4 전용 필드들
    d.c_gbn,
    d.d_gbn,
    d.degree_gbn,
    d.dept_gbn,
    d.s_gbn,
    d.j_gbn,
    
    -- 첫 번째 스테이지 정보
    fsi.first_stage_id,
    fsi.first_stage_name,
    fsi.first_stage_plan_date,
    fsi.first_stage_actual_date,
    
    -- 두 번째 스테이지 정보
    ssi.second_stage_id,
    ssi.second_stage_name,
    ssi.second_stage_plan_date,
    ssi.second_stage_actual_date,
    
    -- 전체 스테이지 (리비전 및 첨부파일 포함)
    COALESCE(sa.all_stages, '[]'::json) as all_stages,
    
    -- 기타
    COALESCE(ac.attachment_count, 0) as attachment_count,
    d.created_at,
    d.updated_at
    
  FROM documents d
  -- projects, vendors 테이블 JOIN (projectId가 이제 documents에 직접 있음)
  LEFT JOIN projects p ON d.project_id = p.id AND p.type = 'ship'
  LEFT JOIN contracts c ON d.contract_id = c.id
  LEFT JOIN vendors v ON c.vendor_id = v.id
  
  -- 스테이지 정보 JOIN
  LEFT JOIN first_stage_info fsi ON d.id = fsi.document_id
  LEFT JOIN second_stage_info ssi ON d.id = ssi.document_id
  LEFT JOIN stage_aggregation sa ON d.id = sa.document_id
  LEFT JOIN attachment_counts ac ON d.id = ac.document_id
  
  ORDER BY d.created_at DESC
);