DROP VIEW "public"."vendor_documents_view";--> statement-breakpoint
CREATE VIEW "public"."vendor_documents_view" AS (
    SELECT 
      d.id, 
      d.doc_number,
      d.title,
      d.pic,
      d.status,
      d.issued_date,
  
      d.contract_id,
  
      (
        SELECT id FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_id,
      (
        SELECT stage_name FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_name,
      (
        SELECT plan_date FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_plan_date,
      (
        SELECT actual_date FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_actual_date,
  
      (
        SELECT r.id FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision_id,
      (
        SELECT r.revision FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision,
      (
        SELECT r.uploader_type FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision_uploader_type,
      (
        SELECT r.uploader_name FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision_uploader_name,
  
      (
        SELECT COUNT(*) FROM document_attachments a
        JOIN revisions r ON a.revision_id = r.id
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
      ) AS attachment_count,
  
      d.created_at,
      d.updated_at
    FROM documents d
    JOIN contracts c ON d.contract_id = c.id
  );