#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# Skeleton code for lab 2 in 729G28
# Erik Prytz [erik.prytz@liu.se]

import pymysql
import re
import csv
import re

# det här är fulkod, men vi kom inte på något bättre sätt
coach_dict = {}

#Creates database connection, use the password that was sent to you by email
conn = pymysql.connect(host='db-und.ida.liu.se', port=3306, user='fabis254', passwd='fabis2540bcb', db='fabis254')
cur = conn.cursor()

def insert_coach():
	with open('AmericanFootballCoach.csv', 'r') as csvfile:
		csvreader = csv.reader(csvfile, delimiter = ',')
		csv
		next(csvreader)

		global coach_dict
		for row in csvreader:
			ID = row[0]
			NAME = row[1]
			INFO = safe_string(row[2])
			BDATE = set_date(row[3])
			BPLACE = row[4]
			TEAM = None
			if ID in coach_dict.keys():
				TEAM = coach_dict[ID]

			#print(str(ID) + ": " + str(TEAM))


			coach_data = (ID, NAME, INFO, BDATE, BPLACE, TEAM)
			INSERT_DATA = "INSERT INTO coach (id, name, info, bdate, bplace, team) \
							VALUES (%s, %s, %s, %s, %s, %s)"
			cur.execute(INSERT_DATA, coach_data)
			conn.commit()

			## fill the College table
			COLLEGE = row[5]

			## Handle multiple values, get array
			colleges = split_multi_value(COLLEGE)
			for college in colleges:
				if colleges[0] is not None:
					col_data = (college, ID)
					INSERT_DATA = "INSERT INTO colleges (name, coach_id) \
									VALUES (%s, %s)"
					cur.execute(INSERT_DATA, col_data)
					conn.commit()


		#cur.execute("""SELECT * FROM coach""")
		#result = cur.fetchall()
		#for row in result:
			#print(row)


		csvfile.close()

def insert_team():
	with open('AmericanFootballTeam.csv', 'r') as csvfile:
		csvreader = csv.reader(csvfile, delimiter = ',')
		next(csvreader)

		# Make a dictionary with coaches
		global coach_dict
		for row in csvreader:
			ID = row[0]
			NAME = row[1]
			OWNER = row[3]
			SYEAR = row[4][:10]

			team_data = (ID, NAME, OWNER, SYEAR)
			INSERT_DATA = "INSERT INTO team (id, name, owner, syear) \
							VALUES (%s, %s, %s, %s)"
			cur.execute(INSERT_DATA, team_data)
			conn.commit()

			# Update the dictionary
			COACH_ID = row[2]
			coach_dict[COACH_ID] = NAME


			#Handle all rows and the data
			#Insert values
		#cur.execute("""SELECT * FROM team""")
		#result = cur.fetchall()
		#for row in result:
		#	print(row)
		#print(coach_dict)
		csvfile.close()

