from egd import connection


cursor=connection.cursor()


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


def playerYear(name,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
	"""
	date1=yearStart(year)
	date2=yearStart(year+1)

	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):
	date1=yearStart(year)
	date2=yearStart(year+1)
	query="""
		select country_code,count(pin) from (
			select pin,count(*),pp.country_code from
				players_performance as pp join tournaments as ts on pp.tournament_code=ts.code
				where datum>=date(?) and datum<date(?)
				group by pin
			)
			group by country_code
			order by country_code asc;
		"""
	res=cursor.execute(query,(date1,date2))
	print("kód země\tpočet aktivních hráčů")
	for rec in res:
		print("\t".join([str(x) for x in rec]))


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
			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
			where ppt.p_country_code=?"""

	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):
	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
				join (select pin,max(datum) as max_datum from players_performance_tournaments where datum>=date(?) and datum<date(?) and p_country_code=? group by pin) as subq on ppt.pin=subq.pin and ppt.datum=subq.max_datum
			group by rating
			order by rating desc
	"""

	res=cursor.execute(query,(date1,date2,country))
	print("rating\tpočet hráčů s takovým a vyšším ratingem")
	for rec in res:
		print("\t".join([str(x) for x in rec]))


def populationAge(year,country):
	date1=yearStart(year)
	date2=yearStart(year+1)
	query="""
		select date(?)-min_datum-1 as playing_years,count(pin)
			from (
				select ppt1.pin,min(ppt1.datum) as min_datum
					from players_performance_tournaments as ppt1
						join players_performance_tournaments as ppt2 on ppt1.pin=ppt2.pin
					where ppt2.datum>=date(?) and ppt2.datum<date(?) and ppt1.p_country_code=?
					group by ppt1.pin
			)
			group by playing_years"""

	res=cursor.execute(query,(date2,date1,date2,country))
	print("celé roky hraní\tpočet hráčů")
	for rec in res:
		print("\t".join([str(x) for x in rec]))


def tournamentAttendance(year):
	date1=yearStart(year)
	date2=yearStart(year+1)
	query="""select country_code,sum(player_count) from tournaments where datum>=date(?) and datum<date(?) group by country_code"""

	res=cursor.execute(query,(date1,date2))
	print("kód země\tpočet hráčů na turnajích")
	for rec in res:
		print("\t".join([str(x) for x in rec]))


# playerYear("Kotowski Jaroslav",2017)
# yearByCountry(2017)
# timeRank(2017,"CZ")
# populationStrength(2017,"CZ")
# populationAge(2017,"CZ")
# tournamentAttendance(2010)