#!/usr/bin/python
# -*- coding: utf-8 -*-

import pywikibot

import MySQLdb
conn = MySQLdb.connect(
    read_default_file = "/data/project/sbot/replica.my.cnf",
    host = "commonswiki.labsdb",
    db = "commonswiki_p",
)

cursor = conn.cursor()
cursor.execute("""
SELECT REPLACE(page_title, '_', ' ') as tt,
    page_namespace as ns
FROM categorylinks
JOIN page
    ON page_id = cl_from
    AND page_is_redirect = 0
    AND page_id NOT IN ("15708733", "79393705")
JOIN revision
    ON rev_id = page_latest
WHERE cl_to = 'Commons_protected_edit_requests'
ORDER BY rev_timestamp ASC
LIMIT 200;
""")

text = '{{Template:Edit request/List/H}}\n'
text += u'=== Commons protected edit requests ===\n'
text += u'[[File:Article-protected.svg|right|60px]]\n'
for tt, ns in cursor.fetchall():
    text += u'* [[:{{subst:ns:%d}}:%s]] - Last edit by [[User:{{REVISIONUSER:{{SUBST:ns:%d}}:%s}}|{{REVISIONUSER:{{SUBST:ns:%d}}:%s}}]] at {{#timel:H:i, d F Y (e)|{{REVISIONTIMESTAMP:{{SUBST:ns:%d}}:%s}}}}\n'  %(ns,tt.decode("utf-8"),ns,tt.decode("utf-8"),ns,tt.decode("utf-8"),ns,tt.decode("utf-8"))

cursor.close()

cursor = conn.cursor()
cursor.execute("""
SELECT REPLACE(page_title, '_', ' ') as tt,
    page_namespace as ns
FROM categorylinks
JOIN page
    ON page_id = cl_from
    AND page_is_redirect = 0
    AND NOT page_id = 37919039
JOIN revision
    ON rev_id = page_latest
WHERE cl_to = 'Commons_protected_edit_requests_(technical)'
ORDER BY rev_timestamp ASC
LIMIT 200;
""")

text += u'\n'
text += u'=== Commons protected edit requests (technical) ===\n'
text += u'[[File:Human-preferences-desktop.svg|right|60px]]\n'
for tt, ns in cursor.fetchall():
    text += u'* [[:{{subst:ns:%d}}:%s]] - Last edit by [[User:{{REVISIONUSER:{{SUBST:ns:%d}}:%s}}|{{REVISIONUSER:{{SUBST:ns:%d}}:%s}}]] at {{#timel:H:i, d F Y (e)|{{REVISIONTIMESTAMP:{{SUBST:ns:%d}}:%s}}}}\n'  %(ns,tt.decode("utf-8"),ns,tt.decode("utf-8"),ns,tt.decode("utf-8"),ns,tt.decode("utf-8"))
cursor.close()

cursor = conn.cursor()
cursor.execute("""
SELECT REPLACE(page_title, '_', ' ') as tt,
    page_namespace as ns
FROM categorylinks
JOIN page
    ON page_id = cl_from
    AND page_is_redirect = 0
    AND NOT page_id = 1715141
JOIN revision
    ON rev_id = page_latest
WHERE cl_to = 'Commons_protected_edit_requests_for_interface_administrators'
ORDER BY rev_timestamp ASC
LIMIT 200;
""")

text += u'\n'
text += u'=== Commons protected edit requests for interface administrators ===\n'
text += u'[[File:Pliers with yellow handles (rotated).svg|right|60px]]\n'
for tt, ns in cursor.fetchall():
    text += u'* [[:{{subst:ns:%d}}:%s]] - Last edit by [[User:{{REVISIONUSER:{{SUBST:ns:%d}}:%s}}|{{REVISIONUSER:{{SUBST:ns:%d}}:%s}}]] at {{#timel:H:i, d F Y (e)|{{REVISIONTIMESTAMP:{{SUBST:ns:%d}}:%s}}}}\n'  %(ns,tt.decode("utf-8"),ns,tt.decode("utf-8"),ns,tt.decode("utf-8"),ns,tt.decode("utf-8"))

site = pywikibot.Site()
page = pywikibot.Page(site, "Template:Edit request/List")
page.put(text, comment=u'Update', minorEdit=False, botflag=False)

cursor.close()
conn.close()