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

	""")

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

	"""

	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

	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

	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

	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

	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")

* (text) country_code

* (text) club

* (integer) win_count

* (integer) game_count

* (real) rating_before

* (real) rating_after