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,
         max(file_remark) AS file_remark,
         regexp_replace(max(file_remark), '^attachment for (.*)$', '\1') AS remark_path
  FROM files
  WHERE deleted_at IS NULL AND obj_key LIKE '/pdf_images/%'
  GROUP BY 1
  ORDER BY max(created_at) DESC
  LIMIT 10
), norm AS (
  SELECT *, regexp_replace(remark_path, '^\.\./\.\./', '') AS rel_path,
            regexp_replace(regexp_replace(remark_path, '^\.\./\.\./', ''), '^storage/', '') AS no_storage_path,
            regexp_replace(remark_path, '^.*/', '') AS basename
  FROM latest_imgs
), src AS (
  SELECT n.*, f.id AS pdf_file_id, f.path AS pdf_path, f.obj_key AS pdf_obj_key, f.file_name AS pdf_name, f.file_type AS pdf_type, f.md5 AS file_md5
  FROM norm n
  LEFT JOIN LATERAL (
    SELECT * FROM files f
    WHERE f.deleted_at IS NULL AND lower(coalesce(f.file_type,''))='pdf' AND (
      f.path = n.rel_path OR f.path = n.no_storage_path OR
      f.obj_key = '/' || n.no_storage_path OR f.obj_key = n.rel_path OR
      f.path LIKE '%' || n.basename OR f.obj_key LIKE '%' || n.basename
    )
    ORDER BY f.id DESC
    LIMIT 1
  ) f ON true
), doc_rows AS (
  SELECT src.*, d.id doc_id, d.name doc_name, d.status, d.parsed_markdown_file_id, df.id doc_file_id
  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
), md AS (
  SELECT doc_rows.*, mf.path md_path, mf.obj_key md_obj_key, mf.file_name md_name
  FROM doc_rows
  LEFT JOIN files mf ON mf.id=doc_rows.parsed_markdown_file_id AND mf.deleted_at IS NULL
)
SELECT pdf_img_key, image_files, image_last_at, basename, pdf_file_id, pdf_path, pdf_obj_key, pdf_name, doc_id, doc_name, status, parsed_markdown_file_id, coalesce(md_path,''), coalesce(md_obj_key,''), coalesce(md_name,'')
FROM md
ORDER BY image_last_at DESC, doc_id NULLS LAST;