def insert_player():
	with open('AmericanFootballPlayer.csv', 'r') as csvfile:
		csvreader = csv.reader(csvfile, delimiter = ',')
		next(csvreader)

		for row in csvreader:
			ID = row[0]
			NAME = set_name(row[1])
			INFO = row[2]
			WEIGHT = set_float(row[3])
			BPLACE = row[6]
			BDATE = set_date_long(row[7])
			DEBUTTEAM = row[8]
			DRAFTYEAR = set_date_long(row[9])
			HEIGHT = set_float(row[11])

			player_data = (ID, NAME, INFO, WEIGHT, BPLACE, BDATE, DEBUTTEAM,
						   DRAFTYEAR, HEIGHT)
			INSERT_DATA = "INSERT INTO player (id, name, info, weight, bplace, \
											 bdate, debutteam, draftyear, height) \
							VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
			cur.execute(INSERT_DATA, player_data)
			conn.commit()

			### position
			POSITION = row[12]
			positions = split_multi_value(POSITION)
			if positions[0] is not None:
				for pos in positions:
					pos_data = (pos, ID)
					INSERT_DATA = "INSERT INTO position (name, player_id) \
					VALUES (%s, %s)"
					cur.execute(INSERT_DATA, pos_data)
					conn.commit()


			### alias
			ALIAS = row[4]
			aliases = split_multi_value(ALIAS)
			if aliases[0] is not None:
				for ali in aliases:
					ali_data = (ali, ID)
					INSERT_DATA = "INSERT INTO alias (name, player_id) \
					VALUES (%s, %s)"
					cur.execute(INSERT_DATA, ali_data)
					conn.commit()

			### current_team

			# Gets foreign key error
			CUR_TEAM = row[13]
			cur_teams = split_multi_value(CUR_TEAM)
			if cur_teams[0] is not None:
				for team in cur_teams:
					if not team_exists(team):
						add_team(team)

					cur_team_data = (team, ID)
					INSERT_DATA = "INSERT INTO current_team (team, player_id) \
					VALUES (%s, %s)"
					cur.execute(INSERT_DATA, cur_team_data)
					conn.commit()

			### Former_team
			FOR_TEAM = row[10]
			for_teams = split_multi_value(FOR_TEAM)
			if for_teams[0] is not None:
				for team in for_teams:
					if not team_exists(team):
						add_team(team)

					for_team_data = (team, ID)
					INSERT_DATA = "INSERT INTO former_team (team, player_id) \
					VALUES (%s, %s)"
					cur.execute(INSERT_DATA, for_team_data)
					conn.commit()

		csvfile.close()

#To solve the lab you will need to define at least one more function here.

def split_multi_value(values_str):
	"""Returns a list of values from a multivalued attribute"""
	# Check for null values
	if values_str == 'NULL':
		values_str = None
	elif values_str[0] == "{":
		# Remove outer brackets
		values_str = values_str[1:-1]
		# split by |
		values_split = values_str.split("|")
		return values_split
	# return as list
	return [values_str]

def safe_string(string):
	"""For handling string errors due to characters outside of latin-1"""
	new_str = re.sub('\ufffd', '-', string)
	return new_str

def set_name(name):
	"""Returns a string that only contains a name. Instead of a name followed
		by info in parenthesis"""
	split = name.split("(")
	split = split.pop(0)
	return split

def set_date(date):
	"""For sorting out dates from M/D/YYYY to YYYY-MM-DD"""
	if date == "NULL":
		return None
	elif date != 'NULL':
		split = date.split('/')
		order =  [split[2], split[0], split[1]]
		i = 0
		for entry in order:
			if len(entry) == 1:
				order[i] = '0' + entry
			i += 1
		new_date = '-'.join(order)
		return new_date
	else:
		print("Error: Unknown date format")

def set_date_long(date):
	if date != 'NULL':
		new_date = date[:10]
	else:
		new_date = None
	return new_date

def set_float(flo):
	if flo != 'NULL':
		new_flo = flo
	else:
		new_flo = None
	return new_flo

#def add_non_existing_team(team_name):
#	# Enclose team_name in extra fnuttar
#	enc_team = "'%s'" % team_name
#	# Check if team in the db team table
#	find_team_name = "SELECT name FROM team \
#						WHERE name=%s;"
#	cur.execute(find_team_name % enc_team)
#	existing_team = cur.fetchone()
#	if existing_team == None:
#		print("inserting new team")
#		# If the team is not in the team table, add it to the table
#		INSERT_TEAM = "INSERT INTO team (name) \
#						 VALUES (%s);"
#		cur.execute(INSERT_TEAM, team_name)
#		conn.commit()
	#print(existing_team[0])

def team_exists(team_name):
	"""return true or false"""
	enc_team = "'%s'" % team_name
	# Check if team in the db team table
	find_team_name = """SELECT name FROM team
						WHERE name=%s;"""
	cur.execute(find_team_name % enc_team)
	existing_team = cur.fetchone()
	if existing_team is not None:
		return True
	return False

def add_team(team_name):
	INSERT_TEAM = """INSERT INTO team (name)
					 VALUES (%s);"""
	cur.execute(INSERT_TEAM, team_name)
	conn.commit()




