-- Query to get original upload date from the image and oldimage tables

SELECT 

    CONCAT("# [[:File:", REPLACE(page_title, '_', ' '), "]]") as File, 

    MIN(oi_timestamp) as oldest_old_version_date,

    MAX(img_timestamp) as latest_version_date,

    (SELECT actor_name FROM actor_image ai JOIN oldimage oi ON ai.actor_id = oi.oi_actor WHERE oi.oi_name = page.page_title ORDER BY oi.oi_timestamp ASC LIMIT 1) as original_uploader,

    (SELECT actor_name FROM actor_image ai JOIN image img ON ai.actor_id = img.img_actor WHERE img.img_name = page.page_title ORDER BY img.img_timestamp DESC LIMIT 1) as latest_uploader

FROM 

    page

LEFT JOIN 

    oldimage ON page.page_title = oi_name AND page.page_namespace = 6 AND oi_deleted = 0

LEFT JOIN 

    image ON page.page_title = img_name AND page_namespace = 6

JOIN 

    templatelinks ON tl_from = page_id

JOIN (

    SELECT cl_from

    FROM categorylinks

    WHERE cl_type = "file"

 #     AND cl_to = "GFDL_files_with_disclaimers"

     AND cl_to IN ('صور_جنو_منشأة_بواسطة_المستخدم', 'صور_رخصة_جنو', 'لقطات_شاشة_لويكيبيديا', 'ملفات_رخصة_جنو_للوثائق_الحرة_مع_إخلاء_المسؤولية')

) AS filtered_categories

ON filtered_categories.cl_from = page_id

WHERE page_id NOT IN (

    SELECT cl_from

    FROM categorylinks

    WHERE cl_to IN ('ملفات_المشاع_الإبداعي_النسبة-الترخيص_بالمثل_4.0', 'لقطات_شاشة_لويكيبيديا', 'ملفات_المشاع_الإبداعي_النسبة-الترخيص_بالمثل_3.0')

       OR cl_to LIKE 'Creative_Commons%'

)

  AND img_timestamp >= '20181014000000'

#WHERE

    # oi_name IS NOT NULL  -- This line ensures only files with old versions are included

GROUP BY 

    page_title;