WITH latest_imgs AS (
  SELECT regexp_replace(obj_key, '^/pdf_images/([^/]+)/.*$', '\1') AS pdf_img_key,
         max(created_at) AS image_last_at,
         count(*) AS image_files,
         regexp_replace(regexp_replace(max(file_remark), '^attachment for (.*)$', '\1'), '^.*/', '') AS basename
  FROM files
  WHERE deleted_at IS NULL AND obj_key LIKE '/pdf_images/%'
  GROUP BY 1
  ORDER BY max(created_at) DESC
  LIMIT 10
), src AS (
  SELECT li.*, f.id AS pdf_file_id, f.path AS pdf_path, f.file_name AS pdf_name
  FROM latest_imgs li
  LEFT JOIN LATERAL (
    SELECT * FROM files f
    WHERE f.deleted_at IS NULL AND lower(coalesce(f.file_type,''))='pdf'
      AND (f.path LIKE '%' || li.basename OR f.obj_key LIKE '%' || li.basename)
    ORDER BY f.id DESC
    LIMIT 1
  ) f ON true
), dmap AS (
  SELECT src.*, d.id doc_id, d.name doc_name, d.status
  FROM src
  LEFT JOIN doc_files df ON df.file_id=src.pdf_file_id AND df.deleted_at IS NULL
  LEFT JOIN docs d ON d.doc_file_id=df.id AND d.deleted_at IS NULL
), pstats AS (
  SELECT dmap.*,
         count(p.id) AS paragraph_count,
         count(*) FILTER (WHERE p.paragraph LIKE '%/pdf_images/' || dmap.pdf_img_key || '/%') AS paragraph_with_this_img_key,
         count(*) FILTER (WHERE p.paragraph LIKE '%/pdf_images/%') AS paragraph_with_any_pdf_image,
         count(*) FILTER (WHERE p.paragraph LIKE '%!\[%') AS paragraph_with_md_image
  FROM dmap
  LEFT JOIN paragraphs p ON p.doc_id=dmap.doc_id AND p.deleted_at IS NULL
  GROUP BY dmap.pdf_img_key, dmap.image_last_at, dmap.image_files, dmap.basename, dmap.pdf_file_id, dmap.pdf_path, dmap.pdf_name, dmap.doc_id, dmap.doc_name, dmap.status
)
SELECT pdf_img_key, image_files, image_last_at, doc_id, status, paragraph_count, paragraph_with_this_img_key, paragraph_with_any_pdf_image, paragraph_with_md_image, doc_name
FROM pstats
ORDER BY image_last_at DESC;