def reset_tables():

	cur.execute("""
	DROP TABLE IF EXISTS colleges;
	DROP TABLE IF EXISTS position;
	DROP TABLE IF EXISTS alias;
	DROP TABLE IF EXISTS current_team;
	DROP TABLE IF EXISTS former_team;
	DROP TABLE IF EXISTS coach;
	DROP TABLE IF EXISTS player;
	DROP TABLE IF EXISTS team;

	CREATE TABLE team (id INT NOT NULL DEFAULT 0,
		   name VARCHAR(100) PRIMARY KEY NOT NULL DEFAULT 'noname',
		   owner VARCHAR(250) DEFAULT 'unknown',
		   syear DATE DEFAULT '1970-01-01');

	-- Create Coach and colleges

	CREATE TABLE coach (id INT PRIMARY KEY NOT NULL DEFAULT 0,
		   name VARCHAR(100) NOT NULL DEFAULT '',
		   info VARCHAR(3000),
		   bdate DATE DEFAULT 0,
		   bplace VARCHAR(250),
		   team VARCHAR(100) );

	CREATE TABLE colleges (name VARCHAR(100) NOT NULL DEFAULT 'noname',
		   coach_id INT NOT NULL,
		   CONSTRAINT PK_college PRIMARY KEY (name, coach_id));

	-- Create Player related tables

	CREATE TABLE player (id INT NOT NULL PRIMARY KEY DEFAULT 0,
		   name VARCHAR(100) DEFAULT 'noname',
		   info VARCHAR(500),
		   weight FLOAT,
		   bplace VARCHAR(250),
		   bdate DATE,
		   height FLOAT,
		   debutteam VARCHAR(250),
		   draftyear DATE);

	CREATE TABLE position (name VARCHAR(250) NOT NULL DEFAULT 'no position',
	     player_id INT NOT NULL DEFAULT 0,
	     CONSTRAINT PK_position PRIMARY KEY (name, player_id));

	CREATE TABLE alias (name VARCHAR(250) NOT NULL DEFAULT 'no alias',
	    player_id INT NOT NULL DEFAULT 0,
	    CONSTRAINT PK_alias PRIMARY KEY (name, player_id));

	CREATE TABLE current_team (team VARCHAR(250) NOT NULL,
	    player_id INT NOT NULL DEFAULT 0,
	    CONSTRAINT PK_current_team PRIMARY KEY (team, player_id));

	CREATE TABLE former_team (team VARCHAR(250) NOT NULL DEFAULT 'no team',
	    player_id INT NOT NULL DEFAULT 0,
	    CONSTRAINT PK_former_team PRIMARY KEY (team, player_id));

	-- Add foreign keys
	ALTER TABLE coach
	  ADD CONSTRAINT FK_team
	  FOREIGN KEY (team) REFERENCES team(name);


	ALTER TABLE colleges
	  ADD CONSTRAINT FK_coach_id
	  FOREIGN KEY (coach_id) REFERENCES coach(id);

	ALTER TABLE position
	  ADD CONSTRAINT FK_pos_player_id
	  FOREIGN KEY (player_id) REFERENCES player(id);

	ALTER TABLE alias
	  ADD CONSTRAINT FK_als_player_id
	  FOREIGN KEY (player_id) REFERENCES player(id);

	ALTER TABLE current_team
	  ADD CONSTRAINT FK_cur_team
	  FOREIGN KEY (team) REFERENCES team(name),
	  ADD CONSTRAINT FK_cur_player_id
	  FOREIGN KEY (player_id) REFERENCES player(id);

	ALTER TABLE former_team
	  ADD CONSTRAINT FK_for_team
	  FOREIGN KEY (team) REFERENCES team(name),
	  ADD CONSTRAINT FK_for_player_id
	  FOREIGN KEY (player_id) REFERENCES player(id);
	  """)
	conn.commit()


if __name__ == "__main__":
	reset_tables()
	insert_team()
	insert_coach()
	insert_player()

	#Other function here
	cur.close()
	conn.close()