CREATE VIEW "public"."document_stages_view" AS (
  SELECT
    d.id AS document_id,
    d.doc_number,
    d.title,
    d.status,
    d.issued_date,
    d.contract_id,

    (
      SELECT COUNT(*) 
      FROM issue_stages 
      WHERE document_id = d.id
    ) AS stage_count,

    (
      SELECT json_agg(
        json_build_object(
          'id', i.id,
          'stageName', i.stage_name,
          'planDate', i.plan_date,
          'actualDate', i.actual_date,
          'createdAt', i.created_at,
          'updatedAt', i.updated_at
        )
      )
      FROM issue_stages i
      WHERE i.document_id = d.id
    ) AS stage_list,

    d.created_at,
    d.updated_at
  FROM documents d
);