import sys
import re
import datetime
import sqlite3


T_CODE=r"[WEGT]\d{6}[A-Z]*"
NAME_CHARS=r"[\w().#_`?°-]"

connection=sqlite3.connect("egd.db")


def loadTourneys(filename,enc="iso8859_2"):
	# code class rounds players (country) description
	regexp=re.compile(r"(%s)\s+([A-D])\s+(\d+)\s+(\d+)\s+\(([A-Z]{2})\)\s+(.*)" % T_CODE)
	tournaments=dict()

	for line in open(filename,encoding=enc):
		line=line.strip()
		match=regexp.match(line)
		if match is None:
			print("ignored:",line,file=sys.stderr)
			continue
		(code,cls,rc,pc,cc,dsc)=match.groups()
		t=dict(zip(
			["code","class","roundCount","playerCount","countryCode","description"],
			(code,cls,int(rc),int(pc),cc,dsc)
		))
		d=datetime.datetime.strptime(t["code"][1:7],"%y%m%d")
		t["date"]=d.date().isoformat()

		tournaments[t["code"]]=t
	return tournaments


def loadPlayers(filename,enc="iso8859_2"):
	# pin name country club rank tournamentCode wins games gor1 gor2
	regexp=re.compile(r"(\d+)\s+(%s+(?: %s+)*)\s+([A-Z]{2})\s+([\w?.=\\-]+)\s+(\d+[kdp])\s+(%s)\s+(\d+)\s+(\d+)\s+(\d+)\s+(\d+)" % (NAME_CHARS,NAME_CHARS,T_CODE))
	players=[]

	for line in open(filename,encoding=enc):
		line=line.strip()
		if not line: continue

		match=regexp.match(line)
		if match is None:
			print("ignored:",line,file=sys.stderr)
			continue
		(pin,name,cc,cl,rank,tcode,wc,gc,r1,r2)=match.groups()
		record=dict(zip(
			["pin","name","countryCode","club","rank","tournamentCode","winCount","gameCount","rating1","rating2"],
			(int(pin),name,cc,cl,rank,tcode,int(wc),int(gc),int(r1),int(r2))
		))
		players.append(record)
	return players


def setupDB():
	cursor=connection.cursor()

	cursor.execute("""create table tournaments (code text primary key, datum text, class text, round_count integer, player_count integer, country_code text, description text)""")
	cursor.execute("""create table players (pin integer primary key, name text)""")
	cursor.execute("""create table players_performance (pin integer references players(pin), tournament_code text references tournaments(code), country_code text, club text, win_count integer, game_count integer, rating_before real, rating_after real, primary key (pin,tournament_code))""")

	cursor.execute("""
		create view players_performance_tournaments (pin,p_country_code,rating_before,rating_after,t_country_code,datum) as
			select pin,pp.country_code,rating_before,rating_after,ts.country_code,datum
				from players_performance as pp join tournaments as ts on pp.tournament_code=ts.code
	""")

	cursor.execute("""create index pp_pin on players_performance (pin)""")
	cursor.execute("""create index pp_tournament_code on players_performance (tournament_code)""")
	cursor.execute("""create index pp_country_ind on players_performance (country_code)""")
	cursor.execute("""create index t_datum_ind on tournaments (datum)""")

	connection.commit()


def fillDB(tournaments,players):
	cursor=connection.cursor()

	cursor.execute("""delete from players_performance""")
	cursor.execute("""delete from players""")
	cursor.execute("""delete from tournaments""")

	cursor.executemany(
		"""insert into tournaments (code,datum,class,round_count,player_count,country_code,description) values (:code,:date,:class,:roundCount,:playerCount,:countryCode,:description)""",
		tournaments.values()
	)

	cursor.executemany("""insert or replace into players (pin,name) values (:pin,:name)""", players)

	cursor.executemany(
		"""insert into players_performance (pin,tournament_code,country_code,club,win_count,game_count,rating_before,rating_after) values (:pin,:tournamentCode,:countryCode,:club,:winCount,:gameCount,:rating1,:rating2)""",
		players
	)

	connection.commit()