USE commonswiki_p;

SELECT now() AS timestamp;

SELECT CONCAT('# [[:Category:', REPLACE(c.page_title,'_',' '), ']]',

              IF (c.page_len < 5," → '''blank'''",''),

              IF (c.page_title LIKE '%_monument%', ' → consider adding {{tl|Prospective category}} if useful for WLM', ''),

              IF (c.page_title LIKE 'Files_uploaded%Library%', ' → consider adding {{tl|Empty category}} if appropriate', ''))

FROM (SELECT * 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_title NOT LIKE 'ACC_containing%' #Chinese characters

      AND page_title NOT LIKE 'BSicon/%'

      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 'Limédia%'

      AND page_title NOT LIKE 'Naturschutzgebiet%'

      AND page_title NOT LIKE 'Motd_%'

      AND page_title NOT LIKE 'NoFoP-%'

      AND page_title NOT LIKE 'Père-Lachaise%'

      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 'WikiChallenge_%'

      AND page_title NOT LIKE 'WikiNusantara_%'

      AND page_title NOT LIKE 'WikiProject%'

      AND page_title NOT LIKE '%Blekinge%' #temp

      AND page_title NOT LIKE '%Berlin%' #temp

#      AND page_title NOT REGEXP '^[0-9]' #temp keep years out

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

     ) AS c

LEFT JOIN (SELECT cl_from FROM categorylinks

            WHERE cl_to IN ('Broken_category_redirects',

                  'Categories_with_authority_control_data',

                  'Category_redirects',

                  'Commons_maintenance_content',

                  'Disambiguation',

                  'Hidden_categories', #temp

                  'Images_for_cleanup',

                  'Interwiki_from_wikidata',

                  'No_more_recognized_taxon',

                  'NoUploads',

                  'Quality_images_by_user',

                  'Sites_of_Community_Importance_in_Spain_by_ID',

                  'Taxon_categories',

                  'Undeletion_requests',

                  'Unicode-CJK', #Chinese/Japanese/Korean characters

                  'UTC_offsets')

      OR 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 'Natural_heritage%'

      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%'

          ) AS cl

            ON cl.cl_from = c.page_id

LEFT JOIN (SELECT tl_from FROM templatelinks JOIN linktarget ON lt_id = tl_target_id

            WHERE lt_namespace = 10

            AND lt_title IN ('CatDevelop',

                             'Category_for_discussion',

                             'Creator_possible',

                             'Empty_category',

                             'Emptycat',

                             'EmptyCatGood',

                             'Fotothek_category_header',

                             'Gemeentelijk_monument',

                             'Global_maintenance_category',

                             'IMOcat',

                             'Infobox_Wikidata',

                             'Infobox_wikidata',

                             'Local_maintenance_category',

                             'NoFoP',

                             'On_Wikidata',

                             'Prospective_category',

                             'Unresolved',

                             'WDPA',

                             'Wikidata',

                             'Wikidata_box',

                             'Wikidata_Infobox',

                             'Wikidata_infobox',

                             'Wdbox',

                             'Інфабокс')

           ) AS tl

           ON tl.tl_from = c.page_id

WHERE cl.cl_from IS NULL

AND tl.tl_from IS NULL

AND ((c.page_len < 5) #blank page

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

    )

LIMIT 2000;