CREATE VIEW "public"."legal_works_detail_view" AS (select "legal_works"."id", "legal_works"."category", "legal_works"."status", "legal_works"."company_id", "legal_works"."vendor_code", "legal_works"."vendor_name", "legal_works"."is_urgent", "legal_works"."request_date", "legal_works"."consultation_date", "legal_works"."expected_answer_date", "legal_works"."legal_completion_date", "legal_works"."reviewer", "legal_works"."legal_responder", "legal_works"."has_attachment", "legal_works"."created_at", "legal_works"."updated_at", "legal_work_requests"."review_department", "legal_work_requests"."inquiry_type", "legal_work_requests"."title", "legal_work_requests"."request_content", "legal_work_requests"."is_public", "legal_work_requests"."contract_project_name", "legal_work_requests"."contract_type", "legal_work_requests"."contract_amount", (
        SELECT response_content 
        FROM legal_work_responses lwr_latest 
        WHERE lwr_latest.legal_work_id = "legal_works"."id" 
        ORDER BY lwr_latest.created_at DESC 
        LIMIT 1
      ) as "response_content", (
        SELECT COUNT(*)::integer 
        FROM legal_work_attachments lwa 
        WHERE lwa.legal_work_id = "legal_works"."id"
      ) as "attachment_count" from "legal_works" left join "legal_work_requests" on "legal_works"."id" = "legal_work_requests"."legal_work_id" left join "vendors" on "legal_works"."company_id" = "vendors"."id");