USE commonswiki_p;

SELECT STRAIGHT_JOIN

  CONCAT("[[:File:", REPLACE(img_name, "_", " "), "]]", IF(page_latest=0, " (bad history)", "")) AS "File",

  IF(img_media_type IN ("AUDIO", "VIDEO"), "—",CONCAT("<b>[[{{ns:6}}:", REPLACE(img_name, "_", " "), "|1x1px|alt=404|Removed]]</b>")) AS "Status",/*-*/

  DATE_FORMAT(IFNULL(img_timestamp, NOW()), "%Y-%m-%d") AS "Timestamp",

  CONCAT("{{user|", REPLACE(img_user_text, "_", " "), "}}") AS "Uploader",

  IFNULL(GROUP_CONCAT(log_action ORDER BY log_timestamp SEPARATOR ", "),"") AS "Actions",

  (SELECT COUNT(*) FROM globalimagelinks WHERE gil_to=img_name) AS "Usage",

  "" AS "NOT STATIC"

FROM image

LEFT JOIN page ON page_namespace=6 AND page_title=img_name

LEFT JOIN logging_logindex ON log_namespace=6 AND log_title=img_name

AND log_action NOT IN ("patrol")

WHERE page_title IS NULL

OR page_latest=0 /* Missing revisions */

GROUP BY img_name

;