#!/usr/bin/python
import MySQLdb
import getpass

f = open('orgchanges.txt', 'r')

connection=MySQLdb.connect("localhost","amstan",getpass.getpass("enter the db password: "),"contest")
cursor=connection.cursor()
def query(*args):
    print args
    cursor.execute(*args)

def AddUpdateOrg(name):
    #TODO
    #find id org by name
    #if not exist insert and get id
    #if exist return id
    query("""select user_id from users where username = %s;""",(name,))
    try:
        return int(cursor.fetchone()[0])
    except TypeError:
        query("""insert into organizations (name) VALUES (%s);""",(name,))
        AddUpdateOrg(name)


def PlayerLookup(name):
    #find player by name
    #return None if you can't
    query("""select user_id from users where username = %s;""",(name,))
    result=cursor.fetchone()
    try:
        return int(cursor.fetchone()[0])
    except TypeError:
        return None

def UpdatePlayerOrg(id,orgId):
    #update statement by playerid and org id
    print("attempting to update", id, orgId)
    query("update users SET org_id = %s WHERE user_id = %s;",(orgId,id))

for line in f:
    items = line.strip().split('|')
    org = items[0]
    ids = []
    if len(items) > 1:
        ids = items[1].split(',')
    orgId = AddUpdateOrg(org)

    for id in ids:
        if not id.isdigit():
            id = PlayerLookup(id)
        if id != None:
            UpdatePlayerOrg(id,orgId)
        else:
            print("player", id, "not found")

connection.close()