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

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

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")


for root, dirs, files in os.walk("history"):
    for file in files:
        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])
            filetimestamp = f"{file[16:20]}-{file[13:15]}-{file[10:12]} {file[21:23]}:{file[24:26]}:{file[27:29]}"
            #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()