-- 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 ('ജി.എഫ്.ഡി.എൽ_ചിത്രങ്ങൾ', 'വിക്കിപീഡിയ_സ്ക്രീൻഷോട്ടുകൾ', 'xxx')

) AS filtered_categories

ON filtered_categories.cl_from = page_id

WHERE page_id NOT IN (

    SELECT cl_from

    FROM categorylinks

    WHERE cl_to IN ('വിക്കിപീഡിയ_സ്ക്രീൻഷോട്ടുകൾ', 'ക്രിയേറ്റിവ്_കോമൺസ്_ചിത്രങ്ങൾ', 'Cc-by-sa-2.5,2.0,1.0_images') # വിക്കിപീഡിയ_സ്ക്രീൻഷോട്ടുകൾ exclude screenshots is duallicensed

       OR cl_to LIKE 'Creative_Commons_Attribution%'

)

  AND img_timestamp >= '20181014000000'

#WHERE

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

GROUP BY 

    page_title;