# -*- coding: utf-8 -*-
"""
Created on Fri Jan 18 11:43:12 2019

@author: daohe
"""
import os, mysql.connector
import xml.etree.ElementTree as xml
import re

db = mysql.connector.connect(host="127.0.0.1", user="root", database = "pg_challenge", password="blueandgreenpotatoes")
#db.autocommit(True)
dbcursor = db.cursor()
dbcursor.execute("use pg_challenge")

dbcursor.execute("SELECT MAX(timestamp) from challenge_1")
leading_edge = dbcursor.fetchall()[0][0] #eg: 2019-01-19 01:10:02 in datetime format.
tle = re.split("-| |:", str(leading_edge)) #tle meaning time leading edge, ['2019', '01', '19', '01', '10', '02']
filename_leading_edge = f"top_users.{tle[0]}.{tle[1]}.{tle[2]}_{tle[3]}.{tle[4]}.{tle[5]}_UTC.xml"
print(filename_leading_edge)

for root, dirs, files in os.walk("history"):
    files.sort()
    start_index = files.index(filename_leading_edge)
    for file_index in range(start_index, len(files)):
        file = files[file_index]
        print(f"processing {file}")
        userslist = []
        tree = xml.parse(f"history/{file}")
        users = tree.getroot()
        for rank, user in enumerate(users):
            userid = user.find('id').text
            name = user.find('name').text
            challenge_credit = user.find('challenge_credit').text
            if (user.find('challenge_tasks') == None):
                challenge_tasks = "NULL"
            else:
                challenge_tasks = user.find('challenge_tasks').text
            
            if(user.find('teamid') == None):
                teamid = "NULL"
                team = "NULL"
            else:
                teamid = user.find('teamid').text
                team = user.find('team').text

            #userslist.append((userid, name, teamid, team, challenge_credit, challenge_tasks, rank+1))
            #print(file[10:-8])
            tfile = re.split("_|\.", str(file))
            filetimestamp = f"{tfile[2]}-{tfile[3]}-{tfile[4]} {tfile[5]}:{tfile[6]}:{tfile[7]}"
            #print(filetimestamp)
            #for user in userslist:
            #    print(user)
            
            sql = f"INSERT INTO challenge_1 (userID, username, teamID, teamname, challenge_credit, challenge_tasks, ranking, timestamp) VALUES ({userid}, \"{name}\", {teamid}, \"{team}\", {challenge_credit}, {challenge_tasks}, {rank+1}, \'{filetimestamp}\')"
            #print(sql)
            try:
                resp = dbcursor.execute(sql) 
                if resp != None:
                    print(sql)
                    print(resp)
                    #print("s")
            except Exception as e:
                print(e)
        db.commit()

db.close()