#!/usr/bin/python

import MySQLdb, sys, hashlib
from optparse import OptionParser

def initDB(db, cursor):
	querystr = """CREATE TABLE `tests` (
  `date` date NOT NULL,
  `matrix` varchar(500) NOT NULL,
  `comparator` varchar(500) NOT NULL,
  `test` varchar(500) NOT NULL,
  `correct` int(11) NOT NULL,
  `incorrect` int(11) NOT NULL,
  `skipped` int(11) NOT NULL,
  `accuracy` double NOT NULL,
  `hash` varchar(200) NOT NULL,
  `comment` varchar(500) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='WBST results table';"""
	cursor.execute(querystr)


def getWBSTresults(wbst_resultfile):
	entries = []
	firstline = True
	for line in open(wbst_resultfile,"r"):
		if firstline:
			firstline = False
			continue
		line = line.strip()
		contents = line.split(";")
		if len(contents)<9:
			continue
		entries.append([contents[1],contents[2],contents[4],contents[5],contents[6],contents[7],contents[8],hashlib.sha1(contents[1]+contents[2]+contents[4]).hexdigest()])
	return entries

def uploadResults(options, entries):
	# Open database connection
	db = MySQLdb.connect(options.host,options.user,options.passwd,options.db,port = int(options.port) )

	# prepare a cursor object using cursor() method
	cursor = db.cursor()

	# execute SQL query using execute() method.


	query = "SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = 'tests'"
	cursor.execute(query)
	r = cursor.fetchone()
	if r == None:
		print "No WBST tests table created, creating 'tests' table"
		initDB(db,cursor)

	newentries = []
	for entry in entries:
		existstr = "SELECT matrix,comparator,test,correct,incorrect,skipped,accuracy,hash FROM tests WHERE hash=\"" + entry[7] + "\""
		cursor.execute(existstr)
		r = cursor.fetchone()
		if not r == None:
			r = list(r)
			if r[0] == entry [0] and r[1] == entry[1] and r[2] == entry[2]:
				if not (str(r[3]),str(r[4]),str(r[5]),str(r[6])) == (entry[3],entry[4],entry[5],entry[6]):
					print (str(r[3]),str(r[4]),str(r[5]),str(r[6]))
					print (entry[3],entry[4],entry[5],entry[6])
					print "A conflict exists with the existing database. Entry to be inserted: \n" + str(entry) + "\nentry in database:\n" + str(r)
			else:
				newentries.append(entry)
		else:
			newentries.append(entry)
		
	for entry in newentries:
		if entry[1] == "":
			continue
		querystr = "INSERT INTO tests (date,matrix,comparator,test,correct,incorrect,skipped,accuracy,hash,comment) VALUES (NOW(),\"" + entry[0] + "\",\"" + entry[1] + "\",\"" + \
		entry[2] + "\"," + entry[3] + "," + entry[4] + "," + entry[5] + "," + entry[6] + "," + "\"" + entry[7] + "\",\"" + options.comment + "\")"
		cursor.execute(querystr)

	querystr = "DELETE FROM tests WHERE comparator=\"\""
	cursor.execute(querystr)

	# disconnect from server
	db.close()

def main():
	parser = OptionParser()
	parser.add_option("-u", "--user", dest="user", default = 'wbst', help="Database user")
	parser.add_option("-H", "--host", dest="host", default = 'localhost', help="Database host")
	parser.add_option("-p", "--passwd", dest="passwd", default = 'wbst', help="Database password")
	parser.add_option("-d", "--db", dest="db", default = 'wbst', help="Database schema")
	parser.add_option("-P", "--port", dest="port", default = '3306', help="Database port")
	parser.add_option("-r", "--result", dest="result", help="Result file")
	parser.add_option("-c", "--comment", dest="comment", default = '', help="A commentary for this upload")

	options = parser.parse_args(sys.argv)[0]

	entries = getWBSTresults(options.result)
	uploadResults(options,entries)

if __name__ == "__main__":
	main()
