import csv

import psycopg2
import psycopg2.extras

import sys

conn_string = "host='localhost' dbname='postgres' user='postgres' password='Hockeyboy10'"


def get_cursor():
    conn = psycopg2.connect(conn_string)
    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    return cursor


def run_query(cursor, query):
    cursor.execute(query)
    records = cursor.fetchall()
    return records


def load_query(path):
    with open(path, 'r') as query_file:
        contents = query_file.readlines()
        lines = map(lambda bytes: str(bytes), contents)
        no_comments = filter(lambda line: not line.lstrip().startswith('--'), lines)
        no_bom = list(map(lambda line: line.replace('\xef\xbb\xbf', ''), no_comments))
        joined = ''.join(no_bom)
        split = joined.split(';')
        return split[0]

def createTeamTable(conn, cursor):
	try:
		cursor.execute("DROP TABLE IF EXISTS teams")
		cursor.execute("CREATE TABLE teams( \
						teamid	VARCHAR(3), \
						team_name	VARCHAR(63) \
						)")
		conn.commit()
	except Exception as e:
		print(e)
		conn.rollback()

def createAmateurTable(conn, cursor):
	try:
		cursor.execute("CREATE TABLE amateur_teams( \
						team_name	VARCHAR(63), \
						league	VARCHAR(63) \
						)")
		conn.commit()
	except Exception as e:
		print(e)
		conn.rollback()


def main():

    createTeamTable(conn, cursor)
    teams = set()

    with open('teams.csv', mode='r') as csv_file:
    	csv_reader = csv.reader(csv_file, delimiter=',', quotechar='|')
    	for row in csv_reader:
    		teams.add((row[1], row[0]))

    for abbr, name in teams:
    	cursor.execute("insert into teams (teamid, team_name) \
    					values('{}', '{}')".format(abbr, name))

    am_teams = set()
	with open('hockey_ref.csv', mode='r') as csv_file:
		csv_reader = csv.reader(csv_file, delimiter=',', quotechar='|')
		for row in csv_reader:
			curString = row[8].split()
			if curString[len(curString)-1][0] == '(':
				league = curString[len(curString)-1].lower()
				teamName = curString[0].lower()
				for i in range(1, len(curString)-1):
					teamName = teamName + " " + curString[i].lower()
				print teamName.count("'")
				teamName.replace("'", "''")
				am_teams.add((teamName, league))

	conn = psycopg2.connect(conn_string)
    cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    #createAmateurTable(conn, cursor)

    for team, league in am_teams:
    	print team, league
    	cursor.execute("insert into amateur_teams (team_name, league) \
    					values('{}', '{}')".format(team, league))


if __name__ == '__main__':
    main()