import sys

from egd import connection

cursor=connection.cursor()

def yearStart(year):

	return "{0}-01-01".format(year)

	query="""

		select name, datum, win_count, game_count, rating_after, rating_after-rating_before

			from players natural join players_performance as pp join tournaments as ts on pp.tournament_code=ts.code

			where name=? and datum>=date(?) and datum<date(?)

			order by datum asc

	"""

	res=cursor.execute(query,(name,date1,date2))

	print("jméno\tdatum\tpočet výher\tpočet partií\trating po turnaji\tzměna")

	for rec in res:

		print("\t".join([str(x) for x in rec]))

def _yearByCountry(year):

	"""Počty hráčů s turnajem odehraným v daném roce, dělené podle země původu. Při změně příslušnosti se hráč započítá oběma zemím."""

	date1=yearStart(year)

	date2=yearStart(year+1)

	query="""

		select p_country_code,count(pin) as player_count from (

			select pin,count(*),p_country_code from

				players_performance_tournaments

				where datum>=date(?) and datum<date(?)

				group by pin,p_country_code

			)

			group by p_country_code

			order by p_country_code asc

		"""

	return cursor.execute(query,(date1,date2))

def yearsByCountry(year1,year2):

	res=dict()

	for year in range(year1,year2):

		records=_yearByCountry(year)

		for (countryCode,playerCount) in records:

			if countryCode not in res:

				res[countryCode]=dict()

			res[countryCode][year]=playerCount

		print("\r",year,end="",file=sys.stderr)

	print("\r",end="",file=sys.stderr)

	print("Počty aktivních hráčů podle let")

	print("kód země\t"+"\t".join(str(y) for y in range(year1,year2)))

	for countryCode in sorted(res.keys()):

		print(countryCode,end="\t")

		print("\t".join(str(res[countryCode].get(y,0)) for y in range(year1,year2)))

def timeRank(year,country):

	date1=yearStart(year)

	date2=yearStart(year+1)

	query="""

		select ppt.pin,date(?)-min_datum-1,min_datum,ppt.rating_after as rating,name

			from

				(select pin,min(datum) as min_datum,max(datum) as max_datum from players_performance_tournaments where datum<date(?) group by pin having max_datum>=date(?)) as subq

				join players_performance_tournaments as ppt on subq.pin=ppt.pin and subq.max_datum=ppt.datum natural join players

			where ppt.p_country_code=?

			order by rating desc

	"""

	res=cursor.execute(query,(date2,date2,date1,country))

	print("pin\tcelé roky hraní\tprvní turnaj\trating")

	for rec in res:

		print("\t".join([str(x) for x in rec]))

def _populationStrength(year,country):

	"""Rozdělení populace země v daném roce podle ratingu (po stovkách). Uvažují se pouze aktivní hráči."""

	date1=yearStart(year)

	date2=yearStart(year+1)

	query="""

		select round((rating_after-50)/100)*100 as rating, count(ppt.pin)

			from players_performance_tournaments as ppt

	for rec in res:

		print("\t".join([str(x) for x in rec]))

def timeToPeak():

	"""Jakého hráči dosáhli maximálního ratingu a jak dlouho jim to trvalo.

	Uvažuje hráče začínající s ratingem<=600 a s vrcholem před 1.1.2017."""

	# join on pin, max_rating může duplikovat řádky, když měl hráč stejný max_rating vícekrát.

	# proto ten ošklivý vnější group by :-ú

	query="""

		select subq.pin,min(min_rating),min(max_rating),min(_min_datum) as min_datum,min(datum) as peak_datum,(julianday(min(datum))-julianday(min(_min_datum)))/365.25 as time_to_peak

			from players_performance_tournaments as ppt

				join (select pin,min(rating_before) as min_rating,max(rating_after) as max_rating,min(datum) as _min_datum from players_performance_tournaments group by pin having min_rating<=600) as subq on ppt.pin=subq.pin and ppt.rating_after=max_rating

			group by ppt.pin

			having peak_datum<date('2017-01-01')

	"""

	res=cursor.execute(query)

	print("pin\tmin rating\tmax rating\tprvní turnaj\tdosažení max ratingu\troky k max ratingu")

	for rec in res:

		print("\t".join([str(x) for x in rec]))

# timeRank(2017,"CZ")

# yearsByCountry(2001,2018)

# populationStrength(2001,2018,"RU")

# populationAge(2001,2018,"RU")

# tournamentAttendance(2001,2018)

# fetchByRating(2600,"CZ")

# timeToPeak()