import settings
##Config
report_title=settings.rootpage+'Duplicate files'
report_template = u'''
List of files that are exact, bit for bit duplicates of another file. The report is limited to the first 200 duplicate files that it finds, and excludes fully protected files;
data as of <onlyinclude>~~~~~</onlyinclude>.




{| class="wikitable sortable plainlinks"
|-
! First File
! Number of uses
! Second File
! Number of uses
|-
%s
|}
<small>(Took %.2f seconds to generate)</small>
'''
###
from wikipedia import Page,getSite
from time import time
site=getSite()
del getSite
from MySQLdb import connect
db = connect(host=settings.host, db=settings.dbname, read_default_file='~/.my.cnf')
t=time()
cursor=db.cursor()
cursor.execute("""SELECT CONCAT(ns_name,concat(':',replace(MAIN.img_name,'_',' '))) AS first,(Select count(*) from imagelinks as MAINCOUNT WHERE MAINCOUNT.il_to=MAIN.img_name) as firstcount, CONCAT(ns_name,concat(':',replace(LAME.img_name,'_',' ')))  as second,
	(Select count(*) from imagelinks as LAMECOUNT WHERE LAMECOUNT.il_to=LAME.img_name) as secondcount
	FROM image as MAIN
	JOIN toolserver.namespace on dbname=%s and ns_id=6
	JOIN image as LAME on LAME.img_name!=MAIN.img_name and LAME.img_sha1=MAIN.img_sha1 and LAME.img_timestamp<MAIN.img_timestamp
	JOIN page on page_namespace=6 and page_title in (MAIN.img_name,LAME.img_name)
	LEFT JOIN page_restrictions on pr_page=page_id and pr_type='edit' and pr_level='sysop'
	where isnull(pr_page) LIMIT 200;"""
	,(settings.dbname,))




t=time()-t
row=''
for firstfile,firstcount,secondfile,secondcount in cursor.fetchall():
	row+="""\n|[[:%s]]
	|[{{fullurl:Special:WhatLinksHere/%s|hidetrans=1&hidelinks=1&hideredirs=1}} %d]
	|[[:%s]]
	|[{{fullurl:Special:WhatLinksHere/%s|hidetrans=1&hidelinks=1&hideredirs=1}} %d]
	|-"""%(unicode(firstfile,'utf-8'), unicode(firstfile,'utf-8'), firstcount, unicode(secondfile,'utf-8'),unicode(secondfile,'utf-8'), secondcount)




report_template=report_template%(row,t)
report_page=Page(site,report_title)
report_page.put(report_template,comment='Updating Page...')