USE commonswiki_p;

SELECT now() AS timestamp;

SELECT CONCAT('# [[:Category:', REPLACE(page_title,'_',' '), ']]', IF(page_len = 0," '''blank'''",'')) AS emptycats

FROM categorylinks

RIGHT JOIN page

ON cl_to = page_title

WHERE cl_to IS NULL

AND page_namespace = 14

AND page_is_redirect = 0

AND ((page_len < 6) #blank page

  OR (DATEDIFF(CURRENT_TIMESTAMP, (SELECT MAX(rev_timestamp) FROM revision WHERE rev_page = page_id)) > 274) #last edit >9 months

  OR (EXISTS (SELECT 1 FROM categorylinks

      WHERE cl_from = page_id

      AND cl_to = 'Category_pages_with_broken_file_links')))

AND page_title NOT LIKE 'Commons:%'

AND page_title NOT LIKE 'Collection_of_%'

AND page_title NOT LIKE 'FoP-%'

AND page_title NOT LIKE 'FOP-%'

AND page_title NOT LIKE 'Images_from_Wiki_Loves_%'

AND page_title NOT LIKE 'Images_%/Reports/%'

AND page_title NOT LIKE 'IMO_%'

AND page_title NOT LIKE 'Naturschutzgebiet%'

AND page_title NOT LIKE 'Motd_%'

AND page_title NOT LIKE 'NoFoP-%'

AND page_title NOT LIKE 'Potd_%'

AND page_title NOT LIKE 'RCE_suggested:%'

AND page_title NOT LIKE 'Uncategorized_%'

AND page_title NOT LIKE 'Unidentified_%'

AND page_title NOT LIKE 'User_%'

AND page_title NOT LIKE 'WikiProject%'

AND page_title NOT LIKE '%_monuments_and_memorials' #temp

AND page_title NOT REGEXP '(check_needed|Expedition_Wikipedia|FOP_cases|requiring_review|to_be_categorised|to_be_checked|to_be_classified|unidentified)'

AND NOT EXISTS (SELECT 1 FROM categorylinks

    WHERE cl_from = page_id

    AND cl_to IN ('Broken_category_redirects',

                  'Categories_with_authority_control_data',

                  'Category_redirects',

                  'Commons_maintenance_content',

                  'Disambiguation',

                  'Hidden_categories', #temp

                  'Images_for_cleanup',

                  'No_more_recognized_taxon',

                  'NoUploads',

                  'Taxon_categories',

                  'Undeletion_requests',

                  'Unicode-CJK',

                  'UTC_offsets'))

AND NOT EXISTS (SELECT 1 FROM categorylinks

    WHERE cl_from = page_id

    AND ((cl_to LIKE 'Biosphere_reserves_%')

      OR (cl_to LIKE 'Cultural_heritage_%')

      OR (cl_to LIKE 'FoP-%')

      OR (cl_to LIKE 'GLAM_%')

      OR (cl_to LIKE 'Images_from_Wiki_Loves_%')

      OR (cl_to LIKE 'Nature_reserves%')

      OR (cl_to LIKE 'NoFoP-%')

      OR (cl_to LIKE 'Protected_area%')

      OR (cl_to LIKE 'Protected_localities%')

      OR (cl_to LIKE 'Radical_%') #chinese characters

      OR (cl_to LIKE 'User_categories%')))

AND NOT EXISTS (SELECT 1 FROM templatelinks

    WHERE tl_from = page_id

    AND tl_namespace = 10

    AND tl_title IN ('CatDevelop',

                     'Category_for_discussion',

                     'Creator_possible',

                     'Empty_category',

                     'Emptycat',

                     'EmptyCatGood',

                     'Fotothek_category_header',

                     'Global_maintenance_category',

                     'IMOcat',

                     'Local_maintenance_category',

                     'NoFoP',

                     'On_Wikidata',

                     'Prospective_category',

                     'WDPA',

                     'Wikidata'))

AND NOT EXISTS (SELECT 1 FROM revision WHERE rev_page = page_id AND rev_user = 318877) #OTFW

AND NOT EXISTS (SELECT 1 FROM revision WHERE rev_page = page_id AND rev_user = 8018) #Fridolin freudenfett

#AND NOT EXISTS (SELECT 1 FROM revision WHERE rev_page = page_id AND rev_user = 108410) #Sitacuisses

#AND NOT EXISTS (SELECT 1 FROM revision WHERE rev_page = page_id AND rev_user = 133084) #Tuvalkin

LIMIT 1000;